ORDER BY LIMIT 的“南辕北辙”：为什么MySQL选错了路？

故障现场：凌晨的慢SQL告警

DBdoctor某电商客户，在使用DBdoctor接管业务MySQL库后，近期凌晨频繁有慢SQL告警发出，执行时间达到40多秒。

数据库管理人员次日使用DBdoctor分析凌晨的问题，发现问题发生期间的慢SQL还导致了服务器IO的升高，随后他使用DBdoctor的根因诊断功能发现了造成这一问题的根因SQL，但是当他尝试执行SQL复现问题时，发现SQL执行很快，这条SQL并没有出现凌晨执行耗时长达30-40S的场景，客户带着疑问找到了我们。

SELECT        *FROM        xxxxxWHERE        pay_info_id > 196341287        AND ( (entrustpay IS NULL                AND trade_status = '0'                AND created_date < NOW() - INTERVAL 3 MONTH)        OR (entrustpay IS NULL                AND trade_status > '0'                AND created_date < NOW() - INTERVAL 13 MONTH)        OR (entrustpay = '1'                AND trade_status = '0'                AND created_date < NOW() - INTERVAL 3 MONTH)        OR (entrustpay = '1'                AND trade_status > '0'                AND created_date < NOW() - INTERVAL 13 MONTH) )ORDER BY        pay_info_id ASCLIMIT 300
同时从客户那了解到，这条SQL语句是凌晨数据归档的一部分，大致逻辑是：从在线表中查询出符合条件的300条记录，然后写入到归档表中，最后删除在线表中的这300条数据，循环删除，直到时间结束。而且从DBdoctor监控上看在归档任务的前半段时间内，执行效率并没有很慢。

我们来查下实际的执行计划，这里我用了explain analyze功能，实际执行SQL后打印真实执行情况。

可以看到基于主键预估要扫描2千8百万行，但是实际只扫描了1434行，为什么差距这么大，这是MySQL针对Limit的优化策略，通过主键扫描，辅以索引条件下推进行过滤，实际扫描1434行便累计到了300条符合过滤条件的数据可以完成查询。

让我们来思考下MySQL为什么这么做，又会有什么问题。

MySQL优化器的决策完全基于成本计算（Cost-Based Optimization），但其思考过程存在固有的局限性：

对比两者，MySQL“理性”地选择了它认为成本更低的方案B（顺序扫描）顺序I/O成本很低，而这个“命中概率”是整个估算的关键，它严重依赖于两个核心前提：

1.数据的均匀分布性： 优化器默认数据是均匀分布的，它假设只要扫描少量记录就一定能凑够Limit数。

2.统计信息的实时准确性： “命中概率”的计算完全基于表的统计信息（如总行数、索引基数）。

而我们遇到的故障场景，恰恰彻底颠覆了这两个前提：

1.统计信息失真： 频繁的删除操作使实际数据量锐减，但若未及时更新统计信息（ANALYZE TABLE），优化器仍基于旧有的、庞大的数据量进行估算。它以为很快就能扫描到足够的数据，实则偏差巨大。

2.数据分布失衡： 更致命的是，删除操作不是随机的，被删除的数据恰好大量包含符合我们查询条件的数据，就会导致剩余数据中满足条件的记录变得异常稀疏。优化器基于“均匀分布”的假设计算出的“命中概率”完全失效，甚至可能导致顺序扫描了远超limit 的行数后，依然无法凑齐limit所需的记录数，造成灾难性的性能表现。

这里我们建议用户使用DBdoctor的AI-SQL改写功能，分析SQL问题，给出SQL改写建议。

如图所示，DBdoctor AI-SQL 改写功能对问题 SQL 进行了深入分析。其中尤为关键的是第三点——索引能力不足：单个二级索引的索引效率太低，查询涉及的多个字段缺乏合适的联合索引，进而导致使用主键进行扫描与过滤。

在修复建议中，不仅推荐了更优的联合索引方案，还贴心地提供了对应的 DDL 语句。同时，它还识别到原 SQL 中存在OR及范围查询条件，这类结构会阻碍联合索引的有效利用。为此，AI-SQL 建议使用UNION ALL 替代OR，从而提升查询性能。

客户根据AI SQL优化后的监控效果图，相同时间段执行时长从40秒降低至1s左右，IO使用率也有明显下降。

1.当慢SQL遇上AI改写，DBdoctor开启性能逆袭之路！  

1️⃣ 免费下载/在线试用

官网 https://www.dbdoctor.cn/?utm=01

2️⃣ 产品文档

https://demo.dbdoctor.cn/modules/dbDoctor/mdPreview/index.html?readme=help#/