1. 先用大白话讲明白Using filesort到底是什么1.1 从一条慢SQL说起如果你接触过MySQL执行计划一定见过EXPLAIN输出Extra列里那个“Using filesort”。很多刚入行的开发第一次看到它都会慌以为MySQL把数据写到磁盘文件里排序了赶紧去调各种参数。实际上这个理解既对又不对。先看一个典型场景。有个订单表结构大概是这样的CREATE TABLE orders ( id bigint NOT NULL AUTO_INCREMENT, user_id bigint NOT NULL, order_no varchar(64) NOT NULL, amount decimal(10,2) NOT NULL, status tinyint NOT NULL, create_time datetime NOT NULL, PRIMARY KEY (id), KEY idx_user_id (user_id) ) ENGINEInnoDB;现在要查某个用户最近的订单SELECT id, order_no, amount, create_time FROM orders WHERE user_id 12345 ORDER BY create_time DESC LIMIT 20;执行EXPLAIN看一下Extra列大概率会出现“Using filesort”。这条查询表里就300万行数据user_id12345的订单也就几百条但耗时却要几百毫秒。问题就出在MySQL先用idx_user_id这个二级索引找到user_id12345的全部记录但二级索引里的data page物理顺序是按照user_id组织的同一个user_id的数据聚在一起彼此之间却完全不保证create_time顺序。为了满足ORDER BY create_time DESCMySQL只能把查出来的这批数据单独排一遍序这个动作在EXPLAIN里就显示为Using filesort。换句话说filesort是MySQL自己额外做的一次排序操作。它在内存里做内存不够用就借助磁盘临时文件做归并排序所以叫“file sort”这个历史名字。1.2 filesort不等于磁盘文件排序这里要纠正一个流传很广的误区。很多文章一看到Using filesort就说“这条查询在磁盘上排序了性能极差”。实际上绝大多数情况下只要排序的数据量不大MySQL压根不会碰磁盘。MySQL在执行排序前会分配一块内存缓冲区这个缓冲区的大小由参数sort_buffer_size控制默认通常是256KBMySQL 8.0默认值。排序的数据先全部加载到这块缓冲区里如果所有待排序行都能塞进缓冲区MySQL在内存里排完就返回结果整个过程不会产生任何磁盘文件。只有当待排序的数据量超过sort_buffer_size时MySQL才会把排序的中间结果分块刷到磁盘临时文件里然后对这些临时文件做归并排序。这个刷盘的过程会产生一个状态变量Sort_merge_passes。如果你检查SHOW GLOBAL STATUS发现这个值增长很快那才是真正意义上的“文件排序”在频繁发生。所以准确的表述是Using filesort代表MySQL放弃了利用索引天然有序性来直接返回结果的方案决定自己额外排序。至于这个排序是在内存还是磁盘完成取决于数据量和参数配置。1.3 索引排序与filesort的分水岭同样是ORDER BY为什么有些查询走索引就不用排序因为InnoDB的索引结构天然有序。B树的叶子节点是按照索引键值排序组织的如果查询的排序字段正好是索引定义的一部分且查询条件用到了索引的最左前缀MySQL就可以顺着索引的顺序扫描并直接返回Extra列里显示的是Using index或什么都没显示只是Using where。举个对比例子。把上面的表加一个联合索引ALTER TABLE orders ADD KEY idx_user_create (user_id, create_time DESC);再跑同样的查询EXPLAIN里Using filesort消失了变成了Using index condition。因为idx_user_create这棵B树先按user_id排序user_id相同的情况下再按create_time排序。查询条件里user_id是等值条件那么目标数据落在一个连续的索引区间内MySQL只要在这个区间里正向或反向扫描拿到的数据顺序天然就是create_time顺序根本不需要额外排序。这就是优化filesort的核心思路让索引结构帮你完成排序工作而不是让MySQL把数据捞出来再排一遍。后面第4节会详细展开各种玩法。2. filesort内部是怎么工作的排序缓冲与归并2.1 sort_buffer_size与内存排序如果你想知道filesort真实消耗了多少资源不能只看EXPLAIN得结合状态变量和参数一起看。MySQL在排序前会向连接线程的内存池申请一块sort buffer。注意这个参数是session级别的每个连接都可能分配自己的sort_buffer_size内存。如果连接数很多每个连接都开着256KB的排序缓冲即使没做排序操作这块内存也可能已经预分配了具体看版本实现8.0早期会预分配8.0.12之后有优化按需增长。排序过程大致是这样MySQL根据查询需要排序的字段和查询返回的字段计算出每条“排序记录”的长度。把所有需要排序的行读入sort buffer每条记录保存排序键和部分查询字段或主键引用。当数据量超过sort_buffer_size就把排好序的数据分成一块块写成临时文件每块内部有序。最后把所有临时文件块做归并输出最终有序结果。判断是否发生第3步可以执行SHOW GLOBAL STATUS LIKE Sort_merge_passes;运行一次慢查询后再执行一次SHOW SESSION STATUS对比这个值有没有增加。如果一次也没增加说明排序都发生在内存里性能损耗没有想象中夸张。我见过不少团队一遇到Using filesort就疯狂调大sort_buffer_size从256KB调到10MB结果排序没见得变多快内存反而被大量占满。因为每个连接都分配一块这么大的缓冲区几十个并发就能吃掉几百MB内存。这不是一个可以盲目加大的参数。2.2 双路排序和单路排序如果你在MySQL 8.0之前的版本上排查问题还会遇到一个经典概念双路排序two-pass和单路排序single-pass。双路排序是MySQL 4.1之前的方案做法是只把排序键和行的ROWID放进sort buffer排完序后再通过ROWID回表拿完整数据。这样做sort buffer能装下更多行但排序完成后需要大量随机回表查询IO成本很高。单路排序是改进版把查询需要的所有列都放进sort buffer排完序后直接返回不用二次回表。但代价是每条排序记录变得更长sort buffer能装下的行数变少如果查询列很多、字段很长反而更容易触发临时文件归并。MySQL 8.0内部用max_length_for_sort_data参数控制走哪种策略不过8.0.20之后这个参数被移除了优化器会自动判断。我在实际使用中发现控制查询返回的列数量比调这个参数更有效——SELECT *的查询如果表特别宽比如10个以上字段排序记录变得很长单路排序很可能变成磁盘归并。这里有一个很反直觉的结论减少SELECT返回的列数不只是减少网络传输还能直接改善排序的内存利用率。2.3 一次排序的代价到底有多大我做过一次压测在一个200万行的订单表上按create_time排序取10000条对比两个场景排序方式耗时Sort_merge_passes说明filesortsort_buffer_size256KB约380ms7多次归并IO增加filesortsort_buffer_size2MB约120ms1几乎全程内存走联合索引无filesort约20ms0索引天然有序这个数字说明几个问题。第一确实存在磁盘归并时耗时会成倍增加。第二就算再怎么优化sort_buffer_sizefilesort和索引排序之间仍然有数量级的差距。索引排序只需要顺序扫描B树叶子节点filesort需要额外做比较和交换这个CPU开销是省不掉的。如果你的业务查询无法完全消除filesort至少要把Sort_merge_passes控制在0或非常低的水平。这是判断排序性能健康度的一个重要信号。3. 什么场景最容易触发filesort3.1 ORDER BY不走索引的常见原因ORDER BY字段没有索引这是最直接的原因。很多表只在WHERE条件的字段上建了索引ORDER BY字段完全裸露查询先把满足条件的行捞出来再对这些行排序必然filesort。但更常见的情况是ORDER BY字段有索引优化器却还是选择了filesort。我总结了几种WHERE条件字段和ORDER BY字段不满足最左前缀。比如索引是(a, b)WHERE里有b的等值条件ORDER BY用的是aa和b换位了走不了索引。WHERE条件带范围查询且范围字段和ORDER BY字段在同一索引里。比如索引(a, b)WHERE a 100 ORDER BY b优化器从索引里取出a100的所有行这些行中b的顺序已经被打乱了。WHERE条件包含OR且部分分支没有索引。优化器只能用索引合并index merge或全表扫描排序也变成filesort。ORDER BY多个字段方向不一致。比如ORDER BY a ASC, b DESC在MySQL 8.0之前没有降序索引同一个索引无法同时支持一个升序和一个降序。ORDER BY字段上有函数或表达式。比如ORDER BY DATE(create_time)索引存储的是原始create_time值无法直接用于这个排序需求。遇到这些情况先不要急着骂优化器傻大多数时候是索引设计本身没有涵盖排序需求。3.2 GROUP BY与DISTINCT的隐藏排序GROUP BY和DISTINCT往往不会让你一眼看到filesort因为执行计划里可能显示的是“Using temporary; Using filesort”。这两个操作都涉及去重和分组MySQL实现方式之一是先排序排好序之后连续相同的值就自然聚在一起然后合并。这个排序动作同样可能触发filesort。比如SELECT user_id, COUNT(*) FROM orders WHERE create_time 2024-01-01 GROUP BY user_id;这条查询如果走的是create_time索引那查出来的user_id顺序是乱的GROUP BY需要把相同的user_id聚到一起统计排序就不可避免。针对这类查询优化的思路同样是让索引的键顺序覆盖GROUP BY字段。如果索引是(user_id, create_time)但WHERE条件是create_time范围还是可能失效。这里我通常建议用覆盖索引后面4.3会细说或者改用窗口函数做预处理看具体业务量级选方案。3.3 JOIN关联查询中的排序陷阱多表JOIN时filesort经常藏得很深。我举个实际例子SELECT u.nickname, o.amount FROM users u JOIN orders o ON o.user_id u.id WHERE u.level 3 ORDER BY o.create_time DESC LIMIT 50;这条查询的执行方式很可能是先根据users表level3找出用户再对每个用户去orders表查订单最后把结果汇总起来按o.create_time排序。问题是JOIN的驱动顺序和ORDER BY字段所属的表不一致导致最终结果无法利用任何索引直接排序。优化方向有几种一是让驱动表的索引覆盖WHERE条件和JOIN条件并且ORDER BY字段属于驱动表二是把ORDER BY字段的排序提前到子查询或派生表里做完外层再JOIN。不过要小心MySQL 8.0.34之前具体版本看实际行为对派生表的合并和物化优化不一定能完全保留子查询里的ORDER BY需要测试确认。4. 实战优化让filesort消失的四个方向4.1 联合索引设计WHERE与ORDER BY必须合体这是最核心的技巧一句话总结就是让索引的键顺序完全匹配WHERE等值条件加ORDER BY字段的组合。原则是WHERE里的等值条件字段放最前面。ORDER BY字段紧跟在等值条件后面。GROUP BY字段也可以当作ORDER BY来看待。举例查询条件是WHERE user_id 123排序是ORDER BY create_time DESC索引就应该设计成(user_id, create_time)。这时候user_id是等值条件索引在(user_id)内部对create_time做了排序查询时MySQL只需要定位到user_id123的区间然后选择在索引内部逆序扫描直接输出结果。但如果你再加一个范围条件比如WHERE user_id 123 AND amount 100 ORDER BY create_time那字段顺序需要推敲。amount是范围条件如果把amount放中间比如索引(user_id, amount, create_time)索引在user_id123的同值区域内按amount排序amount100的行内部create_time是乱序的还是会filesort。此时如果优化器选择不走amount条件而只走(user_id, create_time)反而能用索引排序再用索引条件下推ICP过滤amount100。这里就需要用EXPLAIN实测对比不同数据分布结论可能不同。我的经验是索引设计以等值条件加排序列为第一组合范围条件尽量用索引下推或后置过滤去处理。4.2 升降序问题与8.0降序索引MySQL 8.0之前索引的排序方向只有ASC所以ORDER BY a ASC, b DESC这种查询用索引很困难。很多老DBA的折中方案是把b定义成负数存进一个虚拟列或者牺牲一个索引在内存里做反向扫描但效率总归差点意思。8.0引入了降序索引创建索引时可以明确指定方向CREATE INDEX idx_order ON orders (user_id ASC, create_time DESC);这个定义告诉InnoDBuser_id升序排序user_id相同的情况下按create_time降序存储。这样查询WHERE user_id 123 ORDER BY create_time DESC就直接扫描索引叶子节点按顺序返回perfect。顺便说一下MySQL 8.0里的降序索引底层实际上用的是反序存储还是反向扫描官方文档和源码实现有细节差异但对我们使用者来说只要创建时指定了方向优化器就能用它做正确方向的排序不需要关心内部细节。如果你还在用5.7遇到升降序混排的查询建议在代码层先考虑业务上能否接受统一排序方向不能的话再考虑用冗余列或拆查询。4.3 覆盖索引与延迟关联覆盖索引是从filesort里抢回性能的又一大利器。如果SELECT的列全部在索引里MySQL可以只扫描索引而不回表同时索引本身有序等于一套流程直接出结果。把前面的例子改一下CREATE INDEX idx_user_create_cover ON orders (user_id, create_time, amount, order_no);查询SELECT order_no, amount, create_time FROM orders WHERE user_id 12345 ORDER BY create_time DESC LIMIT 20;因为要查的order_no、amount、create_time都包含在idx_user_create_cover里EXPLAIN结果会显示Using index连Using filesort都没有了整个查询就是一次索引范围的顺序扫描非常快。有时候表字段太多无法全部塞进索引这时候可以用延迟关联。先在索引上完成排序只取主键再回表取完整数据。示例SELECT o.* FROM ( SELECT id FROM orders WHERE user_id 12345 ORDER BY create_time DESC LIMIT 20 ) t JOIN orders o ON o.id t.id;内层子查询只访问索引排序在索引或小结果集上完成外层再对20个主键做回表开销极低。这种写法是处理“排序字段少、返回字段多”时的经典方案。4.4 参数调优sort_buffer_size和max_length_for_sort_data当你确认了查询已经尽量用上索引但某些场景下仍然避免不了filesort比如用户自定义的筛选条件排序字段不固定这时候再考虑参数层面的调优。sort_buffer_size这个参数我建议默认256KB起步然后观察Sort_merge_passes。如果这个值频繁大于0把sort_buffer_size调到1MB再观察一次。一般来说1MB到2MB之间能解决绝大多数常见排序场景。不要一上来就调大内存是共享的每个连接分到的越大整体并发能力越差。MySQL 8.0.20之后max_length_for_sort_data被移除但如果你维护的是5.7或8.0早期版本可以留意这个参数。它控制排序记录里能包含的列总长度上限默认1024字节。当查询返回的字段总长超过这个值时MySQL回退到双路排序策略。适当调大可以让它更倾向于单路排序减少回表次数但过大又会导致sort buffer能容纳的行数变少需要取舍。还有另一个参数innodb_sort_buffer_size很多人误解它和filesort有关。这个参数其实影响的是InnoDB在创建或重建索引时的排序缓冲区跟SELECT查询的filesort没有关系别混在一起。5. 真实排查记录与避坑清单5.1 一次典型的位置上报查询优化前阵子帮一个做物流系统的小团队优化数据库他们有个位置上报表记录每辆车每秒上报的GPS位置表数据到了4000万行。核心查询是SELECT lat, lng, speed, course, create_time FROM location_logs WHERE vehicle_id 10086 AND create_time 2024-05-01 00:00:00 AND create_time 2024-05-02 00:00:00 ORDER BY create_time ASC;原本的索引是(vehicle_id)查询结果3300行耗时2.1秒EXPLAIN显示Using filesort。原因很典型vehicle_id等值条件过滤出几百天的数据create_time顺序完全随机MySQL凑齐3300行后统一排序。我的修改方案ALTER TABLE location_logs ADD INDEX idx_vehicle_time (vehicle_id, create_time);改完后再实测耗时降到22毫秒执行计划里Using filesort消失。整个查询变成先定位vehicle_id10086在索引树中的位置再在create_time的范围内顺序读取。一个索引解决了问题这就是联合索引的真谛。有意思的是加了这个索引后即使查询没有ORDER BY create_time只要WHERE条件里包含vehicle_id等值create_time范围索引同样能加速过滤一举两得。5.2 使用EXPLAIN和optimizer trace定位排序路径如果一条查询的EXPLAIN显示Using filesort但你不确定排序到底花了多少代价、能不能优化可以用EXPLAIN ANALYZEMySQL 8.0.18看实际耗时分布。EXPLAIN ANALYZE SELECT lat, lng, speed, course, create_time FROM location_logs WHERE vehicle_id 10086 AND create_time 2024-05-01 00:00:00 ORDER BY create_time ASC;输出结果里会有actual time你可以看到排序步骤实际消耗了多少毫秒也能看到每一步扫描了多少行。如果排序步骤占据了绝大部分时间说明优化索引是必选项。想要更底层的原理分析可以用optimizer traceSET optimizer_trace enabledon; SELECT ... 你的查询 ...; SELECT * FROM information_schema.OPTIMIZER_TRACE; SET optimizer_trace enabledoff;trace里会记录优化器为什么选择filesort、选择的行数估算、是否考虑过某个索引等决策过程。这个工具对排查“为什么有索引却不用”特别有效。5.3 常见误区与经验总结最后整理几个我反复踩过的坑第一个误区一看到Using filesort就觉得必须消灭它。如果排序的数据量很小比如几十行filesort的代价微乎其微为了消灭它去建一个复杂索引反而得不偿失。判断标准是耗时不是只要有filesort就紧张。第二个误区ORDER BY LIMIT一定很慢。实际上如果LIMIT很小filesort可以只排一部分数据就提前终止取决于优化器能不能用top-N堆排序。MySQL 8.0里的filesort对LIMIT做了优化不用对所有行全排序。所以看到EXPLAIN里有filesort再看一眼LIMIT大小别被表象吓到。第三个误区把sort_buffer_size调到极大。有个朋友把sort_buffer_size调成32MB结果高峰期连接数一上来内存直接打满。排序优化优先靠索引参数调优永远只是补充手段。第四个误区忽略覆盖索引对排序的“顺带优化”。如果你发现EXPLAIN里又出现Using filesort又想加索引先把要返回的列收窄看看能否用覆盖索引一并解决。很多时候减少返回列比多建一个索引更实用。第五个误区group by或distinct产生的filesort和order by产生的一样处理。确实排序机制是一样的但group by很多时候可以用索引配合松散索引扫描来优化设计索引时要把group by字段也当成排序键考虑进去。用filesort出现的位置反向推断索引设计缺口这条思路不管在哪个版本、哪种业务里都适用。碰到排序慢先做三件事看执行计划、看Sort_merge_passes、看实际耗时分布再去动索引和参数。按这个顺序排查绝大多数排序问题都能解决得干净利落。 
长河君 · 2026/9/7 20:55:39 · 阅读 1.2k MySQL Using filesort优化：原理、排查与索引设计实战
1. 先用大白话讲明白Using filesort到底是什么1.1 从一条慢SQL说起如果你接触过MySQL执行计划一定见过EXPLAIN输出Extra列里那个“Using filesort”。很多刚入行的开发第一次看到它都会慌以为MySQL把数据写到磁盘文件里排序了赶紧去调各种参数。实际上这个理解既对又不对。先看一个典型场景。有个订单表结构大概是这样的CREATE TABLE orders ( id bigint NOT NULL AUTO_INCREMENT, user_id bigint NOT NULL, order_no varchar(64) NOT NULL, amount decimal(10,2) NOT NULL, status tinyint NOT NULL, create_time datetime NOT NULL, PRIMARY KEY (id), KEY idx_user_id (user_id) ) ENGINEInnoDB;现在要查某个用户最近的订单SELECT id, order_no, amount, create_time FROM orders WHERE user_id 12345 ORDER BY create_time DESC LIMIT 20;执行EXPLAIN看一下Extra列大概率会出现“Using filesort”。这条查询表里就300万行数据user_id12345的订单也就几百条但耗时却要几百毫秒。问题就出在MySQL先用idx_user_id这个二级索引找到user_id12345的全部记录但二级索引里的data page物理顺序是按照user_id组织的同一个user_id的数据聚在一起彼此之间却完全不保证create_time顺序。为了满足ORDER BY create_time DESCMySQL只能把查出来的这批数据单独排一遍序这个动作在EXPLAIN里就显示为Using filesort。换句话说filesort是MySQL自己额外做的一次排序操作。它在内存里做内存不够用就借助磁盘临时文件做归并排序所以叫“file sort”这个历史名字。1.2 filesort不等于磁盘文件排序这里要纠正一个流传很广的误区。很多文章一看到Using filesort就说“这条查询在磁盘上排序了性能极差”。实际上绝大多数情况下只要排序的数据量不大MySQL压根不会碰磁盘。MySQL在执行排序前会分配一块内存缓冲区这个缓冲区的大小由参数sort_buffer_size控制默认通常是256KBMySQL 8.0默认值。排序的数据先全部加载到这块缓冲区里如果所有待排序行都能塞进缓冲区MySQL在内存里排完就返回结果整个过程不会产生任何磁盘文件。只有当待排序的数据量超过sort_buffer_size时MySQL才会把排序的中间结果分块刷到磁盘临时文件里然后对这些临时文件做归并排序。这个刷盘的过程会产生一个状态变量Sort_merge_passes。如果你检查SHOW GLOBAL STATUS发现这个值增长很快那才是真正意义上的“文件排序”在频繁发生。所以准确的表述是Using filesort代表MySQL放弃了利用索引天然有序性来直接返回结果的方案决定自己额外排序。至于这个排序是在内存还是磁盘完成取决于数据量和参数配置。1.3 索引排序与filesort的分水岭同样是ORDER BY为什么有些查询走索引就不用排序因为InnoDB的索引结构天然有序。B树的叶子节点是按照索引键值排序组织的如果查询的排序字段正好是索引定义的一部分且查询条件用到了索引的最左前缀MySQL就可以顺着索引的顺序扫描并直接返回Extra列里显示的是Using index或什么都没显示只是Using where。举个对比例子。把上面的表加一个联合索引ALTER TABLE orders ADD KEY idx_user_create (user_id, create_time DESC);再跑同样的查询EXPLAIN里Using filesort消失了变成了Using index condition。因为idx_user_create这棵B树先按user_id排序user_id相同的情况下再按create_time排序。查询条件里user_id是等值条件那么目标数据落在一个连续的索引区间内MySQL只要在这个区间里正向或反向扫描拿到的数据顺序天然就是create_time顺序根本不需要额外排序。这就是优化filesort的核心思路让索引结构帮你完成排序工作而不是让MySQL把数据捞出来再排一遍。后面第4节会详细展开各种玩法。2. filesort内部是怎么工作的排序缓冲与归并2.1 sort_buffer_size与内存排序如果你想知道filesort真实消耗了多少资源不能只看EXPLAIN得结合状态变量和参数一起看。MySQL在排序前会向连接线程的内存池申请一块sort buffer。注意这个参数是session级别的每个连接都可能分配自己的sort_buffer_size内存。如果连接数很多每个连接都开着256KB的排序缓冲即使没做排序操作这块内存也可能已经预分配了具体看版本实现8.0早期会预分配8.0.12之后有优化按需增长。排序过程大致是这样MySQL根据查询需要排序的字段和查询返回的字段计算出每条“排序记录”的长度。把所有需要排序的行读入sort buffer每条记录保存排序键和部分查询字段或主键引用。当数据量超过sort_buffer_size就把排好序的数据分成一块块写成临时文件每块内部有序。最后把所有临时文件块做归并输出最终有序结果。判断是否发生第3步可以执行SHOW GLOBAL STATUS LIKE Sort_merge_passes;运行一次慢查询后再执行一次SHOW SESSION STATUS对比这个值有没有增加。如果一次也没增加说明排序都发生在内存里性能损耗没有想象中夸张。我见过不少团队一遇到Using filesort就疯狂调大sort_buffer_size从256KB调到10MB结果排序没见得变多快内存反而被大量占满。因为每个连接都分配一块这么大的缓冲区几十个并发就能吃掉几百MB内存。这不是一个可以盲目加大的参数。2.2 双路排序和单路排序如果你在MySQL 8.0之前的版本上排查问题还会遇到一个经典概念双路排序two-pass和单路排序single-pass。双路排序是MySQL 4.1之前的方案做法是只把排序键和行的ROWID放进sort buffer排完序后再通过ROWID回表拿完整数据。这样做sort buffer能装下更多行但排序完成后需要大量随机回表查询IO成本很高。单路排序是改进版把查询需要的所有列都放进sort buffer排完序后直接返回不用二次回表。但代价是每条排序记录变得更长sort buffer能装下的行数变少如果查询列很多、字段很长反而更容易触发临时文件归并。MySQL 8.0内部用max_length_for_sort_data参数控制走哪种策略不过8.0.20之后这个参数被移除了优化器会自动判断。我在实际使用中发现控制查询返回的列数量比调这个参数更有效——SELECT *的查询如果表特别宽比如10个以上字段排序记录变得很长单路排序很可能变成磁盘归并。这里有一个很反直觉的结论减少SELECT返回的列数不只是减少网络传输还能直接改善排序的内存利用率。2.3 一次排序的代价到底有多大我做过一次压测在一个200万行的订单表上按create_time排序取10000条对比两个场景排序方式耗时Sort_merge_passes说明filesortsort_buffer_size256KB约380ms7多次归并IO增加filesortsort_buffer_size2MB约120ms1几乎全程内存走联合索引无filesort约20ms0索引天然有序这个数字说明几个问题。第一确实存在磁盘归并时耗时会成倍增加。第二就算再怎么优化sort_buffer_sizefilesort和索引排序之间仍然有数量级的差距。索引排序只需要顺序扫描B树叶子节点filesort需要额外做比较和交换这个CPU开销是省不掉的。如果你的业务查询无法完全消除filesort至少要把Sort_merge_passes控制在0或非常低的水平。这是判断排序性能健康度的一个重要信号。3. 什么场景最容易触发filesort3.1 ORDER BY不走索引的常见原因ORDER BY字段没有索引这是最直接的原因。很多表只在WHERE条件的字段上建了索引ORDER BY字段完全裸露查询先把满足条件的行捞出来再对这些行排序必然filesort。但更常见的情况是ORDER BY字段有索引优化器却还是选择了filesort。我总结了几种WHERE条件字段和ORDER BY字段不满足最左前缀。比如索引是(a, b)WHERE里有b的等值条件ORDER BY用的是aa和b换位了走不了索引。WHERE条件带范围查询且范围字段和ORDER BY字段在同一索引里。比如索引(a, b)WHERE a 100 ORDER BY b优化器从索引里取出a100的所有行这些行中b的顺序已经被打乱了。WHERE条件包含OR且部分分支没有索引。优化器只能用索引合并index merge或全表扫描排序也变成filesort。ORDER BY多个字段方向不一致。比如ORDER BY a ASC, b DESC在MySQL 8.0之前没有降序索引同一个索引无法同时支持一个升序和一个降序。ORDER BY字段上有函数或表达式。比如ORDER BY DATE(create_time)索引存储的是原始create_time值无法直接用于这个排序需求。遇到这些情况先不要急着骂优化器傻大多数时候是索引设计本身没有涵盖排序需求。3.2 GROUP BY与DISTINCT的隐藏排序GROUP BY和DISTINCT往往不会让你一眼看到filesort因为执行计划里可能显示的是“Using temporary; Using filesort”。这两个操作都涉及去重和分组MySQL实现方式之一是先排序排好序之后连续相同的值就自然聚在一起然后合并。这个排序动作同样可能触发filesort。比如SELECT user_id, COUNT(*) FROM orders WHERE create_time 2024-01-01 GROUP BY user_id;这条查询如果走的是create_time索引那查出来的user_id顺序是乱的GROUP BY需要把相同的user_id聚到一起统计排序就不可避免。针对这类查询优化的思路同样是让索引的键顺序覆盖GROUP BY字段。如果索引是(user_id, create_time)但WHERE条件是create_time范围还是可能失效。这里我通常建议用覆盖索引后面4.3会细说或者改用窗口函数做预处理看具体业务量级选方案。3.3 JOIN关联查询中的排序陷阱多表JOIN时filesort经常藏得很深。我举个实际例子SELECT u.nickname, o.amount FROM users u JOIN orders o ON o.user_id u.id WHERE u.level 3 ORDER BY o.create_time DESC LIMIT 50;这条查询的执行方式很可能是先根据users表level3找出用户再对每个用户去orders表查订单最后把结果汇总起来按o.create_time排序。问题是JOIN的驱动顺序和ORDER BY字段所属的表不一致导致最终结果无法利用任何索引直接排序。优化方向有几种一是让驱动表的索引覆盖WHERE条件和JOIN条件并且ORDER BY字段属于驱动表二是把ORDER BY字段的排序提前到子查询或派生表里做完外层再JOIN。不过要小心MySQL 8.0.34之前具体版本看实际行为对派生表的合并和物化优化不一定能完全保留子查询里的ORDER BY需要测试确认。4. 实战优化让filesort消失的四个方向4.1 联合索引设计WHERE与ORDER BY必须合体这是最核心的技巧一句话总结就是让索引的键顺序完全匹配WHERE等值条件加ORDER BY字段的组合。原则是WHERE里的等值条件字段放最前面。ORDER BY字段紧跟在等值条件后面。GROUP BY字段也可以当作ORDER BY来看待。举例查询条件是WHERE user_id 123排序是ORDER BY create_time DESC索引就应该设计成(user_id, create_time)。这时候user_id是等值条件索引在(user_id)内部对create_time做了排序查询时MySQL只需要定位到user_id123的区间然后选择在索引内部逆序扫描直接输出结果。但如果你再加一个范围条件比如WHERE user_id 123 AND amount 100 ORDER BY create_time那字段顺序需要推敲。amount是范围条件如果把amount放中间比如索引(user_id, amount, create_time)索引在user_id123的同值区域内按amount排序amount100的行内部create_time是乱序的还是会filesort。此时如果优化器选择不走amount条件而只走(user_id, create_time)反而能用索引排序再用索引条件下推ICP过滤amount100。这里就需要用EXPLAIN实测对比不同数据分布结论可能不同。我的经验是索引设计以等值条件加排序列为第一组合范围条件尽量用索引下推或后置过滤去处理。4.2 升降序问题与8.0降序索引MySQL 8.0之前索引的排序方向只有ASC所以ORDER BY a ASC, b DESC这种查询用索引很困难。很多老DBA的折中方案是把b定义成负数存进一个虚拟列或者牺牲一个索引在内存里做反向扫描但效率总归差点意思。8.0引入了降序索引创建索引时可以明确指定方向CREATE INDEX idx_order ON orders (user_id ASC, create_time DESC);这个定义告诉InnoDBuser_id升序排序user_id相同的情况下按create_time降序存储。这样查询WHERE user_id 123 ORDER BY create_time DESC就直接扫描索引叶子节点按顺序返回perfect。顺便说一下MySQL 8.0里的降序索引底层实际上用的是反序存储还是反向扫描官方文档和源码实现有细节差异但对我们使用者来说只要创建时指定了方向优化器就能用它做正确方向的排序不需要关心内部细节。如果你还在用5.7遇到升降序混排的查询建议在代码层先考虑业务上能否接受统一排序方向不能的话再考虑用冗余列或拆查询。4.3 覆盖索引与延迟关联覆盖索引是从filesort里抢回性能的又一大利器。如果SELECT的列全部在索引里MySQL可以只扫描索引而不回表同时索引本身有序等于一套流程直接出结果。把前面的例子改一下CREATE INDEX idx_user_create_cover ON orders (user_id, create_time, amount, order_no);查询SELECT order_no, amount, create_time FROM orders WHERE user_id 12345 ORDER BY create_time DESC LIMIT 20;因为要查的order_no、amount、create_time都包含在idx_user_create_cover里EXPLAIN结果会显示Using index连Using filesort都没有了整个查询就是一次索引范围的顺序扫描非常快。有时候表字段太多无法全部塞进索引这时候可以用延迟关联。先在索引上完成排序只取主键再回表取完整数据。示例SELECT o.* FROM ( SELECT id FROM orders WHERE user_id 12345 ORDER BY create_time DESC LIMIT 20 ) t JOIN orders o ON o.id t.id;内层子查询只访问索引排序在索引或小结果集上完成外层再对20个主键做回表开销极低。这种写法是处理“排序字段少、返回字段多”时的经典方案。4.4 参数调优sort_buffer_size和max_length_for_sort_data当你确认了查询已经尽量用上索引但某些场景下仍然避免不了filesort比如用户自定义的筛选条件排序字段不固定这时候再考虑参数层面的调优。sort_buffer_size这个参数我建议默认256KB起步然后观察Sort_merge_passes。如果这个值频繁大于0把sort_buffer_size调到1MB再观察一次。一般来说1MB到2MB之间能解决绝大多数常见排序场景。不要一上来就调大内存是共享的每个连接分到的越大整体并发能力越差。MySQL 8.0.20之后max_length_for_sort_data被移除但如果你维护的是5.7或8.0早期版本可以留意这个参数。它控制排序记录里能包含的列总长度上限默认1024字节。当查询返回的字段总长超过这个值时MySQL回退到双路排序策略。适当调大可以让它更倾向于单路排序减少回表次数但过大又会导致sort buffer能容纳的行数变少需要取舍。还有另一个参数innodb_sort_buffer_size很多人误解它和filesort有关。这个参数其实影响的是InnoDB在创建或重建索引时的排序缓冲区跟SELECT查询的filesort没有关系别混在一起。5. 真实排查记录与避坑清单5.1 一次典型的位置上报查询优化前阵子帮一个做物流系统的小团队优化数据库他们有个位置上报表记录每辆车每秒上报的GPS位置表数据到了4000万行。核心查询是SELECT lat, lng, speed, course, create_time FROM location_logs WHERE vehicle_id 10086 AND create_time 2024-05-01 00:00:00 AND create_time 2024-05-02 00:00:00 ORDER BY create_time ASC;原本的索引是(vehicle_id)查询结果3300行耗时2.1秒EXPLAIN显示Using filesort。原因很典型vehicle_id等值条件过滤出几百天的数据create_time顺序完全随机MySQL凑齐3300行后统一排序。我的修改方案ALTER TABLE location_logs ADD INDEX idx_vehicle_time (vehicle_id, create_time);改完后再实测耗时降到22毫秒执行计划里Using filesort消失。整个查询变成先定位vehicle_id10086在索引树中的位置再在create_time的范围内顺序读取。一个索引解决了问题这就是联合索引的真谛。有意思的是加了这个索引后即使查询没有ORDER BY create_time只要WHERE条件里包含vehicle_id等值create_time范围索引同样能加速过滤一举两得。5.2 使用EXPLAIN和optimizer trace定位排序路径如果一条查询的EXPLAIN显示Using filesort但你不确定排序到底花了多少代价、能不能优化可以用EXPLAIN ANALYZEMySQL 8.0.18看实际耗时分布。EXPLAIN ANALYZE SELECT lat, lng, speed, course, create_time FROM location_logs WHERE vehicle_id 10086 AND create_time 2024-05-01 00:00:00 ORDER BY create_time ASC;输出结果里会有actual time你可以看到排序步骤实际消耗了多少毫秒也能看到每一步扫描了多少行。如果排序步骤占据了绝大部分时间说明优化索引是必选项。想要更底层的原理分析可以用optimizer traceSET optimizer_trace enabledon; SELECT ... 你的查询 ...; SELECT * FROM information_schema.OPTIMIZER_TRACE; SET optimizer_trace enabledoff;trace里会记录优化器为什么选择filesort、选择的行数估算、是否考虑过某个索引等决策过程。这个工具对排查“为什么有索引却不用”特别有效。5.3 常见误区与经验总结最后整理几个我反复踩过的坑第一个误区一看到Using filesort就觉得必须消灭它。如果排序的数据量很小比如几十行filesort的代价微乎其微为了消灭它去建一个复杂索引反而得不偿失。判断标准是耗时不是只要有filesort就紧张。第二个误区ORDER BY LIMIT一定很慢。实际上如果LIMIT很小filesort可以只排一部分数据就提前终止取决于优化器能不能用top-N堆排序。MySQL 8.0里的filesort对LIMIT做了优化不用对所有行全排序。所以看到EXPLAIN里有filesort再看一眼LIMIT大小别被表象吓到。第三个误区把sort_buffer_size调到极大。有个朋友把sort_buffer_size调成32MB结果高峰期连接数一上来内存直接打满。排序优化优先靠索引参数调优永远只是补充手段。第四个误区忽略覆盖索引对排序的“顺带优化”。如果你发现EXPLAIN里又出现Using filesort又想加索引先把要返回的列收窄看看能否用覆盖索引一并解决。很多时候减少返回列比多建一个索引更实用。第五个误区group by或distinct产生的filesort和order by产生的一样处理。确实排序机制是一样的但group by很多时候可以用索引配合松散索引扫描来优化设计索引时要把group by字段也当成排序键考虑进去。用filesort出现的位置反向推断索引设计缺口这条思路不管在哪个版本、哪种业务里都适用。碰到排序慢先做三件事看执行计划、看Sort_merge_passes、看实际耗时分布再去动索引和参数。按这个顺序排查绝大多数排序问题都能解决得干净利落。