Mysql中的Btree與Hash索引比較

mysql最常用的索引結構是btree(O(log(n)))，但是總有一些情況下我們為了更好的效能希望能使用別的型別的索引。hash就是其中一種選擇，例如我們在透過使用者名稱檢索使用者id的時候，他們總是一對一的關係，用到的運算子只是=而已，假如使用hash作為索引資料結構的話，時間複雜度可以降到O(1)。不幸的是，目前的mysql版本(5.6)中，hash只支援MEMORY和NDB兩種引擎，而我們最常用的INNODB和MYISAM都不支援hash型別的索引。

不管怎樣，還是要了解一下這兩種索引的區別，下面翻譯自mysql官網文件中對這兩者的解釋。 

B-Tree 索引特徵

B-Tree索引可以被用在像=,>,>=,

複製程式碼 程式碼如下:
SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';

下面這兩種情況不會使用索引：

複製程式碼 程式碼如下:
SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE other_col;

第一條是因為它以萬用字元開頭，第二條是因為沒有使用常量。

假如你使用... LIKE '%string%'而且string超過三個字元，MYSQL使用Turbo Boyer-Moore algorithm演算法來初始化查詢表示式，然後用這個表示式來讓查詢更迅速。

一個這樣的查詢col_name IS NULL是可以使用col_name的索引的。

任何一個沒有覆蓋所有WHERE中AND級別條件的索引是不會被使用的。也就是說，要使用一個索引，這個索引中的第一列需要在每個AND組中出現。

下面的WHERE條件會使用索引：

複製程式碼 程式碼如下:
... WHERE index_part1=1 AND index_part2=2 AND other_column=3
   
... WHERE index=1 OR A=10 AND index=2
   
... WHERE index_part1='hello' AND index_part3=5
   
... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;

下面的WHERE條件不會使用索引：

複製程式碼 程式碼如下:
   
... WHERE index_part2=1 AND index_part3=2

   
... WHERE index=1 OR A=10

   
... WHERE index_part1=1 OR index_part2=10

有時候mysql不會使用索引，即使這個在可用的情況下。例如當mysql預估使用索引會讀取大部分的行資料時。（在這種情況下，一次全表掃描可能比使用索引更快，因為它需要更少的檢索）。然而，假如語句中使用LIMIT來限定返回的行數，mysql則會使用索引。因為當結果行數較少的情況下使用索引的效率會更高。

Hash 索引特徵

Hash型別的索引有一些區別於以上所述的特徵：

1.它們只能用於對等比較，例如=和運算子（但是快很多）。它們不能被用於像2.最佳化器不能用hash索引來為ORDER BY運算子加速。（這類索引不能被用於搜尋下一個次序的值）
3.mysql不能判斷出兩個值之間有多少條資料（這需要使用範圍查詢運算子來決定使用哪個索引）。假如你將一個MyISAM錶轉為一個依靠hash索引的MEMORY表，可能會影響一些語句（的效能）。
4.只有完整的鍵才能被用於搜尋一行資料。（假如用B-tree索引，任何一個鍵的片段都可以用於查詢。我覺得可能意味著帶萬用字元LIKE運算子會不起作用）。

後記

順便記錄一下在使用mysql過程中碰到的一些問題：

有時候使用指令碼遷移資料時會碰到亂碼的問題，即使將表字符集設定成utf8也無濟於事，這個時候在執行sql之前加一句set names utf8即可。