mysql 索引B-Tree類型對索引使用的生效和失效情況詳解

標籤：生日   如何   效能   blank   表示   頭部   術語   開始   erb   

詳見：http://blog.yemou.net/article/query/info/tytfjhfascvhzxcyt343

 

當人們談論索引的時候，如果沒有特別指明類型 ，那多半說的是 B-Tree 索引，它使用B-Tree資料結構來儲存資料。大多數 MySQL引擎都支援這種索引 。Archive引擎是 一個例外 ：5.1 之前 Archive 不支援任何索引 ，直到 5.1 才開始支援單個自增列 （ A UTO INCREMENT ） 的索引。

我們使用術語＂B-Tree ” ，是因為 MySQL 在 CREATE TABLE 和其他語句中也使用該關鍵字 。

  不過，底層的儲存引擎也可能使用不同的儲存結構，例如，NDB叢集儲存引擎內部實際上使用了T-Tree結構儲存這種索引，即使其名字是BTREE1InnoDB則使用的是B+Tree,各種資料結構和算榕的變種不在本書的討論範圍之內。儲存引擎以不同的方式使用B-Tree索引，效能也各有不同，各有優劣。例如，MyISAM使用首碼壓縮技術使得索引更小，但InnoDB則按照原資料格式進行儲存。再如MyISAM索引通過資料的物理位置引用被索引的行，而InnoDB則根據主鍵引用被索引的行。B-Tree通常意味著所有的值都是按順序儲存的，井且每一個葉子頁到根的距離相同。圖5-l展示了B-Tree索引的抽象表示，大致反映了InnoDB索引是如何工作的。MyISAM使用的結構有所不同，但基本思想是類似的。

  B-Tree索引能夠加快訪問資料的速度，因為儲存引擎不再需要進行全表掃描來擷取需要的資料，取而代之的是從索引的根節點（圖示並未畫出）開始進行搜尋。根節點的槽中存放了指向子節點的指標，儲存引擎根據這些指標向下層尋找。通過比較節點頁的值和要尋找的值可以找到合適的指標進入下層子節點，這些指標實際上定義了子節點頁中值的上限和下限。最終儲存引擎要麼是找到對應的值，要麼該記錄不存在。

 

葉子節點比較特別，它們的指標指向的是被索引的資料，而不是其他的節點頁（不同引擎的“指標”類型不同）。圖5-1中僅繪製了一個節點和其對應的葉子節點，其實在根節點和葉子節點之間可能有很多層節點頁。樹的深度和表的大小直接相關。

 

B-Tree對索引列是順序組織儲存的，所以很適合尋找範圍資料。例如，在一個基於文本域的索引樹上，按字母順序傳遞連續的值進行尋找是非常合適的，所以像“找出所有以I到K開頭的名字”這樣的尋找效率會非常高。

假設有如下資料表：

1234567

CREATE TABLE People (last_name VARCHAR (32) NOT NULL,first_name VARCHAR (32) NOT NULL,dob date NOT NULL,gender enum (‘m‘, ‘f‘) NOT NULL,KEY (last_name, f irst_name, dob))

對於表中的每一行資料，索引中包含了last_name、first_name和dob列的值，圖5-2顯示了該索引是如何組織資料的儲存的。

請注意，索引對多個值進行排序的依據是四日.TETABLE語句中定義索引時列的順序。看一下最後兩個條目，兩個人的姓和名都一樣，則根據他們的出生日期來排列順序。

 

可以使用B-Tree索引的查詢類型。B-Tree索引適用於全索引值、索引值範圍或鍵首碼尋找其中鍵首碼尋找只適用於根據最左首碼的尋找。前面所述的索引對如下類型的查詢有效。

 

全值匹配

全值匹配指的是和索引中的所有列進行匹配，例如前面提到的索引可用於尋找姓名為CubaAllen、出生於1960-01-01的人。

匹配最左前級

前面提到的索引可用於尋找所有姓為Allen的人，即只使用索引的第一列。匹配列前級也可以只匹配某一列的值的開頭部分。例如前面提到的索引可用於尋找所有以J開頭的姓的人。這裡也只使用了索引的第一列。

匹配範圍值

例如前面提到的索引可用於尋找姓在Allen和Barrymore之間的人。這裡也只使用了索引的第一列。

精確匹配呆一列並範圍匹配另一列

前面提到的索引也可用於尋找所有姓為Allen，並且名字是字母K開頭（比如Kim、Karl等）的人。即第一列last_name全匹配，第二列first_name範圍匹配。

只訪問索引的查詢

B-Tree通常可以支援“只訪問索引的查詢”，即查詢只需要訪問索引，而無須訪問資料行。後面我們將單獨討論這種“覆蓋索引”的最佳化。

 

因為索引樹中的節點是有序的，所以除了按值尋找之外，索引還可以用於查詢中的ORDERBY操作（按順序尋找）。一般來說，如果B-Tree可以按照某種方式尋找到值，那麼也可以按照這種方式用於排序。所以，如果0陽ERBY子句滿足前面列出的幾種查詢類型，則這個索引也可以滿足對應的排序需求。

 

下面是一些關於B-Tree索引的限制：

 

?如果不是按照索引的最左列開始尋找，則無法使用索引。例如上面例子中的索引在每用於尋找名字為Bill的人，也無怯尋找某個特定生日的人，因為這兩列都不是最左資料列。類似地，也無戰尋找姓氏以某個字母結尾的人。

 

?不能跳過索引中的列。也就是說，前面所述的索引無法用於尋找姓為Smith並且在某個特定日期出生的人。如果不指定名（first_name），則MySQL只能使用索引的第一列。

 

?如果查詢中有某個列的範圍(like  between > < 都算範圍查詢)查詢，則其右邊所有列都無法使用索引最佳化尋找。例如有查詢 WHERE lastname=‘Smith’AND firstname like ‘%J%‘AND dob＝’1976-12-23＇，這個查詢只能使用索引的前兩列，因為這裡的like是一個範圍條件（但是伺服器可以把其餘列用於其他目的）。如果範圍查詢列值的數量有限，那麼可以通過使用多個等於條件來代替範圍條件。在本章的索引案例學習部分，我們將示範一個詳細的案例。

 

到這裡讀者應該可以明白，前面提到的索引列的順序是多麼的重要：這些限制都和索引列的順序有關。在最佳化效能的時候，可能需要使用相同的列但順序不同的索引來滿足不同類型的查詢需求。

 

也有些限制並不是B-Tree本身導致的，而是MySQL最佳化器和儲存引擎使用索引的方式導致的，這部分限制在未來的版本中可能就不再是限制了。

mysql 索引B-Tree類型對索引使用的生效和失效情況詳解