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、初始化測試數(shù)據(jù)

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, '藍(lán)色');
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, '米藍(lán)色');
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的情況是否使用了索引

使用 = 查詢，測試結(jié)果中使用到了索引，其中索引字段的值為“NULL”

EXPLAIN select * from sc_base_color where name = '米黃';
EXPLAIN select * from sc_base_color where group_num = 1;

截圖結(jié)果，兩列數(shù)據(jù)都存在空，最終走了索引。

使用大于、小于查詢

EXPLAIN select * from sc_base_color where name > '米黃';
EXPLAIN select * from sc_base_color where name < '米黃';

截圖結(jié)果

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

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);

截圖結(jié)果

使用isnull、is not null查詢

# 使用is not null可能會導(dǎo)致索引失效，我測試了20條數(shù)據(jù)，只要null值占全部數(shù)據(jù)的百分之50就不會失效，否則會失效。又測了40條數(shù)據(jù)，23條數(shù)據(jù)不會為空，22條為null的會為空
EXPLAIN select  * from sc_base_color where  group_num is not null;
# 使用is null也可能會導(dǎo)致索引失效，我測試了20條數(shù)據(jù)，6數(shù)數(shù)據(jù)不為空不會失效，也就是可能當(dāng)空的數(shù)據(jù)占比70%的時候索引會失效。
EXPLAIN select  * from sc_base_color where  group_num is  null;

由此可以得出結(jié)論，字段為空是可以走索引的，但是部分場景可能會失效，盡量還是給默認(rèn)值。

4、測試組合索引為NULL是否走了索引

先刪除普通索引字段，增加組合索引

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);

測試 = > < 查詢結(jié)果

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 = '米黃';

截圖結(jié)果，是可以走索引的，下面的邏輯就不用測試了和普通索引一樣，除非不符合最左匹配原則直接查詢name字段。