mysql欄位為NULL索引是否會失效範例詳解

2022-05-29 22:02:26

專案場景：

很多部落格說mysql在欄位中建立普通索引，如果該索引中的資料存在null值是不走索引這個結論是錯誤的，不過儘量還是設定預設值。（版本8.0低於這個版本可能結果不一致）

1、建立表sc_base_color，其中普通索引為 “name，group_num”，這裡暫時不測組合索引，下面再測試。

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、初始化測試資料

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”

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

截圖結果，兩列資料都存在空，最終走了索引。

使用大於、小於查詢

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

截圖結果

使用不等於、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);

截圖結果

使用isnull、is not null查詢

# 使用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是否走了索引

先刪除普通索引欄位，增加組合索引

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

測試 = > < 查詢結果

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

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