📌 今日关键词：**Using temporary、Using filesort、临时表、文件排序、sort_buffer、联合索引、ORDER BY 优化、GROUP BY 隐式排序、单路排序、双路排序**

---

大家好呀！我是 **数据库小学妹** 👋

之前我们拆解过 InnoDB 的内存架构和三大日志，都是引擎层面的东西。今天换个角度，回到 SQL 层面，聊聊 ORDER BY。

起因是上周帮同事看一条慢查询。一个很普通的分页报表 SQL，数据量也不算大，跑了 3.2 秒。我打开 EXPLAIN 一看，Extra 那栏写着 `Using temporary; Using filesort`，两个红灯同时亮。同事问我这俩到底啥意思，我才发现自己之前也就是"看到知道不太好"，真要说清楚底层在干嘛，其实说不上来。

翻了好几天文档，总算把这块理顺了。今天从 EXPLAIN 怎么看，到临时表怎么运作，再到排序算法怎么选，一步步拆给你。

---

## 一、EXPLAIN 里的两个"红灯"

> 🔥 核心本质：这两个标记说明 MySQL 没法用索引完成你的排序或分组，需要额外的"体力活"。

先看一条最基础的慢查询：

```sql
EXPLAIN SELECT * FROM orders 
WHERE user_id = 100 
ORDER BY create_time DESC;
```

假设 `orders` 表上有 `idx_user_id(user_id)` 索引，但没有 `(user_id, create_time)` 的联合索引。EXPLAIN 的 Extra 栏大概率会出现这两个标记：

### Using temporary

MySQL 需要在查询过程中创建一张临时表来存放中间结果。你可以理解成做数学题的时候，草稿纸不够用了，得另拿一张纸来写中间步骤。这张"草稿纸"就是内部临时表。

### Using filesort

不是"文件排序"的意思！这个命名真的误导了好多人，包括我。它真正的意思是：没法利用索引的有序性来满足 ORDER BY，MySQL 得自己做一次额外的排序操作。叫"额外排序"更准确。

**注意：出现一个要注意，出现两个必须优化。**

两个同时出现，说明你的查询既要建临时表又要额外排序，数据量一大就很容易出问题。

---

## 二、内部临时表到底是怎么运作的？

> 🔥 核心本质：MySQL 处理复杂查询时，需要一个"暂存区"来放中间结果，这个暂存区就是内部临时表。

### 2.1 哪些操作会触发临时表？

用临时表的操作比你想的多得多，不只是 GROUP BY：

- `GROUP BY` 分组聚合
- `DISTINCT` 去重
- `UNION`（不含 ALL 的那种，需要去重）
- 派生表 / 子查询
- 多表 JOIN + ORDER BY（排序字段不在驱动表索引中）

### 2.2 内存临时表 vs 磁盘临时表

MySQL 创建临时表的时候，优先用 MEMORY 引擎把它放在内存里。但有个前提条件：这张临时表的大小不能超过 `tmp_table_size` 和 `max_heap_table_size` 中较小的那个值。

我第一次看到这个配置项的时候还特意去查了默认值，发现默认是 16MB。听起来不小对吧？但如果你的临时表里有 VARCHAR(5000) 的字段，几百行数据就撑爆了。

一旦超限，MySQL 会把临时表从内存转成磁盘临时表，用 InnoDB 引擎或者 MyISAM 引擎来存。差距有多大呢？我之前做设计师的时候，觉得 SSD 开 PS 已经够快了，但 SSD 随机读写跟内存比还是差两个数量级。从内存读一条数据大概 100 纳秒，从 SSD 读要 100 微秒，中间隔了一千倍。临时表一旦溢出到磁盘，查询耗时翻好几倍都是常事。

### 2.3 一个容易忽略的坑

MEMORY 引擎有个硬伤：不支持 BLOB 和 TEXT 类型。如果你的查询里包含这类字段，MySQL 会直接跳过内存临时表，从一开始就用磁盘临时表。

这也是我之前提到的，为什么建表的时候能用 VARCHAR(200) 就别用 TEXT 的原因之一。不是说 TEXT 不好，而是在排序和临时表的场景下，它会让你的优化空间变窄不少。

---

## 三、ORDER BY 的两种排序算法

> 🔥 核心本质：MySQL 排序就两条路，要么靠索引，要么靠 sort_buffer，后者又分单路和双路。

### 3.1 索引排序（最优解）

如果你的 ORDER BY 字段恰好在索引中，并且索引的顺序和 ORDER BY 的方向一致，MySQL 直接按索引顺序读数据就行，根本不需要额外排序。

```sql
-- 假设有联合索引 idx_user_time(user_id, create_time)
EXPLAIN SELECT * FROM orders 
WHERE user_id = 100 
ORDER BY create_time DESC;
-- Extra: 无 Using filesort ✓
```

这种情况下 EXPLAIN 的 Extra 栏不会出现 Using filesort。数据从索引里读出来本身就是有序的，跟翻字典一个道理，不用再排一次。

### 3.2 文件排序（extra sort）

当索引排序搞不定的时候，MySQL 就得用 sort_buffer 来做额外排序。这里有两种算法：

**单路排序（Single-pass）**

一次性把 ORDER BY 需要的所有字段都读进 sort_buffer，在内存里排好序后直接返回。一趟搞定，所以叫"单路"。

**双路排序（Two-pass）**

sort_buffer 放不下所有字段的时候，MySQL 会先只读排序键和行指针进去排好序，然后再根据排好序的指针回表取其他字段。两趟，所以叫"双路"。

**那 MySQL 怎么决定走哪条路呢？**

靠 `max_length_for_sort_data` 这个参数控制，默认 4096 字节。如果你查询的所有字段总长度超过这个值，就走双路；没超过就走单路。

单路看着好对吧？但也有坑。如果你 SELECT 的字段特别多、特别长，一股脑全塞进 sort_buffer，内存占用会很大，反而可能比双路更慢。所以不是说单路一定比双路快，得看具体情况。

---

## 四、GROUP BY 的隐式排序陷阱

> 🔥 核心本质：GROUP BY 的默认行为在 8.0 前后不一样了，不知道这个改动可能会写出有 bug 的代码。

### 4.1 MySQL 5.7 的"贴心"行为

在 MySQL 5.7 及更早版本中，GROUP BY 会隐含一个 ORDER BY 的效果。也就是说你写了 `GROUP BY status`，结果会自动按 status 排序返回。很多开发者不知道这是"隐式排序"，还以为 GROUP BY 本身就带排序功能。

### 4.2 MySQL 8.0 改了

从 8.0 开始，GROUP BY 不再保证返回顺序。官方文档的原话是"不再隐含排序"（no longer implies sorting）。这个改动坑了不少人。

我第一次碰到这个问题是升级 8.0 之后，一个报表页面的数据顺序突然乱了。排查了半天才发现是 GROUP BY 的隐式排序没了。加上 ORDER BY 就好了，但这个坑确实让人防不胜防。

### 4.3 GROUP BY + ORDER BY 双重排序

更坑的情况是 GROUP BY 和 ORDER BY 同时出现，而且排序字段还不一样：

```sql
SELECT status, COUNT(*) FROM orders 
GROUP BY status 
ORDER BY COUNT(*) DESC;
```

这条 SQL 可能触发两次排序：一次是 GROUP BY 分组用的，一次是 ORDER BY 输出用的。EXPLAIN 里你会看到 Using temporary 和 Using filesort 都亮了。

**我的建议：GROUP BY 后面永远显式写 ORDER BY，不管用的是 5.7 还是 8.0。** 这样代码在不同版本之间行为一致，不用猜 MySQL 会怎么排。

---

## 五、优化实战：一条慢查询从 3.2 秒到 0.05 秒

> 🔥 核心本质：大部分 filesort + temporary 的问题，一根联合索引就能解决。

回到开头说的那条报表 SQL。

### 原始 SQL

```sql
SELECT order_id, user_id, amount, create_time
FROM orders
WHERE user_id = 100
ORDER BY create_time DESC
LIMIT 20;
```

### 原始 EXPLAIN

```
type: ref
key: idx_user_id
Extra: Using where; Using temporary; Using filesort
rows: 18543
```

走了 `idx_user_id` 索引，但索引里没有 `create_time`，所以 MySQL 先按 user_id 找到所有匹配的行放进临时表，再对临时表做文件排序。数据量一大就慢了。

### 优化思路

索引设计有个口诀：**WHERE 字段 + GROUP BY 字段 + ORDER BY 字段**，按这个顺序排进联合索引。

这条 SQL 里：
- WHERE 条件是 `user_id`
- ORDER BY 是 `create_time DESC`

加一个联合索引：

```sql
ALTER TABLE orders ADD INDEX idx_user_time(user_id, create_time);
```

### 优化后 EXPLAIN

```
type: ref
key: idx_user_time
Extra: Using index condition
rows: 18543
```

Using temporary 和 Using filesort 都消失了。rows 虽然一样，但不再需要建临时表和额外排序，查询直接按索引顺序读取。

### 执行耗时对比

```
优化前: 3.2 秒
优化后: 0.05 秒
```

64 倍的差距。一根索引解决的事。

### sort_buffer_size 调优

有时候你没法加索引（比如 ORDER BY 的字段是动态计算的），这时候可以考虑调大 `sort_buffer_size`。但要注意：这个参数是每个线程独立分配的。你设 2MB，100 个并发线程就是 200MB 的内存开销。

所以不是越大越好，得根据实际并发量来。一般 OLTP 场景 256KB~2MB 足够了，特殊的 OLAP 报表场景可以适当加大。

---

## 六、快速诊断 Checklist

下次 EXPLAIN 看到红灯，心里默念这几条：

1. Extra 有 Using temporary → 先查 GROUP BY / DISTINCT / 子查询，能改写就改写，能加索引就加索引
2. Extra 有 Using filesort → ORDER BY 字段有没有被索引盖住？方向对不对？
3. 两个同时出现 → 先上联合索引（WHERE + GROUP BY + ORDER BY 组合），实在不行再调 sort_buffer_size
4. 临时表溢出磁盘 → 看一眼 `Created_tmp_disk_tables` 这个状态值，如果一直在涨就说明 tmp_table_size 不够用。另外 SELECT * 是大忌，只取需要的字段能大幅减少临时表体积
5. GROUP BY 顺序不对 → 先确认版本，8.0 必须显式加 ORDER BY，别偷懒

说个我自己的习惯：每次写带 GROUP BY 或 ORDER BY 的查询，写完第一件事就是跑一遍 EXPLAIN，看看 Extra 那栏干不干净。刚开始觉得很烦，后来发现比上线之后再排查省心太多了。

---

## 八、新手避坑清单

- ✅ EXPLAIN 看到 Using temporary / Using filesort，第一反应是检查索引覆盖
- ✅ 联合索引设计公式：WHERE + GROUP BY + ORDER BY 字段组合
- ✅ ORDER BY 混合 ASC / DESC 时，索引方向要对应（MySQL 8.0 支持 DESC 索引）
- ✅ GROUP BY 在 8.0 不再隐含排序，别依赖旧行为
- ✅ 避免 SELECT *，只选需要的字段可以减少临时表溢出磁盘的概率
- ✅ tmp_table_size 默认 16MB，监控 `Created_tmp_disk_tables` 看有没有频繁溢出
- ✅ sort_buffer_size 每线程独立分配，设太大高并发下会 OOM
- ✅ 包含 BLOB/TEXT 的查询会跳过内存临时表，建表时能用 VARCHAR 就别用 TEXT

---

说到底，Using temporary 和 Using filesort 这两个标记不是"错误"，只是 MySQL 在告诉你："我没法偷懒了，得老老实实干体力活。" 我们做优化的目标就是帮 MySQL 把这些体力活用索引"偷"掉。

👋 我是 **数据库小学妹**，一个用设计师思维学数据库的转行人。你有没有遇到过 ORDER BY 把查询搞慢的情况？或者 EXPLAIN 看到过什么看不懂的标记？评论区聊聊，大家一起少踩坑！💪

---
*本文实验基于 MySQL 8.0。临时表和排序行为在不同版本间有差异，尤其是 GROUP BY 隐式排序在 8.0 被取消，建议在自己的环境上跑一遍 EXPLAIN 验证。*


📌 今日关键词：Using temporary、Using filesort、临时表、文件排序、sort_buffer、联合索引、ORDER BY 优化、GROUP BY 隐式排序、单路排序、双路排序

ORDER BY 查询为什么突然变慢？搞懂临时表和文件排序的底层原理就不慌了

数据库小学妹详解MySQL性能“红灯”：Using temporary（临时表）与Using filesort（额外排序）本质是索引未覆盖导致的体力开销。通过合理设计联合索引（WHERE+GROUP BY+ORDER BY顺序）、禁用隐式排序（ORDER BY NULL）、调优sort_buffer等，可大幅消除二者，提升查询数十倍。

后端

数据库

算法

"MySQL优化必看：详解ORDER BY性能问题与解决方案。分析Using temporary和Using filesort的底层原理，分享联合索引设计技巧，解决GROUP BY隐式排序陷阱，提供慢查询优化实战案例。从3.2秒到0.05秒的性能提升，掌握索引排序与文件排序区别，避免临时表溢出磁盘。适合开发者提升SQL性能的实用指南。"

MySQL

OLAP

OLTP

腾讯云数据库特惠，新用户首单0.5折起


腾讯云BI新客户首次购买9.9元起！


文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

EdgeOne AI 安全实战专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

"MySQL优化必看：详解ORDER BY性能问题与解决方案。分析Using temporary和Using filesort的底层原理，分享联合索引设计技巧，解决GROUP BY隐式排序陷阱，提供慢查询优化实战案例。从3.2秒到0.05秒的性能提升，掌握索引排序与文件排序区别，避免临时表溢出磁盘。适合开发者提升SQL...

ORDER BY 查询为什么突然变慢？搞懂临时表和文件排序的底层原理就不慌了-腾讯云开发者社区-腾讯云

ORDER BY 查询为什么突然变慢？搞懂临时表和文件排序的底层原理就不慌了

ORDER BY 查询为什么突然变慢？搞懂临时表和文件排序的底层原理就不慌了

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐