mysql字段為NULL索引是否會失效實例詳解

Posted on by

項目場景：

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

5、總結

在設計數據庫的時候盡量還是給字段的默認值。

1、比如int、bigint類型默認值為-1/0

2、比如varchar類型默認值為空串

3、bigdecimal類型為0等等。

NULL值會有不少坑

1、count(字段NULL)會過濾統計的數據，sum這些函數也會

2、使用> < 的時候也會過濾掉為NULL的數據

3、group by 的時候會把所有為NULL的數據合並，可以隨機生成UUID解決

4、還有場景可能也有問題，這裡我也忘記瞭，用的時候才會想起來。

總結

到此這篇關於mysql字段為NULL索引是否會失效的文章就介紹到這瞭,更多相關mysql字段NULL索引失效內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：