一、問題復現
今天同事問我一個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’) */
