1. 先弄明白 MySQL 眼里的 NULL 到底排在哪写业务 SQL 的时候ORDER BY把NULL排到哪里是个特别容易被忽略、但又特别容易出线上问题的地方。我自己第一次被这个问题咬到是在做一个后台订单列表的时候运营同学反馈“按下单时间升序排最新那批待支付订单怎么跑到最上面去了下面全是空的”。检查半天代码没毛病最后发现是pay_time这一列允许为NULLORDER BY pay_time ASC时 MySQL 把NULL当成最小值排在队首。这件事之后我把「MySQL 排序时 ORDER BY 怎么把空值 NULL 放到最后」整理成了一套自己的固定做法今天完整摊开讲一遍。这篇文章适合三类人看刚写完带排序接口、还没遇到过NULL排位问题的初中级开发从其他数据库迁到 MySQL、习惯了NULLS LAST写法的同学以及需要在千万级表上兼顾排序结果和查询性能的 DBA 或后端负责人。先把结论摆在最前面省得你翻到最后MySQL 在ORDER BY中把NULL视作比任何非NULL值都小的值。所以升序ASC时NULL排在结果集最前面降序DESC时NULL排在最后面。这一点和“空字符串”“数字 0”“1970-01-01”完全是两码事它遵循的是 SQL 标准里对NULL的定义——未知值。要让升序结果里NULL沉底MySQL 没有现成的关键字不像某些数据库可以直接写NULLS LAST必须借一个额外的排序表达式把它“推”下去。接下来的内容我会从原理、写法、性能、排查四个层面把它讲透每一种写法都会给出可直接复制的 SQL。1.1 三个常见误解NULL 不是空串、不是 0、也不是“最后”在动手改 SQL 之前先把几个高频误解清掉否则改出来的语句往往是“看着对、跑起来错”。第一个误解是把NULL当成空字符串。在 MySQL 里是一个确定的、长度为 0 的字符串值参与排序时它排在所有非空字符串之前、排NULL之后。也就是说 a但NULL 。如果你表里既有NULL又有ORDER BY col ASC的结果是NULL、、a这个顺序很多同学只盯着NULL处理结果又冒到前面去了。第二个误解是把NULL当成 0。数值列里NULL和0在排序上是分开的NULL永远在0前面升序。只有在做算术运算时NULL才会“污染”结果——NULL 1还是NULL这也是为什么对含NULL的列直接SUM、AVG会得到看起来“偏大”的平均值聚合函数会跳过NULL行分母不是总行数。第三个误解也是杀伤力最大的一个以为 MySQL 会自动把NULL放最后。只要你不显式指定MySQL 一定按“NULL 最小”的规则排。很多接口在测试环境跑得好好的是因为测试数据里那几列恰好都有值一上生产历史数据里一堆NULL排序结果立刻和预期错位。1.2 升序降序下的真实表现对照为了让后面的讨论有个基准我用一张简化过的订单表把各种情况跑一遍。建表和造数据的语句如下你可以直接在自己库里的临时表里复现CREATE TABLE t_order ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT NOT NULL, pay_time DATETIME NULL, amount DECIMAL(12,2) NOT NULL, remark VARCHAR(64) NULL, status TINYINT NOT NULL, KEY idx_pay_time (pay_time) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; INSERT INTO t_order (user_id, pay_time, amount, remark, status) VALUES (1001, 2024-03-01 10:00:00, 199.00, 加急, 1), (1002, 2024-03-02 11:30:00, 88.50, NULL, 1), (1003, NULL, 399.00, 备注A, 0), (1004, 2024-02-28 09:15:00, 12.00, , 1), (1005, NULL, 55.00, NULL, 0);四种排序写法的实际输出差异看这张表最清楚排序写法结果顺序按pay_time说明ORDER BY pay_time ASCNULL,NULL, 02-28, 03-01, 03-02NULL 被当作最小值置顶ORDER BY pay_time DESC03-02, 03-01, 02-28,NULL,NULL降序时 NULL 自然沉底ORDER BY pay_time IS NULL, pay_time ASC02-28, 03-01, 03-02,NULL,NULL表达式先按 0/1 分组NULL 沉底ORDER BY IFNULL(pay_time, 9999-12-31) ASC02-28, 03-01, 03-02,NULL,NULL用兜底常量把 NULL 顶到最大从表里能读出一个很实用的推论如果你的业务需求恰好是“降序 NULL 排最后”那什么都不用改DESC天生满足。真正需要动手的是升序场景以及多列混合升降序的场景。这个判断很重要因为很多团队一听到“空值排最后”就无脑加表达式结果给本来能走索引的查询平白加了一次 filesort。1.3 MySQL 为什么不提供 NULLS LAST 关键字用过别的数据库的同学第一反应通常是写ORDER BY pay_time ASC NULLS LAST然后在 MySQL 里得到一个语法错误。原因在于NULLS FIRST / NULLS LAST属于 SQL 标准里较晚被广泛采纳的可选特性MySQL 的ORDER BY语法一直只支持ASC / DESC加上可选的ORDER BY索引提示没有把空值位置独立出来做成语法糖。这个设计差异带来的连锁反应是同一份业务 SQL 从别的库迁过来排序结果可能悄悄变了。迁库的时候大家注意力都在类型、字符集、自增主键上很少有人专门核对“某个允许为 NULL 的列在升序列表里的位置”。我建议在迁移核对清单里加一条把所有ORDER BY后跟的列逐个确认是否NULL可用、是否在两端结果集上做了对比。既然没有语法糖只能在表达式层面自己造。下一节就把几套能用的写法拆开讲重点说清每种写法在什么场景下更合适、会带来什么代价。2. 把 NULL 压到末尾的四种写法与选型这一节的核心是“怎么写”。但我想强调一点排序结果正确只是及格线能不能不给数据库添堵才是分水岭。同一个需求用表达式排序和用兜底常量排序执行计划可能完全不同在数据量上来之后差距会被放大到秒级。所以下面每种写法我都会同时给出“怎么写”和“代价在哪”。2.1 表达式排序法最通用的一招最推荐、也是最通用的一招是在ORDER BY里加一个布尔表达式SELECT id, user_id, pay_time, amount FROM t_order ORDER BY pay_time IS NULL, pay_time ASC;原理很好理解pay_time IS NULL的结果是 0 或 1NULL的行得到 1有值的行得到 0。MySQL 先按这个 0/1 排所有有值的行在前NULL行在后同一组内部再按pay_time ASC排真实时间。既保证了空值沉底又保证了非空值的时间顺序正确。想写成“看起来更直白”的版本也行效果等价ORDER BY pay_time IS NOT NULL DESC, pay_time ASC;这行读起来就是“先按是否有值降序排有值的排前面再按时间升序排”。我个人偏向第一种写法因为少一个DESC脑子里的转换步骤少一步。多列场景下这招依然好用只要把每个需要沉底的列都补一个表达式即可SELECT id, pay_time, amount FROM t_order ORDER BY pay_time IS NULL, pay_time ASC, remark IS NULL, remark ASC;注意ORDER BY里一旦出现表达式MySQL 基本无法再用索引直接完成排序会退化成 filesort把结果集取出来再排。小表无所谓大表加深度分页就是事故现场。这部分我在第 3 节会展开。还有一个容易忽略的细节IS NULL表达式返回的是整数排序时会走数值比较不会被列本身的排序规则collation影响所以对字符串列、日期列、数值列一视同仁这点比兜底常量法更省心。2.2 常量兜底法COALESCE 与 IFNULL 的适用边界第二种思路是“把NULL替换成一个足够大的值”让它自然排到最后-- 日期列 ORDER BY IFNULL(pay_time, 9999-12-31 23:59:59) ASC; -- 数值列 ORDER BY COALESCE(amount, 999999999) ASC; -- 字符串列用高位字符兜底 ORDER BY IFNULL(remark, CHAR(0xFFFF)) ASC;这招的优势是写法短、语义直观前端同学看完就能明白在干什么。但它有两个必须留神的坑。第一个坑是兜底值可能和真实值撞车。日期列用9999-12-31基本安全但如果业务里真的有“永久有效”的合同到期时间设成了 9999 年那这条记录就会和NULL行混在一起排序结果取决于第二排序键。字符串用高位 Unicode 字符兜底更危险不同字符集下CHAR(0xFFFF)的行为不一定一致。第二个坑是类型隐式转换。IFNULL(amount, 999999999)里兜底值写成了字符串MySQL 为了比较会把整列往字符串方向或数值方向转换不但可能排错还可能让原本能用的索引彻底失效。兜底常量的类型必须和列类型严格对齐数值列就给数字、日期列就给DATETIME字面量或CAST(9999-12-31 AS DATETIME)。从我的经验看兜底法更适合展示层的一次性查询比如后台导出、报表拉取对外的分页列表接口我更倾向用表达式排序法语义更干净后期加索引优化时也有余地。2.3 CASE WHEN 与窗口函数复杂业务下的兜底写法当排序规则依赖多个字段的组合判断时CASE WHEN是表达力最强的选择。举个例子订单列表要求“待支付排最前、已支付按支付时间升序、支付时间为空的排在本组末尾”SELECT id, status, pay_time, amount FROM t_order ORDER BY CASE status WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE 2 END, CASE WHEN status 1 THEN (pay_time IS NULL) ELSE 0 END, pay_time ASC, id DESC;这段 SQL 的读法是先按状态分组已支付组内部再按“支付时间是否为空”分成两段非空的按时间升序最后用id DESC兜底保证顺序稳定。CASE WHEN的优势是能把业务规则原样翻译成 SQL缺点是执行计划几乎一定带 filesort而且可读性随分支增多迅速下降。窗口函数在 MySQL 8.0 里也能派上用场尤其是需要“组内编号”的场景SELECT * FROM ( SELECT id, user_id, pay_time, ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY pay_time IS NULL, pay_time ASC ) AS rn FROM t_order ) t WHERE rn 3;这段是“取每个用户最近的三笔有支付时间的订单空值不占位”。窗口函数内部的ORDER BY遵循的规则和普通ORDER BY完全一致——NULL依然最小所以照样需要那个IS NULL表达式。别以为换了个写法规则就变了。2.4 四种写法横向对比与选型建议把上面几种写法放到一张表里选型时对着看就行写法排序正确性索引友好度可读性推荐场景ORDER BY col IS NULL, col高低必 filesort中对外分页接口的默认选择COALESCE / IFNULL兜底中有撞值风险低高后台导出、一次性报表CASE WHEN高低低多字段复合业务规则ORDER BY col DESC利用降序语义高高可走索引高需求本身就允许倒序时首选最后一行值得多说一句。如果产品需求只是“空的别挡在前面”而不是“必须有值的时间必须升序”那把排序方向反过来往往是最省事、性能最好的做法。我曾经把一个“按创建时间升序、空的排最后”的列表和产品沟通改成“默认按创建时间倒序”一次改动省掉了 filesort接口 P99 从 400ms 掉到 30ms。技术选型有时候不是纯技术问题先想清楚需求本质能省一半力气。3. 排序性能索引、filesort 与缓冲区的连锁反应结果对了接下来要盯的是代价。ORDER BY加表达式这件事对执行计划的影响远比大多数人想象的大尤其是在有WHERE条件、有LIMIT、有深分页的组合场景下。这一节我会用真实的EXPLAIN输出对照把“为什么慢了”讲清楚。3.1 加了表达式之后索引为什么就用不上了先看没加表达式的版本EXPLAIN SELECT id, pay_time, amount FROM t_order WHERE pay_time 2024-01-01 ORDER BY pay_time ASC LIMIT 20;输出大致是这样------------------------------------------------------------------------------------ | id | type | possible_keys | key | rows | Extra | ------------------------------------------------------------------------------------ | 1 | range | idx_pay_time | idx_pay_time | 1024 | Using where; Using index | ------------------------------------------------------------------------------------注意Extra里没有Using filesort。MySQL 走的是idx_pay_time索引的范围扫描B 树的叶子节点本身就是按pay_time有序排列的顺着扫出来的结果天然有序LIMIT 20扫够 20 条就停非常快。再看加了IS NULL表达式的版本EXPLAIN SELECT id, pay_time, amount FROM t_order WHERE pay_time 2024-01-01 ORDER BY pay_time IS NULL, pay_time ASC LIMIT 20;------------------------------------------------------------------------------------ | id | type | possible_keys | key | rows | Extra | ------------------------------------------------------------------------------------ | 1 | range | idx_pay_time | idx_pay_time | 1024 | Using where; Using filesort | ------------------------------------------------------------------------------------Using filesort出现了。原因不复杂索引里存的是pay_time的值没有存pay_time IS NULL这个表达式的结果所以 MySQL 无法靠索引顺序满足新的排序要求只能把符合条件的行全部取出来放进排序缓冲区重排排完再取前 20 条。注意这里还有个容易误判的点如果WHERE条件里已经限定了pay_time 2024-01-01那些NULL行其实根本不会进入结果集这时候IS NULL表达式是纯多余的白白引入一次 filesort。写法上要按WHERE的实际过滤情况来判断别条件反射地加。如果确实需要这列既能高效过滤又能把NULL排最后MySQL 8.0 提供了函数索引这条出路ALTER TABLE t_order ADD COLUMN pay_time_nn DATETIME GENERATED ALWAYS AS (IFNULL(pay_time, 9999-12-31 23:59:59)) STORED, ADD KEY idx_pay_time_nn (pay_time_nn);建完之后直接ORDER BY pay_time_nn ASC就能重新走索引有序扫描。代价是多占一份存储、写入时多一次生成列维护。这个方案适合排序极高频、数据量又大的核心列表普通业务表不建议一上来就上生成列先量一下LIMIT前后的响应时间再决定。3.2 filesort 的两种走法与 sort_buffer_size 的调法既然绕不开 filesort就得知道它是怎么跑的。MySQL 的 filesort 有两种数据组织方式一种是把排序键和查询需要的全部字段一起放进排序缓冲区排完直接回表取数据叫“全字段排序”另一种是只放排序键和行标识排完再按行标识回表捞字段叫“rowid 排序”。选哪种取决于单行数据的长度和缓冲区容量——行太宽、缓冲区放不下时MySQL 会选 rowid 排序来减少内存占用代价是排序完成后要额外做大量随机回表。缓冲区大小由sort_buffer_size控制。这个参数是每个排序连接独占的不是全局共享所以不能无脑调大。我的经验值是普通 OLTP 实例保持默认附近256KB~1MB专门跑报表的从库可以给到 8MB~32MB。判断依据是看状态变量SHOW GLOBAL STATUS LIKE Sort_merge_passes; SHOW GLOBAL STATUS LIKE Sort_scan; SHOW GLOBAL STATUS LIKE Sort_range;Sort_merge_passes增长快说明排序缓冲区不够用数据被切块写到磁盘临时文件再归并这时候适当调大sort_buffer_size收益明显。如果这个值长期不怎么涨就别动它调大了只是白占内存。提示不要为了一个慢查询就把sort_buffer_size全局调到几十兆。高并发下每个排序连接都按这个值分配几十个并发就能把实例内存吃干。另外补充一个版本差异点较老的 MySQL 版本里有一个max_length_for_sort_data参数控制两种排序方式的选择阈值新版本对 filesort 做了打包优化这个参数已经被废弃。所以你在网上看到的很多“调大max_length_for_sort_data让排序变快”的教程放到新版本上根本不生效别照抄。3.3 深分页叠加 NULL 排序必须提前设计的场景真正让NULL排序问题爆发的是深分页。LIMIT 100000, 20这种写法MySQL 必须先把前面 100020 条全部取出来排好再丢掉前 100000 条配合上 filesort代价是双份的。常规优化思路是用游标分页把OFFSET换成“上一页最后一条记录的位置”SELECT id, pay_time, amount FROM t_order WHERE (pay_time 2024-03-01 10:00:00) OR (pay_time 2024-03-01 10:00:00 AND id 10086) ORDER BY pay_time ASC, id ASC LIMIT 20;但这个写法有个前提参与游标比较的列不能为NULL。pay_time xxx在pay_time IS NULL的行上求值结果是NULL条件不成立这些行会被直接过滤掉。也就是说游标分页天然就把NULL行排除在外了。所以真实的选型只有两条路要么在业务上禁止排序列为NULL建表时就NOT NULL DEFAULT一个哨兵值从源头消灭问题要么把NULL行单独拆成一个查询——第一段查有值的部分用游标分页第二段单独把NULL行查出来拼在最后。两者都有代价但都比让LIMIT 100000配 filesort 强。我个人强烈偏向第一条排序字段尽量设计成NOT NULL。用1970-01-01 00:00:00或者0表示“尚未发生”把NULL留给真正的“未知”。这条约定一旦在团队里立住后面所有的排序、比较、聚合、索引问题都会少一大半。4. 排查手册NULL 排序踩坑实录前面讲的是“应该怎么写”这一节讲“写错了怎么查”。我在实际项目里积累了一批症状和对应病因整理成速查表遇到问题对着症状找就行。4.1 诡异结果速查表现象大概率原因处理方式升序列表第一条全是空ORDER BY col ASC且列可空加col IS NULL前置排序键加了IS NULL后还是第一页有空用了IFNULL但兜底值类型和列不一致发生隐式转换检查兜底值类型或改用IS NULL表达式分页翻到第 2 页出现重复数据排序键有大量重复值且没加唯一兜底键MySQL 排序不稳定末尾追加id ASC/DESC排序结果在某些行上共现NULL和把空串当成了NULL只处理了一种查询里同时处理NULLIF(col,)或补充条件明明建了索引还是Using filesortORDER BY里有表达式索引无法满足看WHERE是否能过滤掉NULL或建函数索引同一条 SQL 测试库和生产库顺序不同数据分布不同 排序不稳定补兜底排序键别依赖默认顺序用比较NULL的行数永远是 0NULL NULL的结果是NULL不是真用IS NULL或表里第 3 行和第 6 行本质是同一个问题MySQL 的排序不保证稳定。所谓不稳定就是排序键完全相同的多行在两次执行之间返回的相对顺序可能变化。这在单页查询里看不出来一旦分页就会表现为“第 2 页出现了第 1 页的数据”。解决办法很简单——在ORDER BY的最后永远追加主键ORDER BY pay_time IS NULL, pay_time ASC, id ASC。加了主键之后排序键组合唯一顺序就确定了。这条我建议写进团队的 SQL 规范里比事后排查省太多事。4.2 索引与约束里那些反直觉的 NULL 规则NULL在索引和约束上的行为和很多人的直觉相反顺手记一下能避开不少坑。UNIQUE索引允许多个NULL存在。原因是唯一性判断依赖等值比较而NULL NULL结果是NULL而不是真所以两行NULL不构成“重复”。这个特性经常被利用来实现“软删除”deleted_at为NULL表示未删除同一个业务键可以有无数条NULL记录而删除时间一旦写入就有唯一约束。理解这一点之后就不会再奇怪为什么唯一索引没拦住重复数据。COUNT(col)会跳过NULLCOUNT(*)不会。做统计时如果两者结果对不上先去看列里有没有NULL别急着怀疑数据丢了。同理SUM、AVG、MAX、MIN都忽略NULLAVG的分母是非NULL行数而不是总行数这是报表数字“算不平”的经典来源。GROUP BY会把所有NULL归到同一组而ORDER BY里NULL又是最小。这两条规则叠加起来在做“按某列分组统计、组名升序展示”的报表时会出现一个没有名字的分组排在最前面。如果业务上不接受记得在展示层把这一组重命名为“未分类”。CONCAT遇到任意一个NULL参数直接返回NULL而CONCAT_WS会跳过NULL参数。拼接展示字段的时候用后者更安全省得因为一个字段为空导致整行拼接结果变成NULL。4.3 几条我个人沉淀下来的实操心得第一条建表时就想清楚这个字段该不该允许NULL。这不是排序问题是数据建模问题。我现在的习惯是凡是会出现在ORDER BY、WHERE范围比较、GROUP BY里的列一律NOT NULL加一个语义明确的默认值。真正表示“未知”的字段才用NULL并且保证它不参与排序。这个约定推行下去之后团队里关于排序错位的工单基本归零。第二条改排序 SQL 之前先在两端边界数据上看结果。不是看中间那几十条而是专门构造几条NULL、、0、极值的数据看它们分别落在哪。我在做代码评审时有个固定动作就是扫一眼ORDER BY后面的列凡是允许为空的直接问一句“空值排哪”。这一问能拦住大部分线上问题。第三条别迷信“我们数据库里没有 NULL”。只要列是可空的早晚会有NULL进来——历史数据导入、上游接口漏字段、LEFT JOIN没匹配上三件事随便中一件就够了。LEFT JOIN尤其隐蔽主表字段明明NOT NULL但 join 之后取的是从表字段没匹配上就是NULL排序立刻乱。这种情况用COALESCE在查询里兜一下比改表结构更现实。第四条接口返回的排序规则要写进文档。前端做二次排序、做“加载更多”的时候如果不知道后端是按什么顺序返回的很容易出现拼接后顺序错乱。把“空值排在最后、同值按 id 升序”这类规则明确写出来比出问题后再解释省太多沟通成本。最后再分享一个排查小技巧当你怀疑某个列表的顺序不对又说不清是哪一步出的问题把完整 SQL 里的ORDER BY部分单独拿出来用SELECT把排序键表达式一并查出来看。比如SELECT id, pay_time, pay_time IS NULL AS is_null_flag FROM t_order ORDER BY pay_time IS NULL, pay_time ASC LIMIT 20;把is_null_flag这列打印出来一眼就能看到 MySQL 眼里的排序分组到底是不是你以为的那样。这个动作花不了十秒但能省掉半小时的抓耳挠腮。