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子句中出現的順序