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 �99%、like �99用不到索引；
• Where 條件中in和or可以使用索引， not in 和操作無法使用索引；

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

• 符合範圍值，order by 也可用到索引；
• 多用指定列查詢，只傳回自己想到的資料列，少用select *;

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

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

最左匹配原則；

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

#

以上是mysql innodb索引原理的詳細介紹（程式碼範例）的詳細內容。更多資訊請關注PHP中文網其他相關文章！