子查詢系列的上一篇文章??《MySQL 不相關子查詢怎么執(zhí)行？》???提到過，MySQL 有可能把不相關?子查詢轉換為相關子查詢。

這一篇我們就來聊聊不相關子查詢轉換為相關子查詢，以及相關子查詢執(zhí)行的那些事。

本文不相關子查詢都是指的 IN 子查詢，內容基于 MySQL 8.0.29 源碼。

正文

1、explain type、ref 列的顯示邏輯

本文示例 SQL 中的表，都來自于官方提供的測試數(shù)據庫 sakila，下載鏈接如下：https://downloads.mysql.com/docs/sakila-db.tar.gz

相關子查詢有兩種來源（也許還有其它來源？）：

• 一種是我們純手工打造的。
• 另一種就是從不相關子查詢轉換來的了。

通過 explain 查看這兩種 SQL 的執(zhí)行計劃，子查詢的 type、ref 列可能一樣，也可能不一樣，難免讓人困惑。

我們先來弄清楚兩種 SQL 的 explain 結果中，子查詢的 type、ref 列為什么會顯示不一樣？

示例 SQL 1：

-- 為了保證 EXISTS 子查詢不會轉換為半連接
-- 先把半連接優(yōu)化關閉
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 子查詢不會轉換為半連接
-- 先把半連接優(yōu)化關閉
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，在調試控制臺執(zhí)行如下命令可以驗證：

這里 REF_ITEM 是對 FIELD_ITEM 的引用，這是源碼中對包含子查詢的 IN 條件字段所做的優(yōu)化，我們在此不深入具體細節(jié)。

# 新條件 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 準備階段、優(yōu)化階段、執(zhí)行階段的一些內部細節(jié)。特別是可以了解 MySQL 選擇某個執(zhí)行計劃的決策依據。

optimizer trace 默認為關閉狀態(tài)，如果需要，可以通過執(zhí)行以下 SQL 開啟：

SET optimizer_trace = "enabled=on"

開啟了 optimizer trace，執(zhí)行 SQL 語句之后，可以通過以下 SQL 得到 optimizer trace 結果：

SELECT * FROM information_schema.OPTIMIZER_TRACE

OPTIMIZER_TRACE 表有 4 個字段：

• QUERY：SQL 語句。
• TRACE：json 格式的 optimizer trace 內容，如果內容長度超過系統(tǒng)變量 optimizer_trace_max_mem_size 的值就會被截斷。該系統(tǒng)變量控制的是一條 SQL 的 optimizer trace 內容長度，默認值是 1048576(字節(jié))。
• MISSING_BYTES_BEYOND_MAX_MEM_SIZE：如果 optimizer trace 內容因超長被截斷，這個字段記錄了被截斷的字節(jié)數(shù)。
• INSUFFICIENT_PRIVILEGES：如果用戶執(zhí)行 QUERY 字段中的 SQL 語句權限不夠，導致 TRACE 字段內容為空，該字段會顯示為 1。

如果使用客戶端（如 Navicat），我們執(zhí)行一條 SQL，客戶端可能會額外執(zhí)行一些統(tǒng)計 SQL。

因為默認配置只會保留最近一條 SQL 的 optimizer trace 內容，使用客戶端有可能導致我們看不到自己的 SQL optimizer trace 內容。

這種情況下，我們需要修改 2 個系統(tǒng)變量的值：

• optimizer_trace_offset：從最近執(zhí)行的哪條 SQL 開始保存 optimizer trace 內容，默認值為 -1，表示從最近執(zhí)行的 1 條 SQL 開始保存 optimizer trace 內容。
• optimizer_trace_limit：保存多少條 optimizer trace 內容，默認值為 1。

3、IN 子查詢轉換

IN 子查詢有 3 大執(zhí)行策略：

• 轉換為半連接，這是最優(yōu)先的執(zhí)行策略。
• 子查詢物化。
• 轉換為相關子查詢。

如果子查詢中存在像 group by 子句這樣的限制因素，或者因為成本問題不能轉換為半連接，那就要在物化和相關子查詢兩種策略中二選一了。

（1）要不要轉換？

還是以前面的 IN 子查詢 SQL 為例，我們通過 optimizer trace 來看看 MySQL 在物化和相關子查詢兩種策略中二選一的過程。

-- 為了保證 IN 子查詢不會轉換為半連接
-- 先把半連接優(yōu)化關閉
SET optimizer_switch="semijoin=off";

-- 開啟 optimizer trace
SET optimizer_trace = "enabled=on";

-- 執(zhí)行 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 沒有?使用物化方式執(zhí)行子查詢，原因是使用物化方式的成本（cost_of_materialization = 133.749?）比相關子查詢的成本（cost_of_EXISTS = 34.6172）更高。

知道了結果，我們再來看看物化和相關子查詢的成本是怎么計算的。

使用物化方式執(zhí)行子查詢的成本：

parent_fanouts.fanout = 99 表示預估的主查詢 city 表中滿足 city_id < 100 的記錄數(shù)量。

number_of_subquery_evaluations 表示子查詢的執(zhí)行次數(shù)?，因為對于主查詢中滿足 city_id < 100 的每一條記錄，相關子查詢都要執(zhí)行一次，所以，這個字段值等于 parent_fanouts.fanout。

cost_to_create_and_fill_materialized_table 表示創(chuàng)建臨時表的成本，加上把子查詢中的所有記錄都寫入臨時表的成本。

cost_of_materialization 表示使用物化方式執(zhí)行 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 字段值，去臨時表中查詢記錄的成本常數(shù)，可以通過以下 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 的成本常數(shù)，因為使用的是內存臨時表。

如果子查詢使用的是磁盤臨時表，則需要查詢 cost_name 等于 disk_temptable_row_cost 的成本常數(shù)。

轉換為相關子查詢的執(zhí)行成本：cost_of_EXISTS = ?cost_of_one_EXISTS?(0.349669) * number_of_subquery_evaluations(99) = 34.6172。

cost_of_one_EXISTS 表示子查詢執(zhí)行一次的成本，number_of_subquery_evaluations 表示子查詢的執(zhí)行次數(shù)。

（2）怎么轉換?

還是以前面的示例 SQL 為例：

SELECT * FROM city
WHERE city_id < 100 AND city_id IN (
  SELECT city_id FROM address
)

在查詢準備階段，還沒有確定子查詢的執(zhí)行策略之前，就會把主查詢 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
)

那么問題來了，如果查詢優(yōu)化階段決定 IN 子查詢不轉換為相關子查詢，附加到子查詢 where 條件中的新條件怎么辦？

這個好辦，再刪掉就是了。

在構造的時候，新條件會被打上標記，表示這個條件是 IN 子查詢轉換為相關子查詢時新構造的。

有了這個標記，就能知道要刪除子查詢 where 條件中的那個條件了。

4、執(zhí)行流程

不管是 IN 子查詢轉換來的，還是我們純手工打造的相關子查詢，到了執(zhí)行階段，流程就一樣了。

還是以前面的示例 SQL 1 為例，來介紹相關子查詢的主要執(zhí)行流程：

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 在前，先判斷主查詢記錄是否滿足這個條件。

如果滿足，則執(zhí)行子查詢，否則，回到步驟 1。

假設主查詢讀取到 city 表的 city_id 字段值為 8，此時，要執(zhí)行的子查詢就是這樣的了：

SELECT city_id FROM address
WHERE address.city_id = 8

如果執(zhí)行子查詢查到了記錄，說明主查詢記錄滿足 city_id < 100 和 EXISTS 子查詢兩個條件，把主查詢記錄返回給客戶端，否則，回到步驟 1。

重復執(zhí)行步驟 1 ~ 2，直到讀完主查詢 city 表中滿足 city_id < 100 的所有記錄，執(zhí)行流程結束。

通過 optimizer trace 也可以驗證主查詢每讀取一條滿足 city_id < 100 的記錄，EXISTS 子查詢都要執(zhí)行一次，如下：

{
  "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 條件中出現(xiàn)的順序進行判斷的。

由于判斷主查詢記錄是否匹配 IN 子查詢條件時，需要執(zhí)行子查詢，成本比較高，所以，我們寫 SQL 的時候最好是把不包含子查詢的 where 條件放在前面，包含子查詢的 where 條件放在最后。

這個邏輯在《??MySQL 不相關子查詢怎么執(zhí)行？??》 中有過詳細介紹，這里不再重復了。

6、總結

本文主要介紹了以下內容：

• 不相關子查詢轉換為相關子查詢之后，explain 結果中：

• 子查詢 type 列的值 unique_subquery 是 eq_ref 的別名；index_subquery 是 ref 或 ref_or_null 的別名。
• 子查詢 ref 列的值會顯示為 func，這是因為主查詢 IN 條件字段和子查詢 select 子句字段組成的新條件中，IN 條件字段引用了主查詢表中的字段，而不是直接使用主查詢表中的字段。

• 不相關子查詢，如果不能轉換為半連接，則會在物化和相關子查詢兩種策略中二選一。
• 兩種策略二選一的依據是子查詢執(zhí)行成本，哪種執(zhí)行成本低就選擇哪種。通過 optimizer trace 可以看到兩種執(zhí)行策略的成本。
• 簡單介紹了相關子查詢的執(zhí)行流程。

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