MySQL索引使用筆記

非常尛貝 2012-04-12

展開全文

http://www./?p=460

MySQL索引使用筆記

我們平常所說的索引，如果沒有特別指明，都是指B+樹結(jié)構(gòu)組織的B-Tree索引（http://blog.csdn.net/michaelrun/article/details/5617832）。其中聚集索引，次要索引，覆蓋索引，復(fù)合索引，前綴索引，唯一索引默認都是使用B+樹索引，統(tǒng)稱索引。當然，除了B+樹這種類型的索引之外，還有哈稀索引(hash index)等。

MySQL InnoDB B-Tree索引使用Tips

這里主要討論一下InnoDB B-Tree索引的使用，不提設(shè)計，只管使用。B-Tree索引主要作用于WHERE和ORDER BY子句。這里討論的均在MySQL-Server-5.1.38測試

CREATE TABLE `friends` (
`ID` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`uid` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
`fuid` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
`fname` varchar(50) NOT NULL DEFAULT '',
`fpicture` varchar(150) NOT NULL DEFAULT '',
`fsex` tinyint(1) NOT NULL DEFAULT '0',
`status` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
ALTER TABLE `friends` ADD INDEX uid_fuid (uid, fuid);

1.如果索引了多列，要遵守最左前綴法則。所謂最左前列，指的是查詢從索引的最左前列開始，并且不跳過索引中的列。

第2條語句,從索引的第二列開始查找，使用索引失敗，導(dǎo)致MySQL采用ALL訪問策略，即全表查詢.在開發(fā)中，應(yīng)該盡量避免全表查詢。
2.當MySQL一旦估計檢查的行數(shù)可能會”太多”，范圍查找優(yōu)化將不會被使用。

第2條語句使用了全表查詢，它與第1條語句唯一的區(qū)別在于需要檢查的行數(shù)遠遠多于第1條語句。在應(yīng)用中，可能不會碰到這么大的查詢，但是應(yīng)該避免這樣的查詢出現(xiàn): select uid from users where registered < 1295001384
3.索引列不應(yīng)該作為表達式的一部分，即也不能在索引列上使用函數(shù)

第2和3條語句都有使用表達式，索引派不上用場。
4.盡量借用覆蓋索引，減少select * from …語句使用

第1句Extra中使用了Using index表示使用了覆蓋索引。第3句也使用了覆蓋索引,雖然ID不在索引uid_fuid索引列中，但是InnoDB二次索引(second index)葉子頁的值就是PK值，不同于MyISAM。Extra部分的Using index表示應(yīng)用了索引，不要跟type中的index混淆。第2句沒有使用覆蓋索引，因為fsex不在索引中。
5.ORDER BY子句，盡量使用Index方式排序,避免使用FileSort方式排序
MySQL支持二種方式的排序，F(xiàn)ileSort和Index，后者效率高，它指MySQL掃描索引本身完成排序。FileSort方式效率較低。ORDER BY滿足以下情況，會使用Index方式排序:
a)ORDER BY 語句使用索引最左前列。參見第1句
b)使用Where子句與Order BY子句條件列組合滿足索引最左前列。參見第2句.
以下情況，會使用FileSort方式的查詢

a)檢查的行數(shù)過多，且沒有使用覆蓋索引。第3句，雖然跟第2句一樣，order by使用了索引最左前列uid，但依然使用了filesort方式排序，因為status并不在索引中，所以沒辦法只掃描索引。
b)使用了不同的索引，MySQL每回只采用一個索引.第4句,order by出現(xiàn)二個索引，分別是uid_fuid和聚集索引(pk)
c)對索引列同時使用了ASC和DESC。通過where語句將order by中索引列轉(zhuǎn)為常量，則除外。第5句,和第6句在order by子句中，都出現(xiàn)了ASC和DESC排序,但是第5句卻使用了filesort方式排序,是因為第6句where uid取出排序需要的數(shù)據(jù),MySQL將其轉(zhuǎn)為常量,它的ref列為const。
d)where語句與order by語句，使用了不同的索引。參見第8句。
e)where語句或者ORDER BY語句中索引列使用了表達式，包括函數(shù)表達式。參見第9句
f)where 語句與ORDER BY語句組合滿足最左前綴，但where語句中使用了條件查詢。查見第10句,雖然where與order by構(gòu)成了索引最左有綴的條件，但是where子句中使用的是條件查詢。
g)order by子句中加入了非索引列,且非索引列不在where子句中。
h)order by或者它與where組合沒有滿足索引最左前列。參見第11句和12句,where與order by組合，不滿足索引最左前列. （uid, fsex)跳過了fuid
i)當使用left join，使用右邊的表字段排序。參見第13句，盡管user.uid是pk，依然會使用filesort排序。

6.慎用left join語句,避免創(chuàng)建臨時表 使用left join語句的時候，避免出現(xiàn)創(chuàng)建臨時表。盡量不要用left join，分而治之。非要使用的時候，要詢問自己是不是真要必須要使用。

7.高選擇性索引列。盡量使用高選擇性的過引來過濾數(shù)據(jù)。高選擇性指Cardinality/#T越接近1，選擇性越高，其中Cardinality指表中索引列不重復(fù)值(行)的總數(shù)。PK和唯一索引，具有最高的選擇性，即1。推薦可選性達到20%以上。

這里有二個索引可供使用,而MySQL選擇PRIMARY，是因為它具有更高的選擇性。
8.謹防where子句中的OR。where語句使用or，且沒有使用覆蓋索引,會進行全表掃描。應(yīng)該盡量避免這樣OR語句。盡量使用UNION代替OR

第1句雖然使用了索引,但是查行時間依然不可以恭維，mysql要檢查的行很多,但是返回的行卻很少.Extra中的using where表示需要通過where子句扔棄不需要的數(shù)據(jù)行。
9.LIMIT與覆蓋索引 limit子句，使用覆蓋索引時比沒有使用覆蓋索引會快很多