这个标题看着基础但说句实话我这些年排查过的线上慢查询里因为一个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万行订单数据status1的记录大约占三分之一。这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 BYWHERE和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之前索引没法直接支撑通常会产生filesort8.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在满足status1条件后直接在索引内部取到三个字段值既不需要回表也不需要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 enabledon; 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) ) ENGINEInnoDB;线上慢SQLSELECT * 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万行status1的数据但因为这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被彻底消除。优化后的EXPLAINtype: ref key: idx_status_create_time rows: 20 Extra: (空)关键区别在于Extra列不再出现Using filesortrows也从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文本要顺着执行计划去找数据是怎么被读取、怎么被排列的。先把这句话记住排序这关就算过了大半。 MySQL排序优化实战：ORDER BY慢查询排查与索引利用
这个标题看着基础但说句实话我这些年排查过的线上慢查询里因为一个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万行订单数据status1的记录大约占三分之一。这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 BYWHERE和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之前索引没法直接支撑通常会产生filesort8.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在满足status1条件后直接在索引内部取到三个字段值既不需要回表也不需要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 enabledon; 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) ) ENGINEInnoDB;线上慢SQLSELECT * 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万行status1的数据但因为这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被彻底消除。优化后的EXPLAINtype: ref key: idx_status_create_time rows: 20 Extra: (空)关键区别在于Extra列不再出现Using filesortrows也从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文本要顺着执行计划去找数据是怎么被读取、怎么被排列的。先把这句话记住排序这关就算过了大半。