簡(jiǎn)介

這篇文章的初衷是很多文章都告訴你最左匹配原則，卻沒有告訴你，實(shí)際場(chǎng)景下它到底是如何工作的，本文就是為了闡述清這個(gè)問題。

準(zhǔn)備

為了方面后續(xù)的說明，我們首先建立一個(gè)如下的表（MySQL5.7），表中共有5個(gè)字段（a、b、c、d、e），其中a為主鍵，有一個(gè)由b，c，d組成的聯(lián)合索引，存儲(chǔ)引擎為InnoDB，插入三條測(cè)試數(shù)據(jù)。強(qiáng)烈建議自己在MySQL中嘗試本文的所有語(yǔ)句。

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);

這時(shí)候，我們?nèi)绻麍?zhí)行下面這個(gè)SQL語(yǔ)句，你覺得會(huì)走索引嗎？

SELECT b, c, d FROM test WHERE d = 2;

如果你按照最左匹配原則（簡(jiǎn)述為在聯(lián)合索引中，從最左邊的字段開始匹配，若條件中字段在聯(lián)合索引中符合從左到右的順序則走索引，否則不走，可以簡(jiǎn)單理解為(a, b, c)的聯(lián)合索引相當(dāng)于創(chuàng)建了a索引、(a, b)索引和(a, b, c)索引），這句顯然是不符合這個(gè)規(guī)則的，它走不了索引，但是我們用EXPLAIN語(yǔ)句分析，會(huì)發(fā)現(xiàn)一個(gè)很有趣的現(xiàn)象，它的輸出如下是使用了索引的。

這就很奇怪了，最左匹配原則失效了嗎？事實(shí)上，并沒有，我們一步步來(lái)分析。

理論詳解

由于現(xiàn)在基本上以InnoDB引擎為主，我們以InnoDB為例進(jìn)行主要說明。

聚集索引和非聚集索引

MySQL底層使用B+樹來(lái)存儲(chǔ)索引，數(shù)據(jù)均存在葉子節(jié)點(diǎn)上。對(duì)于InnoDB而言，主鍵索引和行記錄時(shí)存儲(chǔ)在一起的，因此叫做聚集索引（clustered index）。除了聚集索引，其他所有都叫做非聚集索引（secondary index），包括普通索引、唯一索引等。

在InnoDB中，只存在一個(gè)聚集索引：

• 若表存在主鍵，則主鍵索引就是聚集索引；
• 若表不存在主鍵，則會(huì)把第一個(gè)非空的唯一索引作為聚集索引；
• 否則，會(huì)隱式定義一個(gè)rowid作為聚集索引。

我們以下圖為例，假設(shè)現(xiàn)在有一個(gè)表，存在id、name、age三個(gè)字段，其中id為主鍵，因此id為聚集索引，name建立索引為非聚集索引。關(guān)于id和name的索引，有如下的B+樹，可以看到，聚集索引的葉子節(jié)點(diǎn)存儲(chǔ)的是主鍵和行記錄，非聚集索引的葉子節(jié)點(diǎn)存儲(chǔ)的是主鍵。

回表查詢

從上面的索引存儲(chǔ)結(jié)構(gòu)來(lái)看，我們可以看到，在主鍵索引樹上，通過主鍵就可以一次性查出我們所需要的數(shù)據(jù)，速度很快。這很直觀，因?yàn)橹麈I就和行記錄存儲(chǔ)在一起，定位到了主鍵就定位到了所要找的包含所有字段的記錄。

但是對(duì)于非聚集索引，如上面的右圖，我們可以看到，需要先根據(jù)name所在的索引樹找到對(duì)應(yīng)主鍵，然后通過主鍵索引樹查詢到所要的記錄，這個(gè)過程叫做回表查詢。

索引覆蓋

上面的回表查詢無(wú)疑會(huì)降低查詢的效率，那么有沒有辦法讓它不回表呢？這就是索引覆蓋。所謂索引覆蓋，就是說，在使用這個(gè)索引查詢時(shí)，使它的索引樹的葉子節(jié)點(diǎn)上的數(shù)據(jù)可以覆蓋你查詢的所有字段，就可以避免回表了。我們回到一開始的例子，我們建立的(b,c,d)的聯(lián)合索引，因此當(dāng)我們查詢的字段在b、c、d中的時(shí)候，就不會(huì)回表，只需要查看一次索引樹，這就是索引覆蓋。

最左匹配原則

指的是聯(lián)合索引中，優(yōu)先走最左邊列的索引。對(duì)于多個(gè)字段的聯(lián)合索引，也同理。如 index(a,b,c) 聯(lián)合索引，則相當(dāng)于創(chuàng)建了 a 單列索引，(a,b)聯(lián)合索引，和(a,b,c)聯(lián)合索引。

我們可以執(zhí)行下面的幾條語(yǔ)句驗(yàn)證一下這個(gè)原則。

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;

接著，我們嘗試一條不符合最左原則的查詢，它也如圖預(yù)期一樣，走了全表掃描。

EXPLAIN SELECT * FROM test WHERE d = 3;

詳細(xì)規(guī)則

我們先來(lái)看下面兩個(gè)語(yǔ)句，他們的輸出如下。

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|

顯然第一條語(yǔ)句是符合最左匹配的，因此type為ref，但是第二條并不符合最左匹配，但是也不是全表掃描，這是因?yàn)榇藭r(shí)這表示掃描整個(gè)索引樹。

具體來(lái)看，index 代表的是會(huì)對(duì)整個(gè)索引樹進(jìn)行掃描，如例子中的，列 d，就會(huì)導(dǎo)致掃描整個(gè)索引樹。ref 代表 mysql 會(huì)根據(jù)特定的算法查找索引，這樣的效率比 index 全掃描要高一些。但是，它對(duì)索引結(jié)構(gòu)有一定的要求，索引字段必須是有序的。而聯(lián)合索引就符合這樣的要求，聯(lián)合索引內(nèi)部就是有序的，你可以理解為order by b,c,d這種排序規(guī)則，先根據(jù)字段b排序，再根據(jù)字段c排序，以此類推。這也解釋了，為什么需要遵守最左匹配原則，當(dāng)最左列有序才能保證右邊的索引列有序。

因此，我們總結(jié)最后的原則為，若符合最左覆蓋原則，則走ref這種索引；若不符合最左匹配原則，但是符合覆蓋索引（index），就可以掃描整個(gè)索引樹，從而找到覆蓋索引對(duì)應(yīng)的列，避免回表；若不符合最左匹配原則，也不符合覆蓋索引（如本例的select *），則需要掃描整個(gè)索引樹，并且回表查詢行記錄，此時(shí)，查詢優(yōu)化器認(rèn)為這樣兩次查找索引樹，還不如全表掃描來(lái)得快（因?yàn)槁?lián)合索引此時(shí)不符合最左匹配原則，要不普通索引查詢慢得多），因此，此時(shí)會(huì)走全表掃描。

補(bǔ)充：為什么要使用聯(lián)合索引

減少開銷。建一個(gè)聯(lián)合索引(col1,col2,col3)，實(shí)際相當(dāng)于建了(col1),(col1,col2),(col1,col2,col3)三個(gè)索引。每多一個(gè)索引，都會(huì)增加寫操作的開銷和磁盤空間的開銷。對(duì)于大量數(shù)據(jù)的表，使用聯(lián)合索引會(huì)大大的減少開銷！

覆蓋索引。對(duì)聯(lián)合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通過遍歷索引取得數(shù)據(jù)，而無(wú)需回表，這減少了很多的隨機(jī)io操作。減少io操作，特別的隨機(jī)io其實(shí)是dba主要的優(yōu)化策略。所以，在真正的實(shí)際應(yīng)用中，覆蓋索引是主要的提升性能的優(yōu)化手段之一。

效率高。索引列越多，通過索引篩選出的數(shù)據(jù)越少。有1000W條數(shù)據(jù)的表，有如下sql:select from table where col1=1 and col2=2 and col3=3,假設(shè)假設(shè)每個(gè)條件可以篩選出10%的數(shù)據(jù)，如果只有單值索引，那么通過該索引能篩選出1000W10%=100w條數(shù)據(jù)，然后再回表從100w條數(shù)據(jù)中找到符合col2=2 and col3= 3的數(shù)據(jù)，然后再排序，再分頁(yè)；如果是聯(lián)合索引，通過索引篩選出1000w10% 10% *10%=1w，效率提升可想而知！

總結(jié)

到此這篇關(guān)于MySQL索引最左匹配原則的文章就介紹到這了,更多相關(guān)MySQL索引最左匹配原則內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論