聊一聊 MySQL 相关子查询

聊一聊 MySQL 相关子查询

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

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

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

正文

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

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

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

通过 explain 查看这两种 SQL 的执行计划，子查询的 type、ref 列可能一样，也可能不一样，难免让人困惑。

我们先来弄清楚两种 SQL 的 explain 结果中，子查询的 type、ref 列为什么会显示不一样？

示例 SQL 1：

-- 为了保证 EXISTS 子查询不会转换为半连接-- 先把半连接优化关闭SET optimizer_switch="semijoin=off";-- 纯手工打造的相关子查询EXPLAIN SELECT * FROM cityWHERE 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 | | 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 cityWHERE 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 | | 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 是怎么来的：

上面代码是 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.hclass 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 traceSET optimizer_trace = "enabled=on";-- 执行 select 语句SELECT * FROM cityWHERE 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_valueFROM mysql.server_costWHERE cost_name = 'memory_temptable_row_cost'+---------------------------+------------+---------------+| cost_name | cost_value | default_value |+---------------------------+------------+---------------+| memory_temptable_row_cost | | 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 cityWHERE 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 cityWHERE 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 cityWHERE 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 addressWHERE 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 可以看到两种执行策略的成本。简单介绍了相关子查询的执行流程。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。