MySQL索引底層實現

標籤：存在   組織   div   基本   資料庫   效率   二叉樹   https   有序   

索引的本質

MySQL官方對於索引的定義為：索引是協助MySQL高效擷取資料的資料結構。即可以理解為：索引是資料結構。

 

我們知道，資料庫查詢是資料庫最主要的功能之一，我們都希望查詢資料的速度儘可能的快，因此資料庫系統的設計者會從查詢演算法的角度進行最佳化。最基本的查詢演算法當然是順序尋找，當然這種時間複雜度為O(n)的演算法在資料量很大時顯然是糟糕的，於是有了二分尋找、二叉樹尋找等。但是二分尋找要求被檢索資料有序，而二叉樹尋找只能應用於二叉尋找樹，但是資料本身的組織圖不可能完全滿足各種資料結構。所以，在資料之外，資料庫系統還維護者滿足特定尋找演算法的資料結構，這些資料結構以某種方式引用資料，這樣就可以在這些資料結構上實現進階尋找演算法。這種資料結構，就是索引。

 

B-Tree和B+Tree

目前大部分資料庫系統及檔案系統都採用B-Tree和B+Tree作為索引結構。

 

索引索引的目的：提高查詢效率原理：通過不斷的縮小想要獲得資料的範圍來篩選出最終想要的結果，同時把隨機的事件變成順序的事件，也就是我們總是通過同一種尋找方式來鎖定資料。資料結構：B+樹圖解B+樹與尋找過程： 如，是一顆b+樹，關於b+樹的定義可以參見B+樹，這裡只說一些重點，淺藍色的塊我們稱之為一個磁碟塊，可以看到每個磁碟塊包含幾個資料項目（深藍色所示）和指標（黃色所示），如磁碟塊1包含資料項目17和35，包含指標P1、P2、P3，P1表示小於17的磁碟塊，P2表示在17和35之間的磁碟塊，P3表示大於35的磁碟塊。真實的資料存在於葉子節點即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非葉子節點只不儲存真實的資料，只儲存指引搜尋方向的資料項目，如17、35並不真實存在於資料表中。 b+樹的尋找過程，如果要尋找資料項目29，那麼首先會把磁碟塊1由磁碟載入到記憶體，此時發生一次IO，在記憶體中用二分尋找確定29在17和35之間，鎖定磁碟塊1的P2指標，記憶體時間因為非常短（相比磁碟的IO）可以忽略不計，通過磁碟塊1的P2指標的磁碟地址把磁碟塊3由磁碟載入到記憶體，發生第二次IO，29在26和30之間，鎖定磁碟塊3的P2指標，通過指標載入磁碟塊8到記憶體，發生第三次IO，同時記憶體中做二分尋找找到29，結束查詢，總計三次IO。真實的情況是，3層的b+樹可以表示上百萬的資料，如果上百萬的資料尋找只需要三次IO，效能提高將是巨大的，如果沒有索引，每個資料項目都要發生一次IO，那麼總共需要百萬次的IO，顯然成本非常非常高。 b+樹性質通過上面的分析，我們知道IO次數取決於b+數的高度h，假設當前資料表的資料為N，每個磁碟塊的資料項目的數量是m，則有h=㏒(m+1)N，當資料量N一定的情況下，m越大，h越小；而m = 磁碟塊的大小 / 資料項目的大小，磁碟塊的大小也就是一個資料頁的大小，是固定的，如果資料項目占的空間越小，資料項目的數量越多，樹的高度越低。這就是為什麼每個資料項目，即索引欄位要盡量的小，比如int佔4位元組，要比bigint8位元組少一半。這也是為什麼b+樹要求把真實的資料放到葉子節點而不是內層節點，一旦放到內層節點，磁碟塊的資料項目會大幅度下降，導致樹增高。當資料項目等於1時將會退化成線性表。

 

MySQL索引底層實現