MySQL中IN写法的一些改写技巧

聊聊慢SQL中关于IN语法的优化分析过程。


技术人人都可以磨炼,但处理问题的思路和角度各有不同,希望这篇文章可以抛砖引玉。
以一个例子为切入点



一、问题背景


某业务模块反馈SQL慢,优化过程中的一些思考做个记录。
基础环境:

  • 主机类型:阿里云 
  • 操作系统:CentOS release 7.4
  • 存储:Alibaba Cloud ECS    
  • 内存:64 G
  • CPU型号:Intel(R) Xeon(R) Platinum 8163 CPU @ 2.50GHz ( 1 U * 8 core) 
  • CPU核数:16CORE
  • 数据库环境:MySQL5.7.27
  • 存储引擎:InnoDB


问题现象:慢SQL


简单说明:

在很多应用场景中,SQL 的性能直接决定了系统的性能。此外,查询速度慢并不只是因为 SQL 语句本身,还可能是因为内存分配不佳、文件结构不合理、刷脏页等其他原因。
本文介绍一些通过调整 SQL 语句就能优化SQL的通用小技巧,优化 SQL 的方法不能解决所有的性能问题,但是却能处理很多因 SQL 写法不合理而产生的性能问题。


二、分析说明

  • 通过分析慢日志定位慢SQL,分析慢SQL原因;
  • 追溯SQL执行历史数据,分析关键指标在SQL多次执行的波动,这些关键指标可以用来做为SQL健康度参考指标。
  • 用实际数据来验证推断,排除掉其它干扰因素,定位SQL慢的根本原因,帮助快速修复。


三、疑问点排查及分析思路


原SQL结构如下:

SELECT * FROM tab_a  WHERE ID IN (SELECT c_id FROM tab_b);


业务需求我看了一下,还真不能怪开发小哥这么写,我第一反应也是这么写,用IN的好处就是SQL比较直观,容易理解SQL逻辑。

1、IN语法的SQL执行计划

SQL如下:

SELECT * FROM tab_a  WHERE ID IN (SELECT c_id FROM tab_b);


执行计划如下:

就这个SQL的执行计划本身来说还是不错的(MySQL查询转换做的不错),我想说的主要关注点在FirstMatch(tab_a)上。


我们看到上面查询计划中,extra列可以看到 FirstMatch(tab_a) 。MySQL使用了连接来处理此查询,对于tab_a表的行,只要能在tab_b表中找到1条满足即可以不必再检索tab_a表。从语义角度来看,和IN-to-EXIST策略转换为Exist子句是相似的,区别就是FirstMath以连接形式执行查询,而不是子查询。


FirstMatch策略背后的思想和in->exists转换思想相同。FirstMatch有以下的优点:

  • 等价传播可以跨越semi-join边界,但是不能跨越子查询边界。因此,使用FirstMatch将子查询转换成semi-join可以提供一个更好的执行计划;
  • 只有一种方式使用in->exists策略,mysql会无条件地使用。对于FirstMatch策略,优化器可以选择是否应该在子查询中使用的所有表都位于join前缀时运行FirstMatch策略,或者在稍后的某个时间点运行FirstMatch策略;


FirstMatch策略意味着子查询的表必须在父查询中的表之后被引用,FirstMatch支持相关子查询;不能应用于子查询带有group by或聚合函数的场景。


PS:是否开启FirstMatch是由系统变量optimizer_switch中的firstmatch=on|off设置的。


2、exists语法的SQL执行计划

SQL如下:

SELECT a.* FROM tab_a  aWHERE exists (SELECT c_id FROM tab_b b where a.id=b.c_id);


执行计划如下:


通常来讲,EXISTS 比 IN 更快的原因有两个:

  • 如果连接列(cr_id)上建立了索引,那么查询 tab 时可以通过索引查询,而不是全表查询;
  • 使用 exists,一旦查到一行数据满足条件就会终止查询,不用像使用 IN 时一样进行扫描全表(NOT EXISTS 也一样)


针对某一个查询,有时候会有多种 SQL 实现,例如 IN、EXISTS、连接之间的互相转换。从理论上来讲,得到相同结果的不同 SQL 语句应该有相同的性能,但遗憾的是,查询优化器生成的执行计划很大程度上要受到外部结构的影响。

因此,如果想优化查询性能,必须知道如何写 SQL 语句才能使优化器生成更高效的执行计划。


3、使用exists代替IN是否更好?


如果 IN 的参数是 1,2,3 这样的数值列表,一般还不需要特别注意,但如果参数是子查询,那么就需要注意了;在大多时候, [NOT] IN 和 [NOT] EXISTS 返回的结果是相同的,但是两者用于子查询时,EXISTS 的速度会更快一些。
当 IN 的参数是子查询时,数据库有可能首先会执行子查询(上述分析案例不是),然后将结果存储在一张临时表里(内联视图),然后扫描整个视图,很多情况下这种做法非常耗费资源。而使用 EXISTS 的话,数据库不会生成临时表。

减少临时表也是在 SQL优化中需要注意的点,子查询的结果会被看成一张新表(临时表),这张新表与原始表一样,可以通过 SQL 进行操作。但是频繁使用临时表会带来两个问题:

  • 临时表相当于原表数据的一份备份,会耗费内存资源;
  • 很多时候(特别是聚合时),临时表没有继承原表的索引结构。 

因此,尽量减少临时表的使用也是提升性能的一个重要方法。

4、其他代替IN的方案

其实在平时工作当中,更多的是用连接代替 IN 来改善查询性能,而非 EXISTS,不是说连接更好,而是 EXISTS 很难掌握(SQL逻辑不够直白)。
刚刚的SQL,如果用连接来实现,如何写?
SQL如下:

SELECT distinct a.* FROM tab_b b left join tab_a a on b.c_id=a.id


执行计划如下:


这种写法能充分利用索引;而且因为没有了子查询,所以数据库也不会生成中间表;所以,查询效率还是不错的。至于 JOIN 与 EXISTS 相比哪个性能更好,不太好说;如果没有索引,可能 EXISTS 会略胜一筹,有索引的话,两者差不多。
执行计划里需要注意的一个点是Using temporary, 表示进行了排序或分组,显然这个 SQL 没有进行分组,而是进行了排序运算。

为了排除重复数据, DISTINCT 也会进行排序,而排序操作一般是要避免的,怎么避免?

5、使用 EXISTS 代替 DISTINCT


还是刚刚的SQL,如果不用 DISTINCT 过滤数据,怎么写?

用 EXISTS 来进行优化


可以看到,已经规避了排序运算。

总结

文中虽然列举了几个要点,但其实优化的核心思想只有一个,那就是找出性能瓶颈所在,然后解决它。不管是减少排序还是使用索引,亦或是避免临时表的使用,其本质都是为了减少对硬盘的访问。
小技巧:

  • 参数是子查询时,使用 EXISTS 或者 JOIN 代替 IN;
  • 在 SQL 中,很多运算都会暗中进行排序,尽量规避这些运算;
  • SQL 的书写,尽量往索引上靠,避免用不上索引的情况;
  • 尽量减少使用临时表。

觉得本文有用,请转发、点赞或点击“在看”聚焦技术与人文,分享干货,共同成长更多内容请关注“数据与人”

为您推荐

发表评论

您的电子邮箱地址不会被公开。