## 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默认是256KBMySQL 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这种 varcharsort 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_id4字节INT、amount8字节DECIMAL、receive_address约80字节VARCHAR、created_at8字节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 bufferbuffer 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) ) engineinnodb;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 enabledon; 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最后才动参数。这套流程基本覆盖了绝大多数排序相关的性能问题也推荐你试一试。