你有没有遇到过那种“排序结果看起来不太对，但又说不出哪里不对”的情况？比如列表页按创建时间倒序，翻到第二页突然出现一条第一页已经见过的记录；再比如查询会员昵称，中英文混排后，顺序怎么排都觉得“没逻辑”。大多数时候问题并不在业务代码，而在 MySQL 的 ORDER BY 排序规则上。这个标题看着简单，背后牵扯到字段比较规则、字符集 Collation、索引能否参与排序、NULL 值位置、甚至翻页稳定性，任何一个环节没对齐，结果都会偏离预期。这篇文章我打算把这些规则完整地拆一遍，包括它怎么生效、什么时候失效、以及我在实际业务里踩过的对应坑，适合正在排查排序异常或准备做分页列表优化的同学。
1. 先搞清楚 ORDER BY 的基础排序规则：多字段、方向与执行的先后顺序
1.1 多字段排序与 ASC/DESC 的混合使用
ORDER BY 最容易被忽略的规则就是“按顺序逐级比较”。写ORDER BY status ASC, create_time DESC时，MySQL 会先按status排，只有status完全相同的行，才会再按create_time排。我见过有人以为这两个字段会“各自独立排序”，然后查出结果说数据是乱的，其实是没理解多字段排序的层级比较逻辑。
举个例子，业务希望待处理订单在最上面，同一状态里最新的订单在前面，那么正确的写法是：
SELECT order_no, status, create_time FROM order_info WHERE deleted = 0 ORDER BY CASE status WHEN 'pending' THEN 0 WHEN 'processing' THEN 1 ELSE 2 END ASC, create_time DESC;这里用CASE把状态转换成可排序的数字，再用create_time DESC做第二关键字。这种混用 ASC 和 DESC 是完全允许的，MySQL 会按照字段出现的先后顺序对每个字段分别决定方向。需要注意的一点是，如果第一排序字段区分度很低，比如status只有两三种值，那么第二排序字段几乎决定了页面的实际顺序。此时第二字段的选择就不是“锦上添花”，而是刚需。
另外一个常见误区是在多字段排序时不写ASC。默认是升序没错，但多字段场景里，你很难一眼看出每个字段到底是按什么方向排序的。我建议每个字段都显式写出方向，既方便别人 Review，也能避免将来有人少改一个字段顺序，造成前后端排序标准不一致。
1.2 表达式排序与别名陷阱
ORDER BY 不止能排字段，还能排表达式。常见的有按字符串内容的数值排序，比如价格列虽然定义成了VARCHAR，但希望按数值大小排，可以写ORDER BY price + 0，或者ORDER BY CAST(price AS UNSIGNED)。这种表达式排序业务里非常常见，但代价是索引大概率用不上了，所以如果查询频繁，最好把字段类型改成合适的数值类型，而不是每次查询都做隐式转换。
另一个很隐蔽的坑是别名。ORDER BY 子句可以直接使用 SELECT 中定义的别名，比如SELECT nickname AS nick FROM user ORDER BY nick是合法的。但 WHERE 子句不能用别名，因为 SQL 的逻辑执行顺序是先FROM，再WHERE、GROUP BY、HAVING，然后才到SELECT阶段生成别名，最后才轮到ORDER BY。很多人在这里栽过跟头：在 WHERE 里写WHERE nick = '张三'直接报错，又说不清为什么。
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT，这个顺序建议记牢。理解了它，就会明白为什么 ORDER BY 里能引用别名，也明白为什么优化器有时候会把 ORDER BY 的排序动作提前或者在临时表上执行。这些都和传统“SELECT 完了再排序”的直觉不一样。
1.3 NULL 的排序位置：比你想的更“靠前”
MySQL 对 NULL 的处理和其他数据库还有一点差异：默认规则下，NULL 被认为比任何非 NULL 值都小。所以ORDER BY field ASC时，NULL 会排在结果集的最前面；ORDER BY field DESC时，NULL 又会被甩到最后面。
这个规则很反直觉。大多数业务希望“没有值的记录排最后”，但默认行为恰好相反。我这里给一个常用写法：
SELECT id, remark FROM user_remark ORDER BY (remark IS NULL) ASC, remark ASC;remark IS NULL是一个布尔表达式，非 NULL 时为 0，NULL 时为 1。把它作为第一排序键，0 永远排在 1 前面，所以就实现了“非空记录在前，空值记录在后”的效果。如果还需要在非空记录里按内容排，就再加第二字段。
多字段下也要注意 NULL 的组合情况。假如ORDER BY a ASC, b DESC，而a大量为 NULL，那么所有a为 NULL 的行都会集中在最前面，此时b的倒序只会在这些 NULL 组内生效，容易让用户觉得“排序根本没生效”。这种场景下，最好先想清楚：到底是用 NULL 过滤掉，还是把 NULL 特殊位置排序，不要指望默认行为符合业务直觉。
2. 排序结果“看起来不对”的头号元凶：字符集与 Collation
2.1 _ci、_bin、_ai：排序规则的后缀到底意味着什么
很多排序异常查到最后，问题都不在 ORDER BY 本身，而在于字段的字符集排序规则，也就是 Collation。字符集（Charset）解决的是“字符如何编码成字节”，而 Collation 解决的是“这些字节按什么规则比较大小”。你可以把字符集想象成新华字典的页码方案，Collation 就是“部首笔画排序”还是“拼音字母排序”这种规则。
MySQL 里最常用的是utf8mb4字符集，但utf8mb4下面还有一堆排序规则：
| Collation 后缀 | 含义 | 典型行为 |
|---|---|---|
_ci | Case Insensitive，不区分大小写 | A与a视为相同，排序位置相同 |
_cs | Case Sensitive，区分大小写 | A与a视为不同，通常大写在前 |
_bin | Binary，二进制比较 | 直接按编码值比较，最严格 |
_ai | Accent Insensitive，不区分重音 | é与e排序位置相近 |
_as | Accent Sensitive，区分重音 | é与e严格分开 |
拿最常见的utf8mb4_general_ci、utf8mb4_unicode_ci、utf8mb4_0900_ai_ci来说，它们的差别不仅是性能，还有对“相等”的判定粒度。general_ci的比较规则比较简化，速度快但对一些特殊字符的处理不太准确；unicode_ci基于 Unicode 排序算法，字符顺序更符合通用语言习惯，但算起来更重；utf8mb4_0900_ai_ci是 MySQL 8.0 引入的默认排序规则，比较规则更完善，默认不区分重音和大小写。
它们之间一个直接的影响是：同一个查询，同一个数据，DBA 把列从utf8mb4_general_ci改成utf8mb4_unicode_ci后，排序结果就可能变了。所以当你听到业务说“我们什么都没动，排序突然变了”，先查一下是不是有人重建过表、改过字符集默认值，或者迁移库的时候版本从 5.7 升到了 8.0。
2.2 查询级别强制 COLLATE：临时改规则的下策
如果你只是想确认某个字段在严格大小写规则下怎么排，无需改动表结构，可以使用COLLATE关键字临时指定排序规则：
SELECT nickname FROM user_profile ORDER BY nickname COLLATE utf8mb4_bin ASC;这句话会把nickname的比较规则临时切到二进制比较，大小写不同会被严格区分，中文也会按 Unicode 码点排序。这种写法适合在测试环境做验证：比如业务反馈“某个用户的昵称是大写的，排序却和小写挨在一起”，你就可以用COLLATE utf8mb4_bin确认到底是不是大小写混排导致的。
但这里我不建议把COLLATE放进所有业务 SQL 里长期使用。第一，强制 COLLATE 可能导致 MySQL 放弃本来可以使用的索引——因为索引是按照列本身定义的排序规则建的，临时指定不同的规则，索引顺序就不一定匹配了，优化器只能去 filesort。第二，排序规则不一致意味着不同模块的排序口径可能漂移，这个模块按大小写严格排，那个模块按不区分大小写排，用户看到的列表就会前后矛盾。
真正稳妥的方案，是在建表阶段就把排序规则定准，比如明确需要区分大小写就定义成utf8mb4_bin或utf8mb4_0900_as_cs。如果已经上线且数据量大，宁可新增一个冗余字段来表达“用于排序的规范化值”，也不要让线上查询都靠 COLLATE 打补丁。
2.3 同样的表结构，不同的库字符集，为什么结果不一样
还有一种常见场景是两个表 JOIN 时，两边字段的字符集或者排序规则不一致。比如 A 表是utf8mb4_unicode_ci，B 表是utf8mb4_general_ci，JOIN 条件里的等值比较又不带COLLATE，MySQL 只能选一个双方都能兼容的比较规则。然后在 ORDER BY 里，别的字段按 A 表规则排，JOIN 出来的字段按兼容规则排，整体顺序就会显得很怪。
我在实际排查中遇到过一种“索引完全失效，但 EXPLAIN 里没有显示出来”的情况：关联字段的字符集一个是utf8mb4，另一个是latin1，优化器在关联时就不得不做字符集转换，把本来能走索引的连接条件变成了逐行比较。ORDER BY 同样受影响，因为排序依赖的列值在内存里已经被转换过一轮。
所以建议数据库巡检时顺手看一眼所有表的字符集，尽量统一成utf8mb4，并且默认排序规则也统一。至少在业务核心链路上，不要出现三五种排序规则并存的情况。你可以用类似下面的查询摸底：
SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'your_db' AND DATA_TYPE IN ('varchar', 'char', 'text') ORDER BY COLLATION_NAME;你会发现不统一的字段比想象中多。这些字段平时查询不多看不出来，一旦进了报表、进了后台列表排序，就是疑难杂症。
3. 索引用不上的代价：filesort 与排序性能排查
3.1 索引有序性的利用条件
ORDER BY 最理想的情况，是 MySQL 直接按照索引顺序取数据，根本不需要额外的排序步骤。这时EXPLAIN的Extra列通常不会出现Using filesort。但要让索引为排序服务，有三个条件缺一不可：排序字段顺序满足索引最左前缀、排序方向和索引定义方向一致、查询过滤条件没有让优化器觉得“扫描全索引更吃亏”。
举个例子，联合索引idx(status, create_time, id)存在的情况下：
-- 可以走索引排序 SELECT * FROM order_info WHERE status = 'pending' ORDER BY create_time ASC, id ASC; -- 无法仅靠索引完成排序 SELECT * FROM order_info ORDER BY create_time ASC, id ASC;第一条能走索引，因为status = 'pending'固定了第一列，后面两个排序字段正好吻合联合索引的剩余列。第二条不行，因为create_time, id不满足最左前缀，跳过status直接拿后面两列排序，索引树本身不保证这个顺序。这是最容易被忽略的联合索引规则，也是“为什么我建了复合索引，ORDER BY 还是 filesort”的常见答案。
还有方向一致的问题。MySQL 8.0 之前，索引默认按升序存储，如果你用ORDER BY create_time DESC，优化器要么选择反向扫描索引，要么干脆转成 filesort。8.0 开始支持降序索引，可以在定义索引时显式写CREATE INDEX idx_order ON order_info (create_time DESC)，这样倒序查询能更稳定地走索引。如果你的数据库还是 5.7，遇到高频倒序查询，更实际的做法是评估是否真的需要一个冗余的升序字段，或者对排序结果做缓存。
3.2 filesort：内存排序与磁盘排序的代价
一旦索引帮不上排序，MySQL 就会把符合条件的行取出来，放进排序缓冲区（sort buffer）自己排序。这就是Explain里Using filesort的真实含义。注意，“filesort”不一定是真的文件排序，数据量小的时候全在内存里完成，数据量一大，缓冲区放不下，就开始用临时文件做外部归并排序，这时候性能下降非常明显。
我用过一个排查思路，可以判断线上是否发生了高代价排序：
SHOW SESSION STATUS LIKE 'Sort%';这里主要看Sort_merge_passes。如果这个值持续增长，说明频繁发生了“内存放不下、转磁盘归并”的排序操作。另一个相关参数是sort_buffer_size，它控制排序缓冲区的上限，默认值对生产环境来说往往偏小。调大它能减少磁盘归并次数，但不是越大越好，因为每个会话都会占用这个内存，并发高的时候会造成较大的内存压力。
实际优化 filesort 的顺序应该是：
- 看
EXPLAIN的Extra是否出现Using filesort。 - 确认现有的索引能否通过调整字段顺序覆盖排序需求。
- 尽量让 SELECT 的列数量精简，特别是避免把
TEXT、超长VARCHAR放进排序结果中，因为排序缓冲区要容纳的行越宽，能装下的行数越少，越容易触发磁盘归并。 - 创建覆盖索引，让排序字段和查询字段都在索引里，省掉回表。
- 最后才考虑调大
sort_buffer_size，而不是一上来就去改全局参数。
这里要特别说一句，很多人遇到慢查询就条件反射调 buffer，但排序性能问题的根源往往不是内存不够大，而是 SQL 设计没有利用有序索引。内存参数是兜底方案，不是首选方案。
3.3 针对深度分页排序的优化突破口
分页和 ORDER BY 绑在一起之后，出现另一个经典问题：翻页越深越慢。例如ORDER BY create_time DESC LIMIT 100000, 20，MySQL 只想要最后 20 条，但它必须先把前 100000 条全部取出来参与排序，再丢弃前 100000 条，这个成本完全浪费。
如果业务场景是“上一页下一页”而不是“跳页”，可以用游标式分页：
SELECT id, title FROM article WHERE create_time < '2025-04-12 10:00:00' -- 上次请求拿到的最后一条时间 ORDER BY create_time DESC LIMIT 20;再配合唯一排序键，翻页就不会重复也不会漏。如果产品跳页需求仍然存在，要降低排序对大偏移量的压力，可以考虑“延迟关联”或者说“先取主键再回表”的写法：
SELECT a.id, a.title FROM article a INNER JOIN ( SELECT id FROM article ORDER BY create_time DESC LIMIT 100000, 20 ) t ON a.id = t.id ORDER BY a.create_time DESC;子查询里只访问索引和主键，行宽度小，排序压力和临时表压力都会小很多；外层再去原表取完整数据。这个技巧在后台管理系统的列表页里很实用，因为后台列表常常带各种条件，能完全消除 filesort 的场景不多，退而求其次就是把文件排序的行宽压到最小。
4. 业务里的排序“变体”需求：自定义优先级、中文排序与翻页稳定
4.1 自定义优先级排序：FIELD 与 CASE WHEN 的取舍
枚举字段排序是业务后台最常见的需求。比如工单状态是new、processing、done，产品想要“处理中的最优先，新建的其次，已完成最后”，单纯按状态字母排肯定不行，这时候有两条路：FIELD()和CASE WHEN。
SELECT id, status FROM ticket ORDER BY FIELD(status, 'processing', 'new', 'done'), id ASC;FIELD(status, 'processing', 'new', 'done')会让processing返回 1，new返回 2，done返回 3。不在列表里的值返回 0，会被排到最前面。这个写法很简洁，但有一个坑：如果枚举值数量多，或者在代码里动态拼接，SQL 会变得难读，也不利于复用。
我更推荐在优先级逻辑复杂的场景里用CASE WHEN：
SELECT id, status, last_updated FROM ticket ORDER BY CASE WHEN status = 'processing' THEN 0 WHEN status = 'new' THEN 1 WHEN status = 'done' THEN 2 ELSE 3 END ASC, last_updated DESC;这样表达更直白，可维护性也更强。要提醒的是，这种自定义排序基本上无法利用索引，如果工单表很大且有高性能要求，最好在表里冗余一个sort_priority数字字段，写入时维护好，排序时直接ORDER BY sort_priority，再通过索引覆盖。业务规则和数据库设计分开，长期维护成本会低很多。
4.2 中文按拼音排、按笔画排怎么做
中文排序是个老话题。MySQL 的utf8mb4字符集虽然支持中文，但默认排序规则是按 Unicode 码点比较的，这和中文使用者习惯的“拼音序”并不一致。换句话说，期望“张”排在“王”前面，但按 Unicode 码点排出来的顺序可能完全不是这回事。
老一些的 MySQL 技巧是直接把列转成 GBK 字符集按字节排：
SELECT name FROM user ORDER BY CONVERT(name USING gbk) ASC;GBK 的编码顺序和汉字拼音顺序有很强的相关性，所以这个写法能够在大多数常用汉字上接近拼音排序。但要注意，它毕竟是编码层面的“曲线救国”，遇到多音字、生僻字、特殊符号，结果可能不符合预期。这个方法适合临时报表导出，不适合作为核心业务的长期排序方案。
真正可靠的做法，是在数据库里冗余一个“拼音首字母”或“全拼”字段，写入时通过业务代码或离线任务生成。比如name_pinyin字段存zhangsan，排序直接用ORDER BY name_pinyin ASC。想要笔画排序也类似，冗余一个stroke_count字段。如果数据量特别大、搜索排序需求复杂，甚至可以考虑把排序需求交给专门的检索引擎来做，数据库里只存拼音或拼音首字母这种轻量级字段。业务需求不该靠数据库的隐式规则硬撑，显式的字段才是最稳定的。
4.3 排序键不唯一导致翻页数据跳变
最后分享一个很少人注意、但线上踩一次就够难受的问题。如果 ORDER BY 的字段不是唯一的，比如只按create_time DESC排序，而同一秒内插入了多条数据，分页翻页时就会有一部分记录像“幽灵一样”跑到上一页或者从本页消失。
原因不复杂：排序键相同时，MySQL 返回这些记录的顺序没有保证，可能是插入顺序，也可能是索引扫描的物理顺序，不同页面的两次查询之间，优化器选中的执行计划或者数据行位置发生变化，相同位置的记录就变了。解决方案是给排序补一个唯一兜底字段：
SELECT id, title, create_time FROM article ORDER BY create_time DESC, id DESC LIMIT 20;加上id DESC之后，每条记录都有了确定性的相对位置，翻页结果才是稳定的。两层排序键仍然可能重复的概率极低，如果真遇到了，再往下加一个唯一字段即可。这个习惯成本极低，收益是排序结果可复现。我在做报表分页时，几乎是无脑在所有 ORDER BY 后面加一个id DESC，唯一例外是已经确认排序键完全唯一的情况。
这个现象也牵出一个更底层的经验：MySQL 的排序结果不要依赖“隐式顺序”。即使某个字段看起来在表里是按新增顺序存的，也不代表每次查询都会按这个顺序返回。所有对顺序有要求的查询，都必须显式指定 ORDER BY，并且最好带上唯一字段。
实际运维了一段时间后，我对 ORDER BY 的态度是：上线前多花五分钟看 EXPLAIN，确认有没有Using filesort；定 Collation 时多确认一层大小写和重音规则；写分页 SQL 时无条件加唯一兜底字段。这几点做到位，线上因为排序引发的“灵异事件”基本能消灭一多半。
