MySQL优化-生产实践，飘忽不定的Backward index scan性能优化，性能提升130倍

1、问题复现

生产环境上产生一个慢SQL,我优化后时快，时慢。通过跟踪执行计划发现，时快时慢的原因就是走Backward index scan不稳定，一会儿能走，一会儿不能走。我深入去了解，写下此篇文章以做记录。
慢SQL如下：

explain
select  a.billdetailid ,ebi.xx,detail.xx,package.xx
....
from a 
LEFT JOIN EBI  ON ebi.billid=a.billid 
LEFT JOIN detail   ON detail.`billdetailid`=a.`billdetailid` 
LEFT JOIN package on package.billdetailid=a.billdetailid
LEFT JOIN extei ON extei.billid=EBI.billid  
-- ...还有几个left join
where a.profileid=10001 and a.billtype in( 604)
AND a.billdate>='2025-10-22' AND a.billdate<'2025-12-29' 
ORDER BY a.profileid desc,a.billtype desc,a.billdate desc,a.billcode desc  ,a.billdetailid  asc
limit 0,20;

执行计划如下

执行时间需要 13S

2、第一次优化 延迟关联。

从执行计划可以看出 a表与其它表的join关系全是一对一的。 那么完全可以，先limit 20再去做关联，那后面关联的虚拟表的行数就从 816252 降到 20行
改造后的SQL如下：

select a.billdetailid ,ebi.xx,detail.xx,package.xx
from(select  a.billdetailid from erp_bill_sale a 
	where a.profileid=10001 and a.billtype in( 604)
	AND a.billdate>='2025-10-22' AND a.billdate<'2025-12-29' 
	ORDER BY a.profileid desc,a.billtype desc,a.billdate desc,a.billcode desc  ,a.billdetailid  asc
	limit 0,20) as a 
LEFT JOIN EBI  ON ebi.billid=a.billid
LEFT JOIN detail  ON detail.`billdetailid`=a.`billdetailid` 
LEFT JOIN package on package.billdetailid=a.billdetailid
LEFT JOIN extei ON extei.billid=EBI.billid AND extei.profileid=EBI.profileid 

执行计划如下，可以看见参与后面join的虚表已降到了20行了

执行时间也从 13s降到了3秒

但我为了演示只查了2个月数据，客户是可以查2年以上时长数据的。所以实际情况更糟糕，还需要继续优化

3、第二次优化 建索引

这个SQL要求降序排序，所以只能建立降序索引，但业务数据可是按日期正序写入的，如果建立倒序索引，那么新数据总是插在倒序索引B+树的最左端，应该会有较大的页分裂情况存在，为了不影响生产环境写入受影响（以前吃过亏，建了降序索引后，在大数据导入的时候有明显的性能下降，查询原因后就是降序索引引发，当时的方案是删除降序索引，导入大数据后，再重新添加解决）

MySQL支持反向扫描，所以我就建了和order by 完全相反的索引，想着即不影响写入，又能提升性能。
建立的反向扫描索引如下【KEY ix_profileid_billtype_billdate_billcode_billdetailid (profileid,billtype,billdate,billcode,billdetailid DESC)】此索引排序是和业务写入一致，无性能风险，和order by 完全相反，符合反向扫描规则。

当时在测试环境测试是可以走Backward index scan，上了生产后，发现偶尔能走，偶尔不能走，关联表的多少也会时走时不走，范围查询改变时走时不走。我试了很多方法，都没有找到必然走和必然不走的原因。

查询各种文档发现一个工作日志
https://dev.mysql.com/worklog/task/?id=1074
在工作日志中找到下面这段话，说的是有混合排序的索引在有范围查询的时候是不完全支持的。我实际测试，即使全是等值，也有可能不走 Backward index scan

段落引用
*) Backward range scans over indexes with mixed ASC/DESC key parts won’t be
fully supported as they might require tricky reordering of ranges in order to
return record in proper order.

3、第三次优化 根据业务特性用巧妙方法实现

经过多次实验发现。不要最后一个a.billdetailid asc 是一定能走Backward index scan的。因为除了最后一个asc后，order by就只有 desc一个方向的排序了。不存在混合排序了。

改造后的SQL如下：

explain
select  a.billdetailid ,ebi.xx,detail.xx,package.xx
....
from a 
LEFT JOIN EBI  ON ebi.billid=a.billid 
LEFT JOIN detail   ON detail.`billdetailid`=a.`billdetailid` 
LEFT JOIN package on package.billdetailid=a.billdetailid
LEFT JOIN extei ON extei.billid=EBI.billid  
-- ...还有几个left join
where a.profileid=10001 and a.billtype in( 604)
AND a.billdate>='2025-10-22' AND a.billdate<'2025-12-29' 
ORDER BY a.profileid desc,a.billtype desc,a.billdate desc,a.billcode desc  
limit 0,20;

执行计划

期待已久的Backward index scan 出现了。无论查几年的数据，查询时间都稳定在0.1s内，而且由于billdetailid是desc 反向扫报描也是升序排序，即符合需求，也有非常好的性能，完美！！！

如果你在生产优化到此就结束了，恭喜你，这时还有一个大坑等着你。

这个坑就是这个

If multiple rows have identical values in the ORDER BY columns, the server is free to return those rows in any order, and may do so differently depending on the overall execution plan. In other words, the sort order of those rows is nondeterministic with respect to the nonordered columns.

具体详细可参见官方文档
https://dev.mysql.com/doc/refman/9.7/en/limit-optimization.html

这段话的意思就是：如果多行在ORDER BY列中的值相同，则服务器可以按任意顺序返回这些行，并且根据整体执行计划的不同，返回顺序也可能不同。如果排序key不唯一，那么返回的结果是不确定的。带来的后果就是翻页数据发现变化，还有可能翻页发现重复数据等重大BUG.

到这里陷入死局：不加a.billdetailid asc会造成返回结果不确定性，加a.billdetailid asc 但会引发 Backward index scan 反向扫描不稳定， 如果增加物理降序索引，又会导致写性能下降。

陷入死局后，当时实在没有想到办法，就放在那里了，但这个事儿就一直挂在心中去不了，今天又来想着怎么优化，发现了曙光。

现在能确定去掉a.billdetailid asc 能稳定的走 Backward index scan，且查询多少时间输出都稳定在0.1S内，唯一的缺陷是输出是不确定。现在要解决的是输出确定，解决了输出确定，问题就解决了。

只要前四个排序ORDER BY a.profileid desc,a.billtype desc,a.billdate desc,a.billcode desc 能稳定输出前20，那么整个输出就稳定了。通过业务发现满足前四个排序。即同一租户，同一个单据类型，同一天，同一张单据。这里面最小维度是同一张单据，我们业务中，同一张单张是不可能超过2000行的。 那我只要取2000后，再来取20行即可。

改造SQL如下：

explain
select a.billdetailid ,ebi.XX,detail.XX,package.XX
from(
    select  t.billdetailid,t.profileid,t.billid from
    (
    select  a.billdetailid,a.profileid,a.billid,a.billtype,a.billdate,a.billcode from a 
	where a.profileid=10001 and a.billtype in( 604)
	AND a.billdate>='2025-10-22' AND a.billdate<'2025-12-29' 
	ORDER BY a.profileid desc,a.billtype desc,a.billdate desc,a.billcode desc limit 2000
	) as t
     ORDER BY t.profileid desc,t.billtype desc,t.billdate desc,t.billcode desc ,t.billdetailid asc   limit 20 )as a
LEFT JOIN EBI  ON ebi.billid=a.billid 
LEFT JOIN detail   detail.`billdetailid`=a.`billdetailid` 
LEFT JOIN  package on  package.billdetailid=a.billdetailid
LEFT JOIN extei ON extei.billid=EBI.billid 

执行计划如下

可以看到可以稳定的走Backward index scan，执行时间也稳定在0.1S以内，且不管查询时间多少长。