MySQL 优化器 - 成本规划器不知道重复清除策略何时创建磁盘表

Question

这是我的示例查询

Select table1.id 
from table1 
where table.id in (select table2.id 
from table2 
where table2.id in (select table3.id 
from table3)
) 
order by table1.id  
limit 100

在检查上述查询的优化器跟踪时。优化跟踪成本

1. 双重收益策略-成本：1.08e7
2. 第一场比赛策略-成本：1.85e7

由于DUPLICATE-WEEDOUT成本较低，mysql对上述查询采取了DUPLICATE-WEEDOUT策略。

在加入优化部分似乎一切都很好。但最后，在检查了join_execution部分之后。DUPLICATE-WEEDOUT通常创建临时表。但在这里，由于堆大小不足以容纳临时表，它继续创建ondisk临时表(conversing_tmp_table_to_ondisk(。

由于磁盘临时表的原因，我的查询执行速度变慢了。

---

这里发生了什么？

优化程序跟踪不计算联接优化部分本身中磁盘表的成本。若计算磁盘表成本，它将高于第一次匹配。那么final_semijin_strategy将是FIRST-MATCH策略，这样我的查询会更快。

对于这个特定的问题，MYSQL是否有任何方法来计算联接优化部分本身或任何其他解决方案中磁盘表的成本

MYSQ-5.7，INNODB

---

注意：这是一个非常动态的查询，其中将根据查询中的请求添加多个条件。因此，我已经以各种可能的方式对查询进行了优化。最后解决了磁盘表的成本问题。请避免优化查询(如更改查询结构、强制第一匹配策略(。对于增加堆大小(我不太确定，在不同的论坛上，许多人说这可能会在其他查询中带来不同的问题(

Answer 1

IN( SELECT ... )一直是出了名的效率低下。尽量避免。

如图所示，该查询可能相当于

SELECT  t1.id
FROM  t1
JOIN  t2 USING(id)
JOIN  t3 USING(id)
ORDER BY  id
LIMIT  100

这将很好地优化。

这个公式不需要构建任何临时表，更不用说基于磁盘的临时表了。