我刚工作的时候，有次要按创建时间排序查询用户，写了 SELECT * FROM users ORDER BY created_at LIMIT 10，结果执行了 30 秒。DBA 帮我一看执行计划，发现没走索引，导致 Using filesort。

今天咱们就来扒一扒 ORDER BY 的原理与优化，看完这篇，你就能把 30 秒的查询优化到 0.01 秒。

ORDER BY 的两种算法

MySQL 的 ORDER BY 有两种算法：索引排序 和 文件排序（filesort）。

1. 索引排序（快！）

如果 ORDER BY 的字段有索引，且顺序和索引一致，MySQL 会直接按索引顺序读，不需要额外排序。

-- created_at 有索引
CREATE INDEX idx_created_at ON users(created_at);

-- ORDER BY 能用索引排序
EXPLAIN SELECT * FROM users ORDER BY created_at LIMIT 10;

输出：

+----+-------------+-------+-------+---------------+-----------------+---------+------+----------+-------+
| id | select_type | table | type  | possible_keys | key             | key_len | ref  | rows     | Extra |
+----+-------------+-------+-------+---------------+-----------------+---------+------+----------+-------+
|  1 | SIMPLE      | users | index | NULL          | idx_created_at | 5       | NULL | 10       |       |
+----+-------------+-------+-------+---------------+-----------------+---------+------+----------+-------+

关键点：type = index（索引扫描），Extra 里没有 Using filesort（不需要额外排序）。

为什么快？ 索引是有序的，直接顺着索引读就行，不需要额外的排序操作。

2. 文件排序（filesort，慢！）

如果 ORDER BY 的字段没索引，或者顺序和索引不一致，MySQL 会把所有符合条件的行放到内存（或磁盘）里排序。

-- created_at 没有索引
EXPLAIN SELECT * FROM users ORDER BY created_at LIMIT 10;

输出：

+----+-------------+-------+------+---------------+------+---------+------+----------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows     | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+----------+----------------+
|  1 | SIMPLE      | users | ALL  | NULL          | NULL | NULL    | NULL | 20000000 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+----------+----------------+

关键点：Extra = Using filesort（需要文件排序）。

为什么慢？ 要扫描所有符合条件的行，放到内存（或磁盘）里排序，然后再取前 N 行。

文件排序的两种算法

filesort 本身有两种算法：双路排序 和 单路排序。

1. 双路排序（Two-pass Sort）

旧版算法（MySQL 4.1 之前）。

流程：

  1. 第一次读：读所有符合条件的行的 主键 ID + 排序字段，按排序字段排序
    1. 第二次读：根据排序后的主键 ID，去聚簇索引里读完整行数据
      问题：要读两次表，I/O 次数多。

2. 单路排序（Single-pass Sort）

新版算法（MySQL 4.1 之后，默认）。

流程：

  1. 读所有符合条件的行的 主键 ID + 排序字段 + 查询字段，按排序字段排序
    1. 直接返回结果（不需要第二次读）
      优点：只读一次表，I/O 次数少。

缺点：如果查询字段太多（比如 SELECT *），内存可能不够，要用到磁盘（临时文件）。

参数控制：max_length_for_sort_data（默认 1024 字节），如果查询字段总长度超过这个值，会退化为双路排序。

优化方案 1：给 ORDER BY 字段加索引（推荐！）

思路：让 ORDER BY 走索引排序，避免 filesort。

优化前

-- created_at 没有索引
SELECT * FROM users ORDER BY created_at LIMIT 10;
-- 执行 30 秒（Using filesort）

优化后

-- 给 created_at 加索引
CREATE INDEX idx_created_at ON users(created_at);

-- ORDER BY 走索引排序
SELECT * FROM users ORDER BY created_at LIMIT 10;
-- 执行 0.01 秒（没有 Using filesort）

关键点：索引的顺序要和 ORDER BY 的顺序一致。

-- 索引：(created_at)
-- 能用索引排序
ORDER BY created_at
ORDER BY created_at ASC
ORDER BY created_at DESC

-- 不能用索引排序（顺序不一致）
ORDER BY created_at ASC, id DESC  -- 一个升序，一个降序

优化方案 2：用覆盖索引（Covering Index）

思路：如果查询的字段都在索引里，不需要回表，性能更好。

优化前

-- 查询所有字段，要回表
SELECT * FROM users ORDER BY created_at LIMIT 10;
-- 执行 0.1 秒（要回表）

优化后

-- 查询的字段都在索引里，不需要回表
SELECT id, created_at FROM users ORDER BY created_at LIMIT 10;
-- 执行 0.01 秒（覆盖索引，不需要回表）

关键点：EXPLAIN 的 Extra 里会有 Using index（覆盖索引）。

优化方案 3：用 WHERE 限制范围（减少排序行数）

思路：如果 WHERE 条件能过滤掉大部分行，排序的行数就少了，性能更好。

优化前

-- 没有 WHERE 条件，要排序 2000 万行
SELECT * FROM users ORDER BY created_at LIMIT 10;
-- 执行 30 秒

优化后

-- 用 WHERE 限制范围，只排序 1000 行
SELECT * FROM users WHERE created_at > '2024-01-01' ORDER BY created_at LIMIT 10;
-- 执行 0.1 秒

关键点：WHERE 条件要走索引（不然全表扫描更慢）。

优化方案 4：用延迟关联（Deferred Join）

思路：先查主键 ID（覆盖索引），再用主键 ID 关联查完整数据。

优化前

-- 查询所有字段，要回表
SELECT * FROM users ORDER BY created_at LIMIT 1000000, 10;
-- 执行 30 秒（要排序 1000010 行，然后丢弃前 1000000 行）

优化后

-- 先查主键 ID（覆盖索引）
SELECT id FROM users ORDER BY created_at LIMIT 1000000, 10;

-- 再用主键 ID 关联查完整数据
SELECT * FROM users a
JOIN (SELECT id FROM users ORDER BY created_at LIMIT 1000000, 10) b
ON a.id = b.id;
-- 执行 0.5 秒（先查 ID 是覆盖索引，快；再关联查完整数据，只查 10 行）

关键点：子查询是覆盖索引（SELECT id），不需要回表，性能很好。

优化方案 5：增加 sort_buffer_size（谨慎！）

思路：如果实在没法用索引排序，只能 filesort，可以增大 sort_buffer_size，让更多排序在内存里完成（减少磁盘 I/O）。

查看当前 sort_buffer_size

SHOW VARIABLES LIKE 'sort_buffer_size';
-- 默认 262144（256KB）

增大 sort_buffer_size

-- 设置为 4MB
SET GLOBAL sort_buffer_size = 4194304;

优点：减少磁盘 I/O，filesort 更快。

缺点：

  • 每个连接都会分配 sort_buffer_size 大小的内存，如果连接数多，内存消耗大
    • 不是越大越好（超过 max_length_for_sort_data，会退化为双路排序）
      建议：不要盲目增大，先优化索引和 SQL。

优化方案 6：用分页游标（Cursor Pagination）

思路：记住上一页的最后一条记录的排序字段值，下一页从这个值开始查（避免 LIMIT offset, size 的大偏移量问题）。

优化前

-- 第 100001 页，要排序 1000010 行
SELECT * FROM users ORDER BY created_at LIMIT 1000000, 10;
-- 执行 30 秒

优化后

-- 假设上一页最后一条记录的 created_at = '2024-01-15 10:30:00'
SELECT * FROM users WHERE created_at > '2024-01-15 10:30:00' ORDER BY created_at LIMIT 10;
-- 执行 0.01 秒（走索引，只排序 10 行）

关键点：WHERE created_at > '...' 是范围查询，走索引，不需要 filesort。

实战：优化一个慢 ORDER BY

假设有个用户表，按创建时间排序查询很慢：

SELECT * FROM users ORDER BY created_at LIMIT 10;
-- 执行 30 秒

第 1 步：看执行计划

EXPLAIN SELECT * FROM users ORDER BY created_at LIMIT 10;

输出：

+----+-------------+-------+------+---------------+------+---------+------+----------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows     | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+----------+----------------+
|  1 | SIMPLE      | users | ALL  | NULL          | NULL | NULL    | NULL | 20000000 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+----------+----------------+

问题：

  1. type = ALL（全表扫描）
    1. Extra = Using filesort（文件排序）

第 2 步：给 ORDER BY 字段加索引

CREATE INDEX idx_created_at ON users(created_at);

再看执行计划：

EXPLAIN SELECT * FROM users ORDER BY created_at LIMIT 10;

输出：

+----+-------------+-------+-------+---------------+-----------------+---------+------+----------+-------+
| id | select_type | table | type  | possible_keys | key             | key_len | ref  | rows     | Extra |
+----+-------------+-------+-------+---------------+-----------------+---------+------+----------+-------+
|  1 | SIMPLE      | users | index | NULL          | idx_created_at | 5       | NULL | 10       |       |
+----+-------------+-------+-------+---------------+-----------------+---------+------+----------+-------+

优化效果：

  1. type = index（索引扫描）
    1. Extra 里没有 Using filesort（走索引排序）
    1. 执行时间从 30 秒降到 0.01 秒（3000 倍提升！）

第 3 步：用覆盖索引进一步优化

-- 优化前：查询所有字段，要回表
SELECT * FROM users ORDER BY created_at LIMIT 10;
-- 执行 0.1 秒

-- 优化后：查询的字段都在索引里，不需要回表
SELECT id, created_at FROM users ORDER BY created_at LIMIT 10;
-- 执行 0.01 秒（覆盖索引）

实战建议

1. 给 ORDER BY 字段加索引（最重要！）

这是最重要的建议。ORDER BY 字段有索引，能避免 filesort，性能提升几十倍。

-- 优化前：没索引，Using filesort
SELECT * FROM users ORDER BY created_at LIMIT 10;

-- 优化后：加索引，走索引排序
CREATE INDEX idx_created_at ON users(created_at);

2. 用覆盖索引

如果查询的字段都在索引里，不需要回表，性能更好。

-- 优化前：要回表
SELECT * FROM users ORDER BY created_at LIMIT 10;

-- 优化后：覆盖索引
SELECT id, created_at FROM users ORDER BY created_at LIMIT 10;

3. 用 WHERE 限制范围

如果 WHERE 条件能过滤掉大部分行，排序的行数就少了，性能更好。

-- 优化前：没有 WHERE，要排序 2000 万行
SELECT * FROM users ORDER BY created_at LIMIT 10;

-- 优化后：用 WHERE 限制范围，只排序 1000 行
SELECT * FROM users WHERE created_at > '2024-01-01' ORDER BY created_at LIMIT 10;

4. 用分页游标（避免大偏移量）

如果分页偏移量很大（LIMIT 1000000, 10），用分页游标优化。

-- 优化前：大偏移量，要排序 1000010 行
SELECT * FROM users ORDER BY created_at LIMIT 1000000, 10;

-- 优化后：分页游标，只排序 10 行
SELECT * FROM users WHERE created_at > '2024-01-15 10:30:00' ORDER BY created_at LIMIT 10;

5. 谨慎增加 sort_buffer_size

如果实在没法用索引排序，可以增大 sort_buffer_size，但不要盲目增大。

-- 查看当前值
SHOW VARIABLES LIKE 'sort_buffer_size';

-- 增大（谨慎！）
SET GLOBAL sort_buffer_size = 4194304;  -- 4MB

总结

  • ORDER BY 的两种算法：索引排序（快）和 文件排序（filesort）（慢）
    • 文件排序的两种算法：双路排序（旧版，读两次表）和 单路排序（新版，读一次表）
    • 优化方案 1：给 ORDER BY 字段加索引（推荐！）
    • 优化方案 2：用覆盖索引（减少回表）
    • 优化方案 3：用 WHERE 限制范围（减少排序行数）
    • 优化方案 4：用延迟关联（先查 ID，再关联）
    • 优化方案 5：增加 sort_buffer_size（谨慎！）
    • 优化方案 6：用分页游标（避免大偏移量）
    • 实战建议：给 ORDER BY 字段加索引、用覆盖索引、用 WHERE 限制范围、用分页游标、谨慎增加 sort_buffer_size
      如果你能把 ORDER BY 的两种算法、6 种优化方案讲清楚，面试官绝对觉得你是高级开发。

实战代码都在我本地跑过，你可以放心复制。 如果有问题，欢迎评论区交流！

Logo
https://edu.csdn.net/learn/39067/627173?utm_source=2019755004

汇聚全球AI编程工具，助力开发者即刻编程。

更多推荐

cover

Claude API + Python实战：结构化输出后，如何拦住“看起来正确”的JSON

avatar AI编程社区
cover

2026年8月开发者实战指南：ChatGPT Plus / Pro + Codex 如何协同完成需求分析、编码、测试、调试与代码审查

avatar AI编程社区
cover

投资一台B300，部署Claude Opus 5多久才能回本？

avatar AI编程社区