MySQL 索引优化进阶：filesort 与临时表消除

现象：翻到第 1000 页要 8 秒

运营反馈"订单列表翻页越往后越慢"。我们复现了一下，第一页 23 ms，第 100 页 340 ms，第 1000 页 8.2 秒。

SQL 长这样（每页 20 条）：

SELECT * FROM t_order
 WHERE user_id = 100372
   AND order_status IN (2, 3)
 ORDER BY created_at DESC
 LIMIT 20000, 20;

EXPLAIN 结果：

mysql> EXPLAIN SELECT * FROM t_order WHERE ... \G
         type: ref
          key: idx_user_id
         rows: 48210
        Extra: Using index condition; Using where; Using filesort

rows: 48210，Using filesort。表名叫 filesort，但大多数时候它并不写文件，真正的含义是"无法利用索引顺序，必须额外做一次排序"。这个排序可能在内存里，也可能落盘。

先看看排序到底发生了什么

MySQL 8.0 提供了 optimizer_trace，能看到排序的细节：

mysql> SET optimizer_trace = 'enabled=on';
mysql> SELECT * FROM t_order WHERE ... LIMIT 20000, 20;
mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE\G

"filesort_summary": {
  "rows": 48210,
  "examined_rows": 48210,
  "number_of_tmp_files": 17,
  "sort_buffer_size": 1048576,
  "sort_mode": "<sort_key, additional_fields>"
}

三个关键信息：

number_of_tmp_files: 17 — 排序数据量超过了 sort_buffer_size（我们配的 1 MB），MySQL 把它切成 17 份，分别排序后归并。这就是落盘了。
sort_mode: <sort_key, additional_fields> — 全字段排序模式。
examined_rows: 48210 — 扫描并参与排序的行数。

两种排序模式

MySQL 做排序时有两种模式，选哪个取决于参数和数据量：

模式	sort_mode 显示	做法	代价
全字段排序	`<sort_key, additional_fields>`	把 SELECT 需要的全部字段都放进 sort_buffer	占用 buffer 大，容易落盘
rowid 排序	`<sort_key, rowid>`	只放排序字段 + 主键，排完再回表取其他字段	buffer 小，但多 48210 次随机回表 IO

选择的分界线是 max_length_for_sort_data（默认 4096 字节）：单行所有字段长度之和超过它，就退化成 rowid 排序。我们有 38 个字段、两个 varchar(255)，单行加起来约 1.2 KB，所以走的是全字段排序。

mysql> SHOW VARIABLES LIKE 'sort_buffer%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| sort_buffer_size        | 1048576 |    -- 1 MB，我们调过，默认 256 KB
+-------------------------+---------+

mysql> SHOW VARIABLES LIKE 'max_length_for_sort_data';
+--------------------------+-------+
| max_length_for_sort_data | 4096  |
+--------------------------+-------+

sort_buffer_size 是每个连接独享的，不是全局共享。设成 8 MB、并发 500 就是 4 GB 内存。不要随便调大，它只能缓解症状，不解决根因。MySQL 8.0.20 起 max_length_for_sort_data 已被废弃，优化器自己决定。

根因：索引没有覆盖排序

真正的问题是 idx_user_id 只有 user_id 一列。按 user_id 过滤出 48210 行之后，这些行在物理上是按主键排的，而我们要按 created_at DESC 排，只能重新排一遍。

如果能有一个索引，让 user_id = 100372 AND status IN (2,3) 的记录天然按 created_at 降序排列，就不用排序了。

联合索引的设计有个简单的 ESR 规则：

E（Equality）：等值条件列放最前
S（Sort）：排序列紧接着
R（Range）：范围条件列放最后

但这里有个冲突：order_status IN (2,3) 是范围条件，created_at 是排序字段。按 ESR 规则，范围列在排序列之后会导致排序列用不上（索引里范围列之后的列无法用于排序）。

两种解法：

解法一：把 IN 拆成等值

-- 用 UNION ALL 把 IN 拆成两次等值查询，各自都能用上 (user_id, order_status, created_at)
(SELECT * FROM t_order
  WHERE user_id = 100372 AND order_status = 2
  ORDER BY created_at DESC LIMIT 20020)
UNION ALL
(SELECT * FROM t_order
  WHERE user_id = 100372 AND order_status = 3
  ORDER BY created_at DESC LIMIT 20020)
ORDER BY created_at DESC LIMIT 20000, 20;

配合索引：

ALTER TABLE t_order ADD INDEX idx_user_status_created
    (user_id, order_status, created_at DESC);

EXPLAIN 变成：

         key: idx_user_status_created
         rows: 20
        Extra: Using index condition

Using filesort 消失了，rows 从 48210 降到 20。MySQL 8.0 支持降序索引（5.7 会忽略 DESC），所以倒序扫描可以直接走索引。

实测耗时：8.2 秒 → 46 ms。

解法二：解决深分页本身

但这个方案还有个问题：LIMIT 20000, 20 意味着 MySQL 要扫描前 20020 行再扔掉前 20000 行。虽然索引能帮上忙，代价还是有的。

更好的做法是改成游标分页：前端把上一页最后一条的 id 带过来。

-- 第一页
SELECT * FROM t_order
 WHERE user_id = 100372 AND order_status IN (2,3)
 ORDER BY created_at DESC, id DESC
 LIMIT 21;        -- 多取一条判断有没有下一页

-- 后续页，把上一页最后的 created_at 和 id 带进来
SELECT * FROM t_order
 WHERE user_id = 100372 AND order_status IN (2,3)
   AND (created_at, id) < ('2021-09-15 14:22:31', 8823741)
 ORDER BY created_at DESC, id DESC
 LIMIT 21;

这里用了元组比较（row constructor），MySQL 支持 (a, b) < (x, y) 这种写法。排序字段要加上 id，因为 created_at 可能重复，不加的话翻页会漏数据或者重复。

页码	优化前（方案一）	优化后（游标分页）
第 1 页	23 ms	11 ms
第 100 页	31 ms	12 ms
第 1000 页	46 ms	13 ms
第 5000 页	128 ms	12 ms

游标分页的耗时基本恒定。代价是不能跳页（没法直接跳到第 1000 页）。我们和产品确认了运营的实际用法是"一页一页翻"和"筛选后查看"，最后改成了游标 + 只允许前后翻页。

Using temporary 又是怎么回事

另一个 Extra 里常见的词是 Using temporary，表示创建了临时表。常见场景：

-- 1. GROUP BY 的列和 ORDER BY 的列不一致
SELECT user_id, COUNT(*) FROM t_order
 GROUP BY user_id ORDER BY created_at DESC;      -- 必然临时表 + filesort

-- 2. DISTINCT 和 ORDER BY 用了不同列
SELECT DISTINCT user_id FROM t_order ORDER BY created_at DESC;

-- 3. UNION（不去重的话用 UNION ALL 可以避免）
SELECT user_id FROM t_order_2021
 UNION
SELECT user_id FROM t_order_2020;

-- 4. 子查询被物化（DERIVED）
SELECT * FROM (SELECT * FROM t_order WHERE amount > 100) t WHERE t.user_id = 1;

我们的报表里踩过第 1 个。原 SQL：

SELECT DATE_FORMAT(created_at, '%Y-%m') AS m, SUM(amount) AS total
  FROM t_order
 WHERE created_at >= '2021-01-01'
 GROUP BY m
 ORDER BY created_at DESC;      -- 排的是原始列，不是分组列

        Extra: Using where; Using temporary; Using filesort

改成按分组列排序即可：

SELECT DATE_FORMAT(created_at, '%Y-%m') AS m, SUM(amount) AS total
  FROM t_order
 WHERE created_at >= '2021-01-01'
 GROUP BY m
 ORDER BY m DESC;               -- 和 GROUP BY 一致

        Extra: Using where; Using temporary      -- filesort 没了

临时表还在（GROUP BY 一个函数表达式没法走索引），但排序没了。耗时从 3.4 秒降到 1.1 秒。要彻底去掉临时表，得加一个生成列（generated column）并对它建索引，我们没做到这一步，因为这个报表一天只跑几次。

临时表的大小阈值：

mysql> SHOW VARIABLES LIKE 'tmp_table_size';
+----------------+----------+
| tmp_table_size | 16777216 |    -- 16 MB，超过就转成磁盘临时表（InnoDB 或 MyISAM）
+----------------+----------+
mysql> SHOW VARIABLES LIKE 'internal_tmp_disk_storage_engine%';
+-------------------------------------+--------+
| internal_tmp_disk_storage_engine    | InnoDB |    -- 8.0 默认 InnoDB，之前是 MyISAM
+-------------------------------------+--------+

判断临时表是否落盘，看状态变量：

mysql> SHOW STATUS LIKE 'Created_tmp%';
+-------------------------+-------+
| Created_tmp_disk_tables | 1284  |    -- 落盘的次数
| Created_tmp_tables      | 12043 |    -- 总次数
+-------------------------+-------+

落盘比例超过 10% 就值得查一查了。

排查清单

我后来把这套整理成一个固定流程，遇到慢查询就按这个走：

1. EXPLAIN 看 type（至少要到 range，最好 ref/const）、key、rows、Extra
2. Extra 里有 Using filesort → 检查 ORDER BY 的列是否被联合索引覆盖
3. Extra 里有 Using temporary → 检查 GROUP BY / DISTINCT 是否和 ORDER BY 一致
4. rows 远大于实际返回行数 → 过滤条件没走索引，或者深分页
5. 开 optimizer_trace 看 number_of_tmp_files 和 sort_mode
6. 用 EXPLAIN ANALYZE（MySQL 8.0.18 起）看真实执行数据，比 EXPLAIN 的估算准

mysql> EXPLAIN ANALYZE SELECT * FROM t_order WHERE user_id = 100372 ... \G
-> Limit/Offset: 20/20000  (cost=4821.20 rows=20) (actual time=8241.331..8241.402 rows=20 loops=1)
    -> Sort: t_order.created_at DESC, limit input to 20020 row(s) per chunk
        (cost=4821.20 rows=48210) (actual time=7984.221..8204.112 rows=20020 loops=1)
        -> Index lookup on t_order using idx_user_id (user_id=100372)
            (actual time=0.331..482.114 rows=48210 loops=1)

EXPLAIN ANALYZE 会真跑一遍并给出 actual time，定位到 Sort 这一步就吃掉了 7984 毫秒，一目了然。这是 MySQL 8.0.18 才有的功能，5.7 用户只能靠 optimizer_trace。

小结

Using filesort 不等于落盘，它的意思是"要额外排序"。有没有落盘看 optimizer_trace 里的 number_of_tmp_files。
sort_buffer_size 是每连接独享的，只能缓解不能根治，别无脑调大。
联合索引按 ESR 规则（等值 → 排序 → 范围）设计。范围条件列后面的排序列用不上，这种情况可以把 IN 拆成 UNION ALL 的等值查询。
深分页（LIMIT 20000, 20）用游标分页解决：WHERE (created_at, id) < (?, ?)，记得排序字段要补上 id 保证全序。代价是不能跳页。
Using temporary 常见原因是 GROUP BY 和 ORDER BY 的列不一致、DISTINCT + 不同列排序、UNION。把排序列改成和分组列一致能去掉 filesort。
EXPLAIN ANALYZE（8.0.18+）比 EXPLAIN 靠谱得多，有 actual time，能直接定位慢在哪一步。

菜单

分享

MySQL 索引优化进阶：filesort 与临时表消除

现象：翻到第 1000 页要 8 秒

先看看排序到底发生了什么

两种排序模式

根因：索引没有覆盖排序

解法一：把 IN 拆成等值

解法二：解决深分页本身

Using temporary 又是怎么回事

排查清单

小结

参考

大模型推理加速：缓存、量化与批处理

事件驱动架构与 Agent 的深度结合

JDK 25 LTS 正式发布：这是 Java 三十而立的一版

Agent 的记忆持久化与跨会话连续性

AI 应用的数据架构：从 OLTP 到向量检索的统一

Java AI 框架选型：Spring AI、LangChain4j、Embabel 怎么选

AgentScope Java 2.0：阿里的企业级多智能体底座

AI 时代 Java 架构师的能力模型

Agent 执行的幂等性与事务边界

Spring 与 DeepSeek 战略合作后的生态变化