子查詢系列的上一篇文章??《MySQL 不相關(guān)子查詢?cè)趺磮?zhí)行？》???提到過，MySQL 有可能把不相關(guān)?子查詢轉(zhuǎn)換為相關(guān)子查詢。

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

本文不相關(guān)子查詢都是指的 IN 子查詢，內(nèi)容基于 MySQL 8.0.29 源碼。

正文

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

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

相關(guān)子查詢有兩種來源（也許還有其它來源？）：

• 一種是我們純手工打造的。
• 另一種就是從不相關(guān)子查詢轉(zhuǎn)換來的了。

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

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

示例 SQL 1：

-- 為了保證 EXISTS 子查詢不會(huì)轉(zhuǎn)換為半連接
-- 先把半連接優(yōu)化關(guān)閉
SET optimizer_switch="semijoin=off";

-- 純手工打造的相關(guān)子查詢
EXPLAIN SELECT * FROM city
WHERE city_id < 100 AND EXISTS (
  SELECT city_id FROM address
  WHERE city.city_id = address.city_id
)

-- explain 結(jié)果如下，為了方便查看
-- 我調(diào)整了 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 列的值）進(jìn)行等值范圍掃描。

子查詢 ref 列的值為 sakila.city.city_id，表示 where 條件中 address.city_id 字段值來源于主查詢 city 表的 city_id 字段值。

示例 SQL 2：

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

-- 不相關(guān)子查詢
EXPLAIN SELECT * FROM city
WHERE city_id < 100 AND city_id IN (
  SELECT city_id FROM address
)

-- explain 結(jié)果如下
-- 可以看到不相關(guān)子查詢已經(jīng)轉(zhuǎn)換為相關(guān)子查詢了
-- 為了方便查看，我調(diào)整了 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 列的值看起來挺唬人的，但實(shí)際上和示例 SQL 1 的 type = ref，ref = sakila.city.city_id 并沒有什么不一樣，無(wú)非是換了一身行頭而已。

我們先從源碼里看看 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) &&
       // 當(dāng)前 select 語(yǔ)句是子查詢
       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".
    */
    // 如果這個(gè) if 判斷條件成立
    // 就說明 IN 子查詢已經(jīng)轉(zhuǎn)換為【相關(guān)子查詢】了
    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 結(jié)果中 type 列的顯示邏輯。

從代碼可以看到 IN 子查詢轉(zhuǎn)換為相關(guān)子查詢之后，type 列的顯示邏輯如下：

• 表的訪問方式是eq_ref，type 列的值為 unique_subquery。
• 表的訪問方式是ref 或 ref_or_null，type 列的值為 index_subquery。

由此，我們就揭開了 index_subquery 的神秘面紗，實(shí)際上它就是 ref 或 ref_no_null。

另外，從代碼的英文注釋中，我們可以看到，type 列之所以這么顯示是為了向后兼容。

接下來，我們?cè)賮砜纯?ref 列的顯示邏輯：

// sql/sql_select.h
class store_key {
  ......
  virtual const char *name() const {
    // Compatible with legacy behavior.
    // where 條件字段是正常字段（另一個(gè)表的字段）
    // 返回的是字段全名，即 db.table.field
    if (item->type() == Item::FIELD_ITEM) {
      return item->full_name();
    } else {
      return "func";
    }
  }
  ......
}

IN 子查詢轉(zhuǎn)換為相關(guān)子查詢之后，主查詢 where 條件的 city_id 字段和子查詢 select 子句的 city_id 字段會(huì)組成新條件（address.city_id = city.city_id），附加到子查詢 where 條件中。

新條件的 city.city_id 字段類型是 REF_ITEM，而不是 FIELD_ITEM，在調(diào)試控制臺(tái)執(zhí)行如下命令可以驗(yàn)證：

這里 REF_ITEM 是對(duì) FIELD_ITEM 的引用，這是源碼中對(duì)包含子查詢的 IN 條件字段所做的優(yōu)化，我們?cè)诖瞬簧钊刖唧w細(xì)節(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 結(jié)果的 ref 列就顯示為 func 了。

ref 列的值雖然顯示為 func，但是新條件 city.city_id 字段還是讀取的主查詢 city_id 字段值，只不過是中間隔了一層，其它并沒有什么特殊的。

厘清了兩種 SQL explain 結(jié)果 type、ref 列的不同之處，就可以開始介紹不相關(guān)子查詢轉(zhuǎn)換為相關(guān)子查詢的邏輯了。

因?yàn)樵诮榻B過程中會(huì)用到 optimizer trace，所以先來簡(jiǎn)單了解下 optimizer trace 的相關(guān)知識(shí)點(diǎn)。

2、optimizer trace

通過 optimizer trace，我們可以了解到 MySQL 準(zhǔn)備階段、優(yōu)化階段、執(zhí)行階段的一些內(nèi)部細(xì)節(jié)。特別是可以了解 MySQL 選擇某個(gè)執(zhí)行計(jì)劃的決策依據(jù)。

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

SET optimizer_trace = "enabled=on"

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

SELECT * FROM information_schema.OPTIMIZER_TRACE

OPTIMIZER_TRACE 表有 4 個(gè)字段：

• QUERY：SQL 語(yǔ)句。
• TRACE：json 格式的 optimizer trace 內(nèi)容，如果內(nèi)容長(zhǎng)度超過系統(tǒng)變量 optimizer_trace_max_mem_size 的值就會(huì)被截?cái)唷Ｔ撓到y(tǒng)變量控制的是一條 SQL 的 optimizer trace 內(nèi)容長(zhǎng)度，默認(rèn)值是 1048576(字節(jié))。
• MISSING_BYTES_BEYOND_MAX_MEM_SIZE：如果 optimizer trace 內(nèi)容因超長(zhǎng)被截?cái)?，這個(gè)字段記錄了被截?cái)嗟淖止?jié)數(shù)。
• INSUFFICIENT_PRIVILEGES：如果用戶執(zhí)行 QUERY 字段中的 SQL 語(yǔ)句權(quán)限不夠，導(dǎo)致 TRACE 字段內(nèi)容為空，該字段會(huì)顯示為 1。

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

因?yàn)槟J(rèn)配置只會(huì)保留最近一條 SQL 的 optimizer trace 內(nèi)容，使用客戶端有可能導(dǎo)致我們看不到自己的 SQL optimizer trace 內(nèi)容。

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

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

3、IN 子查詢轉(zhuǎn)換

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

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

如果子查詢中存在像 group by 子句這樣的限制因素，或者因?yàn)槌杀締栴}不能轉(zhuǎn)換為半連接，那就要在物化和相關(guān)子查詢兩種策略中二選一了。

（1）要不要轉(zhuǎn)換？

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

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

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

-- 執(zhí)行 select 語(yǔ)句
SELECT * FROM city
WHERE city_id < 100 AND city_id IN (
  SELECT city_id FROM address
);

-- 獲取 select 語(yǔ)句的跟蹤信息
SELECT * FROM information_schema.OPTIMIZER_TRACE;

以下是 optimizer trace 中關(guān)于物化和相關(guān)子查詢兩種策略的決策依據(jù)：

{
  "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?）比相關(guān)子查詢的成本（cost_of_EXISTS = 34.6172）更高。

知道了結(jié)果，我們?cè)賮砜纯次锘拖嚓P(guān)子查詢的成本是怎么計(jì)算的。

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

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

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

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

cost_of_materialization 表示使用物化方式執(zhí)行 IN 子查詢的總成本，計(jì)算邏輯如下：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í)表中查詢記錄的成本常數(shù)，可以通過以下 SQL 獲?。?/p>

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ù)，因?yàn)槭褂玫氖莾?nèi)存臨時(shí)表。

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

轉(zhuǎn)換為相關(guān)子查詢的執(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）怎么轉(zhuǎn)換?

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

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

在查詢準(zhǔn)備階段，還沒有確定子查詢的執(zhí)行策略之前，就會(huì)把主查詢 where 條件中的 IN 條件字段和子查詢 select 子句中的字段組成新條件，并附加到子查詢的 where 條件中。

也就是把 city 表的 city_id 字段和 address 表的 city_id 字段組成新條件，附加到子查詢中，看起來就像是這樣的 select 語(yǔ)句：

SELECT * FROM city
WHERE city_id < 100 AND EXISTS (
  SELECT city_id FROM address
  WHERE city.city_id = address.city_id
)

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

這個(gè)好辦，再刪掉就是了。

在構(gòu)造的時(shí)候，新條件會(huì)被打上標(biāo)記，表示這個(gè)條件是 IN 子查詢轉(zhuǎn)換為相關(guān)子查詢時(shí)新構(gòu)造的。

有了這個(gè)標(biāo)記，就能知道要?jiǎng)h除子查詢 where 條件中的那個(gè)條件了。

4、執(zhí)行流程

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

還是以前面的示例 SQL 1 為例，來介紹相關(guān)子查詢的主要執(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 條件。

因?yàn)?nbsp;city_id < 100 在前，先判斷主查詢記錄是否滿足這個(gè)條件。

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

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

SELECT city_id FROM address
WHERE address.city_id = 8

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

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

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

{
  "join_execution": {
    "select#": 1,
    "steps": [
      {
        // 主查詢一共有 99 滿足 where 條件的記錄
        // steps 中有 99 個(gè) subselect_execution
        "subselect_execution": {
          "select#": 2,
          "steps": [
            {
              "join_execution": {
                "select#": 2,
                "steps": []
              }
            }
          ]
        }
        // 此處省略 98 個(gè) subselect_execution
        // ......
      }
    ]
  }
}

以下是 optimizer trace 的部分內(nèi)容截圖，expanded_query 就是經(jīng)過 MySQL 展開處理之后的 select 語(yǔ)句，我做了一些簡(jiǎn)化和處理，如下：

join_execution 的 steps 后面，99 items 就是 99 個(gè)折疊起來的 subselect_execution。

5、最佳實(shí)踐

MySQL 讀取主查詢的一條記錄之后，判斷記錄是否匹配 where 條件，是按照我們寫 SQL 時(shí)字段在 where 條件中出現(xiàn)的順序進(jìn)行判斷的。

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

這個(gè)邏輯在《??MySQL 不相關(guān)子查詢?cè)趺磮?zhí)行？??》 中有過詳細(xì)介紹，這里不再重復(fù)了。

6、總結(jié)

本文主要介紹了以下內(nèi)容：

• 不相關(guān)子查詢轉(zhuǎn)換為相關(guān)子查詢之后，explain 結(jié)果中：

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

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

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