1 標量子查詢递胧、行子查詢的執(zhí)行方式

我們經(jīng)常在下邊兩個場景中使用到標量子查詢或者行子查詢：

SELECT子句中婴削，我們前邊說過的在查詢列表中的子查詢必須是標量子查詢。

子查詢使用=狈谊、>乾翔、<、>=嗡呼、<=纸俭、<>、!=南窗、<=>等操作符和某個操作數(shù)組成一個布爾表達式揍很，這樣的子查詢必須是標量子查詢或者行子查詢。

對于上述兩種場景中的不相關標量子查詢或者行子查詢來說矾瘾，它們的執(zhí)行方式是簡單的女轿，比方說下邊這個查詢語句：

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

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

先單獨執(zhí)行(SELECT common_field FROM s2 WHERE key3 = 'a' LIMIT 1)這個子查詢。

然后在將上一步子查詢得到的結果當作外層查詢的參數(shù)再執(zhí)行外層查詢SELECT * FROM s1 WHERE key1 = ...壕翩。

也就是說蛉迹，對于包含不相關的標量子查詢或者行子查詢的查詢語句來說，MySQL會分別獨立的執(zhí)行外層查詢和子查詢放妈，就當作兩個單表查詢就好了北救。

對于相關的標量子查詢或者行子查詢來說，比如下邊這個查詢：

SELECT * FROM s1 WHERE

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

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

先從外層查詢中獲取一條記錄珍策，本例中也就是先從s1表中獲取一條記錄。

然后從上一步驟中獲取的那條記錄中找出子查詢中涉及到的值宅倒，本例中就是從s1表中獲取的那條記錄中找出s1.key3列的值攘宙，然后執(zhí)行子查詢。

最后根據(jù)子查詢的查詢結果來檢測外層查詢WHERE子句的條件是否成立拐迁，如果成立蹭劈，就把外層查詢的那條記錄加入到結果集，否則就丟棄线召。

再次執(zhí)行第一步铺韧，獲取第二條外層查詢中的記錄，依次類推～

也就是說對于一開始嘮叨的兩種使用標量子查詢以及行子查詢的場景中缓淹，MySQL優(yōu)化器的執(zhí)行方式并沒有什么新鮮的哈打。

2 IN子查詢優(yōu)化

物化表的提出

對于不相關的IN子查詢塔逃，比如這樣：

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

我們最開始的感覺就是這種不相關的IN子查詢和不相關的標量子查詢或者行子查詢是一樣一樣的，都是把外層查詢和子查詢當作兩個獨立的單表查詢來對待料仗，可是很遺憾的是設計MySQL的大叔為了優(yōu)化IN子查詢傾注了太多心血（畢竟IN子查詢是我們日常生活中最常用的子查詢類型）湾盗，所以整個執(zhí)行過程并不像我們想象的那么簡單(>_<)。

其實說句老實話罢维，對于不相關的IN子查詢來說淹仑，如果子查詢的結果集中的記錄條數(shù)很少，那么把子查詢和外層查詢分別看成兩個單獨的單表查詢效率還是蠻高的肺孵，但是如果單獨執(zhí)行子查詢后的結果集太多的話，就會導致這些問題：

結果集太多颜阐，可能內存中都放不下～

對于外層查詢來說平窘，如果子查詢的結果集太多，那就意味著IN子句中的參數(shù)特別多瑰艘，這就導致：

無法有效的使用索引，只能對外層查詢進行全表掃描肤舞。

在對外層查詢執(zhí)行全表掃描時紫新，由于IN子句中的參數(shù)太多，這會導致檢測一條記錄是否符合和IN子句中的參數(shù)匹配花費的時間太長李剖。

比如說IN子句中的參數(shù)只有兩個：

SELECT * FROM tbl_name WHERE column IN (a, b);

這樣相當于需要對tbl_name表中的每條記錄判斷一下它的column列是否符合column = a OR column = b芒率。在IN子句中的參數(shù)比較少時這并不是什么問題，如果IN子句中的參數(shù)比較多時篙顺，比如這樣：

SELECT * FROM tbl_name WHERE column IN (a, b, c ..., ...);

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

于是乎設計MySQL的大叔想了一個招：不直接將不相關子查詢的結果集當作外層查詢的參數(shù)德玫，而是將該結果集寫入一個臨時表里匪蟀。寫入臨時表的過程是這樣的：

該臨時表的列就是子查詢結果集中的列。

寫入臨時表的記錄會被去重宰僧。

我們說IN語句是判斷某個操作數(shù)在不在某個集合中材彪，集合中的值重不重復對整個IN語句的結果并沒有啥子關系，所以我們在將結果集寫入臨時表時對記錄進行去重可以讓臨時表變得更小琴儿，更省地方～

小貼士： 臨時表如何對記錄進行去重段化？這不是小意思嘛，臨時表也是個表凤类，只要為表中記錄的所有列建立主鍵或者唯一索引就好了嘛～

一般情況下子查詢結果集不會大的離譜穗泵，所以會為它建立基于內存的使用Memory存儲引擎的臨時表，而且會為該表建立哈希索引谜疤。

小貼士： IN語句的本質就是判斷某個操作數(shù)在不在某個集合里佃延，如果集合中的數(shù)據(jù)建立了哈希索引现诀，那么這個匹配的過程就是超級快的。 有同學不知道哈希索引是什么履肃？我這里就不展開了仔沿，自己上網(wǎng)找找吧，不會了再來問我～

如果子查詢的結果集非常大尺棋，超過了系統(tǒng)變量tmp_table_size或者max_heap_table_size封锉，臨時表會轉而使用基于磁盤的存儲引擎來保存結果集中的記錄，索引類型也對應轉變?yōu)锽+樹索引膘螟。

設計MySQL的大叔把這個將子查詢結果集中的記錄保存到臨時表的過程稱之為物化（英文名：Materialize）成福。為了方便起見，我們就把那個存儲子查詢結果集的臨時表稱之為物化表荆残。正因為物化表中的記錄都建立了索引（基于內存的物化表有哈希索引奴艾，基于磁盤的有B+樹索引），通過索引執(zhí)行IN語句判斷某個操作數(shù)在不在子查詢結果集中變得非衬谒梗快蕴潦，從而提升了子查詢語句的性能。

物化表轉連接

事情到這就完了俘闯？我們還得重新審視一下最開始的那個查詢語句：

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

當我們把子查詢進行物化之后潭苞，假設子查詢物化表的名稱為materialized_table，該物化表存儲的子查詢結果集的列為m_val真朗，那么這個查詢其實可以從下邊兩種角度來看待：

從表s1的角度來看待此疹，整個查詢的意思其實是：對于s1表中的每條記錄來說，如果該記錄的key1列的值在子查詢對應的物化表中蜜猾，則該記錄會被加入最終的結果集秀菱。畫個圖表示一下就是這樣：

從子查詢物化表的角度來看待，整個查詢的意思其實是：對于子查詢物化表的每個值來說蹭睡，如果能在s1表中找到對應的key1列的值與該值相等的記錄衍菱，那么就把這些記錄加入到最終的結果集。畫個圖表示一下就是這樣：

也就是說其實上邊的查詢就相當于表s1和子查詢物化表materialized_table進行內連接：

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

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

如果使用s1表作為驅動表的話清钥，總查詢成本由下邊幾個部分組成：

物化子查詢時需要的成本

掃描s1表時的成本

s1表中的記錄數(shù)量 × 通過m_val = xxx對materialized_table表進行單表訪問的成本（我們前邊說過物化表中的記錄是不重復的琼锋，并且為物化表中的列建立了索引，所以這個步驟顯然是非乘钫眩快的）缕坎。

如果使用materialized_table表作為驅動表的話，總查詢成本由下邊幾個部分組成：

物化子查詢時需要的成本

掃描物化表時的成本

物化表中的記錄數(shù)量 × 通過key1 = xxx對s1表進行單表訪問的成本（非常慶幸key1列上建立了索引篡悟，所以這個步驟是非趁仗荆快的）匾寝。

MySQL查詢優(yōu)化器會通過運算來選擇上述成本更低的方案來執(zhí)行查詢。

如果IN子查詢符合轉換為semi-join的條件荷腊，

查詢優(yōu)化器會優(yōu)先把該子查詢轉換為semi-join艳悔，然后再考慮下邊5種執(zhí)行半連接的策略中哪個成本最低：

Table pullout

DuplicateWeedout

LooseScan

Materialization

FirstMatch

選擇成本最低的那種執(zhí)行策略來執(zhí)行子查詢。

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

先將子查詢物化之后再執(zhí)行查詢

執(zhí)行IN to EXISTS轉換猜年。

[NOT] EXISTS子查詢的執(zhí)行

如果[NOT] EXISTS子查詢是不相關子查詢，可以先執(zhí)行子查詢疾忍，得出該[NOT] EXISTS子查詢的結果是TRUE還是FALSE乔外，并重寫原先的查詢語句，比如對這個查詢來說：

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

因為這個語句里的子查詢是不相關子查詢一罩，所以優(yōu)化器會首先執(zhí)行該子查詢袁稽，假設該EXISTS子查詢的結果為TRUE，那么接著優(yōu)化器會重寫查詢?yōu)椋?/p>

SELECT * FROM s1

? ? WHERE TRUE OR key2 > 100;

進一步簡化后就變成了：

SELECT * FROM s1

? ? WHERE TRUE;

對于相關的[NOT] EXISTS子查詢來說擒抛，比如這個查詢：

SELECT * FROM s1

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

很不幸，這個查詢只能按照我們年少時的那種執(zhí)行相關子查詢的方式來執(zhí)行补疑。不過如果[NOT] EXISTS子查詢中如果可以使用索引的話歧沪，那查詢速度也會加快不少，比如：

SELECT * FROM s1

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

上邊這個EXISTS子查詢中可以使用idx_key1來加快查詢速度莲组。

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

我們前邊說過把子查詢放在外層查詢的FROM子句后诊胞，那么這個子查詢的結果相當于一個派生表，比如下邊這個查詢：

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

子查詢( SELECT id AS d_id, key3 AS d_key3 FROM s2 WHERE key1 = 'a')的結果就相當于一個派生表锹杈，這個表的名稱是derived_s1撵孤，該表有兩個列，分別是d_id和d_key3竭望。

對于含有派生表的查詢邪码，MySQL提供了兩種執(zhí)行策略：

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

我們可以將派生表的結果集寫到一個內部的臨時表中咬清，然后就把這個物化表當作普通表一樣參與查詢闭专。當然，在對派生表進行物化時旧烧，設計MySQL的大叔使用了一種稱為延遲物化的策略影钉，也就是在查詢中真正使用到派生表時才回去嘗試物化派生表，而不是還沒開始執(zhí)行查詢呢就把派生表物化掉掘剪。比方說對于下邊這個含有派生表的查詢來說：

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

如果采用物化派生表的方式來執(zhí)行這個查詢的話平委，那么執(zhí)行時首先會到s2表中找出滿足s2.key2 = 1的記錄，如果壓根兒找不到夺谁，說明參與連接的s2表記錄就是空的廉赔，所以整個查詢的結果集就是空的肉微，所以也就沒有必要去物化查詢中的派生表了。

將派生表和外層的表合并昂勉，也就是將查詢重寫為沒有派生表的形式

我們來看這個賊簡單的包含派生表的查詢：

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

這個查詢本質上就是想查看s1表中滿足key1 = 'a'條件的的全部記錄浪册，所以和下邊這個語句是等價的：

SELECT * FROM s1 WHERE key1 ='a';

對于一些稍微復雜的包含派生表的語句，比如我們上邊提到的那個：

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

我們可以將派生表與外層查詢的表合并岗照，然后將派生表中的搜索條件放到外層查詢的搜索條件中村象，就像這樣：

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

這樣通過將外層查詢和派生表合并的方式成功的消除了派生表，也就意味著我們沒必要再付出創(chuàng)建和訪問臨時表的成本了攒至『裾撸可是并不是所有帶有派生表的查詢都能被成功的和外層查詢合并，當派生表中有這些語句就不可以和外層查詢合并：

聚集函數(shù)迫吐，比如MAX()库菲、MIN()、SUM()啥的

DISTINCT

GROUP BY

HAVING

LIMIT

UNION 或者 UNION ALL

派生表對應的子查詢的SELECT子句中含有另一個子查詢

... 還有些不常用的情況就不多說了哈～

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