mysql index索引使用方法

MYSQL如果使用索引(from mysql reference)

索引用於快速找出在某個列中有一特定值的行。不使用索引，MySQL必須從第1條記錄開始然後讀完整個表直到找出相關的行。表越大，花費的時間越多。如果表中查詢的列有一個索引，MySQL能快速到達一個位置去搜尋到數據文件的中間，沒有必要看所有數據。如果一個表有1000行，這比順序讀取至少快100倍。注意如果你需要訪問大部分行，順序讀取要快得多，因為此時我們避免磁盤搜索。 大多數MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)在B樹中存儲。只是空間列類型的索引使用R-樹，並且MEMORY表還支持hash索引。 字符串自動地壓縮前綴和結尾空格。 總的來說，按後面的討論使用索引。本節最後描述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-樹索引，任何關鍵字的最左面的前綴可用來找到行）。 EXPLAIN使用方法 EXPLAIN tbl_name 或： EXPLAIN [EXTENDED] SELECT select_options EXPLAIN語句可以用作DESCRIBE的一個同義詞，或獲得關於MySQL如何執行SELECT語句的信息： ?         EXPLAIN tbl_name是DESCRIBE tbl_name或SHOW COLUMNS FROM tbl_name的一個同義詞。 ?         如果在SELECT語句前放上關鍵詞EXPLAIN，MySQL將解釋它如何處理SELECT，提供有關表如何聯接和聯接的次序。 該節解釋EXPLAIN的第2個用法。 借助於EXPLAIN，可以知道什麼時候必須為表加入索引以得到一個使用索引來尋找記錄的更快的SELECT。 如果由於使用不正確的索引出現了問題，應運行ANALYZE TABLE更新表的統計（例如關鍵字集的勢），這樣會影響優化器進行的選擇。 還可以知道優化器是否以一個最佳次序聯接表。為了強制優化器讓一個SELECT語句按照表命名順序的聯接次序，語句應以STRAIGHT_JOIN而不只是SELECT開頭。 EXPLAIN為用於SELECT語句中的每個表返回一行信息。表以它們在處理查詢過程中將被MySQL讀入的順序被列出。MySQL用一遍掃描多次聯接（single-sweep multi-join）的方式解決所有聯接。這意味著MySQL從第一個表中讀一行，然後找到在第二個表中的一個匹配行，然後在第3個表中等等。當所有的表處理完後，它輸出選中的列並且返回表清單直到找到一個有更多的匹配行的表。從該表讀入下一行並繼續處理下一個表。 當使用EXTENDED關鍵字時，EXPLAIN產生附加信息，可以用SHOW WARNINGS浏覽。該信息顯示優化器限定SELECT語句中的表和列名，重寫並且執行優化規則後SELECT語句是什麼樣子，並且還可能包括優化過程的其它注解。 EXPLAIN的每個輸出行提供一個表的相關信息，並且每個行包括下面的列： ?         id SELECT識別符。這是SELECT的查詢序列號。 ?         select_type SELECT類型，可以為以下任何一種： o        SIMPLE 簡單SELECT(不使用UNION或子查詢) o        PRIMARY 最外面的SELECT o        UNION UNION中的第二個或後面的SELECT語句 o        DEPENDENT UNION UNION中的第二個或後面的SELECT語句，取決於外面的查詢 o        UNION RESULT UNION的結果。 o        SUBQUERY 子查詢中的第一個SELECT o        DEPENDENT SUBQUERY 子查詢中的第一個SELECT，取決於外面的查詢 o        DERIVED 導出表的SELECT(FROM子句的子查詢) ?         table 輸出的行所引用的表。 ?         type 聯接類型。下面給出各種聯接類型，按照從最佳類型到最壞類型進行排序： o        system 表僅有一行(=系統表)。這是const聯接類型的一個特例。 o        const 表最多有一個匹配行，它將在查詢開始時被讀取。因為僅有一行，在這行的列值可被優化器剩余部分認為是常數。const表很快，因為它們只讀取一次！ const用於用常數值比較PRIMARY KEY或UNIQUE索引的所有部分時。在下面的查詢中，tbl_name可以用於const表：

SELECT * from tbl_name WHERE primary_key=1； 
SELECT * from tbl_name WHERE primary_key_part1=1 and  primary_key_part2=2；

o        eq_ref 對於每個來自於前面的表的行組合，從該表中讀取一行。這可能是最好的聯接類型，除了const類型。它用在一個索引的所有部分被聯接使用並且索引是UNIQUE或PRIMARY KEY。 eq_ref可以用於使用= 操作符比較的帶索引的列。比較值可以為常量或一個使用在該表前面所讀取的表的列的表達式。 在下面的例子中，MySQL可以使用eq_ref聯接來處理ref_tables：

 

o        ref 對於每個來自於前面的表的行組合，所有有匹配索引值的行將從這張表中讀取。如果聯接只使用鍵的最左邊的前綴，或如果鍵不是UNIQUE或PRIMARY KEY（換句話說，如果聯接不能基於關鍵字選擇單個行的話），則使用ref。如果使用的鍵僅僅匹配少量行，該聯接類型是不錯的。 ref可以用於使用=或<=>操作符的帶索引的列。 在下面的例子中，MySQL可以使用ref聯接來處理ref_tables：