一、问题复现
今天同事问我一个SQL问题：
相同的SQL语句传入两个不同的参数； 一个参数语句执行很快，一个参数执行很慢。
相同的语句，不同的参数。一个是0.078s 一个上1.422S 性能相差 18倍
二、分析问题
第一个习惯 看看执行计划。
看快的参数的语句：
如图：
再来看看慢的参数的语句
通过执行计划 可以看到两者的区别了。
慢的参数。子查询 全是 DEPENDENT SUBQUERY。
快的参数。子查询全是 UNCACHEABLE SUBQUERY。
关于 DEPENDENT SUBQUERY 与 UNCACHEABLE SUBQUERY 的官方说明
我对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后查看执行计划
发现 dependent subquery消失了。
再看一下执行的时间
相同参数，相同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’) */
