mysql中innodb索引原理是什么

這篇文章主要介紹了mysql中innodb索引原理是什么，具有一定借鑒價值，需要的朋友可以參考下。希望大家閱讀完這篇文章后大有收獲。下面讓小編帶著大家一起了解一下。

聚集索引（clustered index）

innodb存儲引擎表是索引組織表，表中數據按照主鍵順序存放。其聚集索引就是按照每張表的主鍵順序構造一顆B+樹，其葉子結點中存放的就是整張表的行記錄數據，這些葉子節點成為數據頁。（相關推薦：MySQL教程）

聚集索引的存儲并不是物理上連續的，而是邏輯上連續的，葉子結點間按照主鍵順序排序，通過雙向鏈表連接。多數情況下，查詢優化器傾向于采用聚集索引，因為聚集索引能在葉子結點直接找到數據，并且因為定義了數據的邏輯順序，能特別快的訪問針對范圍值的查詢。

聚集索引的這個特性決定了索引組織表中的數據也是索引的一部分。由于表里的數據只能按照一顆B+樹排序，因此一張表只能有一個聚簇索引。

在Innodb中，聚簇索引默認就是主鍵索引。如果沒有主鍵，則按照下列規則來建聚簇索引:

• 沒有主鍵時，會用一個非空并且唯一的索引列做為主鍵，成為此表的聚簇索引;
• 如果沒有這樣的索引，InnoDB會隱式定義一個主鍵來作為聚簇索引。

由于主鍵使用了聚簇索引，如果主鍵是自增id，那么對應的數據也會相鄰地存放在磁盤上，寫入性能較高。如果是uuid等字符串形式，頻繁的插入會使innodb頻繁地移動磁盤塊，寫入性能就比較低了。

B+樹（多路平衡查找樹）

我們知道了innodb引擎索引使用了B+樹結構，那么為什么不是其他類型樹結構，例如二叉樹呢？

計算機在存儲數據的時候，有最小存儲單元，這就好比人民幣流通最小單位是分一樣。文件系統的最小單元是塊，一個塊的大小是4k（這個值根據系統不同并且可設置），InnoDB存儲引擎也有自己的最小儲存單元—頁（Page），一個頁的大小是16K（這個值也是可設置的）。

文件系統中一個文件大小只有1個字節，但不得不占磁盤上4KB的空間。同理，innodb的所有數據文件的大小始終都是16384（16k）的整數倍。

所以在MySQL中，存放索引的一個塊節點占16k，mysql每次IO操作會利用系統的預讀能力一次加載16K。這樣，如果這一個節點只放1個索引值是非常浪費的，因為一次IO只能獲取一個索引值，所以不能使用二叉樹。

B+樹是多路查找樹，一個節點能放n個值，n = 16K / 每個索引值的大小。
例如索引字段大小1Kb，這時候每個節點能放的索引值理論上是16個，這種情況下，二叉樹一次IO只能加載一個索引值，而B+樹則能加載16個。

B+樹的路數為n+1，n是每個節點存在的值數量，例如每個節點存放16個值，那么這棵樹就是17路。

從這里也能看出，B+樹節點可存儲多個值，所以B+樹索引并不能找到一個給定鍵值的具體行。B+樹只能找到存放數據行的具體頁，然后把頁讀入到內存中，再在內存中查找指定的數據。

附：B樹和B+樹的區別在于，B+樹的非葉子結點只包含導航信息，不包含實際的值，所有的葉子結點和相連的節點使用鏈表相連，便于區間查找和遍歷。

輔助索引

也稱為非聚集索引，其葉子節點不包含行記錄的全部數據，葉子結點除了包含鍵值以外，每個葉子結點中的索引行還包含一個書簽，該書簽就是相應行的聚集索引鍵。

如下圖可以表示輔助索引和聚集索引的關系（圖片源自網絡，看大概意思即可）：

當通過輔助索引來尋找數據時，innodb存儲引擎會通過輔助索引葉子節點獲得只想主鍵索引的主鍵，既然后再通過主鍵索引找到完整的行記錄。

例如在一棵高度為3的輔助索引樹中查找數據，那需要對這顆輔助索引樹進行3次IO找到指定主鍵，如果聚集索引樹的高度同樣為3，那么還需要對聚集索引樹進行3次查找，最終找到一個完整的行數據所在的頁，因此一共需要6次IO訪問來得到最終的數據頁。

創建的索引，如聯合索引、唯一索引等，都屬于非聚簇索引。

聯合索引

聯合索引是指對表上的多個列進行索引。聯合索引也是一顆B+樹，不同的是聯合索引的鍵值數量不是1，而是大于等于2。

例如有user表，字段為id，age，name，現發現如下兩條sql使用頻率最多：

Select * from user where age = ？ ;
Select * from user where age = ? and name = ?;

這時候不需要為age和name單獨建兩個索引，只需要建如下一個聯合索引即可：

create index idx_age_name on user(age, name)

聯合索引的另一個好處已經對第二個鍵值進行了排序處理，有時候可以避免多一次的排序操作。

覆蓋索引

覆蓋索引，即從輔助索引中就可以得到查詢所需要的所有字段值，而不需要查詢聚集索引中的記錄。覆蓋索引的好處是輔助索引不包含整行記錄的所有信息，故其大小要遠小于聚集索引，因此可以減少大量的IO操作。

例如上面有聯合索引（age，name），如果如下：

select age，name from user where age=？

就能使用覆蓋索引了。

覆蓋索引的另一個好處是對于統計問題，例如：

select count(*) from user

innodb存儲引擎并不會選擇通過查詢聚集索引來進行統計。由于user表上還有輔助索引，而輔助索引遠小于聚集索引，選擇輔助索引可以減少IO操作。

注意事項

• 索引只建合適的，不建多余的

因為每當增刪數據時，B+樹都要進行調整，如果建立多個索引，多個B+樹都要進行調整，而樹越多、結構越龐大，這個調整越是耗時耗資源。如果減少了這些不必要的索引，磁盤的使用率可能會大大降低。

• 索引列的數據長度能少則少。

索引數據長度越小，每個塊中存儲的索引數量越多，一次IO獲取的值更多。

• 匹配列前綴可用到索引 like 9999%，like %9999%、like %9999用不到索引；
• Where 條件中in和or可以使用索引， not in 和 <>操作無法使用索引；

如果是not in或<>，面對B+樹，引擎根本不知道應該從哪個節點入手。

• 匹配范圍值，order by 也可用到索引；
• 多用指定列查詢，只返回自己想到的數據列，少用select *；

不需要查詢無用字段，并且不使用*可能還會命中覆蓋索引哦；

• 聯合索引中如果不是按照索引最左列開始查找，無法使用索引；

最左匹配原則；

• 聯合索引中精確匹配最左前列并范圍匹配另外一列可以用到索引；
• 聯合索引中如果查詢中有某個列的范圍查詢，則其右邊的所有列都無法使用索

感謝你能夠認真閱讀完這篇文章，希望小編分享mysql中innodb索引原理是什么內容對大家有幫助，同時也希望大家多多支持億速云，關注億速云行業資訊頻道，遇到問題就找億速云，詳細的解決方法等著你來學習!