Mysql查詢優化器

本文的目的主要是通過告訴大家，查詢優化器為我們做了那些工作，我們怎么做，才能使查詢優化器對我們的 sql進行優化，以及啟示我們 sql語句怎么寫，才能更有效率。那么到底 mysql到底能進行哪些優化那，下面通過以下幾個方面來探討一下：

1??????????常量轉化

　它能夠對 sql語句中的常量進行轉化，比如下面的表達式：　 WHERE col1 = col2 AND col2 = 'x';　依據傳遞性：如果 A=B and B=C，那么就能得出 A=C。所以上面的表達式 mysql查詢優化器能進行如下的優化： WHERE col1 = 'x' AND col2 = 'x';　對于 col1 col2，只要是屬于下面的操作符之一就可以進行類似的轉化：　 =,<,>,<=,>=,<>,<=>,LIKE

從中我們也可以看出，對于? BETWEEN的情況是不進行轉換的。這個可能與其具體的實現有關。

2??????????無效代碼的排除

　查詢優化器會對一些無用的條件進行過濾，比如說? 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';盡管該條件是無效條件，優化器也不會將它移除。

3?????????常量計算

如下表達式： WHERE col1 = 1 + 2轉化為： WHERE col1 = 3??Mysql會對常量表達進行計算，然后將結果生成條件

4?????????存取類型

當我們評估一個條件表達式， 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列作為驅動表達式。

5?????????范圍存取類型

一些表達式可以使用索引，但是屬于索引的范圍查找。這些表達式通常對應的操作符是： >、 >=、 <、 <=、 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也認為它們是等價的。

　　如果需要檢查過多的索引鍵值，優化器將放棄使用索引范圍查找，而是使用全表掃描的方式。這樣的情況經常出現如下的情況下：索引是多層次的二級索引，查詢條件是 '<'以及是 '>'的情況。

6?????????索引存取類型

考慮如下的查詢語句： SELECT column1 FROM Table1;如果 column1是索引列，?優化器更有可能選擇索引全掃描，而不是采用表全掃描。這是因為該索引覆蓋了我們所需要查詢的列?！≡倏紤]如下的查詢語句：　 SELECT column1,column2 FROM Table1;　　如果索引的定義如下，那么就可以使用索引全掃描： CREATE INDEX … ON Table1(column1,column2);　　也就是說，所有需要查詢的列必須在索引中出現。但是如下的查詢就只能走全表掃描了：? select col3 from Table1;由于 col3沒有建立索引所以只能走全表掃描。由此其實我們的 Cn表中建立的索引其實還是有一些問題的：

PRIMARY KEY??(`CID`),

??UNIQUE KEY `IDX_CN_CNAME` (`CNAME`),

??KEY `INDEX_CN_CID_UID` (`CID`,`CUSTOMERID`),

??KEY `INDEX_CN_PRODTYPE` (`PRODTYPE`),

??KEY `INDEX_CN_P_C` (`PRODTYPE`,`CNSTATUS`),

??KEY `INDEX_CN_UID` (`CUSTOMERID`)

比如所 cid是唯一索引，由 cid已經能唯一確定一條記錄，那么在以 cid和 customerid建立索引實際上是多余的。同樣，建立了 prodtype和 cnstatus的復合索引，再建立 prodtype的索引也是有問題的，即使你使用了 prodtype字段作為條件查詢，也未必就會使用 prodtype的索引，因為他們有著相同的前綴，故優化器根本搞不清楚你要使用哪個索引，所以，盡量避免相同的前綴的索引。

7?????????轉換

MySQL對簡單的表達式支持轉換。比如下面的語法： WHERE -5 = column1轉換為：　?　 WHERE column1 = -5　盡管如此，對于有數學運算存在的情況不會進行轉換。比如下面的語法：　 WHERE 5 = -column1不會轉換為： WHERE column1 = -5，所以盡量減少列上的運算，而將運算放到常量上。比如我們在寫 sql的時候自覺的將 5= -columb1=> column1=-5;

?

8????????? AND

帶 AND的查詢的格式為：? AND?，考慮如下的查詢語句：

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上的索引是創建的時間早。

?

9????????? OR

帶 OR的查詢格式為：? OR?，考慮如下的查詢語句： WHERE column1='x' OR column2='y'

優化器做出的選擇是采用全表掃描。當然，在一些特定的情況，可以使用索引合并，這里不做闡述。如果兩個條件里面設計的列是同一列，那么又是另外一種情況，考慮如下的查詢語句： WHERE column1='x' OR column1='y'在這種情況下，該查詢語句采用索引范圍查找。

10???? UNION

所有帶 UNION的查詢語句都是單獨優化的，考慮如下的查詢語句：以下是引用片段：　　 SELECT *??FROM?Table1???WHERE??column1='x'　

UNIONALL　? SELECT * FROM Table1??WHER??column2='y'

如果 column1與 column2都是擁有索引?的，每個查詢都是使用索引查詢，然后合并結果集。

11???? NOT,<>

考慮如下的表達式：　 Column1<> 5從邏輯上講，該表達式等價于下面的表達式：

Column1<5 OR column1>5　然而， MySQL不會進行這樣的轉換。如果你覺得使用范圍查找會更好一些，應該手動地進行轉換。

　　考慮如下的表達式：　 WHERE NOT (column1!=5)?從邏輯上講，該表達式等價于下面的表達式： WHERE column1=5　同樣地， MySQL也不會進行這樣的轉換。

12???? 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排序操作。

13???? 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()的優化情況。考慮下面的查詢語句：以下是引用片段：
　?　 SELECTMAX(column1)　 FROMTable1　 WHEREcolumn1<'a';?　如果 column1列上存在索引，優化器使用 'a'進行索引定位，然后返回前一條記錄。

7)?考慮如下的查詢語句 :

SELECT DISTINCT column1 FROM Table1;在特定的情況下，語句可以轉化為：

　　? SELECT column1 FROM Table1 GROUP BY column1;轉換的前提條件是： column1上存?在索引， FROM上只有一個單表，沒有 WHERE條件并且沒有 LIMIT條件。

Mysql查詢優化器