优化MySQL中筛选'ARCHIVED'状态的查询性能

问题根源 #

你的查询慢的核心原因是：

• 筛选'ARCHIVED'时，使用了单字段status索引，过滤后得到110多万条数据，之后需要对这些数据做filesort（文件排序）来满足ORDER BY created_at DESC的要求，排序百万级数据的开销极大，导致耗时5.7秒。
• 而'DRAFT'只有2万多条数据，filesort的开销可以忽略；无筛选条件时，MySQL会直接扫描表并快速定位最新的50条数据（因为LIMIT 50，不需要全量排序），所以速度快。

核心优化：创建复合覆盖索引 #

创建一个包含status、created_at（倒序）和查询所需字段的复合覆盖索引，让数据库直接从索引中获取数据，无需回表和排序：

CREATE INDEX idx_status_created_at_id ON article (status, created_at DESC, id);

为什么这个索引有效：

1. 过滤+排序一体化：索引首先按status分组，同一status下的记录已经按created_at倒序排列，无需额外排序。
2. 覆盖索引：索引包含了查询需要的id、created_at字段，数据库不需要回表查询原数据，直接从索引中读取结果即可。
3. 快速取数：对于status = 'ARCHIVED'的查询，数据库会直接定位到索引中该分组的起始位置，取前50条数据即可，全程无额外开销。

验证优化效果 #

创建索引后执行原查询，查看执行计划：

• key字段应显示为idx_status_created_at_id
• Extra字段应显示为Using index（表示使用覆盖索引，无回表），且不再有Using filesort

此时查询耗时应该能降到1秒以内，和DRAFT或无筛选条件的查询速度一致。

可选清理：冗余索引检查 #

如果article_status_idx（单字段status索引）不再被其他查询使用，可以考虑删除，避免索引维护开销：

DROP INDEX article_status_idx ON article;

内容的提问来源于stack exchange，提问作者Unnikrishnan