MySQL 相关子查询

csch

发布于 2023-05-24 10:15:04

5440

发布于 2023-05-24 10:15:04

文章被收录于专栏：一树一溪

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

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

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

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2022-12-26，如有侵权请联系 cloudcommunity@tencent.com 删除

mysql