一、條件簡(jiǎn)化

我們編寫(xiě)的查詢(xún)語(yǔ)句的搜索條件本質(zhì)上是一個(gè)表達(dá)式，這些表達(dá)式可能比較繁雜，或者不能高效的執(zhí)行，MySQL的查詢(xún)優(yōu)化器會(huì)為我們簡(jiǎn)化這些表達(dá)式。為了方便大家理解，我們后邊舉例的時(shí)候都使用諸如a、b、c之類(lèi)的簡(jiǎn)單字母代表某個(gè)表的列名。

1.1 移除不必要的括號(hào)

有時(shí)候表達(dá)式里有很多無(wú)用的括號(hào)，比如這樣：

((a = 5 AND b =c ) OR ((a > c) AND (c < 5)))

是不是看著就很煩，優(yōu)化器會(huì)把哪些用不到的括號(hào)都給消除，就是這樣：

(a = 5 AND b=c) OR (a > c AND c < 5)

1.2 常量傳遞（constant_propagation）

有時(shí)候像某個(gè)表達(dá)式是某個(gè)列和某個(gè)常量做等值匹配，比如這樣：

a = 5

當(dāng)這個(gè)表達(dá)式和其他涉及列a的表達(dá)式使用AND連接起來(lái)時(shí)，可以將其他表達(dá)式中的a的值替換為5，比如這樣：

a = 5 AND b > a

就可以被轉(zhuǎn)換為：

a = 5 AND b > 5

小提示： 為啥用OR連接起來(lái)的表達(dá)式就不能進(jìn)行常量傳遞？自己想想哈~

1.3 等值傳遞（equality_propagation）

有時(shí)候多個(gè)列之間存在等值匹配的關(guān)系，比如這樣：

a = b AND b = c AND c = 5

這個(gè)表達(dá)式可以被簡(jiǎn)化為：

a = 5 AND b = 5 AND c = 5

1.4 移除沒(méi)用的條件（trivial_condition_removal）

對(duì)于一些明顯永遠(yuǎn)為TRUE或者FALSE的表達(dá)式，優(yōu)化器會(huì)移除掉它們，比如這個(gè)表達(dá)式：

(a < 1 AND b = b) OR (a = 6 OR 5 != 5) 

很明顯，b = b 這個(gè)表達(dá)式永遠(yuǎn)是TRUE，5 != 5 這個(gè)表達(dá)式永遠(yuǎn)是FALSE，所有簡(jiǎn)化后的表達(dá)式就是這樣的：

(a < 1 AND TRUE) OR (a = 6 OR 5 FALSE) 

可以繼續(xù)簡(jiǎn)化：

a < 1 OR a = 6 

1.5 表達(dá)式計(jì)算

在查詢(xún)開(kāi)始之前，如果表達(dá)式中只包含常量的話，它的值會(huì)被先計(jì)算出來(lái)，比如這個(gè)：

a = 5 + 1 

因?yàn)?5 + 1 這個(gè)表達(dá)式只包含常量，所以就會(huì)簡(jiǎn)化成：

a = 6 

但是這里需要注意的是，如果某個(gè)列并不是以單獨(dú)的形式作為表達(dá)式的操作數(shù)時(shí)，比如出現(xiàn)在函數(shù)中，出現(xiàn)在某個(gè)更復(fù)雜表達(dá)式中，就像這樣：

ABS(a) > 5 

或者：

-a = -8 

優(yōu)化器是不會(huì)嘗試對(duì)這些表達(dá)式進(jìn)行簡(jiǎn)化的。我們前邊說(shuō)過(guò)只有搜索條件中索引列和常數(shù)使用的某些運(yùn)算符連接起來(lái)才能使用到索引，索引如果可以的話，最好讓索引列以單獨(dú)的形式出現(xiàn)在表達(dá)式中。

1.6 HAVING子句和WHERE子句的合并

如果查詢(xún)中沒(méi)有出現(xiàn)諸如SUM、MAX等的的聚集函數(shù)以及GROUP BY子句，優(yōu)化器就把HAVING子句和WHERE子句合并起來(lái)。

1.7 常量表檢測(cè)

MySQL覺(jué)得以下這兩中查詢(xún)運(yùn)行的特別快

• 查詢(xún)表中一條記錄沒(méi)有或者只用一條記錄

小提示： 大家有沒(méi)有覺(jué)得這一條有點(diǎn)不對(duì)勁，我還沒(méi)有開(kāi)始查表怎么就知道表中有幾條記錄？哈哈~這個(gè)其實(shí)依靠的是統(tǒng)計(jì)數(shù)據(jù)。不過(guò)我們說(shuō)過(guò)InnoDB的統(tǒng)計(jì)數(shù)據(jù)不準(zhǔn)確，所以這一條不能用于使用于InnoDB作為存儲(chǔ)引擎的表，只適用于使用Memory或者M(jìn)yISAM存儲(chǔ)引擎的表。

• 使用主鍵等值匹配或者唯一二級(jí)索引列等值匹配作為搜索條件來(lái)查詢(xún)某個(gè)表

MySQL覺(jué)著這兩種查詢(xún)花費(fèi)的時(shí)間特別少，少到可以忽略，所以把通過(guò)這兩種方式查詢(xún)的表稱(chēng)為常量表（英文名：constant tables）。優(yōu)化器在分析查詢(xún)一個(gè)語(yǔ)句時(shí)，先首先執(zhí)行常量表的查詢(xún)，然后把查詢(xún)中涉及到該表的條件全部替換成常數(shù)，最后在分析其余表的查詢(xún)成本，比方說(shuō)找個(gè)查詢(xún)語(yǔ)句：

SELECT * FROM table1 INNER JOIN table2 
    ON table1.column1 = table2.column2 
    WHERE table1.primary_key = 1;

很明顯，這個(gè)查詢(xún)可以使用主鍵和常量值的等值匹配來(lái)查詢(xún)table1表，也就是在這個(gè)查詢(xún)中table1相當(dāng)于常量表，在分析對(duì)table2表的查詢(xún)成本之前就會(huì)執(zhí)行對(duì)table1表的查詢(xún)，并把查詢(xún)中涉及table1表的條件都替換掉，也就是說(shuō)上邊的語(yǔ)句被轉(zhuǎn)換成這樣：

SELECT table1表記錄的各個(gè)字段的常量值,table2.* 
    FROM table1 INNER JOIN table2 
    ON table1.column1列的常量值 = table2.column2 ;

二、外連接消除

我們前面說(shuō)過(guò)，內(nèi)連接的驅(qū)動(dòng)表和被驅(qū)動(dòng)表的位置是可以相互轉(zhuǎn)換，而左外連接和右外連接的驅(qū)動(dòng)表和被驅(qū)動(dòng)表是固定的。這導(dǎo)致內(nèi)連接可能通過(guò)優(yōu)化表的連接順序來(lái)降低整體的查詢(xún)成本，而外連接卻無(wú)法優(yōu)化表的連接順序。為了我們學(xué)習(xí)的順利進(jìn)展，我們把之前介紹連接原理時(shí)用過(guò)的demo9和demo10表請(qǐng)出來(lái)，為了防止大家早就忘掉，我們?cè)诳匆幌逻@兩個(gè)表的結(jié)構(gòu)：

mysql> create table demo9 (m1 int, n1 char(1));
Query OK, 0 rows affected (0.01 sec)
mysql> create table demo10 (m2 int, n2 char(1));
Query OK, 0 rows affected (0.03 sec)

mysql> select * from demo9;
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
+------+------+
3 rows in set (0.00 sec)
mysql> select * from demo10;
+------+------+
| m2   | n2   |
+------+------+
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
3 rows in set (0.00 sec)

我們之前說(shuō)過(guò)，外連接和內(nèi)連接的本質(zhì)就是：對(duì)于外連接驅(qū)動(dòng)表的記錄來(lái)說(shuō)，如果無(wú)法在被驅(qū)動(dòng)表中找到匹配ON子句中的過(guò)濾條件的記錄，那么該記錄仍然會(huì)被加入到結(jié)果集中，對(duì)應(yīng)的被驅(qū)動(dòng)表各個(gè)字段使用NULL值填充；而內(nèi)連接的驅(qū)動(dòng)表的記錄如果無(wú)法在被驅(qū)動(dòng)表中找到匹配ON子句中的過(guò)濾條件的記錄，那么該記錄會(huì)被舍棄。查詢(xún)效果就是這樣：

mysql> SELECT * FROM demo9 INNER JOIN demo10 ON demo9.m1 = demo10.m2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
+------+------+------+------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM demo9 LEFT  JOIN demo10 ON demo9.m1 = demo10.m2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    1 | a    | NULL | NULL |
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
+------+------+------+------+
3 rows in set (0.00 sec)

對(duì)于上邊例子中的左外連接來(lái)說(shuō)，由于驅(qū)動(dòng)表demo9中m1=1, n1='a'的記錄無(wú)法在被驅(qū)動(dòng)表demo10中找到符合ON子句條件demo9.m1 = demo10.m2的記錄，所以就直接把這條記錄加入到結(jié)果集，對(duì)應(yīng)的demo10表的m2和n2列的值都設(shè)置為NULL。

小提示： 左外連接和右外連接其實(shí)只在驅(qū)動(dòng)表的選取方式上是不同的，其余方面都是一樣的，所以?xún)?yōu)化器會(huì)首先把右外連接查詢(xún)轉(zhuǎn)換成左外連接查詢(xún)。我們后邊就不再?lài)Z叨右外連接了。

我們知道WHERE子句的殺傷力比較大，凡是不符合WHERE子句中條件的記錄都不會(huì)參與連接。只要我們?cè)谒阉鳁l件中指定關(guān)于被驅(qū)動(dòng)表相關(guān)列的值不為NULL，那么外連接中在被驅(qū)動(dòng)表中找不到符合ON子句條件的驅(qū)動(dòng)表記錄也就被排除出最后的結(jié)果集了，也就是說(shuō)：在這種情況下：外連接和內(nèi)連接也就沒(méi)有什么區(qū)別了！比如說(shuō)這個(gè)查詢(xún)：

mysql> SELECT * FROM demo9 LEFT JOIN demo10 on demo9.m1 = demo10.m2 WHERE demo10.n2 IS NOT NULL;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
+------+------+------+------+
2 rows in set (0.00 sec)

由于指定了被驅(qū)動(dòng)表demo10的n2列不允許為NULL，所以上邊的demo9和demo10表的左外連接查詢(xún)和內(nèi)連接查詢(xún)是一樣的。當(dāng)然，我們也可以不用顯式的指定被驅(qū)動(dòng)表的某個(gè)列IS NOT NULL，只要隱含的有這個(gè)意思就行了，比如說(shuō)這樣：

mysql> SELECT * FROM demo9 LEFT JOIN demo10 on demo9.m1 = demo10.m2 WHERE demo10.m2 = 2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
+------+------+------+------+
1 row in set (0.01 sec)

在這個(gè)例子中，我們?cè)?code>WHERE子句中指定了被驅(qū)動(dòng)表demo10的m2列等于2，也就相當(dāng)于間接的指定了m2列不為NULL值，所以上邊的這個(gè)左外連接查詢(xún)其實(shí)和下邊這個(gè)內(nèi)連接查詢(xún)是等價(jià)的：

mysql> SELECT * FROM demo9 INNER JOIN demo10 on demo9.m1 = demo10.m2 WHERE demo10.m2 = 2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
+------+------+------+------+
1 row in set (0.01 sec)

我們把這種在外連接查詢(xún)中，指定的WHERE子句中包含被驅(qū)動(dòng)表中的列不為NULL值的條件稱(chēng)之為空值拒絕（英文名：reject-NULL）。在被驅(qū)動(dòng)表的WHERE子句符合空值拒絕的條件后，外連接和內(nèi)連接可以相互轉(zhuǎn)換。這種轉(zhuǎn)換帶來(lái)的好處就是查詢(xún)優(yōu)化器可以通過(guò)評(píng)估表的不同連接順序的成本，選出成本最低的那種連接順序來(lái)執(zhí)行查詢(xún)。

三、子查詢(xún)優(yōu)化

我們的主題本來(lái)是嘮叨MySQL查詢(xún)優(yōu)化器是如何處理子查詢(xún)的，但是我還是擔(dān)心好多同學(xué)連接查詢(xún)的語(yǔ)法都沒(méi)掌握全，所以我們就先學(xué)習(xí)什么是個(gè)子查詢(xún)（當(dāng)然不會(huì)面面俱到啦，只是說(shuō)個(gè)大概哈~），然后再學(xué)習(xí)關(guān)于子查詢(xún)優(yōu)化的事。

3.1 子查詢(xún)語(yǔ)法

想必大家都是媽媽生下來(lái)的吧，連孫猴子都有媽媽——石頭人。懷孕媽媽肚子里的那個(gè)東東就是她的孩子，類(lèi)似的，在一個(gè)查詢(xún)語(yǔ)句里的某個(gè)位置也可以有另一個(gè)查詢(xún)語(yǔ)句，這個(gè)出現(xiàn)在某個(gè)查詢(xún)語(yǔ)句的某個(gè)位置中的查詢(xún)就被稱(chēng)為子查詢(xún)（我們也可以稱(chēng)它為寶寶查詢(xún)哈~），那個(gè)充當(dāng)“媽媽”角色的查詢(xún)也被稱(chēng)之為外層查詢(xún)。不像我們懷孕時(shí)寶寶們都只在肚子里，子查詢(xún)可以在一個(gè)外層查詢(xún)的各種位置出現(xiàn)，比如：

1. SELECT 子句中

也就是我們平時(shí)說(shuō)的查詢(xún)列表，比如這樣：

mysql> SELECT (SELECT m1 FROM demo9 LIMIT 1);
+--------------------------------+
| (SELECT m1 FROM demo9 LIMIT 1) |
+--------------------------------+
|                              1 |
+--------------------------------+
1 row in set (0.01 sec)

其中的(SELECT m1 FROM demo9 LIMIT 1)就是我們嘮叨的子查詢(xún)。

2. FROM 子句中

比如：

mysql> SELECT m,n FROM (SELECT m2+1 AS m, n2 AS n FROM demo10 WHERE m2 > 1) AS t;
+------+------+
| m    | n    |
+------+------+
|    3 | b    |
|    4 | c    |
|    5 | d    |
+------+------+
3 rows in set (0.00 sec)

這個(gè)例子中的子查詢(xún)是：(SELECT m2 + 1 AS m, n2 AS n FROM demo10 WHERE m2 > 1)，很特別的地方是它出現(xiàn)在了FROM子句中。FROM子句這邊不是存放我們要查詢(xún)的表的名稱(chēng)么，這里放進(jìn)來(lái)一個(gè)子查詢(xún)是個(gè)什么鬼？其實(shí)這里我們可以把子查詢(xún)的查詢(xún)結(jié)果當(dāng)作是一個(gè)表，子查詢(xún)后邊的AS t表明這個(gè)子查詢(xún)的結(jié)果就相當(dāng)于一個(gè)名稱(chēng)為t的表，這個(gè)名叫t的表的列就是子查詢(xún)結(jié)果中的列，比如例子中表 t 就有兩個(gè)列：m 列和 n 列。這個(gè)放在FROM子句中的子查詢(xún)本質(zhì)上相當(dāng)于一個(gè)表，但是和我們平常使用的表有點(diǎn)不一樣，MySQL把這種由子查詢(xún)結(jié)果集組成的表稱(chēng)之為派生表。

3. WHERE或ON 子句中

把子查詢(xún)放在外層查詢(xún)的WHERE子句或者ON子句中可能是我們最常用的一種使用子查詢(xún)的方式了，比如這樣：

mysql> SELECT * FROM demo9 WHERE m1 IN (SELECT m2 FROM demo10);
+------+------+
| m1   | n1   |
+------+------+
|    2 | b    |
|    3 | c    |
+------+------+
2 rows in set (0.00 sec)

這個(gè)查詢(xún)表明我們想要將(SELECT m2 FROM demo10)這個(gè)子查詢(xún)的結(jié)果作為外層查詢(xún)的IN語(yǔ)句參數(shù)，整個(gè)查詢(xún)語(yǔ)句的意思就是我們想找demo9表中的某些記錄，這些記錄的m1列的值能在demo10表的m2列找到匹配的值。

4. ORDER BY和GROUP BY 子句中

雖然語(yǔ)法支持，但沒(méi)啥子意義，不嘮叨這種情況了

3.1.1 按返回的結(jié)果集區(qū)分子查詢(xún)

因?yàn)樽硬樵?xún)本身也算是一個(gè)查詢(xún)，所以可以按照它們返回的不同結(jié)果集類(lèi)型而把這些子查詢(xún)分為不同的類(lèi)型：

1. 標(biāo)量子查詢(xún)

那些只返回一個(gè)單一值的子查詢(xún)稱(chēng)之為標(biāo)量子查詢(xún)，比如這樣：

mysql> SELECT (SELECT m1 FROM demo9 LIMIT 1);
+--------------------------------+
| (SELECT m1 FROM demo9 LIMIT 1) |
+--------------------------------+
|                              1 |
+--------------------------------+
1 row in set (0.00 sec)

或者這樣：

mysql> SELECT * FROM demo9 WHERE m1 = (SELECT MIN(m2) FROM demo10);
+------+------+
| m1   | n1   |
+------+------+
|    2 | b    |
+------+------+
1 row in set (0.00 sec)

這兩個(gè)查詢(xún)語(yǔ)句中的子查詢(xún)都返回一個(gè)單一的值，也就是一個(gè)標(biāo)量。這些標(biāo)量子查詢(xún)可以作為一個(gè)單一值或者表達(dá)式的一部分出現(xiàn)在查詢(xún)語(yǔ)句的各個(gè)地方。

2. 行子查詢(xún)

顧名思義，就是返回一條記錄的子查詢(xún)，不過(guò)這條記錄需要包含多個(gè)列（只包含一個(gè)列就成了標(biāo)量子查詢(xún)了）。比如這樣：

mysql> SELECT * FROM demo9 WHERE (m1, n1) = (SELECT m2, n2 FROM demo10 LIMIT 1);
+------+------+
| m1   | n1   |
+------+------+
|    2 | b    |
+------+------+
1 row in set (0.00 sec)

其中的(SELECT m2, n2 FROM demo10 LIMIT 1)就是一個(gè)行子查詢(xún)，整條語(yǔ)句的含義就是要從demo9表中找一些記錄，這些記錄的m1和n1列分別等于子查詢(xún)結(jié)果中的m2和n2列。

3. 列子查詢(xún)

列子查詢(xún)自然就是查詢(xún)出一個(gè)列的數(shù)據(jù)嘍，不過(guò)這個(gè)列的數(shù)據(jù)需要包含多條記錄（只包含一條記錄就成了標(biāo)量子查詢(xún)了）。比如這樣：

mysql> SELECT * FROM demo9 WHERE m1 IN (SELECT m2 FROM demo10);
+------+------+
| m1   | n1   |
+------+------+
|    2 | b    |
|    3 | c    |
+------+------+
2 rows in set (0.00 sec)

其中的(SELECT m2 FROM demo10)就是一個(gè)列子查詢(xún)，表明查詢(xún)出demo10表的m2列的值作為外層查詢(xún)IN語(yǔ)句的參數(shù)。

4. 表子查詢(xún)

顧名思義，就是子查詢(xún)的結(jié)果既包含很多條記錄，又包含很多個(gè)列，比如這樣：

mysql> SELECT * FROM demo9 WHERE (m1, n1) IN (SELECT m2, n2 FROM demo10);
+------+------+
| m1   | n1   |
+------+------+
|    2 | b    |
|    3 | c    |
+------+------+
2 rows in set (0.00 sec)

其中的(SELECT m2, n2 FROM demo10)就是一個(gè)表子查詢(xún)，這里需要和行子查詢(xún)對(duì)比一下，行子查詢(xún)中我們用了LIMIT 1來(lái)保證子查詢(xún)的結(jié)果只有一條記錄，表子查詢(xún)中不需要這個(gè)限制。

3.1.2 按與外層查詢(xún)關(guān)系來(lái)區(qū)分子查詢(xún)

1. 不相關(guān)子查詢(xún)

如果子查詢(xún)可以單獨(dú)運(yùn)行出結(jié)果，而不依賴(lài)于外層查詢(xún)的值，我們就可以把這個(gè)子查詢(xún)稱(chēng)之為不相關(guān)子查詢(xún)。我們前邊介紹的那些子查詢(xún)?nèi)慷伎梢钥醋鞑幌嚓P(guān)子查詢(xún)，所以也就不舉例子了哈~

2. 相關(guān)子查詢(xún)

如果子查詢(xún)的執(zhí)行需要依賴(lài)于外層查詢(xún)的值，我們就可以把這個(gè)子查詢(xún)稱(chēng)之為相關(guān)子查詢(xún)。比如：

mysql> SELECT * FROM demo9 WHERE m1 IN (SELECT m2 FROM demo10 WHERE n1 = n2);
+------+------+
| m1   | n1   |
+------+------+
|    2 | b    |
|    3 | c    |
+------+------+
2 rows in set (0.00 sec)

例子中的子查詢(xún)是(SELECT m2 FROM demo10 WHERE n1 = n2)，可是這個(gè)查詢(xún)中有一個(gè)搜索條件是n1 = n2，別忘了n1是表demo9的列，也就是外層查詢(xún)的列，也就是說(shuō)子查詢(xún)的執(zhí)行需要依賴(lài)于外層查詢(xún)的值，所以這個(gè)子查詢(xún)就是一個(gè)相關(guān)子查詢(xún)。

3.1.3 子查詢(xún)?cè)诓紶柋磉_(dá)式中的使用

你說(shuō)寫(xiě)下邊這樣的子查詢(xún)有啥意義：

SELECT (SELECT m1 FROM demo9 LIMIT 1);

貌似沒(méi)啥意義～ 我們平時(shí)用子查詢(xún)最多的地方就是把它作為布爾表達(dá)式的一部分來(lái)作為搜索條件用在WHERE子句或者ON子句里。所以我們這里來(lái)總結(jié)一下子查詢(xún)?cè)诓紶柋磉_(dá)式中的使用場(chǎng)景

1. 使用=、>、<、>=、<=、<>、!=、<=>作為布爾表達(dá)式的操作符

這些操作符具體是啥意思就不用我多介紹了吧，如果你不知道的話，那我真的很佩服你是靠著啥勇氣一口看到這里的～ 為了方便，我們就把這些操作符稱(chēng)為comparison_operator吧，所以子查詢(xún)組成的布爾表達(dá)式就長(zhǎng)這樣：

操作數(shù) comparison_operator (子查詢(xún))

這里的操作數(shù)可以是某個(gè)列名，或者是一個(gè)常量，或者是一個(gè)更復(fù)雜的表達(dá)式，甚至可以是另一個(gè)子查詢(xún)。但是需要注意的是，這里的子查詢(xún)只能是標(biāo)量子查詢(xún)或者行子查詢(xún)，也就是子查詢(xún)的結(jié)果只能返回一個(gè)單一的值或者只能是一條記錄。

比如這樣（標(biāo)量子查詢(xún)）：

mysql> SELECT * FROM demo9 WHERE m1 < (SELECT MIN(m2) FROM demo10);
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
+------+------+
1 row in set (0.00 sec)

或者這樣（行子查詢(xún)）：

mysql> SELECT * FROM demo9 WHERE (m1, n1) = (SELECT m2, n2 FROM demo10 LIMIT 1);
+------+------+
| m1   | n1   |
+------+------+
|    2 | b    |
+------+------+
1 row in set (0.00 sec)

2. [NOT] IN/ANY/SOME/ALL子查詢(xún)

對(duì)于列子查詢(xún)和表子查詢(xún)來(lái)說(shuō)，它們的結(jié)果集中包含很多條記錄，這些記錄相當(dāng)于是一個(gè)集合，所以就不能單純的和另外一個(gè)操作數(shù)使用comparison_operator來(lái)組成布爾表達(dá)式了，MySQL通過(guò)下列的語(yǔ)法來(lái)支持某個(gè)操作數(shù)和一個(gè)集合組成一個(gè)布爾表達(dá)式：

a. IN或者NOT IN

具體的語(yǔ)法形式如下：操作數(shù) [NOT] IN (子查詢(xún))

這個(gè)布爾表達(dá)式的意思是用來(lái)判斷某個(gè)操作數(shù)在不在由子查詢(xún)結(jié)果集組成的集合中，比如下邊的查詢(xún)的意思是找出demo9表中的某些記錄，這些記錄存在于子查詢(xún)的結(jié)果集中：

mysql> SELECT * FROM demo9 WHERE (m1, n1) IN (SELECT m2, n2 FROM demo10);
+------+------+
| m1   | n1   |
+------+------+
|    2 | b    |
|    3 | c    |
+------+------+
2 rows in set (0.00 sec)

b. ANY/SOME（ANY和SOME是同義詞） 具體的語(yǔ)法形式如下：操作數(shù) comparison_operator ANY/SOME(子查詢(xún))

這個(gè)布爾表達(dá)式的意思是只要子查詢(xún)結(jié)果集中存在某個(gè)值和給定的操作數(shù)做comparison_operator比較結(jié)果為TRUE，那么整個(gè)表達(dá)式的結(jié)果就為TRUE，否則整個(gè)表達(dá)式的結(jié)果就為FALSE。比如說(shuō)下邊這個(gè)查詢(xún)：

mysql> SELECT * FROM demo9 WHERE m1 > ANY(SELECT m2 FROM demo10);
+------+------+
| m1   | n1   |
+------+------+
|    3 | c    |
+------+------+
1 row in set (0.00 sec)

這個(gè)查詢(xún)的意思就是對(duì)于demo9表的某條記錄的m1列的值來(lái)說(shuō)，如果子查詢(xún)(SELECT m2 FROM demo10)的結(jié)果集中存在一個(gè)小于m1列的值，那么整個(gè)布爾表達(dá)式的值就是TRUE，否則為FALSE，也就是說(shuō)只要m1列的值大于子查詢(xún)結(jié)果集中最小的值，整個(gè)表達(dá)式的結(jié)果就是TRUE，所以上邊的查詢(xún)本質(zhì)上等價(jià)于這個(gè)查詢(xún)：

mysql> SELECT * FROM demo9 WHERE m1 > (SELECT MIN(m2) FROM demo10);
+------+------+
| m1   | n1   |
+------+------+
|    3 | c    |
+------+------+
1 row in set (0.01 sec)

另外，=ANY相當(dāng)于判斷子查詢(xún)結(jié)果集中是否存在某個(gè)值和給定的操作數(shù)相等，它的含義和IN是相同的

c. ALL

具體的語(yǔ)法形式如下：操作數(shù) comparison_operator ALL(子查詢(xún))

這個(gè)布爾表達(dá)式的意思是子查詢(xún)結(jié)果集中所有的值和給定的操作數(shù)做comparison_operator比較結(jié)果為TRUE，那么整個(gè)表達(dá)式的結(jié)果就為TRUE，否則整個(gè)表達(dá)式的結(jié)果就為FALSE。比如說(shuō)下邊這個(gè)查詢(xún)：

mysql> SELECT * FROM demo9 WHERE m1 > ALL(SELECT m2 FROM demo10);
Empty set (0.00 sec)

這個(gè)查詢(xún)的意思就是對(duì)于demo9表的某條記錄的m1列的值來(lái)說(shuō)，如果子查詢(xún)(SELECT m2 FROM demo10)的結(jié)果集中的所有值都大于m1列的值，那么整個(gè)布爾表達(dá)式的值就是TRUE，否則為FALSE，也就是說(shuō)只要m1列的值大于子查詢(xún)結(jié)果集中最小的值，整個(gè)表達(dá)式的結(jié)果就是TRUE，所以上邊的查詢(xún)本質(zhì)上等價(jià)于這個(gè)查詢(xún)：

mysql> SELECT * FROM demo9 WHERE m1 > (SELECT MAX(m2) FROM demo10);
Empty set (0.00 sec)

d. EXISTS子查詢(xún)

有的時(shí)候我們僅僅需要判斷子查詢(xún)的結(jié)果集中是否有記錄，而不在乎它的記錄具體是個(gè)啥，可以使用把EXISTS或者NOT EXISTS放在子查詢(xún)語(yǔ)句前邊，就像這樣：[NOT] EXISTS (子查詢(xún))

我們舉一個(gè)例子?。?/p>

mysql> SELECT * FROM demo9 WHERE EXISTS (SELECT 1 FROM demo10);
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
+------+------+
3 rows in set (0.00 sec)

對(duì)于子查詢(xún)(SELECT 1 FROM demo10)來(lái)說(shuō)，我們并不關(guān)心這個(gè)子查詢(xún)最后到底查詢(xún)出的結(jié)果是什么，所以查詢(xún)列表里填*、某個(gè)列名，或者其他啥東系都無(wú)所謂，我們真正關(guān)心的是子查詢(xún)的結(jié)果集中是否存在記錄。也就是說(shuō)只要(SELECT 1 FROM demo10)這個(gè)查詢(xún)中有記錄，那么整個(gè)EXISTS表達(dá)式的結(jié)果就為TRUE。

3.1.4 子查詢(xún)語(yǔ)法注意事項(xiàng)

1. 子查詢(xún)必須使用括號(hào)擴(kuò)起來(lái)

不擴(kuò)起來(lái)的子查詢(xún)是非法的，比如這樣：

mysql> SELECT SELECT m1 FROM demo9;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near 'SELECT m1 FROM demo9' at line 1

2. 在SELECT子句中的子查詢(xún)必須是標(biāo)量子查詢(xún)

如果子查詢(xún)結(jié)果集中有多個(gè)列或者多個(gè)行，都不允許放在SELECT子句中，也就是查詢(xún)列表中，比如這樣就是非法的：

mysql> SELECT (SELECT m1, n1 FROM demo9);
ERROR 1241 (21000): Operand should contain 1 column(s)

3. 在想要得到標(biāo)量子查詢(xún)或者行子查詢(xún)，但又不能保證子查詢(xún)的結(jié)果集只有一條記錄時(shí)，應(yīng)該使用LIMIT 1語(yǔ)句來(lái)限制記錄數(shù)量。

4. 對(duì)于[NOT] IN/ANY/SOME/ALL子查詢(xún)來(lái)說(shuō)，子查詢(xún)中不允許有LIMIT語(yǔ)句

比如這樣是非法的：

mysql> SELECT * FROM demo9 WHERE m1 IN (SELECT * FROM demo10 LIMIT 2);
ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

為啥不合法？人家就這么規(guī)定的，不解釋～ 可能以后的版本會(huì)支持吧。正因?yàn)?code>[NOT] IN/ANY/SOME/ALL子查詢(xún)不支持LIMIT語(yǔ)句，所以子查詢(xún)中的這些語(yǔ)句也就是多余的了

5. ORDER BY子句

子查詢(xún)的結(jié)果其實(shí)就相當(dāng)于一個(gè)集合，集合里的值排不排序一點(diǎn)都不重要，比如下邊這個(gè)語(yǔ)句中的ORDER BY子句簡(jiǎn)直就是畫(huà)蛇添足：

SELECT * FROM demo9 WHERE m1 IN (SELECT m2 FROM demo10 ORDER BY m2);

6. DISTINCT語(yǔ)句

集合里的值去不去重也沒(méi)啥意義，比如這樣：

SELECT * FROM demo9 WHERE m1 IN (SELECT DISTINCT m2 FROM demo10);

7. 沒(méi)有聚集函數(shù)以及HAVING子句的GROUP BY子句

在沒(méi)有聚集函數(shù)以及HAVING子句時(shí)，GROUP BY子句就是個(gè)擺設(shè)，比如這樣：

SELECT * FROM demo9 WHERE m1 IN (SELECT m2 FROM demo10 GROUP BY m2);

對(duì)于這些冗余的語(yǔ)句，查詢(xún)優(yōu)化器在一開(kāi)始就把它們給干掉了。

8. 不允許在一條語(yǔ)句中增刪改某個(gè)表的記錄時(shí)同時(shí)還對(duì)該表進(jìn)行子查詢(xún)

比方說(shuō)這樣：

mysql> DELETE FROM demo9 WHERE m1 < (SELECT MAX(m1) FROM demo9);
ERROR 1093 (HY000): You can't specify target table 'demo9' for update in FROM clause

3.2 子查詢(xún)?cè)贛ySQL中是怎么執(zhí)行的

好了，關(guān)于子查詢(xún)的基礎(chǔ)語(yǔ)法我們用最快的速度溫習(xí)了一遍，如果想了解更多語(yǔ)法細(xì)節(jié)，大家可以去查看一下MySQL的文檔哈~現(xiàn)在我們就假設(shè)各位都懂了啥是個(gè)子查詢(xún)了喔，接下來(lái)就要嘮叨具體某種類(lèi)型的子查詢(xún)?cè)贛ySQL中是怎么執(zhí)行的了，想想是不是就有一點(diǎn)激動(dòng)～ 當(dāng)然，為了故事的順利發(fā)展，我們的例子也需要跟隨形勢(shì)換槍換炮，還是要祭出我們?cè)谝晃膸懔私釳ySQL之基于成本的優(yōu)化文章中用到的s1表和s2表

mysql> create table s1 (    
id int not null auto_increment,    
key1 varchar(100),    
key2 int,    
key3 varchar(100),    
key_part1 varchar(100),    
key_part2 varchar(100),    
key_part3 varchar(100),    
common_field varchar(100), 
primary key (id),
key idx_key1 (key1),    
unique key idx_key2 (key2),    
key idx_key3 (key3),    
key idx_key_part(key_part1, key_part2, key_part3));
Query OK, 0 rows affected (0.04 sec)
mysql> create table s2 (    
id int not null auto_increment,    
key1 varchar(100),    
key2 int,    
key3 varchar(100),    
key_part1 varchar(100),    
key_part2 varchar(100),    
key_part3 varchar(100),    
common_field varchar(100), 
primary key (id),
key idx_key1 (key1),    
unique key idx_key2 (key2),    
key idx_key3 (key3),    
key idx_key_part(key_part1, key_part2, key_part3));
Query OK, 0 rows affected (0.04 sec)
mysql> insert into s1 select * from demo8;
Query OK, 20000 rows affected (0.83 sec)
Records: 20000  Duplicates: 0  Warnings: 0
mysql> insert into s2 select * from demo8;
Query OK, 20000 rows affected (0.89 sec)
Records: 20000  Duplicates: 0  Warnings: 0

這兩個(gè)表s1、s2的構(gòu)造是相同的，并且這兩個(gè)表里邊都有20000條記錄，除id列外其余的列都插入隨機(jī)值。下邊正式開(kāi)始我們的表演。

3.2.1 小白們眼中子查詢(xún)的執(zhí)行方式

在我還是一個(gè)單純無(wú)知的少年時(shí)，覺(jué)得子查詢(xún)的執(zhí)行方式是這樣的：

• 如果該子查詢(xún)是不相關(guān)子查詢(xún)，比如下邊這個(gè)查詢(xún)：SELECT * FROM s1WHERE key1 IN (SELECT common_field FROM s2)

  我年少時(shí)覺(jué)得這個(gè)查詢(xún)是的執(zhí)行方式是這樣的：

  • 先單獨(dú)執(zhí)行(SELECT common_field FROM s2)這個(gè)子查詢(xún)
  • 然后在將上一步子查詢(xún)得到的結(jié)果當(dāng)作外層查詢(xún)的參數(shù)再執(zhí)行外層查詢(xún)SELECT * FROM s1 WHERE key1 IN (...)。

• 如果該子查詢(xún)是相關(guān)子查詢(xún)，比如下邊這個(gè)查詢(xún)：SELECT * FROM s1 WHERE key1 IN (SELECT common_field FROM s2 WHERE s1.key2 = s2.key2)

  這個(gè)查詢(xún)中的子查詢(xún)中出現(xiàn)了s1.key2 = s2.key2這樣的條件，意味著該子查詢(xún)的執(zhí)行依賴(lài)著外層查詢(xún)的值，所以我年少時(shí)覺(jué)得這個(gè)查詢(xún)的執(zhí)行方式是這樣的：

  • 先從外層查詢(xún)中獲取一條記錄，本例中也就是先從s1表中獲取一條記錄
  • 然后從上一步驟中獲取的那條記錄中找出子查詢(xún)中涉及到的值，本例中就是從s1表中獲取的那條記錄中找出s1.key2列的值，然后執(zhí)行子查詢(xún)
  • 最后根據(jù)子查詢(xún)的查詢(xún)結(jié)果來(lái)檢測(cè)外層查詢(xún)WHERE子句的條件是否成立，如果成立，就把外層查詢(xún)的那條記錄加入到結(jié)果集，否則就丟棄
  • 再次執(zhí)行第一步，獲取第二條外層查詢(xún)中的記錄，依次類(lèi)推～

不要告訴我只是一個(gè)人是這樣認(rèn)為的~

其實(shí)MySQL想了一系列的辦法來(lái)優(yōu)化子查詢(xún)的執(zhí)行，大部分情況下這些優(yōu)化措施其實(shí)挺有效的，但是保不齊有的時(shí)候馬失前蹄，下邊我們?cè)敿?xì)嘮叨各種不同類(lèi)型的子查詢(xún)具體是怎么執(zhí)行的。

3.2.2 標(biāo)量子查詢(xún)、行子查詢(xún)的執(zhí)行方式

我們經(jīng)常在下邊兩個(gè)場(chǎng)景中使用到標(biāo)量子查詢(xún)或者行子查詢(xún)：

• SELECT子句中，我們前邊說(shuō)過(guò)的在查詢(xún)列表中的子查詢(xún)必須是標(biāo)量子查詢(xún)
• 子查詢(xún)使用=、>、<、>=、<=、<>、!=、<=>等操作符和某個(gè)操作數(shù)組成一個(gè)布爾表達(dá)式，這樣的子查詢(xún)必須是標(biāo)量子查詢(xún)或者行子查詢(xún)。

對(duì)于上述兩種場(chǎng)景中的不相關(guān)（注意是不相關(guān)不相關(guān)不相關(guān)）標(biāo)量子查詢(xún)或者行子查詢(xún)來(lái)說(shuō)，它們的執(zhí)行方式是簡(jiǎn)單的，比如說(shuō)下邊這個(gè)查詢(xún)語(yǔ)句：

SELECT * FROM s1 WHERE key1 = (SELECT common_field FROM s2 WHERE key3 = 'a' LIMIT 1);

它的執(zhí)行方式和年少的我想的一樣：

• 先單獨(dú)執(zhí)行(SELECT common_field FROM s2 WHERE key3 = 'a' LIMIT 1)這個(gè)子查詢(xún)
• 然后在將上一步子查詢(xún)得到的結(jié)果當(dāng)作外層查詢(xún)的參數(shù)再執(zhí)行外層查詢(xún)SELECT * FROM s1 WHERE key1 = ...

也就是說(shuō)，對(duì)于包含不相關(guān)的標(biāo)量子查詢(xún)或者行子查詢(xún)的查詢(xún)語(yǔ)句來(lái)說(shuō)，MySQL會(huì)分別獨(dú)立的執(zhí)行外層查詢(xún)和子查詢(xún)，就當(dāng)作兩個(gè)單表查詢(xún)就好了。

對(duì)于相關(guān)的標(biāo)量子查詢(xún)或者行子查詢(xún)來(lái)說(shuō)，比如下邊這個(gè)查詢(xún)：

SELECT * FROM s1 WHERE key1 = (SELECT common_field FROM s2 WHERE s1.key3 = s2.key3 LIMIT 1);

事情也和年少的我想的一樣，它的執(zhí)行方式就是這樣的：

• 先從外層查詢(xún)中獲取一條記錄，本例中也就是先從s1表中獲取一條記錄
• 然后從上一步驟中獲取的那條記錄中找出子查詢(xún)中涉及到的值，本例中就是從s1表中獲取的那條記錄中找出s1.key3列的值，然后執(zhí)行子查詢(xún)
• 最后根據(jù)子查詢(xún)的查詢(xún)結(jié)果來(lái)檢測(cè)外層查詢(xún)WHERE子句的條件是否成立，如果成立，就把外層查詢(xún)的那條記錄加入到結(jié)果集，否則就丟棄。 再次執(zhí)行第一步，獲取第二條外層查詢(xún)中的記錄，依次類(lèi)推～

也就是說(shuō)對(duì)于一開(kāi)始嘮叨的兩種使用標(biāo)量子查詢(xún)以及行子查詢(xún)的場(chǎng)景中，MySQL優(yōu)化器的執(zhí)行方式并沒(méi)有什么新鮮的。

3.3 IN子查詢(xún)優(yōu)化

3.3.1 物化表的提出

對(duì)于不相關(guān)的IN子查詢(xún)，比如這樣：

SELECT * FROM s1 WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');

我們最開(kāi)始的感覺(jué)就是這種不相關(guān)的IN子查詢(xún)和不相關(guān)的標(biāo)量子查詢(xún)或者行子查詢(xún)是一樣一樣的，都是把外層查詢(xún)和子查詢(xún)當(dāng)作兩個(gè)獨(dú)立的單表查詢(xún)來(lái)對(duì)待，可是很遺憾的是MySQL為了優(yōu)化IN子查詢(xún)傾注了太多心血（畢竟IN子查詢(xún)是我們?nèi)丈钪凶畛Ｓ玫淖硬樵?xún)類(lèi)型），所以整個(gè)執(zhí)行過(guò)程并不像我們想象的那么簡(jiǎn)單~

其實(shí)說(shuō)句老實(shí)話，對(duì)于不相關(guān)的IN子查詢(xún)來(lái)說(shuō)，如果子查詢(xún)的結(jié)果集中的記錄條數(shù)很少，那么把子查詢(xún)和外層查詢(xún)分別看成兩個(gè)單獨(dú)的單表查詢(xún)效率還是蠻高的，但是如果單獨(dú)執(zhí)行子查詢(xún)后的結(jié)果集太多的話，就會(huì)導(dǎo)致這些問(wèn)題：

• 結(jié)果集太多，可能內(nèi)存中都放不下～
• 對(duì)于外層查詢(xún)來(lái)說(shuō)，如果子查詢(xún)的結(jié)果集太多，那就意味著IN子句中的參數(shù)特別多，這就導(dǎo)致：

  • 無(wú)法有效的使用索引，只能對(duì)外層查詢(xún)進(jìn)行全表掃描

  • 在對(duì)外層查詢(xún)執(zhí)行全表掃描時(shí)，由于IN子句中的參數(shù)太多，這會(huì)導(dǎo)致檢測(cè)一條記錄是否符合和IN子句中的參數(shù)匹配花費(fèi)的時(shí)間太長(zhǎng)，比如說(shuō)IN子句中的參數(shù)只有兩個(gè)：SELECT * FROM tbl_name WHERE column IN (a, b)

    這樣相當(dāng)于需要對(duì)tbl_name表中的每條記錄判斷一下它的column列是否符合column = a OR column = b。在IN子句中的參數(shù)比較少時(shí)這并不是什么問(wèn)題，如果IN子句中的參數(shù)比較多時(shí)，比如這樣：SELECT * FROM tbl_name WHERE column IN (a, b, c ..., ...)

    那么這樣每條記錄需要判斷一下它的column列是否符合column = a OR column = b OR column = c OR ...，這樣性能耗費(fèi)可就多了。

于是MySQL想了一個(gè)招：不直接將不相關(guān)子查詢(xún)的結(jié)果集當(dāng)作外層查詢(xún)的參數(shù)，而是將該結(jié)果集寫(xiě)入一個(gè)臨時(shí)表里。寫(xiě)入臨時(shí)表的過(guò)程是這樣的：

• 該臨時(shí)表的列就是子查詢(xún)結(jié)果集中的列

• 寫(xiě)入臨時(shí)表的記錄會(huì)被去重

  我們說(shuō)IN語(yǔ)句是判斷某個(gè)操作數(shù)在不在某個(gè)集合中，集合中的值重不重復(fù)對(duì)整個(gè)IN語(yǔ)句的結(jié)果并沒(méi)有啥子關(guān)系，所以我們?cè)趯⒔Y(jié)果集寫(xiě)入臨時(shí)表時(shí)對(duì)記錄進(jìn)行去重可以讓臨時(shí)表變得更小，更省地方～

  小提示： 臨時(shí)表如何對(duì)記錄進(jìn)行去重？這不是小意思嘛，臨時(shí)表也是個(gè)表，只要為表中記錄的所有列建立主鍵或者唯一索引就好了嘛～

• 一般情況下子查詢(xún)結(jié)果集不會(huì)大的離譜，所以會(huì)為它建立基于內(nèi)存的使用Memory存儲(chǔ)引擎的臨時(shí)表，而且會(huì)為該表建立哈希索引。

  小提示： IN語(yǔ)句的本質(zhì)就是判斷某個(gè)操作數(shù)在不在某個(gè)集合里，如果集合中的數(shù)據(jù)建立了哈希索引，那么這個(gè)匹配的過(guò)程就是超級(jí)快的。 有同學(xué)不知道哈希索引是什么？我這里就不展開(kāi)了，自己上網(wǎng)找找吧，不會(huì)了再來(lái)問(wèn)我～

  如果子查詢(xún)的結(jié)果集非常大，超過(guò)了系統(tǒng)變量tmp_table_size或者max_heap_table_size，臨時(shí)表會(huì)轉(zhuǎn)而使用基于磁盤(pán)的存儲(chǔ)引擎來(lái)保存結(jié)果集中的記錄，索引類(lèi)型也對(duì)應(yīng)轉(zhuǎn)變?yōu)锽+樹(shù)索引。

MySQL的把這個(gè)將子查詢(xún)結(jié)果集中的記錄保存到臨時(shí)表的過(guò)程稱(chēng)之為物化（英文名：Materialize）。為了方便起見(jiàn)，我們就把那個(gè)存儲(chǔ)子查詢(xún)結(jié)果集的臨時(shí)表稱(chēng)之為物化表。正因?yàn)槲锘碇械挠涗浂冀⒘怂饕ɑ趦?nèi)存的物化表有哈希索引，基于磁盤(pán)的有B+樹(shù)索引），通過(guò)索引執(zhí)行IN語(yǔ)句判斷某個(gè)操作數(shù)在不在子查詢(xún)結(jié)果集中變得?常快，從而提升了子查詢(xún)語(yǔ)句的性能。

3.3.2 物化表轉(zhuǎn)連接

事情到這就完了？我們還得重新審視一下最開(kāi)始的那個(gè)查詢(xún)語(yǔ)句：

SELECT * FROM s1 WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');

當(dāng)我們把子查詢(xún)進(jìn)行物化之后，假設(shè)子查詢(xún)物化表的名稱(chēng)為materialized_table，該物化表存儲(chǔ)的子查詢(xún)結(jié)果集的列為m_val，那么這個(gè)查詢(xún)其實(shí)可以從下邊兩種角度來(lái)看待：

• 從表s1的角度來(lái)看待，整個(gè)查詢(xún)的意思其實(shí)是：對(duì)于s1表中的每條記錄來(lái)說(shuō)，如果該記錄的key1列的值在子查詢(xún)對(duì)應(yīng)的物化表中，則該記錄會(huì)被加入最終的結(jié)果集。畫(huà)個(gè)圖表示一下就是這樣：

  

• 從子查詢(xún)物化表的角度來(lái)看待，整個(gè)查詢(xún)的意思其實(shí)是：對(duì)于子查詢(xún)物化表的每個(gè)值來(lái)說(shuō)，如果能在s1表中找到對(duì)應(yīng)的key1列的值與該值相等的記錄，那么就把這些記錄加入到最終的結(jié)果集。畫(huà)個(gè)圖表示如下就是這樣：

  

也就是說(shuō)其實(shí)上邊的查詢(xún)就相當(dāng)于表s1和子查詢(xún)物化表materialized_table進(jìn)行內(nèi)連接：

SELECT s1.* FROM s1 INNER JOIN materialized_table ON key1 = m_val;

轉(zhuǎn)化成內(nèi)連接之后就有意思了，查詢(xún)優(yōu)化器可以評(píng)估不同連接順序需要的成本是多少，選取成本最低的那種查詢(xún)方式執(zhí)行查詢(xún)。我們分析一下上述查詢(xún)中使用外層查詢(xún)的s1表和物化表materialized_table進(jìn)行內(nèi)連接的成本都是由哪幾部分組成的：

• 如果使用s1表作為驅(qū)動(dòng)表的話，總查詢(xún)成本由下邊幾個(gè)部分組成：

  • 物化子查詢(xún)時(shí)需要的成本
  • 掃描s1表時(shí)的成本
  • s1表中的記錄數(shù)量 × 通過(guò)m_val = xxx對(duì)materialized_table表進(jìn)行單表訪問(wèn)的成本（我們前邊說(shuō)過(guò)物化表中的記錄是不重復(fù)的，并且為物化表中的列建里了索引，所以這個(gè)步驟顯然是非常快的）

• 如果使用materialized_table表作為驅(qū)動(dòng)表的話，總查詢(xún)成本由下邊幾個(gè)部分組成：

  • 物化子查詢(xún)時(shí)需要的成本
  • 掃描物化表時(shí)的成本
  • 物化表中的記錄數(shù)量 × 通過(guò)key1 = xxx對(duì)s1表進(jìn)行單表訪問(wèn)的成本（非常慶幸key1列上建立了索引，所以這個(gè)步驟是非?？斓模?/li>

MySQL查詢(xún)優(yōu)化器會(huì)通過(guò)運(yùn)算來(lái)選擇上述成本更低的方案來(lái)執(zhí)行查詢(xún)

3.3.3 將子查詢(xún)轉(zhuǎn)換為semi-join

雖然將子查詢(xún)進(jìn)行物化之后再執(zhí)行查詢(xún)都會(huì)有建立臨時(shí)表的成本，但是不管怎么說(shuō)，我們見(jiàn)識(shí)到了將子查詢(xún)轉(zhuǎn)換為連接的強(qiáng)大作用，MySQL繼續(xù)開(kāi)腦洞：能不能不進(jìn)行物化操作直接把子查詢(xún)轉(zhuǎn)換為連接呢？讓我們重新審視一下上邊的查詢(xún)語(yǔ)句：

SELECT * FROM s1 WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');

我們可以把這個(gè)查詢(xún)理解成：對(duì)于s1表中的某條記錄，如果我們能在s2表（準(zhǔn)確的說(shuō)是執(zhí)行完WHERE s2.key3 = 'a'之后的結(jié)果集）中找到一條或多條記錄，這些記錄的common_field的值等于s1表記錄的key1列的值，那么該條s1表的記錄就會(huì)被加入到最終的結(jié)果集。這個(gè)過(guò)程其實(shí)和把s1和s2兩個(gè)表連接起來(lái)的效果很像：

SELECT s1.* FROM s1 INNER JOIN s2 ON s1.key1 = s2.common_field WHERE s2.key3 = 'a';

只不過(guò)我們不能保證對(duì)于s1表的某條記錄來(lái)說(shuō)，在s2表（準(zhǔn)確的說(shuō)是執(zhí)行完WHERE s2.key3 = 'a'之后的結(jié)果集）中有多少條記錄滿足s1.key1 =s2.common_field這個(gè)條件，不過(guò)我們可以分三種情況討論：

• 情況一：對(duì)于s1表的某條記錄來(lái)說(shuō)，s2表中沒(méi)有任何記錄滿足s1.key1 = s2.common_field這個(gè)條件，那么該記錄自然也不會(huì)加入到最后的結(jié)果集
• 情況二：對(duì)于s1表的某條記錄來(lái)說(shuō)，s2表中有且只有記錄滿足s1.key1 = s2.common_field這個(gè)條件，那么該記錄會(huì)被加入最終的結(jié)果集
• 情況三：對(duì)于s1表的某條記錄來(lái)說(shuō)，s2表中至少有2條記錄滿足s1.key1 = s2.common_field這個(gè)條件，那么該記錄會(huì)被多次加入最終的結(jié)果集

對(duì)于s1表的某條記錄來(lái)說(shuō)，由于我們只關(guān)心s2表中是否存在記錄滿足s1.key1 = s2.common_field這個(gè)條件，而不關(guān)心具體有多少條記錄與之匹配，又因?yàn)橛?code>情況三的存在，我們上邊所說(shuō)的IN子查詢(xún)和兩表連接之間并不完全等價(jià)。但是將子查詢(xún)轉(zhuǎn)換為連接又真的可以充分發(fā)揮優(yōu)化器的作用，所以MySQL在這里提出了一個(gè)新概念 --- 半連接（英文名：semi-join）。將s1表和s2表進(jìn)行半連接的意思就是：對(duì)于s1表的某條記錄來(lái)說(shuō)，我們只關(guān)心在s2表中是否存在與之匹配的記錄是否存在，而不關(guān)心具體有多少條記錄與之匹配，最終的結(jié)果集中只保留s1表的記錄。為了讓大家有更直觀的感受，我們假設(shè)MySQL內(nèi)部是這么改寫(xiě)上邊的子查詢(xún)的：

SELECT s1.* FROM s1 SEMI JOIN s2 ON s1.key1 = s2.common_field WHERE key3 = 'a';

小提示： semi-join只是在MySQL內(nèi)部采用的一種執(zhí)行子查詢(xún)的方式，MySQL并沒(méi)有提供面向用戶的semi-join語(yǔ)法，所以我們不需要，也不能?chē)L試把上邊這個(gè)語(yǔ)句放到黑框框里運(yùn)行，我只是想說(shuō)明一下上邊的子查詢(xún)?cè)贛ySQL內(nèi)部會(huì)被轉(zhuǎn)換為類(lèi)似上邊語(yǔ)句的半連接～

概念是有了，怎么實(shí)現(xiàn)這種所謂的半連接呢？MySQL準(zhǔn)備了好幾種辦法

1. Table pullout （子查詢(xún)中的表上拉）

當(dāng)子查詢(xún)的查詢(xún)列表處只有主鍵或者唯一索引列時(shí)，可以直接把子查詢(xún)中的表上拉到外層查詢(xún)的FROM子句中，并把子查詢(xún)中的搜索條件合并到外層查詢(xún)的搜索條件中，比如這個(gè)：

SELECT * FROM s1 WHERE key2 IN (SELECT key2 FROM s2 WHERE key3 = 'a');

由于key2列是s2表的唯一二級(jí)索引列，所以我們可以直接把s2表上拉到外層查詢(xún)的FROM子句中，并且把子查詢(xún)中的搜索條件合并到外層查詢(xún)的搜索條件中，上拉之后的查詢(xún)就是這樣的：

SELECT s1.* FROM s1 INNER JOIN s2 ON s1.key2 = s2.key2 WHERE s2.key3 = 'a';

Q1: 為啥當(dāng)子查詢(xún)的查詢(xún)列表處只有主鍵或者唯一索引列時(shí)，就可以直接將子查詢(xún)轉(zhuǎn)換為連接查詢(xún)呢？

因?yàn)橹麈I或者唯一索引列中的數(shù)據(jù)本身就是不重復(fù)！所以對(duì)于同一條s1表中的記錄，你不可能找到兩條以上的符合s1.key2 =s2.key2的記錄~

2. DuplicateWeedout execution strategy （重復(fù)值消除）

對(duì)于這個(gè)查詢(xún)來(lái)說(shuō)：

SELECT * FROM s1 WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a'); 

轉(zhuǎn)換為半連接查詢(xún)后，s1表中的某條記錄可能在s2表中有多條匹配的記錄，所以該條記錄可能多次被添加到最后的結(jié)果集中，為了消除重復(fù)，我們可以建立一個(gè)臨時(shí)表，比方說(shuō)這個(gè)臨時(shí)表長(zhǎng)這樣：

CREATE TABLE tmp (
  id PRIMARY KEY
 );

這樣在執(zhí)行連接查詢(xún)的過(guò)程中，每當(dāng)某條s1表中的記錄要加入結(jié)果集時(shí)，就首先把這條記錄的id值加入到這個(gè)臨時(shí)表里，如果添加成功，說(shuō)明之前這條s1表中的記錄并沒(méi)有加入最終的結(jié)果集，現(xiàn)在把該記錄添加到最終的結(jié)果集；如果添加失敗，說(shuō)明這條之前這條s1表中的記錄已經(jīng)加入過(guò)最終的結(jié)果集，這里直接把它丟棄就好了，這種使用臨時(shí)表消除semi-join結(jié)果集中的重復(fù)值的方式稱(chēng)之為Duplicate Weedout。

3. LooseScan execution strategy （松散索引掃描）

大家看這個(gè)查詢(xún)：

SELECT * FROM s1 WHERE key3 IN (SELECT key1 FROM s2 WHERE key1 > 'a' AND key1 < 'b'); 

在子查詢(xún)中，對(duì)于s2表的訪問(wèn)可以使用到key1列的索引，而恰好子查詢(xún)的查詢(xún)列表處就是key1列，這樣在將該查詢(xún)轉(zhuǎn)換為半連接查詢(xún)后，如果將s2作為驅(qū)動(dòng)表執(zhí)行查詢(xún)的話，那么執(zhí)行過(guò)程就是這樣：

如圖所示，在s2表的idx_key1索引中，值為'aa'的二級(jí)索引記錄一共有2條，那么只需要取第一條的值到s1表中查找s1.key3 = 'aa'的記錄，如果能在s1表中找到對(duì)應(yīng)的記錄，那么就把對(duì)應(yīng)的記錄加入到結(jié)果集。依此類(lèi)推，其他值相同的二級(jí)索引記錄，也只需要取第一條記錄的值到s1表中找匹配的記錄，這種雖然是掃描索引，但只取值相同的記錄的第一條去做匹配操作的方式稱(chēng)之為松散索引掃描。

4. Semi-join Materialization execution strategy

我們之前介紹的先把外層查詢(xún)的IN子句中的不相關(guān)子查詢(xún)進(jìn)行物化，然后再進(jìn)行外層查詢(xún)的表和物化表的連接本質(zhì)上也算是一種semi-join，只不過(guò)由于物化表中沒(méi)有重復(fù)的記錄，所以可以直接將子查詢(xún)轉(zhuǎn)為連接查詢(xún)。

5. FirstMatch execution strategy （首次匹配）

FirstMatch是一種最原始的半連接執(zhí)行方式，跟我們年少時(shí)認(rèn)為的相關(guān)子查詢(xún)的執(zhí)行方式是一樣一樣的，就是說(shuō)先取一條外層查詢(xún)的中的記錄，然后到子查詢(xún)的表中尋找符合匹配條件的記錄，如果能找到一條，則將該外層查詢(xún)的記錄放入最終的結(jié)果集并且停止查找更多匹配的記錄，如果找不到則把該外層查詢(xún)的記錄丟棄掉；然后再開(kāi)始取下一條外層查詢(xún)中的記錄，重復(fù)上邊這個(gè)過(guò)程。

對(duì)于某些使用IN語(yǔ)句的相關(guān)子查詢(xún)，比方這個(gè)查詢(xún)：

SELECT * FROM s1 WHERE key1 IN (SELECT common_field FROM s2 WHERE s1.key3 = s2.key3); 

它也可以很方便的轉(zhuǎn)為半連接，轉(zhuǎn)換后的語(yǔ)句類(lèi)似這樣：

SELECT s1.* FROM s1 SEMI JOIN s2 ON s1.key1 = s2.common_field AND s1.key3 = s2.key3; 

然后就可以使用我們上邊介紹過(guò)的DuplicateWeedout、LooseScan、FirstMatch等半連接執(zhí)行策略來(lái)執(zhí)行查詢(xún)，當(dāng)然，如果子查詢(xún)的查詢(xún)列表處只有主鍵或者唯一二級(jí)索引列，還可以直接使用table pullout的策略來(lái)執(zhí)行查詢(xún)，但是需要大家注意的是，由于相關(guān)子查詢(xún)并不是一個(gè)獨(dú)立的查詢(xún)，所以不能轉(zhuǎn)換為物化表來(lái)執(zhí)行查詢(xún)。

3.3.4 semi-join的適用條件

當(dāng)然，并不是所有包含IN子查詢(xún)的查詢(xún)語(yǔ)句都可以轉(zhuǎn)換為semi-join，只有形如這樣的查詢(xún)才可以被轉(zhuǎn)換為semi-join：

SELECT ... FROM outer_tables WHERE expr IN (SELECT ... FROM inner_tables ...) AND ... 

或者這樣的形式也可以：

SELECT ... FROM outer_tables WHERE (oe1, oe2, ...) IN (SELECT ie1, ie2, ... FROM inner_tables ...) AND ... 

用文字總結(jié)一下，只有符合下邊這些條件的子查詢(xún)才可以被轉(zhuǎn)換為semi-join：

• 該子查詢(xún)必須是和IN語(yǔ)句組成的布爾表達(dá)式，并且在外層查詢(xún)的WHERE或者ON子句中出現(xiàn)
• 外層查詢(xún)也可以有其他的搜索條件，只不過(guò)和IN子查詢(xún)的搜索條件必須使用AND連接起來(lái)
• 該子查詢(xún)必須是一個(gè)單一的查詢(xún)，不能是由若干查詢(xún)由UNION連接起來(lái)的形式
• 該子查詢(xún)不能包含GROUP BY或者HAVING語(yǔ)句或者聚集函數(shù)
• ... 還有一些條件比較少見(jiàn)，就不嘮叨啦～

3.3.5 不適用于semi-join的情況

對(duì)于一些不能將子查詢(xún)轉(zhuǎn)位semi-join的情況，典型的比如下邊這幾種：

1. 外層查詢(xún)的WHERE條件中有其他搜索條件與IN子查詢(xún)組成的布爾表達(dá)式使用OR連接起來(lái)

SELECT * FROM s1 WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a') OR key2 > 100; 

2. 使用NOT IN而不是IN的情況

SELECT * FROM s1 WHERE key1 NOT IN (SELECT common_field FROM s2 WHERE key3 = 'a') 

3. 在SELECT子句中的IN子查詢(xún)的情況

SELECT key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a') FROM s1 ; 

4. 子查詢(xún)中包含GROUP BY、HAVING或者聚集函數(shù)的情況

SELECT * FROM s1 WHERE key2 IN (SELECT COUNT(*) FROM s2 GROUP BY key1); 

5. 子查詢(xún)中包含UNION的情況

SELECT * FROM s1 WHERE key1 IN (
    SELECT common_field FROM s2 WHERE key3 = 'a'
    UNION
    SELECT common_field FROM s2 WHERE key3 = 'b'
);

MySQL仍然留了兩口絕活來(lái)優(yōu)化不能轉(zhuǎn)為semi-join查詢(xún)的子查詢(xún)，那就是：

1. 對(duì)于不相關(guān)子查詢(xún)來(lái)說(shuō)，可以嘗試把它們物化之后再參與查詢(xún)

比如我們上邊提到的這個(gè)查詢(xún)：

SELECT * FROM s1 WHERE key1 NOT IN (SELECT common_field FROM s2 WHERE key3 = 'a') 

先將子查詢(xún)物化，然后再判斷key1是否在物化表的結(jié)果集中可以加快查詢(xún)執(zhí)行的速度。

小提示： 請(qǐng)注意這里將子查詢(xún)物化之后不能轉(zhuǎn)為和外層查詢(xún)的表的連接，只能是先掃描s1表，然后對(duì)s1表的某條記錄來(lái)說(shuō)，判斷該記錄的key1值在不在物化表中。

2. 不管子查詢(xún)是相關(guān)的還是不相關(guān)的，都可以把IN子查詢(xún)嘗試專(zhuān)為EXISTS子查詢(xún)

其實(shí)對(duì)于任意一個(gè)IN子查詢(xún)來(lái)說(shuō)，都可以被轉(zhuǎn)為EXISTS子查詢(xún)，通用的例子如下：

outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where) 

可以被轉(zhuǎn)換為：

EXISTS (SELECT inner_expr FROM ... WHERE subquery_where AND outer_expr=inner_expr) 

當(dāng)然這個(gè)過(guò)程中有一些特殊情況，比如在outer_expr或者inner_expr值為NULL的情況下就比較特殊。因?yàn)橛?code>NULL值作為操作數(shù)的表達(dá)式結(jié)果往往是NULL，比方說(shuō)：

mysql> SELECT NULL IN (1, 2, 3);
+-------------------+
| NULL IN (1, 2, 3) |
+-------------------+
|              NULL |
+-------------------+
1 row inset (0.00 sec)
mysql> SELECT 1 IN (1, 2, 3);
+----------------+
| 1 IN (1, 2, 3) |
+----------------+
|              1 |
+----------------+
1 row inset (0.00 sec)
mysql> SELECT NULL IN (NULL);
+----------------+
| NULL IN (NULL) |
+----------------+
|           NULL |
+----------------+
1 row inset (0.00 sec)

而EXISTS子查詢(xún)的結(jié)果肯定是TRUE或者FASLE：

mysql> SELECT EXISTS (SELECT 1 FROM s1 WHERE NULL = 1);
+------------------------------------------+
| EXISTS (SELECT 1 FROM s1 WHERE NULL = 1) |
+------------------------------------------+
|                                        0 |
+------------------------------------------+
1 row inset (0.01 sec)
mysql> SELECT EXISTS (SELECT 1 FROM s1 WHERE 1 = NULL);
+------------------------------------------+
| EXISTS (SELECT 1 FROM s1 WHERE 1 = NULL) |
+------------------------------------------+
|                                        0 |
+------------------------------------------+
1 row inset (0.00 sec)
mysql> SELECT EXISTS (SELECT 1 FROM s1 WHERE NULL = NULL);
+---------------------------------------------+
| EXISTS (SELECT 1 FROM s1 WHERE NULL = NULL) |
+---------------------------------------------+
|                                           0 |
+---------------------------------------------+
1 row inset (0.00 sec)

但是幸運(yùn)的是，我們大部分使用IN子查詢(xún)的場(chǎng)景是把它放在WHERE或者ON子句中，而WHERE或者ON子句是不區(qū)分NULL和FALSE的，比方說(shuō)：

mysql> SELECT 1 FROM s1 WHERE NULL;
Empty set (0.00 sec)
mysql> SELECT 1 FROM s1 WHERE FALSE;
Empty set (0.00 sec)

所以只要我們的IN子查詢(xún)是放在WHERE或者ON子句中的，那么IN -> EXISTS的轉(zhuǎn)換就是沒(méi)問(wèn)題的。說(shuō)了這么多，為啥要轉(zhuǎn)換呢？這是因?yàn)椴晦D(zhuǎn)換的話可能用不到索引，比方說(shuō)下邊這個(gè)查詢(xún)：

SELECT * FROM s1 WHERE 
key1 IN (SELECT key3 FROM s2 where s1.common_field = s2.common_field) 
	 OR key2 > 1000;

這個(gè)查詢(xún)中的子查詢(xún)是一個(gè)相關(guān)子查詢(xún)，而且子查詢(xún)執(zhí)行的時(shí)候不能使用到索引，但是將它轉(zhuǎn)為EXISTS子查詢(xún)后卻可以使用到索引：

SELECT * FROM s1 WHERE EXISTS (SELECT 1 FROM s2 wheres1.common_field = s2.common_field AND s2.key3 = s1.key1) OR key2 > 1000; 

轉(zhuǎn)為EXISTS子查詢(xún)時(shí)便可以使用到s2表的idx_key3索引了。

需要注意的是，如果IN子查詢(xún)不滿足轉(zhuǎn)換為semi-join的條件，又不能轉(zhuǎn)換為物化表或者轉(zhuǎn)換為物化表的成本太大，那么它就會(huì)被轉(zhuǎn)換為EXISTS查詢(xún)。

小提示： 在MySQL5.5以及之前的版本沒(méi)有引進(jìn)semi-join和物化的方式優(yōu)化子查詢(xún)時(shí)，優(yōu)化器都會(huì)把IN子查詢(xún)轉(zhuǎn)換為EXISTS子查詢(xún)，好多同學(xué)就驚呼我明明寫(xiě)的是一個(gè)不相關(guān)子查詢(xún)，為啥要按照?qǐng)?zhí)行相關(guān)子查詢(xún)的方式來(lái)執(zhí)行呢？所以當(dāng)時(shí)好多聲音都是建議大家把子查詢(xún)轉(zhuǎn)為連接，不過(guò)隨著MySQL的發(fā)展，最近的版本中引入了非常多的子查詢(xún)優(yōu)化策略，大家可以稍微放心的使用子查詢(xún)了，內(nèi)部的轉(zhuǎn)換工作優(yōu)化器會(huì)為大家自動(dòng)實(shí)現(xiàn)。

小結(jié)一下

如果IN子查詢(xún)符合轉(zhuǎn)換為semi-join的條件，查詢(xún)優(yōu)化器會(huì)優(yōu)先把該子查詢(xún)轉(zhuǎn)換為semi-join，然后再考慮下邊5種執(zhí)行半連接的策略中哪個(gè)成本最低：

• Table pullout
• DuplicateWeedout
• LooseScan Materialization
• FirstMatch 選擇成本最低的那種執(zhí)行策略來(lái)執(zhí)行子查詢(xún)。

如果IN子查詢(xún)不符合轉(zhuǎn)換為semi-join的條件，那么查詢(xún)優(yōu)化器會(huì)從下邊兩種策略中找出一種成本更低的方式執(zhí)行子查詢(xún)：

• 先將子查詢(xún)物化之后再執(zhí)行查詢(xún)
• 執(zhí)行IN to EXISTS轉(zhuǎn)換。

3.4 ANY/ALL子查詢(xún)優(yōu)化

如果ANY/ALL子查詢(xún)是不相關(guān)子查詢(xún)的話，它們?cè)诤芏鄨?chǎng)合都能轉(zhuǎn)換成我們熟悉的方式去執(zhí)行，比方說(shuō)：

3.5 [NOT] EXISTS子查詢(xún)的執(zhí)行

如果[NOT] EXISTS子查詢(xún)是不相關(guān)子查詢(xún)，可以先執(zhí)行子查詢(xún)，得出該[NOT] EXISTS子查詢(xún)的結(jié)果是TRUE還是FALSE，并重寫(xiě)原先的查詢(xún)語(yǔ)句，比如對(duì)這個(gè)查詢(xún)來(lái)說(shuō)：

SELECT * FROM s1 WHERE EXISTS (SELECT 1 FROM s2 WHERE key1 = 'a') OR key2 > 100; 

因?yàn)檫@個(gè)語(yǔ)句里的子查詢(xún)是不相關(guān)子查詢(xún)，所以?xún)?yōu)化器會(huì)首先執(zhí)行該子查詢(xún)，假設(shè)該EXISTS子查詢(xún)的結(jié)果為TRUE，那么接著優(yōu)化器會(huì)重寫(xiě)查詢(xún)?yōu)椋?/p>

SELECT * FROM s1 WHERE TRUE OR key2 > 100; 

進(jìn)一步簡(jiǎn)化后就變成了：

SELECT * FROM s1 WHERE TRUE; 

對(duì)于相關(guān)的[NOT] EXISTS子查詢(xún)來(lái)說(shuō)，比如這個(gè)查詢(xún)：

SELECT * FROM s1 WHERE EXISTS (SELECT 1 FROM s2 WHERE s1.common_field = s2.common_field); 

很不幸，這個(gè)查詢(xún)只能按照我們年少時(shí)的那種執(zhí)行相關(guān)子查詢(xún)的方式來(lái)執(zhí)行。不過(guò)如果[NOT] EXISTS子查詢(xún)中如果可以使用索引的話，那查詢(xún)速度也會(huì)加快不少，比如：

SELECT * FROM s1 WHERE EXISTS (SELECT 1 FROM s2 WHERE s1.common_field = s2.key1); 

上邊這個(gè)EXISTS子查詢(xún)中可以使用idx_key1來(lái)加快查詢(xún)速度。

3.6 對(duì)于派生表的優(yōu)化

我們前邊說(shuō)過(guò)把子查詢(xún)放在外層查詢(xún)的FROM子句后，那么這個(gè)子查詢(xún)的結(jié)果相當(dāng)于一個(gè)派生表，比如下邊這個(gè)查詢(xún)：

SELECT * FROM  (
        SELECT id AS d_id,  key3 AS d_key3 FROM s2 WHERE key1 = 'a'
    ) AS derived_s1 WHERE d_key3 = 'a';

子查詢(xún)( SELECT id AS d_id, key3 AS d_key3 FROM s2 WHERE key1 = 'a')的結(jié)果就相當(dāng)于一個(gè)派生表，這個(gè)表的名稱(chēng)是derived_s1，該表有兩個(gè)列，分別是d_id和d_key3

對(duì)于含有派生表的查詢(xún)，MySQL提供了兩種執(zhí)行策略：

1. 最容易想到的就是把派生表物化

我們可以將派生表的結(jié)果集寫(xiě)到一個(gè)內(nèi)部的臨時(shí)表中，然后就把這個(gè)物化表當(dāng)作普通表一樣參與查詢(xún)。當(dāng)然，在對(duì)派生表進(jìn)行物化時(shí)，MySQL使用了一種稱(chēng)為延遲物化的策略，也就是在查詢(xún)中真正使用到派生表時(shí)才回去嘗試物化派生表，而不是還沒(méi)開(kāi)始執(zhí)行查詢(xún)呢就把派生表物化掉。比方說(shuō)對(duì)于下邊這個(gè)含有派生表的查詢(xún)來(lái)說(shuō)：

SELECT * FROM (
        SELECT * FROM s1 WHERE key1 = 'a'
    ) AS derived_s1 INNER JOIN s2
    ON derived_s1.key1 = s2.key1
    WHERE s2.key2 = 1;

如果采用物化派生表的方式來(lái)執(zhí)行這個(gè)查詢(xún)的話，那么執(zhí)行時(shí)首先會(huì)到s2表中找出滿足s2.key2 = 1的記錄，如果壓根?找不到，說(shuō)明參與連接的s2表記錄就是空的，所以整個(gè)查詢(xún)的結(jié)果集就是空的，所以也就沒(méi)有必要去物化查詢(xún)中的派生表了。

2. 將派生表和外層的表合并，也就是將查詢(xún)重寫(xiě)為沒(méi)有派生表的形式

我們來(lái)看這個(gè)賊簡(jiǎn)單的包含派生表的查詢(xún)：

SELECT * FROM (SELECT * FROM s1 WHERE key1 = 'a') AS derived_s1; 

這個(gè)查詢(xún)本質(zhì)上就是想查看s1表中滿足key1 = 'a'條件的的全部記錄，所以和下邊這個(gè)語(yǔ)句是等價(jià)的：

SELECT * FROM s1 WHERE key1 = 'a'; 

對(duì)于一些稍微復(fù)雜的包含派生表的語(yǔ)句，比如我們上邊提到的那個(gè)：

SELECT * FROM (
        SELECT * FROM s1 WHERE key1 = 'a'
    ) AS derived_s1 INNER JOIN s2
    ON derived_s1.key1 = s2.key1
    WHERE s2.key2 = 1;

我們可以將派生表與外層查詢(xún)的表合并，然后將派生表中的搜索條件放到外層查詢(xún)的搜索條件中，就像這樣：

SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.key1 = 'a' AND s2.key2 = 1; 

這樣通過(guò)將外層查詢(xún)和派生表合并的方式成功的消除了派生表，也就意味著我們沒(méi)必要再付出創(chuàng)建和訪問(wèn)臨時(shí)表的成本了?？墒遣⒉皇撬袔в信缮淼牟樵?xún)都能被成功的和外層查詢(xún)合并，當(dāng)派生表中有這些語(yǔ)句就不可以和外層查詢(xún)合并：

• 聚集函數(shù)，比如MAX()、MIN()、SUM()啥的
• DISTINCT
• GROUP BY
• HAVING
• LIMIT
• UNION 或者 UNION ALL
• 派生表對(duì)應(yīng)的子查詢(xún)的SELECT子句中含有另一個(gè)子查詢(xún)
• ... 還有些不常用的情況就不多說(shuō)了哈～

所以MySQL在執(zhí)行帶有派生表的時(shí)候，優(yōu)先嘗試把派生表和外層查詢(xún)合并掉，如果不行的話，再把派生表物化掉執(zhí)行查詢(xún)。

至此今天的學(xué)習(xí)就到此結(jié)束了，愿您成為堅(jiān)不可摧的自己~~~

以上就是一文帶你了解MySQL基于規(guī)則的優(yōu)化的詳細(xì)內(nèi)容，更多關(guān)于MySQL基于規(guī)則優(yōu)化的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

最新評(píng)論