前言
提到資料庫索引，大家肯定很熟悉，在日常工作中經常會接觸到。這幾天看了不少相關文章、書籍和課程。決定自己總結一篇文章，雖然我寫的這篇文章肯定不如網上各路大神的好文，但是自己總結一遍總歸記得更牢固 ?。這應該也是一種好的學習習慣，別人寫的字再漂亮都是別人的，自己寫的字就算再潦草起碼自己也能認識吧 ?。
索引是一種提高我們查詢效率的資料結構。就好像是字典的目錄，一本幾百頁的字典，如果想快速查詢到某個字，總不能靠硬翻吧 ?。
索引結構
結論
MySQL 索引一般是雜湊表或 B+ 樹，常用的 InnoDB 引擎預設使用的是 B+ 樹來作為索引的資料結構。
為什麼不用雜湊表？
如果使用 B+ 樹作為索引資料結構，那麼訪問或修改一條資料的時間複雜度是 O(log n)，但是使用雜湊表作為索引結構幹這些活的時候，時間複雜度 O(1)。如果只是查一條資料或者修改一條資料，用雜湊表做索引肯定給力呀！但是一般業務系統不會這麼簡單。
在業務開發中，經常會遇到範圍查詢、排序查詢等需求。這個時候雜湊表索引就沒辦法高效的處理這些需求了。它只能通過掃表來實現這些功能，掃表應該是資料庫的噩夢吧 ?。
MySQL 使用 B+ 樹資料結構非葉子節點只儲存鍵值，葉子節點會儲存資料或者是主鍵。並且在葉子節點中鍵是按照順序儲存的，使得範圍查詢、排序查詢等變得異常簡單。
雖然雜湊表索引在操作單列資料的時候十分高效，但是需要範圍查詢、排序查詢的時候，B+ 樹資料結構顯然更合適。在我們業務開發中，不可能只操作一行資料。綜合考慮，還是 B+ 樹更適合作為索引的資料結構。
雜湊表索引不支援範圍查詢，不能利用索引來排序，不支援聯合索引最左匹配原則，如果重複鍵值比較多，還容易造成雜湊碰撞導致效率進一步降低 ?。
為什麼不用 B 樹？
B+ 樹的非葉子節點上只儲存鍵值，而 B 樹的非葉子節點上不僅儲存鍵值還儲存資料。在 MySQL 資料庫中資料頁的大小是固定的，Innodb 引擎資料頁預設大小為 16 KB。B+ 樹這種做法是為了讓樹的階數更大，讓樹更矮胖。進行查詢的時候，磁碟 IO 次數就會減少，查詢效率也會更快。
B+ 樹的所有資料均儲存在葉子節點中，並且是按鍵值有序排列。但是 B 樹的資料分散在各個節點。進行範圍查詢，排序查詢的時候，B 樹的效率肯定不如 B+ 樹。
B+ 樹查詢過程
磁碟塊 1 中儲存 17 和 35 資料項，還有 P1、P2、P3 指標，P1 表示資料項小於 17 的磁碟塊，P2 表示資料項在 17 和 35 之間的資料項，P3 表示資料項大於 35 的資料項。非葉子節點不儲存資料，只儲存指引搜尋方向的資料項。
我們知道每次 IO 讀取一個資料頁的大小，也就是一個磁碟塊。假設我們要查詢 29 這個資料項，首先進行第一次 IO 將磁碟塊 1 讀進記憶體，發現 17 < 29 < 35，然後選用 P2 指標進行第二次 IO 將磁碟塊 3 讀進記憶體，發現 26 < 29 < 30，然後選用 P2 指標將磁碟塊 8 讀進記憶體，在記憶體中做二分查詢，找到 29，結束查詢。
通過分析查詢過程，我們可以知道 IO 次數和 B+ 樹的高度成正比。H 為樹的高度，M 為每個磁碟塊的資料項個數，N 為資料項總數。從下面的公式可以看出如果資料量 N 一定，M 越大相應的 H 就越小。
M 等於磁碟塊的大小除以資料項大小，由於磁碟塊大小一般是固定的，所以減小資料項大小才能使得 M 更大從而讓樹更矮胖。這也是為什麼 B+ 樹把真實資料放在葉子節點而不是非葉子節點的原因，如果真實資料放在非葉子結點，磁碟塊儲存的資料項會大幅度減少，樹就會增高相應查詢資料時的 IO 次數就會變多。
B+ 樹一般能儲存多少資料？
這裡我們先假設 B+ 樹高為 2，即存在一個根節點和若干個葉子節點，假設一行記錄的資料大小為 1 KB，那麼單個葉子節點（頁）中的記錄數等於 16 KB / 1 KB = 16 條資料。
然後要計算出非葉子節點能存放多少指標，我們假設主鍵 ID 為 bigint 型別，長度為 8 位元組，而指標大小在 InnoDB 原始碼中設定為 6 位元組，這樣一共 14 位元組，我們一個頁中能存放多少這樣的單元，其實就代表有多少指標，即 16 KB / 14 B = 1170。那麼可以算出一棵高度為 2 的 B+ 樹，大概就能存放下 1170 * 16 = 18720 條資料。
根據同樣的原理我們可以算出一個高度為 3 的 B+ 樹就可以存放下 21902400 條資料。所以在 InnoDB 中 B+ 樹高度一般為 1 - 3 層，它就能滿足千萬級的資料儲存。在查詢資料時一次頁的查詢代表一次 IO，所以通過主鍵索引查詢通常只需要 1 - 3 次邏輯 IO 操作即可查詢到資料。
總結
- 雜湊表索引操作單資料行的時候很快，但是不支援範圍查詢，不能利用索引來排序，不支援聯合索引最左匹配原則。
- B 樹的資料可以儲存在非葉子節點中，範圍查詢時可能會有額外的隨機磁碟 IO。而且由於真實資料存放在非葉子節點中，B 樹的高度肯定要高於同樣情況下的 B+ 樹。這樣也不利於提升效率。
- B+ 樹把真實資料儲存在葉子節點中是為了讓樹更矮胖，減少 IO 次數，提升效率。
最後
這是我學習 MySQL 索引結構記錄下的一些筆記 ?，之後也還會總結一篇索引使用的相關注意事項。我發現閱讀完各路大神的文章之後，再自己寫一遍印象會深刻許多。雖然是炒舊飯，但也是炒給自己吃 ?。希望大家多多給我鼓勵，哈哈哈哈哈。
參考文章
我以為我對 MySQL 索引很瞭解，直到我遇到了阿里的面試官。
為什麼 MySQL 使用 B+ 樹 - 面向信仰程式設計
一篇文章講透MySQL為什麼要用B+樹實現索引 - 騰訊雲
面試題：InnoDB中一棵B+樹能存多少行資料？- 騰訊雲
MySQL索引原理及慢查詢優化 - 美團技術團隊
搞懂Mysql InnoDB B+樹索引 - 阿里雲開發者社群
本作品採用《CC 協議》，轉載必須註明作者和本文連結