MySQL 相關子查詢
子查詢系列的上一篇文章《MySQL 不相關子查詢怎麼執行？》提到過，MySQL 有可能把不相關子查詢轉換為相關子查詢。
這一篇我們就來聊聊不相關子查詢轉換為相關子查詢，以及相關子查詢執行的那些事。
本文不相關子查詢都是指的
IN 子查詢，內容基於 MySQL 8.0.29 原始碼。
目錄
1. explain type、ref 列的顯示邏輯
2. optimizer trace
3. IN 子查詢轉換
3.1 要不要轉換？
3.2 怎麼轉換?
4. 執行流程
5. 最佳實踐
6. 總結
正文
1. explain type、ref 列的顯示邏輯
本文示例 SQL 中的表，都來自於官方提供的測試資料庫 sakila，下載連結如下：
相關子查詢有兩種來源（也許還有其它來源？）：
一種是我們純手工打造的。 另一種就是從不相關子查詢轉換來的了。
透過 explain 檢視這兩種 SQL 的執行計劃，子查詢的 type、ref 列可能一樣，也可能不一樣，難免讓人困惑。
我們先來弄清楚兩種 SQL 的 explain 結果中，子查詢的 type、ref 列為什麼會顯示不一樣？
示例 SQL 1：
-- 為了保證 EXISTS 子查詢不會轉換為半連線
-- 先把半連線最佳化關閉
SET optimizer_switch="semijoin=off";
-- 純手工打造的相關子查詢
EXPLAIN SELECT * FROM city
WHERE city_id < 100 AND EXISTS (
SELECT city_id FROM address
WHERE city.city_id = address.city_id
)
-- explain 結果如下，為了方便檢視
-- 我調整了 ref 列的位置
-- 並且刪掉了 partitions 列
+----+--------------------+---------+-------+---------------------+----------------+----------------+---------+------+----------+-------------+
| id | select_type | table | type | ref | possible_keys | key | key_len | rows | filtered | Extra |
+----+--------------------+---------+-------+---------------------+----------------+----------------+---------+------+----------+-------------+
| 1 | PRIMARY | city | range | <null> | PRIMARY | PRIMARY | 2 | 99 | 100.0 | Using where |
| 2 | DEPENDENT SUBQUERY | address | ref | sakila.city.city_id | idx_fk_city_id | idx_fk_city_id | 2 | 1 | 100.0 | Using index |
+----+--------------------+---------+-------+---------------------+----------------+----------------+---------+------+----------+-------------+
子查詢 type 列的值為 ref，表示 address 表使用 idx_fk_city_id 索引（key 列的值）進行等值範圍掃描。
子查詢 ref 列的值為 sakila.city.city_id，表示 where 條件中 address.city_id 欄位值來源於主查詢 city 表的 city_id 欄位值。
示例 SQL 2：
-- 為了保證 IN 子查詢不會轉換為半連線
-- 先把半連線最佳化關閉
SET optimizer_switch="semijoin=off";
-- 不相關子查詢
EXPLAIN SELECT * FROM city
WHERE city_id < 100 AND city_id IN (
SELECT city_id FROM address
)
-- explain 結果如下
-- 可以看到不相關子查詢已經轉換為相關子查詢了
-- 為了方便檢視，我調整了 ref 列的位置
-- 並且刪掉了 partitions 列
+----+--------------------+---------+----------------+--------+----------------+----------------+---------+------+----------+-------------+
| id | select_type | table | type | ref | possible_keys | key | key_len | rows | filtered | Extra |
+----+--------------------+---------+----------------+--------+----------------+----------------+---------+------+----------+-------------+
| 1 | PRIMARY | city | range | <null> | PRIMARY | PRIMARY | 2 | 99 | 100.0 | Using where |
| 2 | DEPENDENT SUBQUERY | address | index_subquery | func | idx_fk_city_id | idx_fk_city_id | 2 | 1 | 100.0 | Using index |
+----+--------------------+---------+----------------+--------+----------------+----------------+---------+------+----------+-------------+
子查詢 type 列的值為 index_subquery，ref 列的值為 func。
這 2 列的值看起來挺唬人的，但實際上和示例 SQL 1 的 type = ref，ref = sakila.city.city_id 並沒有什麼不一樣，無非是換了一身行頭而已。
我們先從原始碼裡看看 type = index_subquery 是怎麼來的：
// sql/opt_explain.cc
bool Explain_join::explain_join_type() {
const join_type j_t = type == JT_UNKNOWN ? JT_ALL : type;
const char *str = join_type_str[j_t];
// 訪問方式是 eq_ref
if ((j_t == JT_EQ_REF ||
// 訪問方式是 ref
j_t == JT_REF ||
// 訪問方式是 ref_or_null
j_t == JT_REF_OR_NULL) &&
// 當前 select 語句是子查詢
join->query_expression()->item) {
/*
For backward-compatibility, we have special presentation of "index
lookup used for in(subquery)": we do not show "ref/etc", but
"index_subquery/unique_subquery".
*/
// 如果這個 if 判斷條件成立
// 就說明 IN 子查詢已經轉換為【相關子查詢】了
if (join->query_expression()->item->engine_type() ==
Item_subselect::INDEXSUBQUERY_ENGINE)
str = (j_t == JT_EQ_REF)
? "unique_subquery"
: "index_subquery";
}
fmt->entry()->col_join_type.set_const(str);
return false;
}
上面程式碼是 explain 結果中 type 列的顯示邏輯。
從程式碼可以看到 IN 子查詢轉換為相關子查詢之後，type 列的顯示邏輯如下：
表的訪問方式是 eq_ref，type 列的值為unique_subquery。表的訪問方式是 ref或ref_or_null，type 列的值為index_subquery。
由此，我們就揭開了 index_subquery 的神秘面紗，實際上它就是 ref 或 ref_no_null。
另外，從程式碼的英文註釋中，我們可以看到，type 列之所以這麼顯示是為了向後相容。
接下來，我們再來看看 ref 列的顯示邏輯：
// sql/sql_select.h
class store_key {
......
virtual const char *name() const {
// Compatible with legacy behavior.
// where 條件欄位是正常欄位（另一個表的欄位）
// 返回的是欄位全名，即 db.table.field
if (item->type() == Item::FIELD_ITEM) {
return item->full_name();
} else {
return "func";
}
}
......
}
IN 子查詢轉換為相關子查詢之後，主查詢 where 條件的 city_id 欄位和子查詢 select 子句的 city_id 欄位會組成新條件（address.city_id = city.city_id），附加到子查詢 where 條件中。
新條件的 city.city_id 欄位型別是 REF_ITEM，而不是 FIELD_ITEM，在除錯控制檯執行如下命令可以驗證：
這裡 REF_ITEM 是對 FIELD_ITEM 的引用，這是原始碼中對包含子查詢的 IN 條件欄位所做的最佳化，我們在此不深入具體細節。
# 新條件 city.city_id 欄位的表名
(lldb) print ((Item_field *)((Item_cache_int *)item->real_item())->get_example())->table_name
(const char *) $16 = "city"
# 新條件 city.city_id 欄位的欄位名
(lldb) print ((Item_field *)((Item_cache_int *)item->real_item())->get_example())->field_name
(const char *) $17 = "city_id"
# 新條件 city.city_id 的型別
(lldb) print item->type()
(Item::Type) $18 = REF_ITEM
所以，新條件型別是 REF_ITEM，命中了前面程式碼中的 else 分支（return "func"），explain 結果的 ref 列就顯示為 func 了。
ref 列的值雖然顯示為 func，但是新條件 city.city_id 欄位還是讀取的主查詢 city_id 欄位值，只不過是中間隔了一層，其它並沒有什麼特殊的。
釐清了兩種 SQL explain 結果 type、ref 列的不同之處，就可以開始介紹不相關子查詢轉換為相關子查詢的邏輯了。
因為在介紹過程中會用到 optimizer trace，所以先來簡單瞭解下 optimizer trace 的相關知識點。
2. optimizer trace
透過 optimizer trace，我們可以瞭解到 MySQL 準備階段、最佳化階段、執行階段的一些內部細節。特別是可以瞭解 MySQL 選擇某個執行計劃的決策依據。
optimizer trace 預設為關閉狀態，如果需要，可以透過執行以下 SQL 開啟：
SET optimizer_trace = "enabled=on"
開啟了 optimizer trace，執行 SQL 語句之後，可以透過以下 SQL 得到 optimizer trace 結果：
SELECT * FROM information_schema.OPTIMIZER_TRACE
OPTIMIZER_TRACE 表有 4 個欄位：
QUERY：SQL 語句。TRACE：json 格式的 optimizer trace 內容，如果內容長度超過系統變數optimizer_trace_max_mem_size的值就會被截斷。
該系統變數控制的是一條 SQL 的 optimizer trace 內容長度，預設值是 1048576(位元組)。MISSING_BYTES_BEYOND_MAX_MEM_SIZE：如果 optimizer trace 內容因超長被截斷，這個欄位記錄了被截斷的位元組數。INSUFFICIENT_PRIVILEGES：如果使用者執行 QUERY 欄位中的 SQL 語句許可權不夠，導致 TRACE 欄位內容為空，該欄位會顯示為 1。
如果使用客戶端（如 Navicat），我們執行一條 SQL，客戶端可能會額外執行一些統計 SQL。
因為預設配置只會保留最近一條 SQL 的 optimizer trace 內容，使用客戶端有可能導致我們看不到自己的 SQL optimizer trace 內容。
這種情況下，我們需要修改 2 個系統變數的值：
optimizer_trace_offset：從最近執行的哪條 SQL 開始儲存 optimizer trace 內容，預設值為-1，表示從最近執行的 1 條 SQL 開始儲存 optimizer trace 內容。optimizer_trace_limit：儲存多少條 optimizer trace 內容，預設值為 1。
3. IN 子查詢轉換
IN 子查詢有 3 大執行策略：
轉換為半連線，這是最優先的執行策略。 子查詢物化。 轉換為相關子查詢。
如果子查詢中存在像 group by 子句這樣的限制因素，或者因為成本問題不能轉換為半連線，那就要在物化和相關子查詢兩種策略中二選一了。
3.1 要不要轉換？
還是以前面的 IN 子查詢 SQL 為例，我們透過 optimizer trace 來看看 MySQL 在物化和相關子查詢兩種策略中二選一的過程。
-- 為了保證 IN 子查詢不會轉換為半連線
-- 先把半連線最佳化關閉
SET optimizer_switch="semijoin=off";
-- 開啟 optimizer trace
SET optimizer_trace = "enabled=on";
-- 執行 select 語句
SELECT * FROM city
WHERE city_id < 100 AND city_id IN (
SELECT city_id FROM address
);
-- 獲取 select 語句的跟蹤資訊
SELECT * FROM information_schema.OPTIMIZER_TRACE;
以下是 optimizer trace 中關於物化和相關子查詢兩種策略的決策依據：
{
"execution_plan_for_potential_materialization": {
"subq_mat_decision": {
"parent_fanouts": [
{
"select#": 1,
"subq_attached_to_table": true,
"table": "`city`",
"fanout": 99,
"cacheable": true
}
],
"cost_to_create_and_fill_materialized_table": 123.849,
"cost_of_one_EXISTS": 0.349669,
"number_of_subquery_evaluations": 99,
"cost_of_materialization": 133.749,
"cost_of_EXISTS": 34.6172,
"chosen": false
}
}
}
chosen 欄位值為 false，表示 MySQL 沒有使用物化方式執行子查詢，原因是使用物化方式的成本（cost_of_materialization = 133.749）比相關子查詢的成本（cost_of_EXISTS = 34.6172）更高。
知道了結果，我們再來看看物化和相關子查詢的成本是怎麼計算的。
使用物化方式執行子查詢的成本：
parent_fanouts.fanout = 99 表示預估的主查詢 city 表中滿足 city_id < 100 的記錄數量。
number_of_subquery_evaluations 表示子查詢的執行次數，因為對於主查詢中滿足 city_id < 100 的每一條記錄，相關子查詢都要執行一次，所以，這個欄位值等於 parent_fanouts.fanout。
cost_to_create_and_fill_materialized_table 表示建立臨時表的成本，加上把子查詢中的所有記錄都寫入臨時表的成本。
cost_of_materialization 表示使用物化方式執行 IN 子查詢的總成本，計算邏輯如下：
cost_of_materialization = cost_to_create_and_fill_materialized_table(123.849) + number_of_subquery_evaluations(99) * 0.1 = 133.749。
其中 0.1 是從主查詢中讀取一條記錄之後，拿到 city_id 欄位值，去臨時表中查詢記錄的成本常數，可以透過以下 SQL 獲取：
SELECT
cost_name, cost_value, default_value
FROM mysql.server_cost
WHERE cost_name = 'memory_temptable_row_cost'
+---------------------------+------------+---------------+
| cost_name | cost_value | default_value |
+---------------------------+------------+---------------+
| memory_temptable_row_cost | <null> | 0.1 |
+---------------------------+------------+---------------+
查詢 cost_name 等於 memory_temptable_row_cost 的成本常數，因為使用的是記憶體臨時表。
如果子查詢使用的是磁碟臨時表，則需要查詢 cost_name 等於 disk_temptable_row_cost 的成本常數。
轉換為相關子查詢的執行成本：
cost_of_EXISTS = cost_of_one_EXISTS(0.349669) * number_of_subquery_evaluations(99) = 34.6172。
cost_of_one_EXISTS 表示子查詢執行一次的成本，number_of_subquery_evaluations 表示子查詢的執行次數。
3.2 怎麼轉換?
還是以前面的示例 SQL 為例：
SELECT * FROM city
WHERE city_id < 100 AND city_id IN (
SELECT city_id FROM address
)
在查詢準備階段，還沒有確定子查詢的執行策略之前，就會把主查詢 where 條件中的 IN 條件欄位和子查詢 select 子句中的欄位組成新條件，並附加到子查詢的 where 條件中。
也就是把 city 表的 city_id 欄位和 address 表的 city_id 欄位組成新條件，附加到子查詢中，看起來就像是這樣的 select 語句：
SELECT * FROM city
WHERE city_id < 100 AND EXISTS (
SELECT city_id FROM address
WHERE city.city_id = address.city_id
)
那麼問題來了，如果查詢最佳化階段決定 IN 子查詢不轉換為相關子查詢，附加到子查詢 where 條件中的新條件怎麼辦？
這個好辦，再刪掉就是了。
在構造的時候，新條件會被打上標記，表示這個條件是 IN 子查詢轉換為相關子查詢時新構造的。
有了這個標記，就能知道要刪除子查詢 where 條件中的那個條件了。
4. 執行流程
不管是 IN 子查詢轉換來的，還是我們純手工打造的相關子查詢，到了執行階段，流程就一樣了。
還是以前面的示例 SQL 1 為例，來介紹相關子查詢的主要執行流程：
SELECT * FROM city
WHERE city_id < 100 AND EXISTS (
SELECT city_id FROM address
WHERE city.city_id = address.city_id
)
步驟 1，主查詢從 city 表讀取一條記錄。
步驟 2，判斷主查詢記錄是否匹配 where 條件。
因為 city_id < 100 在前，先判斷主查詢記錄是否滿足這個條件。
如果滿足，則執行子查詢，否則，回到步驟 1。
假設主查詢讀取到 city 表的 city_id 欄位值為 8，此時，要執行的子查詢就是這樣的了：
SELECT city_id FROM address
WHERE address.city_id = 8
如果執行子查詢查到了記錄，說明主查詢記錄滿足 city_id < 100 和 EXISTS 子查詢兩個條件，把主查詢記錄返回給客戶端，否則，回到步驟 1。
重複執行步驟 1 ~ 2，直到讀完主查詢 city 表中滿足 city_id < 100 的所有記錄，執行流程結束。
透過 optimizer trace 也可以驗證主查詢每讀取一條滿足 city_id < 100 的記錄，EXISTS 子查詢都要執行一次，如下：
{
"join_execution": {
"select#": 1,
"steps": [
{
// 主查詢一共有 99 滿足 where 條件的記錄
// steps 中有 99 個 subselect_execution
"subselect_execution": {
"select#": 2,
"steps": [
{
"join_execution": {
"select#": 2,
"steps": []
}
}
]
}
// 此處省略 98 個 subselect_execution
// ......
}
]
}
}
以下是 optimizer trace 的部分內容截圖，expanded_query 就是經過 MySQL 展開處理之後的 select 語句，我做了一些簡化和處理，如下：
join_execution 的 steps 後面，99 items 就是 99 個摺疊起來的 subselect_execution。
5. 最佳實踐
MySQL 讀取主查詢的一條記錄之後，判斷記錄是否匹配 where 條件，是按照我們寫 SQL 時欄位在 where 條件中出現的順序進行判斷的。
由於判斷主查詢記錄是否匹配 IN 子查詢條件時，需要執行子查詢，成本比較高，所以，我們寫 SQL 的時候最好是把不包含子查詢的 where 條件放在前面，包含子查詢的 where 條件放在最後。
這個邏輯在《MySQL 不相關子查詢怎麼執行？》 中有過詳細介紹，這裡不再重複了。
6. 總結
本文主要介紹了以下內容：
不相關子查詢轉換為相關子查詢之後，explain 結果中： 子查詢 type 列的值 unique_subquery 是 eq_ref 的別名；index_subquery 是 ref 或 ref_or_null 的別名。 子查詢 ref 列的值會顯示為 func，這是因為主查詢 IN 條件欄位和子查詢 select 子句欄位組成的新條件中，IN 條件欄位 引用了主查詢表中的欄位，而不是直接使用主查詢表中的欄位。不相關子查詢，如果不能轉換為半連線，則會在物化和相關子查詢兩種策略中二選一。 兩種策略二選一的依據是 子查詢執行成本，哪種執行成本低就選擇哪種。透過 optimizer trace 可以看到兩種執行策略的成本。簡單介紹了相關子查詢的執行流程。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/70024924/viewspace-2929465/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 相關子查詢&非相關子查詢概念
- sql語法相關子查詢與非相關子查詢SQL
- 什麼是SQL 語句中相關子查詢與非相關子查詢SQL
- sql-server相關子查詢SQLServer
- MySQL 之慢查詢相關操作MySql
- MySQL子查詢MySql
- MYsql 子查詢MySql
- 關於mysql 子查詢中 使用 limitMySqlMIT
- MySQL慢查詢日誌相關設定MySql
- mysql的子查詢MySql
- MySQL聯結查詢和子查詢MySql
- 區分關聯子查詢和非關聯子查詢
- exist-in和關聯子查詢-非關聯子查詢
- UNDO相關查詢
- MySQL之連線查詢和子查詢MySql
- 【MySQL】檢視&子查詢MySql
- [MYSQL -14]使用子查詢MySql
- 【MySQL】子查詢之一MySql
- CURL查詢ES相關
- MySQL全面瓦解9：查詢的排序、分頁相關MySql排序
- MySQL查詢最佳化方案彙總（索引相關）MySql索引
- mysql-分組查詢-子查詢-連線查詢-組合查詢MySql
- 關聯查詢子查詢效率簡單比照
- MySQL exists關聯子查詢SQL效能及其低下最佳化之等值子查詢轉換MySql
- 子查詢-表子查詢
- MySQL全面瓦解11：子查詢和組合查詢MySql
- MySQL資料庫基礎——多表查詢：子查詢MySql資料庫
- MYSQL學習筆記26: 多表查詢|子查詢MySql筆記
- 表空間相關查詢
- 關聯子查詢 Correlated Subqueries
- MYSQL學習筆記25: 多表查詢(子查詢)[標量子查詢,列子查詢]MySql筆記
- MySQL 優化五（關聯查詢子查詢以及 in 的效率問題）（高階篇）MySql優化
- MySQL子查詢的優化薦MySql優化
- MySQL索引的最左字首原理與查詢的相關優化MySql索引優化
- MySQL多表關聯查詢MySql
- Oracle許可權相關查詢Oracle
- [鎖機制] 鎖相關查詢
- 鎖表的相關資訊查詢