在 MySQL 中，字段包含 NULL 值本身不会直接导致索引失效，但 查询条件中涉及 NULL 的操作可能影响索引的使用，具体取决于数据分布和优化器的选择。以下是详细分析：

一、索引对 NULL 值的处理机制

1. 索引存储 NULL 值

   • 如果字段允许为 NULL 且有索引，NULL 值会被记录在索引中。
   • InnoDB 的 B+Tree 索引将 NULL 视为一个特殊值，与其他非 NULL 值共存于索引结构中。

2. 唯一索引的例外

   • 对于唯一索引（UNIQUE），MySQL 允许插入多个 NULL 值，因为 NULL 被视为“未知值”，彼此不冲突。
   • 例如，唯一索引 UNIQUE(email) 允许多行 email 为 NULL。

二、查询条件中涉及 NULL 的场景

1. IS NULL 或 IS NOT NULL

• 是否使用索引：
  取决于 NULL 值的分布比例。优化器会根据统计信息（如索引基数）决定是否使用索引。

  • 高 NULL 比例：若某列大部分值为 NULL，优化器可能认为全表扫描比索引扫描更快，从而放弃索引。
  • 低 NULL 比例：若 NULL 值较少，优化器可能选择通过索引定位数据。

• 示例：

  -- 假设 `address` 列有索引且 90% 的值为 NULL
  EXPLAIN SELECT * FROM users WHERE address IS NULL;
  

  • 结果：type 列为 ALL（全表扫描），索引未生效。

2. 等值查询（= NULL 或 = value）

• = NULL 无效：
  SQL 标准中 = NULL 会返回 UNKNOWN，应使用 IS NULL。
• 非 NULL 等值查询：

  SELECT * FROM users WHERE email = 'user@example.com';  -- 若 email 有索引且非 NULL，索引生效
  

3. 范围查询或比较操作符

• <, >, BETWEEN：
  若查询条件中包含 NULL 值，可能导致优化器放弃索引。例如：

  -- 假设 `price` 有索引且部分值为 NULL
  SELECT * FROM products WHERE price > 100;  -- NULL 值会被过滤，但索引是否生效取决于非 NULL 值的分布
  

三、数据分布对索引使用的影响

优化器通过统计信息（如 cardinality）评估查询成本。以下场景可能导致索引失效：

1. 高 NULL 比例：
   若某列大部分值为 NULL，优化器认为全表扫描更快。
2. 低区分度：
   即使列非 NULL，但值重复率高（如性别列），优化器也可能放弃索引。

四、验证索引是否生效的方法

使用 EXPLAIN 分析查询计划：

EXPLAIN SELECT * FROM users WHERE address IS NULL;

• 关键字段：
  • type：ref 或 range 表示使用索引，ALL 表示全表扫描。
  • key：显示实际使用的索引。
  • Extra：若显示 Using index condition，表示索引下推（ICP）生效。

五、优化建议

1. 避免在索引列中存储大量 NULL：

   • 如果 NULL 无实际意义，可设置字段为 NOT NULL 并赋予默认值（如空字符串、0）。
   • 例如：ALTER TABLE users MODIFY address VARCHAR(100) NOT NULL DEFAULT '';

2. 覆盖索引优化 IS NULL 查询：

   • 若需频繁查询 IS NULL，可创建覆盖索引包含查询字段，避免回表。

   CREATE INDEX idx_address ON users (address) INCLUDE (name);  -- MySQL 8.0+ 支持 INCLUDE
   

3. 强制使用索引：

   SELECT * FROM users USE INDEX (idx_address) WHERE address IS NULL;
   

4. 定期更新统计信息：

   ANALYZE TABLE users;  -- 更新索引统计信息，帮助优化器更准确决策
   

六、示例分析

1. 数据表结构

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  salary INT,
  bonus INT,  -- 允许 NULL，且 80% 的值为 NULL
  INDEX idx_bonus (bonus)
);

2. 查询场景

-- 查询 bonus 为 NULL 的员工
EXPLAIN SELECT * FROM employees WHERE bonus IS NULL;

• 可能结果：优化器选择全表扫描（type: ALL），因为 NULL 值占比过高。

3. 优化方案

• 方案 1：为 bonus 设置默认值 0，减少 NULL 比例。
• 方案 2：强制使用索引（需测试性能是否提升）：

  SELECT * FROM employees USE INDEX (idx_bonus) WHERE bonus IS NULL;
  

七、总结

• 索引不会因字段存在 NULL 值而失效，但查询条件涉及 NULL 时，优化器可能因数据分布放弃索引。
• 关键因素：NULL 值的比例、查询条件类型、索引设计。
• 优化方向：减少 NULL 值、合理设计索引、利用覆盖索引或统计信息更新。

通过合理设计表结构和索引，可显著提升包含 NULL 值字段的查询性能。