一、MySQL索引原理

1、索引背景

　　生活中隨處可見索引的例子，如火車站的車次表、圖書的目錄等。它們的原理都是一樣的，通過不斷的縮小想要獲得數據的范圍來篩選出最終想要的結果，同時把隨機的事件變成順序的事件，也就是我們總是通過同一種查找方式來鎖定數據。

　　數據庫也是一樣，但顯然要復雜許多，因為不僅面臨著等值查詢，還有范圍查詢(>、<、between、in)、模糊查詢(like)、并集查詢(or)等等。數據庫應該選擇怎么樣的方式來應對所有的問題呢？我們回想字典的例子，能不能把數據分成段，然后分段查詢呢？最簡單的如果1000條數據，1到100分成第一段，101到200分成第二段，201到300分成第三段......這樣查第250條數據，只要找第三段就可以了，一下子去除了90%的無效數據。但如果是1千萬的記錄呢，分成幾段比較好？稍有算法基礎的同學會想到搜索樹，其平均復雜度是lgN，具有不錯的查詢性能。但這里我們忽略了一個關鍵的問題，復雜度模型是基于每次相同的操作成本來考慮的，數據庫實現比較復雜，數據保存在磁盤上，而為了提高性能，每次又可以把部分數據讀入內存來計算，因為我們知道訪問磁盤的成本大概是訪問內存的十萬倍左右，所以簡單的搜索樹難以滿足復雜的應用場景。

2、磁盤IO與預讀

　　前面提到了訪問磁盤，那么這里先簡單介紹一下磁盤IO和預讀，磁盤讀取數據靠的是機械運動，每次讀取數據花費的時間可以分為尋道時間、旋轉延遲、傳輸時間三個部分，尋道時間指的是磁臂移動到指定磁道所需要的時間，主流磁盤一般在5ms以下；旋轉延遲就是我們經常聽說的磁盤轉速，比如一個磁盤7200轉，表示每分鐘能轉7200次，也就是說1秒鐘能轉120次，旋轉延遲就是1/120/2 = 4.17ms；傳輸時間指的是從磁盤讀出或將數據寫入磁盤的時間，一般在零點幾毫秒，相對于前兩個時間可以忽略不計。那么訪問一次磁盤的時間，即一次磁盤IO的時間約等于5+4.17 = 9ms左右，聽起來還挺不錯的，但要知道一臺500 -MIPS的機器每秒可以執行5億條指令，因為指令依靠的是電的性質，換句話說執行一次IO的時間可以執行40萬條指令，數據庫動輒十萬百萬乃至千萬級數據，每次9毫秒的時間，顯然是個災難。下圖是計算機硬件延遲的對比圖，供大家參考：

　　考慮到磁盤IO是非常高昂的操作，計算機操作系統做了一些優化，當一次IO時，不光把當前磁盤地址的數據，而是把相鄰的數據也都讀取到內存緩沖區內，因為局部預讀性原理告訴我們，當計算機訪問一個地址的數據的時候，與其相鄰的數據也會很快被訪問到。每一次IO讀取的數據我們稱之為一頁(page)。具體一頁有多大數據跟操作系統有關，一般為4k或8k，也就是我們讀取一頁內的數據時候，實際上才發生了一次IO，這個理論對于索引的數據結構設計非常有幫助。

3、索引的數據結構

　　如上圖，是一顆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并不真實存在于數據表中。

4、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，顯然成本非常非常高。

5、b+樹性質

　　1.通過上面的分析，我們知道IO次數取決于b+數的高度h，假設當前數據表的數據為N，每個磁盤塊的數據項的數量是m，則有h=㏒(m+1)N，當數據量N一定的情況下，m越大，h越小；而m = 磁盤塊的大小 / 數據項的大小，磁盤塊的大小也就是一個數據頁的大小，是固定的，如果數據項占的空間越小，數據項的數量越多，樹的高度越低。這就是為什么每個數據項，即索引字段要盡量的小，比如int占4字節，要比bigint8字節少一半。這也是為什么b+樹要求把真實的數據放到葉子節點而不是內層節點，一旦放到內層節點，磁盤塊的數據項會大幅度下降，導致樹增高。當數據項等于1時將會退化成線性表。

　　2.當b+樹的數據項是復合的數據結構，比如(name,age,sex)的時候，b+數是按照從左到右的順序來建立搜索樹的，比如當(張三,20,F)這樣的數據來檢索的時候，b+樹會優先比較name來確定下一步的所搜方向，如果name相同再依次比較age和sex，最后得到檢索的數據；但當(20,F)這樣的沒有name的數據來的時候，b+樹就不知道下一步該查哪個節點，因為建立搜索樹的時候name就是第一個比較因子，必須要先根據name來搜索才能知道下一步去哪里查詢。比如當(張三,F)這樣的數據來檢索時，b+樹可以用name來指定搜索方向，但下一個字段age的缺失，所以只能把名字等于張三的數據都找到，然后再匹配性別是F的數據了， 這個是非常重要的性質，即索引的最左匹配特性。

二、InnoDB 與 MyISAM 的區別

1、InnoDB 與 MyISAM 結構上的區別

1、InnoDB的主鍵索引 ，MyISAM索引文件和數據文件是分離的，索引文件僅保存數據記錄的地址。

2、而在InnoDB中，表數據文件本身就是按B+Tree組織的一個索引結構，這棵樹的葉節點data域保存了完整的數據記錄。這個索引的key是數據表的主鍵，因此InnoDB表數據文件本身就是主索引，所以必須有主鍵，如果沒有顯示定義，自動為生成一個隱含字段作為主鍵，這個字段長度為6個字節，類型為長整形。

3、.InnoDB的輔助索引(Secondary Index， 也就是非主鍵索引)也會包含主鍵列，比如名字建立索引，內部節點 會包含名字，葉子節點會包含該名字對應的主鍵的值，如果主鍵定義的比較大，其他索引也將很大。所以一般都以id自增為主鍵。

4.MyISAM引擎使用B+Tree作為索引結構，索引文件葉節點的data域存放的是數據記錄的地址，指向數據文件中對應的值，每個節點只有該索引列的值

5.MyISAM主索引和輔助索引(Secondary key)在結構上沒有任何區別，只是主索引要求key是唯一的，輔助索引可以重復，

(由于MyISAM輔助索引在葉子節點上存儲的是數據記錄的地址，和主鍵索引一樣，所以相對于B+的InnoDB可通過輔助索引快速找到所有的數據，而不需要再遍歷一邊主鍵索引，所以適用于OLAP)

2、InnoDB索引和MyISAM索引的區別

一是主索引的區別，InnoDB的數據文件本身就是索引文件。而MyISAM的索引和數據是分開的。

二是輔助索引的區別：InnoDB的輔助索引data域存儲相應記錄主鍵的值而不是地址。而MyISAM的輔助索引和主索引沒有多大區別。

3、B+樹在數據庫索引中的應用

目前大部分數據庫系統及文件系統都采用B-Tree或其變種B+Tree作為索引結構

3.1、在數據庫索引的應用

在數據庫索引的應用中，B+樹按照下列方式進行組織 ：

① 葉結點的組織方式 。B+樹的查找鍵 是數據文件的主鍵 ，且索引是稠密的。也就是說 ，葉結點 中為數據文件的第一個記錄設有一個鍵、指針對 ，該數據文件可以按主鍵排序，也可以不按主鍵排序 ;數據文件按主鍵排序，且 B +樹是稀疏索引 ， 在葉結點中為數據文件的每一個塊設有一個鍵、指針對 ;數據文件不按鍵屬性排序 ，且該屬性是 B +樹 的查找鍵 ， 葉結點中為數據文件里出現的每個屬性K設有一個鍵 、 指針對 ， 其中指針執行排序鍵值為 K的 記錄中的第一個。

② 非葉結點 的組織方式。B+樹 中的非葉結點形成 了葉結點上的一個多級稀疏索引。 每個非葉結點中至少有ceil( m/2 ) 個指針 ， 至多有 m 個指針 。

3.2、B+樹索引的插入和刪除

①在向數據庫中插入新的數據時，同時也需要向數據庫索引中插入相應的索引鍵值 ，則需要向 B+樹 中插入新的鍵值。即上面我們提到的B-樹插入算法。

②當從數據庫中刪除數據時，同時也需要從數據庫索引中刪除相應的索引鍵值 ，則需要從 B+樹 中刪 除該鍵值 。即B-樹刪除算法

轉載于:https://www.cnblogs.com/wangzhuxing/p/6165150.html

總結

以上是生活随笔為你收集整理的mysql系列十、mysql索引结构的实现B+树/B-树原理的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。