mysql字段為NULL索引是否會(huì)失效實(shí)例詳解

更新時(shí)間：2022年05月29日 16:31:24 作者：一只小loser

有很多人對(duì)null值是否走索引感覺(jué)很疑惑,所以下面這篇文章主要給大家介紹了關(guān)于mysql字段為NULL索引是否會(huì)失效的相關(guān)資料,文中通過(guò)示例代碼介紹的非常詳細(xì),需要的朋友可以參考下

項(xiàng)目場(chǎng)景：

很多博客說(shuō)mysql在字段中創(chuàng)建普通索引，如果該索引中的數(shù)據(jù)存在null值是不走索引這個(gè)結(jié)論是錯(cuò)誤的，不過(guò)盡量還是設(shè)置默認(rèn)值。（版本8.0低于這個(gè)版本可能結(jié)果不一致）

1、創(chuàng)建表sc_base_color，其中普通索引為 “name，group_num”，這里暫時(shí)不測(cè)組合索引，下面再測(cè)試。

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 '顏色名稱(chēng)',
  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、初始化測(cè)試數(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、測(cè)試普通索引為NULL的情況是否使用了索引

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

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

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

使用大于、小于查詢(xún)

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

截圖結(jié)果

使用不等于、not in 、isnull、!isnull查詢(xún)

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查詢(xún)

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

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

4、測(cè)試組合索引為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);

測(cè)試 = > < 查詢(xún)結(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é)果，是可以走索引的，下面的邏輯就不用測(cè)試了和普通索引一樣，除非不符合最左匹配原則直接查詢(xún)name字段。