MySQL 子查询优化

MySQL 子查询优化

发布时间:2022-04-28 浏览次数:3612

有这么一个 SQL,外查询 where 子句的 bizCustomerIncoming_id 字段,和子查询 where 字句的 cid 字段都有高效索引,为什么这个 SQL 执行的非常慢,需要全表扫描?

image

我们从这么一个问题来引入接下来的内容,如果你知道答案就不用继续看下去了。

子查询优化策略

1. 对于 IN、=ANY 子查询,优化器有如下策略选择:

semijoin

Materializationexists

2. 对于 NOT IN、<>ALL 子查询,优化器有如下策略选择:

Materialization

exists

3. 对于 derived 派生表,优化器有如下策略选择:

derived_merge,将派生表合并到外部查询中(5.7 引入 );

将派生表物化为内部临时表,再用于外部查询。

注意:update 和 delete 语句中子查询不能使用 semijoin、materialization 优化策略

优化思路

为方便分析,先建两张表:

image
image
image

有以下子查询示例:

image
image
image

不相关子查询变成了关联子查询(select_type:DEPENDENT SUBQUERY),子查询需要根据 b 来关联外表 t1,因为需要外表的 t1 字段,所以子查询是没法先执行的。执行流程为:

1. 扫描 t1,从 t1 取出一行数据 R;

2. 从数据行 R 中,取出字段 a 执行子查询,如果得到结果为 TRUE,则把这行数据 R 放到结果集;

3. 重复 1、2 直到结束。

这样会有个问题,如果外层表是一个非常大的表,对于外层查询的每一行,子查询都得执行一次,这个查询的性能会非常差。我们很容易想到将其改写成 join 来提升效率:

image

这样优化可以让 t2 表做驱动表,t1 表关联字段有索引,查找效率非常高。

这是 MySQL 5.6 加入的新特性,MySQL 5.6 以前优化器只有 exists 一种策略来“优化”子查询。经过 semijoin 优化后的 SQL 和执行计划分为:

image
image

semijoin 优化实现比较复杂,其中又分 FirstMatch、Materialize 等策略,上面的执行计划中 select_type=MATERIALIZED 就是代表使用了 Materialize 策略来实现的 semijoin,后面有专门的文章介绍 semijoin,这里不展开。这里 semijoin 优化后的执行流程为:

1. 先执行子查询,把结果保存到一个临时表中,这个临时表有个主键用来去重;

2. 从临时表中取出一行数据 R;

3. 从数据行 R 中,取出字段 b 到被驱动表 t1 中去查找,满足条件则放到结果集;

4. 重复执行 2、3,直到结束。

这样一来,子查询结果有 9 行,即临时表也有 9 行(这里没有重复值),总的扫描行数为 9+9+9*1=27 行,比原来的 1000 行少了很多。

Materialization

image

总扫描行数为 100+9=109。

semijoin 和 materialization 的开启是通过 optimizer_switch 参数中的 semijoin={on|off}、materialization={on|off} 标志来控制的。上文中不同的执行计划就是对 semijoin 和 materialization 进行开/关产生的。特意考古找了下 MySQL 5.5 的官方手册,优化策略相当稀少

总的来说对于子查询,先检查是否满足各种优化策略的条件(比如子查询中有 union 则无法使用 semijoin 优化),然后优化器会按成本进行选择,实在没得选就会用 exists 策略来“优化”子查询,exists 策略是没有参数来开启或者关闭的。

总的来说对于子查询,先检查是否满足各种优化策略的条件(比如子查询中有 union 则无法使用 semijoin 优化),然后优化器会按成本进行选择,实在没得选就会用 exists 策略来“优化”子查询,exists 策略是没有参数来开启或者关闭的。

小结

回到开篇的问题,答案是:delete 无法使用 semijoin、materialization 优化策略,会以 exists 方式执行,外查询即 delete biz_customer_incoming_path 表时必须要进行全表扫描。优化的方法也很简单,改成 join 即可(这里是 delete,不用担心重复行问题):

image

参考资料

1. https://dev.mysql.com/doc/refman/5.7/en/subquery-optimization.html2. 《高性能 MySQL》第 6.5.1 章节


相关推荐

产品试用 产品试用
400-820-6580 免费电话