做后端开发这么多年order by大概是每个人最早接触、却最晚搞懂的一个 MySQL 功能。表面上看它就是一条 SQL 末尾加个排序实际上一旦数据量上来、索引设计不合理你会发现简单的排序能直接把数据库拖垮。很多朋友来问我慢查询排查就是一条 select 加个 order by怎么就扫描了全表还要 filesort这类问题遇到多了我觉得很有必要把 MySQL 的排序机制从原理到优化完整拆一遍。这篇东西适合所有正在跟慢查询作斗争的后端同学也适合刚入门、想知道 explain 里Using filesort到底意味着什么的人。我会把自己在线上环境踩过的坑、调过的参数、改过的 SQL 全部写出来尽量让每个结论都有依据而不是网上说这样快。1. order by 的底层原理排序到底发生在哪一层1.1 一条最简单的 order by 查询执行链路是什么样的先搞清楚一条select ... order by ...从客户端发出去到返回结果中间经历了多少环节。很多人的认知停留在MySQL 把数据取出来排序再返回这个理解方向没错但粒度太粗导致排查问题时无从下手。完整的执行链路大概是这样的客户端把 SQL 发到 server 层经过连接器、分析器、优化器之后生成执行计划交给执行器。执行器负责从存储引擎层读取数据而order by对应的排序动作可能发生在 server 层也可能被优化器下推到索引扫描本身。换句话说排序并不是一个独立的、必然存在的执行步骤它取决于优化器怎么选择执行计划。这里要引入两个在 explain 输出里经常见到的关键字Using filesort和Using index。Using index是好消息表示查询能用索引覆盖连回表都不用更别说排序了Using filesort则意味着 MySQL 需要额外开辟一段内存区域来对结果集排序这个filesort 听起来像文件排序实际上一开始并不一定落盘它可以在内存里完成。关键点在于filesort 不是 MySQL 的某个插件而是一套排序算法的封装。它内部会先尝试在sort_buffer_size指定的内存缓冲区里完成排序只有当待排序数据量超过缓冲区容量时才会把中间结果写入磁盘临时文件再用归并排序把多个分片合并。真正拖垮性能的往往是后面这个落盘过程。1.2 什么时候能走索引排序什么时候必须 filesort优化器的判断逻辑并不复杂如果排序字段本身就在某个索引中而且排序方向也跟索引方向一致那么按索引顺序扫描就能天然拿到有序结果不需要额外排序。这就是所谓的索引排序也是所有 order by 优化的最终目标。但要触发索引排序有几个前置条件必须同时满足排序字段必须满足索引的最左前缀原则且排序列与索引列顺序一致。查询中的 where 条件如果也使用了同一个索引那么排序字段必须能和 where 字段在索引上连续衔接不能中间断了。排序方向要和索引方向一致。MySQL 8.0 之前索引默认都是升序如果你order by id desc理论上要反向扫描老版本 MySQL 会在某些场景下放弃索引排序退化成 filesort8.0 引入了降序索引才真正解决这个问题。举个例子表里有索引idx_a_b(a, b)where a 1 order by b就能走索引排序因为 a 等值条件下 b 在索引里天然有序但where b 1 order by a就不能因为 b 不在最左边索引对 b 的排序在 a 没被约束时是无序的。这就像电话簿按姓氏名字排序你想查所有名字叫张三的人并按姓氏排序那只能全本翻一遍重排。当索引排序走不了优化器就退而求其次选择 filesort。filesort 并不意味着一定慢在小结果集场景下它可能比索引扫描还快——因为不需要维护复杂的索引结构直接对一小撮数据排个序完事。所以看到 explain 里有Using filesort先不要慌先看它处理的数据量再决定要不要优化。2. filesort 的核心机制单趟排序与双趟排序2.1 两种排序算法的本质区别MySQL 的 filesort 实现根据排序数据的体积会在单趟排序和双趟排序之间做选择。这个知识点很少有人讲透但理解了它你就能明白为什么有时候order by慢到无法接受。双趟排序是老版本的默认策略思路是第一趟只把排序字段 行指针读入排序缓冲区排好序之后第二趟再根据行指针回表去取完整的行数据。它的好处是缓冲区能容纳更多的排序键坏处是回表次数多如果排序结果集很大随机 IO 会非常恐怖而且每一行要读两次。单趟排序则是在第一趟就把排序字段和查询需要的所有列一起读进缓冲区直接对完整数据排序省掉了回表这一步。这是 MySQL 4.1 之后引入的优化绝大多数场景下都比双趟排序快。但它有个致命缺陷同样的缓冲区能容纳的行数变少了。一旦数据量超过sort_buffer_size就必须落盘做归并这时单趟排序反而会因磁盘 IO 更频繁而更慢。MySQL 到底选哪种主要由一个老参数max_length_for_sort_data控制。当查询需要读取的所有列的总长度排序字段 select 的列大于这个值MySQL 就退化为双趟排序。这个参数默认值是 4096 字节很多老项目上线后从没调过但表的字段一多、varchar 一长单趟排序的条件很容易就不满足了。2.2 排序缓冲区sort_buffer_size 和 max_length_for_sort_data 怎么调先说sort_buffer_size它是每个线程私有的排序内存不是全局共享的。所以不能盲目调大——如果有 200 个并发连接都在做 filesort每个连接分配 2MB 排序缓冲区瞬间就是 400MB 内存。通常建议设置在 256KB 到 2MB 之间具体要看你的结果集大小。你可以用information_schema里的状态变量来观测实际排序使用量而不是拍脑袋调参。再说max_length_for_sort_data我见过很多 DBA 把它调到 8192 甚至更大理由是让尽量多的查询走单趟排序。这个思路本身没错但忽略了落盘归并的代价。我的建议是先测量你的典型排序 SQL 的单行长度如果普遍超过 4096可以适度上调但不要超过 8KB。更关键的其实是让查询只取需要的列少用select *这样单趟排序的容量自然就上去了。注意sort_buffer_size是每会话独立分配的不是全局共享的。调整它之前先评估连接数和内存总量否则内存暴涨的代价远大于排序节省的时间。2.3 排序方向、字符集与排序规则的影响还有一个很多人忽略的细节order by的排序规则不是简单按字节比大小它受字段的 collation排序规则影响。utf8mb4_general_ci 和 utf8mb4_unicode_ci 对同一批字符串的排序结果可能不同而且不同 collation 的字段之间做排序MySQL 可能无法直接使用索引导致额外的转换和 filesort。跨字符集字段排序更是重灾区。比如一张表里两个字段一个 utf8、一个 latin1order by如果涉及这两个字段的比较MySQL 必须先把它们转成同一个字符集去比较这一转换就意味着索引失效。这类问题在 explain 里经常表现为明明有索引却依然Using filesort而且Extra列有时会提示字符集转换相关的信息。排查时可以用show create table检查字段字符集是否统一。3. 排序临时表与慢查询的深层逻辑3.1 哪些场景会额外产生临时表和排序order by不止出现在最朴素的单表查询里。一旦 SQL 里混入group by、distinct、union、join排序和临时表的关系就变得复杂了。以group by为例如果 group by 的字段不是索引列MySQL 默认要对分组结果排序这就会触发 filesort。如果你根本不在意分组结果的顺序别忘了在 SQL 里明确写order by null来告诉优化器别排了。这个技巧在统计类报表里非常实用很多老开发都不知道实际上一条order by null就能省掉一段完全没必要的排序。union也是隐形排序大户。默认情况下union合并结果集时要去重而去重的一种实现方式就是先排序再比对相邻行。如果你确认两个子查询结果不可能重复直接改成union all不仅省去排序还省掉了去重的临时表开销。我看到过太多查询union和union all的结果完全一样但执行时间差了几十倍。多表 join 的排序就更有意思了。如果 join 用的是索引嵌套循环NLJ驱动表的访问顺序可能跟最终排序要求不一致MySQL 常见策略是先把 join 结果放进临时表再对临时表排序。这个临时表 filesort的组合在 explain 的 Extra 列里会同时出现Using temporary和Using filesort这是最需要警惕的信号因为磁盘临时表一旦产生性能基本就是断崖式下跌。3.2 临时表落盘内存临时表与磁盘临时表的切换MySQL 8.0 之前临时表有内存和磁盘两种形态。内存临时表默认使用 MEMORY 存储引擎一旦数据超过tmp_table_size就会自动转为磁盘临时表默认是 MyISAM 或 InnoDB 临时表。磁盘临时表的性能比内存慢几个数量级这是很多慢查询的幕后黑手。MySQL 8.0 之后引入了 TempTable 引擎内存临时表不够用时会先使用内存映射文件再逐步膨胀。虽然机制变了但避免临时表的优化方向没变。你在排查慢 SQL 时如果看到Created_tmp_disk_tables状态值突增基本可以确定有查询在批量制造磁盘临时表。我自己的排查习惯是先看慢日志里Rows_examined和Rows_sent的比值再看 explain 的 Extra。如果Using temporary和Using filesort同时出现我会优先想办法走索引覆盖 group by 或 order by 的字段其次考虑改写 SQL 结构比如用 join 代替子查询或者把大 group by 拆成多个小查询。4. 性能优化实战从 explain 到最终 SQL 改写4.1 用 optimizer_trace 精确观察排序决策很多人只会看 explain却不知道为什么优化器选择了 filesort。其实 MySQL 提供了optimizer_trace这个神器能直接把优化器的决策过程摊开给你看。开启方式很简单SET optimizer_trace enabledon; -- 执行你的查询 SELECT * FROM orders ORDER BY create_time DESC LIMIT 1000; -- 查看追踪结果 SELECT * FROM information_schema.OPTIMIZER_TRACE\G SET optimizer_trace enabledoff;追踪结果里会包含filesort_decision、sort_buffer_size、sort_algorithm等关键信息。你甚至能看到优化器估算的排序行数、每行长度以及它预测的成本值。这比瞎猜是不是数据量太大要准确得多。举个例子有一次我优化一个订单列表接口explain 里显示Using filesort但表很小、索引也建了怎么都想不通。开了 optimizer_trace 才发现原来排序字段是 varchar 类型的订单号而 where 条件里用函数截断了字符串导致原本的索引前缀匹配失效优化器被迫降级成 filesort。这种问题只靠 explain 是看不出来的。4.2 索引设计的关键参数与取舍order by 优化的核心思路很简单让排序字段成为索引的一部分而且是在 where 字段之后的连续段。这个原则说起来容易做起来需要结合具体业务权衡。比如订单表常见的查询模式是where user_id ? order by create_time desc limit 20那么建idx_user_create(user_id, create_time)就是教科书式的答案。user_id 等值过滤走索引create_time 在 user_id 确定的分支内天然有序limit 20 直接取前 20 条无需排序也无需回表取更多行。但加索引不是免费的。每个索引都会拖慢写入速度、占用磁盘空间。所以业务上要区分高频和低频排序字段。我见过最典型的反面教材是有人为了以防万一给 20 多个字段都建了单列索引结果排序 SQL 依然走不了索引排序——因为单列索引之间是孤立的优化器根本没法定制一条先过滤再排序的路径。判断一个排序字段是否该进索引我的经验是看两个指标一是这个排序查询的 QPS 高不高二是排序结果集大不大。低频 小结果集的排序直接让 filesort 用内存扛住就行不值得为其加索引高频 大结果集才是索引优化的目标。小结果集的 filesort 往往只有几十毫秒加索引反而增加写放大这是很多性能优化专家的共识也符合先量化再优化的原则。4.3 实战案例一深分页 order by 的组合拳深分页是 order by 性能优化里最经典也最难的场景。order by create_time desc limit 100000, 20这类 SQL如果只建了 create_time 索引MySQL 依然要先扫描并排序前 100020 行然后丢弃前 100000 行最后返回 20 行。数据量越大丢弃的开销越离谱。针对深分页我常用的方案是延迟关联。核心思路是先用覆盖索引快速定位目标行的主键再回表取完整数据SELECT a.* FROM orders a INNER JOIN ( SELECT id FROM orders ORDER BY create_time DESC LIMIT 100000, 20 ) b ON a.id b.id;内层子查询只需要索引列和主键走覆盖索引扫描的时候不需要回表自然就不需要为大字段排序付出额外代价。外层 join 只回表取 20 行回表开销被控制在最小范围。如果业务上允许更彻底的方案是游标分页也就是把limit offset改成where create_time 上一页的最后一条时间 order by create_time desc limit 20。这种方式不依赖深分页每一页的扫描行数都是固定的性能非常稳定。但它要求排序字段唯一且连续否则会出现漏数据。时间字段通常有重复需要用(create_time, id)联合条件来保证唯一性WHERE (create_time ?) OR (create_time ? AND id ?) ORDER BY create_time DESC, id DESC LIMIT 20;4.4 实战案例二多表 join 结果集排序多表 join 的排序优化难度比单表高一个量级因为最终结果集的排序字段往往来自非驱动表。我趟得最深的一个坑是两张千万级表 join 后按另一张表的更新时间排序。最初的 SQL 写法是先把两张表 join 完再对 join 结果集排序。explain 里Using temporary; Using filesort赫然在列查询耗时要好几秒。优化方案是先查出符合条件的驱动表主键和排序字段再 join 回原表取完整数据。这个思路的本质是把排序提前到 join 之前。如果排序字段属于小表或索引表优先让小表带排序条件参与 join减少大表参与排序的数据量。反之如果排序字段属于大表就要确保它所在的表是驱动表且排序条件能走索引。MySQL 8.0 的 hash join 出来后join 本身的效率提升了不少但排序问题依然存在。记住一个原则join 的次数越少排序越可控。能用冗余字段解决的就不要在查询里实时 join 排序。业务上适当做字段冗余比如在订单表里冗余一个商品名称看起来破坏了范式却能把排序 join变成一个单表 order by性能收益极其可观。4.5 覆盖索引与延迟关联的完整链路覆盖索引是 order by 优化里含金量最高的一招。当查询的所有列都包含在某个索引中时InnoDB 可以直接从索引叶子节点拿到全部数据连回表都省了更不需要为排序额外折腾。典型的例子是把select id, user_id, create_time和where user_id ? order by create_time组合到一个联合索引idx_user_time(user_id, create_time, id)上。但覆盖索引的容量问题不可忽视。索引列越多B树叶子节点能容纳的行数越少索引体积越大写入成本越高。所以覆盖索引只适合高频且列较少的排序查询不能无脑把所有字段都塞进索引。延迟关联是覆盖索引思想的延伸。它的核心是内层只查索引覆盖的主键和排序列外层再根据主键回表取完整数据。这样排序发生在索引数据上内存占用小排序速度快回表次数也控制在最小范围内。配合前面说的深分页场景延迟关联几乎是必杀技。5. 常见问题与排查技巧实录5.1 五个高频误区和反模式看到 filesort 就加索引。如果结果集小于几千行filesort 在内存里就是几毫秒的事加索引反而拖慢写入。先看Rows_examined再决定。对排序列用函数比如order by DATE(create_time)。一旦对字段做函数运算索引排序直接失效只能 filesort而且无法用覆盖索引。这种场景建议把时间字段拆出日期冗余列或者在业务层做格式化。排序字段和 where 字段不在同一个索引上。建了idx_user(user_id)又建了idx_time(create_time)where 用 user_idorder by 用 create_time优化器两个索引都用不上排序能力只能 filesort。正确做法是建联合索引。order by与limit的顺序没想清楚。limit在排序之后才截断order by必须对所有候选行排序所以先 where 缩范围、再 order by、再 limit看起来 SQL 顺序一样但索引设计直接决定了候选行的数量。忽视排序方向。老版本 MySQL 对order by desc的索引支持不力8.0 之前的版本如果排序字段需要反序最好在索引设计时就把字段建成降序索引或者接受 filesort 的现实。5.2 慢排序的排查工具与步骤遇到 order by 慢我的一般排查顺序是看慢日志锁定 SQL记录Rows_examined、Rows_sent、Lock_time。explain看type、key、Extra。重点看是不是Using filesort、Using temporary以及是否Using index。开optimizer_trace看排序决策确认排序行数估算、每行长度、sort_buffer_size 评估值。用show status like Sort%看Sort_merge_passes如果这个值很大说明排序大量落盘归并优先考虑加大sort_buffer_size或改写 SQL 减少排序数据量。检查字段字符集和 collation 是否统一。根据以上信息决定方案建联合索引、改写 SQL 延迟关联、优化参数、拆分查询。5.3 一个容易忽略的参数max_sort_lengthmax_sort_length这个参数很少被人提及但它在排序 varchar、text 等变长字段时影响很大。它决定了排序时每个字段最多取多少字节参与比较。如果排序字段本身有业务前缀规律比如前 20 个字符就能区分绝大部分数据适当调小这个值可以提升排序性能。反之如果排序要求精确按完整字符串这个参数就不能乱调小否则排序结果不对。这个参数是我在一个商品名排序场景里发现的。当时商品名前缀都是分类编号本质上只需要比较前十几字节就能确定顺序但 MySQL 默认还是取完整字段排序。调小max_sort_length后排序速度提升明显而且结果完全正确。不过这个优化依赖具体业务必须验证排序结果的正确性不能为了性能牺牲准确性。5.4 排序结果不确定性与稳定性问题最后提醒一个容易挨骂的坑如果不指定order by的排序方向或者排序字段不唯一MySQL 返回的相同排序结果在不同版本、不同执行计划下可能不同。比如order by create_time但 create_time 有大量重复值那么多次查询返回的重复行顺序可能不一致这对分页接口是致命的会导致页面数据错乱或重复。解决方案是给排序条件加一个唯一性兜底字段比如order by create_time desc, id desc。这不仅能稳定分页结果还能让索引排序更彻底地发挥作用因为 id 作为二级排序会让索引的有序性更强。这个习惯我从一次线上事故里学到的当时用户反馈分页数据来回跳查了很久才定位到是排序不唯一导致的加了 id 兜底后问题彻底消失。我个人在实际排查和优化中的体会是order by 的问题十有八九不是排序本身的问题而是数据访问路径的问题。很多方案之所以有效本质都是让排序发生在更少的数据量上。所以遇到排序慢先别急着调参数回头看看查询读了多行、走了哪个索引、回表了几次。把这些梳理清楚优化方案自然就出来了。最后再分享一个实用的小习惯把高频排序 SQL 整理成一份排序索引设计清单每次建索引前对照检查 where 字段、order by 字段、覆盖列、排序方向这四项能省下非常多排查时间。
MySQL order by排序原理与性能优化：从filesort到索引设计
做后端开发这么多年order by大概是每个人最早接触、却最晚搞懂的一个 MySQL 功能。表面上看它就是一条 SQL 末尾加个排序实际上一旦数据量上来、索引设计不合理你会发现简单的排序能直接把数据库拖垮。很多朋友来问我慢查询排查就是一条 select 加个 order by怎么就扫描了全表还要 filesort这类问题遇到多了我觉得很有必要把 MySQL 的排序机制从原理到优化完整拆一遍。这篇东西适合所有正在跟慢查询作斗争的后端同学也适合刚入门、想知道 explain 里Using filesort到底意味着什么的人。我会把自己在线上环境踩过的坑、调过的参数、改过的 SQL 全部写出来尽量让每个结论都有依据而不是网上说这样快。1. order by 的底层原理排序到底发生在哪一层1.1 一条最简单的 order by 查询执行链路是什么样的先搞清楚一条select ... order by ...从客户端发出去到返回结果中间经历了多少环节。很多人的认知停留在MySQL 把数据取出来排序再返回这个理解方向没错但粒度太粗导致排查问题时无从下手。完整的执行链路大概是这样的客户端把 SQL 发到 server 层经过连接器、分析器、优化器之后生成执行计划交给执行器。执行器负责从存储引擎层读取数据而order by对应的排序动作可能发生在 server 层也可能被优化器下推到索引扫描本身。换句话说排序并不是一个独立的、必然存在的执行步骤它取决于优化器怎么选择执行计划。这里要引入两个在 explain 输出里经常见到的关键字Using filesort和Using index。Using index是好消息表示查询能用索引覆盖连回表都不用更别说排序了Using filesort则意味着 MySQL 需要额外开辟一段内存区域来对结果集排序这个filesort 听起来像文件排序实际上一开始并不一定落盘它可以在内存里完成。关键点在于filesort 不是 MySQL 的某个插件而是一套排序算法的封装。它内部会先尝试在sort_buffer_size指定的内存缓冲区里完成排序只有当待排序数据量超过缓冲区容量时才会把中间结果写入磁盘临时文件再用归并排序把多个分片合并。真正拖垮性能的往往是后面这个落盘过程。1.2 什么时候能走索引排序什么时候必须 filesort优化器的判断逻辑并不复杂如果排序字段本身就在某个索引中而且排序方向也跟索引方向一致那么按索引顺序扫描就能天然拿到有序结果不需要额外排序。这就是所谓的索引排序也是所有 order by 优化的最终目标。但要触发索引排序有几个前置条件必须同时满足排序字段必须满足索引的最左前缀原则且排序列与索引列顺序一致。查询中的 where 条件如果也使用了同一个索引那么排序字段必须能和 where 字段在索引上连续衔接不能中间断了。排序方向要和索引方向一致。MySQL 8.0 之前索引默认都是升序如果你order by id desc理论上要反向扫描老版本 MySQL 会在某些场景下放弃索引排序退化成 filesort8.0 引入了降序索引才真正解决这个问题。举个例子表里有索引idx_a_b(a, b)where a 1 order by b就能走索引排序因为 a 等值条件下 b 在索引里天然有序但where b 1 order by a就不能因为 b 不在最左边索引对 b 的排序在 a 没被约束时是无序的。这就像电话簿按姓氏名字排序你想查所有名字叫张三的人并按姓氏排序那只能全本翻一遍重排。当索引排序走不了优化器就退而求其次选择 filesort。filesort 并不意味着一定慢在小结果集场景下它可能比索引扫描还快——因为不需要维护复杂的索引结构直接对一小撮数据排个序完事。所以看到 explain 里有Using filesort先不要慌先看它处理的数据量再决定要不要优化。2. filesort 的核心机制单趟排序与双趟排序2.1 两种排序算法的本质区别MySQL 的 filesort 实现根据排序数据的体积会在单趟排序和双趟排序之间做选择。这个知识点很少有人讲透但理解了它你就能明白为什么有时候order by慢到无法接受。双趟排序是老版本的默认策略思路是第一趟只把排序字段 行指针读入排序缓冲区排好序之后第二趟再根据行指针回表去取完整的行数据。它的好处是缓冲区能容纳更多的排序键坏处是回表次数多如果排序结果集很大随机 IO 会非常恐怖而且每一行要读两次。单趟排序则是在第一趟就把排序字段和查询需要的所有列一起读进缓冲区直接对完整数据排序省掉了回表这一步。这是 MySQL 4.1 之后引入的优化绝大多数场景下都比双趟排序快。但它有个致命缺陷同样的缓冲区能容纳的行数变少了。一旦数据量超过sort_buffer_size就必须落盘做归并这时单趟排序反而会因磁盘 IO 更频繁而更慢。MySQL 到底选哪种主要由一个老参数max_length_for_sort_data控制。当查询需要读取的所有列的总长度排序字段 select 的列大于这个值MySQL 就退化为双趟排序。这个参数默认值是 4096 字节很多老项目上线后从没调过但表的字段一多、varchar 一长单趟排序的条件很容易就不满足了。2.2 排序缓冲区sort_buffer_size 和 max_length_for_sort_data 怎么调先说sort_buffer_size它是每个线程私有的排序内存不是全局共享的。所以不能盲目调大——如果有 200 个并发连接都在做 filesort每个连接分配 2MB 排序缓冲区瞬间就是 400MB 内存。通常建议设置在 256KB 到 2MB 之间具体要看你的结果集大小。你可以用information_schema里的状态变量来观测实际排序使用量而不是拍脑袋调参。再说max_length_for_sort_data我见过很多 DBA 把它调到 8192 甚至更大理由是让尽量多的查询走单趟排序。这个思路本身没错但忽略了落盘归并的代价。我的建议是先测量你的典型排序 SQL 的单行长度如果普遍超过 4096可以适度上调但不要超过 8KB。更关键的其实是让查询只取需要的列少用select *这样单趟排序的容量自然就上去了。注意sort_buffer_size是每会话独立分配的不是全局共享的。调整它之前先评估连接数和内存总量否则内存暴涨的代价远大于排序节省的时间。2.3 排序方向、字符集与排序规则的影响还有一个很多人忽略的细节order by的排序规则不是简单按字节比大小它受字段的 collation排序规则影响。utf8mb4_general_ci 和 utf8mb4_unicode_ci 对同一批字符串的排序结果可能不同而且不同 collation 的字段之间做排序MySQL 可能无法直接使用索引导致额外的转换和 filesort。跨字符集字段排序更是重灾区。比如一张表里两个字段一个 utf8、一个 latin1order by如果涉及这两个字段的比较MySQL 必须先把它们转成同一个字符集去比较这一转换就意味着索引失效。这类问题在 explain 里经常表现为明明有索引却依然Using filesort而且Extra列有时会提示字符集转换相关的信息。排查时可以用show create table检查字段字符集是否统一。3. 排序临时表与慢查询的深层逻辑3.1 哪些场景会额外产生临时表和排序order by不止出现在最朴素的单表查询里。一旦 SQL 里混入group by、distinct、union、join排序和临时表的关系就变得复杂了。以group by为例如果 group by 的字段不是索引列MySQL 默认要对分组结果排序这就会触发 filesort。如果你根本不在意分组结果的顺序别忘了在 SQL 里明确写order by null来告诉优化器别排了。这个技巧在统计类报表里非常实用很多老开发都不知道实际上一条order by null就能省掉一段完全没必要的排序。union也是隐形排序大户。默认情况下union合并结果集时要去重而去重的一种实现方式就是先排序再比对相邻行。如果你确认两个子查询结果不可能重复直接改成union all不仅省去排序还省掉了去重的临时表开销。我看到过太多查询union和union all的结果完全一样但执行时间差了几十倍。多表 join 的排序就更有意思了。如果 join 用的是索引嵌套循环NLJ驱动表的访问顺序可能跟最终排序要求不一致MySQL 常见策略是先把 join 结果放进临时表再对临时表排序。这个临时表 filesort的组合在 explain 的 Extra 列里会同时出现Using temporary和Using filesort这是最需要警惕的信号因为磁盘临时表一旦产生性能基本就是断崖式下跌。3.2 临时表落盘内存临时表与磁盘临时表的切换MySQL 8.0 之前临时表有内存和磁盘两种形态。内存临时表默认使用 MEMORY 存储引擎一旦数据超过tmp_table_size就会自动转为磁盘临时表默认是 MyISAM 或 InnoDB 临时表。磁盘临时表的性能比内存慢几个数量级这是很多慢查询的幕后黑手。MySQL 8.0 之后引入了 TempTable 引擎内存临时表不够用时会先使用内存映射文件再逐步膨胀。虽然机制变了但避免临时表的优化方向没变。你在排查慢 SQL 时如果看到Created_tmp_disk_tables状态值突增基本可以确定有查询在批量制造磁盘临时表。我自己的排查习惯是先看慢日志里Rows_examined和Rows_sent的比值再看 explain 的 Extra。如果Using temporary和Using filesort同时出现我会优先想办法走索引覆盖 group by 或 order by 的字段其次考虑改写 SQL 结构比如用 join 代替子查询或者把大 group by 拆成多个小查询。4. 性能优化实战从 explain 到最终 SQL 改写4.1 用 optimizer_trace 精确观察排序决策很多人只会看 explain却不知道为什么优化器选择了 filesort。其实 MySQL 提供了optimizer_trace这个神器能直接把优化器的决策过程摊开给你看。开启方式很简单SET optimizer_trace enabledon; -- 执行你的查询 SELECT * FROM orders ORDER BY create_time DESC LIMIT 1000; -- 查看追踪结果 SELECT * FROM information_schema.OPTIMIZER_TRACE\G SET optimizer_trace enabledoff;追踪结果里会包含filesort_decision、sort_buffer_size、sort_algorithm等关键信息。你甚至能看到优化器估算的排序行数、每行长度以及它预测的成本值。这比瞎猜是不是数据量太大要准确得多。举个例子有一次我优化一个订单列表接口explain 里显示Using filesort但表很小、索引也建了怎么都想不通。开了 optimizer_trace 才发现原来排序字段是 varchar 类型的订单号而 where 条件里用函数截断了字符串导致原本的索引前缀匹配失效优化器被迫降级成 filesort。这种问题只靠 explain 是看不出来的。4.2 索引设计的关键参数与取舍order by 优化的核心思路很简单让排序字段成为索引的一部分而且是在 where 字段之后的连续段。这个原则说起来容易做起来需要结合具体业务权衡。比如订单表常见的查询模式是where user_id ? order by create_time desc limit 20那么建idx_user_create(user_id, create_time)就是教科书式的答案。user_id 等值过滤走索引create_time 在 user_id 确定的分支内天然有序limit 20 直接取前 20 条无需排序也无需回表取更多行。但加索引不是免费的。每个索引都会拖慢写入速度、占用磁盘空间。所以业务上要区分高频和低频排序字段。我见过最典型的反面教材是有人为了以防万一给 20 多个字段都建了单列索引结果排序 SQL 依然走不了索引排序——因为单列索引之间是孤立的优化器根本没法定制一条先过滤再排序的路径。判断一个排序字段是否该进索引我的经验是看两个指标一是这个排序查询的 QPS 高不高二是排序结果集大不大。低频 小结果集的排序直接让 filesort 用内存扛住就行不值得为其加索引高频 大结果集才是索引优化的目标。小结果集的 filesort 往往只有几十毫秒加索引反而增加写放大这是很多性能优化专家的共识也符合先量化再优化的原则。4.3 实战案例一深分页 order by 的组合拳深分页是 order by 性能优化里最经典也最难的场景。order by create_time desc limit 100000, 20这类 SQL如果只建了 create_time 索引MySQL 依然要先扫描并排序前 100020 行然后丢弃前 100000 行最后返回 20 行。数据量越大丢弃的开销越离谱。针对深分页我常用的方案是延迟关联。核心思路是先用覆盖索引快速定位目标行的主键再回表取完整数据SELECT a.* FROM orders a INNER JOIN ( SELECT id FROM orders ORDER BY create_time DESC LIMIT 100000, 20 ) b ON a.id b.id;内层子查询只需要索引列和主键走覆盖索引扫描的时候不需要回表自然就不需要为大字段排序付出额外代价。外层 join 只回表取 20 行回表开销被控制在最小范围。如果业务上允许更彻底的方案是游标分页也就是把limit offset改成where create_time 上一页的最后一条时间 order by create_time desc limit 20。这种方式不依赖深分页每一页的扫描行数都是固定的性能非常稳定。但它要求排序字段唯一且连续否则会出现漏数据。时间字段通常有重复需要用(create_time, id)联合条件来保证唯一性WHERE (create_time ?) OR (create_time ? AND id ?) ORDER BY create_time DESC, id DESC LIMIT 20;4.4 实战案例二多表 join 结果集排序多表 join 的排序优化难度比单表高一个量级因为最终结果集的排序字段往往来自非驱动表。我趟得最深的一个坑是两张千万级表 join 后按另一张表的更新时间排序。最初的 SQL 写法是先把两张表 join 完再对 join 结果集排序。explain 里Using temporary; Using filesort赫然在列查询耗时要好几秒。优化方案是先查出符合条件的驱动表主键和排序字段再 join 回原表取完整数据。这个思路的本质是把排序提前到 join 之前。如果排序字段属于小表或索引表优先让小表带排序条件参与 join减少大表参与排序的数据量。反之如果排序字段属于大表就要确保它所在的表是驱动表且排序条件能走索引。MySQL 8.0 的 hash join 出来后join 本身的效率提升了不少但排序问题依然存在。记住一个原则join 的次数越少排序越可控。能用冗余字段解决的就不要在查询里实时 join 排序。业务上适当做字段冗余比如在订单表里冗余一个商品名称看起来破坏了范式却能把排序 join变成一个单表 order by性能收益极其可观。4.5 覆盖索引与延迟关联的完整链路覆盖索引是 order by 优化里含金量最高的一招。当查询的所有列都包含在某个索引中时InnoDB 可以直接从索引叶子节点拿到全部数据连回表都省了更不需要为排序额外折腾。典型的例子是把select id, user_id, create_time和where user_id ? order by create_time组合到一个联合索引idx_user_time(user_id, create_time, id)上。但覆盖索引的容量问题不可忽视。索引列越多B树叶子节点能容纳的行数越少索引体积越大写入成本越高。所以覆盖索引只适合高频且列较少的排序查询不能无脑把所有字段都塞进索引。延迟关联是覆盖索引思想的延伸。它的核心是内层只查索引覆盖的主键和排序列外层再根据主键回表取完整数据。这样排序发生在索引数据上内存占用小排序速度快回表次数也控制在最小范围内。配合前面说的深分页场景延迟关联几乎是必杀技。5. 常见问题与排查技巧实录5.1 五个高频误区和反模式看到 filesort 就加索引。如果结果集小于几千行filesort 在内存里就是几毫秒的事加索引反而拖慢写入。先看Rows_examined再决定。对排序列用函数比如order by DATE(create_time)。一旦对字段做函数运算索引排序直接失效只能 filesort而且无法用覆盖索引。这种场景建议把时间字段拆出日期冗余列或者在业务层做格式化。排序字段和 where 字段不在同一个索引上。建了idx_user(user_id)又建了idx_time(create_time)where 用 user_idorder by 用 create_time优化器两个索引都用不上排序能力只能 filesort。正确做法是建联合索引。order by与limit的顺序没想清楚。limit在排序之后才截断order by必须对所有候选行排序所以先 where 缩范围、再 order by、再 limit看起来 SQL 顺序一样但索引设计直接决定了候选行的数量。忽视排序方向。老版本 MySQL 对order by desc的索引支持不力8.0 之前的版本如果排序字段需要反序最好在索引设计时就把字段建成降序索引或者接受 filesort 的现实。5.2 慢排序的排查工具与步骤遇到 order by 慢我的一般排查顺序是看慢日志锁定 SQL记录Rows_examined、Rows_sent、Lock_time。explain看type、key、Extra。重点看是不是Using filesort、Using temporary以及是否Using index。开optimizer_trace看排序决策确认排序行数估算、每行长度、sort_buffer_size 评估值。用show status like Sort%看Sort_merge_passes如果这个值很大说明排序大量落盘归并优先考虑加大sort_buffer_size或改写 SQL 减少排序数据量。检查字段字符集和 collation 是否统一。根据以上信息决定方案建联合索引、改写 SQL 延迟关联、优化参数、拆分查询。5.3 一个容易忽略的参数max_sort_lengthmax_sort_length这个参数很少被人提及但它在排序 varchar、text 等变长字段时影响很大。它决定了排序时每个字段最多取多少字节参与比较。如果排序字段本身有业务前缀规律比如前 20 个字符就能区分绝大部分数据适当调小这个值可以提升排序性能。反之如果排序要求精确按完整字符串这个参数就不能乱调小否则排序结果不对。这个参数是我在一个商品名排序场景里发现的。当时商品名前缀都是分类编号本质上只需要比较前十几字节就能确定顺序但 MySQL 默认还是取完整字段排序。调小max_sort_length后排序速度提升明显而且结果完全正确。不过这个优化依赖具体业务必须验证排序结果的正确性不能为了性能牺牲准确性。5.4 排序结果不确定性与稳定性问题最后提醒一个容易挨骂的坑如果不指定order by的排序方向或者排序字段不唯一MySQL 返回的相同排序结果在不同版本、不同执行计划下可能不同。比如order by create_time但 create_time 有大量重复值那么多次查询返回的重复行顺序可能不一致这对分页接口是致命的会导致页面数据错乱或重复。解决方案是给排序条件加一个唯一性兜底字段比如order by create_time desc, id desc。这不仅能稳定分页结果还能让索引排序更彻底地发挥作用因为 id 作为二级排序会让索引的有序性更强。这个习惯我从一次线上事故里学到的当时用户反馈分页数据来回跳查了很久才定位到是排序不唯一导致的加了 id 兜底后问题彻底消失。我个人在实际排查和优化中的体会是order by 的问题十有八九不是排序本身的问题而是数据访问路径的问题。很多方案之所以有效本质都是让排序发生在更少的数据量上。所以遇到排序慢先别急着调参数回头看看查询读了多行、走了哪个索引、回表了几次。把这些梳理清楚优化方案自然就出来了。最后再分享一个实用的小习惯把高频排序 SQL 整理成一份排序索引设计清单每次建索引前对照检查 where 字段、order by 字段、覆盖列、排序方向这四项能省下非常多排查时间。