再一次學習 MySQL 索引

icecho 發表於 2020-08-01

再一次學習 MySQL 索引

前言

提到資料庫索引,大家肯定很熟悉,在日常工作中經常會接觸到。這幾天看了不少相關文章、書籍和課程。決定自己總結一篇文章,雖然我寫的這篇文章肯定不如網上各路大神的好文,但是自己總結一遍總歸記得更牢固 🤓。這應該也是一種好的學習習慣,別人寫的字再漂亮都是別人的,自己寫的字就算再潦草起碼自己也能認識吧 😂。

索引是一種提高我們查詢效率的資料結構。就好像是字典的目錄,一本幾百頁的字典,如果想快速查詢到某個字,總不能靠硬翻吧 😫。

索引結構

結論

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+ 樹查詢過程

再一次學習 MySQL 索引

磁碟塊 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 就越小。

再一次學習 MySQL 索引

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 操作即可查詢到資料。

總結

  1. 雜湊表索引操作單資料行的時候很快,但是不支援範圍查詢,不能利用索引來排序,不支援聯合索引最左匹配原則。
  2. B 樹的資料可以儲存在非葉子節點中,範圍查詢時可能會有額外的隨機磁碟 IO。而且由於真實資料存放在非葉子節點中,B 樹的高度肯定要高於同樣情況下的 B+ 樹。這樣也不利於提升效率。
  3. B+ 樹把真實資料儲存在葉子節點中是為了讓樹更矮胖,減少 IO 次數,提升效率。

最後

這是我學習 MySQL 索引結構記錄下的一些筆記 📝,之後也還會總結一篇索引使用的相關注意事項。我發現閱讀完各路大神的文章之後,再自己寫一遍印象會深刻許多。雖然是炒舊飯,但也是炒給自己吃 😂。希望大家多多給我鼓勵,哈哈哈哈哈。

參考文章

我以為我對 MySQL 索引很瞭解,直到我遇到了阿里的面試官。
為什麼 MySQL 使用 B+ 樹 - 面向信仰程式設計
一篇文章講透MySQL為什麼要用B+樹實現索引 - 騰訊雲
面試題:InnoDB中一棵B+樹能存多少行資料?- 騰訊雲
MySQL索引原理及慢查詢優化 - 美團技術團隊
搞懂Mysql InnoDB B+樹索引 - 阿里雲開發者社群

本作品採用《CC 協議》,轉載必須註明作者和本文連結

Hello。