經(jīng)常有同學(xué)問(wèn)我，我的一個(gè)SQL語(yǔ)句使用了索引，為什么還是會(huì)進(jìn)入到慢查詢之中呢？今天我們就從這個(gè)問(wèn)題開(kāi)始來(lái)聊一聊索引和慢查詢。

案例剖析

為了實(shí)驗(yàn)，我創(chuàng)建了如下表：

CREATE TABLE `T`(`id` int(11) NOT NULL,`a` int(11) DEFAUT NULL,PRIMARY KEY(`id`),KEY `a`(`a`)) ENGINE=InnoDB;

該表有三個(gè)字段，其中用id是主鍵索引，a是普通索引。

首先SQL判斷一個(gè)語(yǔ)句是不是慢查詢語(yǔ)句，用的是語(yǔ)句的執(zhí)行時(shí)間。他把語(yǔ)句執(zhí)行時(shí)間跟long_query_time這個(gè)系統(tǒng)參數(shù)作比較，如果語(yǔ)句執(zhí)行時(shí)間比它還大，就會(huì)把這個(gè)語(yǔ)句記錄到慢查詢?nèi)罩纠锩妫@個(gè)參數(shù)的默認(rèn)值是10秒。當(dāng)然在生產(chǎn)上，我們不會(huì)設(shè)置這么大，一般會(huì)設(shè)置1秒，對(duì)于一些比較敏感的業(yè)務(wù)，可能會(huì)設(shè)置一個(gè)比1秒還小的值。

語(yǔ)句執(zhí)行過(guò)程中有沒(méi)有用到表的索引，可以通過(guò)explain一個(gè)語(yǔ)句的輸出結(jié)果來(lái)看KEY的值不是NULL。

我們看下 explain select * from t;的KEY結(jié)果是NULL

（圖一）

explain select * from t where id=2;的KEY結(jié)果是PRIMARY，就是我們常說(shuō)的使用了主鍵索引

（圖二）

explain select a from t;的KEY結(jié)果是a，表示使用了a這個(gè)索引。

（圖三）

雖然后兩個(gè)查詢的KEY都不是NULL，但是最后一個(gè)實(shí)際上掃描了整個(gè)索引樹(shù)a。

假設(shè)這個(gè)表的數(shù)據(jù)量有100萬(wàn)行，圖二的語(yǔ)句還是可以執(zhí)行很快，但是圖三就肯定很慢了。如果是更極端的情況，比如，這個(gè)數(shù)據(jù)庫(kù)上CPU壓力非常的高，那么可能第2個(gè)語(yǔ)句的執(zhí)行時(shí)間也會(huì)超過(guò)long_query_time，會(huì)進(jìn)入到慢查詢?nèi)罩纠锩妗?/p>

所以我們可以得出一個(gè)結(jié)論：是否使用索引和是否進(jìn)入慢查詢之間并沒(méi)有必然的聯(lián)系。使用索引只是表示了一個(gè)SQL語(yǔ)句的執(zhí)行過(guò)程，而是否進(jìn)入到慢查詢是由它的執(zhí)行時(shí)間決定的，而這個(gè)執(zhí)行時(shí)間，可能會(huì)受各種外部因素的影響。換句話來(lái)說(shuō)，使用了索引你的語(yǔ)句可能依然會(huì)很慢。

全索引掃描的不足

那如果我們?cè)诟顚哟蔚目催@個(gè)問(wèn)題，其實(shí)他還潛藏了一個(gè)問(wèn)題需要澄清，就是什么叫做使用了索引。

我們都知道，InnoDB是索引組織表，所有的數(shù)據(jù)都是存儲(chǔ)在索引樹(shù)上面的。比如上面的表t，這個(gè)表包含了兩個(gè)索引，一個(gè)主鍵索引和一個(gè)普通索引。在InnoDB里，數(shù)據(jù)是放在主鍵索引里的。如圖所示：

可以看到數(shù)據(jù)都放在主鍵索引上，如果從邏輯上說(shuō)，所有的InnoDB表上的查詢，都至少用了一個(gè)索引，所以現(xiàn)在我問(wèn)你一個(gè)問(wèn)題，如果你執(zhí)行select from t where id>0，你覺(jué)得這個(gè)語(yǔ)句有用上索引嗎？

我們看上面這個(gè)語(yǔ)句的explain的輸出結(jié)果顯示的是PRIMARY。其實(shí)從數(shù)據(jù)上你是知道的，這個(gè)語(yǔ)句一定是做了全面掃描。但是優(yōu)化器認(rèn)為，這個(gè)語(yǔ)句的執(zhí)行過(guò)程中，需要根據(jù)主鍵索引，定位到第1個(gè)滿足ID>0的值，也算用到了索引。

所以即使explain的結(jié)果里寫(xiě)的KEY不是NULL，實(shí)際上也可能是全表掃描的，因此InnoDB里面只有一種情況叫做沒(méi)有使用索引，那就是從主鍵索引的最左邊的葉節(jié)點(diǎn)開(kāi)始，向右掃描整個(gè)索引樹(shù)。

也就是說(shuō)，沒(méi)有使用索引并不是一個(gè)準(zhǔn)確的描述。

你可以用全表掃描來(lái)表示一個(gè)查詢遍歷了整個(gè)主鍵索引樹(shù)；
也可以用全索引掃描，來(lái)說(shuō)明像select a from t;這樣的查詢，他掃描了整個(gè)普通索引樹(shù)；
而select * from t where id=2這樣的語(yǔ)句，才是我們平時(shí)說(shuō)的使用了索引。他表示的意思是，我們使用了索引的快速搜索功能，并且有效的減少了掃描行數(shù)。

索引的過(guò)濾性要足夠好

根據(jù)以上解剖，我們知道全索引掃描會(huì)讓查詢變慢，接下來(lái)就要來(lái)談?wù)勊饕倪^(guò)濾性。

假設(shè)你現(xiàn)在維護(hù)了一個(gè)表，這個(gè)表記錄了中國(guó)14億人的基本信息，現(xiàn)在要查出所有年齡在10~15歲之間的姓名和基本信息，那么你的語(yǔ)句會(huì)這么寫(xiě)，select * from t_people where age between 10 and 15。

你一看這個(gè)語(yǔ)句一定要在age字段上開(kāi)始建立索引了，否則就是個(gè)全面掃描，但是你會(huì)發(fā)現(xiàn)，在你建立索引以后，這個(gè)語(yǔ)句還是執(zhí)行慢，因?yàn)闈M足這個(gè)條件的數(shù)據(jù)可能有超過(guò)1億行。

我們來(lái)看看建立索引以后，這個(gè)表的組織結(jié)構(gòu)圖：

這個(gè)語(yǔ)句的執(zhí)行流程是這樣的:

從索引上用樹(shù)搜索，取到第1個(gè)age等于10的記錄，得到它的主鍵id的值，根據(jù)id的值去主鍵索引取整行的信息，作為結(jié)果集的一部分返回；
在索引age上向右掃描，取下一個(gè)id的值，到主鍵索引上取整行信息，作為結(jié)果集的一部分返回；
重復(fù)上面的步驟，直到碰到第1個(gè)age大于15的記錄；

你看這個(gè)語(yǔ)句，雖然他用了索引，但是他掃描超過(guò)了1億行。所以你現(xiàn)在知道了，當(dāng)我們?cè)谟懻撚袥](méi)有使用索引的時(shí)候，其實(shí)我們關(guān)心的是掃描行數(shù)。

對(duì)于一個(gè)大表，不止要有索引，索引的過(guò)濾性還要足夠好。

像剛才這個(gè)例子的age，它的過(guò)濾性就不夠好，在設(shè)計(jì)表結(jié)構(gòu)的時(shí)候，我們要讓所有的過(guò)濾性足夠好，也就是區(qū)分度足夠高。

回表的代價(jià)

那么過(guò)濾性好了，是不是表示查詢的掃描行數(shù)就一定少呢？

我們?cè)賮?lái)看一個(gè)例子：

如果你的執(zhí)行語(yǔ)句是 select * from t_people where name='張三' and age=8

t_people表上有一個(gè)索引是姓名和年齡的聯(lián)合索引，那這個(gè)聯(lián)合索引的過(guò)濾性應(yīng)該不錯(cuò)，可以在聯(lián)合索引上快速找到第1個(gè)姓名是張三，并且年齡是8的小朋友，當(dāng)然這樣的小朋友應(yīng)該不多，因此向右掃描的行數(shù)很少，查詢效率就很高。

但是查詢的過(guò)濾性和索引的過(guò)濾性可不一定是一樣的，如果現(xiàn)在你的需求是查出所有名字的第1個(gè)字是張，并且年齡是8歲的所有小朋友，你的語(yǔ)句會(huì)怎么寫(xiě)呢？

你的語(yǔ)句要怎么寫(xiě)？很顯然你會(huì)這么寫(xiě)：select * from t_people where name like '張%' and age=8;

在MySQL5.5和之前的版本中，這個(gè)語(yǔ)句的執(zhí)行流程是這樣的:

首先從聯(lián)合索引上找到第1個(gè)年齡字段是張開(kāi)頭的記錄，取出主鍵id，然后到主鍵索引樹(shù)上，根據(jù)id取出整行的值；
判斷年齡字段是否等于8，如果是就作為結(jié)果集的一行返回，如果不是就丟棄。
在聯(lián)合索引上向右遍歷，并重復(fù)做回表和判斷的邏輯，直到碰到聯(lián)合索引樹(shù)上名字的第1個(gè)字不是張的記錄為止。

我們把根據(jù)id到主鍵索引上查找整行數(shù)據(jù)這個(gè)動(dòng)作，稱為回表。你可以看到這個(gè)執(zhí)行過(guò)程里面，最耗費(fèi)時(shí)間的步驟就是回表，假設(shè)全國(guó)名字第1個(gè)字是張的人有8000萬(wàn)，那么這個(gè)過(guò)程就要回表8000萬(wàn)次，在定位第一行記錄的時(shí)候，只能使用索引和聯(lián)合索引的最左前綴，最稱為最左前綴原則。

你可以看到這個(gè)執(zhí)行過(guò)程，它的回表次數(shù)特別多，性能不夠好，有沒(méi)有優(yōu)化的方法呢？

在MySQL5.6版本，引入了index condition pushdown的優(yōu)化。我們來(lái)看看這個(gè)優(yōu)化的執(zhí)行流程：

首先從聯(lián)合索引樹(shù)上，找到第1個(gè)年齡字段是張開(kāi)頭的記錄，判斷這個(gè)索引記錄里面，年齡的值是不是8，如果是就回表，取出整行數(shù)據(jù)，作為結(jié)果集的一部分返回，如果不是就丟棄；
在聯(lián)合索引樹(shù)上，向右遍歷，并判斷年齡字段后，根據(jù)需要做回表，直到碰到聯(lián)合索引樹(shù)上名字的第1個(gè)字不是張的記錄為止；

這個(gè)過(guò)程跟上面的差別，是在遍歷聯(lián)合索引的過(guò)程中，將年齡等于8的條件下推到所有遍歷的過(guò)程中，減少了回表的次數(shù)，假設(shè)全國(guó)名字第1個(gè)字是張的人里面，有100萬(wàn)個(gè)是8歲的小朋友，那么這個(gè)查詢過(guò)程中在聯(lián)合索引里要遍歷8000萬(wàn)次，而回表只需要100萬(wàn)次。

虛擬列

可以看到這個(gè)優(yōu)化的效果還是很不錯(cuò)的，但是這個(gè)優(yōu)化還是沒(méi)有繞開(kāi)最左前綴原則的限制，因此在聯(lián)合索引你還是要掃描8000萬(wàn)行，那有沒(méi)有更進(jìn)一步的優(yōu)化方法呢？

我們可以考慮把名字的第一個(gè)字和age來(lái)做一個(gè)聯(lián)合索引。這里可以使用MySQL5.7引入的虛擬列來(lái)實(shí)現(xiàn)。對(duì)應(yīng)的修改表結(jié)構(gòu)的SQL語(yǔ)句:

alter table t_people add name_first varchar(2) generated (left(name,1)),add index(name_first,age);

我們來(lái)看這個(gè)SQL語(yǔ)句的執(zhí)行效果:

CREATE TABLE `t_people`(`id` int(11) DEFAULT NULL,`name` varchar(20) DEFAUT NULL,`name_first` varchar(2) GENERATED ALWAYS AS (left(`name`,1)) VIRTUAL,KEY `name_first`(`name_first`,'age')) ENGINE=InnoDB DEFAULT CHARSET=utf8;

首先他在people上創(chuàng)建一個(gè)字段叫name_first的虛擬列，然后給name_first和age上創(chuàng)建一個(gè)聯(lián)合索引，并且，讓這個(gè)虛擬列的值總是等于name字段的前兩個(gè)字節(jié)，虛擬列在插入數(shù)據(jù)的時(shí)候不能指定值，在更新的時(shí)候也不能主動(dòng)修改，它的值會(huì)根據(jù)定義自動(dòng)生成，在name字段修改的時(shí)候也會(huì)自動(dòng)修改。

有了這個(gè)新的聯(lián)合索引，我們?cè)谡颐值牡?個(gè)字是張，并且年齡為8的小朋友的時(shí)候，這個(gè)SQL語(yǔ)句就可以這么寫(xiě)：select * from t_people where name_first='張' and age=8。

這樣這個(gè)語(yǔ)句的執(zhí)行過(guò)程，就只需要掃描聯(lián)合索引的100萬(wàn)行，并回表100萬(wàn)次，這個(gè)優(yōu)化的本質(zhì)是我們創(chuàng)建了一個(gè)更緊湊的索引，來(lái)加速了查詢的過(guò)程。