左匹配原則，聚集索引，回表查詢，索引覆寫 你真的懂了嗎

一個問題

有一張表test，這張表除了主鍵id外，還有a，b,  c 三列

假設給這三個欄位建一個復合索引 index_abc (a, b, c)，問，下面幾種查詢中，哪種查詢會用到索引 index_abc ？

1. 查詢一

select * from test where a > 1000 and b > 1000;

2. 查詢二

select * from test where a > 1000 and c > 1000；

3. 查詢三

select * from test where b > 1000 and c > 1000;

這是一個經典的面試題，由這個問題，我可以相關問你，什么是 左匹配原則？什么是 聚集索引？什么是 索引覆寫？什么是 回表？

下面給大家捋一捋，以下試驗基于MySQL5.7-InnoDB

左匹配原則

接著上面的問題，回到剛剛的三個查詢上，首先，我們怎么知道查詢有沒有用到索引？有沒有什么命令是可以幫助我們分析查詢陳述句呢？答案當然是有的，那就 explain 命令

我們分別對上面的陳述句進行 explain，看看有哪些資訊：

mysql> explain select * from test where a > 1000 and b > 1000;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | test  | NULL       | range | index_abc     | index_abc | 4       | NULL | 5060 |    33.33 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+

mysql> explain select * from test where a > 1000 and c > 1000;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | test  | NULL       | range | index_abc     | index_abc | 4       | NULL | 5060 |    33.33 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+

mysql> explain select * from test where b > 1000 and c > 1000;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+-------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows  | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+-------+----------+--------------------------+
|  1 | SIMPLE      | test  | NULL       | index | NULL          | index_abc | 12      | NULL | 10120 |    11.11 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+-------+----------+--------------------------+

我們可以看到，對查詢陳述句執行 explain 后，回傳了12列資訊，各列說明如下：

通常分析sql陳述句，我們只關注type，possible_keys，key，rows

對三條查詢陳述句進行explain后，我們發現：

• where a > 1000 and b > 1000 和 where a > 1000 and c > 1000條件的查詢 結果是一樣的，其中type指明的索引查找方式為range，possible_keys 可能使用的索引為 index_abc，key 實際使用的索引為 index_abc
• where b > 1000 and c > 1000 條件的查詢中，type的值為index，possible_keys為NULL，key的值為 index_abc

上面的range 和 index有什么區別呢？

• range：僅檢索給定范圍內的行，使用索引選擇行
• index：索引聯接型別與 ALL 相同，只不過掃描索引樹，有兩種情況：
  • 如果索引是查詢的 覆寫索引（后文有講），并且可用于滿足表中所需的所有資料，則僅掃描索引樹，在這種情況下，"額外"（Extra）串列示使用索引， 僅索引掃描通常比全部掃描快，因為索引的大小通常小于表資料
  • 使用索引中的讀取執行完整的表掃描，以按索引順序查找資料行，使用索引不顯示在"額外"列中，也就是說：如果不是覆寫索引，使用索引不顯示在"額外"列中

換句話說，

range是使用了索引，并且能夠在對應的索引樹上使用快速查找的方法進行快速查找，是有范圍的查找，使用了range，就一定用到了我們建的索引，而index只能是通過掃描整個索引樹

上面也提到ALL，那么type還有哪幾種比較常見的值呢？下面列舉一下（具體其他型別值，看以參考官方檔案）：

• system：該表只有一行 （= 系統表），這是 const 聯接型別的特殊情況
• const：表示通過索引一次就找到了，因為只匹配一行資料，所以很快，如將主鍵置于where串列中，MySQL就能將該查詢轉換為一個常量表最多有一個匹配行，在查詢開始時讀取該行，由于只有一行，因此優化器的其余部分可以將該行中的列中的值視為常量，將主鍵或 UNIQUE 索引的所有部分與常量值進行比較時，將使用 const
• eq_ref：唯一性索引掃描，對于前一表中的每一行組合，將從此表中讀取一行，常見于主鍵或唯一索引掃描，除了system 和 const 型別之外，這是最佳聯接型別
• ref：非唯一性索引掃描，對于前一表中的每一行組合，將從此表中讀取具有匹配索引值的所有行
• ALL：將遍歷全表以找到匹配的行

好，回到上面三條查詢陳述句上，為什么where條件為a > 1000 and b > 1000 和 a > 1000 and c > 1000 的 type 是 range（用到索引）, 而where條件為 b > 1000 and c > 1000 的 type 是 index 呢？這里面索引樹（B+樹）的構建方式及存盤結構有關

那么復合索引B+樹是怎樣的呢？看圖，一圖勝百字

?

對于索引來說只不過比單值索引多了幾列，而這些索引列全都出現在索引樹上，對于復合索引，存儲引擎會首先根據第一個索引列排序，如上圖我們可以單看第一個索引列，如，1 1 4 15 18....他是單調遞增的；如果第一列相等則再根據第二列排序，依次類推就構成了上圖的索引樹

以創建的索引 index_abc (a, b, c)為例，如上圖所示，每個結點都有三個鍵值，從上往下分別對應這a，b，c三個索引列

構造索引樹時，首先使用多列索引的第一列構建的索引樹，以 index_abc (a, b, c) 為例就是優先使用a列構建，當b列值相等時再以c列排序

因此，索引的第一列也就是a列可以說是從左到右單調遞增的，但我們看b列和c列并沒有這個特性，它們只能在a列值相等的情況下這個小范圍內遞增，看上圖的左下角的結點可理解這點

劃重點：由于復合索引樹建的時候就是按照當初你建立索引時（index_abc (a, b, c)）對應索引列的順序從左到右來建的，因此你使用的時候你也得按照從左到右的規則來用，這就是索引的 左匹配原則

所以為什么上面 where a > 1000 and b > 1000 和 where a > 1000 and c > 1000 條件查詢的type是range，而 where b > 1000 and c > 1000 的type是index 你明白來嗎？

回表，聚集索引

我們都知道，B+樹有個特點就是，其葉子結點存的是關鍵字和資料，非葉子結點存的都是索引關鍵字，那么復合索引構造的B+樹中，其葉子結點存的資料是什么呢？答案該條資料的主鍵值

劃重點：也就是說，利用復合索引查找資料的流程是，先在復合索引的B+樹上找到對應資料的主鍵值（ID，注：MyISAM的索引葉子節點存盤記錄指標）,然后再根據這個主鍵（ID）值，到主鍵索引樹（B+樹）上查找這個ID所在的行記錄（主鍵索引樹的頁子結點存盤的關鍵字和對應的行記錄資料），最后查找結束，這個查找流程操作也叫 回表查詢

有沒有注意到，B+樹中，有的葉子結點存盤的行記錄，有點存盤的是主鍵值

劃重點：

• 葉子結點存盤行記錄的索引又叫 聚集索引，InnoDB必須要有，且只有一個聚集索引：
  • 如果定義了主鍵，則主鍵索引就是 聚集索引
  • 如果沒有定義主鍵，則第一個not NULL unique列是聚集索引
  • 否則，InnoDB會創建一個隱藏的row-id作為聚集索引
• 葉子結點存盤主鍵值叫普通索引，也叫 非聚集索引

覆寫索引

還是上面的例子，我們再次看一下 where條件為 b > 1000 and c > 1000 的查詢 explain后的資訊

mysql> explain select * from test where b > 1000 and c > 1000;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+-------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows  | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+-------+----------+--------------------------+
|  1 | SIMPLE      | test  | NULL       | index | NULL          | index_abc | 12      | NULL | 10120 |    11.11 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+-------+----------+--------------------------+

按照我們剛剛講的索引的 左匹配原則，這個查詢應該沒有有效用上我們建的索引 index_abc ，為什么key（實際使用到的索引）列卻是 index_abc？這里就涉及到了 覆寫索引

什么是覆寫索引？覆寫索引 就是：SQL只需要通過索引就可以回傳查詢所需要的資料，而不必通過二級索引查到主鍵之后再去查詢資料（即回表查詢）

不難理解，因為我們的test表本來就只有四個欄位，id, a, b, c，其中（a, b, c）建立列索引，id又是主鍵，復合索引樹的葉子結點存的就是主鍵值，所以 select * from test where b > 1000 and c > 1000 查找的資料通過復合索引樹就可以全部得到，不需要回表，因此這里面用到了索引，這個索引樹實際是什么索引的索引樹呢？，當然是index_abc了，因為b, c 列包含在復合索引列中

為什么possible_keys列（可能使用到的索引）為NULL，因為搜索引擎找不到以b列開頭的索引

所以，使用列索引覆寫，Extra列也就有列Using index

最后，為什么 a > 1000 and b > 1000 和 b > 1000 and a > 1000，explain的結果一樣呢？

mysql> explain select * from test where a > 1000 and b > 1000;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | test  | NULL       | range | index_abc     | index_abc | 4       | NULL | 5060 |    33.33 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+

mysql> explain select * from test where b > 1000 and a > 1000;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | test  | NULL       | range | index_abc     | index_abc | 4       | NULL | 5060 |    33.33 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+

這就該我們mysql 查詢優化器 干活了，mysql查詢優化器會判斷糾正這條sql陳述句該以什么樣的順序執行效率最高，最后才生成真正的執行計劃，

至此，索引的左匹配原則，聚集索引，回表查詢，覆寫索引就分享完了

如有不妥之處，歡迎指正，交流

點個贊再走唄～thxs~~~~

標籤：MySQL

上一篇：帶上資料表，分析一下拼團活動該如何設計

下一篇：5. 索引與演算法—B+樹的操作、輔助索引與聚集索引、Cardinality、聯合索引、覆寫索引、MRR/ICP、哈希演算法、全文索引