MySQL查詢最佳化系列講座之查詢最佳化工具(1)

    當你提交一個查詢的時候，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值進行比較，再也不需要轉換成天數了。在這種情況下，會使用索引。