搞懂MySQL InnoDB B+樹索引

GrimMjx發表於2019-03-16

MySql索引

一.InnoDB索引

　　InnoDB支援以下幾種索引：

B+樹索引
全文索引
雜湊索引

　　本文將著重介紹B+樹索引。其他兩個全文索引和雜湊索引只是做簡單介紹一筆帶過。

　　雜湊索引是自適應的，也就是說這個不能人為干預在一張表生成雜湊索引，InnoDB會根據這張表的使用情況來自動生成。

　　全文索引是將存在資料庫的整本書的任意內容資訊查詢出來的技術，InnoDB從1.2.x版本支援。每張表只能有一個全文檢索的索引。

　　B+樹索引是傳統意義上的索引，B+樹索引並不能根據鍵值找到具體的行資料，B+樹索引只能找到行資料鎖在的頁，然後通過把頁讀到記憶體，再在記憶體中查詢到行資料。B+樹索引也是最常用的最為頻繁使用的索引。

二.什麼是B+樹

概念

　　B+樹是一種平衡查詢樹，其實先想想看為什麼要用平衡查詢樹，不用二叉樹？普通的二叉樹可能因為插入的資料最後變成一個很長的連結串列，怎麼能提高搜尋的速度呢？你可以想想，為什麼HashMap和ConcurrentHashMap在JDK8的時候，當連結串列大於8的時候把連結串列轉成紅黑樹（紅黑樹也是平衡查詢樹）。技術思維是想通的，那麼答案無非是加快速度，效能咯。

　　一個B+樹有以下特徵：

有n個子樹的中間節點包含n個元素，每個元素不儲存資料，只用來索引，所有資料都儲存在葉子節點。
所有葉子節點包含元素的資訊以及指向記錄的指標，且葉子節點按關鍵字自小到大順序連結。
所有的中間節點元素都同時存在於子節點，在子節點元素中是最大（或最小）元素。

　　那麼我們先來看一個B+樹的圖

　　所有的資料都在葉子節點，且每一個葉子節點都帶有指向下一個節點的指標，形成了一個有序的連結串列。為什麼要有序呢？其實是為了範圍查詢。比如說select * from Table where id > 1 and id < 100; 當找到1後，只需順著節點和指標順序遍歷就可以一次性訪問到所有資料節點，極大提到了區間查詢效率。是不是範圍查詢的話hash就搞不定這個事情了？以下為B+樹的優勢：

單一節點儲存更多元素，減少IO
所有查詢都要找到葉子節點，查詢穩定
所有葉子節點形成有序連結串列，方便範圍查詢

　　一般性情況，資料庫的B+樹的高度一般在2~4層，這就是說找到某一鍵值的行記錄最多需要2到4次邏輯IO，相當於0.02到0.04s。

三.聚集索引和輔助索引

聚集索引

　　聚集索引是按表的主鍵構造的B+樹，葉子節點存放的為整張表的行記錄資料，每張表只能有一個聚集索引。優化器更傾向採用聚集索引。因為直接就能獲取行資料。

　　請選擇自增id來做主鍵，不要非空UK列。避免大量分頁碎片。下面來看一個聚集索引的圖：

　　那麼很簡單了，每個葉子節點，都存有完整的行記錄。對於主鍵的查詢速度那是相當的快，美滋滋。

輔助索引

　　輔助索引也叫非聚集索引，葉子節點除了鍵值以外還包含了一個bookmark，用來告訴InnoDB在哪裡可以找到對應的行資料，InnoDB的輔助索引的bookmark就是相對應行資料的聚集索引鍵。也就是先獲取指向主鍵索引的主鍵，然後通過主鍵索引來找到一個完整的行。如果輔助索引的樹和聚集索引的樹的高度都是3，如果不是走主鍵索引走輔助索引的話，那麼需要6次邏輯IO訪問得到最終的資料頁。輔助索引和聚集索引的概念關係圖如下：

四.索引實戰

設計索引

　　設計索引的時候，無論是組合索引還是普通索引等。一般經驗是，選擇經常被用來過濾記錄的欄位，高選擇性，高區分性。別把性別欄位設計索引，性別屬於低選擇性的。你可以選擇名字嘛，你好我大名叫苗嘉杏：）

　　知道加索引快，但是也別亂加索引，插入以及更新索引的操作InnoDB都會維護B+樹的，多加很多索引只會導致效率降低！

　　不要用重複的索引，比如有個聯合索引是a，b，你又整個a列的普通索引。那不是搞事麼？

　　不要在索引上用函式和like

一顆聚集索引B+樹可以放多少行資料？

　　這裡我們先假設B+樹高為2，即存在一個根節點和若干個葉子節點，那麼這棵B+樹的存放總記錄數為：根節點指標數*單個葉子節點記錄行數。假設一行記錄的資料大小為1k，那麼單個葉子節點（頁）中的記錄數=16K/1K=16。

　　那麼現在我們需要計算出非葉子節點能存放多少指標，我們假設主鍵ID為bigint型別，長度為8位元組，而指標大小在InnoDB原始碼中設定為6位元組，這樣一共14位元組，我們一個頁中能存放多少這樣的單元，其實就代表有多少指標，即16kb/14b=1170。那麼可以算出一棵高度為2的B+樹，大概能存放1170*16=18720條這樣的資料記錄。

　　根據同樣的原理我們可以算出一個高度為3的B+樹大概可以存放：1170*1170*16=21902400行資料。所以在InnoDB中B+樹高度一般為1-3層，它就能滿足千萬級的資料儲存。在查詢資料時一次頁的查詢代表一次IO，所以通過主鍵索引查詢通常只需要1-3次邏輯IO操作即可查詢到資料。

Cardinality值

　　如何判斷一個索引建立的是否好呢？可以用show index from指令檢視Cardinality值，這個值是一個預估值，而不是一個準確值。每次對Cardinality值的統計都是隨機取8個葉子節點得到的。

　　對於innodb來說，達到以下2點就會重新計算cardinality

如果表中1/16的資料發生變化
如果stat_modified_counter>200 000 0000

　　實際應用中，（Cardinality/行數）應該儘量接近1。如果非常小則要考慮是否需要此索引。實戰一下，比如有一張表，我們來show index一下

mysql> show index from Order;
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Order   |          0 | PRIMARY          |            1 | id          | A         |       99552 |     NULL | NULL   |      | BTREE      |         |               |
| Order   |          1 | IDX_orderId      |            1 | orderId     | A         |       96697 |     NULL | NULL   |      | BTREE      |         |               |
| Order   |          1 | IDX_productId    |            1 | productId   | A         |          52 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)