MySQL字段为NULL索引是否失效：测试与最佳实践

实验环境与准备

为了排除不同版本的差异，本次测试基于 MySQL 8.0 及以上版本（版本较低的MySQL可能在B+树索引实现上存在细微逻辑差异）。我们将创建一个包含空字符串和数值NULL混合数据的测试表，并分别观察普通单列索引和联合索引的行为。

第1步：创建含NULL字段的测试表

首先，我们建立测试表 sc_base_color，其中包含主键以及两个普通索引字段：name 和 group_num。这两个字段均默认设置为 NULL，用于模拟生产环境中常见的未初始化或缺失数据场景。

CREATE TABLE `sc_base_color` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `group_num` bigint DEFAULT NULL COMMENT '颜色代码',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '颜色名称',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_name` (`name`),
  KEY `idx_group_num` (`group_num`)
) ENGINE=InnoDB AUTO_INCREMENT=574 DEFAULT CHARSET=utf8mb3 COMMENT='颜色';

第2步：插入包含NULL值的测试数据

接下来，我们向表中插入包含实际值和NULL值的数据。为了更清晰地观察结果，我们特意构造了不同比例的NULL数据：

INSERT INTO `sc_base_color`(`id`, `group_num`, `name`) VALUES (30, 1, '米黄'); -- 正常值
INSERT INTO `sc_base_color`(`id`, `group_num`, `name`) VALUES (31, 1, '黑色'); -- 正常值
INSERT INTO `sc_base_color`(`id`, `group_num`, `name`) VALUES (32, 1, NULL);   -- name为NULL
INSERT INTO `sc_base_color`(`id`, `group_num`, `name`) VALUES (36, NULL, NULL); -- 两者均为NULL
-- ...此处省略部分中间数据，核心包含约一半为NULL的记录以测试覆盖率...

普通索引在NULL值下的表现

我们将通过不同类型的查询语句，结合 EXPLAIN 关键字来分析执行计划，从而判断索引是否被引擎（Optimizer）选用。

第3步：等值查询（=）测试

在使用 = 符号精确匹配非NULL值时，即便表中其他行存在NULL值，索引依然正常工作。同样，直接查询值为 NULL 的行时，MySQL 也能利用 B+ 树的索引结构快速定位。

EXPLAIN SELECT * FROM sc_base_color WHERE name = '米黄';
EXPLAIN SELECT * FROM sc_base_color WHERE group_num = 1;

根据测试截图结果显示，这两种查询均成功触发了索引查找过程。

第4步：范围查询（大于/小于）测试

当使用 > 或 < 等操作符进行范围扫描时，观察其执行计划：由于B+树是按照特定顺序排列的，这些操作依然可以高效地通过索引获取相邻节点。在此类操作中，NULL值通常不参与比较或被单独处理，但索引机制本身并未因此失效。

EXPLAIN SELECT * FROM sc_base_color WHERE name > '米黄';
EXPLAIN SELECT * FROM sc_base_color WHERE name < '米黄';

结果显示，范围区间内的数据记录能通过索引快速检索。

第5步：特殊判定查询（!=, IN, ISNULL）测试

这是最容易引发"索引失效"误解的地方。我们通过以下 SQL 进行对比：

-- 不等于查询
EXPLAIN SELECT * FROM sc_base_color WHERE group_num != 1;
EXPLAIN SELECT * FROM sc_base_color WHERE group_num NOT IN (1);
-- 判空查询
EXPLAIN SELECT * FROM sc_base_color WHERE ISNULL(group_num);
EXPLAIN SELECT * FROM sc_base_color WHERE !ISNULL(group_num);

测试结果往往因优化器策略而异。通常情况下，对于全量更新或范围极大的不等式，优化器可能会选择全表扫描；但在我们的测试数据集下，它仍然展示了走索引的迹象。

组合索引在NULL值下的表现

为了进一步探究复合索引的情况，我们需要先清除原有的独立索引并建立联合索引。

第6步：构建组合索引并验证最左匹配原则

删除旧索引后添加新索引：(group_num, name)。

ALTER TABLE sc_base_color DROP INDEX idx_group_num;
ALTER TABLE sc_base_color DROP INDEX idx_name;
ALTER TABLE sc_base_color ADD INDEX idx_group_num_idx_name (group_num, name);

接着再次运行范围查询和等值查询。结果证实，组合索引依然有效。需要注意的是，如果在查询中跳过了前导列 group_num 直接对 name 进行等值查询，则无法利用该联合索引（违背最左前缀原则）。

EXPLAIN SELECT * FROM sc_base_color WHERE group_num = 1 AND name = '米黄';

如下截图所示，正确的查询条件成功命中了组合索引。

结论与设计建议

经过多轮 EXPLAIN 分析得出结论：**MySQL中的NULL值并不会导致普通索引或组合索引绝对失效**。但是，在实际生产环境中，依赖NULL值的索引命中率会受到数据稀疏程度和查询条件性质的显著影响（例如极高频的IS NULL检索可能导致优化器放弃索引转而全表扫描）。

鉴于此，良好的数据库设计规范应尽量避免在关键业务字段中使用NULL值，以减少潜在的优化器误判风险：

1. 整型字段（INT/BIGINT）：建议设置默认值为 -1 或 0，代替NULL来表示未知或无效状态。
2. 字符串字段（VARCHAR）：建议使用空字符串 '' 作为默认值，而非NULL。
3. 数值类型（DECIMAL）：建议将默认值设为 0。

额外风险提示：
除了索引问题外，在设计数据库时还需警惕NULL值引发的其他逻辑陷阱：

• 聚合函数过滤：统计函数如 COUNT(字段), SUM(字段) 会自动忽略值为NULL的行，若业务强依赖所有行数统计，需改用 COUNT(*)。
• 比较运算过滤：任何涉及 >, <, = 的比较运算，当与NULL参与交互时，返回值恒为UNKNOWN（可视为False），导致该行记录不会被筛选出来。
• 分组合并风险：在进行 GROUP BY 时，所有的NULL值会被视为相等的键并合并为一组。如果这种随机合并不符合业务预期，可考虑用生成固定占位符（如UUID）的方式解决。

以上就是对MySQL字段为NULL索引是否会失效及数据库设计的详细内容，更多关于MySQL索引失效的资料请关注本站其它相关文章！