凌晨一点监控群里弹出告警电商后台的订单分页接口P95 从 200 毫秒直接飙到了 3.8 秒慢查询日志里捞出来的那条 SQL光是排序就占了大半时间。打开执行计划一看十几万行的Using filesort挂在 Extra 列里像一根刺扎在眼前。这种「大量数据排序导致的慢SQL」场景几乎每个做后端的人都遇到过但很多人第一反应是狂加索引结果加了半天毫无起色。这篇文章不打算讲教科书式的原理而是把我实际排查和优化这类排序慢SQL的完整链路摊开。从一个真实到不能再真实的业务案例出发看看排序到底慢在哪个环节、怎么从执行计划里快速定位、什么样的索引设计才能让数据库放弃排序、哪些参数调整是有效投入、哪些只是在自我安慰以及最后当业务非要深分页时架构层面还有哪些退路。适合所有被慢SQL告警折腾过的开发、DBA 和运维同学。1. 一条慢SQL的典型画像告警现场与第一反应1.1 现场复盘一个再普通不过的分页查询那次事故的表是order_record存储订单主数据行数在 2500 万左右。接口逻辑是运营后台的「订单列表页」支持按order_status过滤、按paid_at时间范围筛选并且默认按amount DESC实付金额排序前端的每一页是 20 条数据。问题 SQL 长这样SELECT order_no, user_id, amount, paid_at, order_status FROM order_record WHERE order_status 1 AND paid_at 2024-01-01 ORDER BY amount DESC, id DESC LIMIT 300000, 20;单独看这条 SQL逻辑上没有任何问题——用户就是点了「第 15000 页」而已。但就是这看似合理的业务操作让数据库干了一件特别蠢的事先把满足条件的所有行找出来做一次全局排序然后取第 300001 到 300020 条剩下 30 万条白排。更致命的是这个排序动作并没有走索引而是完完全全在数据库内部重新排序。说白了大部分排序慢SQL死就死在这里不是数据库不行是我们让数据库干了它最不擅长的事。1.2 第一反应不该是改代码而是先看执行计划我见到太多人接到慢SQL告警第一件事就是把 SQL 抄到工单里然后开始凭感觉给字段加索引。正确的是先跑一个EXPLAIN看看数据库到底打算怎么执行。EXPLAIN SELECT order_no, user_id, amount, paid_at, order_status FROM order_record WHERE order_status 1 AND paid_at 2024-01-01 ORDER BY amount DESC, id DESC LIMIT 300000, 20;执行计划的结果大致如下列名值typeALLpossible_keysidx_order_statuskeyNULLrows12160000filtered10.00ExtraUsing where; Using filesortrows预估 1216 万行Extra里明晃晃写着Using filesort连key都是 NULL。也就是说这条 SQL 没走任何索引全表扫了一遍然后对所有数据做文件排序。看到这份执行计划基本可以断定问题出在「排序」和「深分页回表」两层没跑。1.3 判断排序慢SQL的三个优先级问题拿到执行计划后我一般按照下面的顺序快速判断问题到底卡在哪一层有没有走索引key列是 NULL 还是走了辅助索引。如果possible_keys有但key是 NULL说明优化器认为索引帮不上忙典型的场景就是范围条件之后跟着排序字段。有没有Using filesort有则意味着排序动作无法由索引天然完成要从当前结果集中再排一遍。LIMIT offset深不深offset 到几万甚至几十万的时候问题就不再是「排序」本身而是「排序 回表 丢弃」的叠加效应。这个三步定位法帮我快速筛掉了大量无效排查也避免了乱加索引的尴尬。2. 慢根因复盘filesort 到底慢在哪个环节2.1 执行计划里的 Using filesort 到底是什么很多人一看到Using filesort就以为是磁盘排序其实不然。filesort这个名字在 MySQL 里很具有迷惑性它泛指「任何额外排序动作」包括内存排序和磁盘临时文件排序。当 SQL 里的ORDER BY字段无法通过索引顺序直接满足时MySQL 就会把需要排序的行拷贝到自己的排序缓冲区内在缓冲区内进行快排或堆排序。这个排序缓冲区叫sort_buffer_size默认值是 1MB在 MySQL 8.0 中通常也是 1MB。如果排序数据量超过了这个缓冲区能容纳的大小MySQL 才会把数据一部分一部分地在内存排好序后写入磁盘临时文件最终做归并排序。这个过程会产成Sort_merge_passes也就是归并趟数一旦趟数多了IO 开销就直接飙升。2.2 内存排序的边界sort_buffer 是什么为什么默认值很容易不够为了把排序原理讲透我拿整理档案打个比方。假设你要把一千份档案按金额从大到小排列桌面上只有一块固定大小的区域可以摊开档案比较。如果档案总量不大桌面一次就能放下你直接在桌面上排好交给别人就行但如果档案实在太多桌面放不下你得先把一部分排好的档案放到旁边的柜子里然后继续排剩下的最后再把柜子里几摞档案合并到一起。MySQL 的sort_buffer_size就是那张桌子的大小。1MB 听起来不小但每个参与排序的记录在缓冲区里占据的空间比你想象的大得多。它不只是排序字段的长度还包括 SQL 里SELECT出来的所有字段长度加上一些辅助列的开销。假设每条排序记录约 200 字节1MB 的缓冲区只够放 5000 条左右。而这条慢SQL要排序的行数是 1200 万级别哪怕只是粗略估算也知道必然要落盘。这里有个很重要的判断指标——Sort_merge_passes来自SHOW GLOBAL STATUS LIKE Sort_%;的输出Sort_merge_passes | 0 Sort_range | 0 Sort_rows | 12160000 Sort_scan | 1216Sort_merge_passes一旦不为 0说明排序过程出现了「内存排好一批、落盘、再排下一批、最后合并」的情况。落盘次数越多排序越慢。当时这条 SQL 的Sort_merge_passes已经出现了较高的数值如果长期维持高位就是一个强烈的信号sort_buffer_size不够或者排序本身的行宽太大。2.3 深分页回表排序之外的另一只老虎很多人以为排序慢就是filesort的锅但真实场景里深分页的回表往往才是压死骆驼的最后一根稻草。MySQL 执行LIMIT 300000, 20时即使排序已经完成它也不会聪明到只取最后 20 条。它的执行逻辑是从排序结果里取第 1 条到第 300020 条然后把前 300000 条全部丢掉。这 30 万次「取出-丢弃」的操作本身不是最耗时的最耗时的是在排序之前如果走的是辅助索引每一行都要通过主键去聚簇索引里把完整行数据捞出来这个过程叫回表。想象一下你要在一本书里按目录找到 30 万条注释每找到一条注释就要翻到对应正文页去看一遍。这不仅仅是翻页动作本身还有物理位置的随机性——今天可能在第 100 页下一跳就到了第 2580 页再下一跳可能在第 300 页附近。这种随机 IO 在数据量大的时候会直接拖垮查询。所以这条 SQL 的耗时构成了一个递进链条全表扫描收集行 - 排序可能落盘 - 逐行回表 - 丢弃前 offset 行 - 返回 20 行。每一环都在放大前一环的成本。3. 从 explain 到 optimizer trace一次完整的定位链路3.1 慢日志定位先把最核心的 SQL 捞出来慢SQL治理的第一步永远是「找到那条最慢的 SQL」而不是凭感觉猜。这个场景下我是这样操作的首先确认慢查询日志已经开启并设置一个合适的阈值一般建议 OLTP 业务设置为 1 秒SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes OFF;阈值设好了慢SQL查询日志会持续记录。下一步是用pt-query-digest这类工具对慢日志做聚合分析它能按 SQL 指纹把同一类 SQL 的执行次数、总耗时、扫描行数、平均耗时全部统计出来。看聚合表的时候重点看Rows examine、Rows examine / Query time比例。如果比例很高比如一条 SQL 扫描了 1200 万行却只返回 20 行可以迅速判断这是一条典型的「大量数据扫描 少量结果返回」的问题 SQL。3.2 explain 能告诉我们的以及不会告诉我们的EXPLAIN能告诉我们是否走了索引、预估扫描行数、Extra 列里有没有Using filesort。但它有一个很大的盲区它不会告诉我们排序时到底有没有产生临时文件也不会告诉我们每条记录在排序缓冲区里占多大空间。换句话说EXPLAIN只能告诉你「这里要排序」但没法告诉你「这次排序到底是不是磁盘排序、落了几趟盘」。想要把这个问题钉死就得请出Optimizer Trace。3.3 optimizer trace还原排序现场的完整操作优化器跟踪是排查复杂 SQL 的利器。它会把优化器生成执行计划的内部过程记录下来包括每条路径的成本计算、是否使用了 filesort、filesort 的预估大小、临时表大小等。操作方法并不复杂-- 打开优化器跟踪 SET optimizer_trace enabledon; -- 执行刚才那条慢 SQL SELECT order_no, user_id, amount, paid_at, order_status FROM order_record WHERE order_status 1 AND paid_at 2024-01-01 ORDER BY amount DESC, id DESC LIMIT 300000, 20; -- 查看优化器跟踪结果 SELECT * FROM information_schema.OPTIMIZER_TRACE \G;输出的内容很长重点关注filesort_summary这一段当时截取的核心数据如下filesort_summary: { rows: 12160000, examined_rows: 12160000, number_of_tmp_files: 3, sort_buffer_size: 1048576, sort_mode: sort_key, additional_fields }这个片段信息量很大。examined_rows达到了 1216 万说明 1200 多万行全部参与了实际排序number_of_tmp_files是 3说明排序过程中至少产生了 3 个临时文件也就是说 1MB 的排序缓冲区装不下已经开始走磁盘归并了sort_mode是additional_fields代表 MySQL 把查询需要的所有列字段都拷贝到了排序缓冲区进一步放大了内存占用。看到number_of_tmp_files大于 0问题就已经从「执行计划带你兜圈子」变成了「明确就是排序内存/排序行宽的问题」。3.4 检查地图与工具组合根据实践整理出一个排序慢SQL的检查路径下次可以直接抄作业检查项工具或手段重点看什么全貌定位pt-query-digestRows examine和耗时占比最高的 SQL是否走索引EXPLAINkey、rows、Extra列排序是否是瓶颈SHOW GLOBAL STATUS LIKE Sort%Sort_merge_passes、Sort_rows排序是否落盘Optimizer Tracenumber_of_tmp_files、sort_buffer_size回表是否严重结合索引行数和LIMIT offset逻辑读次数、随机 IO 情况这套组合一起用基本能把一条排序慢SQL的内外因都查清楚。接下来的问题就是怎么改才能让这条 SQL 跑得快。4. 让索引吃掉排序核心改写方案与实测收益4.1 为什么联合索引能消灭 filesort数据库里最天然、最高效的「排序」不是ORDER BY手动排而是通过 B 树索引的有序性让数据本来就有序。当你ORDER BY的字段顺序能和某个索引的列顺序完全匹配时MySQL 直接顺序扫描索引叶子节点就能拿到有序结果完全不需要再额外排序。这时候Extra列里根本不会出现Using filesort。但这里有个关键原则等值条件优先排序字段随后。联合索引的列顺序要把WHERE里的等值匹配列放在最前面把ORDER BY的列放在后面。例如查询条件是order_status 1排序字段是amount DESC那么一个(order_status, amount)的联合索引可以在满足order_status 1的前提下天然按amount顺序排列。4.2 这个案例的索引设计成败在范围条件与排序字段的博弈我们回到那条 SQL。WHERE里有order_status 1等值和paid_at 2024-01-01范围ORDER BY里有amount DESC, id DESC。如果建索引(order_status, paid_at, amount)那么paid_at作为范围条件会导致同一order_status分组下paid_at之后是索引内部顺序连续的但amount的排序关系在范围条件之后无法被索引继承。也就是说范围条件一旦出现后面的排序字段就无法再依赖索引的有序性。所以从索引设计的角度最优解往往是往两个方向走方向一让排序字段直接作为索引的第二列。把paid_at的范围筛选尽量弱化。如果业务允许比如可以从「必须筛选某个起始时间」改成「只需要最近 30 天/最近 7 天」那就可以不把paid_at放进查询条件而是直接WHERE order_status 1 AND paid_at NOW() - INTERVAL 30 DAY。但现实中这种改动往往受业务限制不容易落地。方向二用覆盖索引把 SELECT 的列全部兜住减少回表。如果你必须保留范围条件排序仍然有可能会走文件排序但你可以把排序动作的代价降下来——让排序的对象尽量小并且让回表动作消失。例如设计索引(order_status, amount, id, paid_at, order_no, user_id)使 SELECT 的列全部在索引内排序时即使发生 filesort也不需要回表取行。这个案例里我给表加了一个综合索引ALTER TABLE order_record ADD INDEX idx_status_amount_id (order_status, amount DESC, id DESC);这里的DESC是 MySQL 8.0 支持的降序索引能完全匹配ORDER BY amount DESC, id DESC。再加上额外几列做覆盖查询基本就可以不再回表。字段描述不是必须但如果你用的 8.0降序索引真的可以解决传统上反向扫描的优化器顾虑。4.3 延迟关联让排序的数据量变小比什么都管用如果无法靠索引完全消除排序还有一招性价比极高的改写延迟关联。核心思路是先把排序和分页的字段缩小到「主键 排序字段」这么小的范围拿到最终需要的 20 条主键后再回到大表关联取出完整行数据。这样做的目的是让最耗时的「排序 深分页丢弃」操作在一个极小的数据集上完成。改写后的 SQL 长这样SELECT a.order_no, a.user_id, a.amount, a.paid_at, a.order_status FROM order_record a INNER JOIN ( SELECT id FROM order_record WHERE order_status 1 AND paid_at 2024-01-01 ORDER BY amount DESC, id DESC LIMIT 300000, 20 ) b ON a.id b.id ORDER BY b.amount DESC, b.id DESC;内层子查询只取id和排序字段行宽大大缩小同样的sort_buffer_size可以装下更多行落盘概率变小同时子查询走idx_status_amount_id索引排序接近零成本。外层查询再根据 20 个主键回表最多 20 次随机 IO代价完全可控。4.4 实测数据对比一套操作做完后的实测数据MySQL 8.0服务器为普通 SSD 云盘2500 万行可以拿来参考优化方案执行耗时平均值扫描行数备注原SQL无索引深分页3.8s1216 万大量 filesort 全表扫仅加联合索引(order_status, amount, id)0.55s约 420 万排序被索引吃掉但回表仍存在联合索引 延迟关联0.08s约 420 万回表次数大幅下降效果最明显注意耗时是相对值不同配置下会有差异但趋势是一致的让索引去做排序、让小的数据集去承担深分页收益立竿见影。5. 参数、硬件与分页需求的博弈哪些优化值得做5.1 sort_buffer_size 到底要不要调大这个问题被问过无数次。先给结论可以调但必须是受控地调绝对不能全局盲目拉高。sort_buffer_size是会话级别的每个连接在执行排序时都可能分配一块这么大的内存。如果一个实例同时有 500 个活跃连接你把sort_buffer_size从 1MB 调大到 64MB在极端情况下内存可能瞬间被吃掉 32GB直接 OOM。因此调整之前务必查一下当前实例的活跃连接数和并发量。比较稳妥的调法是在定位到了number_of_tmp_files 0之后再逐步调整。比如这条 SQL 一开始是 1MB 不够可以尝试在会话级临时调高SET SESSION sort_buffer_size 4194304; -- 4MB重新执行 SQL 后再次看 Optimizer Trace 里的number_of_tmp_files。如果变成了 0说明内存排序就够了不需要继续上调。如果还是很大可能说明单行排序记录体积太大这时候该做的就是上面说的减少排序字段行宽、用延迟关联缩小排序集而不是无限调参数。5.2 历史上的 max_length_for_sort_data 陷阱在 MySQL 8.0.12 之前max_length_for_sort_data这个参数会产生一个诡异的优化分支如果排序行长度大于它MySQL 就不再拷贝全部字段而是只排主键 排序字段排序完后再回表。听起来好像更省内存但副作用是回表次数暴增。很多老博客会建议调大它但在 8.0.12 之后这个参数已经被官方废弃行为由优化器自动控制不需要再去折腾。如果你在网上看到一堆「调大 max_length_for_sort_data 提升排序性能」的旧文看看就好别照着做。顺带一提MySQL 8.0 的排序模式默认是additional_fields即把所有需要返回的字段拷进排序缓冲区因此 SELECT 的字段越宽排序时占用的内存越大。这也是为什么很多实践建议里强调「禁止无脑 SELECT *」——它不只是传输浪费连排序缓冲区也会被拖垮。5.3 宽表、全字段查询与排序行体积的关系很多 SQL 慢的根源不在数据库而在写 SQL 的习惯。举个直观的计算假设你的表有 40 个字段其中几个是VARCHAR(255)、DECIMAL(10,2)每条记录在排序缓冲区里占 500 字节。默认 1MB 的缓冲区就只能装下约 2000 条。但如果你只 SELECT 主键 排序字段每条记录可能只有 32 字节1MB 可以装下 3 万条以上。同样是不落盘内存排序能处理的数据量差了一个数量级。所以在排查这类排序慢SQL时第一件事先问自己这个查询真的需要这么多列吗能不能先 SELECT 主键再二次关联把对齐到列的工作做在前面参数调整才有意义。5.4 硬件层面的「急救」措施如果线上已经出了事故来不及改 SQL 和索引临时救火可以考虑两个方向一是在条件允许的情况下把tmpdir指向内存盘例如/dev/shm或者 tmpfs这样即使排序落盘也是落在内存映射块上IO 快一个量级。请注意这是应急方案重启后数据会丢但排序临时文件本身也不需要持久化因此可行。二是确认存储盘是否为 SSD。如果还在机械盘上跑业务库遇到大量临时文件排序IO 会成为压倒性的瓶颈。但急救归急救它只解决「不崩」不解决「不慢」。真正要扛住持续的排序压力还得回到第 4 章的索引和 SQL 改写这个治本路径。5.5 深分页到底该不该被允许说句得罪人的话绝大部分业务场景用户根本不需要看到第 15000 页。深分页这个需求本身就是技术和产品之间的一种失真。如果业务方坚持要用 offset 分页可以限定最大翻页深度比如最多只能翻 200 页超过就提示「数据量过大请使用筛选条件」。如果产品经理不同意限制那可以从产品交互改成「游标分页」——按上次查询结果里的最后一条记录的paid_at, id作为下一次查询的起始条件。数据库里这种写法的快慢差距是天壤之别因为游标分页几乎能完美利用索引把 30 万行的 offset 全部省掉。通用游标改写长这样SELECT order_no, user_id, amount, paid_at, order_status FROM order_record WHERE order_status 1 AND (paid_at 2024-06-01 10:00:00 OR (paid_at 2024-06-01 10:00:00 AND id 123456)) ORDER BY paid_at DESC, id DESC LIMIT 20;每一页查询只需要 O(log n) 定位 20 次回表效率一级棒。6. 逃不开的另一种选择深分页业务重构与架构兜底6.1 三种分页模式的取舍不是所有排序都适合用索引解决。当排序字段来自多张表、或带有复杂的聚合计算比如按「商品销量评分综合分」排序这时候关系型数据库想靠索引一把梭已经没有可能。我把常见的分页模式整理成一张对比表分页模式实现方式优点缺点offset 深分页LIMIT 300000, 20代码简单产品理解成本低深页性能断崖式下跌keyset 游标分页WHERE 排序字段 上页最后一条单页查询极快索引友好只能顺序翻页不可跳页中间态缓存分页先查主键 ID 列表缓存再按区间回表灵活可控主键列表大时同样有内存成本从实践角度我见过很多团队在前 10 页用游标分页超过 10 页直接提示用户缩小查询范围体验和性能都保住了。6.2 当 MySQL 力不从心搜索引擎与列式存储兜底如果业务真的复杂到必须支持多字段任意组合排序MySQL 再压榨也有限。这时候有一个很成熟的架构方案把检索与排序相关的数据同步到 Elasticsearch查询和排序完全交给 ESMySQL 只负责事务写入和按主键查询详情。这个方案的落地路径一般是业务表每次增删改通过 Binlog 监听或者双写同步到 ES查询接口改走 ES构造排序 DSL返回的主键列表再回 MySQL 取详情。代价是多了一套中间件、一套数据同步链路和一致性保障方案但对超大规模数据下的灵活排序场景这是最「正经」的解法。如果是偏分析型的大数据量排序比如运营后台要按各种维度组合排序导出报表那可以引入 ClickHouse 这类列式存储引擎。它在亿级数据下做排序和聚合的能力远超 MySQL而且 SQL 语法兼容度高写起来并不陌生。6.3 一条慢SQL的优化顺序最终决策树给到团队内部的一套决策顺序不用想着绕开走按这个树判断基本不跑偏先看执行计划。确认是否Using filesort、是否扫描超大行数。改写 SQL 缩小排序集。能先引主键再关联就别大宽表排序能减少 SELECT 列就别把整行拉出来。用索引天然有序性消灭排序。等值条件 排序字段建立联合索引能降序就降序索引。限制深分页需求。产品层解决大于 N 页走游标或限定条件。调整参数与硬件。确认Sort_merge_passes大量存在时在受控并发的范围内调大sort_buffer_size无果则换 SSD/优化 tmpdir。最后才上架构兜底。同步到 ES 或 ClickHouse前提是业务量确实值这个成本。6.4 一些实操体会做慢SQL优化这些年我最大的感受是一条排序慢SQL背后往往不只是一个技术问题而是一个「需求到底合不合理」的问题。你以为你在优化性能实际上你是在帮产品经理还技术债。所以遇到这种问题别急着只改代码先花十分钟搞清楚业务场景到底需要什么。很多时候把第 15000 页直接禁掉比任何优化都有效。另外优化完之后一定要做回归验证。把优化前后的执行计划、慢日志聚合结果、以及 P95 耗时都留档不仅方便自己复盘也能在下次业务增长时对比出到底是因为量涨了还是因为代码质量滑坡。排序场景的慢SQL根子往往在「让数据库反复做大而无当的全量排序」这个错误设计上只要把排序集缩小、让索引承担排序、把深分页的 offset 从源头砍掉绝大多数问题都能在数据库层面解决得干干净净。