問(wèn)題分析：為什么查詢(xún)變慢呢？

我們知道了MySQL查詢(xún)的全過(guò)程，也知道了整個(gè)過(guò)程的瓶頸在于磁盤(pán)IO，那怎么降低磁盤(pán)IO次數(shù)呢？

答案就是索引。

正確的使用索引，我們就能有效的把磁盤(pán)IO的次數(shù)降到常數(shù)級(jí)，這樣查詢(xún)速度將會(huì)變得非常快，接下來(lái)讓我們深入了解下MySQL的索引。

無(wú)索引時(shí)，為什么查詢(xún)會(huì)很慢呢？

在磁盤(pán)中，MYSQL存放數(shù)據(jù)的基本單位是數(shù)據(jù)頁(yè)，數(shù)據(jù)是放在數(shù)據(jù)頁(yè)中的，每個(gè)數(shù)據(jù)頁(yè)中都有很多的數(shù)據(jù)行，如下圖：

可以看到，在數(shù)據(jù)頁(yè)的數(shù)據(jù)區(qū)中存放著很多數(shù)據(jù)行，這些數(shù)據(jù)行就對(duì)應(yīng)數(shù)據(jù)表中的一行行數(shù)據(jù)，它們都是通過(guò)單向鏈表方式連接組合起來(lái)的。

而多個(gè)數(shù)據(jù)頁(yè)之間又是通過(guò)雙向鏈表的方式連接起來(lái)的，如下圖：

而一個(gè)數(shù)據(jù)頁(yè)的大小默認(rèn)為16KB，16KB的大小肯定是不可能放得下一整張表的數(shù)據(jù)的，所以MYSQL表中的數(shù)據(jù)，比如我們的訂單表中的訂單數(shù)據(jù)，會(huì)通過(guò)這樣雙向鏈表的結(jié)構(gòu)放在多個(gè)數(shù)據(jù)頁(yè)中。

如果我們要查詢(xún)一條數(shù)據(jù)的話，就要沿著雙向鏈表一個(gè)個(gè)去尋找。比如，我們要查詢(xún)主鍵為1的那條數(shù)據(jù)，我們可以從數(shù)據(jù)頁(yè)1開(kāi)始查詢(xún)。

首先，將數(shù)據(jù)頁(yè)1從磁盤(pán)中加載到MYSQL內(nèi)存中，如果發(fā)現(xiàn)數(shù)據(jù)頁(yè)1中沒(méi)有找到我們想要的那條數(shù)據(jù)，我們就要沿著雙向鏈表一直尋找下去。

最糟糕的情況，就是我們沿著數(shù)據(jù)頁(yè)1、數(shù)據(jù)頁(yè)2、一直到最后，我們?cè)谧詈笠粋€(gè)數(shù)據(jù)頁(yè)100中才找到我們想要的那條數(shù)據(jù)，但在這之前，我們得要把數(shù)據(jù)頁(yè)1一直到數(shù)據(jù)頁(yè)100，將這100個(gè)數(shù)據(jù)頁(yè)通過(guò)磁盤(pán)IO加載到內(nèi)存中，相當(dāng)于是全表掃描了。

就算MYSQL中有預(yù)讀機(jī)制存在，可能會(huì)預(yù)先發(fā)生幾次磁盤(pán)IO，提前加載一些數(shù)據(jù)頁(yè)到內(nèi)存中，但這100個(gè)數(shù)據(jù)頁(yè)同樣至少會(huì)導(dǎo)致幾十次磁盤(pán)IO了，而磁盤(pán)IO這個(gè)過(guò)程是很耗費(fèi)性能的。

MYSQL的索引是如何形成的呢？

那有沒(méi)有什么辦法，能夠讓我們盡量快的定位到數(shù)據(jù)頁(yè)，而不至于全表掃描呢？

這件事，就要交給索引來(lái)處理了。

順著數(shù)據(jù)頁(yè)的雙向鏈表數(shù)據(jù)結(jié)構(gòu)一個(gè)個(gè)去尋找，未免顯得太費(fèi)力了，我們可以為每個(gè)數(shù)據(jù)頁(yè)創(chuàng)建一個(gè)目錄，查詢(xún)數(shù)據(jù)時(shí)，先到目錄里看一樣有沒(méi)有自己想要的數(shù)據(jù)，這樣不就快很多了嗎。

首先，我們看下數(shù)據(jù)頁(yè)內(nèi)部的構(gòu)造：

比如，我們以數(shù)據(jù)頁(yè)1舉例，數(shù)據(jù)頁(yè)1中有很多的數(shù)據(jù)行，數(shù)據(jù)行之間都是用指針連接，并且以單向鏈表的方式組織起來(lái)的，并且單向鏈表中主鍵一定是保證有序的，無(wú)序的數(shù)據(jù)是沒(méi)法創(chuàng)建索引的。

可以看到，數(shù)據(jù)行前面的 0、2、3 表示記錄的類(lèi)型，也就是數(shù)據(jù)行的類(lèi)型，0表示普通類(lèi)型，就是表中的一行普通數(shù)據(jù)，2表示最小記錄，3表示最大記錄，因?yàn)槲覀兌贾罃?shù)據(jù)行對(duì)應(yīng)的主鍵都是有順序的。

這里為了方便展示索引，我們假設(shè)每個(gè)數(shù)據(jù)頁(yè)中都有20條數(shù)據(jù)，當(dāng)我們建立索引之后，如下圖：

可以看到，索引頁(yè)中會(huì)記錄每個(gè)數(shù)據(jù)頁(yè)中最小的主鍵即id的值，以及對(duì)應(yīng)的數(shù)據(jù)頁(yè)號(hào)，而索引頁(yè)就發(fā)揮了我們剛提到的數(shù)據(jù)頁(yè)目錄的效果。

索引頁(yè)其實(shí)也是數(shù)據(jù)頁(yè)，只不過(guò)是我們拿來(lái)專(zhuān)門(mén)存放數(shù)據(jù)頁(yè)的目錄信息而已，可以看到索引頁(yè)中的記錄類(lèi)型，除了2和3之外還有1，1表示的是目錄的類(lèi)型，因?yàn)樗侵赶蚓唧w的某個(gè)數(shù)據(jù)頁(yè)的。

而如果數(shù)據(jù)頁(yè)很多的話，一個(gè)索引頁(yè)中肯定就放不下這些目錄信息，此時(shí)，MYSQL會(huì)把超出索引頁(yè)的目錄信息放到新的索引頁(yè)中，然后向上再擴(kuò)展出一個(gè)索引頁(yè)，如下圖：

可以看到，數(shù)據(jù)頁(yè)3和數(shù)據(jù)頁(yè)4的目錄信息，被放到了索引頁(yè)2中，然后索引3作為擴(kuò)展出來(lái)的索引頁(yè)，記錄索引頁(yè)1和索引頁(yè)2中的最小主鍵值以及索引頁(yè)號(hào)，也就是說(shuō)索引頁(yè)3中記錄的信息，就相當(dāng)于更上一層索引的目錄信息了。

如果索引頁(yè)3中的容量也不夠了，這個(gè)時(shí)候，同樣會(huì)把超出索引頁(yè)3的信息，放到新的一個(gè)同層級(jí)的索引頁(yè)中，然后再向上擴(kuò)展一層，如下圖：

可以看到，在索引頁(yè)3中的信息放不下之后，就會(huì)放到索引頁(yè)4中，然后向上再拓展一層索引5，索引5中存放的就是索引3和索引4的目錄信息，規(guī)律都是一樣的。

而圖片中，我們可以看到，索引頁(yè)逐層的往上擴(kuò)展，看起來(lái)就像一棵樹(shù)一樣，這也就是我們經(jīng)常說(shuō)的B+索引樹(shù)，圖片中索引的高度為3層，一般就可以存放千萬(wàn)級(jí)別的數(shù)據(jù)了。

為什么利用索引查詢(xún)就能變快呢？

我們?cè)賮?lái)看下剛才這張圖：

有了索引之后，如果我們要查詢(xún)主鍵為1的那條數(shù)據(jù)，就可以從B+索引樹(shù)最上面的那個(gè)索引頁(yè)開(kāi)始查詢(xún)。

如圖，我們可以把索引頁(yè)5先加載到內(nèi)存，此時(shí)會(huì)發(fā)生一次磁盤(pán)IO，然后再通過(guò)二分法，根據(jù)主鍵值1，到索引頁(yè)5中快速的和各個(gè)目錄項(xiàng)中的最小主鍵值對(duì)比一下，然后找到下一個(gè)索引頁(yè)3，通過(guò)索引頁(yè)3又可以定位到下一個(gè)索引頁(yè)1。

在這顆B+索引樹(shù)中，通過(guò)二分法對(duì)比最小主鍵值的方式，最終在索引頁(yè)1中發(fā)現(xiàn)，原來(lái)主鍵值為1的那條數(shù)據(jù)，是位于數(shù)據(jù)頁(yè)1中，此時(shí)，我們就可以針對(duì)性的把數(shù)據(jù)頁(yè)1加載到內(nèi)存，然后在內(nèi)存中就可以查到主鍵為1的數(shù)據(jù)了。

通過(guò)這樣的方式可以發(fā)現(xiàn)，我們不需要像之前無(wú)索引一樣全表掃描，挨個(gè)加載數(shù)據(jù)頁(yè)到內(nèi)存中，而是利用索引頁(yè)，通過(guò)高效的二分法查找，很快就可以定位到數(shù)據(jù)具體是在哪個(gè)數(shù)據(jù)頁(yè)中。

并且這個(gè)過(guò)程中我們也發(fā)現(xiàn)，就算是上千萬(wàn)級(jí)別的數(shù)據(jù)量，我們也可以做到只發(fā)生個(gè)位數(shù)磁盤(pán)IO，就可以查詢(xún)到數(shù)據(jù)，這也是為什么用了索引之后查詢(xún)的效率明顯提高的原因。

所以，接下來(lái)的sql優(yōu)化，關(guān)鍵在于要想辦法讓sql語(yǔ)句能利用索引查數(shù)據(jù)，這樣的話查詢(xún)的效率才會(huì)上來(lái)，但是有時(shí)會(huì)有很多因素，導(dǎo)致不能正常讓sql語(yǔ)句使用索引，這也是接下來(lái)sql優(yōu)化的一個(gè)關(guān)鍵點(diǎn)。

結(jié)束語(yǔ)

好了，通過(guò)剛才的索引原理，我們知道千萬(wàn)級(jí)的數(shù)據(jù)量，B+樹(shù)基本也就是三到四層，那么如果正常使用到了索引，性能通常不是問(wèn)題，所以問(wèn)題原因基本可以確定是因?yàn)閟ql沒(méi)有使用到索引，也就是索引失效了。