上周优化了一条慢查询，从最开始的2秒多，最后优化到20毫秒以内。过程中踩了好几个索引的坑，有些是以为建了索引就会走，有些是索引建了但没生效。记录一下，也给大家提个醒。

问题背景

事情是这样的，运营反馈后台的文章列表页打开特别慢，有时候要转好几秒。我看了一下慢查询日志，发现这条查询耗时2.3秒：

SELECT * FROM blog_posts 
WHERE status = 'published' 
  AND category_id = 2 
  AND created_at > '2026-01-01' 
ORDER BY created_at DESC 
LIMIT 20;

表数据量大概50万条，按说这个查询应该很快才对。一看索引，status、category_id、created_at三个字段分别都建了索引，但就是慢。

误区一：每个字段都建索引就会走索引

很多人以为，WHERE条件里的每个字段都建了索引，查询就会快。其实不是这样的。

MySQL在大多数情况下，一条查询只能使用一个索引（除非是index merge，但适用场景有限）。如果三个字段分别建了三个独立索引，优化器会选择其中一个选择性最好的，其他条件只能在回表后再过滤。

比如上面的查询，如果优化器选择了category_id的索引，先找出分类2的所有文章（可能有十几万条），然后回表逐条判断status和created_at，再排序，最后取20条。这能快才怪。

正确做法：建联合索引，把WHERE条件里的字段和ORDER BY的字段组合起来。

ALTER TABLE blog_posts 
ADD INDEX idx_cat_status_time (category_id, status, created_at);

这样MySQL可以直接在索引里完成过滤和排序，不需要回表，也不需要额外排序。

误区二：联合索引的字段顺序无所谓

联合索引的字段顺序非常重要，遵循"最左前缀原则"。

比如上面的联合索引(category_id, status, created_at)，可以支持：

  • WHERE category_id = ?
  • WHERE category_id = ? AND status = ?
  • WHERE category_id = ? AND status = ? AND created_at = ?

但不能支持：

  • WHERE status = ?（跳过了最左的category_id）
  • WHERE created_at = ?（跳过了前两个字段）

所以建联合索引的时候，要把等值查询的字段放前面，范围查询和排序的字段放后面。一般来说，选择性高的字段（区分度大的）放前面效果更好。

误区三：索引建了就一定会用

建了索引不代表优化器一定会用。优化器会基于成本估算选择执行计划，如果它觉得全表扫描更快，就会放弃索引。

常见的索引失效场景：

1. 对索引列做函数运算或类型转换

-- 索引失效
WHERE DATE(created_at) = '2026-01-01'
-- 索引生效
WHERE created_at >= '2026-01-01' AND created_at < '2026-01-02'

2. 隐式类型转换

比如字段是字符串类型，但查询时传了数字，MySQL会做隐式转换，导致索引失效。

3. LIKE以通配符开头

-- 索引失效
WHERE title LIKE '%PHP%'
-- 索引生效（前缀匹配）
WHERE title LIKE 'PHP%'

4. OR连接的条件

如果OR两边的条件有一个没有索引，整个查询都不会走索引。

5. 数据量小的时候

表只有几百条数据的时候，全表扫描可能比走索引还快，优化器会选择全表扫描。这是正常的，不用纠结。

误区四：ORDER BY的字段单独建索引

很多人会给ORDER BY的字段单独建索引，以为这样排序就快了。其实如果WHERE条件和ORDER BY用的不是同一个索引，MySQL还是需要回表后再排序（Using filesort）。

正确的做法是把WHERE条件和ORDER BY的字段建在同一个联合索引里，让索引本身就是有序的，这样就不需要额外排序了。

比如我们的查询，联合索引(category_id, status, created_at)中，created_at是最后一个字段，在前面两个字段等值的情况下，created_at本身就是有序的，所以ORDER BY created_at DESC可以直接利用索引的顺序，不需要filesort。

误区五：EXPLAIN看到Using index就够了

EXPLAIN是优化SQL的必备工具，但不要只看type和key，还要看Extra和rows。

  • type：最好是const、eq_ref、ref，避免ALL（全表扫描）
  • key：实际使用的索引
  • rows：预估扫描的行数，越小越好
  • Extra：
    • Using index：覆盖索引，不需要回表，最好
    • Using where：回表后过滤
    • Using filesort：需要额外排序，不好
    • Using temporary：需要临时表，更不好

优化完我们的查询后，EXPLAIN结果：type=ref，key=idx_cat_status_time，rows=1000左右，Extra=Using index condition。完美。

最终效果

优化前：2.3秒，扫描15万行，Using filesort

优化后：18毫秒，扫描1000行，Using index condition

性能提升了100多倍。

一些建议

1. 不要盲目建索引。索引不是越多越好，每个索引都会增加写入的开销，也会占用存储空间。建索引前先想清楚查询模式。

2. 用EXPLAIN分析慢查询。不要靠猜，看执行计划才是最靠谱的。

3. 联合索引优于多个单列索引。对于多条件查询，一个好的联合索引比三个单列索引有用得多。

4. 定期检查慢查询日志。把慢查询阈值设低一点（比如100ms），定期分析，把问题消灭在萌芽状态。

5. 索引也要维护。随着数据变化，索引的选择性可能会变化，定期用ANALYZE TABLE更新统计信息。

数据库优化是个细活，没有银弹。多观察、多分析、多测试，才能找到最适合自己业务的方案。