写 ORDER BY 多字段排序这篇文章算是我在后台被问得最多的问题之一了。很多人上来就写ORDER BY a, b, c以为这样就完事了结果线上慢查询一抓一大把或者数据排序结果根本不符合业务预期。尤其是涉及多个字段时排列顺序、升降序混搭、NULL 值位置、字符集规则、索引命中与否每个点都能榨出不少细节。今天就结合我自己的实战经验把 MySQL 中多字段排序这块彻底说透。这篇内容适合刚接触 SQL 的入门选手也适合那些写了好几年 SQL 但没仔细琢磨过排序原理的开发者。我会从最基础的语法讲起逐步深入到索引优化、深分页排序、中文字段排序这些硬核话题最后附上排查技巧和面试题思路。不看吃亏。1. 多字段排序基础语法与执行逻辑1.1 基础语法到底怎么写才是对的ORDER BY多字段排序的语法本身极其简单就是在ORDER BY后面跟多个列用逗号分隔即可SELECT * FROM employees ORDER BY department_id, salary DESC;上面这条 SQL 的含义是先按department_id升序排列默认 ASC当department_id相同时再按salary降序排列。注意这里的顺序很关键。ORDER BY department_id, salary DESC表示salary DESC只作用于salary这一个字段而department_id仍然是默认升序。如果你写成ORDER BY department_id DESC, salary DESC那就是两个字段都按降序排列。但真要理解多字段排序不能只停留在语法记忆上。你要把这个过程理解成逐级比较先比第一个字段能分出高下就直接决定先后顺序如果第一个字段完全相等才看第二个字段如果第二个字段还相等就继续往后看。这个过程类似字典排序先比首字母再比第二个字母。用电商订单表举个例子。假设你要查某用户的历史订单期望先按订单状态排序未支付、已支付、已完成再按下单时间从新到旧排。这时候 SQL 应该写成SELECT order_id, status, create_time FROM orders WHERE user_id 1024 ORDER BY CASE status WHEN 0 THEN 1 -- 未支付 WHEN 1 THEN 2 -- 已支付 WHEN 2 THEN 3 -- 已完成 END, create_time DESC;这里第一优先级是状态的自定义顺序第二优先级才是时间倒序。先状态后时间而不是先时间后状态这个业务优先级的思考方式才是多字段排序真正考验人的地方。1.2 排序背后的执行逻辑MySQL 是怎么排的很多人只知道写 SQL不知道 MySQL 执行排序时内部发生了什么。实际上 MySQL 执行带有ORDER BY的查询大体会走两条路利用索引直接返回有序结果Using index或者把数据读取出来后在内存或磁盘上做排序操作Using filesort。多字段排序跟单字段排序最大的区别在于多字段排序对索引的依赖条件更苛刻。执行计划里出现Using filesort并不一定代表性能差但如果数据量大或者sort_buffer_size设置不合理filesort 会把临时数据写到磁盘上性能就会急剧下降。判断 SQL 是否走索引排序最简单的办法是用EXPLAIN查看执行计划EXPLAIN SELECT order_id, user_id, create_time FROM orders ORDER BY user_id, create_time;如果Extra列显示Using index或没有Using filesort说明排序可以直接利用索引顺序扫描完成如果显示Using filesort说明 MySQL 需要额外的排序步骤。这里要特别说明一个底层逻辑B树索引本身是有序存储的。当你建立了一个联合索引(user_id, create_time)索引叶子节点就先按user_id排序user_id相同的再按create_time排序。所以只要查询的排序顺序和索引的列顺序完全一致MySQL 顺着索引扫一遍就能拿到有序结果完全不需要额外排序。这就是多字段排序最理想的状态。但要注意索引列顺序是固定的数据的扫描方式通常也是正向的。如果你要求ORDER BY user_id ASC, create_time DESC这里一升一降混搭大部分情况下索引就无法直接支持了。这个问题在后面性能优化部分我会展开讲。1.3 多字段排序中 NULL 值的位置怎么控制NULL 值排序是所有写 SQL 的人都会踩的坑。MySQL 的默认行为是ASC升序时 NULL 排在前面DESC降序时 NULL 排在后面。这跟 Oracle 正好相反Oracle 默认是升序 NULL 在最后降序 NULL 在最前。看个实际例子。有一张用户表usersage字段允许为空现在想按年龄从大到小排SELECT name, age FROM users ORDER BY age DESC;你以为结果中年龄最大的在最前面其实所有age为 NULL 的记录跑到了最后面这通常符合业务预期。但如果你用的是升序SELECT name, age FROM users ORDER BY age ASC;结果就是一堆 NULL 的记录先出现在最前面有年龄值的反而在后面很多业务场景下这个结果是反直觉的。MySQL 8.0 参考了 SQL 标准加上了NULLS FIRST和NULLS LAST语法可以直接控制 NULL 的显示位置-- NULL 永远放在最后 SELECT name, age FROM users ORDER BY age ASC NULLS LAST; -- NULL 永远放在最前 SELECT name, age FROM users ORDER BY age DESC NULLS FIRST;如果你还在用 MySQL 5.7 或更早版本没法用这个语法可以用IS NULL表达式来变通SELECT name, age FROM users ORDER BY (age IS NULL), age ASC;这个技巧的原理是age IS NULL这个表达式的结果对非 NULL 为 0、对 NULL 为 1MySQL 先按这个表达式排序0 自然排在 1 前面因此非 NULL 记录在前NULL 记录统一被推到最后。反过来想控制 NULL 在前只需要ORDER BY (age IS NOT NULL), age ASC。多字段排序时 NULL 控制只会干扰当前字段。比如ORDER BY department_id, age DESC NULLS LAST这里NULLS LAST只作用于age不会影响department_id的排序方式。这一点在分页接口里尤其重要因为同一页数据如果 NULL 位置不固定翻页就会出现数据重复或丢失。2. 多字段排序的高阶玩法表达式、函数与自定义排序2.1 使用函数或表达式排序实际业务中直接对字段排序往往不够用你需要对字段做一点加工再排序。最常见的场景是数值型字段被存成了字符串或者日期字段被存成了 varchar。举个例子订单表里的订单号字段order_no是 varchar 类型存储的值是1001、999、1002这种。你直接SELECT order_no FROM orders ORDER BY order_no ASC;结果会得到1001、1002、999因为字符串排序是按字典序一位一位比的9 的 ASCII 码比 1 大所以 999 排在了最后。想让它们按数值大小排需要显式转换SELECT order_no FROM orders ORDER BY CAST(order_no AS UNSIGNED) ASC;更常见的是对日期字符串排序。比如一个字段存的是2024-01-05、2023-12-18这种字符串格式本身可以按字典序排因为日期是定长的并且从大到小排列刚好对应时间顺序。但如果存的是2024-1-5这种格式字典序就会出错。这时候需要统一处理SELECT task_id, task_date FROM tasks ORDER BY STR_TO_DATE(task_date, %Y-%m-%d) ASC;使用函数排序要提前想清楚一旦在ORDER BY子句中对字段使用函数这个字段上的索引基本就废了。因为索引中存储的是原始值MySQL 没法拿函数转换后的结果去匹配索引顺序只能对每一行都计算一遍函数再排序这个代价在大表上是灾难性的。如果某个函数排序查询特别频繁有两条路可以走一是建一个冗余字段写入时就直接存好排序用的值二是用 MySQL 8.0 的函数索引即表达式索引直接支持这种场景。2.2 CASE WHEN 实现自定义优先级排序多字段排序不仅是多个字段的组合也可以是同一个字段按业务规则自定义顺序。比如一张任务表任务状态有0待处理、1进行中、2已完成、3已取消但业务上希望排序结果是进行中 待处理 已完成 已取消而不是简单的数字升序或降序。这时候就得用CASE WHEN把状态映射成排序键SELECT id, title, status FROM tasks ORDER BY CASE status WHEN 1 THEN 1 WHEN 0 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 4 ELSE 5 END, create_time DESC;这个写法本质上是构建了一个不可见的新字段然后用这个新字段排序。它跟你直接写ORDER BY status, create_time DESC最大的区别在于status的原始数字编码和业务想要的排序规则可以完全解耦。有同学可能会问能不能用FIELD()函数更简单地实现当然可以ORDER BY FIELD(status, 1, 0, 2, 3), create_time DESC;FIELD(status, 1, 0, 2, 3)的效果是status 值为 1 时返回 1值为 0 时返回 2值为 2 时返回 3值为 3 时返回 4如果不在列表里则返回 0会被排在最前面。这个函数用起来更简洁但它本质上也是函数运算同样会限制索引的使用。数据量小无所谓数据量大了就要考虑别的方式。自定义排序在多字段场景下的真正威力是多级自定义。比如任务列表第一优先级是业务状态第二优先级是任务级别高、中、低第三优先级才是创建时间。这种需求可以写成ORDER BY CASE status WHEN 1 THEN 1 WHEN 0 THEN 2 WHEN 2 THEN 3 ELSE 4 END, CASE priority WHEN high THEN 1 WHEN medium THEN 2 ELSE 3 END, create_time DESC;清晰直观业务规则一眼就能看懂。但这种写法是 filesort 的重灾区因为它把排序键变成了表达式MySQL 必须在排序阶段为每行数据临时计算这几个映射值。建议这种 SQL 只用于后台管理列表等低并发场景高并发的用户端接口尽量避免。2.3 中文排序与字符集暗坑中文字段排序是个容易翻车的地方。MySQL 的排序规则Collation决定了字符串比较的方式。常见的 utf8mb4 字符集下排序规则有好几种utf8mb4_general_ci不区分大小写按二进制字节比较对中文来说基本是按 Unicode 编码顺序排utf8mb4_unicode_ci基于 Unicode 排序算法比较准确但排序规则更复杂utf8mb4_0900_ai_ciMySQL 8.0 默认规则基于 UCA 9.0.0对中文排序支持更好如果你直接用中文列排序SELECT name FROM students ORDER BY name ASC;大概率得到的顺序是按拼音首字母排——如果表用的字符集是 gbk 且排序规则是gbk_chinese_ciMySQL 会直接按拼音排序但如果是 utf8mb4很多版本下会按 Unicode 码点排这个顺序既不是拼音序也不是笔画序看起来像是随机顺序。实测下来MySQL 8.0 的 utf8mb4 默认排序规则对中文的处理明显比 5.7 合理但依然不保证符合 新华字典拼音序。如果你有严格的拼音排序需求最稳妥的方案是在前端完成排序或者给表增加一个专门的拼音字段或者拼音首字母字段写入时生成好排序直接用这个字段。多字段排序里有个隐蔽的坑字段 A 和字段 B 的字符集或排序规则不一致连表查询时 MySQL 会做隐式转换导致索引失效。比如一张表的name是 utf8mb4_general_ci另一张表的name是 utf8mb4_unicode_ci排序时 MySQL 会把其中一边转换成另一边再做比较这种隐式转换不仅让索引失效还可能在SHOW WARNINGS里看到警告。排查方法就是用EXPLAIN看执行计划如果发现Using filesort出现且 SQL 本身涉及多个表的字段排序优先检查两边的排序规则是否一致。统一字符集排序规则是这种问题最干脆的解法。3. 排序性能优化让 filesort 从执行计划里消失3.1 filesort 的代价远比你想象的大Using filesort这个词经常把新手唬住实际上它并不代表在磁盘上排序而是表示MySQL 需要额外执行一次排序操作。这个排序过程可能在内存中的排序缓冲区sort buffer完成也可能因为缓冲区不够大而使用磁盘临时文件完成。后者才是真正要命的地方。MySQL 执行 filesort 时大致流程是先根据WHERE条件把需要排序的行找出来然后从这些行中取出排序字段和查询需要的字段放到 sort buffer 里排序最后根据排序结果回表查询完整数据。如果 sort buffer 装不下就把中间结果分块写到磁盘临时文件再对多个文件做归并排序。整个过程有三个明显的性能隐患一是需要排序的数据量很大时MySQL 要做很多次磁盘 I/O二是排序之后往往还要回表查一次完整记录回表本身又是一批随机 I/O三是在 innodb 缓冲池里这些排序场景很容易把热数据挤出去影响整体性能。实际业务里一条耗时 2 秒的慢查询可能排序本身只占了几百毫秒但回表查询几万行数据产生的随机 I/O 会拖慢整个查询。这也是为什么很多大厂 DBA 会明确要求核心业务 SQL 不允许出现Using filesort尤其是对数据量大的表。3.2 联合索引是最有效的优化手段多字段排序性能优化的第一选择永远是让排序直接使用索引顺序也就是把执行计划里的Using filesort干掉。前面说过B树的索引结构天然有序如果排序字段的顺序和索引列的顺序完全匹配MySQL 直接按索引扫描返回结果就行完全不用排序。举个例子你有一个联合索引(class_id, score)那么下面这条 SQL 就能直接利用索引SELECT student_id, class_id, score FROM student_scores WHERE class_id 1001 ORDER BY score DESC;这里利用了联合索引的最左前缀原则先通过class_id 1001定位到该班级的所有索引记录这些记录在索引中已经按(class_id, score)排序存储而同一个class_id下的score天然有序因此ORDER BY score可以直接按索引倒序读取不需要额外排序。再看多字段排序的典型场景SELECT * FROM orders WHERE user_id 100 ORDER BY user_id, create_time DESC;如果这里建了索引(user_id, create_time)排序顺序是user_id ASC, create_time DESC。升序加降序的混搭MySQL 8.0 之前只能做 filesort但 MySQL 8.0 引入降序索引后可以显式指定ALTER TABLE orders ADD INDEX idx_user_create (user_id ASC, create_time DESC);有了这个索引ORDER BY user_id ASC, create_time DESC就能完美利用索引了。不过这里有两条铁律要记住。第一排序字段的顺序必须和联合索引的列顺序一致。索引是(a, b, c)你排序写成ORDER BY b, a索引无能为力。第二要么排序字段全部出现在WHERE等值条件里像上面的user_id 100要么排序字段和WHERE条件没有冲突否则也可能用不上。还有一个很多人容易忽略的细节查询列如果都在索引里执行计划会显示Using index覆盖索引这时排序的效率最高。如果你写SELECT *索引即使涵盖了排序字段MySQL 还得回表取其他列性能就会打折。所以针对高性能排序查询可以适当把查询列收窄让索引覆盖更多查询需求。3.3 排序缓冲区参数与 SQL 写法避坑当Using filesort无法避免时比如排序字段带函数、混合排序方向我们能做的是让 filesort 尽量在内存中完成别落到磁盘上。sort_buffer_size是专门控制排序缓冲区大小的参数。理解这个参数要特别注意它是每个会话连接独立的并不是全局共享的。如果你把它调得特别大同时上来几千个连接内存开销可能直接爆掉。一般生产环境设置在 2MB 到 8MB 之间比较常见。大量排序的会话可以通过SET SESSION sort_buffer_size 8 * 1024 * 1024;临时增大而不是全局修改。MySQL 的 filesort 有双路排序和单路排序两种模式它们的分界线是max_length_for_sort_data参数。如果排序行的总长度超过这个值MySQL 会退化为双路排序先排序主键和排序字段再回表取完整记录性能较差。适当调大max_length_for_sort_data可以让更多查询走单路排序但这个参数也不是越大越好。字段特别长、数据量特别大的情况下单路排序会消耗更多内存反而增加磁盘排序概率需要根据实际数据观测调整。相比调参数从 SQL 写法上规避 filesort 更可靠我这里列几个实战体会避免在排序字段上使用函数、表达式比如ORDER BY YEAR(create_time)就是典型的索引杀手SELECT只取需要的列不要无脑SELECT *这样既能减少回表也能降低排序行的大小提高单路排序成功率如果查询同时有WHERE和ORDER BY优先保证WHERE条件的索引其次再考虑ORDER BY能不能搭上索引的顺风车两者冲突时通常优先WHERE多表关联时参与排序的字段最好来自同一个表否则 MySQL 得先做 join 再排序优化空间很小4. 常见问题与排查技巧实录4.1 混合 ASC 和 DESC 导致排序索引失效我遇到过一个典型的线上问题一个订单流水表的查询SQL 长这样SELECT order_id, user_id, amount, create_time FROM order_flow WHERE user_id 12345 ORDER BY create_time ASC, id DESC;表上明明建了idx_user_create (user_id, create_time)联合索引但EXPLAIN显示 Extra 列还是Using filesort。一开始大家以为是 MySQL 优化器版本问题后来仔细一查发现反向排序的锅。MySQL 8.0 之前索引都是默认升序存储的所以ORDER BY create_time ASC可以直接走索引顺序扫描但id DESC这一下就让整个排序方向对不上了索引立刻失效。解决办法有两个一个是对id建立降序索引8.0 支持另一个是调整 SQL 让排序方向和索引方向一致。在这个例子里如果业务只关心时间倒序下的最新记录可以虚拟一个自增 ID 作为第二排序键并把它改成ASC试试。实际操作中我往往建议把业务方案改成ORDER BY create_time DESC, id DESC这两个字段都是降序排序方向和索引一致几乎不损失业务语义。排查这类问题的通用思路拿到慢 SQL 先跑EXPLAIN看key列和Extra列如果key有值但Extra仍然有Using filesort很大概率就是排序方向或排序字段顺序和索引不匹配。然后把ORDER BY里的字段方向和索引建得方向对齐问题基本能解。4.2 深分页排序性能骤降翻页是排序最常见的落地场景。LIMIT 100000, 20这种深分页在数据量大时性能会断崖式下跌。原因很直接MySQL 必须先把前 100000 行数据全部查出来排好序再丢掉前面的 100000 行只返回最后的 20 行。前面那些排了序却扔掉的数据全是白干。多字段排序下的深分页更惨因为排序键往往是多个字段想跳过前面的数据更难。优化手段业界有几种常见方案。第一种是延迟关联先用子查询或连接只取主键拿到分页范围内的主键后再回表查完整数据SELECT t.* FROM orders t INNER JOIN ( SELECT id FROM orders ORDER BY user_id, create_time DESC LIMIT 100000, 20 ) tmp ON t.id tmp.id ORDER BY t.user_id, t.create_time DESC;如果orders表有(user_id, create_time, id)的联合索引子查询可以走覆盖索引排序不需要回表速度比直接SELECT *加快一个量级。第二种是书签法或键集分页。不依赖LIMIT的偏移量而是记住上一页最后一条数据的排序键值下一页查询时带上这个条件SELECT id, user_id, create_time FROM orders WHERE user_id 100 AND (create_time, id) (2024-06-01 10:00:00, 100234) ORDER BY create_time DESC, id DESC LIMIT 20;这个写法的核心是让数据库从某个位置继续往后扫不需要把前面所有数据都排一遍。性能稳定翻页越深优势越明显。缺点是接口需要额外传递上一页最后一条的排序键值且排序键必须唯一通常加主键 id 来保证否则可能漏数据或重复。4.3 排序结果不稳定数据在页与页之间跳动另一个高频问题明明排序规则没变翻页时却出现了重复数据或漏数据。这个现象十有八九是排序键不唯一导致的。举个例子你按ORDER BY create_time DESC翻页create_time精确到秒同一秒内可能有几百条数据。这些数据之间的相对顺序 MySQL 不保证是稳定的。当你LIMIT 0, 20取完第一页后LIMIT 20, 20取第二页时因为数据库的数据分布可能因为其他连接插入、删除或者缓冲池淘汰而变化第一页和第二页之间就可能出现重复或缺失。解决办法很简单在排序字段最后追加一个唯一字段通常就是主键 idORDER BY create_time DESC, id DESC这样每一行的顺序都是完全确定的翻页结果就稳定了。这个经验在我做订单列表、消息列表时屡试不爽凡是分页接口排序里强制加 id 兜底基本不会出问题。补充一个和 NULL 相关的细节如果排序字段允许 NULL用书签法分页时条件要注意 NULL 值的处理。(create_time, id) (2024-06-01, 100)这种条件对create_time为 NULL 的行处理逻辑不一定符合预期建议排序字段设计时尽量避免 NULL或者统一用默认值代替比如 1970-01-01。4.4 我踩过的那些排序坑最后聊几个实战中积累的经验这些内容在文档里很少看到但实际排查问题非常有用。第一个是排序字段类型不一致导致的隐式转换。比如一个字段设计时用了 varchar 存数字排序的时候虽然能排出来但如果你用ORDER BY num_column ASCMySQL 会按字典序排而不是数值序。更隐蔽的是两个表的关联字段一个是 varchar 一个是 int连表排序时 MySQL 会自动把 varchar 转成 int导致索引失效执行计划看起来莫名其妙。建议所有表的关联字段和排序字段在设计阶段统一类型和排序规则。第二个是ORDER BY加LIMIT时的优化器行为。MySQL 有个内部逻辑如果排序开销太大它可能先用索引扫描快速拿到LIMIT条数据再对这部分数据排序从语义上可能产生和全量排序不一致的结果。这是优化器基于代价的取舍不是 bug。遇到这种问题可以在排序字段上加合适的索引引导优化器走你期望的执行路径。第三个是用覆盖索引控制排序结果。我执行SELECT *做多字段排序时即使走了索引排序回表也是一个不小的开销。如果查询频繁我会尝试把 SQL 改成SELECT id, user_id, create_time然后建立对应的覆盖索引(user_id, create_time, id)执行计划会变成Using index整体性能提升非常明显。第四个是关于配置参数的经验。以前我发现某个支付对账系统每天凌晨跑批量任务时数据库 CPU 飙升查慢日志发现全是带ORDER BY的大查询。后来查了SHOW GLOBAL STATUS LIKE Sort_merge_passes发现这个值非常高说明 filesort 频繁使用磁盘归并排序内存缓冲区严重不足。临时调大sort_buffer_size之后Sort_merge_passes明显下降但这是治标不治本。真正的解是重写 SQL 或者添加联合索引让排序不再依赖 filesort。参数能救一时救不了一世。第五个是在联表排序时优先确定驱动表。ORDER BY多字段排序最容易忽略的一个点是参与排序的字段来自哪个表。如果排序字段来自被驱动表MySQL 往往需要先完成 join再对结果集排序这是一种代价非常大的执行路径。如果业务允许尽量让排序字段来自驱动表或者把排序下推到子查询里减少参与排序的数据量。排序这个功能表面上看人人都懂但要在设计、性能、稳定性之间做好平衡还是得踩过坑才能真正掌握。尤其是多字段排序索引设计几乎决定了 SQL 的天花板。写 SQL 之前先想清楚排序的优先级是什么升降序方向是什么NULL 放在哪里能不能用索引覆盖。把这些问题想明白了再动手写性能问题能避免一大半。