閱讀本文大概需要 5 分鐘。
作者：李平 | 來源：個人部落格
一、InnoDB 一棵 B+ 樹可以存放多少行資料？
InnoDB 一棵 B+ 樹可以存放多少行資料？
這個問題的簡單回答是：約 2 千萬。
為什麼是這麼多呢？
因為這是可以算出來的，要搞清楚這個問題，我們先從 InnoDB 索引資料結構、資料組織方式說起。
我們都知道計算機在儲存資料的時候，有最小儲存單元，這就好比我們今天進行現金的流通最小單位是一毛。
在計算機中磁碟儲存資料最小單元是扇區，一個扇區的大小是 512 位元組，而檔案系統（例如XFS/EXT4）他的最小單元是塊，一個塊的大小是 4k，而對於我們的 InnoDB 儲存引擎也有自己的最小儲存單元——頁（Page），一個頁的大小是 16K。
二、下面幾張圖可以幫你理解最小儲存單元
檔案系統中一個檔案大小隻有1個位元組，但不得不佔磁碟上4KB的空間。
Innodb 的所有資料檔案（字尾為 ibd 的檔案），他的大小始終都是 16384（16k）的整數倍。
磁碟扇區、檔案系統、InnoDB 儲存引擎都有各自的最小儲存單元。
在 MySQL 中我們的 InnoDB 頁的大小預設是 16k，當然也可以通過引數設定：
資料表中的資料都是儲存在頁中的，所以一個頁中能儲存多少行資料呢？
假設一行資料的大小是 1k，那麼一個頁可以存放 16 行這樣的資料。
如果資料庫只按這樣的方式儲存，那麼如何查詢資料就成為一個問題，因為我們不知道要查詢的資料存在哪個頁中，也不可能把所有的頁遍歷一遍，那樣太慢了。
所以人們想了一個辦法，用 B+ 樹的方式組織這些資料。如圖所示：
我們先將資料記錄按主鍵進行排序，分別存放在不同的頁中（為了便於理解我們這裡一個頁中只存放 3 條記錄，實際情況可以存放很多），除了存放資料的頁以外，還有存放鍵值+指標的頁，如圖中page number=3 的頁，該頁存放鍵值和指向資料頁的指標，這樣的頁由 N 個鍵值 + 指標組成。
當然它也是排好序的。
這樣的資料組織形式，我們稱為索引組織表。
現在來看下，要查詢一條資料，怎麼查？
如 select * from user where id=5;
這裡 id 是主鍵，我們通過這棵 B+ 樹來查詢，首先找到根頁，你怎麼知道 user 表的根頁在哪呢？
其實每張表的根頁位置在表空間檔案中是固定的，即 page number=3 的頁（這點我們下文還會進一步證明），找到根頁後通過二分查詢法，定位到 id=5 的資料應該在指標 P5 指向的頁中，那麼進一步去 page number=5 的頁中查詢，同樣通過二分查詢法即可找到 id=5 的記錄：
| 5 | zhao2 | 27 |
現在我們清楚了 InnoDB 中主鍵索引 B+ 樹是如何組織資料、查詢資料的，我們總結一下：
1、InnoDB 儲存引擎的最小儲存單元是頁，頁可以用於存放資料也可以用於存放鍵值 + 指標，在 B+ 樹中葉子節點存放資料，非葉子節點存放鍵值 + 指標。
2、索引組織表通過非葉子節點的二分查詢法以及指標確定資料在哪個頁中，進而在去資料頁中查詢到需要的資料；
三、那麼回到我們開始的問題，通常一棵 B+ 樹可以存放多少行資料？
這裡我們先假設 B+ 樹高為 2，即存在一個根節點和若干個葉子節點，那麼這棵 B+ 樹的存放總記錄數為：根節點指標數 * 單個葉子節點記錄行數。
上文我們已經說明單個葉子節點（頁）中的記錄數 =16K/1K=16。（這裡假設一行記錄的資料大小為 1k，實際上現在很多網際網路業務資料記錄大小通常就是 1K 左右）。
那麼現在我們需要計算出非葉子節點能存放多少指標？
其實這也很好算，我們假設主鍵 ID 為 bigint 型別，長度為 8 位元組，而指標大小在 InnoDB 原始碼中設定為 6 位元組，這樣一共 14 位元組，我們一個頁中能存放多少這樣的單元，其實就代表有多少指標，即 16384/14=1170。
那麼可以算出一棵高度為 2 的 B+ 樹，能存放 1170*16=18720 條這樣的資料記錄。
根據同樣的原理我們可以算出一個高度為 3 的 B+ 樹可以存放： 1170117016=21902400 條這樣的記錄。
所以在 InnoDB 中 B+ 樹高度一般為 1-3 層，它就能滿足千萬級的資料儲存。
在查詢資料時一次頁的查詢代表一次 IO，所以通過主鍵索引查詢通常只需要 1-3 次 IO 操作即可查詢到資料。
四、怎麼得到 InnoDB 主鍵索引 B+ 樹的高度？
上面我們通過推斷得出 B+ 樹的高度通常是 1-3，下面我們從另外一個側面證明這個結論。
在 InnoDB 的表空間檔案中，約定 page numbe r為 3 的代表主鍵索引的根頁，而在根頁偏移量為 64 的地方存放了該 B+ 樹的 page level。
如果 page level 為 1，樹高為 2，page level 為 2，則樹高為 3。
即 B+ 樹的高度 =page level+1；
下面我們將從實際環境中嘗試找到這個 page level。
在實際操作之前，你可以通過 InnoDB 後設資料表確認主鍵索引根頁的 page number 為 3，你也可以從《InnoDB 儲存引擎》這本書中得到確認。
執行結果：
可以看出資料庫 dbt3 下的 customer 表、lineitem 表主鍵索引根頁的 page number 均為 3，而其他的二級索引 page number 為 4。
關於二級索引與主鍵索引的區別請參考 MySQL 相關書籍，本文不在此介紹。
下面我們對資料庫表空間檔案做想相關的解析：
因為主鍵索引 B+ 樹的根頁在整個表空間檔案中的第 3 個頁開始，所以可以算出它在檔案中的偏移量：16384*3=49152（16384 為頁大小）。
另外根據《InnoDB 儲存引擎》中描述在根頁的 64 偏移量位置前 2 個位元組，儲存了 page level 的值，因此我們想要的 page level 的值在整個檔案中的偏移量為：16384*3+64=49152+64=49216，前 2 個位元組中。
接下來我們用 hexdump 工具，檢視錶空間檔案指定偏移量上的資料：
linetem 表的 page level 為 2，B+ 樹高度為 page level+1=3；
region 表的 page level 為 0，B+ 樹高度為 page level+1=1；
customer 表的 page level 為 2，B+ 樹高度為 page level+1=3；
這三張表的資料量如下：
五、小結
lineitem 表的資料行數為 600 多萬，B+ 樹高度為 3，customer 表資料行數只有 15 萬，B+ 樹高度也為 3。
可以看出儘管資料量差異較大，這兩個表樹的高度都是 3，換句話說這兩個表通過索引查詢效率並沒有太大差異，因為都只需要做 3 次 IO。
那麼如果有一張錶行數是一千萬，那麼他的 B+ 樹高度依舊是 3，查詢效率仍然不會相差太大。
region 表只有 5 行資料，當然他的 B+ 樹高度為 1。
六、最後回顧一道面試題
有一道 MySQL 的面試題，為什麼 MySQL 的索引要使用 B+ 樹而不是其它樹形結構？
比如 B 樹？
現在這個問題的複雜版本可以參考本文；
他的簡單版本回答是：
因為 B 樹不管葉子節點還是非葉子節點，都會儲存資料，這樣導致在非葉子節點中能儲存的指標數量變少（有些資料也稱為扇出），指標少的情況下要儲存大量資料，只能增加樹的高度，導致 IO 操作變多，查詢效能變低。
七、總結
本文從一個問題出發，逐步介紹了 InnoDB 索引組織表的原理、查詢方式，並結合已有知識，回答該問題，結合實踐來證明。
當然為了表述簡單易懂，文中忽略了一些細枝末節，比如一個頁中不可能所有空間都用於存放資料，它還會存放一些少量的其他欄位比如 page level，index number 等等，另外還有頁的填充因子也導致一個頁不可能全部用於儲存資料。
關於二級索引資料存取方式可以參考 MySQL 相關書籍，他的要點是結合主鍵索引進行回表查詢。
本作品採用《CC 協議》，轉載必須註明作者和本文連結