Mysql中的B-Tree索引的底層結(jié)構(gòu)以及使用原則和特性

這篇文章主要介紹“Mysql中的B-Tree索引的底層結(jié)構(gòu)以及使用原則和特性”，在日常操作中，相信很多人在Mysql中的B-Tree索引的底層結(jié)構(gòu)以及使用原則和特性問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”Mysql中的B-Tree索引的底層結(jié)構(gòu)以及使用原則和特性”的疑惑有所幫助！接下來，請跟著小編一起來學(xué)習(xí)吧！

    MySQL是目前業(yè)界最為流行的關(guān)系型數(shù)據(jù)庫之一，而索引的優(yōu)化也是數(shù)據(jù)庫性能優(yōu)化的關(guān)鍵之一。所以，充分地了解MySQL索引有助于提升開發(fā)人員對MySQL數(shù)據(jù)庫的使用優(yōu)化能力。

    MySQL的索引有很多種類型，可以為不同的場景提供更好的性能。而B-Tree索引是最為常見的MySQL索引類型，一般談?wù)揗ySQL索引時，如果沒有特別說明，就是指B-Tree索引。本文就詳細(xì)講解一下B-Tree索引的底層結(jié)構(gòu)，使用原則和特性。

    為了節(jié)約你的時間，本文的主要內(nèi)容如下：

• - B-Tree索引的底層結(jié)構(gòu)

• B-Tree索引的使用規(guī)則

• 聚簇索引

• InnoDB和MyISAM引擎索引的差異

• 松散索引

• 覆蓋索引

B-Tree索引

    B-Tree索引使用B-Tree來存儲數(shù)據(jù)，當(dāng)然不同存儲引擎的實現(xiàn)方式不同。B-Tree通常意味著所有的值都是按順序存儲的，并且每一個葉子頁到根的距離相同，下圖展示了B-Tree索引的抽象表示，由此可以看出MySQL的B-Tree索引的大致工作機(jī)制。

    B-Tree索引的底層數(shù)據(jù)結(jié)構(gòu)一般是B+樹，其具體數(shù)據(jù)結(jié)構(gòu)和優(yōu)勢這里就不作詳細(xì)描述，下圖展示了B-樹索引的抽象表示，大致反應(yīng)了MyISAM索引是如何工作的，而InnoDB使用的結(jié)構(gòu)有所不同。

    MySQL可以在單獨一列上添加B-Tree索引，也可以在多列數(shù)據(jù)上添加B-Tree索引，多列的數(shù)據(jù)按照添加索引聲明的順序組合起來，存儲在B-Tree的頁中。假設(shè)有如下數(shù)據(jù)表：

    對于表中的每一行數(shù)據(jù)，索引中包含了last_name，first_name和birthday列的值，下圖展示了該索引是如何組織數(shù)據(jù)的存儲的。

    B-Tree索引使用B-Tree作為其存儲數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)，其使用的查詢規(guī)則也由此決定。一般來說，B-Tree索引適用于全鍵值、鍵值范圍和鍵前綴查找，其中鍵前綴查找只適用于根據(jù)最左前綴查找。B-Tree索引支持的查詢原則如下所示：

• 全值匹配：全值匹配指的是和索引中的所有列進(jìn)行匹配。

• 匹配最左前綴：前邊提到的索引可以用于查找所有姓Allen的人，即只使用索引中的第一列。

• 匹配列前綴：也可以只匹配某一列的值的開頭部分。例如前面提到的索引可用于查找所有以J開頭的姓的人。這里也只用到了索引的第一列。

• 匹配范圍值：例如前邊提到的索引可用于查找姓在Allen和Barrymore之間的人。這里也只使用了索引的第一列。

• 精確匹配某一列并范圍匹配另外一列：前邊提到的索引也可用于查找所有姓為Allen，并且名字是字母K開頭(比如Kim,Karl等)的人。即第一列l(wèi)ast_name全匹配，第二列first_name范圍匹配。

    因為索引樹的節(jié)點是有序的，所以除了按值查找之外，索引還可以用于查詢中的ORDER BY操作(按順序查找)，如果ORDER BY子句滿足前面列出的幾種查詢類型，則這個索引也可以滿足對應(yīng)的排序需求。

    下面是一些關(guān)于B-Tree索引的限制：

• 如果不是按照索引的最左列開始查找，則無法使用索引。例如上面例子中的索引無法查找名字為Bill的人，也無法查找某個特定生日的日，因為這兩列都不是最左數(shù)據(jù)列。

• 如果查詢中有某個列的范圍查詢，則其右側(cè)所有列都無法使用索引優(yōu)化查找。

聚簇索引

    聚簇索引并不是一種單獨的索引類型，而是一種數(shù)據(jù)存儲方式。具體的細(xì)節(jié)依賴于其實現(xiàn)方式，但是InnoDB的聚簇索引實際上在同一個結(jié)構(gòu)中保存了B-Tree索引和數(shù)據(jù)行。

    當(dāng)表有聚簇索引時，它的數(shù)據(jù)行實際上存放在索引的葉子頁中，這也就是說數(shù)據(jù)行和相鄰的鍵值緊湊地存儲在一起。

    下圖展示了聚簇索引中的記錄是如何存放的。注意到，葉子頁包含了行的全部數(shù)據(jù)行，但是節(jié)點頁只包含了索引列。

    聚簇索引可能對性能有幫助，但也可能導(dǎo)致嚴(yán)重的性能問題。聚簇的數(shù)據(jù)是有一些重要的優(yōu)點：

• 數(shù)據(jù)訪問更快，聚簇索引將索引和數(shù)據(jù)保存在同一個B-Tree中，因此從聚簇索引中獲取數(shù)據(jù)通常比在非聚簇索引中查找要快。

• 使用覆蓋索引掃描的查詢可以直接使用頁節(jié)點中的主鍵值。

    如果在設(shè)計表和查詢時能充分利用上面的優(yōu)點，那么就能極大地提升性能。同時，聚簇索引也有一些缺點：

• 插入順序嚴(yán)重依賴插入順序。按照主鍵的順序插入是向InnoDB表中插入數(shù)據(jù)速度最快的方式，需要避免主鍵鍵值隨機(jī)的(不連續(xù)且值得分布范圍非常大)聚簇索引，比如使用UUID作為主鍵，而應(yīng)該使用類似AUTO_INCREMENT的自增列。

• 更新聚簇索引列的代價很高，因為會強制InnoDB將每個被更新的行移動位置到新的位置。

• 基于聚簇索引的表在插入新行，或者主鍵被更新導(dǎo)致需要移動行時，可能面臨“頁分裂”的問題。當(dāng)行的主鍵值要求必須將這行插入到某個已滿的頁中時，存儲引擎會將該頁分裂成兩個頁面來容納該行，這就是一次頁分裂操作。頁分裂會導(dǎo)致表占用更多的磁盤空間。

• 二級索引可能比想象的更大，因為在二級索引中的葉節(jié)點包含了引用行的主鍵列。

• 二級索引訪問需要兩次索引查找，而不是一次。

InnoDB和MyISAM的索引區(qū)別

    聚簇索引和非聚簇索引的數(shù)據(jù)分布有區(qū)別，以及對應(yīng)的主鍵索引和二級索引的數(shù)據(jù)分布也有區(qū)別，通常會讓人感到困惑和意外。下圖展示了MyISAM和InnoDB的不同索引和數(shù)據(jù)存儲方式。

    MyISAM的數(shù)據(jù)分布非常簡單，按照數(shù)據(jù)插入的順序存儲在磁盤上，主鍵索引和二級索引的葉節(jié)點存儲著指針，指向?qū)?yīng)的數(shù)據(jù)行。

 InnoDB中，聚簇索引“就是”表，所以不會像MyISAM那樣需要獨立的行存儲。聚簇索引的每個葉節(jié)點都包含了主鍵值和所有的剩余列(在此例中是col2)。

    InnoDB的二級索引和聚簇索引很不同。InnoDB二級索引的葉節(jié)點中存儲的不是“行指針”，而是主鍵值，并以此作為指向行的“指針”。

松散索引掃描

    MySQL并不支持松散索引掃描，也就是無法按照不連續(xù)的方式掃描一個索引。通常，MySQL的索引掃描需要先定義一個起點和終點，即使需要的數(shù)據(jù)只是這段索引中很少數(shù)的幾個，MySQL仍然需要掃描這段索引中的每個條目。

    下面，我們通過一個示例說明這點，假設(shè)我們有如下索引(a,b)，有下面的查詢：

    因為索引的前導(dǎo)字段是列a，但是在查詢中只指定了字段b，MySQL無法使用這個索引，從而只能通過全表掃描找到匹配的行，如下圖所示。

    了解索引的物理結(jié)構(gòu)的話，不難發(fā)現(xiàn)還可以有一個更快的辦法執(zhí)行上面的查詢。索引的物理結(jié)構(gòu)(不是存儲引擎的API)是的可以先掃描a列第一個值對應(yīng)的b列的范圍，然后再跳到a列第二個不不同值掃描對應(yīng)的b列的范圍。下圖展示了如果由MySQL來實現(xiàn)這個過程會怎樣。

    注意到，這時就無須再使用WHERE子句過濾，因為松散索引掃描已經(jīng)跳過了所有不需要的記錄。

    MySQL 5.0之后的版本，在某些特殊的場景下是可以使用松散索引掃描的，例如，在一個分組查詢中需要找到分組的最大值和最小值：

    在EXPLAIN中的Extra字段顯示"Using index for group-by"，表示這里將使用松散索引掃描。

覆蓋索引

    索引除了是一種查找數(shù)據(jù)的高效方式之外，也是一種列數(shù)據(jù)的直接獲取方式。MySQL可以使用索引來直接獲取列的數(shù)據(jù)，這樣就不需要讀取數(shù)據(jù)行。如果一個索引包含所有需要查詢的字段的值，我們就稱之為“覆蓋索引”。

    覆蓋索引是非常有用的工具，能夠極大地提高性能。SQL查詢只需要掃描索引而無需回表，會帶來很多好處：

• 索引條目數(shù)量和大小通常遠(yuǎn)小于數(shù)據(jù)行的條目和大小，所以如果只需要讀取索引，那么MySQL就會極大地減少數(shù)據(jù)訪問量。

• 因為索引是按照列順序存儲的，所以對于I/O密集型的范圍查找會比隨機(jī)從磁盤讀取每一行數(shù)據(jù)的I/O要少的多。

• 由于InnoDB的聚簇索引，覆蓋索引對InnoDB表特別有用。InnoDB的二級索引在葉子節(jié)點中保存了行的主鍵，索引如果二級主鍵能夠覆蓋查詢，則避免對主鍵索引的第二次查詢。

    當(dāng)發(fā)起一個被覆蓋索引的查詢(也叫索引覆蓋查詢)時，在EXPLAIN的Extra列可以看到"Using Index"的信息。例如，表sakila.inventory有一個多列索引(store_id, film_id)。MySQL如果只需要訪問這兩列，就可以使用這個索引做覆蓋索引，如下所示：

到此，關(guān)于“Mysql中的B-Tree索引的底層結(jié)構(gòu)以及使用原則和特性”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識，請繼續(xù)關(guān)注億速云網(wǎng)站，小編會繼續(xù)努力為大家?guī)砀鄬嵱玫奈恼拢?/p>