这个标题看着基础，但说句实话，我这些年排查过的线上慢查询里，因为一个ORDER BY写得不合适导致全表排序、接口超时的案例，少说也有几十起了。很多人以为"排序不就是ORDER BY字段一下嘛"，可真等数据量上来，索引又没建对，一条排序SQL把数据库CPU打满的情况一点都不稀奇。这篇文章我不想只念语法手册，而是把MySQL排序从底层执行逻辑到索引利用、再到真实业务场景的排查优化，完整串一遍，配合实际案例的EXPLAIN分析和优化前后耗时对比，希望能帮你把排序这块吃透。
1. 从一条慢SQL说起：ORDER BY远比想象中复杂
先还原一个典型的线上问题。运营后台有个订单列表页，按创建时间倒序展示最新订单，分页取前20条。SQL写出来长这样：
SELECT * FROM orders WHERE status = 1 ORDER BY create_time DESC LIMIT 20;表里大概有200万行订单数据，status=1的记录大约占三分之一。这SQL在测试环境只有几万条数据时毫无压力，等上了生产，接口响应从几十毫秒慢慢涨到两三秒，最后直接超时。
这时候很多人第一反应是"给create_time建索引不就完了吗"。可问题恰恰出在这里：单独给create_time建索引，WHERE条件还带了一个status = 1，优化器综合评估后认为走status上的索引过滤行数更划算，但status索引内部记录不是按create_time排序的，于是查询结果出来后还得额外做一次排序操作，去磁盘里把200万行的部分数据捞出来排一遍——这就是慢的根源。
这个例子说明了一个核心问题：排序能不能快，关键不是"有没有排序"这个动作，而是排序发生在"哪一层"，以及能否直接利用索引天然的顺序。理解这一点，是所有排序优化的起点。
所以下面我们先把MySQL排序的底层机制讲清楚，再回过头来解决这类"既有过滤条件又有排序"的实际场景。
2. 排序的底层机制：索引天然有序与filesort的两个分支
2.1 MySQL到底是怎么完成一次排序的
当MySQL执行带ORDER BY的查询时，存在两条完全不同的路径：
第一条路径是直接利用索引的顺序读取数据。因为InnoDB的B+树索引叶子节点本身是有序的，如果查询计划走的索引恰好和ORDER BY的顺序一致，MySQL就顺着索引顺序一路扫描，根本不需要额外的排序动作。这也是为什么我们说"索引天然有序"。
第二条路径是无法利用索引顺序时，MySQL自己动手排序，这个动作在EXPLAIN的结果里会以Using filesort标记。注意，"filesort"这个名字很容易让人误以为一定会写磁盘文件——其实不是。它先是尽可能在内存里的sort buffer中完成排序，只有当排序数据量超过sort_buffer_size限制时，才会使用磁盘上的临时文件做外部归并排序。所以"filesort"更准确的理解是"MySQL自行排序"这个动作，不一定真写了文件。
判断一次查询是否走了排序的通用手段，就是看EXPLAIN输出里Extra列有没有Using filesort。有，就说明排序发生在MySQL层面；没有，说明排序被索引吸收了。
2.2 filesort的两种算法：单路排序与双路排序
MySQL执行filesort时，根据排序字段和查询列的数据量，会选择两种不同的排序算法：
单路算法（Single-pass）：一次扫描读取所有需要的字段——包括排序字段和查询要返回的字段——全部放进sort buffer，在内存里排好序后直接输出结果。这种方式I/O次数少，对性能更友好。MySQL 8.0中，只要排序行大小不是太大，一般优先采用单路排序。
双路算法（Two-pass）：当查询的字段非常多，或者单行数据很长，比如SELECT *带了一堆TEXT、VARCHAR(1000)这种大字段，sort buffer可能放不下太多行。MySQL就会退化为双路排序：第一次只读取排序字段和行ID放进sort buffer排序，排好后根据行ID再回聚簇索引取完整行数据返回。这种方式会多一次回表读取，且随机I/O多，性能明显更差。
2.3 sort buffer不是越大越好
sort_buffer_size是每个会话单独分配的内存，默认256KB，最大可以配到几MB。很多新手遇到排序慢就猛调这个参数，但其实有副作用：如果数据库并发连接数很高，每个连接都分配这么大的sort buffer，内存很快就扛不住，反而引发OOM风险。
我的一般做法是：线上sort_buffer_size保持默认256KB，除非确认某个业务场景排序数据量大、并发不高，才针对性调到1MB-2MB。优先做的永远是"能不能去掉filesort"，而不是"能不能让filesort更快"。
现在我们对filesort有了清晰认识，接下来需要逐一拆解ORDER BY用法的细节——很多人踩坑，往往就踩在这些"不起眼"的语法点上。
3. 单列和多列排序的语法细节与排序规则
3.1 基础语法与升降序
ORDER BY最基本的用法是：
ORDER BY create_time DESC ORDER BY amount ASC需要注意两点：
- 默认排序方向是
ASC（升序），不写方向时按升序处理。 DESC/ASC只作用于它前面的那一个列，不是作用于后面所有列。
很多人会犯一个直觉性错误，写ORDER BY a, b DESC，以为a和b都是倒序。实际上这个语句的含义是：先按a升序，a相同时再按b倒序。想要两列都倒序，必须写成ORDER BY a DESC, b DESC。
3.2 多列排序的执行顺序
看这个例子：
SELECT user_id, order_amount FROM order_detail ORDER BY user_id ASC, order_amount DESC;MySQL的处理逻辑是：先取user_id做第一轮排序，所有user_id相同的行内部，再按order_amount降序排列。最终结果呈现的效果是"用户ID从小到大，同一用户的订单金额从大到小"。
这种多列排序如果想走索引，对索引设计要求非常死板：索引列的最左前缀和排序方向必须完全一致。比如上面这条SQL，最理想的索引是(user_id ASC, order_amount DESC)。MySQL 8.0开始支持索引的降序存储，可以显式创建(user_id, order_amount DESC)这样的倒序索引。在8.0之前，索引只能按升序存，但优化器有时能反向扫描索引来模拟降序，性能差异不大；不过混合了ASC和DESC的排序，老版本就很难完全利用索引了。
3.3 ORDER BY后面可以跟什么
ORDER BY不仅能跟列名，还能跟下面这些形式：
- 表达式：
ORDER BY price * quantity，按照商品单价和数量的乘积排序。此时索引完全失效，因为索引存的是原始列值不是乘积结果，排序只能走filesort。 - 别名：
SELECT user_name AS name FROM users ORDER BY name。MySQL允许在ORDER BY中使用SELECT列表里的别名，这在编写复杂报表时很省事。但要注意，这个待遇只给ORDER BY，WHERE和HAVING里引用列别名是另一回事。WHERE子句里不能用别名，因为WHERE在SELECT别名定义之前执行。 - 字段序号：
ORDER BY 2表示按SELECT列表的第二列排序。不推荐这么写，可读性太差，一旦SELECT的字段列表顺序调整，排序结果就悄悄变了，排查起来很折磨人。
3.4 NULL值的排序位置：最容易忽略的坑
MySQL里，NULL在排序中有非常特殊的地位：升序时，NULL排在最前；降序时，NULL排在最后。也就是说，MySQL把NULL看作比任何实际值都"小"。
这一条在日常开发中经常制造问题。比如一个"下单时间"字段允许为空，业务方希望create_time DESC时，最晚下单的排最前，没下过单的排最后——这恰好符合MySQL的默认行为，不需要额外处理。但反过来，如果业务要求"已下过单的按时间从晚到早排，最后再显示没下过单的"，MySQL的默认行为就不符合了。
解决办法是用一个额外的标记列控制位置：
ORDER BY CASE WHEN create_time IS NULL THEN 1 ELSE 0 END ASC, create_time DESC;这个写法把NULL的优先级通过CASE表达式"翻译"成了0或1，先按标记排序，再按实际时间排序。缺点是这个CASE表达式让索引利用变得困难，排序字段变成表达式后无法走索引。如果数据量大且对性能有硬指标，更稳妥的方案是在表里加一个is_ordered标记字段，或者直接禁止空值、用默认时间兜底。
3.5 字符串排序的隐藏逻辑
字符串列排序时，是根据字符集和排序规则（Collation）来决定的。同一个列，在utf8mb4_general_ci、utf8mb4_0900_ai_ci、utf8mb4_bin这些不同collation下的排序结果可能完全不同。
_ci结尾表示case insensitive，也就是大小写不敏感，排序时a和A视为一样；_bin表示二进制比较，按字符的编码字节顺序来排，大小写敏感。
所以如果你发现某个字符串列排序结果和预期不一致，第一反应应该去看该列定义的collation，而不是怀疑SQL写错了。比如需要大小写敏感排序，可以临时指定排序规则：
SELECT name FROM users ORDER BY name COLLATE utf8mb4_bin;接下来我们把字符串排序里最特殊、也最让中文开发者头疼的"中文排序"单独拎出来说。
4. 中文、字符串与特殊字段的排序陷阱
中文排序是个经典的"看似能行，一测就翻车"的问题。原因在于MySQL的utf8mb4字符集虽然有良好的字符存储能力，但它对中文内容的默认排序是按Unicode码点来排的，既不按拼音，也不按笔画——ORDER BY name ASC得到的结果，在中文语境下通常"没有规律可言"，因为Unicode码点先排完拉丁字母、日文假名等符号，中文内部的排列也不是日常使用的字典序。
举个最简单的例子，三个人名"张三""李四""王五"，按UTF-8排序结果是李四、张三、王五。如果业务期望"按拼音字母顺序"，正确结果应该是李四（L）、王五（W）、张三（Z）。两者大相径庭。
那么MySQL到底能不能按拼音排？有一些"偏方"，最常见的是把中文转换为GBK编码后，再按GBK的字节顺序排序：
SELECT user_name FROM user_info ORDER BY CONVERT(user_name USING gbk);这个写法在utf8/utf8mb4字符集下可以生效，因为GBK编码的汉字顺序和拼音顺序一致，结果是按拼音排序的。但有几个前提要清楚：
- 如果数据里包含大量生僻字、繁体字，GBK可能无法完整编码，会出现转换错误或排序不准确。
CONVERT操作会让全表每行的字段都做一次转换计算，排序字段变成函数表达式，索引完全失效，数据量一大性能就很差。- 同时，MySQL 8.0默认字符集已经是utf8mb4，但GBK排序方式跟UTF-8的collation完全是两套体系，混用时容易出幺蛾子。
所以我的建议非常明确：除非是几千条的小数据量场景用用还行，否则不要依赖CONVERT USING gbk来做中文拼音排序。更靠谱的路线有两种：
第一，在应用层用支持拼音处理的函数库进行排序。Java里可以转成拼音再比较，Python可以用pypinyin库，这样排序逻辑可控，不污染数据库。
第二，如果一定要在数据库里排，那就单独增加一个pinyin_sort字段，在写入数据时由应用层计算好拼音首字母或完整拼音存入，对这个字段建索引排序，效率远高于任何运行时的转换函数。
除了中文排序，还有两个常见"特殊字段"的坑也值得提醒一下：
**UUID主键排序。**UUID是随机字符串，按它排序不仅索引利用率差，而且因为随机性高，插入时也会导致页分裂严重。如果业务表使用了UUID做主键又有排序需求，最好加一个自增id列或创建时间列来替代排序依据。
**VARCHAR类型的数字排序。**如果字段类型是VARCHAR但存的是数字字符串，ORDER BY会按照字典序排：'10'会排在'9'前面，因为字符串比较先比第一位字符。此时需要ORDER BY CAST(phone AS UNSIGNED)之类的显式转换，但同样的，函数会阻碍索引。
5. 排序性能优化：让filesort消失的实战手段
前面说了这么多底层机制，现在回到整个排序优化最核心的话题：如何让排序不再成为瓶颈。直接给出优先级最高的优化策略：先争取消除filesort，再考虑减少filesort本身的开销。
5.1 用好索引消除filesort：方向与最左前缀
索引能消除排序的原理是：B+树叶子节点本身就是按索引列有序排列的。MySQL只要顺着索引扫描，读取到的数据天然就是有序的，省去了排序操作。但这里有两个硬性约束。
约束一是排序列必须遵守最左前缀。如果索引是(status, create_time)，那么只有ORDER BY status, create_time能完全利用索引排序；单独ORDER BY create_time不行，ORDER BY status, create_time, user_id也不行，user_id超出了索引键范围。
约束二是排序方向和索引扫描方向要匹配。MySQL优化器可以正向扫描索引（ASC）也可以逆向扫描索引（DESC），但如果你写了混合方向如ORDER BY status ASC, create_time DESC，在MySQL 8.0之前索引没法直接支撑，通常会产生filesort；8.0支持索引定义中的倒序键，可以更灵活地匹配这类排序需求。
还有一点容易被忽略：如果WHERE条件里使用了范围查询（如create_time BETWEEN '2024-01-01' AND '2024-12-31'），那么范围条件之后的排序列就无法继续利用索引顺序了。这是因为索引顺序首先要满足WHERE的范围扫描范围内有序，跳出范围后其他键的顺序对WHERE过滤意义不大。所以混合等值条件+范围+排序时，索引设计要特别小心。
5.2 尽量少取列：SELECT *是filesort的隐形放大镜
很多人不重视SELECT *的问题，但在排序场景下，它带来的影响会被显著放大。
前文说filesort单路算法会把"查询需要的所有字段"都放进sort buffer。如果SELECT *把每行所有列都塞进buffer，同样的buffer容量能装下的行数就少，一旦超过阈值，就会走上磁盘外部归并排序，性能陡降。同时双路排序需要按行ID回表读取完整数据，随机I/O次数更多。
所以排序SQL的一个重要优化原则是：只查出真正需要的列，能用覆盖索引就尽量用覆盖索引——即索引中已经包含了查询的所有列，不需要回表。例如：
-- 覆盖索引 (status, create_time, order_no) SELECT status, create_time, order_no FROM orders WHERE status = 1 ORDER BY create_time DESC LIMIT 20;这条SQL在满足status=1条件后，直接在索引内部取到三个字段值，既不需要回表，也不需要filesort——因为索引已经给出了排序顺序。这是排序场景下性能最优的形态。
5.3 参数调优：filesort逃不掉时的兜底手段
确实有一些场景无论怎么设计索引都无法消除filesort。比如排序字段是表达式ORDER BY price * quantity，或者业务需求本身就要全表排序后取TopN。这时候才有必要考虑调优filesort本身。
最直接影响filesort效率的参数是sort_buffer_size，针对这类无法避免的排序任务，可以在会话级别临时调大：
SET SESSION sort_buffer_size = 2 * 1024 * 1024;注意这里是会话级设置，只对当前连接生效。如果放在全局配置，要考虑所有并发线程同时占用的内存总量，别把小内存机器撑爆。另外MySQL 8.0.20之后被参数max_sort_length限制排序行单行大小的逻辑也有调整，核心思路依然是：单行数据越小，sort buffer能装的行越多，排序效率越高。
再分享一个平时用得不多的诊断工具：optimizer_trace。它能打印优化器做排序决策的完整过程，包含是否选择filesort、sort buffer是否溢出、排序算法选择等关键信息。用法是：
SET optimizer_trace = "enabled=on"; SELECT ... ORDER BY ...; SELECT * FROM information_schema.OPTIMIZER_TRACE;在分析为什么某条排序SQL没有走索引时，这个工具给的线索比EXPLAIN还要细。
6. 完整实例复盘：电商订单列表的排序优化全记录
接下来用一个贯穿全文的完整案例，把排序优化的完整链路串起来。这个案例源自某电商后台"待处理订单列表"的真实优化过程。
6.1 原始表结构和慢SQL
表结构简化为：
CREATE TABLE orders ( id BIGINT AUTO_INCREMENT PRIMARY KEY, user_id BIGINT NOT NULL, status TINYINT NOT NULL, order_no VARCHAR(32) NOT NULL, create_time DATETIME NOT NULL, amount DECIMAL(10,2) NOT NULL, remark VARCHAR(255), KEY idx_status (status), KEY idx_create_time (create_time) ) ENGINE=InnoDB;线上慢SQL：
SELECT * FROM orders WHERE status = 1 ORDER BY create_time DESC LIMIT 20;通过EXPLAIN看到的结果是：
type: ref key: idx_status rows: 650000 Extra: Using filesort也就是说，MySQL选择使用idx_status过滤出65万行status=1的数据，但因为这65万行在索引里不是按create_time排序的，所以需要把这65万行取出来在sort buffer里排一遍，再取前20条。
6.2 第一次优化：联合索引让排序消失
解决方案是新建一个联合索引：
ALTER TABLE orders ADD INDEX idx_status_create_time (status, create_time DESC);这个索引的设计思路是：先通过status = 1等值条件定位，索引内部再按create_time倒序排列。MySQL可以从索引叶子节点直接按create_time倒序读出前20条记录，然后回表取完整行数据返回。filesort被彻底消除。
优化后的EXPLAIN：
type: ref key: idx_status_create_time rows: 20 Extra: (空)关键区别在于Extra列不再出现Using filesort，rows也从65万降到了20。因为LIMIT 20配合有序索引，扫描到20行就可以直接结束。这条优化是排序场景中收益最大的一类改动：不但省掉了排序的开销，还让扫描行数从65万骤降到20。
6.3 第二次优化：覆盖索引干掉回表
上面这个方案虽然没了filesort，但因为SELECT *需要回表读取每一行的完整数据，随机I/O还是有一定开销。如果后台列表只需要显示订单号、金额、创建时间这几个字段，就可以进一步把查询改成覆盖索引：
ALTER TABLE orders ADD INDEX idx_status_create_amount (status, create_time DESC, order_no, amount); SELECT status, create_time, order_no, amount FROM orders WHERE status = 1 ORDER BY create_time DESC LIMIT 20;此时索引本身已经包含查询要的所有列，MySQL连回表都省了，直接扫索引叶子节点返回结果。数据量越大，这步优化的收益越明显。
如果业务硬要返回全列，覆盖索引也覆盖不了太多字段，那我的经验是：先保证消除filesort，再结合业务评估是否可以把SELECT *改成明确字段列表，或者把大字段拆到另外一张扩展表。
6.4 第三次观察：分页深翻页时的排序退化
有了联合索引以后，列表第一页速度飞快，但运营翻页翻到第500页时，问题又来了：
SELECT * FROM orders WHERE status = 1 ORDER BY create_time DESC LIMIT 10000, 20;这条SQL虽然依然走索引没有filesort，但这并不是一个简单的"跳过10000条再读取20条"的过程。为了跳过前10000条，MySQL需要先从索引顺序扫描并数到第10000条的位置，如果索引叶子节点不是直接按位访问，那么这部分可能带来明显的成本增长。更麻烦的是，这种深分页写法在数据量大时会让数据库CPU突增，响应时间直线上升。
优化方案是改写成基于上一页最大create_time的"游标分页"：
SELECT * FROM orders WHERE status = 1 AND create_time < '上一页最后一条的create_time' ORDER BY create_time DESC LIMIT 20;这样MySQL直接从上次看到的时间点向后扫20条，不再需要跳上万行的偏移量。如果create_time有重复值，记得额外带上id作为辅助排序条件保证结果稳定：
ORDER BY create_time DESC, id DESC6.5 本次案例的启示
这个案例能归纳出的优化路径其实很通用：先看EXPLAIN确认是否有Using filesort，有就优先设计联合索引消除它；再看是否回表，用覆盖索引减少I/O；最后看业务形态，分页查询尽量用游标而不是深offset。排序优化不只是"加个索引"这么简单，它是一个从执行计划出发、逐步逼近最优方案的过程。
我在处理过这么多排序性能问题之后，最深的一个感受是：排序SQL的慢，从来不是ORDER BY这个语法本身慢，而是它触发了不必要的全量排序或回表。所以排查的时候永远不要只盯着SQL文本，要顺着执行计划去找数据是怎么被读取、怎么被排列的。先把这句话记住，排序这关就算过了大半。
解法)