Mysql查詢優化器

1 定義
Mysql查詢優化器的工作是為查詢語句選擇合適的執行路徑。查詢優化器的代碼一般是經常變動的，這和存儲引擎不太一樣。因此，需要理解最新版本的查詢優化器是如何組織的，請參考相應的源代碼。整體而言，優化器有很多相同性，對mysql一個版本的優化器做到整體掌握，理解起mysql新版本以及其他數據庫的優化器都是類似的。優化器會對查詢語句進行轉化，轉化等價的查詢語句。舉個例子，優化器會將下面語句進行轉化：
SELECT … WHERE 5=a;
轉化后的等價語句為：
SELECT … WHERE a=5;
因為這兩個語句的結果集是一致的，所以這兩個語句是等價的。
這里我需要提出一點需要注意的，如果查詢語句沒帶order by。查詢語句1出現的結果為(1,1),(2,2)，查詢語句2出現的結果為(2,2),(1,1)，我們會認為這是等價的，因為不帶order by的查詢語句是無序的，怎么排序都行。
 2 代碼組織
在內核當中handle_select()函數是處理查詢語句的頂層函數，里面有兩個分支，一個是處理帶union的情況，另外一個是處理不帶union的情況，這里我們只是列出一個簡單的路徑便于說明，調用層次見下圖。
handle_select()
   mysql_select()
     JOIN::prepare()
       setup_fields()
     JOIN::optimize()            / optimizer is from here ... /
       optimize_cond()
       opt_sum_query()
       make_join_statistics()
         get_quick_record_count()
         choose_plan()
           / Find the best way to access tables /
           / as specified by the user.          /
           optimize_straight_join()
             best_access_path()
           / Find a (sub-)optimal plan among all or subset /
           / of all possible query plans where the user    /
           / controlls the exhaustiveness of the search.   /
           greedy_search()
             best_extension_by_limited_search()
               best_access_path()
           / Perform an exhaustive search for an optimal plan /
           find_best()
       make_join_select()        / ... to here /
     JOIN::exec()
上面的縮進表示函數的相互調用關系，因此可以看出handle_select()調用函數mysql_select(),mysql_select()調用 JOIN::prepare()，等等。mysql_select()首先調用函數JOIN::prepare()進行語句分析、元數據設置、子查詢轉化等等。然后調用函數JOIN::optimize()進行優化，選出最后的執行計劃。最后調用函數JOIN::exec()執行該執行計劃。盡管出現了單詞“JOIN”，這些優化函數是為所有的查詢語句服務的，不管你是什么查詢類型。函數optimize_cond()和函數 opt_sum_query()是執行一些轉化操作。函數make_join_statistics()對所有可用索引統計信息進行分析。
 3 常量轉化
 對類似下面的表達式可以進行轉化：
WHERE column1 = column2 AND column2 = 'x';
 因為我們知道：如果A=B and B=C，那么A=C。所以上面的表達式可以轉化為：
WHERE column1 = 'x'  AND column2 = 'x';
 對于column1 <operator> column2，只要<operator>是屬于下面的操作符之一就可以進行類似的轉化：
=,<,>,<=,>=,<>,<=>,LIKE
 從中我們也可以看出，對于BETWEEN的情況是不進行轉換的。
 4 無效代碼的排除
 見如下表達式：WHERE 0=0 AND column1='y'因為第一個條件是始終為true的，所以可以移除該條件，變為：WHERE column1='y'
 再見如下表達式：WHERE (0=1 AND s1=5) OR s1=7因為前一個括號內的表達式始終為false，因此可以移除該表達式，變為：WHERE s1=7
一些情況下甚至可以將整個WHERE子句去掉，見下面的表達式：WHERE (0=1 AND s1=5)我們可以看到，WHERE子句始終為FALASE，那么WHERE條件是不可能發生的。當然我們也可以講，WHERE條件被優化掉了。
如果一個列的定義是不允許為NULL，那么：WHERE not_null_column IS NULL該條件是始終為false的，再看：WHERE not_null_column IS NOT NULL
該條件是始終為true的，因此這樣的表達式也是可以從條件表達式中刪除的。當然，也是有特殊情況的，比如在out join中，被定義為NOT NULL的列也可能包含NULL值。在這種情況下，IS NULL條件是被保留的。
當然優化器沒有對所有的情況進行檢測，因為這實在太復雜了。舉個例子：CREATE TABLE Table1(column1 CHAR(1));SELECT FROM Table1 WHERE column1 = 'Canada';盡管該條件是無效條件，優化器也不會將它移除。
5 常量計算 如下表達式：WHERE columb1 = 1 + 2轉化為：WHERE columb1 = 3
6 常量以及常量表
 常量表的定義如下：
1)一個表只有0行或者1行數據。
2)在WHERE子句中包含條件column = constant，并且這些列是primary key，或者這些列是UNIQUE（假設該UNIQUE同時被定義為NOT NULL）。這樣生成的查詢結果也可以成為常量表。
如果表Table0定義中包含：PRIMARY KEY(column1,column2)再看下面的語法：FROM Table0 … WHERE column1=5 AND column2=7 …
 那么該語句返回的就是常量表。舉個更簡單的情況，建設Table1定義中包含:unique_not_null_column INT NOT NULL UNIQUE
再看下面的語法：FROM Table1 ... WHERE unique_not_null_column=5
 該語句返回的也是常量表。
從例子中我們可以看出常量表最多只有1個行值。MySQL會預先評估常量表，找出這個值，然后將這個值引入到查詢語句中進行優化，舉例如下：
SELECT Table1.unique_not_null_column, Table2.any_column
    FROM Table1, Table2
    WHERE Table1.unique_not_null_column = Table2.any_column
    AND Table1.unique_not_null_column = 5;
 在評估這個查詢語句時，MySQL首先發現通過Table1.unique_not_null_column條件的限制，Table1會變成一個常量表。然后，取回該值。
 如果取回操作失敗（Table1中沒有行滿足條件unique_not_null_column = 5），那么該常量表就包含0行，那么如果對該語句執行EXPLAIN操作，會得到提示信息：
Impossible WHERE noticed after reading const tables
 另外一種情況是取回操作成功（Table1中嚴格只有一行滿足條件unique_not_null_column = 5），那么常量表中包含一條數據，并且MySQL會將查詢語句轉化為：
SELECT 5, Table2.any_column
    FROM Table1, Table2
    WHERE 5 = Table2.any_column
    AND 5 = 5;
7 存取類型
 當我們評估一個條件表達式，MySQL判斷該表達式的存取類型。下面是一些存取類型，按照從最優到最差的順序進行排列：
system      … 系統表，并且是常量表
const       … 常量表
eq_ref      …   unique/primary索引，并且使用的是'='進行存取
ref         … 索引使用'='進行存取
ref_or_null … 索引使用'='進行存取，并且有可能為NULL
range       … 索引使用BETWEEN、IN、>=、LIKE等進行存取
index       …   索引全掃描
ALL        … 表全掃描
 優化器根據存取類型選擇合適的驅動表達式。考慮如下的查詢語句：
SELECT FROM Table1 WHERE indexed_column = 5 AND unindexed_column = 6
 因為indexed_column擁有更好的存取類型，所以更有可能使用該表達式做為驅動表達式。這里只考慮簡單的情況，不考慮特殊的情況。
 那么驅動表達式的意思是什么呢？考慮到這個查詢語句有兩種可能的執行方法:
1)不好的執行路徑：讀取表的每一行（稱為“全表掃描”），對于讀取到的每一行，檢查相應的值是否滿足indexed_column以及unindexed_column對應的條件。
2)好的執行路徑：通過鍵值indexed_column=5查找B樹，對于符合該條件的每一行，判斷是否滿足unindexed_column對應的條件。
 一般情況下，索引查找比全表掃描需要更少的存取路徑，尤其當表數據量很大，并且索引的類型是UNIQUE的時候。因此稱它為好的執行路徑，使用indexed_column列作為驅動表達式。
8 范圍存取類型
 一些表達式可以使用索引，但是屬于索引的范圍查找。這些表達式通常對應的操作符是：>、>=、<、<=、IN、LIKE、BETWEEN。對優化器而言，如下表達式：
column1 IN (1,2,3)
該表達式與下面的表達式是等價的：column1 = 1 OR column1 = 2 OR column1 = 3并且MySQL也是認為它們是等價的，所以沒必要手動將IN改成OR,或者把OR改成IN。
 優化器將會對下面的表達式使用索引范圍查找：column1 LIKE 'x%'但對下面的表達式就不會使用到索引了：column1 LIKE '%x'這是因為當首字符是通配符的時候，沒辦法使用到索引進行范圍查找。
 對優化器而言，如下表達式：column1 BETWEEN 5 AND 7該表達式與下面的表達式是等價的：column1 >= 5 AND column1 <= 7 同樣，MySQL也認為它們是等價的。
 如果需要檢查過多的索引鍵值，優化器將放棄使用索引范圍查找，而是使用全表掃描的方式。這樣的情況經常出現如下的情況下：索引是多層次的二級索引，查詢條件是'<'以及是'>'的情況。
9 索引存取類型
 考慮如下的查詢語句：
SELECT column1 FROM Table1;
 如果column1是索引列，優化器更有可能選擇索引全掃描，而不是采用表全掃描。這是因為該索引覆蓋了我們所需要查詢的列。
 再考慮如下的查詢語句：SELECT column1,column2 FROM Table1;如果索引的定義如下，那么就可以使用索引全掃描：CREATE INDEX … ON Table1(column1,column2);也就是說，所有需要查詢的列必須在索引中出現。
10轉換
 MySQL對簡單的表達式支持轉換。比如下面的語法：WHERE -5 = column1轉換為：WHERE column1 = -5盡管如此，對于有數學運算存在的情況不會進行轉換。比如下面的語法：WHERE 5 = -column1不會轉換為：WHERE column1 = -5
11 AND
 帶AND的查詢的格式為：<condition> AND <condition>，考慮如下的查詢語句：WHERE column1='x' AND column2='y'
 優化的步驟：
1)如果兩個列都沒有索引，那么使用全表掃描。
2)否則，如果其中一個列擁有更好的存取類型（比如，一個具有索引，另外一個沒有索引；再或者，一個是唯一索引，另外一個是非唯一索引），那么使用該列作為驅動表達式。
3)否則，如果兩個列都分別擁有索引，并且兩個條件對應的存取類型是一致的，那么選擇定義索引時的先定義的索引。
舉例如下：
CREATE TABLE Table1 (s1 INT,s2 INT);
CREATE INDEX Index1 ON Table1(s2);
CREATE INDEX Index2 ON Table1(s1);
SELECT FROM Table1 WHERE s1=5 AND s2=5;優化器選擇s2=5作為驅動表達式，因為s2上的索引是新建的。
 12 OR
 帶 OR的查詢格式為：<condition> OR <condition>，考慮如下的查詢語句：WHERE column1='x' OR column2='y'優化器做出的選擇是采用全表掃描。當然，在一些特定的情況，可以使用索引合并，這里不做闡述。
如果兩個條件里面設計的列是同一列，那么又是另外一種情況，考慮如下的查詢語句：WHERE column1='x' OR column1='y'在這種情況下，該查詢語句采用索引范圍查找。
13 UNION
 所有帶UNION的查詢語句都是單獨優化的，考慮如下的查詢語句：SELECT FROM Table1 WHERE column1='x'UNION ALL SELECT FROM Table1 WHERE column2='y'如果column1與column2都是擁有索引的，每個查詢都是使用索引查詢，然后合并結果集。
14 NOT,<>
 考慮如下的表達式：Column1<> 5從邏輯上講，該表達式等價于下面的表達式：Column1<5 OR column1>5然而，MySQL不會進行這樣的轉換。如果你覺得使用范圍查找會更好一些，應該手動地進行轉換。
 考慮如下的表達式：
WHERE NOT (column1!=5)從邏輯上講，該表達式等價于下面的表達式：WHERE column1=5同樣地，MySQL也不會進行這樣的轉換。
15 ORDER BY
 一般而言，ORDER BY的作用是使結果集按照一定的順序排序，如果可以不經過此操作就能產生順序的結果，可以跳過該ORDER BY操作。
 考慮如下的查詢語句：SELECT column1 FROM Table1 ORDER BY 'x';優化器將去除該ORDER BY子句，因為此處的ORDER BY子句沒有意義。
 再考慮另外的一個查詢語句：
SELECT column1 FROM Table1 ORDER BY column1;在這種情況下，如果column1類上存在索引，優化器將使用該索引進行全掃描，這樣產生的結果集是有序的，從而不需要進行ORDER BY操作。
 再考慮另外的一個查詢語句：
SELECT column1 FROM Table1 ORDER BY column1+1;假設column1上存在索引，我們也許會覺得優化器會對column1索引進行全掃描，并且不進行ORDER BY操作。實際上，情況并不是這樣，優化器是使用column1列上的索引進行全掃表，僅僅是因為索引全掃描的效率高于表全掃描。對于索引全掃描的結果集仍然進行ORDER BY排序操作。
16 GROUP BY
 這里列出對GROUP BY子句以及相關集函數進行優化的方法：
1)如果存在索引，GROUP BY將使用索引。
2)如果沒有索引，優化器將需要進行排序，一般情況下會使用HASH表的方法。
3)如果情況類似于“GROUP BY x ORDER BY x”,優化器將會發現ORDER BY子句是沒有必要的，因為GROUP BY產生的結果集是按照x進行排序的。
4)盡量將HAVING子句中的條件提升中WHERE子句中。
5) 對于MyISAM表，“SELECT COUNT() FROM Table1;”直接返回結果，而不需要進行表全掃描。但是對于InnoDB表，則不適合該規則。補充一點，如果column1的定義是NOT NULL的，那么語句“SELECT COUNT(column1) FROM Table1;”等價于“SELECT COUNT(*) FROM Table1;”。
6)考慮MAX()以及MIN()的優化情況。考慮下面的查詢語句：
SELECT MAX(column1)FROM Table1 WHERE column1 < 'a';如果column1列上存在索引，優化器使用'a'進行索引定位，然后返回前一條記錄。
7)考慮如下的查詢語句:
SELECT DISTINCT column1 FROM Table1;在特定的情況下，語句可以轉化為：
SELECT column1 FROM Table1 GROUP BY column1;該轉換的前提條件是：column1上存在索引，FROM上只有一個單表，沒有WHERE條件并且沒有LIMIT條件