經(jīng)過(guò)??上一篇??? where field in (...) 的開(kāi)場(chǎng)準(zhǔn)備，本文正式開(kāi)啟??子查詢系列??，這個(gè)系列會(huì)介紹子查詢的各種執(zhí)行策略，計(jì)劃包括以下主題：

• 不相關(guān)子查詢 (Subquery)
• 相關(guān)子查詢 (Dependent Subquery)
• 嵌套循環(huán)連接 (Blocked Nested Loop Join)
• 哈希連接 (Hash Join)
• 表上拉 (Table Pullout)
• 首次匹配 (First Match)
• 松散掃描 (Loose Scan)
• 重復(fù)值消除 (Duplicate Weedout)
• 子查詢物化 (Materialize)

上面列表中，從表上拉（Table Pullout）開(kāi)始的 5 種執(zhí)行策略都用 Join 實(shí)現(xiàn)，所以把嵌套循環(huán)連接、哈希連接也包含在這個(gè)系列里面了。

子查詢系列文章的主題，在寫作過(guò)程中可能會(huì)根據(jù)情況調(diào)整，也可能會(huì)插入其它不屬于這個(gè)系列的文章。

本文我們先來(lái)看看不相關(guān)子查詢是怎么執(zhí)行的？

本文內(nèi)容基于 MySQL 8.0.29 源碼。

1、概述

從現(xiàn)存的子查詢執(zhí)行策略來(lái)看，半連接 (Semijoin) 加入之前，不相關(guān)子查詢有兩種執(zhí)行策略：

策略 1，子查詢物化，也就是把子查詢的執(zhí)行結(jié)果存入臨時(shí)表，這個(gè)臨時(shí)表叫作物化表。

explain select_type = ??SUBQUERY?? 就表示使用了物化策略執(zhí)行子查詢，如下：

+----+-------------+---------+------------+-------+------------------------+----------------+---------+--------+------+----------+--------------------------+
| id | select_type | table   | partitions | type  | possible_keys          | key            | key_len | ref    | rows | filtered | Extra                    |
+----+-------------+---------+------------+-------+------------------------+----------------+---------+--------+------+----------+--------------------------+
| 1  | PRIMARY     | city    | <null>     | ALL   | <null>                 | <null>         | <null>  | <null> | 600  |  33.33   | Using where              |
| 2  | SUBQUERY    | address | <null>     | range | PRIMARY,idx_fk_city_id | idx_fk_city_id | 2       | <null> | 9    | 100.0    | Using where; Using index |
+----+-------------+---------+------------+-------+------------------------+----------------+---------+--------+------+----------+--------------------------+

策略 2，轉(zhuǎn)換為相關(guān)子查詢，explain select_type = DEPENDENT SUBQUERY，如下：

+----+--------------------+---------+------------+-----------------+------------------------+---------+---------+--------+------+----------+-------------+
| id | select_type        | table   | partitions | type            | possible_keys          | key     | key_len | ref    | rows | filtered | Extra       |
+----+--------------------+---------+------------+-----------------+------------------------+---------+---------+--------+------+----------+-------------+
| 1  | PRIMARY            | city    | <null>     | ALL             | <null>                 | <null>  | <null>  | <null> | 600  | 33.33    | Using where |
| 2  | DEPENDENT SUBQUERY | address | <null>     | unique_subquery | PRIMARY,idx_fk_city_id | PRIMARY | 2       | func   | 1    |  5.0     | Using where |
+----+--------------------+---------+------------+-----------------+------------------------+---------+---------+--------+------+----------+-------------+

本文我們要介紹的就是使用物化策略執(zhí)行不相關(guān)子查詢的過(guò)程，不相關(guān)子查詢轉(zhuǎn)換為相關(guān)子查詢的執(zhí)行過(guò)程，留到下一篇文章。

2、執(zhí)行流程

我們介紹的執(zhí)行流程，不是整條 SQL 的完整執(zhí)行流程，只會(huì)涉及到子查詢相關(guān)的那些步驟。

查詢優(yōu)化階段，MySQL 確定了要使用物化策略執(zhí)行子查詢之后，就會(huì)創(chuàng)建臨時(shí)表。

關(guān)于創(chuàng)建臨時(shí)表的更多內(nèi)容，后面有一小節(jié)單獨(dú)介紹。

執(zhí)行階段?，server 層從存儲(chǔ)引擎讀取到主查詢?的第一條記錄之后，就要判斷記錄是否匹配 where 條件。

判斷包含子查詢的那個(gè) where 條件字段時(shí)，發(fā)現(xiàn)子查詢需要物化，就會(huì)執(zhí)行子查詢。

為了方便描述，我們給包含子查詢的那個(gè) where 條件字段取個(gè)名字：sub_field，后面在需要時(shí)也會(huì)用到這個(gè)名字。

執(zhí)行子查詢的過(guò)程，是從存儲(chǔ)引擎一條一條讀取子查詢表中的記錄。每讀取到一條記錄，都寫入臨時(shí)表中。

子查詢的記錄都寫入臨時(shí)表之后，從主查詢記錄中拿到 sub_field? 字段值，去臨時(shí)表中查找，如果找到了記錄，sub_field 字段條件結(jié)果為 true，否則為 false。

主查詢的所有 where 條件都判斷完成之后，如果每個(gè) where 條件都成立，記錄就會(huì)返回給客戶端，否則繼續(xù)讀取下一條記錄。

server 層從存儲(chǔ)引擎讀取主查詢?的第 2 ~ N 條記錄，判斷記錄是否匹配 where 條件時(shí)，就可以直接用 sub_field? 字段值去臨時(shí)表中查詢是否有相應(yīng)的記錄，以判斷 sub_field 字段條件是否成立。

從以上內(nèi)容可以見(jiàn)，子查詢物化只會(huì)執(zhí)行一次。

3、創(chuàng)建臨時(shí)表

臨時(shí)表是在查詢優(yōu)化階段創(chuàng)建的，它也是一個(gè)正經(jīng)表。既然是正經(jīng)表，那就要確定它使用什么存儲(chǔ)引擎。

臨時(shí)表會(huì)優(yōu)先使用內(nèi)存存儲(chǔ)引擎，MySQL 8 有兩種內(nèi)存存儲(chǔ)引擎：

• 從 5.7 繼承過(guò)來(lái)的MEMORY 引擎。
• 8.0 新加入的TempTable 引擎。

有了選擇就要發(fā)愁，MySQL 會(huì)選擇哪個(gè)引擎？

這由我們決定，我們可以通過(guò)系統(tǒng)變量 internal_tmp_mem_storage_engine 告訴 MySQL 選擇哪個(gè)引擎，它的可選值為 TempTable（默認(rèn)值）、MEMORY。

然而，internal_tmp_mem_storage_engine? 指定的引擎并不一定是最終的選擇，有兩種情況會(huì)導(dǎo)致臨時(shí)表使用磁盤?存儲(chǔ)引擎 InnoDB。

這兩種情況如下：

情況 1，如果我們指定了使用 MEMORY 引擎，而子查詢結(jié)果中包含 BLOB 字段，臨時(shí)表就只能使用 InnoDB 引擎了。

為啥？因?yàn)?MEMORY 引擎不支持 BLOB 字段。

情況 2，如果系統(tǒng)變量 big_tables? 的值為 ON?，并且子查詢中沒(méi)有指定 SQL_SMALL_RESULT Hint，臨時(shí)表也只能使用 InnoDB 引擎。

big_tables 的默認(rèn)值為 OFF。

這又為啥？

因?yàn)?nbsp;big_tables = ON 是告訴 MySQL 我們要執(zhí)行的所有 SQL 都包含很多記錄，臨時(shí)表需要使用 InnoDB 引擎。

然而，時(shí)移事遷，如果某天我們發(fā)現(xiàn)有一條執(zhí)行頻繁的 SQL，雖然要使用臨時(shí)表，但是記錄數(shù)量比較少，使用內(nèi)存存儲(chǔ)引擎就足夠用了。

此時(shí)，我們就可以通過(guò) Hint 告訴 MySQL 這條 SQL 的結(jié)果記錄數(shù)量很少，MySQL 就能心領(lǐng)神會(huì)的直接使用 internal_tmp_mem_storage_engine 中指定的內(nèi)存引擎了。

SQL可以這樣指定 Hint：

SELECT * FROM city WHERE country_id IN (
  SELECT SQL_SMALL_RESULT address_id FROM address WHERE city_id < 10
) AND city < 'China'

捋清楚了選擇存儲(chǔ)引擎的邏輯，接下來(lái)就是字段了，臨時(shí)表會(huì)包含哪些字段?

這里沒(méi)有復(fù)雜邏輯需要說(shuō)明，臨時(shí)表只會(huì)包含子查詢 SELECT 子句中的字段，例如：上面的示例  SQL 中，臨時(shí)表包含的字段為 address_id。

使用臨時(shí)表存放子查詢的結(jié)果，是為了提升整個(gè) SQL 的執(zhí)行效率。如果臨時(shí)表中的記錄數(shù)量很多，根據(jù)主查詢字段值去臨時(shí)表中查找記錄的成本就會(huì)比較高。

所以，MySQL 還會(huì)為臨時(shí)表中的字段創(chuàng)建索引，索引的作用有兩個(gè)：

• 提升查詢臨時(shí)表的效率。
• 保證臨時(shí)表中記錄的唯一性，也就是說(shuō)創(chuàng)建的索引是唯一索引。

說(shuō)完了字段，我們?cè)賮?lái)看看索引結(jié)構(gòu)，這取決于臨時(shí)表最終選擇了哪個(gè)存儲(chǔ)引擎：

• MEMORY、TempTable 引擎，都使用 HASH 索引。
• InnoDB 引擎，使用 BTREE 索引。

4、自動(dòng)優(yōu)化

為了讓 SQL 執(zhí)行的更快，MySQL 在很多細(xì)節(jié)處做了優(yōu)化，對(duì)包含子查詢的 where 條件判斷所做的優(yōu)化就是其中之一。

介紹這個(gè)優(yōu)化之前，我們先準(zhǔn)備一條 SQL：

SELECT * FROM city WHERE country_id IN (
  SELECT address_id FROM address WHERE city_id < 10
) AND city < 'China'

主查詢 city 表中有以下記錄：

示例  SQL where 條件中，country_id 條件包含子查詢，如果不對(duì) where 條件判斷做優(yōu)化，從 city 表中每讀取一條記錄之后，先拿到 country_id 字段值，再去臨時(shí)表中查找記錄，以判斷條件是否成立。

從上面 city 表的記錄可以看到， city_id = 73 ~ 78 的記錄，country_id 字段值都是 44。

從 city 表中讀取到 city_id = 73 的記錄之后，拿到 country_id 的值 44，去臨時(shí)表中查找記錄。

不管是否找到記錄，都會(huì)有一個(gè)結(jié)果，為了描述方便，我們假設(shè)結(jié)果為 true。

接下來(lái)從 city 表中讀取 city_id = 74 ~ 78 的記錄，因?yàn)樗鼈兊?country_id 字段值都是 44，實(shí)際上沒(méi)有必要再去臨時(shí)表里找查找記錄了，直接復(fù)用 city_id = 73 的判斷結(jié)果就可以了，這樣能節(jié)省幾次去臨時(shí)表查找記錄的時(shí)間。

由上所述，總結(jié)一下 MySQL 的優(yōu)化邏輯：

對(duì)于包含子查詢的 where 條件字段，如果連續(xù)幾條記錄的字段值都相同，這組記錄中，只有第一條記錄會(huì)根據(jù) where 條件字段值去臨時(shí)表中查找是否有對(duì)應(yīng)記錄，這一組的剩余記錄直接復(fù)用第一條記錄的判斷結(jié)果。

5、手動(dòng)優(yōu)化

上一小節(jié)介紹的是 MySQL 已經(jīng)做過(guò)的優(yōu)化，但還有一些可以做而沒(méi)有做的優(yōu)化，我們寫 SQL 的時(shí)候，可以自己優(yōu)化，也就是手動(dòng)優(yōu)化。

我們還是使用前面的示例 SQL 來(lái)介紹手動(dòng)優(yōu)化：

主查詢有兩個(gè) where 條件，那么判斷 where 條件是否成立有兩種執(zhí)行順序：

• 先判斷 country_id 條件，如果結(jié)果為 true，再判斷 city 條件。
• 先判斷 city 條件，如果結(jié)果為 true，再判斷 country_id 條件。

MySQL 會(huì)按照 where 條件出現(xiàn)的順序判斷，也就是說(shuō)，我們把哪個(gè) where 條件寫在前面，MySQL 就先判斷哪個(gè)。對(duì)于示例 SQL 來(lái)說(shuō)，就是上面所列的第一種執(zhí)行順序。

為了更好的比較兩種執(zhí)行順序的優(yōu)劣，我們用量化數(shù)據(jù)來(lái)說(shuō)明。

根據(jù) country_id 字段值去子查詢臨時(shí)表中查找記錄的成本，會(huì)高于判斷 city 字段值是否小于 China 的成本，所以，假設(shè)執(zhí)行一次 country_id 條件判斷的成本為 5，執(zhí)行一次 city 條件判斷的成本為 1。

對(duì)于主查詢的某一條記錄，假設(shè) country_id 條件成立，city 條件不成立，兩種執(zhí)行順序成本如下：

• 先判斷 country_id 條件，成本為 5，再判斷 city 條件，成本為 1，總成本 5 + 1 = 6。
• 先判斷 city 條件，成本為 1，因?yàn)闂l件不成立，不需要再判斷 country_id 條件，總成本為 1。

上面所列場(chǎng)景，第一種執(zhí)行順序的成本高于第二種執(zhí)行順序的成本，而 MySQL 使用的是第一種執(zhí)行順序。

MySQL 沒(méi)有為這種場(chǎng)景做優(yōu)化，我們可以手動(dòng)優(yōu)化，寫 SQL 的時(shí)候，把這種包含子查詢的 where 條件放在最后，盡可能讓 MySQL 少做一點(diǎn)無(wú)用工，從而讓 SQL 可以執(zhí)行的更快一點(diǎn)。

6、總結(jié)

對(duì)于 where 條件包含子查詢的 SQL，我們可以做一點(diǎn)優(yōu)化，就是把這類 where 條件放在最后，讓 MySQL 能夠少做一點(diǎn)無(wú)用功，提升 SQL 執(zhí)行效率。

本文轉(zhuǎn)載自微信公眾號(hào)「一樹(shù)一溪」，可以通過(guò)以下二維碼關(guān)注。轉(zhuǎn)載本文請(qǐng)聯(lián)系一樹(shù)一溪公眾號(hào)。