目錄一、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博客