上个月排查客服后台慢SQL我被一条order by查询折磨了一个下午。MySQL order by性能优化听起来是老生常谈可真到线上出问题时你依仗的绝不是“加个索引”这种一句话经验。SQL本身不长SELECT * FROM order_info WHERE seller_id 128744 AND status 2 ORDER BY created_at DESC LIMIT 20;WHERE条件两个字段都建了单列索引单表数据量也不过千万级可高峰期这条查询硬是跑了4.7秒把一个8核16G实例的CPU直接打满。explain结果里Extra列明晃晃写着Using filesort。就这一行输出背后牵扯出的是MySQL排序的完整执行链路、索引与排序的匹配边界、排序缓冲区的取舍以及业务模型层面的改造思路。这篇文章适合三类人每天被慢查询日志轰炸的DBA写SQL时想知道“为什么这里会排序”的后端开发以及正在面试中被问到order by原理、想系统补课的同学。我会按“执行原理 - 索引排序边界 - filesort底细 - 排查手段 - 实战优化 - 调参与schema建议”这条线往下走所有结论都基于InnoDB引擎、MySQL 5.7/8.0的实测经验。1. 一条带order by的SQL在MySQL里到底经历了什么1.1 从SQL到结果的完整执行链路很多人以为order by是存储引擎里顺手完成的其实排序发生在MySQL server层跨引擎通用。一条查询从客户端发出要经过连接器验证权限、分析器词法/语法解析、优化器、执行器这几个阶段。优化器是最关键的决策者它根据表的统计信息行数、索引基数、筛选比例等判断用哪条路径成本最低同时决定排序策略。执行器拿到执行计划后通过存储引擎接口逐行读取满足条件的记录该回表的回表然后把需要排序的行交给server层的排序模块。这里有个8.0用户容易忽略的变化MySQL 8.0彻底移除了查询缓存也就是说每次执行都是一次完整的链路不存在“缓存了就不用排序”的侥幸。我习惯把这个过程类比成整理书架——索引相当于书架按字母排好的标签习惯了就直接按位置拿书没有索引排序时就像把一堆乱书搬到书桌上重新排书桌放不下还得摊到地板磁盘临时文件上慢慢归并。1.2 三种排序路径索引有序、filesort和临时表从执行结果看一条带order by的查询最终只会落入三种情况路径是否需要额外排序是否可能回表常见触发场景索引有序扫描否视查询列是否被索引覆盖排序字段满足索引最左前缀内存filesort是视排序采用单路或双路结果集较小sort buffer装得下磁盘filesort/临时表是是结果集超过sort buffer或涉及去重/合并索引有序扫描是理想态B树的叶子节点天然有序MySQL沿着索引顺序读下去输出自然有序完全不需要执行器再做排序动作explain里也不会出现Using filesort。内存filesort则是把需要排序的记录全部读进sort buffer在内存里完成排序后返回速度尚可但CPU成本不低。磁盘filesort是性能杀手sort buffer装不下时MySQL会把数据分块排序后写到磁盘临时文件再通过归并排序合并每多一次磁盘I/O查询延迟都成倍上升。临时表则是另一条分支。当SQL里同时出现DISTINCT、GROUP BY、UNION这类需要“先合并再去重”的操作时MySQL可能先建一张临时表把这些中间结果装进去再在临时表之上做排序。所以从慢查询日志里看到Using temporary和Using filesort同时出现时问题往往不只是排序本身还牵扯到临时表的设计。这里给出我的结论order by优化的本质就是尽量把查询变成“第一种情况”如果做不到就尽量让filesort发生在内存里而不是磁盘上。后面四章全部围绕这条主线展开。2. 索引排序的边界与陷阱最左前缀、范围查询和反向扫描2.1 最左前缀对order by的约束联合索引是最常用的排序优化手段但它的排序能力受最左前缀规则严格约束。假设表结构CREATE TABLE trade ( id BIGINT PRIMARY KEY, seller_id INT NOT NULL, order_no VARCHAR(32), status TINYINT, amount DECIMAL(12,2), created_at DATETIME NOT NULL, KEY idx_seller_status_time (seller_id, status, created_at) ) ENGINEInnoDB;基于 idx_seller_status_time以下几类order by可以直接利用索引排序WHERE seller_id ? ORDER BY status等值条件消耗第一列后续status有序WHERE seller_id ? AND status ? ORDER BY created_at两列等值都消耗掉created_at在索引内有序WHERE seller_id ? ORDER BY status, created_at第一列等值后其余列按索引顺序排列。不能利用索引排序的典型写法WHERE seller_id ? ORDER BY created_at跳过了status这一列B树只在seller_id、status、created_at三列同时有序。你可以把索引看成嵌套的目录册第一层按seller_id分组第二层按status分组第三层才按created_at排序。只指定seller_id后索引只是把多组(status, created_at)子序列拼在一起其中每组内部有序但组与组之间并不是全局有序所以排序必须靠filesort完成。ORDER BY created_at LIMIT 10没有条件消耗seller_id整棵索引树根本没有按created_at全局有序自然用不上。最常用的判断口诀是把WHERE里的等值条件按索引从左到右消耗消耗完之后剩余的排序字段必须正好接在索引的下一列不能跳列方向还要一致。2.2 范围条件与混合排序方向那些让人措手不及的filesort等值条件消耗索引列之后一旦进入范围条件排序能力就会断掉。比如WHERE seller_id ? AND status 2 ORDER BY created_atstatus用到了范围扫描那么status之后的所有列都无法再保证有序created_at排序只能走filesort。这是个特别容易踩的坑很多人以为“created_at在索引里肯定能用”却没有意识到它前面的列被范围条件截断了。另外一类是排序方向的问题。B树索引默认按升序组织如果你写ORDER BY created_at ASC可以顺着扫写ORDER BY created_at DESC理论上也能反着扫8.0.13之后优化器对反向扫描的支持更完善单列降序排序通常能直接利用索引。但一旦出现混合方向比如ORDER BY status ASC, created_at DESC5.7和8.0早期版本就无法通过同一个索引同时满足两个方向必须filesort。要解决这种场景8.0可以建立真正意义上的降序索引ALTER TABLE trade ADD KEY idx_status_time_desc (status ASC, created_at DESC);这样ORDER BY status ASC, created_at DESC就能天然走索引。我在8.0.32上验证过混合方向的排序从Using filesort变成Using index性能提升非常明显。2.3 反向扫描与8.0降序索引的实战价值反向扫描的实用性比想象中大。社交App的时间线、电商订单倒序展示几乎都是ORDER BY created_at DESC而很多人的索引还是按ASC建的。在支持反向扫描的版本里这个查询本身也能走索引所以不要一看到DESC就默认“要filesort”。真正的分水岭是混合方向和多列排序。如果你要优化的SQL是升序降序混用8.0请直接建降序索引5.7只能靠调整业务排序方向来规避或者干脆用覆盖索引让filesort的成本低到可以接受。还有一个容易被忽略的细节排序稳定性。MySQL官方并不承诺order by结果的稳定性尤其是使用非唯一键排序时相同键值的记录返回顺序可能随版本、数据分布变化。如果你业务上需要“先按时间倒序同时同一时间的记录也要顺序稳定”最稳妥的做法是在排序列后面补一个唯一键比如ORDER BY created_at DESC, id DESC。这既稳定了顺序又让联合索引的排序能力更容易被利用。3. filesort的底细sort buffer、临时表与磁盘归并3.1 sort buffer到底是什么filesort不是一把梭把整张表读进内存再排而是有明确的缓冲机制执行器读取满足WHERE条件的行从行中抽取排序字段和查询需要的列写入sort buffer当sort buffer满MySQL对当前缓冲区内数据做一次内存排序把排序结果写成一个临时chunk到磁盘继续读取后续行重复2和3全部读完后对多个磁盘chunk做归并排序最终得到完整有序结果。这里的sort buffer大小由sort_buffer_size控制默认256KB部分版本默认值略有差异。它是个会话级内存不是全局共享的意味着每个并发连接都有自己的sort buffer。如果一条SQL要排序的数据量超过bufferSort_merge_passes状态变量就会增长每次增长都代表一次磁盘溢出和归并。所以我在排查排序性能时第一个看的指标就是Sort_merge_passes。一个非常有用的细节是如果SQL里带LIMIT N比如ORDER BY created_at LIMIT 10MySQL的排序模块会针对这个场景使用priority queue优先队列算法只保留当前最小的N行并不需要对全部候选行做完整排序。这意味着即使候选集很大只要LIMIT较小内存占用和控制成本都可能低于预期。这也是为什么很多优化建议里强调“排序尽量配合LIMIT”。3.2 单路排序与双路排序宽字段的经典博弈网络上关于filesort的文章十有八九会提到“单路排序”和“双路排序”。这套机制在5.7及更早版本里确实存在由max_length_for_sort_data参数决定当一行需要排序的数据总长度超过这个阈值时MySQL采用老式的双路排序只把排序键和主键放进sort buffer排序完成后根据主键回表读取完整行数据总长度小于阈值时采用单路排序把所有需要的列一起放进sort buffer排完直接输出省掉回表。这套机制在8.0.20之后被移除了MySQL统一了排序策略不再让用户在单路和双路之间纠结。但了解它仍然有用因为很多生产环境还在运行5.7而且“宽行导致排序内存膨胀”这件事本身没有消失。我见过一个真实的例子一张表有十几个varchar字段开发图省事写了SELECT * FROM xxx ORDER BY score LIMIT 20单行数据1000多字节5.7判断超过max_length_for_sort_data后走双路排序排序键加上主键只占很小内存看起来好像没问题但双路排序多了一次全表回表加上WHERE条件本身过滤性不强回表开销直接把查询拖垮。后来改成只查需要的列同一SQL耗时从3秒降到200毫秒。3.3 临时表为什么比filesort更可怕临时表和filesort经常一起出现但临时表的代价通常更高。什么时候会产生临时表我把高频场景列一下GROUP BY8.0之前还自带隐式排序本来只想去重统计结果多了一次排序DISTINCT结果去重需要先放临时表UNION默认会去重除非用UNION ALLORDER BY的字段不在SELECT的GROUP BY列里且无法用索引解决排序或查询涉及TEXT/BLOB类型字段MySQL需要为它们建临时表存储。MySQL的临时表分内存临时表和磁盘临时表。内存装不下时会自动落盘而一旦落盘读写就是磁盘I/O和sort buffer溢写一样致命。我处理过一个报表SQLSELECT client_id, COUNT(*) FROM logs GROUP BY client_id ORDER BY cnt DESClogs表有text字段参与输出结果MySQL必须建临时表临时表超过max_heap_table_size后落盘整个查询跑了30多秒。改成先按client_id聚合应用层再做排序或者去掉text字段的排序性能问题立刻没了。记住一个判断原则explain的Extra里如果出现Using temporary先考虑能不能用索引替代临时表如果不行就考虑让临时表留在内存里。和filesort一样临时表最怕的就是落盘。4. 从explain到performance_schema定位排序瓶颈的完整链路4.1 explain结果里那些排序暗号explain是定位排序问题的第一工具重点看type、key、Extra三列。先说Extra出现Using filesort意味着本次查询有额外排序动作出现Using temporary意味着用了临时表两者同时出现属于“排序临时表”最坏组合。如果Extra里有Using index且没有Using filesort说明覆盖索引让排序和取数都在索引里完成这是最理想的状态。type列决定访问路径。常见值从好到差依次是const、ref、range、index、ALL。index在这里有迷惑性它扫的是整个索引但未必是高效的因为可能是索引全扫描而非定位后的扫描。比如ORDER BY created_at LIMIT 100如果只有单个created_at索引execution type可能是indexExtra没有filesort看起来很美但它是把整个索引从头到尾扫一遍再取100条代价随表大小线性增长。一条经常看的经验排序优化的成就感不是“消除了Using filesort”而是“让查询需要的行数和扫描路径同时变小”。我曾见过同事加了一堆索引把所有慢查询都改成Using index结果总索引空间比表还大写放大严重。排序优化要在性能和成本之间找平衡。4.2 用状态变量和performance_schema量化排序开销explain只给方向要量化还得靠状态变量。最常用的三条Sort_rows执行排序的行数反映排序规模Sort_merge_passes磁盘溢写和归并次数持续增长说明sort buffer不够Sort_range / Sort_scan说明排序动作的触发方式。SHOW SESSION STATUS LIKE Sort_%;如果需要更细的耗时分布可以查performance_schemaSELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT / 1000000000 AS total_ms FROM performance_schema.events_stages_summary_global_by_event_name WHERE EVENT_NAME LIKE %sort%;8.0.18以后还有一颗银子弹EXPLAIN ANALYZE。它不光是预测执行计划而是真实执行SQL并输出每个节点的实际耗时。比如EXPLAIN ANALYZE SELECT * FROM trade WHERE seller_id 3281 AND status 2 ORDER BY created_at DESC LIMIT 20;输出里会明确显示Sort节点的actual time是内存排序还是磁盘排序一眼就能看出来。这是我最近强烈推荐的习惯先explain看方案再EXPLAIN ANALYZE看实测。4.3 一个完整案例从4.7秒到80ms的排查过程回到开头的那个客服后台慢SQL。我的排查顺序是这样的第一步explain查看执行计划。发现typeref命中了seller_id的单列索引但Extra有Using filesort。这说明过滤部分没问题排序无法利用索引。第二步查Sort_merge_passes。SHOW SESSION STATUS LIKE Sort_merge_passes显示为几十次说明排序规模远超sort buffer已经发生了磁盘溢写。为什么因为单列索引只完成了seller_id的过滤created_at排序需要把满足条件的可能几十万行全部读出来排序而客服后台是按天倒序查数据量很容易积累到几十万行。第三步修改索引结构。把单列索引替换为联合索引(seller_id, status, created_at)让排序字段直接吃索引尾巴。改完explainExtra里的Using filesort消失type变为ref扫描行数从几十万降到几千。第四步EXPLAIN ANALYZE确认。Sort节点的实际耗时从秒级降到个位数毫秒整条SQL从4.7秒降到80ms。这里最关键的一步是第三步但最容易被忽略的是第二步。如果排序字段没有吃上索引不能只看explain的Using filesort就去建索引先算清楚“要排序的数据量有多大、是否溢写磁盘”这样才能判断到底该优化访问路径还是优化排序本身。5. 深翻页、覆盖索引与业务模型改造三类排序优化实战5.1 深分页LIMIT 1000000, 20 为什么慢到离谱列表页做分页最容易被攻击的写法是这样的SELECT * FROM trade WHERE seller_id 3281 AND status 2 ORDER BY created_at DESC LIMIT 1000000, 20;逻辑上它只要第1000001到第1000020条但MySQL无法像数组那样直接跳到第100万个偏移量。就算order by走了索引它也必须从索引的最开始或范围起点一直数到第100万个位置扫描1000020行丢弃前面100万行。如果order by还没走索引、走的是filesort那更惨它得把满足条件的所有行都排序完再丢弃前100万行。B树索引的叶子节点是链表结构天然不支持按位置随机偏移这正是深分页的根源。针对这种场景工程上有两种主流优化延迟关联和keyset游标。延迟关联的核心思路是先走覆盖索引拿到本页需要的20个主键再回表取完整行把“扫描100万行”变成“扫描索引100万行但只回表20行”SELECT tr.* FROM trade tr INNER JOIN ( SELECT id FROM trade WHERE seller_id 3281 AND status 2 ORDER BY created_at DESC, id DESC LIMIT 1000000, 20 ) tmp ON tr.id tmp.id ORDER BY tr.created_at DESC, tr.id DESC;子查询里排序和分页需要的字段都在索引(seller_id, status, created_at, id)上是索引扫描不会触发filesort外层只对20行做一次轻量排序再回表。实测三五百万的深分页这种写法通常能把几十秒的查询压到几百毫秒。如果是“下一页”按钮式的产品而不是“跳页”可以彻底绕过偏移量改用keyset游标把上一页最后一条记录的排序键记下来下一页直接从这个位置往后找SELECT * FROM trade WHERE seller_id 3281 AND status 2 AND (created_at 2024-06-01 10:00:00 OR (created_at 2024-06-01 10:00:00 AND id 100123)) ORDER BY created_at DESC, id DESC LIMIT 20;这种方案无论翻多少页扫描量都不变是深分页的最佳解法。前提是你愿意和产品协商把“跳页”改成“流式加载”。5.2 覆盖索引让过滤、排序、取数一次完成排序优化的另一个高效手段是覆盖索引。它的原理很简单如果查询要返回的列全都包含在索引里MySQL就不需要回表直接扫索引树就能把数据查完配合排序字段也在索引中整条查询可以在索引这一棵树内自洽完成。还是订单表举例列表页高频查询是SELECT id, order_no, amount, created_at FROM trade WHERE seller_id 3281 AND status 2 ORDER BY created_at DESC LIMIT 20;如果只建(seller_id, status, created_at)联合索引过滤和排序都解决了但order_no、amount还需要回表。把这几个高频显示的列追加进索引ALTER TABLE trade ADD KEY idx_cover (seller_id, status, created_at, order_no, amount);这时explain里不仅没有Using filesort还会出现Using index回表完全消失。不过我要泼一盆冷水覆盖索引是把双刃剑。索引列越多写入和更新时维护索引的代价越大存储空间也越大。我的经验是只对“高频且列数可控”的查询做覆盖设计别为了覆盖把所有列都塞进索引。索引不是越宽越好是越“贴合高频查询”越好。另外注意索引内字段放置的顺序先等值条件列再排序列最后才是要覆盖的返回列。范围条件列一旦放进去它后面的排序列就废了这一点和前面2.2节讲的范围截断完全一致。5.3 别让业务给数据库出难题ORDER BY RAND()之类的改造有些SQL慢根源不在数据库而在业务提了一个数据库不擅长解决的问题。最经典的就是ORDER BY RAND() LIMIT 1。它的语义是“在整张表里随机取一行”MySQL的实现方式是对全表做一个filesort而且因为排序键是随机函数生成的值无法用任何索引数据量大时必定磁盘溢写。我在一个300万行的活动表上测试过随机抽一条的SQL跑了2.8秒流量稍微上来实例就报警。如果业务只是想“大致随机取一条”完全可以绕开全表排序-- 先取主键的范围 SELECT MIN(id), MAX(id) INTO min_id, max_id FROM trade; -- 在范围内随机一个值再定位到最近的一行 SET target FLOOR(RAND() * (max_id - min_id 1)) min_id; SELECT * FROM trade WHERE id target ORDER BY id LIMIT 1;这个方案只扫一条记录缺点是主键有空洞时随机分布不均匀且抽到的总是“距随机值最近的记录”不是字面意义上的等概率。但对绝大多数营销抽奖、首页推荐场景这个误差完全可以接受。真要更均匀的随机可以在应用层维护一张密集的自增id表先随机取一个连续id再回表代价只是多了张几百行的小表。我真正想说的是DBA能优化的不止是索引。把“不分页的列表”“必须实时的排行榜”“要在千万级表上做随机抽样”这类需求挡在数据库外面本身也是性能优化的一部分。很多时候我和业务方聊十分钟把“必须实时”改成“缓存5分钟”比任何索引调优都有效。6. 参数取舍与schema设计层面的全局建议6.1 sort_buffer_size到底该不该调大先说结论没有统一的“最佳值”只有配合监控的合理配置。sort_buffer_size是会话级内存每个连接都会分配一块独立的排序缓冲区。默认值通常为256KB不少DBA一见到排序慢就把它调到8MB甚至64MB结果高并发下内存瞬间爆炸——1000个活跃连接每个8MB就是8GB再厚的机器也扛不住。我的调优流程是以下三步先看Sort_merge_passes是否持续增长。如果长期为0说明当前缓冲区足够调大纯属浪费内存如果持续大于0先尝试优化SQL和索引把排序规模降下来确认索引无法再压减排序数据量后才去调整会话级的sort_buffer_size从1MB起步观察Sort_merge_passes有没有显著下降。修改方式有两种-- 全局生效只影响新连接 SET GLOBAL sort_buffer_size 1024 * 1024; -- 会话生效只影响当前连接 SET SESSION sort_buffer_size 1024 * 1024;生产环境我更推荐改会话级别或者只针对特定慢查询的账号统一设置别全局开大。MySQL文档里对sort_buffer_size有一个明确的警告增大这个参数要非常谨慎因为它可能让你误以为解决了问题实际上只是把磁盘I/O换成了内存占用而内存是不可压缩资源。6.2 字符集排序规则一个容易被忽略的比较成本排序的本质是比较。数字和日期比较快如闪电字符串比较却要逐字符按排序规则计算。业务表如果默认是utf8mb4拿一个varchar(64)的流水号做排序每次比较都要走utf8mb4的字符映射开销自然不小。8.0默认的utf8mb4_0900_ai_ci比5.7时代的utf8mb4_general_ci更规范但某些场景下计算成本并不低。实战中的建议很朴素能用数字、日期做排序键就不要用字符串字符串排序尽量选短字段超长字段可以先冗余一个hash列用于排序如果确实是字符串且业务上不区分大小写可以用binary排序规则或考虑改成latin1/ascii类列避免对TEXT/BLOB做排序这类字段参与排序基本都会触发临时表。我见过一个案例订单表用varchar(32)存“订单时间戳”开发图省事用它做排序和过滤结果排序慢。后来把数据迁移成了datetime类型列只改了类型同样SQL秒变快就是因为避免了字符串比较的额外开销。别小看这一层排序十万行字符串的CPU消耗是日期的数倍。6.3 新表设计时提前做好的三件事等到线上慢查询日志堆起来再优化永远是被动的。新表设计阶段提前做好三件事能省掉后面大半的排序调优第一明确每个高频查询的排序方向索引的方向跟着排序走。排序是倒序就建倒序索引8.0支持降序索引5.7尽量用覆盖索引兜底。第二把“过滤等值列、排序列、范围列、需要覆盖的返回列”排好序去建联合索引。等值条件列放在最前面排序列紧跟其后范围条件列要非常谨慎地放因为它会截断后面的排序能力。第三从产品层面约定分页模型。大表一律不接受跳页式深分页用keyset游标列表页固定排序键不允许前端动态换排序字段。如果业务必须支持任意列排序那就把数据放到分析型存储里别为难OLTP数据库。我自己的项目规范里还有一条任何慢查询从发现到优化完成必须闭环记录“优化前explain - 优化后explain - 耗时对比”三段信息。没有这三段下次遇到同类问题你还是得从零开始猜。排序优化这件事我做下来最深的体会是真正的收益往往不在最后一公里的索引调整而在你把“为什么要排序、能不能不排序、能不能少排点”这些前置问题想清楚的时候。每次排查慢排序我都先问自己这三个问题把框架立住再去动执行计划和索引基本不会走偏。
上个月排查客服后台慢SQL我被一条order by查询折磨了一个下午。MySQL order by性能优化听起来是老生常谈可真到线上出问题时你依仗的绝不是“加个索引”这种一句话经验。SQL本身不长SELECT * FROM order_info WHERE seller_id 128744 AND status 2 ORDER BY created_at DESC LIMIT 20;WHERE条件两个字段都建了单列索引单表数据量也不过千万级可高峰期这条查询硬是跑了4.7秒把一个8核16G实例的CPU直接打满。explain结果里Extra列明晃晃写着Using filesort。就这一行输出背后牵扯出的是MySQL排序的完整执行链路、索引与排序的匹配边界、排序缓冲区的取舍以及业务模型层面的改造思路。这篇文章适合三类人每天被慢查询日志轰炸的DBA写SQL时想知道“为什么这里会排序”的后端开发以及正在面试中被问到order by原理、想系统补课的同学。我会按“执行原理 - 索引排序边界 - filesort底细 - 排查手段 - 实战优化 - 调参与schema建议”这条线往下走所有结论都基于InnoDB引擎、MySQL 5.7/8.0的实测经验。1. 一条带order by的SQL在MySQL里到底经历了什么1.1 从SQL到结果的完整执行链路很多人以为order by是存储引擎里顺手完成的其实排序发生在MySQL server层跨引擎通用。一条查询从客户端发出要经过连接器验证权限、分析器词法/语法解析、优化器、执行器这几个阶段。优化器是最关键的决策者它根据表的统计信息行数、索引基数、筛选比例等判断用哪条路径成本最低同时决定排序策略。执行器拿到执行计划后通过存储引擎接口逐行读取满足条件的记录该回表的回表然后把需要排序的行交给server层的排序模块。这里有个8.0用户容易忽略的变化MySQL 8.0彻底移除了查询缓存也就是说每次执行都是一次完整的链路不存在“缓存了就不用排序”的侥幸。我习惯把这个过程类比成整理书架——索引相当于书架按字母排好的标签习惯了就直接按位置拿书没有索引排序时就像把一堆乱书搬到书桌上重新排书桌放不下还得摊到地板磁盘临时文件上慢慢归并。1.2 三种排序路径索引有序、filesort和临时表从执行结果看一条带order by的查询最终只会落入三种情况路径是否需要额外排序是否可能回表常见触发场景索引有序扫描否视查询列是否被索引覆盖排序字段满足索引最左前缀内存filesort是视排序采用单路或双路结果集较小sort buffer装得下磁盘filesort/临时表是是结果集超过sort buffer或涉及去重/合并索引有序扫描是理想态B树的叶子节点天然有序MySQL沿着索引顺序读下去输出自然有序完全不需要执行器再做排序动作explain里也不会出现Using filesort。内存filesort则是把需要排序的记录全部读进sort buffer在内存里完成排序后返回速度尚可但CPU成本不低。磁盘filesort是性能杀手sort buffer装不下时MySQL会把数据分块排序后写到磁盘临时文件再通过归并排序合并每多一次磁盘I/O查询延迟都成倍上升。临时表则是另一条分支。当SQL里同时出现DISTINCT、GROUP BY、UNION这类需要“先合并再去重”的操作时MySQL可能先建一张临时表把这些中间结果装进去再在临时表之上做排序。所以从慢查询日志里看到Using temporary和Using filesort同时出现时问题往往不只是排序本身还牵扯到临时表的设计。这里给出我的结论order by优化的本质就是尽量把查询变成“第一种情况”如果做不到就尽量让filesort发生在内存里而不是磁盘上。后面四章全部围绕这条主线展开。2. 索引排序的边界与陷阱最左前缀、范围查询和反向扫描2.1 最左前缀对order by的约束联合索引是最常用的排序优化手段但它的排序能力受最左前缀规则严格约束。假设表结构CREATE TABLE trade ( id BIGINT PRIMARY KEY, seller_id INT NOT NULL, order_no VARCHAR(32), status TINYINT, amount DECIMAL(12,2), created_at DATETIME NOT NULL, KEY idx_seller_status_time (seller_id, status, created_at) ) ENGINEInnoDB;基于 idx_seller_status_time以下几类order by可以直接利用索引排序WHERE seller_id ? ORDER BY status等值条件消耗第一列后续status有序WHERE seller_id ? AND status ? ORDER BY created_at两列等值都消耗掉created_at在索引内有序WHERE seller_id ? ORDER BY status, created_at第一列等值后其余列按索引顺序排列。不能利用索引排序的典型写法WHERE seller_id ? ORDER BY created_at跳过了status这一列B树只在seller_id、status、created_at三列同时有序。你可以把索引看成嵌套的目录册第一层按seller_id分组第二层按status分组第三层才按created_at排序。只指定seller_id后索引只是把多组(status, created_at)子序列拼在一起其中每组内部有序但组与组之间并不是全局有序所以排序必须靠filesort完成。ORDER BY created_at LIMIT 10没有条件消耗seller_id整棵索引树根本没有按created_at全局有序自然用不上。最常用的判断口诀是把WHERE里的等值条件按索引从左到右消耗消耗完之后剩余的排序字段必须正好接在索引的下一列不能跳列方向还要一致。2.2 范围条件与混合排序方向那些让人措手不及的filesort等值条件消耗索引列之后一旦进入范围条件排序能力就会断掉。比如WHERE seller_id ? AND status 2 ORDER BY created_atstatus用到了范围扫描那么status之后的所有列都无法再保证有序created_at排序只能走filesort。这是个特别容易踩的坑很多人以为“created_at在索引里肯定能用”却没有意识到它前面的列被范围条件截断了。另外一类是排序方向的问题。B树索引默认按升序组织如果你写ORDER BY created_at ASC可以顺着扫写ORDER BY created_at DESC理论上也能反着扫8.0.13之后优化器对反向扫描的支持更完善单列降序排序通常能直接利用索引。但一旦出现混合方向比如ORDER BY status ASC, created_at DESC5.7和8.0早期版本就无法通过同一个索引同时满足两个方向必须filesort。要解决这种场景8.0可以建立真正意义上的降序索引ALTER TABLE trade ADD KEY idx_status_time_desc (status ASC, created_at DESC);这样ORDER BY status ASC, created_at DESC就能天然走索引。我在8.0.32上验证过混合方向的排序从Using filesort变成Using index性能提升非常明显。2.3 反向扫描与8.0降序索引的实战价值反向扫描的实用性比想象中大。社交App的时间线、电商订单倒序展示几乎都是ORDER BY created_at DESC而很多人的索引还是按ASC建的。在支持反向扫描的版本里这个查询本身也能走索引所以不要一看到DESC就默认“要filesort”。真正的分水岭是混合方向和多列排序。如果你要优化的SQL是升序降序混用8.0请直接建降序索引5.7只能靠调整业务排序方向来规避或者干脆用覆盖索引让filesort的成本低到可以接受。还有一个容易被忽略的细节排序稳定性。MySQL官方并不承诺order by结果的稳定性尤其是使用非唯一键排序时相同键值的记录返回顺序可能随版本、数据分布变化。如果你业务上需要“先按时间倒序同时同一时间的记录也要顺序稳定”最稳妥的做法是在排序列后面补一个唯一键比如ORDER BY created_at DESC, id DESC。这既稳定了顺序又让联合索引的排序能力更容易被利用。3. filesort的底细sort buffer、临时表与磁盘归并3.1 sort buffer到底是什么filesort不是一把梭把整张表读进内存再排而是有明确的缓冲机制执行器读取满足WHERE条件的行从行中抽取排序字段和查询需要的列写入sort buffer当sort buffer满MySQL对当前缓冲区内数据做一次内存排序把排序结果写成一个临时chunk到磁盘继续读取后续行重复2和3全部读完后对多个磁盘chunk做归并排序最终得到完整有序结果。这里的sort buffer大小由sort_buffer_size控制默认256KB部分版本默认值略有差异。它是个会话级内存不是全局共享的意味着每个并发连接都有自己的sort buffer。如果一条SQL要排序的数据量超过bufferSort_merge_passes状态变量就会增长每次增长都代表一次磁盘溢出和归并。所以我在排查排序性能时第一个看的指标就是Sort_merge_passes。一个非常有用的细节是如果SQL里带LIMIT N比如ORDER BY created_at LIMIT 10MySQL的排序模块会针对这个场景使用priority queue优先队列算法只保留当前最小的N行并不需要对全部候选行做完整排序。这意味着即使候选集很大只要LIMIT较小内存占用和控制成本都可能低于预期。这也是为什么很多优化建议里强调“排序尽量配合LIMIT”。3.2 单路排序与双路排序宽字段的经典博弈网络上关于filesort的文章十有八九会提到“单路排序”和“双路排序”。这套机制在5.7及更早版本里确实存在由max_length_for_sort_data参数决定当一行需要排序的数据总长度超过这个阈值时MySQL采用老式的双路排序只把排序键和主键放进sort buffer排序完成后根据主键回表读取完整行数据总长度小于阈值时采用单路排序把所有需要的列一起放进sort buffer排完直接输出省掉回表。这套机制在8.0.20之后被移除了MySQL统一了排序策略不再让用户在单路和双路之间纠结。但了解它仍然有用因为很多生产环境还在运行5.7而且“宽行导致排序内存膨胀”这件事本身没有消失。我见过一个真实的例子一张表有十几个varchar字段开发图省事写了SELECT * FROM xxx ORDER BY score LIMIT 20单行数据1000多字节5.7判断超过max_length_for_sort_data后走双路排序排序键加上主键只占很小内存看起来好像没问题但双路排序多了一次全表回表加上WHERE条件本身过滤性不强回表开销直接把查询拖垮。后来改成只查需要的列同一SQL耗时从3秒降到200毫秒。3.3 临时表为什么比filesort更可怕临时表和filesort经常一起出现但临时表的代价通常更高。什么时候会产生临时表我把高频场景列一下GROUP BY8.0之前还自带隐式排序本来只想去重统计结果多了一次排序DISTINCT结果去重需要先放临时表UNION默认会去重除非用UNION ALLORDER BY的字段不在SELECT的GROUP BY列里且无法用索引解决排序或查询涉及TEXT/BLOB类型字段MySQL需要为它们建临时表存储。MySQL的临时表分内存临时表和磁盘临时表。内存装不下时会自动落盘而一旦落盘读写就是磁盘I/O和sort buffer溢写一样致命。我处理过一个报表SQLSELECT client_id, COUNT(*) FROM logs GROUP BY client_id ORDER BY cnt DESClogs表有text字段参与输出结果MySQL必须建临时表临时表超过max_heap_table_size后落盘整个查询跑了30多秒。改成先按client_id聚合应用层再做排序或者去掉text字段的排序性能问题立刻没了。记住一个判断原则explain的Extra里如果出现Using temporary先考虑能不能用索引替代临时表如果不行就考虑让临时表留在内存里。和filesort一样临时表最怕的就是落盘。4. 从explain到performance_schema定位排序瓶颈的完整链路4.1 explain结果里那些排序暗号explain是定位排序问题的第一工具重点看type、key、Extra三列。先说Extra出现Using filesort意味着本次查询有额外排序动作出现Using temporary意味着用了临时表两者同时出现属于“排序临时表”最坏组合。如果Extra里有Using index且没有Using filesort说明覆盖索引让排序和取数都在索引里完成这是最理想的状态。type列决定访问路径。常见值从好到差依次是const、ref、range、index、ALL。index在这里有迷惑性它扫的是整个索引但未必是高效的因为可能是索引全扫描而非定位后的扫描。比如ORDER BY created_at LIMIT 100如果只有单个created_at索引execution type可能是indexExtra没有filesort看起来很美但它是把整个索引从头到尾扫一遍再取100条代价随表大小线性增长。一条经常看的经验排序优化的成就感不是“消除了Using filesort”而是“让查询需要的行数和扫描路径同时变小”。我曾见过同事加了一堆索引把所有慢查询都改成Using index结果总索引空间比表还大写放大严重。排序优化要在性能和成本之间找平衡。4.2 用状态变量和performance_schema量化排序开销explain只给方向要量化还得靠状态变量。最常用的三条Sort_rows执行排序的行数反映排序规模Sort_merge_passes磁盘溢写和归并次数持续增长说明sort buffer不够Sort_range / Sort_scan说明排序动作的触发方式。SHOW SESSION STATUS LIKE Sort_%;如果需要更细的耗时分布可以查performance_schemaSELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT / 1000000000 AS total_ms FROM performance_schema.events_stages_summary_global_by_event_name WHERE EVENT_NAME LIKE %sort%;8.0.18以后还有一颗银子弹EXPLAIN ANALYZE。它不光是预测执行计划而是真实执行SQL并输出每个节点的实际耗时。比如EXPLAIN ANALYZE SELECT * FROM trade WHERE seller_id 3281 AND status 2 ORDER BY created_at DESC LIMIT 20;输出里会明确显示Sort节点的actual time是内存排序还是磁盘排序一眼就能看出来。这是我最近强烈推荐的习惯先explain看方案再EXPLAIN ANALYZE看实测。4.3 一个完整案例从4.7秒到80ms的排查过程回到开头的那个客服后台慢SQL。我的排查顺序是这样的第一步explain查看执行计划。发现typeref命中了seller_id的单列索引但Extra有Using filesort。这说明过滤部分没问题排序无法利用索引。第二步查Sort_merge_passes。SHOW SESSION STATUS LIKE Sort_merge_passes显示为几十次说明排序规模远超sort buffer已经发生了磁盘溢写。为什么因为单列索引只完成了seller_id的过滤created_at排序需要把满足条件的可能几十万行全部读出来排序而客服后台是按天倒序查数据量很容易积累到几十万行。第三步修改索引结构。把单列索引替换为联合索引(seller_id, status, created_at)让排序字段直接吃索引尾巴。改完explainExtra里的Using filesort消失type变为ref扫描行数从几十万降到几千。第四步EXPLAIN ANALYZE确认。Sort节点的实际耗时从秒级降到个位数毫秒整条SQL从4.7秒降到80ms。这里最关键的一步是第三步但最容易被忽略的是第二步。如果排序字段没有吃上索引不能只看explain的Using filesort就去建索引先算清楚“要排序的数据量有多大、是否溢写磁盘”这样才能判断到底该优化访问路径还是优化排序本身。5. 深翻页、覆盖索引与业务模型改造三类排序优化实战5.1 深分页LIMIT 1000000, 20 为什么慢到离谱列表页做分页最容易被攻击的写法是这样的SELECT * FROM trade WHERE seller_id 3281 AND status 2 ORDER BY created_at DESC LIMIT 1000000, 20;逻辑上它只要第1000001到第1000020条但MySQL无法像数组那样直接跳到第100万个偏移量。就算order by走了索引它也必须从索引的最开始或范围起点一直数到第100万个位置扫描1000020行丢弃前面100万行。如果order by还没走索引、走的是filesort那更惨它得把满足条件的所有行都排序完再丢弃前100万行。B树索引的叶子节点是链表结构天然不支持按位置随机偏移这正是深分页的根源。针对这种场景工程上有两种主流优化延迟关联和keyset游标。延迟关联的核心思路是先走覆盖索引拿到本页需要的20个主键再回表取完整行把“扫描100万行”变成“扫描索引100万行但只回表20行”SELECT tr.* FROM trade tr INNER JOIN ( SELECT id FROM trade WHERE seller_id 3281 AND status 2 ORDER BY created_at DESC, id DESC LIMIT 1000000, 20 ) tmp ON tr.id tmp.id ORDER BY tr.created_at DESC, tr.id DESC;子查询里排序和分页需要的字段都在索引(seller_id, status, created_at, id)上是索引扫描不会触发filesort外层只对20行做一次轻量排序再回表。实测三五百万的深分页这种写法通常能把几十秒的查询压到几百毫秒。如果是“下一页”按钮式的产品而不是“跳页”可以彻底绕过偏移量改用keyset游标把上一页最后一条记录的排序键记下来下一页直接从这个位置往后找SELECT * FROM trade WHERE seller_id 3281 AND status 2 AND (created_at 2024-06-01 10:00:00 OR (created_at 2024-06-01 10:00:00 AND id 100123)) ORDER BY created_at DESC, id DESC LIMIT 20;这种方案无论翻多少页扫描量都不变是深分页的最佳解法。前提是你愿意和产品协商把“跳页”改成“流式加载”。5.2 覆盖索引让过滤、排序、取数一次完成排序优化的另一个高效手段是覆盖索引。它的原理很简单如果查询要返回的列全都包含在索引里MySQL就不需要回表直接扫索引树就能把数据查完配合排序字段也在索引中整条查询可以在索引这一棵树内自洽完成。还是订单表举例列表页高频查询是SELECT id, order_no, amount, created_at FROM trade WHERE seller_id 3281 AND status 2 ORDER BY created_at DESC LIMIT 20;如果只建(seller_id, status, created_at)联合索引过滤和排序都解决了但order_no、amount还需要回表。把这几个高频显示的列追加进索引ALTER TABLE trade ADD KEY idx_cover (seller_id, status, created_at, order_no, amount);这时explain里不仅没有Using filesort还会出现Using index回表完全消失。不过我要泼一盆冷水覆盖索引是把双刃剑。索引列越多写入和更新时维护索引的代价越大存储空间也越大。我的经验是只对“高频且列数可控”的查询做覆盖设计别为了覆盖把所有列都塞进索引。索引不是越宽越好是越“贴合高频查询”越好。另外注意索引内字段放置的顺序先等值条件列再排序列最后才是要覆盖的返回列。范围条件列一旦放进去它后面的排序列就废了这一点和前面2.2节讲的范围截断完全一致。5.3 别让业务给数据库出难题ORDER BY RAND()之类的改造有些SQL慢根源不在数据库而在业务提了一个数据库不擅长解决的问题。最经典的就是ORDER BY RAND() LIMIT 1。它的语义是“在整张表里随机取一行”MySQL的实现方式是对全表做一个filesort而且因为排序键是随机函数生成的值无法用任何索引数据量大时必定磁盘溢写。我在一个300万行的活动表上测试过随机抽一条的SQL跑了2.8秒流量稍微上来实例就报警。如果业务只是想“大致随机取一条”完全可以绕开全表排序-- 先取主键的范围 SELECT MIN(id), MAX(id) INTO min_id, max_id FROM trade; -- 在范围内随机一个值再定位到最近的一行 SET target FLOOR(RAND() * (max_id - min_id 1)) min_id; SELECT * FROM trade WHERE id target ORDER BY id LIMIT 1;这个方案只扫一条记录缺点是主键有空洞时随机分布不均匀且抽到的总是“距随机值最近的记录”不是字面意义上的等概率。但对绝大多数营销抽奖、首页推荐场景这个误差完全可以接受。真要更均匀的随机可以在应用层维护一张密集的自增id表先随机取一个连续id再回表代价只是多了张几百行的小表。我真正想说的是DBA能优化的不止是索引。把“不分页的列表”“必须实时的排行榜”“要在千万级表上做随机抽样”这类需求挡在数据库外面本身也是性能优化的一部分。很多时候我和业务方聊十分钟把“必须实时”改成“缓存5分钟”比任何索引调优都有效。6. 参数取舍与schema设计层面的全局建议6.1 sort_buffer_size到底该不该调大先说结论没有统一的“最佳值”只有配合监控的合理配置。sort_buffer_size是会话级内存每个连接都会分配一块独立的排序缓冲区。默认值通常为256KB不少DBA一见到排序慢就把它调到8MB甚至64MB结果高并发下内存瞬间爆炸——1000个活跃连接每个8MB就是8GB再厚的机器也扛不住。我的调优流程是以下三步先看Sort_merge_passes是否持续增长。如果长期为0说明当前缓冲区足够调大纯属浪费内存如果持续大于0先尝试优化SQL和索引把排序规模降下来确认索引无法再压减排序数据量后才去调整会话级的sort_buffer_size从1MB起步观察Sort_merge_passes有没有显著下降。修改方式有两种-- 全局生效只影响新连接 SET GLOBAL sort_buffer_size 1024 * 1024; -- 会话生效只影响当前连接 SET SESSION sort_buffer_size 1024 * 1024;生产环境我更推荐改会话级别或者只针对特定慢查询的账号统一设置别全局开大。MySQL文档里对sort_buffer_size有一个明确的警告增大这个参数要非常谨慎因为它可能让你误以为解决了问题实际上只是把磁盘I/O换成了内存占用而内存是不可压缩资源。6.2 字符集排序规则一个容易被忽略的比较成本排序的本质是比较。数字和日期比较快如闪电字符串比较却要逐字符按排序规则计算。业务表如果默认是utf8mb4拿一个varchar(64)的流水号做排序每次比较都要走utf8mb4的字符映射开销自然不小。8.0默认的utf8mb4_0900_ai_ci比5.7时代的utf8mb4_general_ci更规范但某些场景下计算成本并不低。实战中的建议很朴素能用数字、日期做排序键就不要用字符串字符串排序尽量选短字段超长字段可以先冗余一个hash列用于排序如果确实是字符串且业务上不区分大小写可以用binary排序规则或考虑改成latin1/ascii类列避免对TEXT/BLOB做排序这类字段参与排序基本都会触发临时表。我见过一个案例订单表用varchar(32)存“订单时间戳”开发图省事用它做排序和过滤结果排序慢。后来把数据迁移成了datetime类型列只改了类型同样SQL秒变快就是因为避免了字符串比较的额外开销。别小看这一层排序十万行字符串的CPU消耗是日期的数倍。6.3 新表设计时提前做好的三件事等到线上慢查询日志堆起来再优化永远是被动的。新表设计阶段提前做好三件事能省掉后面大半的排序调优第一明确每个高频查询的排序方向索引的方向跟着排序走。排序是倒序就建倒序索引8.0支持降序索引5.7尽量用覆盖索引兜底。第二把“过滤等值列、排序列、范围列、需要覆盖的返回列”排好序去建联合索引。等值条件列放在最前面排序列紧跟其后范围条件列要非常谨慎地放因为它会截断后面的排序能力。第三从产品层面约定分页模型。大表一律不接受跳页式深分页用keyset游标列表页固定排序键不允许前端动态换排序字段。如果业务必须支持任意列排序那就把数据放到分析型存储里别为难OLTP数据库。我自己的项目规范里还有一条任何慢查询从发现到优化完成必须闭环记录“优化前explain - 优化后explain - 耗时对比”三段信息。没有这三段下次遇到同类问题你还是得从零开始猜。排序优化这件事我做下来最深的体会是真正的收益往往不在最后一公里的索引调整而在你把“为什么要排序、能不能不排序、能不能少排点”这些前置问题想清楚的时候。每次排查慢排序我都先问自己这三个问题把框架立住再去动执行计划和索引基本不会走偏。
