MySQL大表查询优化求助：如何加速百万级TopN数据查询？

针对你140万数据量的ct表查询慢的问题，核心原因是当前索引无法同时满足过滤和排序需求，导致了Using filesort（文件排序），以下是具体优化步骤：

1. 构建组合索引解决文件排序 #

单个字段索引无法覆盖你的WHERE过滤+ORDER BY排序逻辑，必须创建过滤字段+排序字段的组合索引，让MySQL直接通过索引完成筛选和排序，避免额外的文件排序操作。

推荐两种组合索引方案，根据你的数据分布选择：

方案一（优先针对等值过滤） #

因为hidden、deleted、status都是等值匹配，number是范围匹配，最后是排序字段views，索引顺序遵循“等值字段在前，范围字段次之，排序字段最后”的原则：

CREATE INDEX idx_ct_filter_sort ON ct (hidden, deleted, status, number, views);

方案二（优先高基数字段） #

如果status的基数（不同值的数量）比hidden、deleted高，可以调整索引顺序，让MySQL更快缩小筛选范围：

CREATE INDEX idx_ct_status_sort ON ct (status, hidden, deleted, number, views);

2. 简化WHERE条件避免索引失效 #

• 把status LIKE 'active'改成status = 'active'，不带通配符的LIKE和等值查询逻辑一致，但明确写=能让MySQL更精准地选择索引。
• 确认number字段类型，如果是整数，不要用字符串类型的值和它比较，避免隐式类型转换导致索引失效。

3. 验证优化效果 #

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

EXPLAIN SELECT uid, title, number, views 
FROM ct 
WHERE hidden = 0 
AND deleted = 0 
AND number > 0 
AND status = 'active' 
ORDER BY views DESC 
LIMIT 0, 50000;

理想的结果是：Extra列不再出现Using filesort，key列显示你创建的组合索引，type列显示range或更优的类型。

4. 可选：用覆盖索引进一步提速 #

如果想让查询完全不需要回表访问主数据，可以把查询需要的字段（uid, title）加入索引末尾，做成覆盖索引：

CREATE INDEX idx_ct_covering ON ct (status, hidden, deleted, number, views, uid, title);

这样MySQL直接从索引中就能拿到所有需要的数据，大幅减少IO开销。

5. 辅助优化手段 #

• 定期清理无效数据：如果表中有大量hidden=1或deleted=1的数据，建议归档或删除，减少表的总数据量。
• 避免过度依赖配置调整：比如增大sort_buffer_size只能临时缓解，优化索引才是根本解决办法。

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