MySQL 索引最佳化 btree hash rtree

一、MySQL索引類型

mysql裡目前只支援4種索引分別是：full-text，b-tree，hash，r-tree

b-tree索引應該是mysql裡最廣泛的索引的了，除了archive基本所有的儲存引擎都支援它.

1. full-text索引

full-text在mysql裡僅有myisam支援它，而且支援full-text的欄位只有char、varchar、text資料類型。

full-text主要是用來代替like "%***%"效率低下的問題

2. b-tree索引

b-tree在myisam裡的形式和innodb稍有不同

在 innodb裡，有兩種形態：一是primary key形態，其leaf node裡存放的是資料，而且不僅存放了索引鍵的資料，還存放了其他欄位的資料。二是secondary index，其leaf node和普通的b-tree差不多，只是還存放了指向主鍵的資訊.

而在myisam裡，主鍵和其他的並沒有太大區別。不過和innodb不太一樣的地方是在myisam裡，leaf node裡存放的不是主鍵的資訊，而是指向資料檔案裡的對應資料行的資訊.

3. hash索引

目前我所知道的就只有memory和ndb cluster支援這種索引.

hash索引由於其結構，所以在每次查詢的時候直接一次到位，不像b-tree那樣一點點的前進。所以hash索引的效率高於b-tree，但hash也有缺點，主要如下：

(1)由於存放的是hash值，所以僅支援<=>以及in操作.

(2)hash索引無法通過操作索引來排序，這是因為存放的時候經過hash計算，但是計算的hash值和存放的不一定相等，所以無法排序.

(3)在組合所以裡，無法對部分使用索引.

(4)不能避免全表掃描，只是由於在memory表裡支援非唯一值hash索引，就是不同的索引鍵，可能存在相同的hash值.

(5)當存在大量相同hash值得時候，hash索引的效率會變低.

4. r-tree索引

r-tree在mysql很少使用，僅支援geometry資料類型，支援該類型的儲存引擎只有myisam、bdb、innodb、ndb、archive幾種。

相對於b-tree，r-tree的優勢在於範圍尋找.

二、mysql裡sql語句值得注意的地方

1. myisam裡所有鍵的長度僅支援1000位元組，innodb是767.

2. blob和text欄位僅支援首碼索引.

3. 使用!=以及<>不等於的時候，mysql不使用索引.

4. 當在欄位時候函數的時候，mysql無法使用索引；在join時條件欄位類型不一致的時候，mysql無法使用索引；在複合式索引裡使用非第一個索引時也不使用索引.

5. 在使用like的時候，以%開頭，即"%***"的時候無法使用索引；在使用or的時候，要求or前後欄位都有索引.

有時候mysql query optimizer會認為使用索引並不是最優計劃,所以不使用索引。可以在sql語句裡可以用use,force index,當然有時候使用也不會比不用快,所以需要忽略掉index方法是ignore index.

關閉查詢快取sql_no_cache

select sql_no_cache * from table_name;

這樣可以讓一些很少使用的語句不放在緩衝裡，尋找的時候不會去緩衝裡找；對應的是強制緩衝sql_cache

select sql_cache * from table_name;

另外，在my.cnf中如果設定query_cache_type=2的話，那麼只有在使用sql_cache後才會使用緩衝;

還有mysql裡的優先操作hight_priority讓mysql優先操作這個語句

select high_priority * fromtable_name;

與其對應的是low_priority;

mysql裡還有延時插入insert delayed

insert delayed into table_name....; 

#當提交之後，mysql返回ok，但不立即插入，而是當mysql有空再插入。假如等待時伺服器崩潰，那麼所有資料丟失，並且插入不會返回自增id.

三、幾個技巧

1. 強制串連順序： STRAIGHT_JOIN

SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1 STRAIGHT_JOIN TABLE2 WHERE …

由上面的SQL語句可知，通過STRAIGHT_JOIN強迫MySQL按TABLE1、TABLE2的順序串連表。如果你認為按自己的順序比MySQL推薦的順序進行串連的效率高的話，就可以通過STRAIGHT_JOIN來確定串連順序。

2. 強制使用暫存資料表： SQL_BUFFER_RESULT

SELECT SQL_BUFFER_RESULT * FROM TABLE1 WHERE …

當我們查詢的結果集中的資料比較多時，可以通過SQL_BUFFER_RESULT，選項強制將結果集放到暫存資料表中，這樣就可以很快地釋放MySQL的表鎖（這樣其它的SQL語句就可以對這些記錄進行查詢了），並且可以長時間地為用戶端提供大記錄集。

3. 分組使用暫存資料表 SQL_BIG_RESULT和SQL_SMALL_RESULT

SELECT SQL_BUFFER_RESULT FIELD1, COUNT(*) FROM TABLE1 GROUP BY FIELD1;

一般用於分組或DISTINCT關鍵字，這個選項通知MySQL，如果有必要，就將查詢結果放到暫存資料表中，甚至在暫存資料表中進行排序。SQL_SMALL_RESULT比起SQL_BIG_RESULT差不多，很少使用。

轉載聲明： 本文轉自 http://apps.hi.baidu.com/share/detail/23985447