MySQL全面瓦解23：MySQL索引實現和使用

2021 年 2 月 25 日
筆記
index, Innodb, MyISAM, MySQL, MySQL全面瓦解, 索引, 聚集索引

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、盡量能夠覆蓋常用欄位

Tags: index Innodb MyISAM MySQL MySQL全面瓦解索引聚集索引