暂无图片
暂无图片
3
暂无图片
暂无图片
暂无图片

MySQL 子查询自动优化带来慢SQL问题

原创 aisql 2022-09-07
2289

一、问题复现

今天同事问我一个SQL问题:
相同的SQL语句传入两个不同的参数; 一个参数语句执行很快,一个参数执行很慢。
image.png
image.png

相同的语句,不同的参数。一个是0.078s 一个上1.422S 性能相差 18倍

二、分析问题

第一个习惯 看看执行计划。
看快的参数的语句:
如图:
image.png
再来看看慢的参数的语句
image.png

通过执行计划 可以看到两者的区别了。

慢的参数。子查询 全是 DEPENDENT SUBQUERY。
快的参数。子查询全是 UNCACHEABLE SUBQUERY。

关于 DEPENDENT SUBQUERY 与 UNCACHEABLE SUBQUERY 的官方说明

image.png

我对UNCACHEABLE SUBQUER 不熟悉。但对DEPENDENT SUBQUERY还是挺熟悉的
DEPENDENT SUBQUERY 一般出现在相关子查询中,外部查询有多少行。子查询就会循环执行多少次。

因为是DEPENDENT SUBQUERY 这个执行计划的更慢。所以我把重心放在为什么要走 DEPENDENT SUBQUERY 上。

我又去分析一下语句。
我的子查询内部并没有引用外部表。按理说不该是DEPENDENT SUBQUERY啊。

于是我来看看MySQL优化器改写后的内容是什么。
执行 show warnings;
看到优化器改写后的语句

/* select#1 */ select `i`.`billid` AS `billid` from `userdb0004_bill`.`salebill` `i` where ((`i`.`tid` = (@`tid`)) and ((`i`.`searchtext` like (@`text`)) or <in_optimizer>(`i`.`billid`,<exists>(/* select#2 */ select 1 from `userdb0004_bill`.`goods` `g` join `userdb0004_bill`.`salebilldetail` `d` where ((`g`.`goodsid` = `d`.`goodsid`) and (`g`.`tid` = (@`tid`)) and (`d`.`tid` = (@`tid`)) and (<cache>(`i`.`billid`) = `d`.`billid`) and ((`g`.`goodsname` like (@`text`)) or (`g`.`goodscode` like (@`text`)) or (`g`.`memcode` like (@`text`)) or (`g`.`barcode` like (@`text`))))))))

可以看到优化器把我的in 改写成了 exsists 相关子查询

好的。问题大概知道了。现在我的想法是不让MySQL改写。

查找官方文档。
发现如下一段话:
The subquery_materialization_cost_based flag enables control over the choice between subquery materialization and IN-to-EXISTS subquery transformation. If the flag is on (the default), the optimizer performs a cost-based choice between subquery materialization and IN-to-EXISTS subquery transformation if either method could be used. If the flag is off, the optimizer chooses subquery materialization over IN-to-EXISTS subquery transformation.

这个参数 subquery_materialization_cost_based 会影响 in 转换为 exists?
那接下来验证一下吧。

三、验证

subquery_materialization_cost_based 默认为ON
改为OFF后查看执行计划
image.png
发现 dependent subquery消失了。
再看一下执行的时间

image.png

相同参数,相同SQL 执行语句从1.422S提升到了0.109S

4、结论

subquery_materialization_cost_based 关闭。可以强制优化器不做in与exists之间的自动转换。
但在8.0以前。此参数影响是全局的 不能顺便修改。
但在8.0以后 可以用 set_var 语法来只控制单语句。 /*+ SET_VAR(optimizer_switch = ‘subquery_materialization_cost_based =off’) */

最后修改时间:2022-09-08 14:14:41
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论