一、问题复现
今天同事问我一个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 (`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’) */