ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > MySQL > 第72期 NULL 字段索引会不会失效？

第72期 NULL 字段索引会不会失效？

原创 MySQL 作者：pxbibm 时间：2026-07-17 16:22:06 0 删除 编辑

如果说性能优化是数据库技术中的明珠，那么索引无疑是其中最耀眼的一颗，特别是 OLTP业务数据库。掌握了索引技术，基本上性能就不会有太大的问题。 有时我们创建了字段上的索引，但是通过执行计划却发现索引并没有被使用，还是会 使用全表扫描。随着表上数据量的增长，性能会越来越差。如果不能查明原因，就只能盲 目的靠不断扩容硬件来缓解（不是解决）这类问题，投资巨大，收益甚微。有时实在没办 法，只能通过定期清空表来解决。 对于大表来说，能否使用到索引，效率上可能会相差上万倍（表越大，性能差距就越 大）。下面我们就列举一些有索引，但是没有被使用的情况，主要分享一下关于null值的情况，并给出对应的解决方法。

MySQL NULL 字段索引会不会失效？分场景讲清楚（5.7/8.0 通用）

一、核心结论

  • 索引本身可以正常存储 NULL，不会因为字段有 NULL 就整个索引失效；

  • 失效只出现在 特定查询写法，不是字段存 NULL 导致索引废掉；

  • B+Tree 索引中 NULL 统一放在索引最左侧，MySQL 完全支持索引扫描 NULL 值。

二、场景 1：查询 IS NULL / IS NOT NULL → 能走索引（有效）

建表测试：
CREATE TABLE t(
  id INT PRIMARY KEY,
  phone VARCHAR(20),
  KEY idx_phone(phone));
  -- 插入大量数据，包含很多phone=null
  	insert into t value('1','1380000001');
	insert into t value('2','1380000002');
	insert into t value('3','1380000003');
	insert into t value('4','1380000004');
	insert into t value('5','1380000005');
	insert into t value('6','1380000006');
	insert into t value('7','1380000007');
	insert into t value('8','1380000008');
	insert into t value('9',NULL);
	insert into t value('10',NULL);
	insert into t value('11',NULL);
	insert into t value('12',NULL);

1）IS NULL 走索引

EXPLAIN SELECT * FROM t WHERE phone IS NULL;

type=ref/range，使用 idx_phone， 不会全表扫描。

2）IS NOT NULL 也能走索引

EXPLAIN SELECT * FROM t WHERE phone IS NOT NULL;

误区纠正：很多人以为 NULL 不走索引，这是老版本谣言，MySQL5.6 之后完全支持 NULL 索引检索。

三、场景 2：查询用 =、 != 匹配 NULL → 索引失效（重点）

错误 1：= NULL（语法逻辑错误）

SELECT * FROM t WHERE phone = NULL;

SQL 逻辑：
  • 执行计划：type=ALL 全表扫描，索引完全不用。

错误 2：!= NULL

SELECT * FROM t WHERE phone != NULL;
同样不走索引，必须改用 IS NOT NULL。

四、场景 3：联合索引中存在 NULL，最左匹配是否失效？

不会失效，遵循最左前缀规则。索引： KEY idx_a_b (a,b)
WHERE a=1;                -- 走索引
WHERE a=1 AND b IS NULL;  -- 走索引
WHERE a IS NULL AND b=2;  -- 走索引
WHERE b=2;                -- 丢失最前列，不走索引（和NULL无关）
NULL 不破坏联合索引最左匹配逻辑。

五、场景 4：NOT IN 包含 NULL → 查询直接返回空，看似索引失效

SELECT * FROM t WHERE id NOT IN (1,2,NULL);
原理： NOT IN 内存在 NULL，逻辑运算结果永未知，数据库直接返回空结果，不会走索引，属于逻辑陷阱，不是索引不支持 NULL。

六、场景 5：字段大量 NULL，优化器主动放弃索引（统计信息代价判断）

表 100 万行，99 万行 phone 都是 NULL，仅 1 万行有值：
SELECT * FROM t WHERE phone IS NULL;
MySQL 优化器计算：符合条件数据占比极高，索引回表代价 > 全表扫描， 主动选择全表扫描。
  • 不是索引失效，是优化器认为全表更快；
  • 解决： FORCE INDEX(idx_phone) 强制走索引。

七、场景 6：分组 / 排序 NULL

SELECT phone,COUNT(*) FROM t GROUP BY phone;ORDER BY phone;

索引有序存储 NULL，GROUP BY、ORDER BY 均可利用索引避免临时表 / 文件排序。

八、生产实操建议

  1. 查询 NULL 必须用 IS NULL / IS NOT NULL，禁止 = NULL / != NULL；

  2. 若业务 90% 数据都是 NULL，该字段单独建索引收益极低，可考虑：

    给 NULL 填充默认占位值（如 unknown），提升索引选择性；
  3. 联合索引第一列允许为 NULL，不破坏最左匹配；
  4. 遇到 IS NULL 不走索引，先看 explain type：
    type=ALL：大概率符合条件数据太多，优化器放弃索引；
    检查统计信息： ANALYZE TABLE t; 更新统计后重新执行。

简短总结

  • 字段存 NULL 不会让索引永失效；

  • IS NULL / IS NOT NULL 正常走索引；

  • = NULL / != NULL / NOT IN(...,NULL) 会不走索引；

  • NULL 过多导致过滤性差，优化器可能主动放弃索引扫描。


Oracle NULL 与索引完整结论（B 树索引为主，区分普通单列、联合、唯一、位图索引）

核心总览

  1. 单列 B 树索引：不会存储全 NULL 行一条记录索引字段全部为 NULL → 这条记录不会写入 B 树索引。

  2. 联合复合 B 树索引：只要至少一列非 NULL，整条索引条目会保存；所有列同时 NULL 才不存。

  3. 查询写法不同，会出现「看似索引失效」，分场景逐一说明。

一、单列 B 树索引（最常踩坑）

表 t(id,email)，单列索引 idx_email(email)

1. where email IS NULL

索引失效，全表扫描原因：所有 email=null 的数据根本没有进索引，索引里找不到这些行，只能全表扫描。
explain plan for select * from t where email is null;
-- PLAN_TABLE：TABLE ACCESS FULL

2. where email IS NOT NULL

正常走索引（INDEX RANGE SCAN）非空值都存在索引里，优化器优先走索引。

3. where email = 'xxx'

正常走索引，和 NULL 无关。

解决单列 IS NULL 无法走索引的方案

  • 建立 反向索引 / 位图索引（位图索引会存 NULL）

  • 建立函数索引，把 NULL 转为固定值：

create index idx_email_nn on t(nvl(email,'#NULL#'));-- 查询时对齐写法
select * from t where nvl(email,'#NULL#') = '#NULL#';
  • 给字段设置默认值，杜绝业务 NULL。

二、复合（联合）B 树索引（重点，很多人混淆）

索引 idx_a_b (a, b)

规则： 只要索引中任意一列不为 NULL，该行就会写入索引；只有 a AND b 同时为 NULL 才不存。

场景 1：where a is null and b = 10

能走索引！因为 b 不为 NULL，这条记录在索引内存在，可通过索引定位。

场景 2：where a is null and b is null

索引无这条数据，只能全表扫描。

场景 3：where b=10（跳过前导列 a）

和 NULL 无关，单纯违反最左前缀，不走索引。

示例

create table t(a number,b varchar2(20));
create index idx_ab on t(a,b);
-- 能走索引
select * from t where a is null and b='test';
-- 全表扫描
select * from t where a is null and b is null;

三、唯一索引 Unique Index

和普通 B 树规则一致：

  • 单列唯一索引：null 不存入索引， col is null 全表扫；

  • 复合唯一索引：字段不同时 NULL 就存入索引；

  • 允许多条 NULL（唯一约束仅限制非空值唯一性）。

四、位图索引（特殊，完全支持 NULL 检索）

位图索引 会存储 NULL， where col is null 直接走位图索引，无失效问题。

适用场景：字段基数低（性别、状态、少量枚举），不适合 DML 频繁更新表。

缺点：并发 DML 会锁整张表，OLTP 业务慎用。

五、其他导致 NULL 查询不走索引的常见误区

  1. where col = null

     Oracle 中 null = 任意值 结果 UNKNOWN，无返回数据，必然全表扫描，必须用 is null 。

  2.not in (包含null)
select * from t where id not in (1,2,null);
逻辑直接返回空结果，优化器放弃索引。


   3.NULL 数据量占比极高哪怕索引能检索，若满足条件数据超表 30%，优化器自动选择全表扫描，不是索引失效，是代价判断。

六、MySQL vs Oracle NULL 索引核心区别（对比记忆）


特性 MySQL B + 树 Oracle B 树
单列 NULL 是否存入索引 存入，IS NULL 走索引 不存入，IS NULL 全表扫
复合索引全 NULL 行 存入 不存入
IS NULL 查询 天然支持索引 单列失效，复合部分场景可用
位图索引 无 支持 NULL 检索

七、生产优化方案汇总

  1. 单列字段经常查 is null：

    低基数：位图索引
  2. 高基数：NVL 函数索引

  3. 联合索引尽量保证查询条件至少一列非空，避免双 NULL 条件；

  4. 业务层设计：用占位值（N/A、0、unknown）替代 NULL，从根源规避问题；
  5. 禁止使用 = null，统一标准 is null / is not null。



来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/12798004/viewspace-3176183/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 第71期 通过SQL Monitor 实时执行监控抓取TOP N慢SQL
下一篇： 第73期 MySQL一条SELECT从回车到返回结果内部经历了什么
请登录后发表评论 登录
全部评论
pxbibm
专家姓名：彭小波
【DBAplus社群】联合发起人。ACOUG核心成员，Oracle用户组年轻专家。熟悉Oracle的体系结构，擅长Oracle数据库架构规划、SQL优化、OWI方面性能的优化。曾服务于航天、航空、机车、大型制造行业数据库的维护以及系统开发工作,开发过基于Oracle数据库的大型ERP系统。
  • 博文量
    341
  • 访问量
    2469360

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593