php教程：MySql資料庫索引原理

標籤：

　　PHP教程有好幾天都沒有給大家帶來關於PHP方面的知識啦，今天補上啊!本文這要講述的是：MySql資料庫索引原理，希望能對大家帶來協助!

　　第一部分主要從資料結構及演算法理論層面討論MySQL資料庫索引的數理基礎。

　　第二部分結合MySQL資料庫中InnoDB資料存放區引擎中索引的架構實現討論叢集索引、非叢集索引及覆蓋索引等話題。

　　第三部分討論MySQL中高效能使用索引的策略。

　　一、資料結構及演算法理論

　　Innodb儲存引擎實現索引的資料結構是B+樹，下面介紹幾種資料結構，一步步闡述為什麼要使用B+樹

　　1.1

　　B+樹索引的構造類似於二叉樹，根據索引值快速找到資料。但是B+樹種的B不是代表二叉，而是代表平衡。注意：B+樹索引能找到的只是被尋找資料行所在的頁。然後資料庫通過把頁讀入記憶體，再在記憶體中進行尋找，最後查到資料。

　　下面介紹二分尋找法：將記錄按有序化(遞增或遞減)排列，尋找過程中採用跳躍式方式尋找，例如：5、10、19、21、31、37、42、48、50、52這10個數，：

　　

　　用了三次尋找速度就能找到48。如果是順序尋找的話，則需要8次。對於上面10個數來說，順序尋找的平均尋找次數為5.5次，而二分尋找法為2.9次，在最壞的情況下，順序尋找的次數為10，而二分尋找的次數為4。二分尋找在innodb中Page Directory中的槽是按照主鍵的順序存放的，對於每一條具體記錄的查詢時通過對Page Directory進行二分尋找。

　　1.2

　　二叉尋找樹

　　

　　數字代表每個節點的索引值，二叉尋找樹中，左子樹的索引值總是小於跟的索引值，右子樹的索引值總是大於跟的索引值。通過中序遍曆得到索引值：2、3、5、6、7、8。

　　二叉尋找樹的平均尋找次數為2.3次。但是二叉尋找樹是可以任意構建，如構造

　　

　　但是這樣跟順序尋找就差不多，所以就引用了平衡二叉樹的思想，AVL樹。

　　1.3

　　定義：符合二叉尋找樹的定義，其次必須滿足任何節點的左右兩個子樹的高度最大差為1。

　　平衡二叉樹雖然尋找速度非常快但是維護一顆平衡二叉樹的代價是非常大，通常需要1次或多次左旋和右旋來得到插入或更新後樹的平衡性。

　　1.4

　　B+樹的特性：

　　

　　所有記錄都在分葉節點，並且是順序存放，各個分葉節點(頁為單位)都是邏輯的連續存放，是一個雙向迴圈鏈表。

　　B+樹插入必須保證插入後分葉節點中的記錄依然排序，所以在插入時必須考慮以下三種情況：

　　

　　B+樹索引在資料庫中有一個特點就是其高扇出性，因此在資料庫中，B+樹高度一般在2-3層，也就是尋找某一索引值的行記錄，最多2-3次IO，而一般的磁碟每秒至少可以做100次IO，2-3次的意味著查詢時間只需0.02-0.03秒。

　　二、 叢集索引、非叢集索引

　　叢集索引與非叢集索引的區別是：頁節點是否存放一整行記錄

　　2.1 叢集索引

　　InnoDB儲存引擎表是索引組織表，即表中資料按照主鍵順序存放。而叢集索引就是按照每張表的主鍵構造一顆B+樹，並且分葉節點中存放著整張表的行記錄資料，因此也讓叢集索引的分葉節點成為資料頁。叢集索引的這個特性決定了索引組織表中的資料也是索引一部分。同時B+樹資料結構一樣，每個資料頁都通過一個雙向鏈表來進行連結。

　　實際資料也只能按照一顆B+樹進行排序，因此每張表只能擁有一個叢集索引。在許多情況下，查詢最佳化工具非常傾向於採用叢集索引，因為叢集索引能夠讓我們在索引的分葉節點直接找到資料。此外，由於定義了資料的邏輯順序，叢集索引能夠快速地訪問針對範圍值得到查詢。查詢最佳化工具能夠快速發現某一段範圍的資料需要掃描。注意每一個頁中的記錄也是雙向鏈表維護的。

　　2.2 非叢集索引

　　也稱輔助索引，頁層級不包含行的全部資料。頁節點除了包含索引值以外，每個頁層級中的索引中還包含了一個書籤，該書籤用來告訴InnoDB儲存引擎，哪裡可以找到與索引相對應的行資料。因為InnoDB儲存引擎表是索引組織表，因此InnoDB儲存引擎的輔助索引書籤就是相應行資料的叢集索引鍵。是叢集索引和輔助索引的關係：

　　

　　當通過輔助索引來尋找資料時，InnoDB儲存引擎會遍曆輔助索引並通過分葉層級的指標獲得指向主鍵索引的主鍵，然後再通過主鍵索引來找到了一個完整的行記錄。舉例來說：一顆高度為3的輔助索引樹中尋找資料，那麼需要對這顆輔助索引遍曆3次找到指定主鍵;如果叢集索引樹的高度同樣為3，那麼還需要對叢集索引進行三次尋找，才能尋找一個完整的行資料所在的頁，因此需要6次的邏輯Io來訪問最終的一個資料頁。

php教程：MySql資料庫索引原理