慢SQL里的“大量数据排序”我接下来说的事情应该是很多后端同学都踩过的坑。它表面上看是数据库慢查询实际上背后牵涉到索引设计、缓存利用、SQL改写甚至业务逻辑取舍。这篇文章我想从一次真实的生产事故开始讲起把排序场景下的慢SQL原理、定位手段和优化方案一次说透内容偏MySQL但思路对PostgreSQL、OceanBase这类关系型数据库同样适用。无论你是正在排查线上告警还是在做SQL评审这篇文章都值得往下看。1. 一次真实的生产教训排序慢不是慢在排序本身1.1 现象与第一反应某天线上监控突然报出一条慢SQL高峰期执行时间飙到5秒多查询语句长这样SELECT order_id, user_id, pay_amount, status, created_at FROM orders WHERE status 2 ORDER BY pay_amount DESC LIMIT 20;表本身不大当时数据量才2000多万行即使过滤出来也有300多万行参与排序。第一反应是“给排序列加索引”。于是直接执行ALTER TABLE orders ADD INDEX idx_status_pay_amount (status, pay_amount);以为万事大吉跑了一遍发现耗时反而更差了有的请求直接到10秒。执行计划显示走了索引还用了Using filesort。当时一头雾水后来静下心把执行计划完整看了一遍才意识到问题出在索引字段的选择顺序以及回表成本上。1.2 教训先看执行计划再看数据特征那次事故给我的教训非常直接在讨论排序优化之前必须先把执行计划看清楚把数据分布摸清楚。上面这个索引没生效本质原因是status 2这条件里符合条件的数据量占了三分之一优化器估算回表加排序的成本后选择了全表扫描加文件排序。也就是说过滤性不够强加再多索引都可能是负优化。从那以后我遇到任何带ORDER BY的慢SQL都会先问自己这几个问题排序字段是什么类型长度多少WHERE条件筛选出来的结果集有多大需要返回哪些字段能否全部走覆盖索引排序方向是升序还是降序索引能否匹配分页的LIMIT offset, sizeoffset是否很大把这些问题回答完基本就能确定优化方向。大多数时候大量数据排序的慢不是“排序动作”本身慢而是为了排序要搬运的数据太多或者排序完拿到结果后还要回表这双重成本叠加才让SQL慢到不能忍。2. 深入理解排序发生在哪为什么这么慢2.1 文件排序filesort到底做了什么很多开发知道Extra里出现Using filesort就紧张但真正理解它的人并不多。filesort并不一定表示“在磁盘上排序”它只是MySQL官方对“无法使用索引排序时必须额外做一次排序操作”的统一称呼排序过程既可能在内存中完成也可能溢出到磁盘。当你看到Using filesort时MySQL大致是这样工作的根据查询条件把满足条件的行读取出来对于每行记录提取排序需要的字段和查询返回字段如果排序字段和返回字段较短则直接放入sort buffersort_buffer_size指定大小中如果sort buffer放不下就把中间结果分块写到磁盘临时文件中每个块内部排好序最后对多个有序块做归并排序排序完成后如果采用“全字段排序”直接返回结果如果采用“rowid排序”则根据排好序的主键或rowid再回表获取完整行数据。这里有两种排序模式值得单独讲讲全字段排序把SQL里SELECT需要的所有字段都放进sort buffer。优点是排序完直接返回不需要回表缺点是如果字段很多、值很大buffer很快装满导致更多磁盘临时文件操作。rowid排序只在sort buffer里放“排序字段”和“主键”排序完成后再根据主键去聚簇索引查整行。优点是sort buffer可以装载更多行减少磁盘归并缺点是需要额外回表可能产生大量随机I/O。MySQL到底选哪种模式通常由max_length_for_sort_data参数和查询字段总长度共同决定。字段总长度超过max_length_for_sort_data默认1024字节则倾向于使用rowid排序。这就是为什么“大量数据排序”场景会同时出现“磁盘临时文件”和“回表随机读”两种开销。你看到的慢其实是这两种动作叠加的累积。2.2 什么时候会用到索引排序什么时候不会MySQL使用索引来避免filesort前提是排序字段满足“索引最左前缀”的规则。最典型的情况-- 联合索引 idx_age_salary (age, salary) SELECT * FROM employee WHERE age 30 ORDER BY salary; -- 这个SQL可以直接用索引完成排序因为age用到了索引salary作为第二列正好满足最左前缀。 SELECT * FROM employee WHERE age 30 ORDER BY salary; -- 这个SQL就不能用索引排序因为age用了范围查询后面的salary列无法保证全局有序只能filesort。除了范围查询导致索引排序失效之外下面这些情况也会让优化器放弃索引排序** ORDER BY字段方向不一致例如索引是(a ASC, b ASC)SQL是ORDER BY a DESC, b ASC排序方向冲突MySQL 8.0对方向处理比旧版好但不是所有场景都能用上**排序字段出现在表达式或函数中例如ORDER BY YEAR(create_time)**排序字段不满足索引最左前缀例如索引(age, salary)SQL却ORDER BY salary前面没有age等值条件**强制用不上索引排序的统计信息问题比如优化器认为全表扫描比走索引更快。所以设计“可以避免排序”的索引核心原则是让ORDER BY字段紧跟WHERE条件里已经匹配的等值字段后面且不要跨过一个范围条件。这句话值得反复记。2.3 内存排序 vs 磁盘排序sort_buffer_size不是万能药遇到大量数据排序很多人第一时间想到调大sort_buffer_size。这确实有效但效果有限。sort_buffer_size是MySQL每个排序线程私有的内存区域。如果设置成4MB排序数据量小于4MB时全部内存排序快得很但一旦超过这个值多出来的数据就需要写临时文件分段排序后归并。临时文件路径、大小都能通过状态变量Sort_merge_passes看到。我见过很多人把sort_buffer_size调到64MB甚至更大期望彻底消灭磁盘排序。但要知道这个参数是每个连接独占的高并发下直接导致内存爆炸。假设线上同时有100个排序请求每个分配64MB瞬间就是6.4GB内存消耗这比慢SQL本身还危险。那么正确的做法是什么是多管齐下优先让排序走索引完全避免filesort即使需要filesort也要减少参与排序的行宽通过max_length_for_sort_data引导使用rowid排序避免全字段占用巨大内存最后才结合并发量谨慎微调sort_buffer_size。我之前优化一个订单报表接口时调大sort_buffer_size确实把耗时从5秒压到2秒但并发一高数据库内存告警。后来通过改索引和延迟关联把SQL压到200毫秒以内这段经历告诉我内存参数是止痛药不是治病根的药。3. 大量数据排序场景核心优化套路3.1 优化1设计能覆盖排序与过滤的联合索引这是最根本的优化手段。目标很简单让排序字段直接走索引顺序让MySQL连排序算法都不用跑。设计时需要同时覆盖WHERE条件和ORDER BY字段。举个例子业务表payment_record有这些字段CREATE TABLE payment_record ( id BIGINT NOT NULL AUTO_INCREMENT, merchant_id BIGINT NOT NULL, channel VARCHAR(32) NOT NULL, pay_amount DECIMAL(10,2) NOT NULL, status TINYINT NOT NULL, created_at DATETIME NOT NULL, PRIMARY KEY (id), KEY idx_merchant_channel (merchant_id, channel) ) ENGINEInnoDB;业务场景是某个商户在某个渠道下按支付金额排序分页获取记录SELECT id, pay_amount FROM payment_record WHERE merchant_id 1001 AND channel wxpay ORDER BY pay_amount DESC LIMIT 20;优化前索引是idx_merchant_channel(merchant_id, channel)能满足过滤条件但排序只能filesort。改进方案是把主查询改成覆盖索引ALTER TABLE payment_record ADD INDEX idx_merchant_channel_amount (merchant_id, channel, pay_amount);这个索引能同时服务WHERE和ORDER BY执行计划里Extra不再出现Using filesort而是显示Using index condition甚至直接Using index速度会有数量级的提升。这里有个小细节ORDER BY pay_amount DESC而索引默认是ASCMySQL 5.7里如果建的是(merchant_id, channel, pay_amount)需要额外处理DESC方向的排序。MySQL 8.0支持降序索引可以直接建pay_amount DESC。旧版本里单字段排序反序读取索引即可成本不算高但如果多字段方向不一致就另说了。3.2 优化2减少排序字段和行宽度让一个sort buffer装下更多候选数据如果无论如何都要filesort那就尽量让“挤在sort buffer里的每行数据”短一点。sort buffer容量固定能装下的行数越多需要写临时文件的概率就越低。具体操作包括SELECT只保留必要字段不要把不需要的TEXT、BLOB、超长VARCHAR字段一下子全选出来。大字段会直接推高max_length_for_sort_data判定导致MySQL选择rowid排序后面还得回表。拆分宽表字段如果业务允许把很少用到的大字段放到附属表或者把超长内容放到对象存储数据库只留引用。排序字段尽量使用数字/日期等定长类型少用长字符串排序。字符串排序的排序字节序规则也复杂性能和数字类型不是一个量级。考虑使用LATERAL DERIVED或子查询来做聚合排序不过这属于高级玩法普通场景不推荐。举一个我实际改过的例子原业务SQL返回了30多个字段其中还包括一个remark列类型VARCHAR(2000)。加上排序字段后每行数据超过4KB。SQL执行时338万行候选数据全部进磁盘归并平均耗时8.7秒。后来把查询改成“先查主键和排序字段分页后再回表”一行sort buffer只占几十字节内存基本能装下耗时降到了0.9秒。这还没换硬件、没加内存纯粹是“行宽瘦身”带来的效果。3.3 优化3延迟关联先取主键再回表大量数据排序场景里延迟关联deferred join是极其常用的一招。它的思想是先用过滤条件和排序所需字段获取满足条件的主键ID集合这一步尽量走覆盖索引对ID集合排序并分页拿到最终需要的ID列表后再通过主键关联原表获取完整业务字段。说起来简单但收益巨大。对比一下-- 原方案全字段排序回表 SELECT * FROM payment_record WHERE merchant_id 1001 AND channel wxpay ORDER BY pay_amount DESC LIMIT 200 OFFSET 100000; -- 延迟关联方案 SELECT * FROM payment_record INNER JOIN ( SELECT id FROM payment_record WHERE merchant_id 1001 AND channel wxpay ORDER BY pay_amount DESC LIMIT 200 OFFSET 100000 ) tmp USING(id);子查询tmp里只取id和排序字段所有数据都能塞进内存排序甚至走覆盖索引外层再根据20条ID回表查询随机I/O只有20次。相比原方案直接把300多万行全字段搬运、排序、再回表代价天差地别。注意MySQL优化器有时候会因为统计信息和成本估算自动把子查询改成半连接semi-join导致延迟关联失效。检查执行计划时一定要确认子查询里仍然只取主键。必要时可以用STRAIGHT_JOIN或者加上NO_MERGE提示锁住执行计划确保延迟关联真正生效。3.4 优化4分页排序的深坑与极限优化思路大量数据排序里最容易被忽视的陷阱是大偏移量分页。LIMIT 100000, 20这个写法看起来很省事实际上MySQL会先把前面100020行全部排好序然后丢弃前100000行只返回最后20行。前面那些数据的工作全部白费。这类慢SQL的优化思路有几个方向方案A通过“上次读取到的位置”替代大偏移量如果业务允许把分页方式从“页码”改成“游标”-- 第一页每页20条按id降序 SELECT * FROM payment_record WHERE id 100000 ORDER BY id DESC LIMIT 20; -- 第二页以上次返回的最小id作为游标 SELECT * FROM payment_record WHERE id 100050 ORDER BY id DESC LIMIT 20;如果排序字段不是唯一的可以拼上主键作为游标确保稳定排序WHERE (pay_amount, id) (100.50, 100000) ORDER BY pay_amount DESC, id DESC LIMIT 20;这种“seek method”的效率极高因为每次都直接从索引定位到目标位置不需要扫描无关数据。缺点是不支持传统页码跳转只能一页一页往下翻。好在绝大多数业务都是连续翻页能接受。方案B延迟关联覆盖索引组合即便非要用大偏移量也可以通过延迟关联把“排序分页”限制在覆盖索引内部SELECT * FROM payment_record INNER JOIN ( SELECT id FROM payment_record WHERE merchant_id 1001 ORDER BY pay_amount DESC LIMIT 200 OFFSET 100000 ) tmp USING(id);方案C从业务层禁止深分页对管理后台、导出任务这类场景可以直接限制最大页数或者改用“加载更多”的交互。合理的产品设计能消灭最棘手的SQL文本优化只是兜底。4. 实战复盘一个报表接口从5.2s到180ms4.1 业务场景与SQL原文有一次做电商运营数据报表接口按“大区门店销售额”排序展示某促销期间的数据。原SQL如下SELECT o.id, o.shop_id, o.shop_name, o.region, o.sale_amount, o.order_count, o.customer_cnt, o.settlement_status, o.created_at FROM sales_summary o WHERE o.biz_date 2024-05-10 ORDER BY o.sale_amount DESC LIMIT 100 OFFSET 5000;sales_summary表当时已经积累了1.2亿行按天拆了分区。biz_date2024-05-10分区过滤后还剩约220万条记录需要排序。执行计划显示分区范围扫描然后Using filesort完事还有回表。线上平均5.2秒报表导出时并发一高能把主库CPU打满。4.2 分析与改造过程第一步看索引。当时表上只有主键和idx_biz_date(biz_date)排序只能filesort。我尝试加索引ALTER TABLE sales_summary ADD INDEX idx_biz_date_saleamount (biz_date, sale_amount DESC);执行计划确实从Using filesort变成了Using index condition但耗时才降到4秒多并没有质变。原因是虽然排序走索引了但SQL里SELECT了10个字段这些字段都不在索引里MySQL需要根据每一行记录的主键回表读取完整数据。220万次随机I/O才是最大的耗时点。第二步改成延迟关联。先把查询拆成两步SELECT * FROM sales_summary o INNER JOIN ( SELECT id FROM sales_summary WHERE biz_date 2024-05-10 ORDER BY sale_amount DESC LIMIT 100 OFFSET 5000 ) tmp ON o.id tmp.id;这里的子查询只访问idx_biz_date_saleamount索引里已经包含biz_date、sale_amount、id主键所以整个排序分页过程可以在覆盖索引里完成不需要回表。子查询取出来的100个ID再回原表查完整数据回表次数只有100次。第三步进一步删减报告字段。我发现shop_name其实没必要在数据库里存改成关联维表获取表里的shop_name字段去掉后行宽下降回表成本进一步降低。改造后的执行计划子查询显示Using index覆盖索引外层回表100行无关痛痒。最终耗时稳定在180ms左右和之前5.2秒对比性能提升接近30倍。4.3 前后对比与方案取舍项目优化前优化后参与排序的数据量220万行220万行排序字段是否走索引否是是否回表220万次随机I/O100次随机I/O执行时间5.2s0.18s内存/磁盘压力磁盘大量临时文件内存排序无临时文件这个案例中我没有调整任何MySQL参数也没改分页逻辑纯靠“联合索引覆盖索引延迟关联”就解决了问题。实际优化中很多问题根本不需要上升到调参层面SQL本身解决不了才考虑参数和架构调整。5. 慢SQL排序场景的排查思路与工具5.1 从慢日志抓到候选语句大部分数据库排障都从慢查询日志开始。MySQL里开启慢日志一般这样配置# 在my.cnf中配置 slow_query_log ON slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 2 log_queries_not_using_indexes OFF注意log_queries_not_using_indexes建议关掉否则大量小查询因为没有索引被记进日志日志会迅速膨胀反而干扰真实问题。抓到慢SQL后不要急着改。先看全貌这条SQL一天执行几次每次执行多慢执行频率高的慢SQL优先级高候选集有多大排序量级是几百行还是百万行量级决定优化方案的复杂度数据库负载如何如果是高峰期集中出现的批量任务可能就是并发排队放大出来的问题。5.2 用执行计划快速定位排序问题执行计划是慢SQL排查的核心。对于排序相关的问题重点看这几列type如果出现ALL或index说明全表扫描或用索引序遍历全索引多半有问题key实际用到的索引是不是预期中的索引rows估算的扫描行数和实际数据量对比判断估算是否离谱ExtraUsing filesort、Using temporary、Using index、Using index condition这些关键字一出现排序问题就一目了然。我用EXPLAIN加FORMATJSON比较多EXPLAIN FORMATJSON SELECT ...JSON格式会给出每条操作的成本评估比如sort_cost、query_cost可以对比不同改写方案的代价避免按直觉瞎猜。当Using filesort出现在子查询里时可以通过JSON看到具体哪一步在排序方便定向优化。5.3 优化排序的常见问题速查问题现象可能原因解决方法明明有索引还是Using filesortORDER BY字段不满足最左前缀或索引字段存在范围条件调整索引字段顺序把等值条件放在前面排序慢且内存暴涨sort_buffer_size过大高并发导致内存爆炸不要盲目调大sort_buffer_size优先减少参与排序的行数和行宽分页越深越慢LIMIT offset过大扫描并丢弃大量已排序记录改游标分页或延迟关联限制offset深度的代价排序结果与期望顺序不一致多字段排序方向与索引定义不一致MySQL 8.0使用降序索引或改写ORDER BY保证与索引方向一致filesort期间磁盘使用率飙升排序数据量超过sort buffer内存容量减少排序字段、使用覆盖索引、延迟关联减少临时文件写入查询返回字段太多回表严重SELECT *或返回了大量大字段只返回业务必需字段或将大字段拆出主表还有两个容易被忽略的小技巧如果明确知道结果集只需要一条可以直接在SQL末尾加LIMIT 1。MySQL在排序时能提前终止部分流程虽然对filesort来说停止不全但某些场景下能优化一截。排序字段允许时尽量与“过滤条件”使用同一索引的最左前缀。例如WHERE status IN (1,2) ORDER BY created_at DESC最好建立(status, created_at)索引并把IN改写成UNION ALL等值条件让排序更可控。不过IN范围也会影响最左前缀这里需要具体分析不能一概而论。在真实项目里慢SQL优化不是一锤子买卖。我在一次大促前优化过一个排行榜接口当时看执行计划、加索引、延迟关联都做了线上稳定了一段时间。后来业务把过滤条件从“单城市”改成“多城市”索引的最左前缀直接被多个城市IN条件打断又重新触发filesort性能掉回1秒多。最后花了很大力气把多城市拆成子查询再合并排序才彻底解决问题。这个案例告诉我慢SQL排查不能只负责“把当前语句跑快”还得考虑优化方案对索引结构的依赖以及未来业务扩展可能带来的变化。提前留出弹性好过SQL每次都炸在高峰上。