LiFe不NG

當你提交一個查詢的時候，MySQL會分析它，看是否可以做一些優化使處理該查詢的速度更快。這一部分將介紹查詢優化器是如何工作的。如果你想知道MySQL採用的優化手段，可以查看MySQL參考手冊。
　　
　　當然，MySQL查詢優化器也利用了索引，但是它也使用了其他一些資訊。例如，如果你提交如下所示的查詢，那麼無論數據表有多大，MySQL執行它的速度都會非常快：
　　
　　SELECT * FROM tbl_name WHERE 0;
　　
　　在這個例子中，MySQL查看WHERE子句，認識到沒有符合查詢條件的數據行，因此根本就不考慮搜索數據表。你可以通過提供一個EXPLAIN語句看到這種情況，這個語句讓MySQL顯示自己執行的但實際上沒有真正地執行的SELECT查詢的一些資訊。如果要使用EXPLAIN，只需要在EXPLAIN單詞放在SELECT語句的前面：
　　
　　mysql> EXPLAIN SELECT * FROM tbl_name WHERE 0\G
　　*************************** 1. row ***************************
　　id: 1
　　select_type: SIMPLE
　　table: NULL
　　type: NULL
　　possible_keys: NULL
　　key: NULL
　　key_len: NULL
　　ref: NULL
　　rows: NULL
　　Extra: Impossible WHERE
　　
　　通常情況下，EXPLAIN返回的資訊比上面的資訊要多一些，還包括用於掃描數據表的索引、使用的聯結類型、每張數據表中估計需要檢查的數據行數量等非空（NULL）資訊。
　　
　　優化器是如何工作的
　　
　　MySQL查詢優化器有幾個目標，但是其中最主要的目標是盡可能地使用索引，並且使用最嚴格的索引來消除盡可能多的數據行。你的最終目標是提交SELECT語句搜尋數據行，而不是排除數據行。優化器試圖排除數據行的原因在於它排除數據行的速度越快，那麼找到與條件匹配的數據行也就越快。如果能夠首先進行最嚴格的測試，查詢就可以執行地更快。假設你的查詢檢驗了兩個數據列，每個列上都有索引：
　　
　　SELECT col3 FROM mytable
　　WHERE col1 = ’some value’ AND col2 = ’some other value’;
　　
　　假設col1上的測試匹配了900個數據行，col2上的測試匹配了300個數據行，而同時進行的測試只得到了30個數據行。先測試Col1會有900個數據行，需要檢查它們找到其中的30個與col2中的值匹配記錄，其中就有870次是失敗了。先測試col2會有300個數據行，需要檢查它們找到其中的30個與col1中的值匹配的記錄，只有270次是失敗的，因此需要的計算和磁片I/O更少。其結果是，優化器會先測試col2，因為這樣做開銷更小。
　　
　　你可以通過下面一個指導幫助優化器更好地利用索引：
　　
　　儘量比較數據類型相同的數據列。當你在比較操作中使用索引數據列的時候，請使用數據類型相同的列。相同的數據類型比不同類型的性能要高一些。例如，INT與BIGINT是不同的。CHAR(10)被認為是CHAR(10)或VARCHAR(10)，但是與CHAR(12)或VARCHAR(12)不同。如果你所比較的數據列的類型不同，那麼可以使用ALTER TABLE來修改其中一個，使它們的類型相匹配。
　　
　　盡可能地讓索引列在比較運算式中獨立。如果你在函數調用或者更複雜的算術運算式條件中使用了某個數據列，MySQL就不會使用索引，因為它必須計算出每個數據行的運算式值。有時候這種情況無法避免，但是很多情況下你可以重新編寫一個查詢讓索引列獨立地出現。
　　
　　下面的WHERE子句顯示了這種情況。它們的功能相同，但是對於優化目標來說就有很大差異了：
　　
　　WHERE mycol < 4 / 2
　　WHERE mycol * 2 < 4
　　
　　對於第一行，優化器把運算式4/2簡化為2，接著使用mycol上的索引來快速地搜尋小于2的值。對於第二個運算式，MySQL必須檢索出每個數據行的mycol值，乘以2，接著把結果與4進行比較。在這種情況下，不會使用索引。數據列中的每個值都必須被檢索到，這樣才能計算出比較運算式左邊的值。
　　
　　我們看另外一個例子。假設你對date_col列進行了索引。如果你提交一條如下所示的查詢，就不會使用這個索引：
　　
　　SELECT * FROM mytbl WHERE YEAR(date_col) < 1990;
　　
　　這個運算式不會把1990與索引列進行比較；它會把1990與該數據列計算出來的值比較，而每個數據行都必須計算出這個值。其結果是，沒有使用date_col上的索引，因為執行這樣的查詢需要全表掃描。怎麼解決這個問題呢？只需要使用文本日期，接著就可以使用date_col上的索引來搜尋列中匹配的值了：
　　
　　WHERE date_col < ’1990-01-01’
　　
　　但是，假設你沒有特定的日期。你可能希望找到一些與今天相隔固定的幾天的日期的記錄。表達這種類型的比較有很多種方法--它們的效率並不同。下面就有三種：
　　
　　WHERE TO_DAYS(date_col) - TO_DAYS(CURDATE()) < cutoff
　　WHERE TO_DAYS(date_col) < cutoff + TO_DAYS(CURDATE())
　　WHERE date_col < DATE_ADD(CURDATE(), INTERVAL cutoff DAY)
　　
　　對於第一行，不會用到索引，因為每個數據行都必須檢索以計算出TO_DAYS(date_col)的值。第二行要好一些。Cutoff和TO_DAYS(CURDATE())都是常量，因此在處理查詢之前，比較運算式的右邊可以被優化器一次性計算出來，而不需要每個數據行都計算一次。但是date_col列仍然出現在函數調用中，它阻止了索引的使用。第三行是這幾個中最好的。同樣，在執行查詢之前，比較運算式的右邊可以作為常量一次性計算出來，但是現在它的值是一個日期。這個值可以直接與date_col值進行比較，再也不需要轉換成天數了。在這種情況下，會使用索引。
　　
　　在LIKE模式的開頭不要使用通配符。有些字符串搜索使用如下所示的WHERE子句：
　　
　　WHERE col_name LIKE ’%string%’
　　
　　如果你希望找到那些出現在數據列的任何位置的字符串，這個語句就是對的。但是不要因為習慣而簡單地把"%"放在字符串的兩邊。如果你在搜尋出現在數據列開頭的字符串，就刪掉前面的"%"。假設你要搜尋那些類似MacGregor或MacDougall等以"Mac"開頭的名字。在這種情況下，WHERE子句如下所示：
　　
　　WHERE last_name LIKE ’Mac%’
　　
　　優化器查看該模式中詞首的文本，並使用索引找到那些與下面的運算式匹配的數據行。下面的運算式是使用last_name索引的另一種形式：
　　
　　WHERE last_name >= ’Mac’ AND last_name < ’Mad’
　　
　　這種優化不能應用於使用了REGEXP操作符的模式匹配。REGEXP運算式永遠不會被優化。
　　
　　幫助優化器更好的判斷索引的效率。在默認情況下，當你把索引列的值與常量進行比較的時候，優化器會假設鍵值在索引內部是均勻分佈的。在決定進行常量比較是否使用索引的時候，優化器會快速地檢查索引，估計出會用到多少個實體（entry）。對應MyISAM、InnoDB和BDB數據表來說，你可以使用ANALYZE TABLE讓伺服器執行對鍵值的分析。它會為優化器提供更好的資訊。
　　
　　使用EXPLAIN驗證優化器的操作。EXPLAIN語句可以告訴你是否使用了索引。當你試圖用另外的方式編寫語句或檢查添加索引是否會提高查詢執行效率的時候，這些資訊對你是有幫助的。
　　
　　在必要的時候給優化器一些提示。正常情況下，MySQL優化器自由地決定掃描數據表的次序來最快地檢索數據行。在有些場合中優化器沒有作出最佳選擇。如果你察覺這種現象發生了，就可以使用STRAIGHT_JOIN關鍵字來重載優化器的選擇。帶有STRAIGHT_JOIN的聯結類似于交叉聯結，但是強迫數據表按照FROM子句中指定的次序來聯結。
　　
　　在SELECT語句中有兩個地方可以指定STRAIGHT_JOIN。你可以在SELECT關鍵字和選擇列表之間的位置指定，這樣會對語句中所有的交叉聯結產生影響；你也可以在FROM子句中指定。下面的兩個語句功能相同：
　　
　　SELECT STRAIGHT_JOIN ... FROM t1, t2, t3 ... ;
　　SELECT ... FROM t1 STRAIGHT_JOIN t2 STRAIGHT_JOIN t3 ... ;
　　
　　分別在帶有STRAIGHT_JOIN和不帶STRAIGHT_JOIN的情況下運行這個查詢；MySQL可能因為什麼原因沒有按照你認為最好的次序使用索引（你可以使用EXPLAIN來檢查MySQL處理每個語句的執行計劃）。
　　
　　你還可以使用FORCE INDEX、USE INDEX或IGNORE INDEX來指導伺服器如何使用索引。
　　
　　利用優化器更加完善的區域。MySQL可以執行聯結和子查詢，但是子查詢是最近才支援的，是在MySQL 4.1中添加的。因而在很多情況下，優化器對聯結操作的調整比對子查詢的調整要好一些。當你的子查詢執行地很慢的時候，這就是一條實際的提示。有一些子查詢可以使用邏輯上相等的聯結來重新表達。在可行的情況下，你可以把子查詢重新改寫為聯結，看是否執行地快一些。
　　
　　測試查詢的備用形式，多次運行。當你測試查詢的備用形式的時候（例如，子查詢與等同的聯結操作對比），每種方式都應該多次運行。如果兩種形式都只運行了一次，那麼你通常會發現第二個查詢比第一個快，這是因為第一個查詢得到的資訊仍然保留在緩存中，以至於第二個查詢沒有真正地從磁片上讀取數據。你還應該在系統負載相對平穩的時候運行查詢，以避免系統中其他的事務影響結果。
　　
　　避免過度地使用MySQL自動類型轉換。MySQL會執行自動的類型轉換，但是如果你能夠避免這種轉換操作，你得到的性能就更好了。例如，如果num_col是整型數據列，那麼下面這些查詢將返回相同的結果：
　　
　　SELECT * FROM mytbl WHERE num_col = 4;
　　SELECT * FROM mytbl WHERE num_col = ’4’;
　　
　　但是第二個查詢涉及到了類型轉換。轉換操作本身為了把整型和字符串型轉換為雙精度型進行比較，使性能惡化了。更嚴重的情況是，如果num_col是索引的，那麼涉及到類型轉換的比較操作不會使用索引。