mysql字段为NULL索引会失效吗(mysql,null,开发技术)

项目场景：

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

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

CREATETABLE`sc_base_color`(`id`bigintNOTNULLAUTO_INCREMENT,`group_num`bigintDEFAULTNULLCOMMENT'颜色代码',`name`varchar(255)CHARACTERSETutf8COLLATEutf8_general_ciDEFAULTNULLCOMMENT'颜色名称',PRIMARYKEY(`id`)USINGBTREE,KEY`idx_name`(`name`),KEY`idx_group_num`(`group_num`))ENGINE=InnoDBAUTO_INCREMENT=574DEFAULTCHARSET=utf8mb3COMMENT='颜色';

2、初始化测试数据

INSERTINTO`sc_base_color`(`id`,`group_num`,`name`)VALUES(30,1,'米黄');INSERTINTO`sc_base_color`(`id`,`group_num`,`name`)VALUES(31,1,'黑色');INSERTINTO`sc_base_color`(`id`,`group_num`,`name`)VALUES(32,1,NULL);INSERTINTO`sc_base_color`(`id`,`group_num`,`name`)VALUES(33,1,'白色');INSERTINTO`sc_base_color`(`id`,`group_num`,`name`)VALUES(34,1,NULL);INSERTINTO`sc_base_color`(`id`,`group_num`,`name`)VALUES(35,1,'绿色');INSERTINTO`sc_base_color`(`id`,`group_num`,`name`)VALUES(36,NULL,NULL);INSERTINTO`sc_base_color`(`id`,`group_num`,`name`)VALUES(37,NULL,NULL);INSERTINTO`sc_base_color`(`id`,`group_num`,`name`)VALUES(38,NULL,NULL);INSERTINTO`sc_base_color`(`id`,`group_num`,`name`)VALUES(39,NULL,NULL);INSERTINTO`sc_base_color`(`id`,`group_num`,`name`)VALUES(40,NULL,'紫色');INSERTINTO`sc_base_color`(`id`,`group_num`,`name`)VALUES(41,NULL,NULL);INSERTINTO`sc_base_color`(`id`,`group_num`,`name`)VALUES(42,NULL,NULL);INSERTINTO`sc_base_color`(`id`,`group_num`,`name`)VALUES(43,NULL,NULL);INSERTINTO`sc_base_color`(`id`,`group_num`,`name`)VALUES(44,NULL,'蓝色');INSERTINTO`sc_base_color`(`id`,`group_num`,`name`)VALUES(45,NULL,NULL);INSERTINTO`sc_base_color`(`id`,`group_num`,`name`)VALUES(46,NULL,NULL);INSERTINTO`sc_base_color`(`id`,`group_num`,`name`)VALUES(47,2,'米蓝色');INSERTINTO`sc_base_color`(`id`,`group_num`,`name`)VALUES(48,2,NULL);INSERTINTO`sc_base_color`(`id`,`group_num`,`name`)VALUES(49,2,NULL);INSERTINTO`sc_base_color`(`id`,`group_num`,`name`)VALUES(50,2,'黑红色');

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

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

EXPLAINselect*fromsc_base_colorwherename='米黄';EXPLAINselect*fromsc_base_colorwheregroup_num=1;

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

使用 大于、小于 查询

EXPLAINselect*fromsc_base_colorwherename>'米黄';EXPLAINselect*fromsc_base_colorwherename<'米黄';

截图结果

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

EXPLAINselect*fromsc_base_colorwheregroup_num!=1;EXPLAINselect*fromsc_base_colorwheregroup_numnotin(1);EXPLAINselect*fromsc_base_colorwhereisnull(group_num);EXPLAINselect*fromsc_base_colorwhere!isnull(group_num);

截图结果

使用isnull、is not null查询

#使用isnotnull可能会导致索引失效，我测试了20条数据，只要null值占全部数据的百分之50就不会失效，否则会失效。又测了40条数据，23条数据不会为空，22条为null的会为空EXPLAINselect*fromsc_base_colorwheregroup_numisnotnull;#使用isnull也可能会导致索引失效，我测试了20条数据，6数数据不为空不会失效，也就是可能当空的数据占比70%的时候索引会失效。EXPLAINselect*fromsc_base_colorwheregroup_numisnull;

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

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

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

ALTERTABLEsc_base_colorDROPINDEXidx_group_num;ALTERTABLEsc_base_colorDROPINDEXidx_name;altertable`sc_base_color`addindexidx_group_num_idx_name(group_num,name);

测试 = > < 查询结果

EXPLAINselect*fromsc_base_colorwheregroup_num>1;EXPLAINselect*fromsc_base_colorwheregroup_num<1;EXPLAINselect*fromsc_base_colorwheregroup_num=1;EXPLAINselect*fromsc_base_colorwheregroup_num=1andname='米黄';

截图结果，是可以走索引的，下面的逻辑就不用测试了和普通索引一样，除非不符合最左匹配原则直接查询name字段。