MySQL索引實作
我們知道，MySQL内部索引是由不同的引擎實作的，主要包含InnoDB和MyISAM這兩種，并且這兩種引擎中的索引都是使用b+樹的結構來存儲的。
InnoDB引擎中的索引
Innodb中有2種索引：主鍵索引（也叫聚集索引）、輔助索引（也叫非聚集索引）。
主鍵索引：每個表隻有一個主鍵索引，b+樹結構，葉子節點存儲主鍵的值以及對應整條記錄的資料，非葉子節點不存儲記錄的資料，隻存儲主鍵的值。
當表中未指定主鍵時，MySQL内部會自動給每條記錄添加一個隐藏的rowid字段（預設4個位元組）作為主鍵，用rowid建構聚集索引。聚集索引在MySQL中即主鍵索引。
輔助索引：每個表可以有多個輔助索引，b+樹結構，非聚集索引葉子節點存儲字段（索引字段）的值以及對應記錄主鍵的值，其他節點隻存儲字段的值（索引字段），這就是與聚集索引不同的地方。每個表可以有多個非聚集索引。
MySQL中非聚集索引進一步區分：
MyISAM引擎中的索引
也是B+樹結構，MyISM使用的是非聚簇索引，如下圖，非聚簇索引的兩棵B+樹看上去沒什麼不同，節點的結構完全一緻隻是存儲的内容不同而已，主鍵索引B+樹的節點存儲了主鍵，
輔助鍵索引B+樹存儲了輔助鍵。表資料存儲在獨立的地方，這兩顆B+樹的葉子節點都使用一個位址指向真正的表資料，對于表資料來說，這兩個鍵沒有任何差别。
由于索引樹是獨立的，通過輔助鍵檢索無需通路主鍵的索引樹。
下圖更形象說明這兩種索引的差別，這邊假設了一個存儲4行資料的表。Id為主鍵索引，Name作為輔助索引，圖中清晰的展現了聚簇索引和非聚簇索引的差異。
我們來分析一下圖中資料檢索過程：
InnoDB資料檢索過程
上面的表中有2個索引：id作為主鍵索引，name作為輔助索引。
如果需要查詢id=14的資料，隻需要在左邊的主鍵索引中檢索就可以了。
如果需要搜尋name='Ellison'的資料，需要2步：
1、先在輔助索引中檢索到name='Ellison'的資料，擷取id為14
2、再到主鍵索引中檢索id為14的記錄
輔助索引這個查詢過程在mysql中叫做回表，相對于主鍵索引多了第二步操作。
MyISAM資料檢索過程
1、在索引中找到對應的關鍵字，擷取關鍵字對應的記錄的位址
2、通過記錄的位址查找到對應的資料記錄
對比發現：innodb中最好是采用主鍵查詢，這樣隻需要一次索引，如果使用輔助索引檢索，涉及多一步的回表操作，比主鍵查詢要耗時一些。
而innodb中輔助索引差別于myisam的是：
表中的資料發生變更的時候，會影響其他記錄位址的變化，如果輔助索引中記錄資料的位址，此時會受影響，而主鍵的值一般是很少更新的，當頁中的記錄發生位址變更的時候，對輔助索引是沒有影響的。
索引管理和使用
建立索引
create 方式：
1 create [unique] index index_name on t_name(c_name[(length)]); alter表 方式：
1 alter t_name add [unique] index index_name on (cname[(length)]); 這邊需注意的是：
index_name 代表索引名稱、t_name代表 表名稱、c_name代表字段名稱。
[] 中括号的内容是可以省略的，也就是說 unique 和 length 可以不寫。如果加上了unique，表示建立唯一索引。
如果字段是char、varchar類型，length可以小于字段實際長度，如果是blog、text等長文本類型，必須指定length。
如果tname後面隻寫一個字段，就是單列索引，如果需要寫多個字段，可以使用逗号隔開，這種叫做複合索引。
删除索引
1 drop index index_name on t_name; 檢視索引
1 show index from t_name; 索引修改
即先删除索引，再重建索引：drop +create。
示例
emp表中有500W資料 我們用emp來做測試
1 mysql> select count(*) from emp;
2 +----------+
3 | count(*) |
4 +----------+
5 | 5000000 |
6 +----------+
7 1 row in set 檢視和建立索引
記得我們之前在emp表上做過索引，是以先看一下這個表目前所有的索引
可以看到，目前主鍵字段id和depno字段上都有建立索引
1 mysql> desc emp;
2 +----------+-----------------------+------+-----+---------+----------------+
3 | Field | Type | Null | Key | Default | Extra |
4 +----------+-----------------------+------+-----+---------+----------------+
5 | id | int(10) unsigned | NO | PRI | NULL | auto_increment |
6 | empno | mediumint(8) unsigned | NO | | 0 | |
7 | empname | varchar(20) | NO | | | |
8 | job | varchar(9) | NO | | | |
9 | mgr | mediumint(8) unsigned | NO | | 0 | |
10 | hiredate | datetime | NO | | NULL | |
11 | sal | decimal(7,2) | NO | | NULL | |
12 | comn | decimal(7,2) | NO | | NULL | |
13 | depno | mediumint(8) unsigned | NO | MUL | 0 | |
14 +----------+-----------------------+------+-----+---------+----------------+
15 9 rows in set
16
17 mysql> show index from emp;
18 +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
19 | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
20 +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
21 | emp | 0 | PRIMARY | 1 | id | A | 4952492 | NULL | NULL | | BTREE | | |
22 | emp | 1 | idx_emp_id | 1 | id | A | 4952492 | NULL | NULL | | BTREE | | |
23 | emp | 1 | idx_emp_depno | 1 | depno | A | 18 | NULL | NULL | | BTREE | | |
24 +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
25 3 rows in set 我們在沒有做索引的字段上做一下查詢看看，在500W資料中查詢一個名叫LsHfFJA的員工，消耗 2.239S
再看看他的執行過程，掃描了4952492 條資料才找到該行資料：
1 mysql> explain select * from emp where empname='LsHfFJA';
2 +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
3 | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4 +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
5 | 1 | SIMPLE | emp | ALL | NULL | NULL | NULL | NULL | 4952492 | Using where |
6 +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
7 1 row in set 我們在empname這個字段上建立索引
1 mysql> create index idx_emp_empname on emp(empname);
2 Query OK, 0 rows affected
3 Records: 0 Duplicates: 0 Warnings: 0
4
5 mysql> show index from emp;
6 +-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
8 +-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
9 | emp | 0 | PRIMARY | 1 | id | A | 4952492 | NULL | NULL | | BTREE | | |
10 | emp | 1 | idx_emp_id | 1 | id | A | 4952492 | NULL | NULL | | BTREE | | |
11 | emp | 1 | idx_emp_depno | 1 | depno | A | 18 | NULL | NULL | | BTREE | | |
12 | emp | 1 | idx_emp_empname | 1 | empname | A | 1650830 | NULL | NULL | | BTREE | | |
13 +-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
14 4 rows in set 再看一下這個執行效率，就會發現有質的飛躍：0.001S，就是這麼神奇，學過之前那篇的B+ Tree就知道，它不用從頭開始掃表核對，而是很小次數的io讀取
再看看他的執行過程，一次定位到該條資料：
1 mysql> explain select * from emp where empname='LsHfFJA';
2 +----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-----------------------+
3 | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4 +----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-----------------------+
5 | 1 | SIMPLE | emp | ref | idx_emp_empname | idx_emp_empname | 22 | const | 1 | Using index condition |
6 +----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-----------------------+
7 1 row in set 設定合适的索引長度
根據我們之前的了解，每個磁盤塊（disk）存儲的内容是有限的，如果一個頁中可以存儲的索引記錄越多，那麼查詢效率就會提高，是以我們可以指定索引的字段長度。
但并不是越短越好，要保證字元類型字段查詢有足夠高的區分度，如果隻設定了一個長度，反而導緻查詢的相似比對度不高。
長度的原則是要恰到好處，太長索引檔案就會變大，是以要在區分度和長度上做一個平衡。
如果在我們搜尋的内容中，最後的内容是一緻的或者高度一緻的，那我們就可以省略，比如在使用者的email字段上做索引，幾乎前10個字元是不一樣的，結尾限定在 @****，那麼通過前面10個字元就可以定位一個email位址了。
我們在該字段建立索引的時候就可以指定長度為10，這樣相對于整個email字段更短些，查詢效果确卻基本一樣，這樣一個頁中也可以存儲更多的索引記錄。
像我們上面的那個 empname 字段，基本都是6位數的，隻是小部分是超過6位數，而且字尾基本一緻，是以6位數之後的區分度差不多。
有一個判斷 高區分度以及合适長度索引 的通用算法，如下：
1 select count(distinct left(`c_name`,calcul_len))/count(*) from t_name; 下面是對 empname 做的分析，比對度越高搜尋效率越高：
1 mysql> select count(distinct left(`empname`,3))/count(*) from emp;
2 +--------------------------------------------+
3 | count(distinct left(`empname`,3))/count(*) |
4 +--------------------------------------------+
5 | 0.0012 |
6 +--------------------------------------------+
7 1 row in set
8
9 mysql> select count(distinct left(`empname`,4))/count(*) from emp;
10 +--------------------------------------------+
11 | count(distinct left(`empname`,4))/count(*) |
12 +--------------------------------------------+
13 | 0.0076 |
14 +--------------------------------------------+
15 1 row in set
16
17 mysql> select count(distinct left(`empname`,6))/count(*) from emp;
18 +--------------------------------------------+
19 | count(distinct left(`empname`,6))/count(*) |
20 +--------------------------------------------+
21 | 0.1713 |
22 +--------------------------------------------+
23 1 row in set
24
25 mysql> select count(distinct left(`empname`,7))/count(*) from emp;
26 +--------------------------------------------+
27 | count(distinct left(`empname`,7))/count(*) |
28 +--------------------------------------------+
29 | 0.1713 |
30 +--------------------------------------------+
31 1 row in set 删除索引
1 mysql> drop index idx_emp_empname on emp;
2 Query OK, 0 rows affected
3 Records: 0 Duplicates: 0 Warnings: 0
4
5 mysql> show index from emp;
6 +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
8 +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
9 | emp | 0 | PRIMARY | 1 | id | A | 4952492 | NULL | NULL | | BTREE | | |
10 | emp | 1 | idx_emp_id | 1 | id | A | 4952492 | NULL | NULL | | BTREE | | |
11 | emp | 1 | idx_emp_depno | 1 | depno | A | 18 | NULL | NULL | | BTREE | | |
12 +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
13 3 rows in set 執行完删除指令再檢視，發現索引已經沒了
小結
本文隻是了解索引的基本用法，後面會認真講一講索引的性能分析和優化政策。
總之，理想的索引應該符合以下特征：
1、相對低頻的寫操作，以及高頻的查詢的表和字段上建立索引
2、字段區分度高
3、長度小（合适的長度，不是越小越好）
4、盡量能夠覆寫常用字段
為幫助開發者們提升面試技能、有機會入職BATJ等大廠公司，特别制作了這個專輯——這一次整體放出。
大緻内容包括了： Java 集合、JVM、多線程、并發程式設計、設計模式、Spring全家桶、Java、MyBatis、ZooKeeper、Dubbo、Elasticsearch、Memcached、MongoDB、Redis、MySQL、RabbitMQ、Kafka、Linux、Netty、Tomcat等大廠面試題等、等技術棧！
歡迎大家關注公衆号【Java爛豬皮】，回複【666】，擷取以上最新Java後端架構VIP學習資料以及視訊學習教程，然後一起學習，一文在手，面試我有。
每一個專欄都是大家非常關心，和非常有價值的話題，如果我的文章對你有所幫助，還請幫忙點贊、好評、轉發一下，你的支援會激勵我輸出更高品質的文章，非常感謝！