MySQL优化生产实践-MySQL 优化器负优化产生超慢查询(二)，优化后性能提升 252倍

今天收到研发发来一条SQL, Join一张空表后就需要11S，当去掉一张空表的join 速度快得飞起。只需要0.047S 开发让我找找原因。

SQL如下

explain #analyze
SELECT a.*
,d1.*,d2.*
FROM goods_stock_inout_deal a
LEFT JOIN deal d1 ON a.deal1id = d1.dealid 
LEFT JOIN deal d2 ON a.deal2id = d2.dealid
WHERE  a.billdate >= '2025-01-01' 
AND a.billdate < '2025-08-30'
AND a.profileid=147 AND  a.ifred = 0
ORDER BY a.billdate, a.id
LIMIT 0,19

deal是空表

执行计划如下：

执行上述SQL需要11.859秒

通过计划看到join deal表时 MySQL选择Hash join

当join空表时 MySQL总是会选择Hash join 因为空表 hash join 构建表 Build Table的成本为0

但hash join有一个缺陷 不能保持驱动表的索引顺序。

此SQL 即有一个特点，索引顺序即是最终的排序顺序。而NLJ可以保持驱动表的索引顺序的。所以我将SQL进行了改造，强制让SQL走NLJ

强制走NLJ下面三种

1、force index

explain 
SELECT 
a.*
,d1.*,d2.*
FROM goods_stock_inout_deal a
LEFT JOIN deal d1 force index(pri) ON a.deal1id = d1.dealid 
LEFT JOIN deal d2  force index(pri) ON a.deal2id = d2.dealid
WHERE  a.billdate >= '2025-01-01' 
AND a.billdate < '2025-08-30'
AND a.profileid=147 AND  a.ifred = 0 
ORDER BY a.billdate, a.id
LIMIT 0,19

2、NO_BNL

explain 
SELECT 
/*+ NO_BNL()*/
a.*
,d1.*,d2.*
FROM goods_stock_inout_deal a
LEFT JOIN deal d1 ON a.deal1id = d1.dealid 
LEFT JOIN deal d2  ON a.deal2id = d2.dealid
WHERE  a.billdate >= '2025-01-01' 
AND a.billdate < '2025-08-30'
AND a.profileid=147 AND  a.ifred = 0 
ORDER BY a.billdate, a.id
LIMIT 0,19

3、block_nested_lop=off

explain 
SELECT 
/*+ SET_VAR(optimizer_switch='block_nested_loop=off') */ 
a.*
,d1.*,d2.*
FROM goods_stock_inout_deal a
LEFT JOIN deal d1 ON a.deal1id = d1.dealid 
LEFT JOIN deal d2  ON a.deal2id = d2.dealid
WHERE  a.billdate >= '2025-01-01' 
AND a.billdate < '2025-08-30'
AND a.profileid= 147 AND  a.ifred = 0  
ORDER BY a.billdate, a.id
LIMIT 0,19

执行计划都如下：

通过执行计划已能看到即消除了排序，扫描行数也大幅减少
当此SQL走NLJ时，执行该SQL 只需要0.047S ,性能提升252倍

如果关联列上都有索引，需要关联Hash join 我建议是使用force index 方式更好
第三种是语句级的。如果多个join均不会走hash
第二种是官方在不同版本下hint又不一样，比较麻烦

HASH_JOIN, NO_HASH_JOIN: In MySQL 8.0.18 only, enable or disable use of a hash join for the specified tables. These hints have no effect in MySQL 8.0.19 or later, where you should use BNL or NO_BNL instead.

第一种即灵活控制到具体表，还能保证走不了hash join就走NLJ 不会造成重大的性能损失。

通过这个案例可以看出MySQL的CBO(基于成本优化)还是有进步空间。
更重要的是我们需要掌握更多MySQL底层原理，拿到慢SQL才有很好的优化方向，并加以验证。