日韩无码专区无码一级三级片|91人人爱网站中日韩无码电影|厨房大战丰满熟妇|AV高清无码在线免费观看|另类AV日韩少妇熟女|中文日本大黄一级黄色片|色情在线视频免费|亚洲成人特黄a片|黄片wwwav色图欧美|欧亚乱色一区二区三区

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

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

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

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

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

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

1、概述

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

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

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

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

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

2、執(zhí)行流程

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• 從 5.7 繼承過來的MEMORY 引擎。
• 8.0 新加入的TempTable 引擎。

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

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

然而，internal_tmp_mem_storage_engine? 指定的引擎并不一定是最終的選擇，有兩種情況會導致臨時表使用磁盤?存儲引擎 InnoDB。

這兩種情況如下：

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

為啥？因為 MEMORY 引擎不支持 BLOB 字段。

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

big_tables 的默認值為 OFF。

這又為啥？

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

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

此時，我們就可以通過 Hint 告訴 MySQL 這條 SQL 的結(jié)果記錄數(shù)量很少，MySQL 就能心領(lǐng)神會的直接使用 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'

捋清楚了選擇存儲引擎的邏輯，接下來就是字段了，臨時表會包含哪些字段?

這里沒有復雜邏輯需要說明，臨時表只會包含子查詢 SELECT 子句中的字段，例如：上面的示例  SQL 中，臨時表包含的字段為 address_id。

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

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

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

說完了字段，我們再來看看索引結(jié)構(gòu)，這取決于臨時表最終選擇了哪個存儲引擎：

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

4、自動優(yōu)化

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

介紹這個優(yōu)化之前，我們先準備一條 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 條件包含子查詢，如果不對 where 條件判斷做優(yōu)化，從 city 表中每讀取一條記錄之后，先拿到 country_id 字段值，再去臨時表中查找記錄，以判斷條件是否成立。

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

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

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

接下來從 city 表中讀取 city_id = 74 ~ 78 的記錄，因為它們的 country_id 字段值都是 44，實際上沒有必要再去臨時表里找查找記錄了，直接復用 city_id = 73 的判斷結(jié)果就可以了，這樣能節(jié)省幾次去臨時表查找記錄的時間。

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

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

5、手動優(yōu)化

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

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

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

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

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

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

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

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

• 先判斷 country_id 條件，成本為 5，再判斷 city 條件，成本為 1，總成本 5 + 1 = 6。
• 先判斷 city 條件，成本為 1，因為條件不成立，不需要再判斷 country_id 條件，總成本為 1。

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

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

6、總結(jié)

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

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

標題名稱：MySQL不相關(guān)子查詢怎么執(zhí)行？
本文網(wǎng)址：http://m.5511xx.com/article/codcgpj.html