`## 1. 写在前面：一条简单SQL背后的排序真相
如果你平时写过带order by的查询，一定对“排序”这个动作不陌生。但真的深挖起来，MySQL 内部的排序逻辑远不止“排个序”这么简单——它可能直接在内存里排完就返回，也可能先把数据扔进临时文件做归并，甚至还会触发全字段排序和rowid排序两种完全不同的执行策略。
我用一个具体的场景开头。假设你维护着一张用户订单表，里面有用户ID、订单金额、下单时间、收货地址等一堆字段。某天你执行了这条查询：
select user_id, amount, receive_address from orders where status = 1 order by created_at desc limit 50;表面上看这只是“按时间倒序取前50条”。但MySQL执行引擎内部到底经历了什么，取决于sort buffer（排序缓冲区）大小、返回字段的长度、是否回表等一系列因素。很多人排查慢SQL时只盯着索引，却忽略了排序这个隐藏的吞性能大户。
这篇文章围绕order by的两种核心执行策略展开：全字段排序和rowid排序。我会聊清楚它们的触发条件、底层执行流程、各自优劣，再配合实际案例讲明白怎么让排序更快。无论你是刚接触MySQL的开发新人，还是被线上慢查询折磨过的DBA，这都值得花几分钟读完。
全文没有晦涩的理论堆砌，每一步都配合可验证的示例和参数去讲。你只需要有MySQL环境，就可以顺着做一遍实验，直观感受两种排序策略的差异。
2. 先搞清楚MySQL排序的三个基本概念
2.1 sort buffer：排序发生的“工作台”
sort buffer是MySQL为每个排序操作分配的私有内存区域，可以把它想象成一个临时工作台：需要排序的行会被先放到这个工作台上，排完序再返回给客户端。
这个工作台的大小由参数sort_buffer_size决定。我见过不少人在配置文件里随便写个sort_buffer_size = 2M，也有人习惯性地把它调得很大，觉得“缓冲区越大性能越好”。这其实都有点想当然了。
要理解 sort buffer 的作用，得先看排序的整体流程：
- 根据 where 条件在索引上定位满足条件的行。
- 把需要的字段读出来，放入 sort buffer。
- 在 sort buffer 内执行排序算法（快速排序或堆排序）。
- 如果 sort buffer 装不下所有数据，就有一部分数据先写到磁盘临时文件，最终做归并排序。
- 排好序的数据按查询要求返回。
sort_buffer_size就是第2步和第3步能用的内存上限。它的默认值在不同版本中稍有差异，MySQL 5.7默认是256KB，MySQL 8.0默认也是256KB。但这个值不是越大越好，而是够用就好，核心原因后面我会专门讲。
2.2 filesort：听起来吓人，其实是一类“外部排序”
MySQL 的执行计划中，如果Extra列出现Using filesort，并不代表一定使用了磁盘文件。filesort这个术语涵盖了两层含义：
- 如果排序数据量小于 sort buffer，那么整个排序都在内存中完成，不需要磁盘文件。这时的 filesort 只是“通过排序算法处理”的代名词。
- 如果数据量超过 sort buffer，则一部分数据先溢出到磁盘临时文件，后续通过归并排序（merge sort）把多个有序文件合并成最终结果。此时的 filesort 才是名副其实的“文件排序”。
从这个角度说，看到Using filesort先不用紧张，最关键的是判断它是否产生了磁盘临时文件。磁盘 I/O 和内存操作的速度差距是数量级的，一旦排序落盘，性能就会急剧下降。
2.3 全字段排序 vs rowid排序：两种取舍
MySQL 在实现 filesort 时，会根据查询涉及的字段总长度是否超过某个阈值，选择两种不同的排序策略：
- 全字段排序（full-field sort）：把查询涉及的所有字段都放入 sort buffer，在内存中完成排序后，直接返回结果集。
- rowid排序（rowid sort）：只把排序字段和主键ID放入 sort buffer。排好序后，再根据主键值回表查出完整的行，返回给客户端。
这里的“阈值”由参数max_length_for_sort_data控制。当单行数据的“排序相关总长度”超过阈值时，MySQL 启用 rowid 排序；否则使用全字段排序。
一句话概括：全字段排序省了回表但费内存，rowid排序省了内存但多了回表。
这两种策略的执行细节，我分别展开讲。
3. 全字段排序的完整执行链路
3.1 全字段排序做了什么
先用一条具体的SQL做例子：
select user_id, amount, receive_address, created_at from orders where status = 1 order by created_at desc limit 100;如果 MySQL 选择全字段排序，那么它会做这么几件事：
- 在二级索引上扫描满足
status = 1的记录主键。 - 根据主键回表，取出
user_id、amount、receive_address、created_at这几个字段。 - 把这一行数据整体放入 sort buffer。
- 在 sort buffer 里按
created_at做降序排序。 - 排序完成后，直接从 sort buffer 取出需要的字段返回给客户端。
这里最核心的特点：排序过程中携带着完整的查询列。好处是排序一结束，结果数据直接可用，不需要二次查询。坏处也很直观：字段越多、字段越长（比如receive_address这种 varchar），sort buffer 能装下的行就越少，溢出的概率就越大。
3.2 内存不够时会发生什么
假设sort_buffer_size是 1MB，每条参与排序的行有 200 字节，那么 sort buffer 大约能容纳 5000 行。如果status = 1的记录有 10 万行，排序过程就会发生如下事情：
- 前 5000 行进入 sort buffer，先在内存中完成排序；
- 把排好序的结果写入磁盘上的临时文件，形成第一个有序分片；
- 再读入下一批 5000 行，排序后写入第二个临时文件；
- 以此类推，直到所有记录处理完；
- 最后 MySQL 把多个有序文件做归并排序，得到全量有序结果。
这种“分片 + 归并”的流程，本质上就是外部排序的经典思路。需要多少个临时文件，取决于 sort buffer 大小和每行长度。优化器会尽量控制临时文件数量，但在数据量很大的情况下，临时文件数量几十个甚至上百个都不稀奇。
3.3 内存排序与磁盘排序的性能分界线
我在一个模拟项目X上实测过一组数据。表里插入 50 万行订单记录，字段包含user_id（4字节INT）、amount（8字节DECIMAL）、receive_address（约80字节VARCHAR）、created_at（8字节DATETIME）。按created_at排序，不同sort_buffer_size下的耗时差异非常明显：
| sort_buffer_size | 排序耗时（约） | 是否产生磁盘临时文件 |
|---|---|---|
| 256KB（默认） | 约870ms | 是，多个临时文件 |
| 1MB | 约420ms | 是，少量临时文件 |
| 8MB | 约120ms | 否，全程内存排序 |
从这个对比能看到，小缓冲区导致的大量临时文件读写是性能杀手。但8MB之后继续调大，收益就开始递减，因为全部数据已经能装进内存了，更多缓冲并不能减少排序本身的计算量。
提示：判断是否产生了磁盘临时文件，可以借助
SHOW SESSION STATUS LIKE 'Sort_merge_passes';查看归并排序的次数。这个值如果很大，说明频繁落盘，应该考虑调大 sort buffer 或者优化 SQL。
3.4 全字段排序的适用场景
全字段排序比较适合“查询列少、列长度短、单行数据紧凑”的场景。例如：
- 只查
id和created_at两个字段； - 表结构基本是定长字段；
- 每次排序的数据量不大，sort buffer 能轻松容纳。
这种情况下，全字段排序的效率非常高，省去了回表的额外开销。反过来，如果业务上必须返回text、blob或其他超长列，全字段排序会很快把 sort buffer 占满，反而不如 rowid 排序来得稳。
4. rowid排序：用回表换内存的执行过程
4.1 rowid排序为什么会出现
在全字段排序下，MySQL 发现“每行需要排序的总长度”超过了max_length_for_sort_data的阈值，就会改用 rowid 排序。这是优化器基于成本做的自动选择，目的在于减少 sort buffer 的占用、降低磁盘溢出的概率。
max_length_for_sort_data在 MySQL 5.7 默认是 1024 字节，在 MySQL 8.0 默认也是 1024 字节。它的含义是“排序时单行数据允许的最大长度”。一旦预估单行长度超过 1024 字节，避免全字段排序，转入 rowid 模式。
注意：这个参数设置过小会频繁触发 rowid 排序，导致回表开销突增；设置过大又会让 sort buffer 装不了多少行。生产环境中不要盲目调大，而是应该在分析具体 SQL 的单行宽度之后再决定。
4.2 rowid排序的完整步骤
还是用上面的订单查询做例子，但假设receive_address字段很长、单行长度超过了阈值。rowid 排序的执行流程是这样的：
- 扫描满足
status = 1的二级索引，取得主键 ID。 - 回表取出
created_at字段（排序字段），同时带着主键 ID。 - 只把
created_at和主键 ID 放进 sort buffer。 - 在 sort buffer 内按
created_at排序。 - 排序完成后，得到一列排好序的主键 ID。
- 再次回表，根据这些主键 ID 查询完整行数据（即刚才 select 里的
user_id、amount、receive_address等字段），返回给客户端。
与全字段排序相比，第2步只取了排序字段，第6步才补全整行。很明显，rowid 排序在排序阶段更“轻”，但代价是最终多了一次批量回表。
4.3 排序字段较少的执行过程
实际执行时，MySQL 在 sort buffer 中只放排序需要的字段和主键。排序字段可以是单个，也可以是多个。比如order by user_id asc, created_at desc这种多字段排序，rowid 模式下会把这两个排序字段都放进去，再加上主键。
这里有个值得注意的细节：MySQL 的 filesort对多字段排序的支持是有限制的。如果 order by 中的字段来自多个不同的表（比如 join 之后的排序），或者使用了表达式、函数，通常会触发 filesort，而且可能还会伴随Using temporary（临时表）。所以我们现在讨论的 rowid 排序，最好是应用在单表查询或者只对单表字段排序的场景下，效果才最可控。
4.4 rowid排序与全字段排序的本质差异
我把两者放在一张对比表里，方便直接理解：
| 对比项 | 全字段排序 | rowid排序 |
|---|---|---|
| sort buffer 内容 | select 的所有字段 + 排序字段 | 排序字段 + 主键ID |
| 内存占用 | 较大 | 较小 |
| 是否需要回表 | 不需要 | 需要（排序后回表） |
| 触发条件 | 单行长度 ≤ max_length_for_sort_data | 单行长度 > max_length_for_sort_data |
| 适用场景 | 查询列短、数据量小 | 查询列长、数据量大 |
| 总体IO | 较少 | 排序前一次回表 + 排序后一次回表 |
理解这张表，你就能明白为什么 MySQL 不总是选择全字段排序——因为内存永远是稀缺资源。rowid 排序牺牲一次额外的回表，换取更低的缓冲区压力和更少的磁盘溢出，在宽表场景下反而更划算。
5. 参数调优与索引优化：让order by不再拖慢业务
5.1 搞清楚哪些参数真正影响排序性能
除了sort_buffer_size和max_length_for_sort_data，还有一个参数经常被忽略：innodb_buffer_pool_size。它虽然不直接参与 filesort，但影响回表性能。当 rowid 排序触发二次回表时，数据页如果已经缓存在 buffer pool 中，效率就会很高；如果缓存命中率低，每次回表都是一次磁盘读，性能断崖式下跌。
所以，针对 order by 的调优，我通常按以下顺序检查：
- 确认 SQL 是否走了索引，能否在索引中直接消除排序；
- 确认
sort_buffer_size是否满足当前排序数据量； - 确认是否需要调整
max_length_for_sort_data来改变排序策略； - 确认表统计信息是否过旧，需要
ANALYZE TABLE更新统计信息； - 确认磁盘 I/O 能力和 buffer pool 命中率。
其中第1点是最有效的优化手段，没有之一。
5.2 利用索引天然有序，直接避开filesort
排序的本质是让数据有序输出。如果索引中的KEY顺序本身就是排好的，那么按照这个索引扫描出来的数据天然有序，MySQL 就不需要再做 filesort。
举个例子：
alter table orders add index idx_status_created (status, created_at desc);执行这条语句后，下面的查询：
select user_id, amount, created_at from orders where status = 1 order by created_at desc limit 50;在EXPLAIN中的Extra列就不会再出现Using filesort，因为idx_status_created这个联合索引已经保证了在status相同时，created_at是按降序排列的。MySQL 扫到status = 1的第一行之后，直接顺序读下去，天然有序。
这里有一个值得注意的小坑：联合索引里字段顺序很关键。如果写成(created_at, status)或者(status asc, created_at asc)，上述查询依然可能触发 filesort。核心原因在于索引必须满足“最左前缀”和排序方向一致性。
5.3 多个排序字段时，索引设计要“同升同降”
很多开发只对单字段排序有意识，一遇到多字段排序就蒙了。举个常见需求：
select ... from orders order by user_id asc, created_at desc;如果索引设计成idx_user_created(user_id asc, created_at asc)，那么这里created_at的升序排列和 SQL 要求的降序不一致，优化器无法直接利用索引的有序性，只能 filesort。
正确的索引写法应该是：
alter table orders add index idx_user_created (user_id asc, created_at desc);MySQL 8.0 开始支持索引降序定义，这为排序优化提供了更灵活的手段。如果你还在用 MySQL 5.7，那么反向扫描可能无法利用，一般只能通过阶值转换或者反转字段值的方式实现。
提示：索引不是越多越好。每个索引都占用写入时的维护成本。如果表写多读少，那么“利用索引消除排序”之前一定要评估写入价格。
5.4 深分页排序的实战优化
另一个高频问题就是深分页。类似这样：
select user_id, amount, receive_address from orders where status = 1 order by created_at desc limit 100000, 20;这里的排序逻辑不只是“找前20条”，而是“找出所有满足条件的数据，排序后跳过前10万条，再取20条”。数据量一大，排序代价和被跳过的回表开销都会被放大。
我常用的优化方案是“延迟关联 + 覆盖索引”：
select o.user_id, o.amount, o.receive_address from ( select id from orders where status = 1 order by created_at desc limit 100000, 20 ) t join orders o on o.id = t.id;这个写法先在子查询里只查主键，利用联合索引尽可能快地定位需要的主键集合。由于排序需要的字段全部在索引里，子查询不会触发大面积回表。然后拿这20个主键回去关联完整行，回表数量被压到最小。
在某些业务场景下，还可以配合“上一页最后一条记录的游标”代替 offset，把深翻页优化成恒定深度查询。性能差异从秒级降到毫秒级都不夸张。
6. 常见问题与排查技巧实录
6.1 明明按索引字段排序，为什么还是 Using filesort
这是初学者最容易困惑的问题。一个典型的例子：
select user_id, amount from orders order by user_id;此时user_id有索引，但EXPLAIN显示Using filesort。原因很简单：优化器认为这个查询需要全表扫描，返回的行数占总行数的比例很高，直接扫表排序可能比用索引快。利用索引意味着需要额外回表，回表次数多了反而更慢。
换句话说，索引排序不是万能的。要出现利用索引排序的执行计划，通常满足两个条件之一：
- 查询覆盖了排序所需的全部字段（覆盖索引），不需要回表；
- 满足排序字段组合的 where 条件能把结果集过滤到很小的范围。
碰到Using filesort先别急着调参数，看看是不是优化器的选择问题。
6.2 如何判断 sort buffer 是否满足当前负载
在 MySQL 命令行执行：
SHOW SESSION STATUS LIKE 'Sort%';重点看两个值：
Sort_merge_passes：归并排序的次数，越大说明磁盘溢出越严重；Sort_rows：实际排序的行数。
在压力测试或者真实业务高峰期采集几次数据，如果Sort_merge_passes持续偏高，说明默认 256KB 可能不够用。先试着调到 1MB，观察是否明显下降。这里我的个人经验是：别一次性调到 64MB 之类的夸张值。因为每个连接都有独立的 sort buffer，在高并发下内存消耗极大，反而会引发内存不足。
6.3 排序慢但单条SQL快速，如何定位问题
有时候单条 SQL 单独跑没问题，并发一高就慢。这种问题多数不在排序本身，而在于内存争抢。常见的罪魁祸首是：
- 并发连接数过多，每个连接都分配了独立的 sort buffer；
- buffer pool 被频繁淘汰，回表效率下降；
- 磁盘写入临时文件的 I/O 能力饱和。
排查思路：
- 查看
SHOW PROCESSLIST;，找到大量Sorting result状态； - 通过
performance_schema查看内存分配情况； - 如果连接数很高，考虑在业务侧加缓存、降并发，而不是盲目调大单连接参数。
6.4 filesort 导致的磁盘占用问题
如果排序数据量非常大，临时文件会占用临时表空间或 tmpdir 空间。我曾经遇到一个案例：某数据分析任务一次性对 800 万行做排序，导致生成几十个临时文件，把磁盘可用空间直接打满，数据库实例直接转入只读状态。
这种场景的正确解法有两个方向：
- SQL优化：避免全量排序，比如先 filter 缩小结果集；拆分批次执行；
- 参数调整：确保
tmpdir有足够的空间，最好使用独立的快速磁盘。让 sort buffer 适当增大，减少临时文件数量。
顺便提一句：不是所有排序都能通过索引规避。OR条件、IN列表、函数包裹列，这些都会打破索引的有序利用。遇到这种 SQL，最优解往往是在应用层做预排序，或者把数据落到数仓/ES 等专门处理排序的系统中。
7. 一组排序列实验，直观观察两种策略切换
理论讲了这么多，实际操作一次才能留下深刻印象。我在某测试环境下做了一个简单实验，整个过程可以在你自己环境中复现。
7.1 实验环境准备
MySQL 版本：8.0.34
创建一张宽表：
create table sort_test ( id int primary key auto_increment, user_id int not null, amount decimal(10,2) not null, created_at datetime not null, remark varchar(2000) default null, index idx_user_created (user_id, created_at) ) engine=innodb;remark字段故意设成长度为 2000 的 varchar，用于把单行长度推高。- 使用一条存储过程插入 20 万行测试数据：
delimiter $$ create procedure insert_sort_test() begin declare i int default 0; while i < 200000 do insert into sort_test(user_id, amount, created_at, remark) values (i % 10000, i * 0.01, now(), repeat('a', 500)); set i = i + 1; end while; end$$ delimiter ; call insert_sort_test();这样每一行remark至少 500 字符，单行整体长度远超 1024，天然会进入 rowid 排序模式。
7.2 观察不同参数下的排序策略
先保持默认参数执行：
explain select id, user_id, amount, created_at, remark from sort_test order by user_id limit 100;在 Extra 列几乎必然看到Using filesort。
接着切换参数：
set session max_length_for_sort_data = 6000; explain select id, user_id, amount, created_at, remark from sort_test order by user_id limit 100;因为每行长度约 6000 字节以内，全字段排序被允许，sort buffer 单行可容纳。但 20 万行的总数据量依然远超 sort buffer，所以还是会触发归并排序。比较两个执行计划的耗时表现，可以发现 rowid 模式下排序阶段更轻，但最终回表数量多；全字段模式下排序阶段内存消耗更高，但省了回表。
7.3 用 optimizer trace 查看排序选择
MySQL 提供了 optimizer trace，可以精确看到优化器如何选择排序策略：
set session optimizer_trace = 'enabled=on'; select id, user_id, amount, created_at, remark from sort_test order by user_id limit 100; select * from information_schema.optimizer_trace where query like '%sort_test%' \G观察输出中的filesort_inner或者排序相关文本，你会发现优化器会根据max_length_for_sort_data和行长度自动选择，不需要人为干预。
个人经验：在生产环境不要轻易修改
max_length_for_sort_data。修改它很难带来质的提升，反而可能因为切换排序策略导致回表增加。真正需要做的是检查 SQL 是否需要那么多字段，以及索引是否合理。
8. 我在实战中亲历的一次排序性能事故
最后分享一个真实案例，是我在维护某跨平台系统时遇到的。
当时的业务表是一张日志表，每天新增约 300 万行。某个查询要统计最近7天的数据并按时间倒序分页展示。最初版本的 SQL 长这样：
select id, user_id, action, request_url, created_at from access_log where created_at >= date_sub(now(), interval 7 day) order by created_at desc limit 0, 50;当时created_at有单列索引，where条件能过滤出近百万行。但order by created_at desc后的limit 50，让 MySQL 不得不把过滤出的所有行排序，再取前50。这种情况即便走了索引，也无法利用索引顺序，因为id、user_id、action、request_url都不在索引中，必须回表。结果就是慢查询日志天天爆，接口超时率居高不下。
当时我做了两个调整：
第一步，把查询改成覆盖索引优先的子查询，先只查主键排序，再延迟关联：
select l.id, l.user_id, l.action, l.request_url, l.created_at from ( select id from access_log where created_at >= date_sub(now(), interval 7 day) order by created_at desc limit 0, 50 ) t join access_log l on l.id = t.id;第二步，把联合索引调整成(created_at, id)，让排序字段和回表的主键都在索引里。
效果非常明显：接口响应时间从 3 秒左右降到 200ms 以内，慢查询日志里再没出现过这条 SQL。这次经历让我对“排序优化先看索引，再看参数”这句话有了切身体会。
从这之后我排查 order by 性能问题养成了一个习惯：先EXPLAIN看Extra列，再查Sort_merge_passes，最后才动参数。这套流程基本覆盖了绝大多数排序相关的性能问题，也推荐你试一试。
