MySQL/MariaDB NULL 值查询优化：避开索引失效的坑

在 MySQL 或 MariaDB 开发中，NULL值是处理 “缺失数据” 的常用方式，但包含IS NULL的查询往往隐藏着性能隐患。尤其当多个字段同时使用IS NULL条件时，查询可能从毫秒级骤降至分钟级。本文基于 350 万行数据的实测案例，拆解 NULL 值与索引的交互逻辑，揭秘性能暴跌的根源，并提供可落地的优化方案。

一、实测现象：单字段 NULL 查询快，多字段 NULL 查询慢

先看三组对比实验（测试表含 350 万行数据，建有联合索引idx_a_b(a,b)和单字段索引idx_b(b)）：

实验 1：单字段含 IS NULL 条件（快）

 

-- 条件：a=2或a为NULL，且b=5
SELECT COUNT(*) FROM t WHERE (a=2 OR a IS NULL) AND (b=5);
-- 执行时间：0.01秒-- 条件：a=2，且b=5或b为NULL
SELECT COUNT(*) FROM t WHERE (a=2) AND (b=5 OR b IS NULL);
-- 执行时间：0.01秒

 

 

结论：单个字段搭配IS NULL时，查询仍能高效利用索引，执行速度极快。

实验 2：多字段同时含 IS NULL 条件（慢到离谱）

 

-- 条件：a=2或a为NULL，且b=5或b为NULL
SELECT COUNT(*) FROM t WHERE (a=2 OR a IS NULL) AND (b=5 OR b IS NULL);
-- 执行时间：1分21.32秒（81秒），结果返回146万行

 

 

结论：仅增加一个字段的IS NULL条件，查询速度暴跌 8000 倍，从毫秒级沦为分钟级。

实验 3：用默认值替代 NULL（恢复快速）

将表中所有NULL值替换为0（逻辑上代表 “无数据”），重复实验 2 的查询逻辑：

 

-- 条件：a=2或a=0（替代a IS NULL），且b=5或b=0（替代b IS NULL）
SELECT COUNT(*) FROM t WHERE (a=2 OR a=0) AND (b=5 OR b=0);
-- 执行时间：1.93秒，结果返回245万行（比实验2多100万行）

 

 

结论：用具体默认值替代NULL后，即使返回数据量更多，查询速度仍大幅提升，仅需 1.93 秒。

二、根源解析：索引优化器的 “ref_or_null” 限制

为什么多字段IS NULL会导致性能灾难？核心原因是 MySQL 索引优化器对IS NULL的处理机制存在限制，关键在于ref_or_null检索类型的局限性。

1. 单字段 IS NULL：ref_or_null 高效检索

当查询中仅一个字段含IS NULL条件时，优化器会使用ref_or_null检索类型。这种类型能利用索引快速定位 “指定值 + NULL 值” 的记录，本质是对索引的高效扫描，因此查询速度快。

2. 多字段 IS NULL：ref_or_null 无法跨列生效

MySQL 的ref_or_null检索类型不支持同时作用于多个索引列。当两个字段都包含IS NULL条件时：

 

• 优化器无法使用联合索引idx_a_b进行跨列优化；
• 只能选择单字段索引idx_b，先筛选b=5或b IS NULL的记录，再通过WHERE子句过滤a字段的条件；
• 最终导致 “索引失效 + 全表扫描” 的低效执行计划，这也是实验 2 耗时 81 秒的核心原因。

3. EXPLAIN 验证：执行计划的差异

通过EXPLAIN对比实验 2 和实验 3 的执行计划，能直观看到问题所在：

实验 2（多字段 IS NULL）的执行计划：

 

EXPLAIN SELECT COUNT(*) FROM t WHERE (a=2 OR a IS NULL) AND (b=5 OR b IS NULL) G

 

 

关键输出：

 

• type: ref_or_null（仅支持单字段）；
• key: idx_b（仅使用单字段索引idx_b）；
• rows: 1815359（扫描 181 万行，需二次过滤a字段）；
• Extra: Using where; Using index（需额外过滤条件，未充分利用联合索引）。

实验 3（默认值替代 NULL）的执行计划：

EXPLAIN SELECT COUNT(*) FROM t WHERE (a=2 OR a=0) AND (b=5 OR b=0) G

 

 

关键输出：

 

• type: range（范围检索，支持多字段）；
• key: idx_a_b（使用联合索引idx_a_b）；
• rows: 1908763（扫描 190 万行，但无需二次过滤）；
• Extra: Using where; Using index（仅通过索引即可完成查询，无需回表）。

 

核心差异：range检索类型支持联合索引的多字段优化，而ref_or_null仅支持单字段，多字段时会导致索引选择失效。

三、优化方案：从根源避免 NULL 值的性能坑

针对多字段IS NULL的性能问题，最优解决方案是 “避免使用 NULL 值”，用具体默认值替代；若必须使用 NULL，则需通过查询改写优化。

1. 方案 1：用默认值替代 NULL（推荐，根治问题）

这是最彻底的优化方式，从数据存储层面消除 NULL 值，从根源避免索引优化限制。

具体做法：

• 设计表结构时，为可能 “无数据” 的字段设置默认值：
  • 数值型字段（如a、b）：默认值0；
  • 字符串型字段（如status）：默认值空字符串''；
  • 日期型字段（如create_time）：默认值1970-01-01 00:00:00。
• 应用逻辑中，用默认值代表 “无数据”，而非存储 NULL。

优势：

• 完全兼容range检索，联合索引能充分发挥作用；
• 避免ref_or_null的限制，查询性能稳定；
• 无需修改查询语句，仅需调整数据存储逻辑。

2. 方案 2：查询改写（适用于无法修改表结构场景）

若已存在大量 NULL 数据，无法直接修改表结构，可通过拆分查询、使用UNION改写，让优化器能利用索引：

改写示例（针对实验 2 的慢查询）：

-- 原慢查询
SELECT COUNT(*) FROM t WHERE (a=2 OR a IS NULL) AND (b=5 OR b IS NULL);-- 改写为4个查询的UNION（覆盖所有组合）
SELECT COUNT(*) FROM t WHERE a=2 AND b=5
UNION ALL
SELECT COUNT(*) FROM t WHERE a=2 AND b IS NULL
UNION ALL
SELECT COUNT(*) FROM t WHERE a IS NULL AND b=5
UNION ALL
SELECT COUNT(*) FROM t WHERE a IS NULL AND b IS NULL;-- 最终求和（可在应用层或SQL中完成）
SELECT SUM(total) FROM (-- 上述4个查询的UNION结果
) AS temp;

 

 

原理：将多字段IS NULL的复杂条件，拆分为多个单字段IS NULL的简单查询，每个子查询都能利用ref_or_null高效检索，再通过UNION合并结果，总体性能远优于原查询。

3. 方案 3：特殊场景优化（含范围条件时）

若查询中包含>、<等范围条件，即使字段含IS NULL，优化器仍可能使用range检索，无需过度担心：

 

-- 含范围条件b>5，即使a含IS NULL，仍能使用联合索引
SELECT COUNT(*) FROM t WHERE (a=2000 OR a IS NULL) AND (b>5 OR b IS NULL);
-- 执行计划：type=range，key=idx_a_b，执行速度快

 

 

结论：ref_or_null的限制仅在 “多字段同时含 IS NULL + 等值条件” 时触发，含范围条件时优化器会自动切换为range检索，性能不受影响。

四、避坑指南：NULL 值使用的 3 个原则

1. 优先用默认值替代 NULL：表设计阶段避免字段允许 NULL，用具体默认值（0、''、1970-01-01）代表 “无数据”，从根源消除性能隐患；
2. 单字段 IS NULL 可放心用：仅单个字段含 IS NULL 条件时，性能不受影响，无需刻意避免；
3. 多字段 IS NULL 必优化：若业务必须使用多字段 IS NULL 查询，要么改写查询语句，要么替换 NULL 为默认值，切勿直接使用OR a IS NULL AND OR b IS NULL的写法。

总结

MySQL/MariaDB 中，NULL 值的性能坑不在 “单字段使用”，而在 “多字段同时使用”。核心原因是ref_or_null检索类型无法跨列优化，导致联合索引失效，触发全表扫描。

 

优化的关键是 “避开多字段 IS NULL 的组合”：优先选择用默认值替代 NULL（根治方案），其次通过UNION拆分查询（临时方案）。掌握这一原则，就能避免 NULL 值导致的查询性能暴跌，让索引始终发挥高效作用