凌晨两点，监控大屏报警炸了。我盯着那个P99延迟20000ms的曲线，心想：完了。

这不是我第一次遇到这种场景，但每次都觉得刺激——就像过山车爬到顶的瞬间，你明知道要往下掉，但还是忍不住心跳加速。

那天晚上我学到的东西，比读三个月文档都多。今天把这些经验整理出来，希望能帮各位在踩坑之前先看看别人的坑长什么样。

故事背景：一个看似简单的查询

系统里有个订单查询接口，上线两年了，一直稳如老狗。突然某天开始，响应时间从50ms慢慢变成200ms，再到2秒，最后直接超时。

开发第一反应：加缓存。我懂，我也这么想。但问题是，这个查询条件组合太多，缓存命中率不到30%，加了等于没加。

运维同学倒是很淡定：「监控显示慢查询数量暴涨，集中在某几个时间点。」

好，那就先看SQL。

SELECT * FROM orders 
WHERE user_id = ? 
  AND status = ? 
  AND created_at BETWEEN ? AND ? 
  AND deleted_at IS NULL
ORDER BY created_at DESC 
LIMIT 20

看起来很正常对吧？有user_id过滤，有status过滤，有时间范围，还有deleted_at软删除标记。索引应该怎么建？

我当时的建法是：

ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, created_at);

完美，符合最左前缀原则。自信满满上线。

然后监控报警更严重了。

问题定位：EXPLAIN是个好东西，但你得会用

后来我学会了一件事：任何SQL优化之前，先EXPLAIN。这不是建议，是规矩。

EXPLAIN SELECT * FROM orders 
WHERE user_id = 12345 
  AND status = paid 
  AND created_at BETWEEN 2026-01-01 AND 2026-03-01 
  AND deleted_at IS NULL
ORDER BY created_at DESC 
LIMIT 20

结果出来，type列显示的是ALL。ALL是什么？是全表扫描。

我的索引居然没生效？

仔细一看，key列显示的确实是idx_user_status_time，但rows列是48万——整个表的行数。这说明什么？说明索引虽然被提到了，但实际扫描的行数并没有减少。

问题出在BETWEEN上。

第一个坑：范围条件导致索引中断

这是个经典问题，但很多人包括我自己，以前只是「知道」，并没有真正理解。

当SQL中使用BETWEEN ... AND ...或>/<这类范围条件时，MySQL只能利用索引的前缀部分。什么意思？

你的联合索引是(user_id, status, created_at)，查询条件是user_id = ? AND status = ? AND created_at BETWEEN ? AND ?。

理论上MySQL应该能走完整个索引，但实际执行时，BETWEEN会被优化器认为是一个范围扫描的开始——它会先匹配user_id和status，然后直接在created_at的范围上扫描，但此时索引的有序性已经被破坏。

等等，这不对。我又查了一下文档，发现BETWEEN其实是可以和范围条件一起用的。真正的问题在别处。

真正的问题是：deleted_at IS NULL这个条件。

第二个坑：软删除字段的隐身杀手

我意识到，deleted_at IS NULL这个条件在索引里根本不存在。当MySQL使用(user_id, status, created_at)索引过滤后，还需要回表查询所有匹配的行，然后才能判断deleted_at是否为空。

这就导致了一个经典问题：索引过滤率不够高，大量的回表操作拖累了性能。

解决方案很简单：把deleted_at加到索引里。

ALTER TABLE orders ADD INDEX idx_query (user_id, status, deleted_at, created_at);

上线后测试，响应时间从20000ms降到了800ms。有进步，但还不够。

深入排查：订单数据量只是表面

800ms对于一个查询来说还是太慢了。我开始怀疑是不是数据量的问题。

看了下订单表，5000万数据。user_id过滤后大概有多少？

SELECT COUNT(*) FROM orders WHERE user_id = 12345;
-- 结果：约200条

200条数据怎么会慢？问题不在数据量，在于查询方式。

我又看了一遍EXPLAIN结果。type列显示的是range，而不是ref。

range比全表扫描好，但比ref差。为什么是range？因为BETWEEN。

我灵机一动，换了个思路：不用BETWEEN，改用两个大于小于。

SELECT * FROM orders 
WHERE user_id = ? 
  AND status = ? 
  AND created_at >= ? 
  AND created_at <= ? 
  AND deleted_at IS NULL
ORDER BY created_at DESC 
LIMIT 20

结果一样。MySQL对BETWEEN和>/<的处理是一样的，优化器表示：你别想耍我。

那问题到底在哪？

第三个坑：ORDER BY的性能陷阱

我突然意识到另一个问题：ORDER BY created_at DESC。

索引是(user_id, status, deleted_at, created_at)，created_at在最后，是递增的。但我在查最新数据，需要降序排列。

MySQL可以利用索引的有序性来避免排序操作。如果索引是(created_at ASC)，那么ORDER BY created_at DESC就需要额外的排序；但如果索引是(created_at DESC)，MySQL可以直接利用。

但更重要的是：我查了官方文档，发现了一个关键点——当查询需要回表且回表成本高于扫描成本时，优化器可能选择全表扫描。

5000万数据的表，即使只返回200行，如果需要大量随机IO读取数据页，性能肯定差。

真正有效的解决方案

我最后是怎么解决的？三个改动：

1. 覆盖索引，避免回表

把所有需要查询的字段都加到索引里，让查询完全在索引树完成，不需要回表。

ALTER TABLE orders ADD INDEX idx_cover (user_id, status, deleted_at, created_at, id, amount);

这样SQL变成：

SELECT id, amount, ... FROM orders 
WHERE user_id = ? 
  AND status = ? 
  AND created_at >= ? 
  AND created_at <= ? 
  AND deleted_at IS NULL
ORDER BY created_at DESC 
LIMIT 20

实际上，如果只需要分页数据，可以用延迟关联：

SELECT o.* FROM orders o
INNER JOIN (
  SELECT id FROM orders 
  WHERE user_id = ? 
    AND status = ? 
    AND created_at >= ? 
    AND created_at <= ? 
    AND deleted_at IS NULL
  ORDER BY created_at DESC 
  LIMIT 20
) t ON o.id = t.id

2. 拆分查询，牺牲一致性换性能

业务上允许的话，把时间范围查询和排序分开处理。比如先查最近30天的数据，如果不够再扩大范围。

3. 利用分区表

这个改动最大，但效果也最明显。把orders表按月分区，这样查询只会扫描特定分区的数据，而不是全表。

ALTER TABLE orders PARTITION BY RANGE (YEAR(created_at)*100 + MONTH(created_at)) (
  PARTITION p202601 VALUES LESS THAN (202602),
  PARTITION p202602 VALUES LESS THAN (202603),
  PARTITION p202603 VALUES LESS THAN (202604),
  ...
);

最终效果

三个方案组合上线后，P99延迟稳定在180ms左右。比最初的20000ms好了100倍。

但说实话，这个过程中最让我震撼的不是优化本身，而是——很多看似简单的SQL，背后藏着数据库多少心思。

你以为建了索引就万事大吉？数据库要考虑的包括：数据分布、查询条件、排序方向、返回字段、软删除、时分区分...每一个因素都可能让你的索引形同虚设。

最后送各位一句话：优化SQL之前先EXPLAIN，优化EXPLAIN之前先理解执行计划每一列的含义。

这不是玄学，这是科学。

有问题欢迎留言，我会在凌晨两点的时候看到——毕竟，程序员的生物钟就是这么奇怪的。