對B+樹與索引在MySQL中的認識
概述
- 本質：資料庫維護某種資料結構以某種方式引用（指向）資料
- 索引取捨原則：索引的結構組織要盡量減少尋找過程中磁碟I/O的存取次數
B樹滿足的條件
- d為大於1的一個正整數，稱為B-Tree的度
- h為一個正整數，稱為B-Tree的高度
- 每個非葉子節點由n-1個key和n個指標組成，其中d<=n<=2d
- 每個葉子節點最少包含一個key和兩個指標，最多包含2d-1個key和2d個指標，分葉節點的指標均為null
- 所有分葉節點具有相同的深度，等於樹高h
- key和指標互相間隔，節點兩端是指標
- 一個節點中的key從左至右非遞減排列
- 所有節點群組成樹結構
- 每個指標要麼為null，要麼指向另外一個節點
- 一個度為d的B-Tree，設其索引N個key，則其樹高h的上限為logd((N+1)/2),檢索一個key尋找節點的個數的漸進複雜度為logd(N)
更新後的操作
- 插入刪除新的資料記錄會破壞B-Tree的性質，因此在插入刪除時，需要對樹進行一個分裂、合并、轉移等操作以保持B-Tree性質
B+樹
為什麼使用B/B+樹？主存讀取
- 當系統需要讀取主存時，則將地址訊號放到地址匯流排上傳給主存
- 主存讀到地址訊號後，解析訊號並定位到指定儲存單元，然後將此儲存單中繼資料放到資料匯流排上，供其它組件讀取
- 主存存取的時間僅與存取次數呈線性關係，因為不存在機械操作，兩次存取的資料的“距離”不會對時間有任何影響
磁碟存取原理
- 磁碟轉動，每個磁頭不動，負責讀取內容
- 不過已經有了多磁頭獨立技術
- 局部性原理
- 磁碟預讀：長度一般以頁的整數倍為單位
MyISAM索引實現
.MYI檔案的組成
- 整個索引檔案的基本資料state
- 各索引的限制資訊base
- 各索引的定義資訊keydef
- 各索引記錄的概要資訊recinfo
讀取索引的流程
- query請求，直接讀取key cache中的cache block，有就返回
- 沒有就到.MYI檔案中以file block方式讀取資料
- 再以相同的格式存取key cache
- 再將key cache中的資料返回
InnoDB索引實現
第一個與MyISAM的不同點
- 第一個重大區別是InnoDB的資料檔案本身就是索引檔案，表資料檔案本身就是按B+Tree組織的一個索引結構
- InnoDB的資料檔案本身要按主鍵聚集
- 所以InnoDB要求表必須有主鍵（MyISAM可以沒有）
- 沒有顯式指定，自動選擇唯一識別欄位
- 不存在的話，產生6個位元組長整型的隱含欄位
第二個與MyISAM的不同點
- InnoDB的輔助索引data域儲存相應記錄主鍵的值而不是地址
- 換句話說，InnoDB的所有輔助索引都引用主鍵作為data域
- 輔助索引搜尋需要檢索兩遍索引：首先檢索輔助索引獲得主鍵，然後用主鍵到主索引中檢索獲得記錄
得出的最佳化點
- 不建議使用過長的欄位作為主鍵，因為所有輔助索引都引用主索引，過長的主索引會令輔助索引變得過大
- 用非單調的欄位作為主鍵在InnoDB中也不好，因為InnoDB資料檔案本身是一顆B+Tree，非單調的主鍵會造成在插入新記錄時資料檔案為了維持B+Tree的特性而頻繁的分裂調整，十分低效，而使用自增欄位作為主鍵就很不錯了
- 聚簇索引鍵被更新造成的成本除了索引資料可能會移動，相關的所有記錄資料也要移動
索引使用原則及最佳化全列匹配
- 按照索引中所有列進行精確匹配（這裡精確匹配指“=”或“IN”匹配）時，索引可以被用到
- 理論上索引對順序是敏感的，但是由於MySQL的查詢最佳化工具會自動調整where子句的條件順序以使用適合的索引
最左首碼匹配
- 當查詢條件精確匹配索引的左邊連續一個或幾個列時，索引可以被用到
查詢條件用到了索引中列的精確匹配，但是中間某個條件未提供
- 只能用到索引中，從中間斷開前的列
- 應對
- 可以增加輔助索引
- 當中間條件選項較少時，用隔離列的方式，使用IN包含
- 看情況，比較建立
查詢條件沒有指定索引第一列
匹配某列的前置詞字元串
- 可以使用索引
- 如果萬用字元%不出現在開頭，則可以用到索引，但根據具體情況不同可能只會用其中一個首碼
範圍查詢
- 範圍列可以用到索引（必須是最左首碼），但是範圍列後面的列無法用到索引
- 同時，索引最多用於一個範圍列，因此如果查詢條件中有兩個範圍列則無法全用到索引
- 僅用explain可能無法區分範圍索引和多值匹配
查詢條件中含有函數/運算式
索引選擇性與首碼索引MyISAM與InnoDB基數統計方式
- MyisAM索引的基數值（Cardinality，show index 命令可以看見）是精確的，InnoDB則是估計值
- MyisAM統計資訊是儲存磁碟中，在alter表或Analyze table操作更新此資訊
- 而InnoDB則是在表第一次開啟的時候估計值儲存在緩衝區內
不建議建立索引的情況
- 表記錄比較少
- 索引的選擇性低：不重複的索引值（也叫基數，Cardinality）與表記錄數（#T）的比值
首碼索引
- 用列的首碼代替整個列作為索引key，當前置長度合適時，可以做到既使得首碼索引的選擇性接近全列索引，同時因為索引key變短而減少了索引檔案的大小和維護開銷
缺點
- 不能用於ORDER BY和GROUP BY操作
- 也不能用於Covering index（即當索引本身包含查詢所需全部資料時，不再訪問資料檔案本身）
InnoDB主鍵選擇與插入最佳化
- 如果沒有特別的需要，請永遠使用一個與業務無關的自增欄位作為主鍵
- InnoDB使用叢集索引，資料記錄本身被存於主索引（一顆B+Tree）的葉子節點上
- 這就要求同一個葉子節點內（大小為一個記憶體頁或磁碟頁）的各條資料記錄按主鍵順序存放，因此每當有一條新的記錄插入時，MySQL會根據其主鍵將其插入適當的節點和位置，如果頁面達到裝載因子（InnoDB預設為15/16），則開闢一個新的頁（節點）
- 如果使用非自增主鍵，每次插入近似隨機，容易引起資料的移動，重新讀目標頁面，片段也多了，雖然也可以用OPTIMIZE TABLE重建最佳化，但麻煩啊
參考資料
- 圖片來源網路
- 《高效能MySQL》 高效能MySQL（第3版） 中文PDF帶目錄清晰版 下載
本文永久更新連結地址：
