时间戳

首页 > 解决方案 > MySQL 优化器 - 成本规划器不知道 DuplicateWeedout 策略何时创建磁盘表

mysql - MySQL 优化器 - 成本规划器不知道 DuplicateWeedout 策略何时创建磁盘表

问题描述

这是我的示例查询

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. DUPLICATE-WEEDOUT 策略 - 成本：1.08e7
  2. FIRST MATCH 策略 - 成本：1.85e7

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

join_optimization 部分似乎一切都很好。但最后，在检查了 join_execution 部分之后。DUPLICATE-WEEDOUT 通常创建临时表。但是这里由于临时表的堆大小不够，它继续创建ondisk临时表（converting_tmp_table_to_ondisk）。

由于磁盘临时表，我的查询执行变得更慢。

那么这里发生了什么？

优化器跟踪不计算连接优化部分本身的磁盘表成本。如果计算磁盘表成本，它将高于第一次匹配。然后 final_semijoin_strategy 将是 FIRST-MAT​​CH 策略，这样我的查询会更快。

MYSQL 有没有办法计算连接优化部分本身的磁盘表成本或针对此特定问题的任何其他解决方法？

MYSQ-5.7，INNODB

注意：这是一个非常动态的查询，其中将根据查询中的请求添加多个条件。所以我已经以所有可能的方式优化了查询。最后坚持这个磁盘表成本问题。请避免优化查询（例如更改查询结构，强制首次匹配策略）。并且为了增加堆大小（我不太确定，在不同的论坛上很多人说它可能会在其他查询中带来不同的问题）

标签： mysqlsqlinnodbsql-execution-plan

解决方案

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

如前所述，查询可能等同于

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

这将很好地优化。

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

推荐阅读