MySQL Where条件写重复导致的慢查询

原创 chengang 2025-06-11

341

1、问题复现

开发同学发来一个SQL,说不能理解为什么执行计划选择全表扫描，驱动表也选择错误，原始SQL更复杂，我将SQL简化后如下

explain 
select * from  erp_bill_sale ebi 
inner join erp_bill_index  outbill  ON outbill.orderbillid=ebi.billid AND outbill.billtype=601 AND outbill.ifred=0 AND ebi.profileid=outbill.profileid   and outbill.status in(20,28) 
where ebi.profileid=200006732 AND ebi.billtype IN (604) and ebi.billdate>='2025-05-26' and ebi.billdate<'2025-06-01'
and outbill.status in(20,28)

通过执行计划可以看到 MySQL选用了outbill做驱动表，而且是全表扫描

我写个hint强制用ebi做驱动表看看计划

explain 
select 
/*+ JOIN_PREFIX(EBI) */
* from  erp_bill_sale ebi 
inner join erp_bill_index  outbill  ON outbill.orderbillid=ebi.billid AND outbill.billtype=601 AND outbill.ifred=0 AND ebi.profileid=outbill.profileid   and outbill.status in(20,28) 
where ebi.profileid=200006732 AND ebi.billtype IN (604) and ebi.billdate>='2025-05-26' and ebi.billdate<'2025-06-01'
and outbill.status in(20,28)

真正的执行时间也相差100倍左右，outbill做驱动表需要60多秒，而ebi做驱动表只需要0.63秒

这里明显该用ebi做驱动表，但为什么MySQL选用了outbill呢？
我们慢慢的来找原因。

2、MySQL优化器改进建议

以下的知识点最终会影响上面执行计划的错误原因

MySQL官网有对where子句优化的说明
常见的有常数条件去除/常量折叠,删除不必要的括号等
在官网有现成的例子
比如有 where (a<b AND b=c) AND a=5 这样的条件，会优化成where b>5 AND b=c AND a=5
where (b>=5 AND b=5) OR (b=6 AND 5=5) OR (b=7 AND 5=6) => where b=5 OR b=6
详见官网

即MySQL通过优化器或准备阶段，会改写你的SQL,你可以通过show warnings 来查看优化器改写后的SQL

而在上面语句中有两个完全重复的条件即outbill.status in(20,28) 写了两次在inner join 的on 条件写了一次，又在where条件处又写了一次。正因为多写了一次改变了整个查询计划

我们单独将Outbill表单独摘出来查询一次

explain
select * from  erp_bill_index outbill
where outbill.profileid=200006732 AND outbill.billtype IN (601) AND outbill.ifred=0   and outbill.status in(20,28) and   outbill.status in(20,28) ;

通过执行计划得知该查询计划的filtered = 0.02

我们去掉一个多余的 and outbill.status in(20,28) 再来观察执行计划的filtered值

explain
select * from  erp_bill_index outbill
where outbill.profileid=200006732 AND outbill.billtype IN (601) AND outbill.ifred=0   and outbill.status in(20,28) -- and   outbill.status in(20,28) ;

通过执行计划可以看到filtered的值由0.02变成了0.1

但由于是完全重复的多余条件，实际不会对结果产生影响，但MySQL估算filtered还是变化了，当是等值条件写重复的时候，MySQL优化器会直接干掉重复的条件
举一个例子

explain
select 1 from  erp_bill_index outbill
where outbill.profileid=200006732   and outbill.billtype= 601 AND   outbill.billtype= 601

通过show warnings 可以看到
语句变成了以下

/* select#1 */ select 1 AS `1` from `erp_ql_539`.`erp_bill_index` `outbill` where ((`erp_ql_539`.`outbill`.`billtype` = 601) and (`erp_ql_539`.`outbill`.`profileid` = 200006732))

重复的outbill.billtype = 601 被消除掉了，所以无论如何重复写都不会让执行计划的filtered变化

explain
select 1 from  erp_bill_index outbill
where outbill.profileid=200006732   and outbill.ifred in (0,1)   and outbill.ifred in (0,1)

通过show warnings 可以看到
语句变成了以下

/* select#1 */ select 1 AS `1` from `erp_ql_539`.`erp_bill_index` `outbill` where ((`erp_ql_539`.`outbill`.`profileid` = 200006732) and (`erp_ql_539`.`outbill`.`ifred` in (0,1)) and (`erp_ql_539`.`outbill`.`ifred` in (0,1)))

重复的and outbill.ifred in (0,1) 没有被消除

建议MySQL可以优化一下。重复的in条件也可以消除，不要影响filtered值

因为filtered 会影响多表关联驱动表的选择
参见官网详细文档

3、问题解决

回到此问题本身上来

explain 
select * from  erp_bill_sale ebi 
inner join erp_bill_index  outbill  ON outbill.orderbillid=ebi.billid AND outbill.billtype=601 AND outbill.ifred=0 AND ebi.profileid=outbill.profileid   and outbill.status in(20,28) 
where ebi.profileid=200006732 AND ebi.billtype IN (604) and ebi.billdate>='2025-05-26' and ebi.billdate<'2025-06-01'
-- and outbill.status in(20,28)

通过执行计划可以看到只要注释掉多余的and outbill.status in(20,28) 即走了正确的执行计划

如果你认真看了第2点链接中官网对filtered的详细介绍，你可能已经知晓答案了
即因为多了一个in条件，导致 filtered很小了，MySQL认为，表Outbill通过filtered剩下的行将很少了，更适合做驱动表。刚好多一个 in 与少一个in 达到了驱动表评估的临界值。

我们可以通过优化器开关来确认这个推测

explain 
select 
/*+ set_var(optimizer_switch='condition_fanout_filter=off') */
* from  erp_bill_sale ebi 
inner join erp_bill_index  outbill  ON outbill.orderbillid=ebi.billid AND outbill.billtype=601 AND outbill.ifred=0 AND ebi.profileid=outbill.profileid   and outbill.status in(20,28) 
where ebi.profileid=200006732 AND ebi.billtype IN (604) and ebi.billdate>='2025-05-26' and ebi.billdate<'2025-06-01'
and outbill.status in(20,28)