高性能的MySQL（5）建立高性能的索引一B-Tree索引

一、索引的類型

MySQL中，索引是在存儲引擎層實作的，而不是伺服器層，是以沒有統一的标準。

MySQL支援的索引類型如下：

1、B-Tree索引（也包括B+Tree索引，統稱為B-Tree索引，隻是資料結構上的不同，特性上是一樣的）

使用B-Tree資料結構來存儲資料，實際上很有存儲引擎使用的是B+Tree。關于BTree、B-Tree、B+Tree的差別請看本部落格的附件。

InnoDB就是使用的B+Tree索引。

B-Tree通常意味着所有的值都是按順序存儲的，并且每一個葉子頁到跟的距離相同。

B-Tree對索引列是順序組織存儲的，是以很适合查找範圍資料。

B-Tree索引适合用于全鍵值、鍵值範圍或鍵字首查找，其中鍵字首查找隻适合用于根據最左字首查找。

舉例說明：

<a href="http://blog.51cto.com/attachment/201310/113840753.png" target="_blank"></a>

資料如下：

<a href="http://blog.51cto.com/attachment/201310/114521246.png" target="_blank"></a>

a、全值比對

b、比對最左字首

隻使用索引的第一列

<a href="http://blog.51cto.com/attachment/201310/120120123.png" target="_blank"></a>

c、比對列字首

<a href="http://blog.51cto.com/attachment/201310/120505768.png" target="_blank"></a>

d、比對值範圍

<a href="http://blog.51cto.com/attachment/201310/130301192.png" target="_blank"></a>

e、精确比對某一列并範圍比對另外一列

<a href="http://blog.51cto.com/attachment/201310/130554513.png" target="_blank"></a>

f、隻通路索引的查詢

B-Tree通常可以支援“隻通路索引的查詢”，即查詢隻需要通路索引，而無須通路資料行，這個在後面單獨讨論這種“覆寫索引”的優化。

因為B-Tree是按順序存儲的，是以還可以用于查詢中的排序操作order by，是以order by 如果滿足前面列出的幾種查詢類型，則這個索引也可以滿足對應的排序需求。

B-Tree索引的限制，無法用到索引的說明

a、不是按照最左列開始查找，則無法使用到索引。

<a href="http://blog.51cto.com/attachment/201310/131416461.png" target="_blank"></a>

當然dob字段也是一樣的，即使是最左列也無法使用索引查找“%a”的格式

<a href="http://blog.51cto.com/attachment/201310/131741565.png" target="_blank"></a>

b、不能跳過中間的列，否則隻能使用到索引的第一列

<a href="http://blog.51cto.com/attachment/201310/132745270.png" target="_blank"></a>

c、如果查詢中有某個列的範圍查詢，則其右邊的列無法使用索引優化，左邊可以用到索引

<a href="http://blog.51cto.com/attachment/201310/150023855.png" target="_blank"></a>

這裡顯示都沒有用到索引，是因為資料太少，可能引擎有自己的優化，增加資料到了1000條以上可以看看效果：可以看到的卻查詢影響的條數一樣，的卻是沒有用到右側的索引。

<a href="http://blog.51cto.com/attachment/201310/195111951.png" target="_blank"></a>

而實際的條數隻有更少

<a href="http://blog.51cto.com/attachment/201310/195518429.png" target="_blank"></a>

是以，索引的順序是很重要的，在優化性能的時候，可能需要使用相同的列但順序不同的索引來滿足不同類型的查詢。

<a href="http://down.51cto.com/data/2363620" target="_blank">附件：http://down.51cto.com/data/2363620</a>

本文轉自shayang8851CTO部落格，原文連結：http://blog.51cto.com/janephp/1309667，如需轉載請自行聯系原作者