從萌新的角度理解Mysql索引基本原理

本文是針對Mysql索引原理剖析的入門級文章，主要圍繞以下四個話題展開對索引相關原理的描述。

• 一丶索引基本概念
• 二丶(B+)-Tree索引基本實現
• 三丶關於Mysql索引常見術語解疑(聚族索引，非聚族索引，最左字首原則, 索引覆蓋,雜湊索引,自適應雜湊索引)
• 四丶索引侷限性

一丶索引基本概念

對照著生活中的概念，資料庫索引的概念理解起來比較容易。資料庫的索引相當於書籍的目錄。

• 書籍目錄： 查閱書籍內容時可對照著目錄，直接定位到想查閱內容的具體頁數，避免逐頁翻書的工作量
• 資料庫索引: 某條查詢sql語句可以對照資料表中的索引，直接定位到符合查詢條件的資料行的實體地址，避免對資料表進行全表掃描的工作。 上述概念關於索引的概念很簡單，但是包含了很多的資訊量。進一步挖掘如下:
  1. 索引是建立資料表上的，每張資料表都可以有自己的索引。並且資料表中的索引可以有多個，但是不能設定重複的索引。
  2. 索引更具體一點來說，其是建立在資料表的相關列上, 畢竟只有索引建立在列上才能和查詢條件相關聯嘛。若索引在一個列上建立稱為單一索引，若索引在多個列上建立成為複合索引。在表table1中的col1，col2，col3列上建立名為indx1的索引的sql語句如下: create index idx1 on table1(col1,col2,col3) 這裡需要特別注意，索引和建立索引時的資料列順序有關，比如在col3，col2，col1這三個列上建立名為idx2的索引和上述idx1索引是不同的索引。這是一個非常重要的概念
  3. 索引是一個獨立於資料表的結構，就像書籍一樣，目錄和正文分屬於不同的部分。資料表的內容發生了改變了，那麼索引的結構也會發生相應調整，簡而言之，索引的更新和資料表內容的更新保持一致。 該條具體一點就是，在對建立了索引的相關列進行增刪改操作時，會附加維護索引結構的相關操作。未建立索引的列則不需要考慮這個效能消耗。
  4. 索引提高查詢的效能，索引通過避免全表掃描，減少掃描行的數量來提高查詢的效能。
  5. 對於一條簡單的查詢來說，是如何使用索引查詢的呢？ select * from table1 where col1 = A and col2 = B and col3 = C 這條查詢就利用idx1這個索引。目前來看，非常簡單查詢條件和索引列完全一致就可以利用索引完成查詢避免全表掃描。此處注意，查詢條件的順序和索引建立是的順序是一致的，後續的關於索引的最左字首原則會進一步描述，此處記住是一致的就行 ####二丶(B+)-Tree索引基本實現 (B+)-Tree是一個資料結構，是一個平衡的多路查詢二叉樹。Mysql innodb引擎中建立的索引預設就是基於(B+)-Tree實現的索引。
• B-Tree和(B+)-Tree資料結構基本概念 關於B-Tree和(B+)-Tree資料結構具體特性參見該篇文章，本文借用上篇文章中的圖來剖析B-Tree是如何構建索引的。

  

  

  上圖就是B+樹和B樹結構圖，非常清晰。B樹和B+樹之間有兩點最大的不同:

1. B+樹的葉子節點儲存了所有的資料，非葉子節點中儲存的是比較關鍵字。而B數所有的節點都會儲存資料。例如，在B+樹中查詢數字26的過程是 p1->p3->26,最終在葉子節點裡找到了待查詢數字26。在B樹中查詢數字26，查詢的順序是p2->26,在非葉子節點中查詢到了資料就返回。
2. B+樹的葉子節點之間存在一個指標連線，B樹不存在指標連線。B+樹這種設計結構能帶來什麼好處呢？ B+樹所有的資料都儲存在葉子節點，那麼順著葉子節點從左往右即可完成對資料的遍歷，極大了簡化了排序操作。這也是mysql設計索引是採用B+樹的原因，不僅僅能方便查詢，而且有助於排序，在mysql的索引中葉子節點之間數雙向連結串列可正反遍歷，更加靈活

• B+樹和Mysql索引之間關係 介紹關於B+樹資料結構的相關內容後，如何將其與索引聯絡在一起呢？請看下圖

  

  在一張資料表的整型id上建立一個索引，該索引對應的B+樹結構如上圖所示。在B+樹中通過主鍵之間的比較，最終在葉子節點將找到指向資料表中對應資料行的指標。通過訪問指標即可拿到需查詢的資料，通過這種方式可以避免對資料表全表掃描。極大的減少了檢索資料的時間

三丶關於Mysql常見術語的解疑

1. 聚族索引和非聚族索引

聚族索引和非聚族索引指的是儲存結構 Mysql中InnoDB儲存引擎是採用聚族索引的儲存方式，是在主鍵上建立的聚族索引，MyISAM則是非聚族索引的方式。下文引用《高效能Mysql第三版167頁的一張圖解釋下聚族索引和非聚族索引》

聚族索引: 資料表和索引檔案是儲存在一起的，位於同一檔案。如圖所示，以B+樹構建的索引，其葉子節點儲存了所有的行資訊。資料表中的所有資料全部儲存在索引的葉子節點中 非聚族索引: 資料表和索引檔案是分開儲存的，是兩個不同的檔案。B+樹的葉子節點，並不儲存行資訊，儲存的是資料行的實體地址。 InnoDB儲存引擎中非主鍵索引(二級索引)每個葉子節點除了儲存索引欄位外，額外儲存了主鍵列。通過二級索引檢索資料時，先檢索到主鍵列，最後通過主鍵列在聚族索引中檢索相應的資料行，這是一種二次檢索的過程。非聚族索引不存在這種過程

2. 索引覆蓋

理解索引的儲存結構後，理解索引的覆蓋就非常簡單了。如果select語句所查詢的欄位全部都是索引列的話，稱為索引覆蓋。

1. 對於聚族索引而言，如果滿足索引覆蓋，那麼不用通過主鍵訪問聚族索引。
2. 對於非聚族索引而言，如果滿足索引覆蓋，那麼不需要再次訪問資料表。 在滿足索引覆蓋的條件下，select語句從索引檔案從就可以拿到所查詢的資料，而不必訪問資料行。

3. 最左字首原則

最左字首原則就是Mysql通過索引檢索資料時必須遵守的原則。最左字首原則的內容規定如下，滿足如下情況，將使用索引查詢。

• 全值匹配，select語句中的查詢條件(查詢欄位和欄位順序)和索引完全對應。
• 匹配最左字首，select語句中的查詢條件並未和索引完全匹配。但是和索引最左側完全匹配。比如index(col1,col2,col3)，查詢條件(col1,col2)或者(col1)都成為匹配索引的最左字首。
• 匹配列字首，這是匹配最左字首長的特殊情況。查詢條件是匹配索引第一列的開頭部分。比如 like col1 = aaa%。匹配索引第一列與aaa開頭的資料行。
• 匹配範圍，針對索引的第一列，使用了範圍查詢。比如， col1<A。
• 精確匹配某一個列，範圍匹配某一列。比如col1 =A and col2<B。精確匹配的列必須是索引的最左列。 ######4. 雜湊索引 雜湊索引不同於以B+樹為儲存結構的索引。雜湊以雜湊為儲存結構組織索引。

  

  hash索引原理比較簡答，通過hash計算hashcode。hashcode = hash(col1,co2,..待索引列)。如果遇見hash衝突的話，可以鏈地址方法解決衝突。hashcode對應儲存的value值是相關行的實體地址。雜湊索引想比較於B+樹構建的索引，其有如下不同:
• Hash索引檢索資料的速度比B+樹索引更快
• Hash索引值只適用於全值匹配查詢，查詢條件和索引列必須完全一致。B+樹所適應的最左字首原則Hash索引並不適用
• Hash索引只能滿足精確匹配，比如查詢條件是==或者！=。並不能滿足範圍查詢的場景。 ######5. 自適應雜湊索引 自適應Hash索引是InnoDB儲存引擎新增的一種優化策略。InnoDB儲存引擎對那些查詢頻繁的索引條件，構建一個hash索引。下若有相同的查詢語句，則直接命中hash索引，而不必走B+樹索引，能提高檢索速度。自適應Hash索引是innoDB的一種優化策略，對使用者而言是透明的。

四丶索引的侷限性

有關索引侷限性的討論是一個比較有難度的話題，其不像原理分析那樣固定。其在不同的業務場景下會有不同的結論。本文論文幾種索引常見分析，並未涵蓋所有情況

1. 什麼情況需要建立索引？

• 當資料表較小時，維護索引的代價將超過索引加速查詢所帶來的好處。資料表較大時，索引能夠極大加速查詢，適合建立索引。
• 對於那些，查詢多於增刪改的操作，建立索引是合適的。

2. 在什麼列上建立索引

• 一般而言，選擇在選擇性比較高的列上建立索引。
• 選擇性 = 不重複的索引列/所有資料行數。選擇性越接近於1越好。

3. 索引建立的順序。

• 受限於索引的最左字首原則，索引建立的順序並不能是隨意的，應該和查詢場景相互印證。讓索引順序能滿足最多的查詢場景

4. 多列索引和多個單列索引

• 一般提供選擇建立多列索引，而不建立單例索引。多列索引能覆蓋單列索引的查詢條件。

5. 是否針對不同的查詢條件，建立不同的索引

• 索引的建立是有代價的，包括索引儲存代價，資料增刪改的效能下降。對不同查詢條件建立索引，需要仔細考慮。 受限於作者水平，關於索引侷限性的討論，並不一定正確。在不同的場景，具有不同的選擇。