mysql字段为NULL索引是否会失效实例详解

吾爱主题阅读：209 2024-04-01 23:25:47 评论：0

项目场景：

很多博客说mysql在字段中创建普通索引，如果该索引中的数据存在null值是不走索引这个结论是错误的，不过尽量还是设置默认值。（版本8.0低于这个版本可能结果不一致）

1、创建表sc_base_color，其中普通索引为 “name，group_num”，这里暂时不测组合索引，下面再测试。

1 2 3 4 5 6 7 8 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、初始化测试数据

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 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 ); INSERT INTO `sc_base_color`(`id`, `group_num`, ` name `) VALUES (33, 1, '白色' ); INSERT INTO `sc_base_color`(`id`, `group_num`, ` name `) VALUES (34, 1, NULL ); INSERT INTO `sc_base_color`(`id`, `group_num`, ` name `) VALUES (35, 1, '绿色' ); INSERT INTO `sc_base_color`(`id`, `group_num`, ` name `) VALUES (36, NULL , NULL ); INSERT INTO `sc_base_color`(`id`, `group_num`, ` name `) VALUES (37, NULL , NULL ); INSERT INTO `sc_base_color`(`id`, `group_num`, ` name `) VALUES (38, NULL , NULL ); INSERT INTO `sc_base_color`(`id`, `group_num`, ` name `) VALUES (39, NULL , NULL ); INSERT INTO `sc_base_color`(`id`, `group_num`, ` name `) VALUES (40, NULL , '紫色' ); INSERT INTO `sc_base_color`(`id`, `group_num`, ` name `) VALUES (41, NULL , NULL ); INSERT INTO `sc_base_color`(`id`, `group_num`, ` name `) VALUES (42, NULL , NULL ); INSERT INTO `sc_base_color`(`id`, `group_num`, ` name `) VALUES (43, NULL , NULL ); INSERT INTO `sc_base_color`(`id`, `group_num`, ` name `) VALUES (44, NULL , '蓝色' ); INSERT INTO `sc_base_color`(`id`, `group_num`, ` name `) VALUES (45, NULL , NULL ); INSERT INTO `sc_base_color`(`id`, `group_num`, ` name `) VALUES (46, NULL , NULL ); INSERT INTO `sc_base_color`(`id`, `group_num`, ` name `) VALUES (47, 2, '米蓝色' ); INSERT INTO `sc_base_color`(`id`, `group_num`, ` name `) VALUES (48, 2, NULL ); INSERT INTO `sc_base_color`(`id`, `group_num`, ` name `) VALUES (49, 2, NULL ); INSERT INTO `sc_base_color`(`id`, `group_num`, ` name `) VALUES (50, 2, '黑红色' );

3、测试普通索引为NULL的情况是否使用了索引

使用 = 查询，测试结果中使用到了索引，其中索引字段的值为“NULL”

1 2	`EXPLAIN` `select` `` `from` `sc_base_color` `where` `name` `=` `'米黄'` `;` `EXPLAIN` `select` `` `from` `sc_base_color` `where` `group_num = 1;`

截图结果，两列数据都存在空，最终走了索引。

使用大于、小于查询

1 2	`EXPLAIN` `select` `` `from` `sc_base_color` `where` `name` `>` `'米黄'` `;` `EXPLAIN` `select` `` `from` `sc_base_color` `where` `name` `<` `'米黄'` `;`

截图结果

使用不等于、not in 、isnull、!isnull查询

1 2 3 4 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);

截图结果

使用isnull、is not null查询

1 2 3 4 # 使用 is not null 可能会导致索引失效，我测试了20条数据，只要 null 值占全部数据的百分之50就不会失效，否则会失效。又测了40条数据，23条数据不会为空，22条为 null 的会为空 EXPLAIN select * from sc_base_color where group_num is not null ; # 使用 is null 也可能会导致索引失效，我测试了20条数据，6数数据不为空不会失效，也就是可能当空的数据占比70%的时候索引会失效。 EXPLAIN select * from sc_base_color where group_num is null ;

由此可以得出结论，字段为空是可以走索引的，但是部分场景可能会失效，尽量还是给默认值。

4、测试组合索引为NULL是否走了索引

先删除普通索引字段，增加组合索引

1 2 3 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 );

测试 = > < 查询结果

1 2 3 4 EXPLAIN select * from sc_base_color where group_num > 1; EXPLAIN select * from sc_base_color where group_num < 1; EXPLAIN select * from sc_base_color where group_num = 1; EXPLAIN select * from sc_base_color where group_num = 1 and name = '米黄' ;