上个月排查客服后台慢SQL，我被一条order by查询折磨了一个下午。MySQL order by性能优化，听起来是老生常谈，可真到线上出问题时，你依仗的绝不是“加个索引”这种一句话经验。SQL本身不长：SELECT * FROM order_info WHERE seller_id = 128744 AND status = 2 ORDER BY created_at DESC LIMIT 20;WHERE条件两个字段都建了单列索引，单表数据量也不过千万级，可高峰期这条查询硬是跑了4.7秒，把一个8核16G实例的CPU直接打满。explain结果里，Extra列明晃晃写着Using filesort。就这一行输出，背后牵扯出的是MySQL排序的完整执行链路、索引与排序的匹配边界、排序缓冲区的取舍，以及业务模型层面的改造思路。
这篇文章适合三类人：每天被慢查询日志轰炸的DBA，写SQL时想知道“为什么这里会排序”的后端开发，以及正在面试中被问到order by原理、想系统补课的同学。我会按“执行原理 -> 索引排序边界 -> filesort底细 -> 排查手段 -> 实战优化 -> 调参与schema建议”这条线往下走，所有结论都基于InnoDB引擎、MySQL 5.7/8.0的实测经验。
1. 一条带order by的SQL，在MySQL里到底经历了什么
1.1 从SQL到结果的完整执行链路
很多人以为order by是存储引擎里顺手完成的，其实排序发生在MySQL server层，跨引擎通用。一条查询从客户端发出，要经过连接器（验证权限）、分析器（词法/语法解析）、优化器、执行器这几个阶段。优化器是最关键的决策者：它根据表的统计信息（行数、索引基数、筛选比例等）判断用哪条路径成本最低，同时决定排序策略。执行器拿到执行计划后，通过存储引擎接口逐行读取满足条件的记录，该回表的回表，然后把需要排序的行交给server层的排序模块。
这里有个8.0用户容易忽略的变化：MySQL 8.0彻底移除了查询缓存，也就是说每次执行都是一次完整的链路，不存在“缓存了就不用排序”的侥幸。我习惯把这个过程类比成整理书架——索引相当于书架按字母排好的标签，习惯了就直接按位置拿书；没有索引排序时，就像把一堆乱书搬到书桌上重新排，书桌放不下还得摊到地板（磁盘临时文件）上慢慢归并。
1.2 三种排序路径：索引有序、filesort和临时表
从执行结果看，一条带order by的查询最终只会落入三种情况：
| 路径 | 是否需要额外排序 | 是否可能回表 | 常见触发场景 |
|---|---|---|---|
| 索引有序扫描 | 否 | 视查询列是否被索引覆盖 | 排序字段满足索引最左前缀 |
| 内存filesort | 是 | 视排序采用单路或双路 | 结果集较小，sort buffer装得下 |
| 磁盘filesort/临时表 | 是 | 是 | 结果集超过sort buffer，或涉及去重/合并 |
索引有序扫描是理想态：B+树的叶子节点天然有序，MySQL沿着索引顺序读下去，输出自然有序，完全不需要执行器再做排序动作，explain里也不会出现Using filesort。内存filesort则是把需要排序的记录全部读进sort buffer，在内存里完成排序后返回，速度尚可但CPU成本不低。磁盘filesort是性能杀手：sort buffer装不下时，MySQL会把数据分块排序后写到磁盘临时文件，再通过归并排序合并，每多一次磁盘I/O，查询延迟都成倍上升。
临时表则是另一条分支。当SQL里同时出现DISTINCT、GROUP BY、UNION这类需要“先合并再去重”的操作时，MySQL可能先建一张临时表把这些中间结果装进去，再在临时表之上做排序。所以从慢查询日志里看到Using temporary和Using filesort同时出现时，问题往往不只是排序本身，还牵扯到临时表的设计。
这里给出我的结论：order by优化的本质，就是尽量把查询变成“第一种情况”；如果做不到，就尽量让filesort发生在内存里而不是磁盘上。后面四章全部围绕这条主线展开。
2. 索引排序的边界与陷阱：最左前缀、范围查询和反向扫描
2.1 最左前缀对order by的约束
联合索引是最常用的排序优化手段，但它的排序能力受最左前缀规则严格约束。假设表结构：
CREATE TABLE trade ( id BIGINT PRIMARY KEY, seller_id INT NOT NULL, order_no VARCHAR(32), status TINYINT, amount DECIMAL(12,2), created_at DATETIME NOT NULL, KEY idx_seller_status_time (seller_id, status, created_at) ) ENGINE=InnoDB;基于 idx_seller_status_time，以下几类order by可以直接利用索引排序：
- WHERE seller_id = ? ORDER BY status：等值条件消耗第一列，后续status有序；
- WHERE seller_id = ? AND status = ? ORDER BY created_at：两列等值都消耗掉，created_at在索引内有序；
- WHERE seller_id = ? ORDER BY status, created_at：第一列等值后，其余列按索引顺序排列。
不能利用索引排序的典型写法：
- WHERE seller_id = ? ORDER BY created_at：跳过了status这一列，B+树只在seller_id、status、created_at三列同时有序。你可以把索引看成嵌套的目录册，第一层按seller_id分组，第二层按status分组，第三层才按created_at排序。只指定seller_id后，索引只是把多组(status, created_at)子序列拼在一起，其中每组内部有序，但组与组之间并不是全局有序，所以排序必须靠filesort完成。
- ORDER BY created_at LIMIT 10：没有条件消耗seller_id，整棵索引树根本没有按created_at全局有序，自然用不上。
最常用的判断口诀是：把WHERE里的等值条件按索引从左到右消耗，消耗完之后，剩余的排序字段必须正好接在索引的下一列，不能跳列，方向还要一致。
2.2 范围条件与混合排序方向：那些让人措手不及的filesort
等值条件消耗索引列之后，一旦进入范围条件，排序能力就会断掉。比如WHERE seller_id = ? AND status > 2 ORDER BY created_at，status用到了范围扫描，那么status之后的所有列都无法再保证有序，created_at排序只能走filesort。这是个特别容易踩的坑：很多人以为“created_at在索引里，肯定能用”，却没有意识到它前面的列被范围条件截断了。
另外一类是排序方向的问题。B+树索引默认按升序组织，如果你写ORDER BY created_at ASC，可以顺着扫；写ORDER BY created_at DESC，理论上也能反着扫，8.0.13之后优化器对反向扫描的支持更完善，单列降序排序通常能直接利用索引。但一旦出现混合方向，比如ORDER BY status ASC, created_at DESC，5.7和8.0早期版本就无法通过同一个索引同时满足两个方向，必须filesort。要解决这种场景，8.0可以建立真正意义上的降序索引：
ALTER TABLE trade ADD KEY idx_status_time_desc (status ASC, created_at DESC);这样ORDER BY status ASC, created_at DESC就能天然走索引。我在8.0.32上验证过，混合方向的排序从Using filesort变成Using index，性能提升非常明显。
2.3 反向扫描与8.0降序索引的实战价值
反向扫描的实用性比想象中大。社交App的时间线、电商订单倒序展示，几乎都是ORDER BY created_at DESC，而很多人的索引还是按ASC建的。在支持反向扫描的版本里，这个查询本身也能走索引，所以不要一看到DESC就默认“要filesort”。真正的分水岭是混合方向和多列排序。如果你要优化的SQL是升序降序混用，8.0请直接建降序索引；5.7只能靠调整业务排序方向来规避，或者干脆用覆盖索引让filesort的成本低到可以接受。
还有一个容易被忽略的细节：排序稳定性。MySQL官方并不承诺order by结果的稳定性，尤其是使用非唯一键排序时，相同键值的记录返回顺序可能随版本、数据分布变化。如果你业务上需要“先按时间倒序，同时同一时间的记录也要顺序稳定”，最稳妥的做法是在排序列后面补一个唯一键，比如ORDER BY created_at DESC, id DESC。这既稳定了顺序，又让联合索引的排序能力更容易被利用。
3. filesort的底细：sort buffer、临时表与磁盘归并
3.1 sort buffer到底是什么
filesort不是一把梭把整张表读进内存再排，而是有明确的缓冲机制：
- 执行器读取满足WHERE条件的行；
- 从行中抽取排序字段和查询需要的列，写入sort buffer；
- 当sort buffer满，MySQL对当前缓冲区内数据做一次内存排序，把排序结果写成一个临时chunk到磁盘；
- 继续读取后续行，重复2和3；
- 全部读完后，对多个磁盘chunk做归并排序，最终得到完整有序结果。
这里的sort buffer大小由sort_buffer_size控制，默认256KB（部分版本默认值略有差异）。它是个会话级内存，不是全局共享的，意味着每个并发连接都有自己的sort buffer。如果一条SQL要排序的数据量超过buffer，Sort_merge_passes状态变量就会增长，每次增长都代表一次磁盘溢出和归并。所以我在排查排序性能时，第一个看的指标就是Sort_merge_passes。
一个非常有用的细节是，如果SQL里带LIMIT N，比如ORDER BY created_at LIMIT 10，MySQL的排序模块会针对这个场景使用priority queue（优先队列）算法，只保留当前最小的N行，并不需要对全部候选行做完整排序。这意味着即使候选集很大，只要LIMIT较小，内存占用和控制成本都可能低于预期。这也是为什么很多优化建议里强调“排序尽量配合LIMIT”。
3.2 单路排序与双路排序：宽字段的经典博弈
网络上关于filesort的文章，十有八九会提到“单路排序”和“双路排序”。这套机制在5.7及更早版本里确实存在，由max_length_for_sort_data参数决定：当一行需要排序的数据总长度超过这个阈值时，MySQL采用老式的双路排序，只把排序键和主键放进sort buffer，排序完成后根据主键回表读取完整行；数据总长度小于阈值时，采用单路排序，把所有需要的列一起放进sort buffer，排完直接输出，省掉回表。
这套机制在8.0.20之后被移除了，MySQL统一了排序策略，不再让用户在单路和双路之间纠结。但了解它仍然有用，因为很多生产环境还在运行5.7，而且“宽行导致排序内存膨胀”这件事本身没有消失。我见过一个真实的例子：一张表有十几个varchar字段，开发图省事写了SELECT * FROM xxx ORDER BY score LIMIT 20，单行数据1000多字节，5.7判断超过max_length_for_sort_data后走双路排序，排序键加上主键只占很小内存，看起来好像没问题，但双路排序多了一次全表回表，加上WHERE条件本身过滤性不强，回表开销直接把查询拖垮。后来改成只查需要的列，同一SQL耗时从3秒降到200毫秒。
3.3 临时表为什么比filesort更可怕
临时表和filesort经常一起出现，但临时表的代价通常更高。什么时候会产生临时表？我把高频场景列一下：
- GROUP BY：8.0之前还自带隐式排序，本来只想去重统计，结果多了一次排序；
- DISTINCT：结果去重需要先放临时表；
- UNION：默认会去重，除非用UNION ALL；
- ORDER BY的字段不在SELECT的GROUP BY列里，且无法用索引解决；
- 排序或查询涉及TEXT/BLOB类型字段，MySQL需要为它们建临时表存储。
MySQL的临时表分内存临时表和磁盘临时表。内存装不下时会自动落盘，而一旦落盘，读写就是磁盘I/O，和sort buffer溢写一样致命。我处理过一个报表SQL：SELECT client_id, COUNT(*) FROM logs GROUP BY client_id ORDER BY cnt DESC，logs表有text字段参与输出，结果MySQL必须建临时表，临时表超过max_heap_table_size后落盘，整个查询跑了30多秒。改成先按client_id聚合，应用层再做排序，或者去掉text字段的排序，性能问题立刻没了。
记住一个判断原则：explain的Extra里如果出现Using temporary，先考虑能不能用索引替代临时表；如果不行，就考虑让临时表留在内存里。和filesort一样，临时表最怕的就是落盘。
4. 从explain到performance_schema：定位排序瓶颈的完整链路
4.1 explain结果里那些排序暗号
explain是定位排序问题的第一工具，重点看type、key、Extra三列。先说Extra：出现Using filesort意味着本次查询有额外排序动作；出现Using temporary意味着用了临时表；两者同时出现属于“排序+临时表”最坏组合。如果Extra里有Using index且没有Using filesort，说明覆盖索引让排序和取数都在索引里完成，这是最理想的状态。
type列决定访问路径。常见值从好到差依次是const、ref、range、index、ALL。index在这里有迷惑性：它扫的是整个索引，但未必是高效的，因为可能是索引全扫描而非定位后的扫描。比如ORDER BY created_at LIMIT 100，如果只有单个created_at索引，execution type可能是index，Extra没有filesort，看起来很美，但它是把整个索引从头到尾扫一遍再取100条，代价随表大小线性增长。
一条经常看的经验：排序优化的成就感不是“消除了Using filesort”，而是“让查询需要的行数和扫描路径同时变小”。我曾见过同事加了一堆索引把所有慢查询都改成Using index，结果总索引空间比表还大，写放大严重。排序优化要在性能和成本之间找平衡。
4.2 用状态变量和performance_schema量化排序开销
explain只给方向，要量化还得靠状态变量。最常用的三条：
- Sort_rows：执行排序的行数，反映排序规模；
- Sort_merge_passes：磁盘溢写和归并次数，持续增长说明sort buffer不够；
- Sort_range / Sort_scan：说明排序动作的触发方式。
SHOW SESSION STATUS LIKE 'Sort_%';如果需要更细的耗时分布，可以查performance_schema：
SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT / 1000000000 AS total_ms FROM performance_schema.events_stages_summary_global_by_event_name WHERE EVENT_NAME LIKE '%sort%';8.0.18以后还有一颗银子弹：EXPLAIN ANALYZE。它不光是预测执行计划，而是真实执行SQL并输出每个节点的实际耗时。比如：
EXPLAIN ANALYZE SELECT * FROM trade WHERE seller_id = 3281 AND status = 2 ORDER BY created_at DESC LIMIT 20;输出里会明确显示Sort节点的actual time，是内存排序还是磁盘排序，一眼就能看出来。这是我最近强烈推荐的习惯：先explain看方案，再EXPLAIN ANALYZE看实测。
4.3 一个完整案例：从4.7秒到80ms的排查过程
回到开头的那个客服后台慢SQL。我的排查顺序是这样的：
第一步，explain查看执行计划。发现type=ref，命中了seller_id的单列索引，但Extra有Using filesort。这说明过滤部分没问题，排序无法利用索引。
第二步，查Sort_merge_passes。SHOW SESSION STATUS LIKE 'Sort_merge_passes'显示为几十次，说明排序规模远超sort buffer，已经发生了磁盘溢写。为什么？因为单列索引只完成了seller_id的过滤，created_at排序需要把满足条件的可能几十万行全部读出来排序，而客服后台是按天倒序查，数据量很容易积累到几十万行。
第三步，修改索引结构。把单列索引替换为联合索引(seller_id, status, created_at)，让排序字段直接吃索引尾巴。改完explain，Extra里的Using filesort消失，type变为ref，扫描行数从几十万降到几千。
第四步，EXPLAIN ANALYZE确认。Sort节点的实际耗时从秒级降到个位数毫秒，整条SQL从4.7秒降到80ms。
这里最关键的一步是第三步，但最容易被忽略的是第二步。如果排序字段没有吃上索引，不能只看explain的Using filesort就去建索引，先算清楚“要排序的数据量有多大、是否溢写磁盘”，这样才能判断到底该优化访问路径还是优化排序本身。
5. 深翻页、覆盖索引与业务模型改造：三类排序优化实战
5.1 深分页：LIMIT 1000000, 20 为什么慢到离谱
列表页做分页，最容易被攻击的写法是这样的：
SELECT * FROM trade WHERE seller_id = 3281 AND status = 2 ORDER BY created_at DESC LIMIT 1000000, 20;逻辑上它只要第1000001到第1000020条，但MySQL无法像数组那样直接跳到第100万个偏移量。就算order by走了索引，它也必须从索引的最开始或范围起点一直数到第100万个位置，扫描1000020行，丢弃前面100万行。如果order by还没走索引、走的是filesort，那更惨：它得把满足条件的所有行都排序完，再丢弃前100万行。B+树索引的叶子节点是链表结构，天然不支持按位置随机偏移，这正是深分页的根源。
针对这种场景，工程上有两种主流优化：延迟关联和keyset游标。
延迟关联的核心思路是先走覆盖索引拿到本页需要的20个主键，再回表取完整行，把“扫描100万行”变成“扫描索引100万行，但只回表20行”：
SELECT tr.* FROM trade tr INNER JOIN ( SELECT id FROM trade WHERE seller_id = 3281 AND status = 2 ORDER BY created_at DESC, id DESC LIMIT 1000000, 20 ) tmp ON tr.id = tmp.id ORDER BY tr.created_at DESC, tr.id DESC;子查询里排序和分页需要的字段都在索引(seller_id, status, created_at, id)上，是索引扫描，不会触发filesort；外层只对20行做一次轻量排序再回表。实测三五百万的深分页，这种写法通常能把几十秒的查询压到几百毫秒。
如果是“下一页”按钮式的产品而不是“跳页”，可以彻底绕过偏移量，改用keyset游标，把上一页最后一条记录的排序键记下来，下一页直接从这个位置往后找：
SELECT * FROM trade WHERE seller_id = 3281 AND status = 2 AND (created_at < '2024-06-01 10:00:00' OR (created_at = '2024-06-01 10:00:00' AND id < 100123)) ORDER BY created_at DESC, id DESC LIMIT 20;这种方案无论翻多少页，扫描量都不变，是深分页的最佳解法。前提是你愿意和产品协商，把“跳页”改成“流式加载”。
5.2 覆盖索引：让过滤、排序、取数一次完成
排序优化的另一个高效手段是覆盖索引。它的原理很简单：如果查询要返回的列全都包含在索引里，MySQL就不需要回表，直接扫索引树就能把数据查完；配合排序字段也在索引中，整条查询可以在索引这一棵树内自洽完成。
还是订单表举例，列表页高频查询是：
SELECT id, order_no, amount, created_at FROM trade WHERE seller_id = 3281 AND status = 2 ORDER BY created_at DESC LIMIT 20;如果只建(seller_id, status, created_at)联合索引，过滤和排序都解决了，但order_no、amount还需要回表。把这几个高频显示的列追加进索引：
ALTER TABLE trade ADD KEY idx_cover (seller_id, status, created_at, order_no, amount);这时explain里不仅没有Using filesort，还会出现Using index，回表完全消失。不过我要泼一盆冷水：覆盖索引是把双刃剑。索引列越多，写入和更新时维护索引的代价越大，存储空间也越大。我的经验是只对“高频且列数可控”的查询做覆盖设计，别为了覆盖把所有列都塞进索引。索引不是越宽越好，是越“贴合高频查询”越好。
另外注意索引内字段放置的顺序：先等值条件列，再排序列，最后才是要覆盖的返回列。范围条件列一旦放进去，它后面的排序列就废了，这一点和前面2.2节讲的范围截断完全一致。
5.3 别让业务给数据库出难题：ORDER BY RAND()之类的改造
有些SQL慢，根源不在数据库，而在业务提了一个数据库不擅长解决的问题。最经典的就是ORDER BY RAND() LIMIT 1。它的语义是“在整张表里随机取一行”，MySQL的实现方式是对全表做一个filesort，而且因为排序键是随机函数生成的值，无法用任何索引，数据量大时必定磁盘溢写。我在一个300万行的活动表上测试过，随机抽一条的SQL跑了2.8秒，流量稍微上来实例就报警。
如果业务只是想“大致随机取一条”，完全可以绕开全表排序：
-- 先取主键的范围 SELECT MIN(id), MAX(id) INTO @min_id, @max_id FROM trade; -- 在范围内随机一个值，再定位到最近的一行 SET @target = FLOOR(RAND() * (@max_id - @min_id + 1)) + @min_id; SELECT * FROM trade WHERE id >= @target ORDER BY id LIMIT 1;这个方案只扫一条记录，缺点是主键有空洞时随机分布不均匀，且抽到的总是“距随机值最近的记录”，不是字面意义上的等概率。但对绝大多数营销抽奖、首页推荐场景，这个误差完全可以接受。真要更均匀的随机，可以在应用层维护一张密集的自增id表，先随机取一个连续id再回表，代价只是多了张几百行的小表。
我真正想说的是：DBA能优化的不止是索引。把“不分页的列表”“必须实时的排行榜”“要在千万级表上做随机抽样”这类需求挡在数据库外面，本身也是性能优化的一部分。很多时候我和业务方聊十分钟，把“必须实时”改成“缓存5分钟”，比任何索引调优都有效。
6. 参数取舍与schema设计层面的全局建议
6.1 sort_buffer_size到底该不该调大
先说结论：没有统一的“最佳值”，只有配合监控的合理配置。sort_buffer_size是会话级内存，每个连接都会分配一块独立的排序缓冲区。默认值通常为256KB，不少DBA一见到排序慢就把它调到8MB甚至64MB，结果高并发下内存瞬间爆炸——1000个活跃连接，每个8MB就是8GB，再厚的机器也扛不住。
我的调优流程是以下三步：
- 先看Sort_merge_passes是否持续增长。如果长期为0，说明当前缓冲区足够，调大纯属浪费内存；
- 如果持续大于0，先尝试优化SQL和索引，把排序规模降下来；
- 确认索引无法再压减排序数据量后，才去调整会话级的sort_buffer_size，从1MB起步，观察Sort_merge_passes有没有显著下降。
修改方式有两种：
-- 全局生效（只影响新连接） SET GLOBAL sort_buffer_size = 1024 * 1024; -- 会话生效（只影响当前连接） SET SESSION sort_buffer_size = 1024 * 1024;生产环境我更推荐改会话级别，或者只针对特定慢查询的账号统一设置，别全局开大。MySQL文档里对sort_buffer_size有一个明确的警告：增大这个参数要非常谨慎，因为它可能让你误以为解决了问题，实际上只是把磁盘I/O换成了内存占用，而内存是不可压缩资源。
6.2 字符集排序规则：一个容易被忽略的比较成本
排序的本质是比较。数字和日期比较快如闪电，字符串比较却要逐字符按排序规则计算。业务表如果默认是utf8mb4，拿一个varchar(64)的流水号做排序，每次比较都要走utf8mb4的字符映射，开销自然不小。8.0默认的utf8mb4_0900_ai_ci比5.7时代的utf8mb4_general_ci更规范，但某些场景下计算成本并不低。
实战中的建议很朴素：
- 能用数字、日期做排序键，就不要用字符串；
- 字符串排序尽量选短字段，超长字段可以先冗余一个hash列用于排序；
- 如果确实是字符串且业务上不区分大小写，可以用binary排序规则或考虑改成latin1/ascii类列；
- 避免对TEXT/BLOB做排序，这类字段参与排序基本都会触发临时表。
我见过一个案例：订单表用varchar(32)存“订单时间戳”，开发图省事用它做排序和过滤，结果排序慢。后来把数据迁移成了datetime类型列，只改了类型，同样SQL秒变快，就是因为避免了字符串比较的额外开销。别小看这一层，排序十万行字符串的CPU消耗是日期的数倍。
6.3 新表设计时，提前做好的三件事
等到线上慢查询日志堆起来再优化，永远是被动的。新表设计阶段提前做好三件事，能省掉后面大半的排序调优：
第一，明确每个高频查询的排序方向，索引的方向跟着排序走。排序是倒序就建倒序索引，8.0支持降序索引，5.7尽量用覆盖索引兜底。
第二，把“过滤等值列、排序列、范围列、需要覆盖的返回列”排好序去建联合索引。等值条件列放在最前面，排序列紧跟其后，范围条件列要非常谨慎地放，因为它会截断后面的排序能力。
第三，从产品层面约定分页模型。大表一律不接受跳页式深分页，用keyset游标；列表页固定排序键，不允许前端动态换排序字段。如果业务必须支持任意列排序，那就把数据放到分析型存储里，别为难OLTP数据库。
我自己的项目规范里还有一条：任何慢查询从发现到优化完成，必须闭环记录“优化前explain -> 优化后explain -> 耗时对比”三段信息。没有这三段，下次遇到同类问题你还是得从零开始猜。
排序优化这件事，我做下来最深的体会是：真正的收益往往不在最后一公里的索引调整，而在你把“为什么要排序、能不能不排序、能不能少排点”这些前置问题想清楚的时候。每次排查慢排序，我都先问自己这三个问题，把框架立住，再去动执行计划和索引，基本不会走偏。
