MySQL索引是如何形成的

■金瀾

MySQL查詢過程的瓶頸在于磁盤IO，那怎么降低磁盤IO次數(shù)呢？答案就是索引。

正確的使用索引，就能有效地把磁盤IO的次數(shù)降到常數(shù)級，這樣查詢速度將會變得非?？?。

無索引時，為什么查詢會很慢？

在磁盤中，MySQL存放數(shù)據(jù)的基本單位是數(shù)據(jù)頁，數(shù)據(jù)是放在數(shù)據(jù)頁中的，每個數(shù)據(jù)頁中都有很多的數(shù)據(jù)行。在數(shù)據(jù)頁的數(shù)據(jù)區(qū)中存放著很多數(shù)據(jù)行，這些數(shù)據(jù)行就對應(yīng)數(shù)據(jù)表中的一行行數(shù)據(jù)，它們都是通過單向鏈表方式連接組合起來的。而多個數(shù)據(jù)頁之間又是通過雙向鏈表的方式連接起來的。

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

如果要查詢一條數(shù)據(jù)，就要沿著雙向鏈表一個個去尋找。比如，要查詢主鍵為1的那條數(shù)據(jù)，可從數(shù)據(jù)頁1開始查詢。

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

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

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

MySQL的索引是如何形成的呢?

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

這件事，就要交給索引來處理了。

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

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

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

數(shù)據(jù)行前面的0、2、3表示記錄的類型，也就是數(shù)據(jù)行的類型，0表示普通類型，就是表中的一行普通數(shù)據(jù)，2表示最小記錄，3表示最大記錄，因為數(shù)據(jù)行對應(yīng)的主鍵都是有順序的。這里為了方便展示索引，假設(shè)每個數(shù)據(jù)頁中都有20條數(shù)據(jù)，當(dāng)我們建立索引之后，可以看到，索引頁中會記錄每個數(shù)據(jù)頁中最小的主鍵即ID的值，以及對應(yīng)的數(shù)據(jù)頁號，而索引頁就發(fā)揮了數(shù)據(jù)頁目錄的效果。

索引頁其實也是數(shù)據(jù)頁，只不過是我們拿來專門存放數(shù)據(jù)頁的目錄信息而已。索引頁中的記錄類型，除了2和3之外還有1，1表示的是目錄的類型，因為它是指向具體的某個數(shù)據(jù)頁。

而如果數(shù)據(jù)頁很多的話，一個索引頁中肯定就放不下，此時，MySQL會把超出索引頁的目錄信息放到新的索引頁中，然后向上再擴展出一個索引頁。這時數(shù)據(jù)頁3和數(shù)據(jù)頁4的目錄信息，被放到了索引頁2中，然后索引3作為擴展出來的索引頁，記錄索引頁1和索引頁2中的最小主鍵值以及索引頁號，也就是說索引頁3中記錄的信息，就相當(dāng)于更上一層索引的目錄信息了。

如果索引頁3中的容量也不夠了，這個時候，同樣會把超出索引頁3的信息，放到新的一個同層級的索引頁中，然后再向上擴展一層。在索引頁3中的信息放不下之后，就會放到索引頁4中，然后向上再拓展一層索引5，索引5中存放的就是索引3和索引4的目錄信息，規(guī)律都是一樣的。

索引頁逐層的往上擴展，看起來就像一棵樹一樣，這也就是我們經(jīng)常說的B+索引樹，3層索引一般就可以存放千萬級別的數(shù)據(jù)了。

為什么利用索引查詢就能變快呢？

有了索引之后，如果要查詢主鍵為1的那條數(shù)據(jù)，就可以從B+索引樹最上面的那個索引頁開始查詢。可以把索引頁5先加載到內(nèi)存，此時會發(fā)生一次磁盤IO，然后再通過二分法，根據(jù)主鍵值1，到索引頁5中快速的和各個目錄項中的最小主鍵值對比一下，然后找到下一個索引頁3，通過索引頁3又可以定位到下一個索引頁1。

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

這樣的方式不需要像無索引一樣全表掃描，挨個加載數(shù)據(jù)頁到內(nèi)存中，而是利用索引頁，通過高效的二分法查找，很快就可以定位到數(shù)據(jù)具體是在哪個數(shù)據(jù)頁中。

在這個過程中就算是上千萬級別的數(shù)據(jù)量，也可以做到只發(fā)生個位數(shù)磁盤IO，就可以查詢到數(shù)據(jù)，這也是為什么用了索引之后查詢的效率明顯提高的原因。

所以，SQL優(yōu)化，關(guān)鍵在于要想辦法讓SQL語句能利用索引查數(shù)據(jù)，這樣的話查詢的效率才會上來，但是有時會有很多因素，導(dǎo)致不能讓SQL語句使用索引，這也是SQL優(yōu)化的一個關(guān)鍵點。