做后端这几年“慢SQL”三个字见的次数不少其中一大类就是“大量数据排序”。这类问题有个特别迷惑人的地方SQL 看起来人畜无害条件、字段、分页都很普通索引该有的也都有但数据量一上来接口响应直接从小几十毫秒飙到两三秒再往后就是几十秒。我处理过不少这样的案例订单列表翻到深分页开始卡报表导出跑到一半超时后台看板转圈圈慢查询日志里一抓一大把都是“ORDER BY LIMIT”惹的祸。这篇文章就把“大量数据排序”这个慢SQL场景彻底拆开MySQL 在数据量大的情况下到底是怎么排序的慢在哪一步为什么常规“加个索引”有时灵有时不灵以及我实际优化中验证过最有效的手段。如果你也正在被深分页、大表排序、临时表爆掉的 SQL 折磨照着下面的方法排查和改造大概率能找到出路。1. 先给问题画像大量数据排序为什么容易拖垮查询1.1 执行计划里的 Using filesort 到底意味着什么先说一个很多人容易误解的点EXPLAIN 里出现Using filesort不代表这个 SQL 一定“废了”更不代表它一定用了磁盘文件排序。filesort 的意思只是“MySQL 没办法直接利用索引的有序性返回结果必须自己做一次额外的排序动作”。这个动作可能发生在内存里也可能溢到磁盘上取决于数据量和配置。MySQL 拿到一批需要排序的行之后会先把它们放进一块会话级内存也就是sort_buffer。这块内存默认只有 256KB非常小。如果这批数据能塞进去排序就在内存里完成速度很快如果塞不下MySQL 会把排序的中间结果写到磁盘临时文件里然后像归并排序那样一轮一轮地合并。这个过程会产生真实的磁盘 IO一旦出现耗时就是线性往上走。所以判断一个排序慢不慢不能只看有没有 filesort要看参与排序的数据量、单行数据有多宽、以及有没有溢出到磁盘。这也是为什么同样的 SQL在小表上毫秒级复制到千万级大表上就秒级甚至更久。1.2 三个关键因素排序行数、单行宽度、内存溢出大量数据排序的性能问题本质上由三个变量决定。第一是参与排序的行数。ORDER BY 需要处理的行越多排序成本越高。如果是深分页比如LIMIT 200000, 10MySQL 得先按照排序规则找完前 20 万条再扔掉它们最后才取那 10 条。这就是个典型的“排序大量数据只要十几条”的场景。第二是单行宽度。这个最容易被忽略。SELECT *和SELECT id, name对排序的影响差异极大。因为 MySQL 在 filesort 时会把需要返回的列一起放进 sort_buffer行越宽同样大小的 sort_buffer 能装下的行数就越少也就越容易触发磁盘归并排序。我见过有人把一张 20 多个字段的宽表整行排序结果 sort_buffer 里一行数据就占掉好几百字节几千行就把缓冲区塞满了后面全在走磁盘。第三是内存是否溢出。一旦sort_merge_passes这个状态变量开始增长就说明排序已经走到“内存放不下、落盘归并”的路子上。磁盘 IO 的速度和内存差几个数量级这就像一个仓库管理员在办公室能同时整理几十个箱子空间不够就只能搬到院子里来回搬和翻找的时间远超整理本身。这三个因素不是独立存在的行宽大能放进 sort_buffer 的行就少能放进去的行少遇到大结果集就更容易溢盘溢盘之后行数和 IO 叠加慢就成了必然。理解了这条链路再看后面的优化方案思路就非常清晰了。1.3 排序优化的核心思路是“减少排序量”而不是“加速排序”很多人一遇到排序慢第一反应是调大sort_buffer_size但这是治标不治本。真正的优化方向应该是要么让 MySQL 根本不需要排序用索引的有序性要么让参与排序的数据量尽可能小延迟关联要么让业务根本不需要翻那么深的页游标翻页。顺着这个思路走你会发现大部分“大量数据排序”的慢 SQL都能找到对应的解法。2. 现场还原大量数据排序的慢SQL到底长什么样2.1 深分页排序OFFSET 越深越慢先看一个最典型的线上案例。订单列表接口用户按下单时间倒序翻页SQL 长这样SELECT id, order_no, user_id, amount, status, create_time FROM orders WHERE user_type 2 ORDER BY create_time DESC LIMIT 200000, 10;表里总共 800 万行user_type 2的数据大约 60 万行。这条 SQL 在翻到第 2 万页的时候实测 1.8 秒。EXPLAIN 看一下type: ALLkey: NULLrows: 8000000Extra: Using where; Using filesort这就是典型的全表扫描 全量排序 深分页三重叠加。MySQL 先把 800 万行全部读出来过滤然后按 create_time 排序再数到第 20 万条之后取 10 条。注意即便 MySQL 对ORDER BY ... LIMIT有优先队列堆排序的优化但那是针对“取前 N 条”的场景这里是取“第 20 万页之后的 N 条”前 20 万条依然要全部参与比较和排序堆排序的优势完全发挥不出来。这类 SQL 有个共同特征前端页面翻得越深数据库压力越大接口越慢。所以线上遇到“前几页秒开后面的页越来越慢”的反馈十有八九就是它。2.2 无索引字段排序 大范围查询第二种常见形态是排序字段根本没进索引。比如后台需要统计某种状态下金额最大的用户SELECT user_id, amount, order_time FROM orders WHERE status 1 ORDER BY amount DESC LIMIT 50;如果status没索引MySQL 就要把 status 1 的所有行扫出来然后对 amount 排序。哪怕最终只要 50 条排序的对象却是几十万行。这种 SQL 比深分页更隐蔽因为从慢日志看它执行次数不多但每次执行都吃满 CPU 和临时空间一旦和业务高峰期撞上数据库整体就被拖慢了。更麻烦的情况是 status 有索引但 amount 没有构成“索引定位 大量回表 filesort”的组合。回表本身也是成本如果状态分布不均匀某个状态的数据特别多这条 SQL 还是会慢。2.3 多表 JOIN 之后排序排序字段来自关联表第三种出现在报表和查询类业务里。多个表关联查询排序字段在关联表上SELECT o.id, o.order_no, u.user_name, o.amount FROM orders o INNER JOIN users u ON o.user_id u.id ORDER BY u.user_name LIMIT 100;这种 SQL 的执行路径通常很“曲折”MySQL 先把两张表 join 出结果集再对结果集按 user_name 排序。问题是 join 的中间结果集可能非常大而且临时结果集可能压根没有合适的索引只能全部落进 sort_buffer 甚至磁盘临时表。我在实际排查中见过的极端案例join 出 200 万行中间结果排序花了 30 多秒。这种场景下单独给 orders 加索引基本没用因为排序字段在另一张表上索引覆盖不到。优化的核心思路往往是改写 SQL先在小范围内排好序再回表 join。这一点放到下一节详细说。2.4 排序 分组统计临时表爆掉最后一种是 GROUP BY 和 ORDER BY 叠加的场景。很多统计接口会写类似这样的 SQLSELECT user_id, COUNT(*) AS cnt FROM orders WHERE create_time 2024-01-01 GROUP BY user_id ORDER BY cnt DESC LIMIT 100;这类 SQL 的执行计划经常出现Using temporary; Using filesort。意思是 MySQL 先要把数据分组生成一个临时表再对这个临时表排序。如果参与统计的数据量非常大内存临时表放不下临时表就会从内存转到磁盘上Created_tmp_disk_tables状态变量会飙升整个查询的耗时就会被磁盘读写吞掉。这种慢 SQL 是所有类型里最棘手的一种因为它光靠索引调整很难完全解决。业务上如果频繁需要这类 Top N 统计我更建议建一张汇总表维护预聚合数据查询直接落到几百行的小表上从根上避开大数据量排序。3. 优化方案落地从索引、SQL改写到参数调整3.1 方案A用联合索引消除 filesort这是首选优化排序的第一选择永远是让排序动作消失也就是走索引有序扫描。回到 2.1 的案例原始 SQL 的 WHERE 条件是user_type 2排序条件是create_time DESC。要消除 filesort就要让索引同时覆盖这两个字段并且保证“等值条件字段在前排序字段在后”ALTER TABLE orders ADD INDEX idx_ut_ct (user_type, create_time);加完索引之后再看执行计划Using filesort消失了。MySQL 会沿着idx_ut_ct索引找到所有user_type 2的索引项而这些索引项天然就是按 create_time 排好的倒序读取即可。哪怕 LIMIT 的偏移量很大也只需要顺序跳过前 20 万条索引项再回表取 10 条真实数据不再需要把几十万行搬进 sort_buffer 排序。这里要提醒一句联合索引字段顺序非常关键。把索引建成(create_time, user_type)是没用的因为 WHERE 条件是 user_type 等值它必须出现在最左前缀才能被用上排序字段 create_time 放在后面才有意义。这个顺序反了优化器很可能仍然选择全表扫描。另一个判断标准我经常告诉团队只要 EXPLAIN 里type不是 ALL、key不是 NULL、Extra里没有 filesort且 rows 估算值明显变小这条排序 SQL 基本就治好了。3.2 方案B延迟关联给排序集“瘦身”并不是所有排序场景都能靠加索引解决。比如排序字段是多个字段组合、或者 WHERE 条件里有范围查询破坏了索引的有序性再或者排序字段根本在另一张表上这时候就要换思路让参与排序的数据行变“瘦”。这就是延迟关联deferred join的核心思想先在索引或小结果集里完成排序和分页拿到主键列表再回头去查完整行。拿 2.1 的案例举例如果没有合适的联合索引可以改写成SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders WHERE user_type 2 ORDER BY create_time DESC LIMIT 200000, 10 ) t ON o.id t.id;子查询只取id一列单行宽度非常小。假设一行完整记录是 200 字节而一个 id 只有 8 字节同样 256KB 的 sort_buffer原来只能装下 1000 行现在能装下 3 万行磁盘溢出的概率大大降低。而且排序完成后回表只回 10 行JOIN 的代价几乎可以忽略。这个方案特别适合“SELECT 的列很多但排序只需要一两个字段”的场景。我曾经优化过一条 SQL原查询要 SELECT 30 个字段延迟关联之后把排序数据量从几万行降到几千行耗时从 1.2 秒降到 80 毫秒效果立竿见影。3.3 方案C深分页改成游标翻页从根上消除 OFFSET如果你问什么方案对深分页最有效我的答案是不要让用户翻那么深的页。业务上可以考虑限制最大翻页深度技术上则应该把 OFFSET 分页改成游标分页keyset pagination。原理很简单OFFSET 分页的问题是每次请求都要“重新数前面所有的行”而游标分页直接携带上一页最后一条的位置SQL 从一开始就只查目标位置之后的数据。继续用订单案例假设上一页最后一条记录的create_time 2024-06-01 10:30:00id 12345下一页的 SQL 就写成SELECT id, order_no, user_id, amount, status, create_time FROM orders WHERE user_type 2 AND (create_time, id) (2024-06-01 10:30:00, 12345) ORDER BY create_time DESC, id DESC LIMIT 10;这里用了元组比较MySQL 完全支持。加一个(user_type, create_time, id)联合索引这条 SQL 每次只扫描 10 条索引项不管翻到第 1 页还是第 10 万页耗时始终稳定在毫秒级。游标分页也有代价用户不能直接跳页比如从第 1 页直接跳到第 50 页前端交互需要调整。但大多数业务场景里用户根本不会真的翻到 2 万页。如果一个订单列表允许靠 OFFSET 翻到这么深往往本身就是设计问题。我的经验是面向 C 端的列表能改游标就改游标统计类的报表结果集通常不大游标分页反而不必要。3.4 方案D参数调优但别把它当万能药参数调优放在最后是因为它最容易“看起来有效”却解决不了本质问题。先把常用的三个参数说清楚。sort_buffer_size控制排序内存大小。调大它确实能让更多数据在内存里排序减少溢盘但它是“会话级”的每个连接独立分配。一个连接给 2MB100 个并发连接就是 200MB内存很快就爆。我的建议是保持默认或稍调高到 512KB 到 1MB 之间优先用延迟关联减少排序行宽而不是靠堆内存去硬扛大数据量。max_length_for_sort_data控制 filesort 使用“单路排序”还是“双路排序”。当排序涉及的字段总长度超过这个值MySQL 会退回双路排序也就是先把排序列和主键排好再回表取完整数据。适当调大可以增加单路排序的比例但也需要更大的 sort_buffer 配合。这个参数在实际优化中我基本不动因为现代 MySQL 的优化器已经能自己选得比较好了乱调反而容易出现内存紧张。tmp_table_size和max_heap_table_size控制内存临时表的大小两者取小值作为内存临时表的容量上限。对 2.4 节那种 GROUP BY ORDER BY 的场景调大这些参数可以让更多临时表留在内存而不是落盘。但要注意内存临时表也是内存消耗大户量大了一样危险。真正治本还是要减少分组排序的数据量或者落到预聚合表。参数调优永远排在 SQL 改写和索引优化之后这是我在项目里反复强调的底线。4. 问题排查实录我是怎么一步步定位这类慢SQL的4.1 先看慢日志精确“抓案”再上 EXPLAIN 三件套排查排序慢 SQL 的第一步不是看业务代码而是先拿到慢查询日志。我会把long_query_time设置为 1 秒观察一个业务周期把那些频繁出现的 ORDER BY 语句全部抓出来。抓到之后先用 pt-query-digest 之类工具汇总看哪些语句是“执行次数不多但单次极慢”哪些是“次数多且单条就慢”优先级不同。然后针对每一条 SQL 做 EXPLAIN重点看三列type是不是 ALL全表扫描或 index全索引扫描如果是说明过滤和读取本身就有问题key实际用了哪个索引NULL 就是没索引Extra有没有Using filesort和Using temporary这两个是排序问题的直接证据。这里有个经验如果rows估出来是几十万甚至上百万同时 Extra 里带着 filesort地基本可以锁定“大量数据排序”就是瓶颈来源。接下来再判断排序发生在哪个环节是简单的字段排序还是分组临时表排序还是 join 之后排序——三种处理思路完全不同。4.2 用状态变量确认是否真的发生了磁盘排序EXPLAIN 只能说明“做了排序动作”不能说明排序有多慢。要证明排序是否拖垮性能我通常会对比执行前后的状态变量重点看三个指标-- 执行前 SHOW SESSION STATUS LIKE Sort%; SHOW SESSION STATUS LIKE Created_tmp%; -- 执行慢SQL -- 执行后再次查看 SHOW SESSION STATUS LIKE Sort%; SHOW SESSION STATUS LIKE Created_tmp%;关键看Sort_merge_passes这个值的含义是“排序过程中数据在磁盘和内存之间合并的次数”。如果执行一次慢查询后它涨了很多说明 sort_buffer 已经容纳不下排序数据真实发生了磁盘归并排序。这就是排序部分耗时的铁证。Created_tmp_disk_tables也同样重要。GROUP BY 类的 SQL 如果这个值暴涨说明内存临时表溢到了磁盘临时表读写成了新的瓶颈。定位到这一步优化方向就很明确了针对溢盘的环节做瘦身或加索引。4.3 从定位到优化的一次完整复盘我之前接过一个案例线上报表服务每天凌晨跑批其中一条统计 SQL 稳定执行 15 秒以上导致批处理排队。慢 SQL 是这样的SELECT shop_id, SUM(amount) AS total_amount FROM sales_record WHERE sale_date 2024-01-01 GROUP BY shop_id ORDER BY total_amount DESC LIMIT 100;EXPLAIN 的结果是 typeALLExtra 是Using temporary; Using filesortrows 估算 500 万。再看状态变量Sort_merge_passes和Created_tmp_disk_tables涨得都很明显。判断结论是全表扫描 500 万行分组 临时表排序 磁盘溢出四重问题叠在一起。优化分成两步走。第一步给sale_date加索引把 WHERE 的范围扫描从全表变成索引范围扫描这是为了减少参与分组的数据量。第二步考虑到这种 Top N 统计每天都会跑我直接在业务侧建议加一张 shop 日销售汇总表每天凌晨的批处理先增量聚合到汇总表报表查询直接对几百行做 ORDER BY耗时降到了 20 毫秒以内。这个案例的启示是排序慢的根本原因可能是“数据量已经不适合在业务 SQL 层面硬算”这时候与其反复调 SQL不如换个存储形态把大数据量排序从核心链路里彻底移走。5. 常见问题速查与避坑心得5.1 常见问题与处理对照表问题特征典型现象优先处理方案深分页 ORDER BYOFFSET 越深越慢游标翻页改造或延迟关联全表扫描 filesorttypeALLkeyNULL建联合索引WHERE 字段在前排序字段在后SELECT 字段过多导致排序慢行宽大Sort_merge_passes 增长延迟关联子查询只取 id 和排序列GROUP BY ORDER BY 统计Using temporary; Using filesort预聚合汇总表或调整 tmp_table_size排序字段来自 join 的另一张表join 后结果集很大子查询先排序取主键再 join 回原表5.2 几个容易被“坑”的认知误区第一个误区是“看到 Using filesort 就紧张”。我前面反复说了filesort 只是一个动作标识如果参与排序的数据量小、sort_buffer 装得下它甚至可以快到可以忽略。很多 SQL 的 filesort 根本就不是性能瓶颈真正的问题是 rows 太大或溢盘。先看Sort_merge_passes再决定要不要优化。第二个误区是“sort_buffer 调得越大越好”。排序内存是每个会话独立分配的不是全局共享。把 sort_buffer 调到 64MB意味着每个连接都可能吃掉 64MB连接一多数据库内存直接被打穿。排序内存的调整要克制优先通过延迟关联缩小排序数据的“体积”。第三个误区是“有了索引就一定能消除排序”。索引消除排序有条件WHERE 等值字段必须在联合索引最左侧ORDER BY 字段必须紧随其后而且排序方向要和索引一致。比如ORDER BY 字段A ASC, 字段B DESC在普通升序索引下就没办法同时满足MySQL 只能 filesort。范围查询如 BETWEEN、、也会打断后续排序字段的有序性导致优化器放弃走索引排序。建索引之前先想清楚这些规则能少走很多弯路。第四个误区是“延迟关联只能用于深分页”。不是。只要排序字段少、返回字段多、排序结果集大都值得用延迟关联。它唯一的代价是多一次 join但这通常比在 sort_buffer 里塞宽数据要便宜得多。实测下来大部分场景的收益都远超损失。我个人处理排序类慢 SQL 的体会是最忌讳一上来就调参数或者盲目加索引。拿着 EXPLAIN先看 rows 和 Extra判断排序发生在哪个环节再决定是消除排序、缩小排序集、还是改变业务翻页方式。很多时候看似“无解”的大排序问题只是选错了优化工具。最后再分享一个排查时的实用小技巧优化完一条排序 SQL不要只看执行时间把优化前后的Sort_merge_passes和Created_tmp_disk_tables记下来对比。这两个数字归零或大幅下降说明优化是真的解决了“大量数据排序”的根源而不只是把问题从瓶颈处挪到了别的地方。