explain
SELECT
COUNT(*)
FROM erp_bill_index bi
INNER JOIN erp_bill_finance_voucher a ON bi.`billid`=a.`billid` AND bi.profileid=a.profileid AND bi.billtype=905
WHERE a.profileid=500004255 AND a.billdate >= '2023-11-15' AND bi.billdate >= '2023-11-15'
AND a.billdate < '2024-01-16' AND bi.billdate < '2024-01-16' AND a.ifred = 0
bi表有索引profileid_billdate,表a在where条件中的字段上是没有索引的,因此在写这个sql的时候预想的是以bi表为驱动表执行,按常理来说sql执行起来是不会慢的,但实际上却遇到了执行很慢的情况;打开执行计划如下(下文以A执行计划指代):
选择的是a表作为驱动表,并且是全表扫描;
然后我们强制选择bi表为驱动表,执行计划如下(下文以B执行计划指代):
忍不住吐槽,优化器咋这么傻?
小表驱动大表性能是优于大表驱动小表的,假设优化器是遵循这个原则的,也就是说优化器认定bi表是更小的表,更适合用做驱动表?
因此像大佬请教,也查阅了部分资料、文章,了解到优化器默认情况下考虑的是引擎级别过滤乘以server层的过滤,可根据explain中的rows*filter/100来估算最终行数;
A计划中1012722*0.56/100=5671
B计划中35916*100/100=35916
因此认定A计划执行代价更小,所以选择了计划A,但是a表无索引,全表扫描导致更慢了。
因此这个sql优化方式两种:
1、通用hint指定bi表为驱动表(join_prefix);
2、a表增加索引profileid_billdate;
有兴趣的可更深入的可了解 MySQL optimizer_switch参数之condition_fanout_filter;