Mysql查詢優(yōu)化器

本文的目的主要是通過(guò)告訴大家，查詢優(yōu)化器為我們做了那些工作，我們?cè)趺醋觯拍苁共樵儍?yōu)化器對(duì)我們的 sql進(jìn)行優(yōu)化，以及啟示我們 sql語(yǔ)句怎么寫(xiě)，才能更有效率。那么到底 mysql到底能進(jìn)行哪些優(yōu)化那，下面通過(guò)以下幾個(gè)方面來(lái)探討一下：

1??????????常量轉(zhuǎn)化

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

從中我們也可以看出，對(duì)于? BETWEEN的情況是不進(jìn)行轉(zhuǎn)換的。這個(gè)可能與其具體的實(shí)現(xiàn)有關(guān)。

2??????????無(wú)效代碼的排除

　查詢優(yōu)化器會(huì)對(duì)一些無(wú)用的條件進(jìn)行過(guò)濾，比如說(shuō)? WHERE 0=0 AND?column1='y'　　因?yàn)榈谝粋€(gè)條件是始終為 true的，所以可以移除該條件，變?yōu)椋? WHERE column1='y'再見(jiàn)如下表達(dá)式： WHERE (0=1 AND s1=5) OR s1=7因?yàn)榍耙粋€(gè)括號(hào)內(nèi)的表達(dá)式始終為 false，因此可以移除該表達(dá)式，變?yōu)椋? WHERE s1=7

一些情況下甚至可?以將整個(gè) WHERE子句去掉，見(jiàn)下面的表達(dá)式： WHERE (0=1 AND s1=5)我們可以看到， WHERE子句始終為 FALASE，那么 WHERE條件是不可能發(fā)生的。當(dāng)然我們也可以講， WHERE條件被優(yōu)化掉了。

如果一個(gè)列的定義是不允許為 NULL，那么： WHERE not_null_column IS NULL該條件?是始終為 false的，再看： WHERE not_null_column IS NOT NULL該條件是始終為? true的，因此這樣的表達(dá)式也是可以從條件表達(dá)式中刪除的。

當(dāng)然，也是有特殊情況的，比如在 out join中，被定義為 NOT NULL的列也可能包含 NULL值。在這種情況下， IS NULL條件是被保留的。

當(dāng)然優(yōu)化器沒(méi)有對(duì)所有的情況進(jìn)行檢測(cè)，因?yàn)檫@實(shí)在太?復(fù)雜了。舉個(gè)例子： CREATE TABLE Table1(column1 CHAR(1));

SELECT * FROM Table1 WHERE column1 = 'Canada';盡管該條件是無(wú)效條件，優(yōu)化器也不會(huì)將它移除。

3?????????常量計(jì)算

如下表達(dá)式： WHERE col1 = 1 + 2轉(zhuǎn)化為： WHERE col1 = 3??Mysql會(huì)對(duì)常量表達(dá)進(jìn)行計(jì)算，然后將結(jié)果生成條件

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

當(dāng)我們?cè)u(píng)估一個(gè)條件表達(dá)式， MySQL判斷該表達(dá)式的存取類型。下面是一些存取類型，按照從最優(yōu)到最差的順序進(jìn)行排列：

system系統(tǒng)表，并且是常量表

const??常量表

eq_ref??unique/primary索引，并且使用的是 '='進(jìn)行存取

ref??索引使用 '='進(jìn)行存取

ref_or_null??索引使用 '='進(jìn)行存取，并且有可能為 NULL

range??索引使用 BETWEEN、 IN、 >=、 LIKE等進(jìn)行存取

index??索引全掃描

ALL??表全掃描

優(yōu)化器根據(jù)存取類型選擇合適的驅(qū)動(dòng)表達(dá)式。考慮如下的查詢語(yǔ)句：以下是引用片段：

　?　 SELECT *　 FROM Table1??WHERE indexed_column=5 AND??unindexed_column=6

因?yàn)? indexed_column擁有更好的存取類型，所以更有可能使用該表達(dá)式做為驅(qū)動(dòng)表達(dá)式。這里只考慮簡(jiǎn)單的情況，不考慮特殊的情況。那么驅(qū)動(dòng)表達(dá)式的意思是什么呢 ?考慮到這個(gè)查詢語(yǔ)句有兩種可能的執(zhí)行方法 :

1)?不好的執(zhí)行路徑：讀取表的每一行 (稱為“全表掃描” )，對(duì)于讀取到的每一行，檢查相應(yīng)的值是否滿足 indexed_column以及? unindexed_column對(duì)應(yīng)的條件。

2)?好的執(zhí)行路徑：通過(guò)鍵值 indexed_column=5查找 B樹(shù)，對(duì)于符合該條件的每一行，判斷是否滿足 unindexed_column對(duì)應(yīng)的條件。

一般情況下，索引查找比全表掃描需要更少的存取路徑，尤其當(dāng)表數(shù)據(jù)量很大，并且索引的類型是 UNIQUE的時(shí)候。因此稱它為好的執(zhí)行路徑，使用? indexed_column列作為驅(qū)動(dòng)表達(dá)式。

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

一些表達(dá)式可以使用索引，但是屬于索引的范圍查找。這些表達(dá)式通常對(duì)應(yīng)的操作符是： >、 >=、 <、 <=、 IN、 LIKE、? BETWEEN。

　　對(duì)優(yōu)化器而言，如下表達(dá)式：

column1 IN (1,2,3)

　　該表達(dá)式與下面的表達(dá)式是等價(jià)的：

column1 = 1 OR column1 = 2 OR column1 = 3

　　并且? MySQL也是認(rèn)為它們是等價(jià)的，所以沒(méi)必要手動(dòng)將 IN改成 OR,或者把 OR改成 IN。

　　優(yōu)化器將會(huì)對(duì)下面的表達(dá)式使用索引范圍查找： column1 LIKE 'x%'，但對(duì)下面的表達(dá)式就不會(huì)使用到索引了： column1 LIKE '%x'，這是因?yàn)楫?dāng)首字符是通配符的時(shí)候，?沒(méi)辦法使用到索引進(jìn)行范圍查找。

　　對(duì)優(yōu)化器而言，如下表達(dá)式： column1 BETWEEN 5 AND 7　該表達(dá)式與下面的表達(dá)式是等價(jià)的： column1 >= 5 AND column1 <= 7同樣， MySQL也認(rèn)為它們是等價(jià)的。

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

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

考慮如下的查詢語(yǔ)句： SELECT column1 FROM Table1;如果 column1是索引列，?優(yōu)化器更有可能選擇索引全掃描，而不是采用表全掃描。這是因?yàn)樵撍饕采w了我們所需要查詢的列。　再考慮如下的查詢語(yǔ)句：　 SELECT column1,column2 FROM Table1;　　如果索引的定義如下，那么就可以使用索引全掃描： CREATE INDEX … ON Table1(column1,column2);　　也就是說(shuō)，所有需要查詢的列必須在索引中出現(xiàn)。但是如下的查詢就只能走全表掃描了：? select col3 from Table1;由于 col3沒(méi)有建立索引所以只能走全表掃描。由此其實(shí)我們的 Cn表中建立的索引其實(shí)還是有一些問(wèn)題的：

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已經(jīng)能唯一確定一條記錄，那么在以 cid和 customerid建立索引實(shí)際上是多余的。同樣，建立了 prodtype和 cnstatus的復(fù)合索引，再建立 prodtype的索引也是有問(wèn)題的，即使你使用了 prodtype字段作為條件查詢，也未必就會(huì)使用 prodtype的索引，因?yàn)樗麄冇兄嗤那熬Y，故優(yōu)化器根本搞不清楚你要使用哪個(gè)索引，所以，盡量避免相同的前綴的索引。

7?????????轉(zhuǎn)換

MySQL對(duì)簡(jiǎn)單的表達(dá)式支持轉(zhuǎn)換。比如下面的語(yǔ)法： WHERE -5 = column1轉(zhuǎn)換為：　?　 WHERE column1 = -5　盡管如此，對(duì)于有數(shù)學(xué)運(yùn)算存在的情況不會(huì)進(jìn)行轉(zhuǎn)換。比如下面的語(yǔ)法：　 WHERE 5 = -column1不會(huì)轉(zhuǎn)換為： WHERE column1 = -5，所以盡量減少列上的運(yùn)算，而將運(yùn)算放到常量上。比如我們?cè)趯?xiě) sql的時(shí)候自覺(jué)的將 5= -columb1=> column1=-5;

?

8????????? AND

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

WHERE column1='x' AND column2='y'　　

優(yōu)化的步驟：

1)?如果兩個(gè)列都沒(méi)有索引，那么使用全表掃描。

2)?否則，如果其中一個(gè)列擁有更好的存取類型 (比如，一個(gè)具有索引，另外一個(gè)沒(méi)有索引 ;再或者，一個(gè)是唯一索引，另外一個(gè)是非唯一索引 )，那么使用該列作為驅(qū)動(dòng)表達(dá)式。

3)?否則，如果兩個(gè)列都分別擁有索引，并且兩個(gè)條件對(duì)應(yīng)的存取類型是一致的，那么選擇定義索引時(shí) ,先定義的索引。

　?　舉例如下：

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;

　　優(yōu)化器選擇 s2=5作為驅(qū)動(dòng)表達(dá)式，因?yàn)? s2上的索引是創(chuàng)建的時(shí)間早。

?

9????????? OR

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

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

10???? UNION

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

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

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

11???? NOT,<>

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

Column1<5 OR column1>5　然而， MySQL不會(huì)進(jìn)行這樣的轉(zhuǎn)換。如果你覺(jué)得使用范圍查找會(huì)更好一些，應(yīng)該手動(dòng)地進(jìn)行轉(zhuǎn)換。

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

12???? ORDER BY

一般而言， ORDER BY的作用是使結(jié)果集按照一定的順序排序，如果可以不經(jīng)過(guò)此操作就能產(chǎn)生順序的結(jié)果，可以跳過(guò)該 ORDER BY操作。考慮如下的查詢?語(yǔ)句：

SELECT column1 FROM Table1 ORDER BY 'x';優(yōu)化器將去除該? ORDER BY子句，因?yàn)榇颂幍? ORDER BY子句沒(méi)有意義。再考慮另外的一個(gè)查詢語(yǔ)句： SELECT column1 FROM Table1 ORDER BY column1;

在這種情況下，如果 column1類上存在索引，優(yōu)化器將使用該索引進(jìn)行全掃描，這樣產(chǎn)生的結(jié)果集是有序的，從而不需要進(jìn)行 ORDER BY操作。

再考慮另外的一個(gè)查詢語(yǔ)句： SELECT column1 FROM Table1 ORDER BY column1+1;　　假設(shè) column1上存在索引，我?們也許會(huì)覺(jué)得優(yōu)化器會(huì)對(duì) column1索引進(jìn)行全掃描，并且不進(jìn)行 ORDER BY操作。實(shí)際上，情況并不是這樣，優(yōu)化器是使用 column1列上的索引進(jìn)行全掃表，僅僅是因?yàn)樗饕珤呙璧男矢哂诒砣珤呙琛?duì)于索引全掃描的結(jié)果集?仍然進(jìn)行 ORDER BY排序操作。

13???? GROUP BY

這里列出對(duì) GROUP BY子句以及相關(guān)集函數(shù)進(jìn)行優(yōu)化的方法：

1)??????如果存在索引， GROUP BY將使用索引。

2)?如果沒(méi)有索引，優(yōu)化器將需要進(jìn)行排序，一般情況下會(huì)使用 HASH表的方法。

3)?如果情況類似于 “GROUP BY x ORDER BY x”,優(yōu)化器將會(huì)發(fā)現(xiàn) ORDER BY子句是沒(méi)有必要的，因?yàn)? GROUP BY產(chǎn)生的結(jié)果集是按照 x進(jìn)行排序的。

4)?盡量將 HAVING子句中的條件提升中 WHERE子句中。

5)?對(duì)于 MyISAM表， “SELECT COUNT(*) FROM Table1;”直接返回結(jié)果，而不需要進(jìn)行表全掃描。但是對(duì)于 InnoDB表，則不適合該規(guī)則。補(bǔ)充一點(diǎn)，如果 column1的定義是 NOT NULL的，那么語(yǔ)句 “SELECT COUNT(column1) FROM Table1;”等價(jià)于 “SELECT COUNT(*) FROM Table1;”。

6)?考慮 MAX()以及 MIN()的優(yōu)化情況。考慮下面的查詢語(yǔ)句：以下是引用片段：
　?　 SELECTMAX(column1)　 FROMTable1　 WHEREcolumn1<'a';?　如果 column1列上存在索引，優(yōu)化器使用 'a'進(jìn)行索引定位，然后返回前一條記錄。

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

SELECT DISTINCT column1 FROM Table1;在特定的情況下，語(yǔ)句可以轉(zhuǎn)化為：

　　? SELECT column1 FROM Table1 GROUP BY column1;轉(zhuǎn)換的前提條件是： column1上存?在索引， FROM上只有一個(gè)單表，沒(méi)有 WHERE條件并且沒(méi)有 LIMIT條件。

Mysql查詢優(yōu)化器