MySQL覆盖索引同时满足排序与过滤的字段布局

我排查 MySQL 列表查询时，最容易误判的一点是：过滤快，不代表排序和取列也快。真正适合覆盖索引的查询，通常同时满足三个条件：WHERE 范围明确、ORDER BY 顺序稳定、SELECT 只取少量固定列。字段布局应围绕这条查询路径设计，而不是把表中所有常用列都堆进一个超宽索引。

官方地址：https://dev.mysql.com/doc/refman/8.4/en/

先拆出过滤、排序和返回字段

先拿一条真实查询做拆解，不要从“哪个字段区分度高”一句话开始。假设订单列表只展示未归档订单，按创建时间倒序取最近20条：

-- 只取列表页需要的列，避免把大字段带进覆盖索引
SELECT id, customer_id, status, created_at
FROM orders
WHERE tenant_id = 42
  AND status = 'paid'
  AND archived = 0
ORDER BY created_at DESC, id DESC
LIMIT 20;

这条语句里，tenant_id、status、archived 是等值过滤，created_at 和 id 负责稳定排序，返回列还包括 customer_id。如果把排序列放在过滤列之前，索引可能能顺序扫描，却要读出大量租户或状态不匹配的记录；如果漏掉排序列，过滤后仍可能出现额外排序。

按左前缀布局复合覆盖索引

对这类固定列表，我会先尝试“等值过滤列 → 排序列 → 必要返回列”的顺序，再用执行计划修正。示例索引如下：

-- 等值列先收窄租户和状态，排序列保持连续
CREATE INDEX idx_orders_list
ON orders (tenant_id, status, archived, created_at DESC, id DESC, customer_id);

-- 仅展示索引定义，确认字段顺序和方向
SHOW INDEX FROM orders;

这里的“覆盖”是针对这条 SELECT：id 是 InnoDB 二级索引记录隐含的主键列，显式列出的 customer_id 补齐了返回字段。并不是说这条索引覆盖所有查询。只要 SELECT 又取了 shipping_address 这类大字段，就会重新回表，索引也会变宽。

字段顺序还有两个边界。第一，复合索引遵循左前缀，单独按 status 查找不能直接等价利用这个索引。第二，等值列之后若出现范围条件，后续列对定位和排序的帮助会受限；不要仅凭字段名称推断“肯定不回表”。

用 EXPLAIN 验证回表与排序

建索引后先看计划，不要只看响应时间。测试数据分布、缓存状态和并发都会影响一次执行的耗时。下面这条命令足以作为第一轮检查：

-- 只观察优化器选择，不改变查询数据
EXPLAIN SELECT id, customer_id, status, created_at
FROM orders
WHERE tenant_id = 42 AND status = 'paid' AND archived = 0
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- 统计信息明显滞后时再更新，避免凭旧估算下结论
ANALYZE TABLE orders;

-- 更新后重新查看同一条查询的执行计划
EXPLAIN SELECT id, customer_id, status, created_at
FROM orders
WHERE tenant_id = 42 AND status = 'paid' AND archived = 0
ORDER BY created_at DESC, id DESC
LIMIT 20;

如果仍看到 Using filesort，先检查 ORDER BY 是否加入了索引之外的表达式、排序方向是否与版本和索引定义匹配，以及查询是否在过滤列之后遇到了范围条件。覆盖索引减少回表和排序是两个不同目标，不能把一个 Extra 结果当成全部结论。

处理范围条件和写入代价

生产环境我不会因为一条列表查询变快，就立即把宽索引推广到所有实例。新索引会增加磁盘占用，也会让 INSERT、UPDATE、DELETE 多维护一份 B+Tree。可以先在低风险副本或灰度实例观察索引大小、写入延迟和慢查询变化。

• 返回列经常变化时，优先保留“过滤 + 排序”的窄索引，不要追求勉强覆盖。
• 存在时间范围条件时，先确认范围宽度；范围很大时，后续排序列未必还能带来预期收益。
• 混合 ASC/DESC、表达式排序或函数过滤时，按实际 EXPLAIN 结果判断，不用固定口诀替代验证。
• 回退前记录索引名、创建时间和计划变化；确认没有其他关键查询依赖后再删除。

一个可操作的上线清单是：保存原始 EXPLAIN、创建索引、更新统计、重复 EXPLAIN、观察一段真实流量，再比较写入指标。只有读收益稳定且写成本可接受，才值得把这条复合覆盖索引留在主库。

常见问题

覆盖索引是不是一定不会回表？

不是。只有查询需要的列都能从索引记录和主键路径取得时，才可能避免回表；新增一个未覆盖的大字段就会改变结果。

为什么命中了索引仍然出现 filesort？

索引可用于过滤，不等于能按当前 ORDER BY 输出。常见原因是排序列不连续、方向不匹配、出现范围条件或排序表达式改变了索引顺序。

应该先放区分度最高的列吗？

不能只看区分度。固定等值条件、排序连续性、其他查询复用率和写入成本都要一起考虑；对列表查询，能否减少扫描和回表比单一基数口诀更重要。