青島免費建網(wǎng)站外貿(mào)google推廣
目錄
一、InnoDB中的B+ 樹索引介紹
二、聚簇索引
（一）使用記錄主鍵值的大小進行排序
?頁內(nèi)記錄排序
頁之間的排序
目錄項頁的排序
（二）葉子節(jié)點存儲完整的用戶記錄
?數(shù)據(jù)即索引
自動創(chuàng)建
（三）聚簇索引的優(yōu)缺點
三、二級索引
（一）二級索引的特點
基于非主鍵列排序
葉子節(jié)點存儲部分數(shù)據(jù)
（二）二級索引的工作流程
（三）二級索引的優(yōu)缺點
四、聯(lián)合索引
（一）聯(lián)合索引的特點
多列排序規(guī)則
聯(lián)合索引的組成
（二）聯(lián)合索引與單列索引的區(qū)別
聯(lián)合索引
單列索引
（三）聯(lián)合索引的優(yōu)缺點
（四）聯(lián)合索引的使用建議
五、總結(jié)
?參考文獻、書籍及鏈接
干貨分享，感謝您的閱讀！
在現(xiàn)代數(shù)據(jù)庫系統(tǒng)中，索引是提高數(shù)據(jù)檢索速度的關(guān)鍵機制之一。InnoDB作為MySQL的默認存儲引擎，采用了高效的B+樹結(jié)構(gòu)來實現(xiàn)其索引功能。這種結(jié)構(gòu)不僅確保了數(shù)據(jù)的快速檢索，還支持高效的插入、更新和刪除操作。理解InnoDB中的B+樹索引對于數(shù)據(jù)庫優(yōu)化和性能調(diào)優(yōu)至關(guān)重要。
為了更好地理解 InnoDB 中 B+ 樹索引的工作機制，我們從創(chuàng)建一個示例表index_demo開始，并通過詳細的示意圖展示記錄在頁中的存儲結(jié)構(gòu)及索引的作用。
CREATE TABLE index_demo (c1 INT,c2 INT,c3 CHAR(1),PRIMARY KEY (c1)
) ROW_FORMAT = Compact;
這個表中有兩個 INT 類型的列 c1 和 c2，一個 CHAR(1) 類型的列 c3，并且 c1 列為主鍵。表的行格式為 Compact。其基礎(chǔ)可見：
一、InnoDB中的B+ 樹索引介紹
B+ 樹索引是一種自平衡的樹結(jié)構(gòu)，其節(jié)點分為內(nèi)部節(jié)點和葉子節(jié)點：
- 內(nèi)部節(jié)點（Internal Nodes）：用于索引導(dǎo)航，存儲鍵值和指向子節(jié)點的指針。
- 葉子節(jié)點（Leaf Nodes）：存儲實際的數(shù)據(jù)記錄或指向數(shù)據(jù)記錄的指針（稱為記錄指針）。
在 B+ 樹中，所有的數(shù)據(jù)記錄都存儲在葉子節(jié)點中，而內(nèi)部節(jié)點僅用于存儲鍵值和導(dǎo)航信息。
不論是存放用戶記錄的數(shù)據(jù)頁，還是存放目錄項記錄的數(shù)據(jù)頁，我們都把它們存放到B+樹這個數(shù)據(jù)結(jié)構(gòu)中，所以我們也稱這些數(shù)據(jù)頁為節(jié)點。
從圖中可以看出來，我們的實際用戶記錄都存放在B+樹的最底層的節(jié)點上，這些節(jié)點也被稱為葉子節(jié)點或葉節(jié)點，其余用來存放目錄項的節(jié)點稱為非葉子節(jié)點或者內(nèi)節(jié)點，其中B+樹最上面的那個節(jié)點也稱為根節(jié)點。
依據(jù)InnoDB存儲引擎B+樹的樹高推導(dǎo)：當(dāng)樹高為4時，可以存放200百多億行數(shù)據(jù)。這樣的數(shù)據(jù)容量，可以滿足絕大部分應(yīng)用的需求，因此我們可以說在絕大部分應(yīng)用中，B+樹高度為3或4就可以滿足數(shù)據(jù)存儲的需求。B+樹這種高扇出低樹高的特征，也大大的提高了主鍵查詢性能。
二、聚簇索引
在InnoDB存儲引擎中，聚簇索引（Clustered Index）是數(shù)據(jù)存儲和索引的一種特殊而重要的結(jié)構(gòu)。聚簇索引主要特點：
（一）使用記錄主鍵值的大小進行排序
聚簇索引通過主鍵值對記錄和頁進行排序，這涉及三個方面：
?頁內(nèi)記錄排序
在每個頁內(nèi)，記錄按照主鍵值的大小順序排成一個單向鏈表，確保了頁內(nèi)記錄的有序性，方便快速查找。頁內(nèi)的記錄被劃分成若干個組，每個組中主鍵值最大的記錄在頁內(nèi)的偏移量會被當(dāng)作槽依次存放在頁目錄中（當(dāng)然Supermum記錄比任何用戶記錄都大），我們可以在頁目錄內(nèi)通過二分法定位到主鍵列等于某個值的記錄。
頁之間的排序
存放用戶記錄的頁按照頁內(nèi)記錄的主鍵大小順序排成一個雙向鏈表。這種結(jié)構(gòu)使得范圍查詢和順序掃描更加高效。
目錄項頁的排序
存放目錄項記錄的頁根據(jù)頁內(nèi)目錄項記錄的主鍵大小順序排成一個雙向鏈表。不同層次的頁同樣遵循這種排序規(guī)則，確保樹的平衡性和查詢效率。?
（二）葉子節(jié)點存儲完整的用戶記錄
B+樹的葉子節(jié)點存儲的是完整的用戶記錄，即包括所有列的值（包括隱藏列），在InnoDB中，葉子節(jié)點不僅僅是索引，還包含了實際的數(shù)據(jù)記錄。這種特性使得聚簇索引與普通索引有所不同。
數(shù)據(jù)即索引?
聚簇索引中的葉子節(jié)點存儲了完整的用戶記錄，因此聚簇索引就是數(shù)據(jù)的存儲方式。換句話說，索引即數(shù)據(jù)，數(shù)據(jù)即索引。
自動創(chuàng)建
在InnoDB存儲引擎中，聚簇索引會自動為每個表創(chuàng)建，并且不需要在MySQL語句中顯式使用INDEX語句去創(chuàng)建。通常情況下，聚簇索引是基于表的主鍵創(chuàng)建的。?
（三）聚簇索引的優(yōu)缺點
| 聚簇索引的優(yōu)點 | 聚簇索引的缺點 |
|---|---|
| 快速數(shù)據(jù)訪問：由于數(shù)據(jù)和索引存儲在一起，基于主鍵的查詢非常高效，不需要額外的索引查找。 | 插入和刪除成本較高：由于需要維護數(shù)據(jù)的有序性，插入和刪除操作可能需要移動大量記錄，導(dǎo)致性能開銷。 |
| 有序數(shù)據(jù)存儲：記錄按照主鍵順序存儲，適合范圍查詢和順序掃描，提高查詢性能。 | 更新成本較高：如果更新操作導(dǎo)致主鍵變化，會引發(fā)記錄的重新定位和頁的重新排序，影響性能。 |
聚簇索引是InnoDB存儲引擎中一種關(guān)鍵的索引類型，通過主鍵排序和存儲完整用戶記錄，提供了高效的數(shù)據(jù)訪問和有序的數(shù)據(jù)存儲。在優(yōu)化數(shù)據(jù)庫性能時，理解和合理使用聚簇索引可以顯著提升查詢和數(shù)據(jù)操作的效率。具體優(yōu)化可見：MySQL索引性能優(yōu)化分析。
三、二級索引
聚簇索引只能在搜索條件是主鍵值時才能發(fā)揮作用，因為B+樹中的數(shù)據(jù)都是按照主鍵進行排序的。那如果我們想以別的列作為搜索條件該咋辦呢？難道只能從頭到尾沿著鏈表依次遍歷記錄么？不，我們可以多建幾棵B+樹，不同的B+樹中的數(shù)據(jù)采用不同的排序規(guī)則。比方說我們用c2列的大小作為數(shù)據(jù)頁、頁中記錄的排序規(guī)則，再建一棵B+樹，效果如下圖所示：
在InnoDB存儲引擎中，除了聚簇索引（Clustered Index），我們還可以使用二級索引（Secondary Index）來提高非主鍵列上的查詢性能。二級索引是一種基于非主鍵列的B+樹結(jié)構(gòu)，用于快速定位數(shù)據(jù)記錄。
（一）二級索引的特點
基于非主鍵列排序
二級索引的B+樹結(jié)構(gòu)基于指定的非主鍵列進行排序，這包括以下幾個方面：
- 頁內(nèi)記錄排序：在每個頁內(nèi)，記錄按照指定列（例如c2列）的大小順序排成一個單向鏈表。
- 頁之間的排序：存放用戶記錄的頁按照頁內(nèi)記錄的指定列順序排成一個雙向鏈表。這種結(jié)構(gòu)便于快速范圍查詢和順序掃描。
- 目錄項頁的排序：存放目錄項記錄的頁根據(jù)頁內(nèi)目錄項記錄的指定列順序排成一個雙向鏈表，不同層次的頁同樣遵循這種排序規(guī)則。
葉子節(jié)點存儲部分數(shù)據(jù)
與聚簇索引不同，二級索引的葉子節(jié)點存儲的是索引列和主鍵列的值，而不是完整的用戶記錄。這種設(shè)計減少了存儲空間的占用，但在查詢過程中需要進行回表操作以獲取完整的用戶記錄。
（二）二級索引的工作流程
假設(shè)我們創(chuàng)建了一個基于c2列的二級索引，并通過c2列的值查找某些記錄，以查找c2列的值為4的記錄為例，查找過程如下：：
-
確定目錄項記錄頁
從根頁面開始，根據(jù)c2列的值
4定位到目錄項記錄所在的頁，通過頁44快速定位到目錄項記錄所在的頁為頁42（因為2 < 4 < 9）。 -
通過目錄項記錄頁確定用戶記錄真實所在的頁
在頁42中，根據(jù)c2列的值確定實際存儲用戶記錄的頁。由于c2列沒有唯一性約束，值為4的記錄可能分布在多個數(shù)據(jù)頁中。最終確定實際存儲用戶記錄的頁在頁34和頁35中（因為
2 < 4 ≤ 4）。 -
在真實存儲用戶記錄的頁中定位到具體的記錄
在頁34和頁35中定位到具體的記錄，但二級索引的葉子節(jié)點中僅存儲c2列和主鍵列c1的值。
-
回表操作
根據(jù)主鍵值到聚簇索引中查找完整的用戶記錄。這個過程稱為回表操作，即從二級索引定位到主鍵，再通過主鍵在聚簇索引中查找完整記錄。
（三）二級索引的優(yōu)缺點
| 二級索引的優(yōu)點 | 二級索引的缺點 |
|---|---|
| 提高查詢效率：基于非主鍵列的查詢可以利用二級索引快速定位數(shù)據(jù)，減少全表掃描的開銷。 | 回表操作：查詢完整記錄時需要回表操作，增加了一次I/O開銷。 |
| 靈活性：可以為多個列創(chuàng)建二級索引，提升多種查詢條件下的性能。 | 占用空間：雖然葉子節(jié)點不存儲完整記錄，但仍會占用額外的存儲空間。 |
二級索引通過基于非主鍵列排序和存儲索引列與主鍵列的值，為非主鍵列的查詢提供了高效的解決方案。然而，由于葉子節(jié)點僅存儲部分數(shù)據(jù)，查詢完整記錄時需要回表操作。因此，合理使用和配置二級索引，對于提升數(shù)據(jù)庫查詢性能至關(guān)重要。?具體優(yōu)化可見：MySQL索引性能優(yōu)化分析。
四、聯(lián)合索引
在InnoDB存儲引擎中，聯(lián)合索引（Composite Index）是一種基于多個列的索引，用于提高復(fù)雜查詢的效率。聯(lián)合索引通過對多個列進行排序，能夠更有效地處理包含多個條件的查詢。
同時以多個列的大小作為排序規(guī)則，也就是同時為多個列建立索引，比方說我們想讓B+樹按照c2和c3列的大小進行排序，這個包含兩層含義：
- 先把各個記錄和頁按照
c2列進行排序。 - 在記錄的
c2列相同的情況下，采用c3列進行排序
為c2和c3列建立的索引的示意圖如下：
如圖所示，我們需要注意一下幾點：
-
每條
目錄項記錄都由c2、c3、頁號這三個部分組成，各條記錄先按照c2列的值進行排序，如果記錄的c2列相同，則按照c3列的值進行排序。 -
B+樹葉子節(jié)點處的用戶記錄由c2、c3和主鍵c1列組成。
（一）聯(lián)合索引的特點
多列排序規(guī)則
聯(lián)合索引按照多個列的值進行排序，其排序規(guī)則包括以下兩個層次：
- 第一列排序：首先按照第一個指定列（例如c2列）的值進行排序。
- 第二列排序：在第一列相同的情況下，按照第二個指定列（例如c3列）的值進行排序。
在這個結(jié)構(gòu)中，每個目錄項記錄由c2、c3和頁號組成，葉子節(jié)點存儲c2、c3和主鍵c1。
聯(lián)合索引的組成
- 目錄項記錄：每條目錄項記錄由c2、c3和頁號組成，先按照c2列排序，如果c2列相同，則按照c3列排序。
- 葉子節(jié)點記錄：葉子節(jié)點處的用戶記錄包含c2、c3和主鍵c1列。這種結(jié)構(gòu)使得查詢包含c2和c3列的條件時更加高效。
（二）聯(lián)合索引與單列索引的區(qū)別
聯(lián)合索引
- 建立聯(lián)合索引會生成一棵B+樹，該樹按照c2和c3列進行排序。
- 查詢時，如果使用c2和c3作為條件，能夠快速定位記錄，減少查詢時間。
單列索引
- 為c2和c3分別建立索引會生成兩棵獨立的B+樹，每棵樹分別按照c2或c3進行排序。
- 查詢時，如果只使用c2或c3作為條件，可以利用相應(yīng)的索引。但如果同時使用c2和c3作為條件，可能需要進行多次索引查找和合并操作，增加查詢開銷。
（三）聯(lián)合索引的優(yōu)缺點
| 聯(lián)合索引的優(yōu)點 | 聯(lián)合索引的缺點 |
|---|---|
| 高效的多列查詢：聯(lián)合索引能夠顯著提高包含多個列條件的查詢性能。 | 插入和維護成本較高：由于需要對多個列進行排序和維護，插入和更新操作可能較慢。 |
| 減少單列索引的數(shù)量：通過一個聯(lián)合索引代替多個單列索引，可以節(jié)省存儲空間。 | 部分匹配限制：聯(lián)合索引在查詢中只能高效利用前綴列，如果查詢條件不包括索引的最左列，索引的利用率會降低。 |
（四）聯(lián)合索引的使用建議
前綴匹配原則
聯(lián)合索引在查詢中按照列的順序生效，因此查詢條件應(yīng)盡量包括索引的最左列（即前綴列）。例如，創(chuàng)建了(c2, c3)的聯(lián)合索引后，查詢條件包含c2或(c2, c3)時能夠有效利用索引。
適用場景
聯(lián)合索引適用于需要同時基于多個列進行查詢的場景。例如，在電商系統(tǒng)中，可以為商品類別和價格區(qū)間創(chuàng)建聯(lián)合索引，以優(yōu)化相關(guān)查詢。
聯(lián)合索引是InnoDB中一種重要的索引類型，通過對多個列進行排序和索引，提高了多列查詢的性能。與單列索引相比，聯(lián)合索引在處理復(fù)雜查詢時更加高效。然而，合理的索引設(shè)計和使用對于優(yōu)化數(shù)據(jù)庫性能至關(guān)重要。理解聯(lián)合索引的工作原理和最佳實踐，可以幫助我們更好地利用MySQL數(shù)據(jù)庫。??具體優(yōu)化可見：MySQL索引性能優(yōu)化分析。
五、總結(jié)
InnoDB中的索引是提高數(shù)據(jù)檢索效率的關(guān)鍵。本文介紹了三種主要索引類型：
- 聚簇索引：基于主鍵排序存儲完整的用戶記錄，適合快速主鍵查詢和范圍查詢。
- 二級索引：基于非主鍵列排序，提升非主鍵查詢性能，但需要回表操作。
- 聯(lián)合索引：基于多個列排序，適用于復(fù)雜查詢，能夠顯著提升多列條件查詢的效率。
通過合理使用和配置這些索引，能有效提升數(shù)據(jù)庫查詢和數(shù)據(jù)操作的性能。理解索引的工作機制和最佳實踐，對于優(yōu)化MySQL數(shù)據(jù)庫性能至關(guān)重要。
?參考文獻、書籍及鏈接
- 《MySQL技術(shù)內(nèi)幕：InnoDB存儲引擎》（第2版）：MySQL技術(shù)內(nèi)幕 (豆瓣)
- 《MySQL 是怎樣運行的：從根兒上理解 MySQL》
- 《Inside InnoDB: The InnoDB Storage Engine》：MySQL :: MySQL 8.0 Reference Manual :: 15 The InnoDB Storage Engine
- 《InnoDB: The Ultimate Guide》：https://www.percona.com/blog/2018/06/05/innodb-the-ultimate-guide/
- 《InnoDB Storage Engine Internals》：https://mariadb.com/kb/en/innodb-storage-engine-internals/
- InnoDB的數(shù)據(jù)頁結(jié)構(gòu)
- InnoDB存儲引擎B+樹的樹高推導(dǎo)_b+樹一般多少層-CSDN博客
- MySQL索引性能優(yōu)化分析_mysql索引和性能分析(實戰(zhàn))-CSDN博客