开发者日记

外观

实测降序索引：多方向排序从 Using filesort 到索引直读 ​

问题背景：多方向排序为什么总会出现 filesort ​

在后台管理、排行榜、消息流等业务中，常见的 SQL 并不是简单的单列排序，而是“先按状态过滤，再按创建时间倒序、同时间按主键正序”这类多方向排序：

sql
SELECT id, category_id, status, created_at, title
FROM articles
WHERE category_id = 10
ORDER BY created_at DESC, id ASC
LIMIT 20;

MySQL 5.7 虽然支持在 ORDER BY 中写 ASC、DESC，但索引记录本身基本按升序存储。遇到 DESC 排序时，优化器可以反向扫描索引；可一旦联合索引中同时出现升序和降序，例如 (category_id, created_at DESC, id ASC)，普通索引就无法同时满足两个方向，只能额外排序，执行计划中常见：

text
Using where; Using filesort

这里的 filesort 不一定真的落盘，但一定代表需要排序器处理。偏移量变大、结果集变多或排序字段包含复杂表达式时，CPU、临时空间和响应时间都会明显上升。

MySQL 8.0 引入真正的降序索引后，B+Tree 叶子节点可以按指定方向存储值；8.0.13 之后还支持索引跳过扫描（Skip Scan），在部分前导列低基数场景下，即使 WHERE 没有指定联合索引第一列，也可能复用联合索引。本文用一组可直接运行的 SQL，实测二者在多方向排序中的收益和边界。

方案对比：普通联合索引、降序索引与 Skip Scan ​

先看三种方案的差异：

  • 普通联合索引：如 (category_id, created_at, id)，适合所有排序列方向一致的查询；混合方向容易 filesort。
  • 降序索引：如 (category_id, created_at DESC, id ASC)，索引物理顺序与 ORDER BY 完全一致，可正向扫描并直接配合 LIMIT 提前终止。
  • 跳跃扫描：针对 (low_card_col, high_card_col...) 联合索引，在未指定低基数前导列但满足统计、成本等条件时，优化器可能为每个前导值发起类似多个范围扫描的访问。

需要强调：Skip Scan 不是“跳过最左前缀之后就万能”，它更依赖前导列 distinct 值少、后续列选择性好、统计信息估算成本低于全表扫描等条件。降序索引解决的是排序方向问题，Skip Scan 解决的是特定前导列缺失时的索引复用问题，二者不能混为一谈。

普通联合索引同向排序友好8.0 降序索引混合方向直读Skip Scan低基数前导列复用可能 filesort扫描量较大消除额外排序LIMIT 提前停止条件较严格需看成本选择

实操步骤：构造可复现的多方向排序实验 ​

1. 建表并生成 10 万行测试数据 ​

以下示例基于 MySQL 8.0.13 及以上版本。先创建表：

sql
DROP TABLE IF EXISTS articles;

CREATE TABLE articles (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  category_id TININT UNSIGNED NOT NULL,
  status TINYINT UNSIGNED NOT NULL,
  title VARCHAR(100) NOT NULL,
  created_at DATETIME(3) NOT NULL,
  PRIMARY KEY (id),
  KEY idx_category_time_id (category_id, created_at, id),
  KEY idx_status_time (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

使用递归 CTE 生成 10 万行数据。category_id 控制为 1 到 10 的低基数列，status 只有 3 种值，便于观察 Skip Scan：

sql
INSERT INTO articles (category_id, status, title, created_at)
WITH RECURSIVE seq AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 100000
)
SELECT
  1 + MOD(n, 10) AS category_id,
  1 + MOD(n, 3) AS status,
  CONCAT('文章标题-', n) AS title,
  DATE_ADD('2024-01-01 00:00:00', INTERVAL n MINUTE) AS created_at
FROM seq;

如果你的客户端递归深度受限，可先执行：

sql
SET SESSION cte_max_recursion_depth = 100000;

收集统计信息，避免执行计划因数据字典统计过旧而失真：

sql
ANALYZE TABLE articles;

2. 普通联合索引的执行计划 ​

先测试普通索引上的混合排序：

sql
EXPLAIN
SELECT id, category_id, created_at
FROM articles
WHERE category_id = 5
ORDER BY created_at DESC, id ASC
LIMIT 20;

在 idx_category_time_id(category_id, created_at, id) 中，category_id 等值匹配后，created_at 可以反向扫描获得降序，但后面的 id ASC 与反向扫描方向不一致。因此常见计划是使用该索引做过滤，却仍出现 Using filesort。

再看方向一致的排序：

sql
EXPLAIN
SELECT id, category_id, created_at
FROM articles
WHERE category_id = 5
ORDER BY created_at DESC, id DESC
LIMIT 20;

此时优化器沿 (category_id, created_at, id) 的反向顺序扫描即可，通常不需要 filesort。这说明排序方向不是可有可无的语法细节，而是决定索引能否“按序返回”的关键条件。

3. 创建 MySQL 8.0 降序索引 ​

针对核心 SQL 创建与排序方向完全一致的联合索引：

sql
ALTER TABLE articles
ADD INDEX idx_category_time_desc_id_asc (
  category_id ASC,
  created_at DESC,
  id ASC
);

再次查看执行计划：

sql
EXPLAIN FORMAT=TREE
SELECT id, category_id, created_at
FROM articles
WHERE category_id = 5
ORDER BY created_at DESC, id ASC
LIMIT 20;

理想情况下会看到新索引上的索引范围访问，并带有限制行数的节点，而不是排序节点。也可以用传统格式确认：

sql
EXPLAIN
SELECT id, category_id, created_at
FROM articles
WHERE category_id = 5
ORDER BY created_at DESC, id ASC
LIMIT 20;

重点关注：

  • key 是否为 idx_category_time_desc_id_asc；
  • Extra 是否消除了 Using filesort；
  • rows 是否因为 LIMIT 保持较小估算；
  • key_len 是否体现 category_id 等值条件被使用。

如果查询列较多，不建议盲目把所有字段塞进索引。只有高频、响应时间敏感且回表成本明显的查询，才考虑覆盖索引，例如：

sql
ALTER TABLE articles
ADD INDEX idx_category_cover (
  category_id ASC,
  created_at DESC,
  id ASC,
  status
);

4. Skip Scan 的触发与验证 ​

Skip Scan 的典型形态是：联合索引第一列低基数，查询没有指定第一列，但选择了后续高选择性列。先看已有索引 idx_status_time(status, created_at)：

sql
EXPLAIN
SELECT status, created_at
FROM articles
WHERE created_at BETWEEN '2024-02-01 00:00:00'
                    AND '2024-02-02 00:00:00';

如果优化器认为枚举 3 个 status 值后分别扫描时间范围更便宜，Extra 中可能出现：

text
Using index for skip scan

也可以通过优化器开关强制观察差异：

sql
-- 关闭 Skip Scan，对比成本与访问方式
SET optimizer_switch = 'skip_scan=off';
EXPLAIN
SELECT status, created_at
FROM articles
WHERE created_at BETWEEN '2024-02-01 00:00:00'
                    AND '2024-02-02 00:00:00';

-- 重新开启
SET optimizer_switch = 'skip_scan=on';
EXPLAIN
SELECT status, created_at
FROM articles
WHERE created_at BETWEEN '2024-02-01 00:00:00'
                    AND '2024-02-02 00:00:00';

但在我们构造的数据中，时间范围跨越大、条件选择性不足时，优化器可能选择全表扫描或其他索引。这不是 Skip Scan 失效，而是成本模型后的正常决策。可以把条件收窄再测：

sql
EXPLAIN
SELECT status, created_at
FROM articles
WHERE created_at BETWEEN '2024-01-10 10:00:00'
                    AND '2024-01-10 10:05:00';

降序索引也可以参与 Skip Scan，但要求更复杂：索引前导列的枚举成本、后续列方向、查询范围以及是否覆盖列都会影响选择。不要仅因为索引里写了 DESC，就断定跳跃扫描一定发生，一切以 EXPLAIN 为准。

执行链路：为什么降序索引能减少排序成本 ​

等值定位分类category_id时间倒序读取created_at DESC同时间正序id ASC取前 20 行立即停止扫描索引顺序即结果顺序，跳过 filesort

避坑总结：实战中最容易误判的 5 个细节 ​

坑 1：把 ORDER BY DESC 当成已经创建降序索引 ​

常见错误现象是表仍停留在 MySQL 5.7 或索引 DDL 没写 DESC，但 SQL 中写了：

sql
ORDER BY created_at DESC, id ASC;

开发同学看到 DESC 就以为数据库会自动建立降序索引。实际上，ORDER BY 只描述查询需求，不改变索引结构。修正方法是在 MySQL 8.0 中显式创建方向匹配的索引，并通过 SHOW CREATE TABLE 确认：

sql
SHOW CREATE TABLE articles\G

坑 2：范围条件放在联合索引中间，后续排序列失效 ​

错误索引设计如下：

sql
ALTER TABLE articles
ADD INDEX idx_bad_range (
  category_id,
  created_at,
  status
);

-- 典型不佳写法：created_at 范围之后，再期望 status 有序
SELECT id
FROM articles
WHERE category_id = 5
  AND created_at >= '2024-02-01'
ORDER BY status
LIMIT 20;

created_at 一旦使用范围扫描，后面的 status 在每个大范围内并不全局有序，容易产生 filesort。修正方法是把等值列放前面、排序列紧随其后，范围条件要结合具体 SQL 重新安排，而不是机械背诵“等值、范围、排序”的固定顺序。

坑 3：函数或隐式类型转换导致索引用不上 ​

例如 created_at 是 DATETIME(3)，却对索引列使用函数：

sql
-- 不推荐：索引列被函数包裹
SELECT id
FROM articles
WHERE DATE(created_at) = '2024-02-01'
ORDER BY created_at DESC;

应改成范围：

sql
SELECT id
FROM articles
WHERE created_at >= '2024-02-01 00:00:00'
  AND created_at <  '2024-02-02 00:00:00'
ORDER BY created_at DESC;

字符串、数字类型不匹配也可能触发隐式转换，表现为 key 为空或扫描行数异常巨大。建表时统一类型，查询参数按字段类型传递，才是稳定方案。

坑 4：以为前导列缺失就一定能 Skip Scan ​

很多人看到联合索引 (status, created_at)，就认为查 created_at 必然走 Skip Scan。实际上下述场景经常不会触发：status distinct 值很多；查询选择了非索引列导致大量回表；条件范围太宽；统计信息过旧；MySQL 版本低于 8.0.13。修正方法是先 ANALYZE TABLE，再用 EXPLAIN 查看 Using index for skip scan，必要时为高频查询建立以 created_at 开头的独立索引，而不是依赖优化器“跳跃”。

坑 5：只看有没有走索引，不看扫描行数和回表成本 ​

执行计划显示 key 命中并不代表 SQL 高效。某个索引可能扫描几万行再回表，最终不如一个更小的覆盖索引或改写后的范围查询。建议同时关注 type、rows、filtered、Extra，并用实际计时验证：

sql
-- 查看当前会话执行指标
FLUSH STATUS;

SELECT id, category_id, status, created_at, title
FROM articles
WHERE category_id = 5
ORDER BY created_at DESC, id ASC
LIMIT 20;

SHOW SESSION STATUS LIKE 'Handler_read%';

如果 Handler_read_next 或回表相关计数很高，即使没有 filesort，也可能存在索引扫描范围过大的问题。

结语 ​

MySQL 8.0 的降序索引不是玄学优化，它最确定的收益场景是：过滤列等值匹配后，ORDER BY 中存在明确的升序、降序混合需求，并且业务通常只取前若干行。此时让 B+Tree 的物理顺序与查询顺序一致，往往比加大 sort_buffer_size 更直接。Skip Scan 则是补充能力，适合低基数前导列、覆盖索引、高选择性后续范围的场景，不能替代符合最左前缀原则的主索引设计。

实际调优时，建议按“固定过滤列 → 排序方向 → 覆盖列 → 回表成本”的顺序设计联合索引，再用 EXPLAIN、FORMAT=TREE 和 Handler 指标验证，而不是仅凭经验添加大量冗余索引。

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。

本文来自开发者日记（devdiary.cn）· 记录开发者实战笔记与免费在线工具箱

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。