索引用于快速找出在某個列中有一特定值的行。不使用索引,MySQL必須從第1條記錄開始然后讀完整個表直到找出相關的行。表越大,花費的時間越多。如果表中查詢的列有一個索引,MySQL能快速到達一個位置去搜尋到數據文件的中間,沒有必要看所有數據。如果一個表有1000行,這比順序讀取至少快100倍。注意如果你需要訪問大部分行,順序讀取要快得多,因為此時我們避免磁盤搜索。
大多數MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)在B樹中存儲。只是空間列類型的索引使用R-樹,并且MEMORY表還支持hash索引。
字符串自動地壓縮前綴和結尾空格。參見13.1.4節,“CREATE INDEX語法”。
總的來說,按后面的討論使用索引。本節最后描述hash索引(用于MEMORY表)的特征。
索引用于下面的操作:
· 快速找出匹配一個WHERE子句的行。
· 刪除行。如果可以在多個索引中進行選擇,MySQL通常使用找到最少行的索引。
· 當執行聯接時,從其它表檢索行。
· 對具體有索引的列key_col找出MAX()或MIN()值。由預處理器進行優化,檢查是否對索引中在key_col之前發生所有關鍵字元素使用了WHERE key_part_# = constant。在這種情況下,MySQL為每個MIN()或MAX()表達式執行一次關鍵字查找,并用常數替換它。如果所有表達式替換為常量,查詢立即返回。例如:
· SELECT MIN(key_part2),MAX(key_part2)
· FROM tbl_name WHERE key_part1=10;
·
如果對一個可用關鍵字的最左面的前綴進行了排序或分組(例如,ORDER
BY key_part_1,key_part_2),排序或分組一個表。如果所有關鍵字元素后面有DESC,關鍵字以倒序被讀取。參見7.2.12節,“MySQL如何優化ORDER BY”。
· 在一些情況中,可以對一個查詢進行優化以便不用查詢數據行即可以檢索值。如果查詢只使用來自某個表的數字型并且構成某些關鍵字的最左面前綴的列,為了更快,可以從索引樹檢索出值。
· SELECT key_part3 FROM tbl_name
· WHERE key_part1=1
假定你執行下面的SELECT語句:
mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
如果col1和col2上存在一個多列索引,可以直接取出相應行。如果col1和col2上存在單列索引,優化器試圖通過決定哪個索引將找到更少的行來找出更具限制性的索引并且使用該索引取行。
如果表有一個多列索引,優化器可以使用最左面的索引前綴來找出行。例如,如果有一個3列索引(col1,col2,col3),則已經對(col1)、(col1,col2)和(col1,col2,col3)上的搜索進行了索引。
如果列不構成索引最左面的前綴,MySQL不能使用局部索引。假定有下面顯示的SELECT語句。
SELECT * FROM tbl_name WHERE col1=val1;
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
SELECT * FROM tbl_name WHERE col2=val2;
SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;
如果 (col1,col2,col3)有一個索引,只有前2個查詢使用索引。第3個和第4個查詢確實包括索引的列,但(col2)和(col2,col3)不是 (col1,col2,col3)的最左邊的前綴。
也可以在表達式通過=、>、>=、<、<=或者BETWEEN操作符使用B-樹索引進行列比較。如果LIKE的參數是一個不以通配符開頭的常量字符串,索引也可以用于LIKE比較。例如,下面的SELECT語句使用索引:
SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';
在第1個語句中,只考慮帶'Patrick' <=key_col < 'Patricl'的行。在第2個語句中,只考慮帶'Pat' <=key_col < 'Pau'的行。
下面的SELECT語句不使用索引:
SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE other_col;
在第一條語句中,LIKE值以一個通配符字符開始。在第二條語句中,LIKE值不是一個常數。
如果使用... LIKE '%string%'并且string超過3個字符,MySQL使用Turbo Boyer-Moore算法初始化字符串的模式然后使用該模式來更快地進行搜索。
如果col_name被索引,使用col_name IS NULL的搜索將使用索引。
任何不跨越WHERE子句中的所有AND級的索引不用于優化查詢。換句話說,為了能夠使用索引,必須在每個AND組中使用索引前綴。
下面的WHERE子句使用索引:
... WHERE index_part1=1 AND index_part2=2 AND other_column=3
/* index = 1 OR index = 2 */
... WHERE index=1 OR A=10 AND index=2
/* optimized like "index_part1='hello'" */
... WHERE index_part1='hello' AND index_part3=5
/* Can use index on index1 but not on index2 or index3 */
... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;
下面的WHERE子句不使用索引:
/* index_part1 is not used */
... WHERE index_part2=1 AND index_part3=2
/* Index is not used in both parts of the WHERE clause */
... WHERE index=1 OR A=10
/* No index spans all rows */
... WHERE index_part1=1 OR index_part2=10
有時MySQL不使用索引,即使有可用的索引。一種情形是當優化器估計到使用索引將需要MySQL訪問表中的大部分行時。(在這種情況下,表掃描可能會更快些,因為需要的搜索要少)。然而,如果此類查詢使用LIMIT只搜索部分行,MySQL則使用索引,因為它可以更快地找到幾行并在結果中返回。
Hash索引還有一些其它特征:
· 它們只用于使用=或<=>操作符的等式比較(但很快)。它們用于比較 操作符,例如發現范圍值的<。
· 優化器不能使用hash索引來加速ORDER BY操作。(該類索引不能用來按順序搜索下一個條目)。
· MySQL不能確定在兩個值之間大約有多少行(這被范圍優化器用來確定使用哪個索引)。如果你將一個MyISAM表改為hash-索引的MEMORY表,會影響一些查詢。
· 只能使用整個關鍵字來搜索一行。(用B-樹索引,任何關鍵字的最左面的前綴可用來找到行)。