【51CTO.com原創(chuàng)稿件】小李今天剛上班就收到客戶的反饋，說(shuō)查詢(xún)用戶信息會(huì)非常的慢，有時(shí)甚至?xí)霈F(xiàn)超時(shí)的現(xiàn)象。

[[349161]]

圖片來(lái)自 Pexels 

小李這就納悶了分明已經(jīng)給表加上了索引為什么還這么慢呢。小李分析了好久都沒(méi)分析出原因，于是只能找到同部門(mén)的掃地僧大林子。

大林子一邊聽(tīng)著小李的描述一邊看著項(xiàng)目，就在小李剛把問(wèn)題描述完大林子就對(duì)小李說(shuō)：“問(wèn)題解決了”，小李震驚不已，問(wèn)道：“這么 6，是什么原因?qū)е碌哪?分明我已經(jīng)加了索引了啊?”

大林子說(shuō)：“這是很多開(kāi)發(fā)人員很容易忽視的問(wèn)題......”聽(tīng)完大林子的講解小李瞬間茅塞頓開(kāi)。那么具體什么原因呢，下面我就給大家講解一下。

原因講解

首先，我們來(lái)創(chuàng)建一個(gè)存儲(chǔ)引擎為 InnoDB 的 User 表，這個(gè)表包含三個(gè)字段分別是 id，name 和 age。

其中 id 為主鍵 name 上添加了一個(gè)普通索引名字叫 n，接著我們像這條表中插入 10 億條數(shù)據(jù)。

表和數(shù)據(jù)都創(chuàng)建完了，下面我們就來(lái)說(shuō)說(shuō)為什么加上了索引還是查詢(xún)很慢，以及解決方案。

MySQL 會(huì)根據(jù)語(yǔ)句的執(zhí)行時(shí)間來(lái)判斷 SQL 語(yǔ)句是否是慢查詢(xún)語(yǔ)句。

當(dāng)一個(gè) SQL 語(yǔ)句在執(zhí)行時(shí)，MySQL 把語(yǔ)句執(zhí)行時(shí)間和系統(tǒng)參數(shù) long_query_time(這個(gè)參數(shù)的默認(rèn)值是 10 秒，但是在實(shí)際項(xiàng)目中我們會(huì)將這個(gè)參數(shù)值設(shè)置為1秒甚至更短的時(shí)間) 作比較。

如果執(zhí)行時(shí)間大于這個(gè)參數(shù)的值，那么就把這個(gè)語(yǔ)句記錄到慢查詢(xún)?nèi)罩局小Ｄ敲丛谡Z(yǔ)句執(zhí)行過(guò)程中我們?nèi)绾蔚弥欠袷褂昧怂饕?

這時(shí)我們就可以使用 explain 語(yǔ)句來(lái)查看數(shù)據(jù)結(jié)果中 Key 的值是否 null ，如果是 null 則說(shuō)明沒(méi)有使用索引。

下面我們來(lái)看一個(gè)例子：

explain select * from user; 
explain select * from user where id=1; 
explain select name from user; 

上面三個(gè) explain 語(yǔ)句返回的 key 如下表所示：

從上表我們可以看出第一個(gè)語(yǔ)句沒(méi)有使用索引，第二個(gè)使用了主鍵索引，第三個(gè)語(yǔ)句使用 n 這個(gè)索引。

我們的 user 表有 10 億條數(shù)據(jù)，可想而知第一條查詢(xún)語(yǔ)句執(zhí)行效率肯定低，第二個(gè)查詢(xún)語(yǔ)句看似執(zhí)行效率高，其實(shí)在極端環(huán)境下(比如 CPU 高負(fù)載)也會(huì)出現(xiàn)查詢(xún)效率低的問(wèn)題。

最后一個(gè)查詢(xún)語(yǔ)句呢雖然使用了 n 這個(gè)索引，但是它實(shí)際上執(zhí)行了掃描整個(gè)索引樹(shù)的操作，因此查詢(xún)效率也高不到哪去。

綜上所述，我們可知索引是否使用和是否被記錄到慢查詢(xún)中幾乎沒(méi)有聯(lián)系，索引只是 SQL 的一個(gè)執(zhí)行過(guò)程，SQL 的執(zhí)行時(shí)間才是決定是否被記錄到慢查詢(xún)中的關(guān)鍵。

前面一小節(jié)我們只是簡(jiǎn)單的分析了一下問(wèn)題，下面我們進(jìn)一步看這個(gè)問(wèn)題。我們知道 InnoDB 是索引組織表，所有數(shù)據(jù)都存儲(chǔ)在索引樹(shù)上。

在 InnoDB 中數(shù)據(jù)放在主鍵索引里，因此理論上來(lái)說(shuō)所有在 InnoDB 表中的查詢(xún)至少使用了一個(gè)索引。

比如這個(gè) SQL 查詢(xún)語(yǔ)句 select * from user where id > 1000，很明顯它使用主鍵索引，并且這個(gè)語(yǔ)句一定執(zhí)行了整個(gè)索引樹(shù)的掃描。

在 InnoDB 中只有一種情況叫不使用索引，就是從主鍵索引的最左邊的葉子結(jié)點(diǎn)開(kāi)始向右掃描整個(gè)索引樹(shù)。

到目前為止我們已經(jīng)知道了全索引掃描會(huì)造成查詢(xún)變慢，下面我們就來(lái)說(shuō)一下另一個(gè)知識(shí)點(diǎn)過(guò)濾性 。

假如我們要查詢(xún) user 表中 age 在 70 歲以上 80 歲以下的人員信息，你一定會(huì)在 age 字段上加入索引來(lái)避免全局掃描。

不錯(cuò)，這是個(gè)好的想法，但是當(dāng)你運(yùn)行查詢(xún)語(yǔ)句時(shí)就會(huì)發(fā)現(xiàn)它依然執(zhí)行的很慢，這是為什么呢?

要解答這個(gè)問(wèn)題我們先來(lái)看一下 SQL 查詢(xún)語(yǔ)句的執(zhí)行流程：

• 搜索 age 索引樹(shù)，獲取到第一個(gè) age 為 70 的記錄。
• 拿到主鍵值，根據(jù)主鍵值去主鍵索引樹(shù)上獲取對(duì)應(yīng)的信息，并將信息加入結(jié)果集。
• 在 age 索引樹(shù)上向右側(cè)掃描，獲取到下一個(gè)主鍵值，執(zhí)行第二部的操作。
• 不斷執(zhí)行上面的步驟，直到遇到第一個(gè) age 大于 80 的記錄為止。

從上面的步驟中我們可以看出雖然使用了索引，但是查詢(xún)過(guò)程中掃描了上萬(wàn)行甚至上億行。

因此我們可以得出結(jié)論：對(duì)于這種數(shù)據(jù)非常多的表，我們所要做的不僅僅是加入索引，還要保證索引的過(guò)濾性足夠優(yōu)秀。

假如說(shuō)我們把索引的過(guò)濾性也處理好了，是不是查詢(xún)時(shí)要掃描的行數(shù)就一定會(huì)表少呢?

這個(gè)答案是否定的，比如說(shuō)我們的 user 表中的 name 和 age 字段共同組成了聯(lián)合索引并處理好了過(guò)濾性，這時(shí)當(dāng)我們查詢(xún)姓李的并且年齡是 60 歲的數(shù)據(jù)時(shí)查詢(xún)效率依然很低。

我們先來(lái)看一下查詢(xún)語(yǔ)句的執(zhí)行流程：

• 首先從聯(lián)合索引上找到姓名字段是李字開(kāi)頭的數(shù)據(jù)記錄。
• 拿到主鍵值，根據(jù)主鍵值在主鍵索引書(shū)上去除匹配的數(shù)據(jù)。
• 接著根據(jù) age 字段去判斷年齡是否等于 60，如果符合就加入結(jié)果集。
• 然后再聯(lián)合所以上向右側(cè)遍歷，并不斷做回表和判斷，直到遇到 name 的第一個(gè)字不是李的為止。

Tip：所謂的回表就是根據(jù)主鍵值去主鍵索引樹(shù)上查找對(duì)應(yīng)的數(shù)據(jù)。

從上面的步驟中我們可以看出最耗時(shí)的就是回表，如果姓李的數(shù)據(jù)有 2 億條那么就要回表 2 億次，并且 SQL 在定位第一行數(shù)據(jù)時(shí)只能使用最左前綴原則。

這種耗時(shí)的回表操作步驟在 MySQL 5.6 及其以后的版本中已經(jīng)做了 index condition pushdown 優(yōu)化。

優(yōu)化后的流程很簡(jiǎn)單：

• 首先從聯(lián)合索引上找到姓名字段是李字開(kāi)頭的數(shù)據(jù)記錄，并判斷這個(gè)記錄里 age 是不是 60，如果是就執(zhí)行回表取出數(shù)據(jù)假如結(jié)果集。
• 重復(fù)步驟1，直到配當(dāng)?shù)谝粋€(gè)字不是李字的記錄為止。

優(yōu)化后和優(yōu)化前的區(qū)別是把 age 的對(duì)比步驟放在了遍歷聯(lián)合索引樹(shù)上，減少了回表次數(shù)。

但是雖然減少了回表次數(shù)，聯(lián)合索引樹(shù)的遍歷去沒(méi)有減少依然要遍歷 2 億次，那么有沒(méi)有更好的優(yōu)化方案呢?答案是有的，我們可以實(shí)虛擬列來(lái)進(jìn)行處理。

首先我們需要把 name 的第一個(gè)字和 age 做一個(gè)聯(lián)合索引，讓虛擬列的值總是等于 name 字段的前兩個(gè)字節(jié)，這里需要注意的是虛擬列不隨著 insert 和 update 變化，它的值是自定義生成的。

語(yǔ)句如下：

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

經(jīng)過(guò)上述的優(yōu)化后聯(lián)合索引樹(shù)的查詢(xún)次數(shù)也降低了，本質(zhì)上就是創(chuàng)建一個(gè)緊湊的索引加快查詢(xún)。

總結(jié)

這篇文章主要介紹了查詢(xún)優(yōu)化的基本思路，只要記住優(yōu)化查詢(xún)的過(guò)程都是減少掃描行數(shù)的過(guò)程，就可以做到在 SQL 查詢(xún)面前百戰(zhàn)百勝。

作者：朱鋼，筆名喵叔

簡(jiǎn)介：.NET 高級(jí)開(kāi)發(fā)人員，2019 年度博客之星 20 強(qiáng)，長(zhǎng)期從事電子政務(wù)系統(tǒng)和AI客服系統(tǒng)的設(shè)計(jì)與開(kāi)發(fā)，目前就職于國(guó)內(nèi)某 BIM 大廠從事招投標(biāo)軟件的開(kāi)發(fā)。

編輯：陶家龍

征稿：有投稿、尋求報(bào)道意向技術(shù)人請(qǐng)聯(lián)絡(luò) editor@51cto.com

【51CTO原創(chuàng)稿件，合作站點(diǎn)轉(zhuǎn)載請(qǐng)注明原文作者和出處為51CTO.com】