登录
首页 >  数据库 >  MySQL

技术分享 | MySQL 子查询优化

来源:SegmentFault

时间:2023-02-16 15:27:18 287浏览 收藏

知识点掌握了,还需要不断练习才能熟练运用。下面golang学习网给大家带来一个数据库开发实战,手把手教大家学习《技术分享 | MySQL 子查询优化》,在实现功能的过程中也带大家重新温习相关知识点,温故而知新,回头看看说不定又有不一样的感悟!

作者:胡呈清
爱可生 DBA 团队成员,擅长故障分析、性能优化,个人博客:https://www.jianshu.com/u/a95...,欢迎讨论。
本文来源:原创投稿
*爱可生开源社区出品,原创内容未经授权不得随意使用,转载请联系小编并注明来源。

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

delete FROM biz_customer_incoming_path WHERE bizCustomerIncoming_id IN \
(SELECT id FROM biz_customer_incoming WHERE cid='315upfdv34umngfrxxxxxx');

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

子查询优化策略

对于不同类型的子查询,优化器会选择不同的策略。

  1. 对于 IN、=ANY 子查询,优化器有如下策略选择:
  • semijoin
  • Materialization
  • exists
  1. 对于 NOT IN、ALL 子查询,优化器有如下策略选择:
  • Materialization
  • exists
  1. 对于 derived 派生表,优化器有如下策略选择:
  • derived_merge,将派生表合并到外部查询中(5.7 引入 );
  • 将派生表物化为内部临时表,再用于外部查询。

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

优化思路

那么这些策略分别是什么意思?为什么会有这些优化策略?

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

CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`)
) ENGINE=InnoDB;

drop procedure idata;
delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i

有以下子查询示例:

SELECT * FROM t1 WHERE t1.a IN (SELECT t2.b FROM t2 WHERE id 

你肯定认为这个 SQL 会这样执行:

SELECT t2.b FROM t2 WHERE id 

但实际上 MySQL 并不是这样做的。MySQL 会将相关的外层表压到子查询中,优化器认为这样效率更高。也就是说,优化器会将上面的 SQL 改写成这样:

select * from t1 where exists(select b from t2 where id 

执行计划为:

+----+--------------------+-------+-------+---------+------+----------+-------------+
| id | select_type        | table | type  | key     | rows | filtered | Extra       |
+----+--------------------+-------+-------+---------+------+----------+-------------+
|  1 | PRIMARY            | t1    | ALL   | NULL    |  100 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | t2    | range | PRIMARY |    9 |    10.00 | Using where |
+----+--------------------+-------+-------+---------+------+----------+-------------+

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

  1. 扫描 t1,从 t1 取出一行数据 R;
  2. 从数据行 R 中,取出字段 a 执行子查询,如果得到结果为 TRUE,则把这行数据 R 放到结果集;
  3. 重复 1、2 直到结束。

总的扫描行数为 100+100*9=1000(这是理论值,实际值为 964,怎么来的一直没想明白,看规律是子查询结果集每多一行,总扫描行数就会少几行)。

Semi-join

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

select t1.* from t1 join t2 on t1.a=t2.b and t2.id

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

但这里会有个问题,join 是有可能得到重复结果的,而 in(select ...) 子查询语义则不会得到重复值。而 semijoin 正是解决重复值问题的一种特殊联接。在子查询中,优化器可以识别出 in 子句中每组只需要返回一个值,在这种情况下,可以使用 semijoin 来优化子查询,提升查询效率。这是 MySQL 5.6 加入的新特性,MySQL 5.6 以前优化器只有 exists 一种策略来“优化”子查询。经过 semijoin 优化后的 SQL 和执行计划分为:

select 
    `t1`.`id`,`t1`.`a`,`t1`.`b` 
from `t1` semi join `t2` 
where
    ((`t1`.`a` = ``.`b`) 
    and (`t2`.`id`  | ALL   | NULL    | NULL          | NULL | Using where |
|  1 | SIMPLE       | t1          | ref   | a       | .b |    1 | NULL        |
|  2 | MATERIALIZED | t2          | range | PRIMARY | NULL          |    9 | Using where |
+----+--------------+-------------+-------+---------+---------------+------+-------------+

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

MySQL 5.6 版本中加入的另一种优化特性 materialization,就是把子查询结果物化成临时表,然后代入到外查询中进行查找,来加快查询的执行速度。内存临时表包含主键(hash 索引),消除重复行,使表更小。如果子查询结果太大,超过 tmp_table_size 大小,会退化成磁盘临时表。这跟前面提到的“我们误以为的”过程相似,这样子查询只需要执行一次,而不是对于外层查询的每一行都得执行一遍。不过要注意的是,这样外查询依旧无法通过索引快速查找到符合条件的数据,只能通过全表扫描或者全索引扫描,materialization 优化后的执行计划为:

+----+-------------+-------+-------+---------+------+------+-------------+
| id | select_type | table | type  | key     | ref  | rows | Extra       |
+----+-------------+-------+-------+---------+------+------+-------------+
|  1 | PRIMARY     | t1    | ALL   | NULL    | NULL |  100 | Using where |
|  2 | SUBQUERY    | t2    | range | PRIMARY | NULL |    9 | Using where |
+----+-------------+-------+-------+---------+------+------+-------------+

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

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

371711746557132499.jpg

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

小结

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

delete 
    biz_customer_incoming_path 
FROM biz_customer_incoming_path a  join biz_customer_incoming b 
WHERE 
    a.bizCustomerIncoming_id=b.id 
    and b.cid='7Ex46Dz22Fqq6iuPCLPlzQ';

参考资料

1. https://dev.mysql.com/doc/ref...

  1. 《高性能 MySQL》第 6.5.1 章节

文中关于mysql的知识介绍,希望对你的学习有所帮助!若是受益匪浅,那就动动鼠标收藏这篇《技术分享 | MySQL 子查询优化》文章吧,也可关注golang学习网公众号了解相关技术文章。

声明:本文转载于:SegmentFault 如有侵犯,请联系study_golang@163.com删除
相关阅读
更多>
最新阅读
更多>
课程推荐
更多>