MySQL中NULL值是否會(huì)影響索引的使用

為何寫這一篇文章

🐭🐭在面試的時(shí)候被問到NULL值是否會(huì)走索引的時(shí)候，感到有點(diǎn)不理解，于是事后就有了這篇文章
問題：
為name建立索引，name可以為空
select * from user where name is null是否會(huì)使用索引？
生活會(huì)拷打每一個(gè)做事不認(rèn)真的人😭

索引的結(jié)構(gòu)

詳細(xì)的可以參照我的上一篇文章深入淺出MySQL，里面有關(guān)于索引的詳細(xì)介紹
在InnoDB引擎中，索引分為聚簇索引和二級索引，對于二級索引

，在這個(gè)場景下我們要考慮的就是是否會(huì)為NULL建立索引和如果列中存在NULL值，是否會(huì)走索引去查找這個(gè)NULL

訪問方法

訪問方法是MySQL來實(shí)際訪問數(shù)據(jù)的執(zhí)行方法大致分為：

1. 全表掃描
2. 使用索引掃

測試表

CREATE TABLE user (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(20) NOT NULL,  `age` int(11) DEFAULT NULL,  `sex` varchar(20) DEFAULT NULL,  PRIMARY KEY (`id`)  
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;  INSERT INTO user (`id`, `name`, `age`, `sex`) VALUES ('1', 'Bob', '20', '男');  
INSERT INTO user (`id`, `name`, `age`, `sex`) VALUES ('2', 'Jack', '20', '男');  
INSERT INTO user (`id`, `name`, `age`, `sex`) VALUES ('3', 'Tony', '20', '男');  
INSERT INTO user (`id`, `name`, `age`, `sex`) VALUES ('4', 'Alan', '20', '男');  CREATE  UNIQUE  INDEX indexName ON user(name(20));  
# 為age建立索引  
CREATE INDEX indexAge ON user(age);

const

通過主鍵或者唯一二級索引列來定位一條記錄的訪問方法
explain select * from user where id = 1;
解決如下：
?

通過type我們可以看見訪問方法是const

ref

如果二級索引列不是唯一的，那么就使用二級索引的值去匹配，之后再回表
?

explain select * from user where age = 20;
?

如圖使用的是ref方法
二級索引列值為NULL時(shí)：
二級索引列對NULL值的數(shù)量時(shí)不限制的，所以key is NULL最多使用的是ref，而不是const

ref_or_null

有時(shí)候我們需要找出二級索引等于常數(shù)和為NULL的記錄一同找出
explain select * from user where age = 20 or age is null ;
?

執(zhí)行的流程：
如圖，NULL是放在每一層中最左側(cè)的，并且是連在一起的

range

使用索引進(jìn)行范圍訪問，可以是聚簇索引，也可以是二級索引。
explain select * from user where age > 11 and age <= 20;

index

遍歷二級索引記錄的執(zhí)行方式，常常出現(xiàn)在查詢列和條件都包含在索引中，不需要回表，所以直接遍歷即可

all

全表掃描

NULL在二級索引中的位置

通過查詢資料，發(fā)現(xiàn)如果索引列允許NULL值，那么NULL在二級索引中是被當(dāng)作最小值放在樹的每一層的最左側(cè)的，也就是NULL值會(huì)被當(dāng)成索引列的數(shù)據(jù)使用的，所以NULL值匹配是可能會(huì)走索引的

1. 如果在索引列上使用IS NULL或IS NOT NULL，MySQL通常會(huì)走索引

   
   explain select * from user where age is null;  explain select * from user where age is not null;

2. 符合索引，如果簽到列不為NULL，后續(xù)的列也是可以走索引的