MySQL大量数据排序慢SQL优化：从原理到实战

做后端这几年，“慢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, 10，MySQL 得先按照排序规则找完前 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: ALL
• key: NULL
• rows: 8000000
• Extra: 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 叠加的场景。很多统计接口会写类似这样的 SQL：

SELECT 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:00'，id = 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控制排序内存大小。调大它确实能让更多数据在内存里排序，减少溢盘，但它是“会话级”的，每个连接独立分配。一个连接给 2MB，100 个并发连接就是 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 的结果是 type=ALL，Extra 是Using temporary; Using filesort，rows 估算 500 万。再看状态变量，Sort_merge_passes和Created_tmp_disk_tables涨得都很明显。判断结论是：全表扫描 + 500 万行分组 + 临时表排序 + 磁盘溢出，四重问题叠在一起。

优化分成两步走。第一步，给sale_date加索引，把 WHERE 的范围扫描从全表变成索引范围扫描，这是为了减少参与分组的数据量。第二步，考虑到这种 Top N 统计每天都会跑，我直接在业务侧建议加一张 shop 日销售汇总表，每天凌晨的批处理先增量聚合到汇总表，报表查询直接对几百行做 ORDER BY，耗时降到了 20 毫秒以内。

这个案例的启示是：排序慢的根本原因可能是“数据量已经不适合在业务 SQL 层面硬算”，这时候与其反复调 SQL，不如换个存储形态，把大数据量排序从核心链路里彻底移走。

5. 常见问题速查与避坑心得

5.1 常见问题与处理对照表

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记下来对比。这两个数字归零或大幅下降，说明优化是真的解决了“大量数据排序”的根源，而不只是把问题从瓶颈处挪到了别的地方。