企业官网建设流程全解析

凌晨一点，监控群里弹出告警：电商后台的订单分页接口，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;

执行计划的结果大致如下：

rows预估 1216 万行，Extra里明晃晃写着Using filesort，连key都是 NULL。也就是说，这条 SQL 没走任何索引，全表扫了一遍，然后对所有数据做文件排序。看到这份执行计划，基本可以断定问题出在「排序」和「深分页回表」两层，没跑。

1.3 判断排序慢SQL的三个优先级问题

拿到执行计划后，我一般按照下面的顺序快速判断问题到底卡在哪一层：

1. 有没有走索引？key列是 NULL 还是走了辅助索引。如果possible_keys有但key是 NULL，说明优化器认为索引帮不上忙，典型的场景就是范围条件之后跟着排序字段。
2. 有没有Using filesort？有则意味着排序动作无法由索引天然完成，要从当前结果集中再排一遍。
3. 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 | 1216

Sort_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 = 'enabled=on'; -- 执行刚才那条慢 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的检查路径，下次可以直接抄作业：

这套组合一起用，基本能把一条排序慢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 万行）可以拿来参考：

注意，耗时是相对值，不同配置下会有差异，但趋势是一致的：让索引去做排序、让小的数据集去承担深分页，收益立竿见影。

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 三种分页模式的取舍

不是所有排序都适合用索引解决。当排序字段来自多张表、或带有复杂的聚合计算，比如按「商品销量+评分综合分」排序，这时候关系型数据库想靠索引一把梭已经没有可能。我把常见的分页模式整理成一张对比表：

从实践角度，我见过很多团队在前 10 页用游标分页，超过 10 页直接提示用户缩小查询范围，体验和性能都保住了。

6.2 当 MySQL 力不从心：搜索引擎与列式存储兜底

如果业务真的复杂到必须支持多字段任意组合排序，MySQL 再压榨也有限。这时候有一个很成熟的架构方案：把检索与排序相关的数据同步到 Elasticsearch，查询和排序完全交给 ES，MySQL 只负责事务写入和按主键查询详情。

这个方案的落地路径一般是：业务表每次增删改通过 Binlog 监听或者双写同步到 ES；查询接口改走 ES，构造排序 DSL；返回的主键列表再回 MySQL 取详情。代价是多了一套中间件、一套数据同步链路和一致性保障方案，但对超大规模数据下的灵活排序场景，这是最「正经」的解法。

如果是偏分析型的大数据量排序，比如运营后台要按各种维度组合排序导出报表，那可以引入 ClickHouse 这类列式存储引擎。它在亿级数据下做排序和聚合的能力远超 MySQL，而且 SQL 语法兼容度高，写起来并不陌生。

6.3 一条慢SQL的优化顺序：最终决策树

给到团队内部的一套决策顺序，不用想着绕开走，按这个树判断基本不跑偏：

1. 先看执行计划。确认是否Using filesort、是否扫描超大行数。
2. 改写 SQL 缩小排序集。能先引主键再关联就别大宽表排序；能减少 SELECT 列就别把整行拉出来。
3. 用索引天然有序性消灭排序。等值条件 + 排序字段建立联合索引，能降序就降序索引。
4. 限制深分页需求。产品层解决，大于 N 页走游标或限定条件。
5. 调整参数与硬件。确认Sort_merge_passes大量存在时，在受控并发的范围内调大sort_buffer_size，无果则换 SSD/优化 tmpdir。
6. 最后才上架构兜底。同步到 ES 或 ClickHouse，前提是业务量确实值这个成本。

6.4 一些实操体会

做慢SQL优化这些年，我最大的感受是：一条排序慢SQL背后往往不只是一个技术问题，而是一个「需求到底合不合理」的问题。你以为你在优化性能，实际上你是在帮产品经理还技术债。所以遇到这种问题，别急着只改代码，先花十分钟搞清楚业务场景到底需要什么。很多时候把第 15000 页直接禁掉，比任何优化都有效。

另外，优化完之后一定要做回归验证。把优化前后的执行计划、慢日志聚合结果、以及 P95 耗时都留档，不仅方便自己复盘，也能在下次业务增长时对比出到底是因为量涨了，还是因为代码质量滑坡。排序场景的慢SQL，根子往往在「让数据库反复做大而无当的全量排序」这个错误设计上，只要把排序集缩小、让索引承担排序、把深分页的 offset 从源头砍掉，绝大多数问题都能在数据库层面解决得干干净净。