MySQL 8 索引与慢查询：一条 order by limit 的优化过程

列表查询变慢，第一反应往往是“加个索引”。但联合索引的列顺序、范围条件、排序和回表，任何一项不匹配，执行计划都可能从索引扫描退回全表加 filesort。下面用一张工单表把过程拆开。

一、问题查询

SELECT id, title, status, created_at
FROM work_order
WHERE user_id = 42 AND status = 1
ORDER BY created_at DESC
LIMIT 20;

表上原本只有 idx_user(user_id)。EXPLAIN 里 type=ref，但 Extra 出现 Using filesort：先按 user_id 取出该用户所有工单，再在临时结果里排序，最后取前 20 条。用户工单越多，排序成本越高。

二、联合索引的列顺序

把索引改成 (user_id, status, created_at) 后，user_id 和 status 都是等值条件，created_at 在索引内天然有序，优化器可以直接沿索引倒序读 20 条，filesort 消失。关键是“等值列在前、排序列在后”：

ALTER TABLE work_order
  ADD INDEX idx_user_status_created (user_id, status, created_at);

如果写成 (user_id, created_at, status)，created_at 在中间，status 的等值条件无法继续用于定位，排序也用不上，效果会差很多。最左前缀不是“把常用列都塞进去”，而是按查询模式排。

三、范围条件会截断后面的列

把 status 改成 IN (1, 2) 或 status > 0 时，情况就变了。对于联合索引，范围条件之后的列不再能用于精确查找，也通常无法继续保证排序。此时 EXPLAIN 可能显示 range 并重新出现 filesort。解决办法不是硬加索引，而是先确认业务是否真的需要这个范围；如果绝大多数请求都是单状态，保持等值查询更划算。

如果 status 的分布非常倾斜，比如 99% 都是已完成，优化器可能低估等值条件的过滤能力。这时可以更新统计信息，或者对 status 建直方图：ANALYZE TABLE work_order UPDATE HISTOGRAM ON status WITH 64 BUCKETS;。直方图不是万能药，但能让优化器少做几次错误判断。

四、覆盖索引与回表

上面的查询还需要 title，而 title 不在索引里，每一行都要回表。20 行回表关系不大；如果列表页一次取几百行，或者字段较大，回表成本就会显现。可以把查询需要的列放进索引尾部，做成覆盖索引：

ALTER TABLE work_order
  ADD INDEX idx_user_status_created_cover
    (user_id, status, created_at, title);

代价是索引更大、写入更慢。覆盖索引适合“读多写少、列不多”的列表查询，不适合把整行都塞进去。用 EXPLAIN 的 Extra 看到 Using index，才算真正覆盖。

索引也不是越多越好。每个二级索引都会增加写入和清理成本，并占用 buffer pool；长期不用的索引可以用 sys.schema_unused_indexes 找出来，再按“先设为 INVISIBLE、观察一个业务周期、确认无依赖后删除”的流程处理。

五、深分页不要直接 offset

LIMIT 100000, 20 会先扫描并丢弃 10 万行，越翻越慢。两种常见改法：

游标分页：记住上一页最后一条的 id 或 (created_at, id)，下一页用 WHERE (created_at, id) < (?, ?) ORDER BY created_at DESC, id DESC LIMIT 20；
延迟关联：先用覆盖索引查出主键，再回表取详情，减少大范围回表。

游标分页不支持随机跳页，产品上要提前对齐；延迟关联则适合必须保留页码的场景。两者都比直接 OFFSET 更稳定。

六、先看慢日志，再看执行计划

不要凭感觉优化。打开慢查询日志，阈值按业务定，一般 0.5 到 1 秒；log_queries_not_using_indexes 建议关闭，否则小表也会刷屏。聚合分析可以用 pt-query-digest，或者直接查 performance_schema.events_statements_summary_by_digest，按总耗时排序。

对单条 SQL 用 EXPLAIN ANALYZE（8.0.18+）看实际行数和耗时，而不是只看估算。MySQL 8 还提供不可见索引，删除前先把索引设为 INVISIBLE，观察一段时间，确认没有查询依赖再真正删除。索引优化没有银弹，只有在“这条查询、这个数据分布”下的更优解。