从1.13秒到0.01秒：一次索引优化带来的100倍性能提升实战

一次看似简单的 SQL 查询，如何将扫描 2279 万行优化到精准定位，实现 100 倍性能提升？

问题

某天收到数据库告警，提示 CPU 使用率过高。明明前段时间才扩容过，按理说不至于又撑不住。上云平台一查，发现嫌疑 SQL 居然扫描了 2000 万行，而这个表总共才 3000 万行。

image.png

排查

嫌疑 SQL 如下：

SELECT a.amount, a.record_time FROM coin AS a WHERE a.uid=? AND a.record_time>=? AND a.record_time<? AND a.flow_id<? ORDER BY a.flow_id DESC LIMIT ?

我记得这个表同事加过索引，索引如下：

KEY `idxxxx` (`uid`, `record_time`, `flow_id`)

相信细心的同学已经一眼看出问题了。

有些人可能会疑惑，为什么索引顺序没问题，扫描的行数却这么多，感觉索引根本没用上。

我们先用 explain 看一下。

image.png

关键问题出现在 Using filesort ——这个信号说明 MySQL 正在执行昂贵的排序操作。

问题根因

为什么原有索引效率低下？

1. 范围查询中断索引连续性
   • 索引顺序：uid → record_time → flow_id
   • 查询条件：uid=?（等值）→ record_time（范围）→ flow_id<?（范围）
   • 当 record_time 用作范围查询后，flow_id 在索引中变得无序
2. 排序操作无法利用索引
   • ORDER BY flow_id DESC 需要倒序排序，但由于索引中的 flow_id 已无序，MySQL 只能把所有数据收集到内存里排序
3. LIMIT 优化失效
   • 理想情况：按 flow_id 倒序扫描，取前 N 行后立即停止
   • 实际情况：必须先扫描所有数据，排序后再取前 N 行

优化方案

那应该怎么建立索引才合理呢？

答案如下：

KEY idx_uid_flowid_recordtime (uid, flow_id, record_time)

为什么换个顺序就有效了？

1. 完美匹配排序需求
   • 索引以 flow_id 排序，天然支持 ORDER BY flow_id DESC
   • 无需内存排序，直接利用索引的有序性
2. 高效的范围查询处理
   • 先用 uid= 快速定位用户数据
   • 然后按 flow_id 倒序扫描，遇到不满足条件的立即停止
   • 最后用 record_time 进行过滤
3. 提前终止机制
   • 由于索引有序，找到 LIMIT 所需的行数后即可立即停止扫描

来看下优化后的 explain。

image.png

可以看到，Extra 已经是 Using Where，没有了之前的排序操作，效率大大提升。

Extra 的各个阶段：

• Using filesort：需要额外排序 → 性能较差
• Using where：在存储引擎层过滤 → 良好状态
• Using index：覆盖索引，最优状态 → 下一步优化目标

PS：我这边需要查询大量数据，必须回表，所以最多只能优化到 Using Where。把所有字段都放到索引里的成本太高了。

优化后执行时间大约是 14ms，之前监控显示为 1.13s，提升了 80 倍，差不多可以说是提升了 100 倍。

img_v3_02r8_7d64f95a-887f-4b98-b409-bd06a196694g.jpg

知识点

索引设计黄金法则

1. 等值查询字段优先：将等值查询的列放在索引最左侧
2. 排序字段紧跟前缀：ORDER BY 字段应紧跟在等值查询字段后面
3. 范围查询放在最后：避免范围查询中断索引的连续性
4. 考虑覆盖索引：将 SELECT 字段包含在索引中，减少回表操作

查询优化检查清单

• 避免在索引列上使用函数或运算
• 确保 WHERE 条件与索引顺序一致
• 注意数据类型一致性，避免隐式转换
• 定期分析执行计划，监控慢查询

总结

这次优化经历再次证明：合理的索引设计是数据库性能的基础。深入理解查询特性和索引原理，才能将性能优化做到极致。

优化没有终点，每一次性能提升都是对技术理解的进一步加深。