MySQL索引實現
上一篇我們詳細瞭解了B+樹的實現原理（傳送門）。我們知道，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的是：
表中的資料發生變更的時候，會影響其他記錄地址的變化，如果輔助索引中記錄資料的地址，此時會受影響，而主鍵的值一般是很少更新的，當頁中的記錄發生地址變更的時候，對輔助索引是沒有影響的。
索引管理和使用
資料準備
請參考第21篇（MySQL全面瓦解21（番外）：一次深夜優化億級資料分頁的奇妙經歷）中模擬的千萬資料，我們以這個資料為測試資料。
建立索引
create 方式：
1 create [unique] index index_name on t_name(c_name[(length)]);
alter表 方式：
create [unique] index index_name on t_name(c_name[(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、儘量能夠覆蓋常用欄位