mysql SELECT查詢的速度優化詳解

總的來說，要想使一個較慢速select ... where更快，應首先檢查是否能增加一個索引。不同表之間的引用通常通過索引來完成。你可以使用explain語句來確定select語句使用哪些索引。參見7.4.5節，"mysql教程如何使用索引"和7.2.1節，"explain語法（獲取關於select的信息）"。

下面是一些加速對myisam表的查詢的一般建議：

·         為了幫助mysql更好地優化查詢，在一個裝載數據後的表上運行analyze table或myisamchk --analyze。這樣為每一個索引更新指出有相同值的行的平均行數的值（當然，如果只有一個索引，這總是1。）mysql使用該方法來決定當你聯接兩個基於非常量表達式的表時選擇哪個索引。你可以使用show index from tbl_name並檢查cardinality值來檢查表分析結果。myisamchk --description --verbose可以顯示索引分布信息。

·         要想根據一個索引排序一個索引和數據，使用myisamchk --sort-index --sort-records=1（如果你想要在索引1上排序）。如果只有一個索引，想要根據該索引的次序讀取所有的記錄，這是使查詢更快的一個好方法。但是請注意，第一次對一個大表按照這種方法排序時將花很長時間！

7.2.4. mysql怎樣優化where子句
該節討論為處理where子句而進行的優化。例子中使用了select語句，但相同的優化也適用delete和update語句中的where子句。

請注意對mysql優化器的工作在不斷進行中，因此該節並不完善。mysql執行了大量的優化，本文中所列的並不詳盡。

下面列出了mysql執行的部分優化：

·         去除不必要的括號：

·                        ((a and b) and c or (((a and b) and (c and d))))·                -> (a and b and c) or (a and b and c and d)·         常量重疊：

·                   (a<b and b=c) and a=5·                -> b>5 and b=c and a=5·         去除常量條件(由於常量重疊需要)：

·                   (b>=5 and b=5) or (b=6 and 5=5) or (b=7 and 5=6)·                -> b=5 or b=6·         索引使用的常數表達式僅計算一次。

對於myisam和heap表，在一個單個表上的沒有一個where的count(*)直接從表中檢索信息。當僅使用一個表時，對not null表達式也這樣做。
無效常數表達式的早期檢測。mysql快速檢測某些select語句是不可能的並且不返回行。
如果不使用group by或分組函數(count()、min()……)，having與where合並。
對於聯接內的每個表，構造一個更簡單的where以便更快地對表進行where計算並且也盡快跳過記錄。
所有常數的表在查詢中比其它表先讀出。常數表為：
空表或只有1行的表。
與在一個primary key或unique索引的where子句一起使用的表，這裡所有的索引部分使用常數表達式並且索引部分被定義為not null。
下列的所有表用作常數表：

mysql> select * from t where primary_key=1;mysql> select * from t1,t2           where t1.primary_key=1 and t2.primary_key=t1.id;嘗試所有可能性便可以找到表聯接的最好聯接組合。如果所有在order by和group by的列來自同一個表，那麼當聯接時，該表首先被選中。
如果有一個order by子句和不同的group by子句，或如果order by或group by包含聯接隊列中的第一個表之外的其它表的列，則創建一個臨時表。
如果使用sql_small_result，mysql使用內存中的一個臨時表。
每個表的索引被查詢，並且使用最好的索引，除非優化器認為使用表掃描更有效。是否使用掃描取決於是否最好的索引跨越超過30%的表。優化器更加復雜，其估計基於其它因素，例如表大小、行數和i/o塊大小，因此固定比例不再決定選擇使用索引還是掃描。
在一些情況下，mysql能從索引中讀出行，甚至不查詢數據文件。如果索引使用的所有列是數值類，那麼只使用索引樹來進行查詢。
輸出每個記錄前，跳過不匹配having子句的行。
下面是一些快速查詢的例子：

select count(*) from tbl_name; select min(key_part1),max(key_part1) from tbl_name; select max(key_part2) from tbl_name    where key_part1=constant; select ... from tbl_name    order by key_part1,key_part2,... limit 10; select ... from tbl_name    order by key_part1 desc, key_part2 desc, ... limit 10;下列查詢僅使用索引樹就可以解決(假設索引的列為數值型)：

select key_part1,key_part2 from tbl_name where key_part1=val; select count(*) from tbl_name    where key_part1=val1 and key_part2=val2; select key_part2 from tbl_name group by key_part1;下列查詢使用索引按排序順序檢索行，不用另外的排序：

select ... from tbl_name    order by key_part1,key_part2,... ; select ... from tbl_name    order by key_part1 desc, key_part2 desc, ... ;7.2.5. 范圍優化
7.2.5.1. 單元素索引的范圍訪問方法
7.2.5.2. 多元素索引的范圍訪問方法
range訪問方法使用單一索引來搜索包含在一個或幾個索引值距離內的表記錄的子集。可以用於單部分或多元素索引。後面的章節將詳細描述如何從where子句提取區間。

7.2.5.1. 單元素索引的范圍訪問方法
對於單元素索引，可以用where子句中的相應條件很方便地表示索引值區間，因此我們稱為范圍條件而不是"區間"。

單元素索引范圍條件的定義如下：

·         對於btree和hash索引，當使用=、<=>、in、is null或者is not null操作符時，關鍵元素與常量值的比較關系對應一個范圍條件。

·         對於btree索引，當使用>、<、>=、<=、between、!=或者<>，或者like 'pattern'(其中 'pattern'不以通配符開始)操作符時，關鍵元素與常量值的比較關系對應一個范圍條件。

·         對於所有類型的索引，多個范圍條件結合or或and則產生一個范圍條件。

前面描述的"常量值"系指：

·         查詢字符串中的常量

·         同一聯接中的const或system表中的列

·         無關聯子查詢的結果

·         完全從前面類型的子表達式組成的表達式

下面是一些where子句中有范圍條件的查詢的例子：

select * from t1     where key_col > 1     and key_col < 10; select * from t1     where key_col = 1     or key_col in (15,18,20); select * from t1     where key_col like 'ab%'     or key_col between 'bar' and 'foo'; 請注意在常量傳播階段部分非常量值可以轉換為常數。

mysql嘗試為每個可能的索引從where子句提取范圍條件。在提取過程中，不能用於構成范圍條件的條件被放棄，產生重疊范圍的條件組合到一起，並且產生空范圍的條件被刪除。

例如，考慮下面的語句，其中key1是有索引的列，nonkey沒有索引：

select * from t1 where   (key1 < 'abc' and (key1 like 'abcde%' or key1 like '%b')) or   (key1 < 'bar' and nonkey = 4) or   (key1 < 'uux' and key1 > 'z');key1的提取過程如下：

1.    用原始where子句開始：

2.    (key1 < 'abc' and (key1 like 'abcde%' or key1 like '%b')) or

3.     (key1 < 'bar' and nonkey = 4) or

4.     (key1 < 'uux' and key1 > 'z')

5.    刪除nonkey = 4和key1 like '%b'，因為它們不能用於范圍掃描。刪除它們的正確途徑是用true替換它們，以便進行范圍掃描時不會丟失匹配的記錄。用true替換它們後，可以得到：

6.            (key1 < 'abc' and (key1 like 'abcde%' or true)) or7.            (key1 < 'bar' and true) or8.            (key1 < 'uux' and key1 > 'z')9.    取消總是為true或false的條件：

·         (key1 like 'abcde%' or true)總是true

·         (key1 < 'uux' and key1 > 'z')總是false

用常量替換這些條件，我們得到：

(key1 < 'abc' and true) or (key1 < 'bar' and true) or (false)刪除不必要的true和false常量，我們得到

(key1 < 'abc') or (key1 < 'bar')10.將重疊區間組合成一個產生用於范圍掃描的最終條件：

11.        (key1 < 'bar')總的來說(如前面的例子所述)，用於范圍掃描的條件比where子句限制少。mysql再執行檢查以過濾掉滿足范圍條件但不完全滿足where子句的行。

范圍條件提取算法可以處理嵌套的任意深度的and/or結構，並且其輸出不依賴條件在where子句中出現的順序