mysql字段為NULL索引會失效嗎

本篇內容介紹了“mysql字段為NULL索引會失效嗎”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

項目場景：

很多博客說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字段。

“mysql字段為NULL索引會失效嗎”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！