實例詳解MySQL索引最左匹配原則
- WBOY轉載
- 2022-09-06 17:32:462777瀏覽
推薦學習：mysql影片教學
#準備
為了方面後續的說明，我們先建立一個如下的表格（MySQL5.7），表格中共有5個欄位（a、b、c、d、e# ），其中a為主鍵，有一個由b，c，d#組成的聯合索引，儲存引擎為InnoDB，插入三個測試資料。 強烈建議自己在MySQL中嘗試本文的所有語句。
CREATE TABLE `test` ( `a` int NOT NULL AUTO_INCREMENT, `b` int DEFAULT NULL, `c` int DEFAULT NULL, `d` int DEFAULT NULL, `e` int DEFAULT NULL, PRIMARY KEY(`a`), KEY `idx_abc` (`b`,`c`,`d`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; INSERT INTO test(`a`, `b`, `c`, `d`, `e`) VALUES (1, 2, 3, 4, 5); INSERT INTO test(`a`, `b`, `c`, `d`, `e`) VALUES (2, 2, 3, 4, 5); INSERT INTO test(`a`, `b`, `c`, `d`, `e`) VALUES (3, 2, 3, 4, 5);
這時候，我們如果執行下面這個SQL語句，你覺得會走索引嗎？
SELECT b, c, d FROM test WHERE d = 2;
如果你按照最左匹配原則（簡述為在聯合索引中，從最左邊的字段開始匹配，若條件中字段在聯合索引中符合從左到右的順序則走索引，否則不走，可以簡單理解為(a, b, c)的聯合索引相當於創建了a索引、(a, b)索引和(a, b, c)索引），這句話顯然是不符合這個規則的，它走不了索引，但是我們用EXPLAIN語句分析，會發現一個很有趣的現象，它的輸出如下是使用了索引的。
這就很奇怪了，最左邊匹配原則失效了嗎？事實上，並沒有，我們一步一步來分析。
理論詳解
由於現在基本上以InnoDB引擎為主，我們以InnoDB為例進行主要說明。
聚集索引和非聚集索引
MySQL底層使用B 樹來儲存索引，資料均存在葉子節點上。對於InnoDB而言，主鍵索引和行記錄時儲存在一起的，因此稱為聚集索引（clustered index）。除了聚集索引，其他都稱為非聚集索引（secondary index），包括普通索引、唯一索引等。
在InnoDB中，只存在一個聚集索引：
- 若表存在主鍵，則主鍵索引就是聚集索引；
- 若表不存在主鍵，則會把第一個非空的唯一索引當作聚集索引；
- 否則，會隱式定義一個rowid作為聚集索引。
我們以下圖為例，假設現在有一個表，存在id、name、age三個字段，其中id為主鍵，因此id為聚集索引，name建立索引為非聚集索引。關於id和name的索引，有如下的B 樹，可以看到，聚集索引的葉子節點儲存的是主鍵和行記錄，非聚集索引的葉子節點儲存的是主鍵。
回表查詢
從上面的索引儲存結構來看，我們可以看到，在主鍵索引樹上，透過主鍵就可以一次性找出我們所需要的數據，速度很快。這很直觀，因為主鍵就和行記錄儲存在一起，定位到了主鍵就定位到了所要找的包含所有欄位的記錄。
但是對於非聚集索引，如上面的右圖，我們可以看到，需要先根據name所在的索引樹找到對應主鍵，然後透過主鍵索引樹查詢到所要的記錄，這個過程叫做回表查詢。
索引覆蓋
上面的回表查詢無疑會降低查詢的效率，那麼有沒有辦法讓它不回表呢？這就是索引覆蓋。所謂索引覆蓋，就是說，在使用這個索引查詢時，使它的索引樹的葉子節點上的資料可以覆蓋你查詢的所有字段，就可以避免回表了。我們回到一開始的例子，我們建立的(b,c,d)的聯合索引，因此當我們查詢的欄位在b、c、d中的時候，就不會回表，只需要查看一次索引樹，這就是索引覆蓋。
最左邊匹配原則
指的是聯合索引中，優先走最左邊列的索引。對於多個欄位的聯合索引，也同理。如 index(a,b,c) 聯合索引，則相當於創建了 a 單列索引，(a,b)聯合索引，和(a,b,c)聯合索引。
我們可以執行下面的幾個語句來驗證一下這個原則。
EXPLAIN SELECT * FROM test WHERE b = 1;
EXPLAIN SELECT * FROM test WHERE b = 1 and c = 2;
EXPLAIN SELECT * FROM test WHERE b = 1 and c = 2 and d = 3;
接着，我们尝试一条不符合最左原则的查询，它也如图预期一样，走了全表扫描。
EXPLAIN SELECT * FROM test WHERE d = 3;
详细规则
我们先来看下面两个语句，他们的输出如下。
EXPLAIN SELECT b, c from test WHERE b = 1 and c = 1; EXPLAIN SELECT b, d from test WHERE d = 1;
id|select_type|table|partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra | --+-----------+-----+----------+----+-------------+-------+-------+-----------+----+--------+-----------+ 1|SIMPLE |test | |ref |idx_bcd |idx_bcd|10 |const,const| 1| 100.0|Using index| i d|select_type|table|partitions|type |possible_keys|key |key_len|ref|rows|filtered|Extra | --+-----------+-----+----------+-----+-------------+-------+-------+---+----+--------+------------------------+ 1|SIMPLE |test | |index|idx_bcd |idx_bcd|15 | | 3| 33.33|Using where; Using index|
显然第一条语句是符合最左匹配的，因此type为ref，但是第二条并不符合最左匹配，但是也不是全表扫描，这是因为此时这表示扫描整个索引树。
具体来看，index 代表的是会对整个索引树进行扫描，如例子中的，列 d，就会导致扫描整个索引树。ref 代表 mysql 会根据特定的算法查找索引，这样的效率比 index 全扫描要高一些。但是，它对索引结构有一定的要求，索引字段必须是有序的。而联合索引就符合这样的要求，联合索引内部就是有序的，你可以理解为order by b,c,d这种排序规则，先根据字段b排序，再根据字段c排序，以此类推。这也解释了，为什么需要遵守最左匹配原则，当最左列有序才能保证右边的索引列有序。
因此，我们总结最后的原则为，若符合最左覆盖原则，则走ref这种索引；若不符合最左匹配原则，但是符合覆盖索引（index），就可以扫描整个索引树，从而找到覆盖索引对应的列，避免回表；若不符合最左匹配原则，也不符合覆盖索引（如本例的select *），则需要扫描整个索引树，并且回表查询行记录，此时，查询优化器认为这样两次查找索引树，还不如全表扫描来得快（因为联合索引此时不符合最左匹配原则，要不普通索引查询慢得多），因此，此时会走全表扫描。
补充：为什么要使用联合索引
减少开销。建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！
覆盖索引。对联合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。
效率高。索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W10%=100w条数据，然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出1000w10% 10% *10%=1w，效率提升可想而知！
推荐学习：mysql视频教程
以上是實例詳解MySQL索引最左匹配原則的詳細內容。更多資訊請關注PHP中文網其他相關文章！