做后端开发的应该都遇到过这种场景线上接口刚上线时跑得飞快数据量一涨一条简单的order by查询直接把接口拖到了超时告警。很多人第一反应是“加索引”但索引加上了、EXPLAIN一看还是Using filesort也有人上来就调sort_buffer_size调完却发现内存压力上去了慢查询还是稳稳地留在慢日志里。这篇文章我想把 MySQL 的order by处理从头到尾完整串一遍重点回答几个被反复问烂了的问题MySQL 内部到底是怎样完成一次排序的为什么明明有索引排序还是慢filesort是什么是不是真的要落盘“调优”到底调什么才有意义内容分四块推进先讲清楚order by的两条执行路径和原理再深入filesort内部把排序缓冲区、排序算法这些机制讲透然后进入实操给出真正有效的索引优化和参数调整方法最后整理我在实际排查中常见的几个问题以及对应的定位思路和避坑经验。这篇东西适合所有被慢查询折磨过的开发和维护同学不管你对 MySQL 了解多深我都尽量用大白话把机制讲明白同时给到能直接落地的方法。1. 先搞清 order by 的两条执行路径order by慢不慢根源在于 MySQL 执行排序时选了一条什么路。核心两条路一条是走索引直接拿有序数据一条是自己开辟内存或临时文件来做真正的排序也就是filesort。这两条路的成本差距通常不是一个量级而是好几倍甚至几十倍。1.1 走索引MySQL 最理想的一条路MySQL 的 InnoDB 引擎用的是 B 树索引叶子节点本身是按索引键值有序排列的。这意味着如果你查询的排序字段恰好命中了某个索引的有序性MySQL 根本不需要自己再去排一次序直接沿着索引顺序扫描返回数据就行。比如这样一条查询SELECT * FROM t WHERE status 1 ORDER BY create_time DESC LIMIT 50;假设表上建了联合索引(status, create_time)那么数据在索引中就是先按status分组、再按create_time倒序排列的。MySQL 利用这个索引定位到status1的那一段然后直接按照create_time的倒序一路读下去读满 50 条就收工。整个过程不需要排序也不需要等你算完所有符合条件的行再去排——这就是走索引的核心价值排序动作被彻底消除而且可以利用索引的有序性配合 LIMIT 提前终止扫描。实际开发的时候我最常用来确认是否走索引的方式就是EXPLAINmysql EXPLAIN SELECT * FROM t WHERE status 1 ORDER BY create_time DESC LIMIT 50; ------------------------------------------------------------------------------------ | id | select_type | table | type | key | key_len | rows | Extra ------------------------------------------------------------------------------------ | 1 | SIMPLE | t | ref | idx_status_createtime | 2 | 200000 | Using index condition ------------------------------------------------------------------------------------注意看Extra列。如果EXPLAIN的结果里没有出现Using filesort说明排序被索引消化掉了这种情况通常不需要再做额外的排序优化。如果Extra里出现了Using filesort那就要注意下一步的排序是 MySQL 自己来做的这就要看第二条路了。这里有个常见的误区只要在order by的字段上建了索引就一定不用排序。这是不对的。索引要能被用到必须满足最左前缀原则而且还要求where和order by能用上同一个索引。如果你的where条件用了一个普通索引order by字段又想用另一个索引那么 MySQL 大概率还是会自己排序。这一点在后面优化部分会详细展开。1.2 真正的 filesort大多数慢查询的真相当你看到EXPLAIN的Extra列里出现Using filesort说明这次查询的排序动作已经无法避免——MySQL 会把查询结果行按排序字段组织起来放到内部排序机制里去处理。很多人被 “filesort” 这个名字误导以为它一定要把数据写到文件里。“file” 这个单词确实让人联想成磁盘文件但实际上filesort并不一定真落盘MySQL 会优先把需要排序的数据放到内存里处理内存放不下了才会把中间结果写到磁盘上的临时文件再通过归并排序完成全部数据的排序。filesort的触发条件其实相当宽泛我总结一下最常见的情况order by的字段跟where条件使用的索引不是同一个索引或者完全没索引order by字段不属于某个联合索引的最左前缀排序字段上使用了函数、表达式导致索引无法直接提供有序性查询需要回表而回表成本太高优化器权衡后宁可先排序order by和group by/distinct混用产生了临时表排序的需求。注意Using filesort不等于“这个查询一定很慢”但它是一个强烈的信号代表你的排序可能需要 MySQL 消耗额外的内存、磁盘 IO 和 CPU 资源。优化order by的第一步永远是先看懂这条Extra信息。真正麻烦的地方在于一旦进入filesortMySQL 的排序性能就受制于两个关键变量参与排序的数据量和排序缓冲区的配置。数据量越大排序的动作就越重缓冲区越小越容易被拖到磁盘上做归并排序。所以理解filesort的内部细节才是优化慢排序的第二步。2. 深入 filesort 核心排序缓冲区与算法细节filesort的完整过程并不是一句“把数据拿出来排一下”这么简单。MySQL 在内部会执行一套相对固定的流程先根据查询需要从表或索引中读取排序字段和所需的行数据存入排序缓冲区如果缓冲区放不下就分批处理、写入临时文件最后对所有临时文件做归并排序得到最终的有序结果。这个流程里有几个关键点直接影响性能掌握了它们你就知道调参和优化到底该从哪里下手。2.1 sort_buffer_size排序的“内存战场”sort_buffer_size是 MySQL 给每个排序操作分配的一块内存缓冲区。你可以把它理解成你工作桌上能摊开文件的区域文件少的时候全部放桌上没问题文件多了桌子放不下就只能开到地上、再堆到隔壁房间每次还要来回搬——速度自然就下来了。排序缓冲区默认值通常是 256KB不同版本、不同配置会有差异这个值对很多查询来说确实不大。当filesort过程中发现缓冲区不够用时MySQL 会把数据拆成多个“块”每个块在内存里排好序后写入磁盘临时文件最后再把所有临时文件用归并排序合并成最终结果。这个归并过程有一个很关键的统计指标Sort_merge_passes。你可以通过下面这条命令看到SHOW GLOBAL STATUS LIKE Sort%;或者针对当前会话执行完慢查询后看SHOW SESSION STATUS LIKE Sort%;Sort_merge_passes表示排序过程中因为内存不够而触发的临时文件归并次数。这个数字如果持续大于 0说明排序过程中反复发生了磁盘读写这时候sort_buffer_size的调整才有实际意义。如果Sort_merge_passes一直是 0说明当前所有排序都在内存里完成了此时你盲目调大sort_buffer_size基本就是浪费内存。调大这个参数要特别小心。sort_buffer_size是按会话分配的不是全局共享的一块空间。每个连接、每次排序都可能申请一块独立的缓冲区。如果一个实例有几百个并发连接每个连接都把这个参数调得很大内存一下子就会被吃光。同时这个参数不支持动态按需增长你设置多少MySQL 就可能实际占用多少。实战建议以 1MB2MB 为起点逐步调大同时观察Sort_merge_passes是否明显下降。如果调大到一定程度后归并次数不再变化就不要再加了。一般生产环境设置在 1MB4MB 之间是比较稳妥的区间。2.2 排序模式的变化从双路到单路再到 8.0 的简化早些年 MySQL 的filesort有两种排序模式很多老文章都会讲到双路排序和单路排序。双路排序Two-pass sort第一次只把排序字段和行 ID 读入缓冲区排好序后再根据行 ID 回表读取所需的完整数据行。这样排序占用的内存小但回表次数多。单路排序Single-pass sort直接把排序字段和查询需要的全部列都读入缓冲区排序完成后直接输出结果省掉回表但需要更大的内存空间。这两种模式的切换在旧版本里由max_length_for_sort_data这个参数控制当排序涉及的行数据总长度超过阈值时MySQL 就会退回到双路排序。但到了 MySQL 8.0这个参数被移除了排序行为变得简单且统一尽可能把需要的列都读进缓冲区一次排序完成。这其实是好事省去了很多让人困惑的配置项。但别高兴太早这里反而引出一个更重要的优化点既然单路排序会把“查询需要的全部列”都读入缓冲区那么你 SELECT 出来的列越多越宽缓冲区消耗就越大。很多人写查询习惯性SELECT *这放在小表上没感觉一旦排序行数多起来内存缓冲区就会迅速被一堆你压根不用的字段撑爆触发磁盘归并性能直线下降。所以从排序优化的角度尽可能只 SELECT 你真正需要的列永远是最立竿见影的手段之一。我见过一个真实案例一张表有 30 多个字段其中几个是几 KB 的文本字段开发同学一条分页查询直接SELECT *再order by单条查询跑了 8 秒。后来改成只选 5 个必要字段同样的排序耗时降到 800 毫秒。没改一条 SQL 的排序逻辑也没调整任何参数性能提升 10 倍。2.3 排序缓冲区到底是如何被占用的理解sort_buffer_size消耗的不只是排序字段而是“参与排序的行”这点非常关键。MySQL 在排序时会把查询需要返回的每一行数据都完整放进缓冲区。这里的“完整”用 8.0 的话说就是排序字段 SELECT 出来的其他字段。所以sort_buffer_size的占用和以下因素直接相关参与排序的行数SELECT 的字段数量与字段宽度排序字段的数量和类型varchar 字段排在前面长度越长占的空间越大。这推导出一个有意思的结论即使你有 1GB 的排序缓冲区参与排序的行有几十万行、每行又要带上几个 text 字段缓冲区照样会被打爆。相反如果每行只有几十个字节几万行数据256KB 的缓冲区可能也够用了。所以优化排序时缩小“行宽”往往是比调整缓冲区更划算的做法。到这里机制层面的东西算是讲完了。接下来要把这些原理转化成实际的优化路径这也是整篇文章的重点部分。3. order by 性能优化的实操方法很多人把“优化order by”等同于“加索引”或者等同于“调大sort_buffer_size”。其实真正的优化优先级应该是先考虑用索引消除排序再考虑缩小参与排序的数据量最后才考虑调参数。这个顺序不能反反了就是典型的头疼医头、脚疼医脚。3.1 索引优化让排序成为“附带品”用索引优化排序核心就一句话让order by字段的有序性直接由索引来提供。这句话听着简单但落实起来有两条铁律第一order by字段必须和where条件字段组成同一个联合索引且顺序要符合最左前缀。举个例子SELECT * FROM t WHERE status 1 ORDER BY create_time DESC;这条查询里where等值匹配status排序字段是create_time。如果只建(status, create_time)联合索引那么索引本身就是先按status排、再在status相同的组内按create_time排的正好满足查询需要排序动作直接消除。反过来如果你建的是(create_time, status)索引先按create_time排再按status排对于where status 1 ORDER BY create_time这种查询来说就没法直接用了MySQL 还是得自己排序。第二排序方向要一致。MySQL 8.0 之前对索引的升序和降序支持比较有限如果索引是升序的你又强制ORDER BY create_time DESC部分场景下索引就没法用了。8.0 支持索引的倒序遍历情况好很多但在联合索引里升降序混用依然可能让索引失效。比如ORDER BY status ASC, create_time DESC这时候严格来说MySQL 8.0 可以从索引的status ASC和create_time DESC两个方向同时处理前提是索引也是这么定义的但如果你给索引定义的是两个字段都升序那么create_time DESC这一段的处理可能还是会有额外的排序开销。那怎么判断索引优化是否生效还是那句话EXPLAIN里看不到Using filesort就说明排序被索引吃掉了。这里我再强调一下覆盖索引的额外收益如果索引本身包含了查询需要的所有列MySQL 连回表都不需要直接扫描索引就返回数据这条路是最快的。在你优化order by的时候可以顺带考虑把 SELECT 的字段放进索引里实现“索引覆盖”。SELECT id, status, create_time FROM t WHERE status 1 ORDER BY create_time DESC LIMIT 50;联合索引(status, create_time, id)或者(status, create_time)加上主键就能让这条查询完全不回表。3.2 查询改写缩小排序的“战场范围”不是所有场景都能靠索引消除排序比如你要对多个字段排序、字段上还要套函数、或者排序字段本身来自多表连接的结果集。这时候改 SQL 的思路就派上用场了。第一个思路是让排序更晚发生。如果你不需要所有数据的排序结果只是取前 N 条那不妨把排序尽量推迟到数据量被过滤到足够小以后再做。比如先通过子查询把符合条件的海量数据缩小到一个临时结果集再对这个结果集排序-- 不太好的写法 SELECT * FROM t WHERE status IN (SELECT id FROM another_table WHERE valid 1) ORDER BY create_time DESC LIMIT 20; -- 更可控的写法先缩小范围再排序 SELECT * FROM ( SELECT * FROM t WHERE status IN (SELECT id FROM another_table WHERE valid 1) LIMIT 2000 ) AS sub ORDER BY create_time DESC LIMIT 20;第二种写法虽然结果语义上不完全等价取决于业务是否能接受先截断再排序但在很多业务场景下是可以接受的比如排行榜、最新动态列表等数据量大但只需要头部少量数据。这比直接让 MySQL 对所有符合条件的数据做完整排序要划算得多。第二个思路是避免让宽字段参与排序。如果业务上需要对一个很长的 VARCHAR 字段排序或者需要 SELECT 大量文本字段排序缓冲区会被快速打满。碰到这种情况可以考虑把宽字段拆分到附属表主查询只排序窄字段再按主键回查宽字段。说白了就是让排序操作的“行”尽量短小精悍。第三个思路是检查 ORDER BY 与 GROUP BY/DISTINCT 的混用。GROUP BY本身就需要排序或者使用哈希DISTINCT也涉及去重排序两者一旦和order by混在一起很容易生成内部临时表。可以在EXPLAIN的Extra列看有没有Using temporary如果有说明排序发生在临时表上成本更高。这种情况下要么改写 SQL 把group by的结果范围缩小要么增加临时表大小参数tmp_table_size、max_heap_table_size但这个参数调整也只是治标最根本的还是要减少进入临时表的数据量。3.3 参数调优什么时候调 sort_buffer_size 才有用前文提过调sort_buffer_size之前先看Sort_merge_passes。这里我再给一个更完整的判断流程Sort_merge_passes 0所有排序都在内存完成调大缓冲区没有实际收益Sort_merge_passes 0且持续增长说明频繁触发磁盘归并可以适当调大缓冲区调大到Sort_merge_passes不再下降说明内存已经不是瓶颈继续调大无意义。另外还要注意除了sort_buffer_size还有一个关联参数值得关注innodb_buffer_pool_size。为什么它和 order by 性能有关系因为filesort之后往往要回表读取数据行。如果 InnoDB 的 Buffer Pool 太小回表会频繁发生磁盘读排序速度会被“读数据”这一环拖住。反过来Buffer Pool 足够大、数据都在内存中时回表就会快很多。回答一个很多人纠结的问题sort_buffer_size到底设多少合适我的建议是如果你不是一个纯排序类的 OLAP 应用这个参数就别往大了调。这个参数其实是“线程级”的不是共享内存池。它的设计初衷是“够用就好”。很多生产系统里把sort_buffer_size从 256KB 调到 4MB就是很合理的操作直接调到 64MB 甚至 128MB在高并发下几乎等于自杀式设置内存会在瞬间被连接数乘以缓冲区大小这个公式吃干抹净。3.4 联合索引优化实战一个完整案例光讲理论不够落地我拿一个实际改造过程来模拟一遍。假设有张订单表orders结构大致如下orders(id, user_id, status, order_amount, create_time)现有查询SELECT id, user_id, order_amount FROM orders WHERE status 1 ORDER BY create_time DESC LIMIT 50;改造前表上有两个独立索引idx_status和idx_create_time。WHERE status 1会用到idx_status过滤出 20 万行然后再对这 20 万行做filesort全表扫描回表取数耗时约 1.8 秒。改造方案删除idx_status和idx_create_time两个单列索引改成联合索引idx_status_create_time(status, create_time, id)。改造后MySQL 通过联合索引直接定位到status1的数据段索引内数据已按create_time倒序排列按索引顺序读取前 50 条且因为索引里包含了id、status、create_time再加上主键InnoDB 二级索引叶子节点自带主键值此次查询可以通过覆盖索引直接获取id、status、create_time仅order_amount需要回表。查询耗时降到几十毫秒。这个案例说明联合索引的设计要把 where 等值条件放在前面order by 排序字段放在后面最后再加覆盖列这基本是最通用的排序优化套路。4. 常见问题与排查技巧实录原理和优化方法讲完了落到实际操作时还会有一堆具体情况。下面把我这些年排查order by慢查询时遇到的高频问题和解决思路整理出来按场景拆开讲方便你对照排查。4.1 索引明明建了为什么还是 Using filesort这个现象可以说是排障区出现率第一的问题。索引建了但EXPLAIN还是给你来一句Using filesort通常有四个原因最典型的原因是where 和 order by 字段不在同一个索引里。比如where status 1用的是idx_statusorder by create_time用的设想是idx_create_time但优化器在一个查询里只能选择一条索引路径来走你没法同时用两个索引的有序性。解决办法就是建前面说的(status, create_time)联合索引。第二个原因是排序字段用了表达式或函数。例如ORDER BY DATE(create_time)、ORDER BY LENGTH(name)这种写法索引根本无法提供有序性因为索引里存的是原始值而不是函数值而函数的引入会使得原本索引顺序不再有效。MySQL 8.0 虽然支持ORDER BY指定索引生成的表达式索引但在一般开发场景里尽量避免对排序字段做额外处理才是根本解法。第三个原因是字符集和排序规则的影响。如果表和字段使用 utf8mb4 字符集默认排序规则是utf8mb4_general_ci或utf8mb4_0900_ai_ci字符串排序对大小写和重音符号的处理逻辑很复杂。尤其当你在 ORDER BY 里对字符串字段强制指定了 COLLATE那索引可能直接失效老老实实filesort。第四个原因是数据分布让优化器觉得“索引排序不划算”。如果where条件过滤出的行数占全表的比例太高优化器算一笔账发现走索引回表的成本可能高于全表扫描filesort它就会主动放弃索引的有序性。这种场景下你可以尝试强制索引或者调整查询逻辑但更根本的思路还是缩小扫描范围。4.2 ORDER BY LIMIT 分页慢深翻页问题这是另一个高频场景。很多业务都会写这样一条分页查询SELECT * FROM t ORDER BY create_time LIMIT 100000, 20;表面看是一条普通分页实际上 MySQL 为了拿到第 100001100020 条数据需要先把前 10 万条都找出来并排序再丢弃前 10 万条。数据量越大、页数越深这条 SQL 越慢而且你通常会发现sort_buffer_size调多大都没用因为问题根本不在排序内存而在排序后要丢掉的数据太多。应对深翻页有几条实用思路使用游标分页也就是基于上次查询的最后一条记录的排序字段值继续往下查SELECT * FROM t WHERE create_time 2023-05-20 12:00:00 ORDER BY create_time DESC LIMIT 20;这样 MySQL 可以利用索引直接定位到上次的位置避免排序前遍历大量无关数据。前提是排序字段唯一性足够建议加上主键组合防止同值数据被跳过。如果必须用数字分页可以尝试先只取主键再回表取完整数据-- 先取主键再联表查完整记录 SELECT t.* FROM t JOIN ( SELECT id FROM t ORDER BY create_time DESC LIMIT 100000, 20 ) AS tmp ON t.id tmp.id;因为子查询里只排主键和排序字段行非常“瘦”排序缓冲区的压力小很多速度往往会快一大截。4.3 字符串字段排序注意两个坑第一个坑是字符串里存数字。比如一个varchar字段存了“1”、“10”、“100”你打算ORDER BY这个字段得到的结果会是 “1”、“10”、“100”——因为字符串排序是按字符逐位比较的不是按数字大小。如果你确实想按数字排序通常需要ORDER BY CAST(field AS SIGNED)但这种写法又会让索引失效属于两难选择。最好的做法是在设计表时该用数字类型的字段就不要用字符串类型来凑合。第二个坑是中文排序。MySQL 默认的 utf8mb4 排序规则对中文的排序结果往往不是按拼音或笔画来的而是按字符的编码值排的。如果你的业务要求按首字母排序单靠 SQL 层面做是不现实的一般要在表设计时增加一个拼音首字母的辅助字段或者在应用层排序。大家在做中文名称字典表、通讯录等业务时一定要提前想清楚这一点别上线后被“奇怪的排序结果”坑个措手不及。4.4 排查工具组合EXPLAIN 之外还能看什么EXPLAIN是定位 order by 问题的第一板斧但它只告诉你有没有Using filesort不会告诉你排序花了多长时间、消耗了多少临时文件。所以要真正定位问题我一般会连续看三个层面的信息。第一层是EXPLAIN本身。重点看type、key、rows、Extra四列。rows是预估的扫描行数如果rows显示几十万甚至百万那就算有索引排序工作量也可能不小。第二层是profiling。MySQL 的 profiling 可以统计一条查询内部各阶段耗时SET profiling 1; -- 执行你的慢查询 SHOW PROFILES; -- 查看具体查询各步骤耗时 SHOW PROFILE FOR QUERY 1;在输出里你会看到一个叫sorting result或者filesort的阶段这个阶段的耗时就是排序本身的时间。第三层是全局状态变量和慢日志配合。慢日志里打开了log_queries_not_using_indexes可以帮助你找出没有有效索引参与的查询状态变量Sort_merge_passes、Sort_rows、Sort_range_count则能告诉你排序有没有落盘、排序了多少行。排查的时候把这三种信息拼在一起基本就能判断出是索引没设计好、还是数据行过宽、还是参数配置不合理。4.5 调优还不起作用时最后的兜底手段有时候你把索引也建了、查询也改了排序还是慢。这种时候我通常会考虑下面两个极端手段一是让排序结果“缓存”起来。如果查询的排序结果变化频率很低比如每日榜单、月度统计排行那么完全可以在业务层做结果缓存或者用一张结果表定期刷新。MySQL 再快也快不过不查它。二是考虑换一种存储引擎或组件。如果你的场景本质是“海量数据排序取 TopN”这种需求用 MySQL 硬扛不如考虑引入 Rediszset、ClickHouse 这类对排序场景更友好的组件。我一直认为MySQL 不是万能钥匙它擅长的是事务型查询面对大型分析型排序需求选择更合适的工具本来就是一种合理的优化。最后一个系列的经验是任何参数调优都必须压测验证不要凭感觉。同一个排序查询在开发环境、测试环境、生产环境的表现可能完全不同这受数据量、数据分布、硬件 IO 能力、并发数等多重因素影响。每次调整完用慢查询日志或者压测工具量化对比一下耗时变化再决定要不要继续加大力度这样你的优化动作才有迹可循。我在实际踩坑中最大的体会是order by性能问题绝大多数情况下不是 MySQL 的“排序慢”而是你的索引设计或 SQL 写法导致排序成本暴增。先把Using filesort消灭掉再考虑缓冲区调参最后再考虑换组件这个顺序基本不会错。最后再分享一个小技巧平时写 SQL 就养成只用必要字段的习惯别让排序缓冲区为那些压根不会用到的列买单这个习惯成本最低收益却最稳定。