小賴的實戰記錄

參考文章

http://article.denniswave.com/6337

http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html

http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

 

截取重點資訊如下，對於index有些瞭解，要進一步知道，如何下索引，或如何分析索引效率，可以快速知道重點部分。

最左前綴

多列索引還有另外一個優點，它通過稱為最左前綴（Leftmost Prefixing）的概念體現出來。繼續考慮前面的例子，現在我們有一個firstname、lastname、age列上的多列索引，我們稱這個索引為fname_lname_age。當搜索條件是以下各種列的組合時，MySQL將使用fname_lname_age索引：

firstname，lastname，age
firstname，lastname
firstname

 

我自己瞭解的狀況如下：

根據 leftmost prefixes 的狀況，

如果我的索引是colA,colB,colC

則(colA)或(colA,colB)或(colA,colB,colC)的狀況下，將會使用索引

若使用 colA 及colD查詢時，則會先查colA，減少需要查詢的row，再使用中結果集再繼續比對colD

 

分析索引效率

現在我們已經知道了一些如何選擇索引列的知識，但還無法判斷哪一個最有效。MySQL提供了一個內建的SQL命令幫助我們完成這個任務，這就是EXPLAIN命令。EXPLAIN命令的一般語法是：EXPLAIN <SQL命令>。你可以在MySQL文檔找到有關該命令的更多說明。下面是一個例子：

EXPLAIN SELECT peopleid FROM people WHERE firstname='Mike' AND lastname='Sullivan' AND age='17';

這個命令將返回下面這種分析結果：

table	type	possible_keys	key	key_len	ref	rows	Extra
people	ref	fname_lname_age	fname_lname_age	102	const,const,const	1	Where used

 

我實際使用狀況

我有一個SQL語句會是如下

select count(*) , colC from table where colA = 1 and colB = 0 group by colC

如果我下索引只有colA and colB  ，命名為  index_nameA

則他的

type =ALL ,

key = NULL ,

possible_keys=index_nameA

Extra = Using where ;Using temporary;Using filesort

結果，40萬筆的資料，需查5秒鐘的時間。如果不下index，則需要13秒的時間

修改index_nameA 為 (colA,colB,colC) 則得到3秒以內的查詢時間。分析結果會變如下

type =index, 

key = index_nameA, 

possible_keys=index_nameA

Extra = Using where ,Using index; Using temporary;Using filesort

 

當我有兩個index

一個 UNIQUE 及 一個 index_nameA

則 當查詢條件中有UNIQUE時，如colD 

select count(*) , colC from table where colA = 1 and colB = 0 and colD group by colC

他將以colD為優先

 

type 的值，可能有system,,const,eq_ref,ref , ref_or_null,unique_subquery,index_subquery,range,index,ALL

ref的值，可能有ref,null,const

參考

http://dev.mysql.com/doc/refman/4.1/en/explain-output.html