我所理解的MySQL之二：索引

mysql教學欄位今天介紹相關索引知識。

MySQL 系列的第二篇，主要討論MySQL 中關於索引的一些問題，包括索引種類、資料模型、索引執行流程、最左前綴原則、索引失效情況以及索引下推等內容。

最早知道索引應該是在大二的資料庫原理這門課中，當一個查詢語句非常慢時，可以透過為某些欄位添加索引來提高查詢效率。

還有一個很經典的例子：我們可以把資料庫想像成字典，把索引想像成目錄，當我們藉助字典的目錄來查詢一個字的時候，就能體現出索引的作用了。

1. 索引種類

在MySQL 中，從索引的邏輯或說欄位特性來區分，索引大致分為以下幾個種類：普通索引、唯一索引、主鍵索引、聯合索引和前綴索引。

• 普通索引：最基礎的索引，沒有任何限制。
• 唯一索引：索引列的值必須唯一。
• 主鍵索引：特殊的唯一索引，作為主鍵它的值不能為空。
• 聯合索引：聯合索引就是索引列為多個欄位的普通索引，需要考慮最左邊前綴原則。
• 前綴索引：對字元類型的前幾個字元或二進位類型的前幾個位元組建立索引。

還有另外一種從實體儲存來區分的索引分類：叢集索引和非叢集索引。

• 叢集索引：索引順序與資料儲存順序一致，其葉子節點儲存的是資料行。
• 非聚集索引：非叢集索引的葉子節點儲存的是叢集索引的值，同時它是基於叢集索引建立的。

簡單來說，所謂的叢集索引就是索引 key 與資料行在一起，而非叢集索引的索引 key 對應的值是叢集索引的值。

2. 索引的資料結構

常見的用於實作索引的資料結構有雜湊表、有序數組和搜尋樹。

2.1 哈希索引

哈希表是一個以key-value 形式來儲存資料的容器，和HashMap 一樣，雜湊索引也會將key 透過特定的雜湊函數計算得到索引值，然後在數組的對應位置存放key 對應的value，如果有兩個key 透過雜湊函數計算得到的索引值相同（發生雜湊衝突），那麼數組的這個位置就會變成一個鍊錶，存放所有哈希值相同的value。

所以在一般情況下，雜湊表進行等值查詢的時間複雜度可以達到O(1)，但是在發生雜湊衝突的情況下，還需要額外遍歷鍊錶中的所有值，才能夠找到符合條件的數據。

另外，考慮到經過雜湊函數計算得到的索引是不規則的－雜湊表希望所有的key 能夠得到充分散列，這樣才能讓key 均勻分佈，不浪費空間－即哈希表的key 是非順序的，所以使用哈希表來進行區間查詢時很慢的，排序也是同樣的道理。

所以，哈希表只適用於等值查詢。

2.2 有序數組

有序數組顧名思義是一個按照key 的順序進行排列的數組，它進行等值查詢的時間複雜度使用二分查詢可以達到O(logN)，這與哈希表相比遜色不少。

但是透過有序數組進行範圍查詢的效率較高：首先透過二分查詢找到最小值（或最大值），然後反向遍歷，直到另一個邊界。

至於排序，有序數組本來就是有序的，天然已經排好序了，當然排序字段不是索引字段就另說了。

但是有序數組有一個缺點，由於數組元素是連續且有序的，如果此時插入新的資料行，為了維持有序數組的有序性，需要將比此元素key 大的元素都往後移動一個單位，給他騰出一個地方插入。而這種維護索引的方式的代價是很大的。

所以，有序數組適合用來儲存衣服初始化過後就不再更新的資料。

2.3 搜尋樹

了解資料結構的人應該會知道，搜尋樹是查詢時間複雜度為O(logN)，更新的時間複雜度也是O(logN)的資料結構。所以搜尋樹相較於雜湊表和有序數組來說兼顧查詢與更新兩方面。也正是因為這個原因，在 MySQL 中最常用的資料模型就是搜尋樹。

而考慮到索引是存放在磁碟中的，如果搜尋樹是一棵二元樹，那麼它的子節點只能有左右兩個，在資料比價多的情況下，這棵二元樹的樹高可能會非常高，當MySQL 進行查詢的時候，可能因為樹高導致磁碟I/O次數過多，查詢效率變慢。

2.4 全文索引

除此之外，還有一種全文索引，它透過建立倒排索引，解決了判斷欄位是否包含的問題。

倒排索引是用來儲存在全文搜尋下某個單字在一個文件或一組文件中的儲存位置的映射，透過倒排索引可以根據單字快速取得包含這個單字的文件清單。

當透過關鍵字進行檢索的時候，全文索引就會派上用場。

3. InnoDB 中的 BTree 索引

3.1 B 樹

這是一棵比較簡單的B 樹。

圖片來源: Data Structure Visualizations

從上面這張範例圖也可以看到，這棵B 樹最下面的葉子節點儲存了所有的元素，並且是按順序儲存的，而不是葉子節點僅儲存索引列的值。

3.2 圖解 BTree 索引

在 InnoDB 中，基於 BTree 的索引模型的最為常用的，下面以一個實際的例子來圖解 InnoDB 中 BTree 索引的結構。

CREATE TABLE `user`  (  `id` int(11) NOT NULL,  `name` varchar(36) DEFAULT NULL,  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,  INDEX `nameIndex`(`name`) USING BTREE
) ENGINE = InnoDB;-- 插入数据insert into user1(id,name,age) values (1,'one',21),(2,'two',22),(3,'three',23),(4,'four',24),(5,'five',25);复制代码

在這張表中只有兩個字段：主鍵 id 和 name 字段，同時建立了一個以 name 字段為索引列的 BTree 索引。

以主鍵id 欄位的為索引的索引，又叫主鍵索引，它的索引樹結構是：索引樹的非葉子階段存放的都是主鍵id 的值，葉子節點存放的值是該主鍵id 對應的整個資料行，如下圖所示：

也正因為主鍵索引的葉子節點儲存的是該主鍵id 對應的整個資料行，主鍵索引又被稱為叢集索引。

而以name 欄位為列的索引樹，非葉子節點存放的同樣是索引列的值，而其葉子階段存放的值是主鍵id 的值，如下圖所示。

3.3 索引的執行流程

先來看下面這句 SQL，查詢 user 表中 id=1 的資料行。

select * from user where id=1;复制代码

這句SQL 的執行流程很簡單，儲存引擎會走主鍵id 的索引樹，當找到id=1 時，就會把索引樹上id=1 的資料行回傳（由於主鍵值是唯一的，所以找到命中目標就會停止搜索，直接返回結果集）。

3.3.1 回表

接下來再看使用普通索引進行查詢的情況，它的情況與主鍵索引略有不同。

select * from user where name='one';复制代码

上面這句SQL 查詢語句的流程是這樣的：首先儲存引擎會搜尋普通索引name 欄位的索引樹，當命中name 等於one 的記錄後，儲存引擎需要經過一個非常重要的步驟：回表。

由於普通索引的索引樹子節點存放的是主鍵值，當查詢語句需要查詢除主鍵id 及索引列之外的其他欄位時，需要根據主鍵id 的值再回到主鍵索引樹中進行查詢，得到主鍵id 對應的整個資料行，然後從中取得客戶端所需的欄位後，才將這一行加入結果集。

隨後儲存引擎會繼續搜尋索引樹，直到遇到第一個不滿足name='one' 的記錄才會停止搜索，最後將所有命中的記錄傳回客戶端。

我們把根據從普通索引查詢到的主鍵 id 值，再在主鍵索引中查詢整個資料行的過程稱之為回表。

當資料量十分龐大時，回表是一個十分耗時的過程，所以我們應該盡量避免回表發生，這就引出了下一個問題：使用覆蓋索引避免回表。

3.3.2 覆蓋索引

不知道你有沒有註意到，在上一個回表的問題中有這樣一句描述：「當查詢語句需要查詢除主鍵id 及索引列以外的其他欄位時...”，在這種場景下需要透過回表來取得其他的查詢欄位。也就是說，如果查詢語句需要查詢的欄位只有主鍵 id 和索引列的欄位時，是不是就不需要回表了？

下面來分析一波這個過程，先建立一個聯合索引。

alter table user add index name_age ('name','age');复制代码

那麼這棵索引樹的結構圖應該是下面這樣：

#聯合索引索引樹的子節點順序是按照聲明索引時的字段來排序的，類似於order by name, age ，而它索引對應的值與普通索引一樣是主鍵值。

select name,age from user where name='one';复制代码

上面这条 SQL 是查询所有 name='one' 记录的 name 和 age 字段，理想的执行计划应该是搜索刚刚建立的联合索引。

与普通索引一样，存储引擎会搜索联合索引，由于联合索引的顺序是先按照 name 再按照 age 进行排序的，所以当找到第一个 name 不是 one 的索引时，才会停止搜索。

而由于 SQL 语句查询的只是 name 和 age 字段，恰好存储引擎命中查询条件时得到的数据正是 name, age 和 id 字段，已经包含了客户端需要的字段了，所以就不需要再回表了。

我们把只需要在一棵索引树上就可以得到查询语句所需要的所有字段的索引成为覆盖索引，覆盖索引无须进行回表操作，速度会更快一些，所以我们在进行 SQL 优化时可以考虑使用覆盖索引来优化。

4. 最左前缀原则

上面所举的例子都是使用索引的情况，事实上在项目中复杂的查询语句中，也可能存在不使用索引的情况。首先我们要知道，MySQL 在执行 SQL 语句的时候一张表只会选择一棵索引树进行搜索，所以一般在建立索引时需要尽可能覆盖所有的查询条件，建立联合索引。

而对于联合索引，MySQL 会遵循最左前缀原则：查询条件与联合索引的最左列或最左连续多列一致，那么就可以使用该索引。

为了详细说明最左前缀原则，同时说明最左前缀原则的一些特殊情况。

5. 索引失效场景

即便我们根据最左前缀的原则创建了联合索引，还是会有一些特殊的场景会导致索引失效，下面举例说明。

假设有一张 table 表，它有一个联合索引，索引列为 a,b,c 这三个字段，这三个字段的长度均为10。

CREATE TABLE `demo`  (  `a` varchar(1) DEFAULT NULL,  `b` varchar(1) DEFAULT NULL,  `c` varchar(1) DEFAULT NULL,  INDEX `abc_index`(`a`, `b`, `c`) USING BTREE
) ENGINE = InnoDB;复制代码

5.1 全字段匹配

第一种情况是查询条件与索引字段全部一致，并且用的是等值查询，如：

select * from demo where a='1' and b='1' and c='1';select * from demo where c='1' and a='1' and b='1';复制代码

输出上述两条 SQL 的执行计划来看它们使用索引的情况。

mysql> explain select * from demo where a='1' and b='1' and c='1';
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref               | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------------+|  1 | SIMPLE      | demo  | NULL       | ref  | abc_index     | abc_index | 18      | const,const,const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)

mysql> explain select * from demo where c='1' and a='1' and b='1';
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref               | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------------+|  1 | SIMPLE      | demo  | NULL       | ref  | abc_index     | abc_index | 18      | const,const,const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)复制代码

第一条 SQL 很显然能够用到联合索引。

从执行计划中可以看到，第二条 SQL 与第一条 SQL 使用的索引以及索引长度是一致的，都是使用 abc_index 索引，索引长度为 18 个字节。

按理说查询条件与索引的顺序不一致，应该不会用到索引，但是由于 MySQL 有优化器存在，它会把第二条 SQL 优化成第一条 SQL 的样子，所以第二条 SQL 也使用到了联合索引 abc_index。

综上所述，全字段匹配且为等值查询的情况下，查询条件的顺序不一致也能使用到联合索引。

5.2 部分字段匹配

第二种情况是查询条件与索引字段部分保持一致，这里就需要遵循最左前缀的原则，如：

select * from demo where a='1' and b='1';select * from demo where a='1' and c='1';复制代码

上述的两条查询语句分别对应三个索引字段只用到两个字段的情况，它们的执行计划是：

mysql> explain select * from demo where a='1' and b='1';
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref         | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------------+|  1 | SIMPLE      | demo  | NULL       | ref  | abc_index     | abc_index | 12      | const,const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)

mysql> explain select * from demo where a='1' and c='1';
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+--------------------------+| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+--------------------------+|  1 | SIMPLE      | demo  | NULL       | ref  | abc_index     | abc_index | 6       | const |    1 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+--------------------------+1 row in set, 1 warning (0.00 sec)复制代码

从它们的执行计划可以看到，这两条查询语句都使用到了 abc_index 索引，不同的是，它们使用到索引的长度分别是：12、6 字节。

在这里需要额外提一下索引长度的计算方式，对于本例中声明为 varchar(1) 类型的 a 字段，它的索引长度= 1 * (3) + 1 + 2 = 6。

• 第一个数字 1 是该字段声明时的长度。
• 第二个数字 3 是该字段字符类型的长度：utf8=3, gbk=2, latin1=1。
• 第三个数字 1 是该字段的默认类型，若默认允许 NULL，第三个数字是 1，因为 NULL 需要一个字节的额外空间；若默认不允许 NULL，这里应该是0。
• 第四个数字 2 是 varchar 类型的变长字段需要附加的字节。

所以这两条查询语句使用索引的情况是：

1. 使用联合索引，索引长度为 12 字节，使用到的索引字段是 a,b 字段；
2. 使用联合索引，索引长度为 6 字节，使用到的索引字段是 a 字段；

由此可见：最左前缀原则要求，查询条件必须是从索引最左列开始的连续几列。

5.3 范围查询

第三种情况是查询条件用的是范围查询（,!=,=,between,like）时，如：

select * from demo where a='1' and b!='1' and c='1';复制代码

这两条查询语句的执行计划是：

mysql> EXPLAIN select * from demo where a='1' and b!='1' and c='1';
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+|  1 | SIMPLE      | demo  | NULL       | range | abc_index     | abc_index | 12      | NULL |    2 |    10.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+1 row in set, 1 warning (0.00 sec)复制代码

从执行计划可以看到，第一条 SQL 使用了联合索引，且索引长度为 12 字节，即用到了 a,b 两个字段；第二条 SQL 也使用了联合索引，索引长度为 6 字节，仅使用了联合索引中的 a 字段。

综上所述，在全字段匹配且为范围查询的情况下，也能使用联合索引，但只能使用到联合索引中第一个出现范围查询条件的字段。

需要注意的是：

• like 必须要求是左模糊匹配才能用到索引，因为字符类型字段的索引树也是有序的。
• between 并不一定是范围查询，它相当于使用 in 多值精确匹配，所以 between 并不会因为是范围查询就让联合索引后面的索引列失效。

5.4 查询条件为函数或表达式

第四种情况是查询条件中带有函数或特殊表达式的，比如：

select * from demo where id + 1 = 2;select * from demo where concat(a, '1') = '11';复制代码

可能由于数据的原因（空表），我输出的执行计划是使用了联合索引的，但是事实上，在查询条件中，等式不等式左侧的字段包含表达式或函数时，该字段是不会用到索引的。

至于原因，是因为使用函数或表达式的情况下，索引字段本身的值已不具备有序性。

5.5 其他索引失效的场景

• 查询影响行数大于全表的25%
• 查询条件使用 (!=), not in, is not null
• in 查询条件中值数据类型不一致，MySQL 会将所有值转化为与索引列一致的数据类型，从而无法使用索引

6. 索引下推

上文中已经罗列了联合索引的实际结构、最左前缀原则以及索引失效的场景，这里再说一下索引下推这个重要的优化规则。

select * from demo where a > '1' and b='1';

mysql> explain select * from demo where a > '1' and b='1';
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+|  1 | SIMPLE      | demo  | NULL       | range | abc_index     | abc_index | 6       | NULL |    1 |    10.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+1 row in set, 1 warning (0.00 sec)复制代码

上面这条查询语句，从它的执行计划也可以看出，它使用的索引长度为 6 个字节，只用到了第一个字段。

所以 MySQL 在查询过程中，只会对第一个字段 a 进行 a > '1' 的条件判断，当满足条件后，存储引擎并不会进行 b=1 的判断， 而是通过回表拿到整个数据行之后再进行判断。

这好像很蠢，就算索引只用到了第一个字段，但明明索引树中就有 b 字段的数据，为什么不直接进行判断呢？

听上去好像是个 bug，其实在未使用索引下推之前整个查询逻辑是：由存储引擎检索索引树，就算索引树中存在 b 字段的值，但由于这条查询语句的执行计划使用了联合索引但没有用到 b 字段，所以也无法进行 b 字段的条件判断，当存储引擎拿到满足条件（a>'1'）的数据后，再由 MySQL 服务器进行条件判断。

在 MySQL5.6 版本中对这样的情况进行优化，引入索引下推技术：在搜索索引树的过程中，就算没能用到联合索引的其他字段，也能优先对查询条件中包含且索引也包含的字段进行判断，减少回表次数，提高查询效率。

在使用索引下推优化之后，b 字段作为联合索引列，又存在于查询条件中，同时又没有在搜索索引树时被使用到，MySQL 服务器会把查询条件中关于 b 字段的部分也传给存储引擎，存储引擎会在搜索索引树命中数据之后再进行 b 字段查询条件的判断，满足的才会加入结果集。

Ps: 执行计划中 Extra 字段的值包含 Using index condition 就代表使用到了索引下推。

7. 温故知新

1. 索引分类？聚簇索引结构？非聚簇索引结构？
2. 常用的实现索引的数据模型？
3. B+树索引的执行流程？
4. 什么是回表？如何优化？
5. 什么是覆盖索引？
6. 什么是最左前缀原则？
7. 索引在哪些情况下可能会失效？
8. 什么是索引下推？

更多相关免费学习推荐：mysql教程(视频)

以上是我所理解的MySQL之二：索引的詳細內容。更多資訊請關注PHP中文網其他相關文章！