聊一聊 MySQL 相关子查询-mysql子查询和连接查询

子查询系列的上一篇文章《MySQL 不相关子查询怎么执行？》提到过，MySQL 有可能把不相关子查询转换为相关子查询。

这一篇我们就来聊聊不相关子查询转换为相关子查询，以及相关子查询执行的那些事。

本文不相关子查询都是指的 IN 子查询，内容基于 MySQL 8.0.29 源码。

正文

1、explain type、ref 列的显示逻辑

本文示例 SQL 中的表，都来自于官方提供的测试数据库 sakila，下载链接如下：https://downloads.mysql.com/docs/sakila-db.tar.gz

相关子查询有两种来源（也许还有其它来源？）：

一种是我们纯手工打造的。
另一种就是从不相关子查询转换来的了。

通过 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 子句这样的限制因素，或者因为成本问题不能转换为半连接，那就要在物化和相关子查询两种策略中二选一了。

（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 表示子查询的执行次数。

（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 可以看到两种执行策略的成本。
简单介绍了相关子查询的执行流程。

本文转载自微信公众号「一树一溪」，可以通过以下二维码关注。转载本文请联系一树一溪公众号。