关于后端:mysql中IS-NULLIS-NOT-NULL不能走索引

mysql中IS NULL、IS NOT NULL不能走索引？

不晓得是啥起因也不晓得啥时候, 江湖上流传着这么一个说法 mysql查问条件蕴含IS NULL、IS NOT NULL、!=、like %* 、like %*%,不能应用索引查问，只能应用全表扫描。

刚入行时我也是这么认为的，还奉为真谛！

然而工夫工作中你会发现还是走索引啊！上面咱们来一一探索其中的神秘。

一、首先验证一下是会走索引的

创立一个表，构造如下：

                        create table user_info(           id int PRIMARY key auto_increment,           name varchar(16) default '',           age tinyint default 0,           address varchar(32) default '',           PRIMARY KEY (`id`),           KEY `name` (`name`),           KEY `address_2` (`address`,`name`)           );           ALTER TABLE user_info ADD INDEX (NAME);           ALTER TABLE user_info ADD INDEX (address);               

                        数据1           INSERT INTO user_info(NAME,age,address)           VALUES (9,9,'shenzhen9');           BEGIN           DECLARE i INT DEFAULT 1000;           WHILE i < 9000 DO           INSERT INTO user_info (`NAME`, `age`, `address`)           VALUES           (NULL, i , SUBSTRING(MD5(RAND()),1,10) ) ;           SET i = i+ 1 ;           END WHILE ;           ① EXPLAIN SELECT * FROM user_info WHERE `name` IS NOT NULL           ② EXPLAIN SELECT * FROM user_info WHERE `name` !='9'           ③ EXPLAIN SELECT * FROM user_info WHERE `name` is null               

                        数据2           INSERT INTO user_info(NAME,age,address)           VALUES (null,9,'shenzhen9');           BEGIN           DECLARE i INT DEFAULT 1000;           WHILE i < 9000 DO           INSERT INTO user_info (`NAME`, `age`, `address`)           VALUES           (REPLACE(UUID(),'-',''), i , SUBSTRING(MD5(RAND()),1,10) ) ;           SET i = i+ 1 ;           END WHILE ;           ④ EXPLAIN SELECT * FROM user_info WHERE `name` IS NOT NULL           ⑤ EXPLAIN SELECT * FROM user_info WHERE `name` !='9'           ⑥ EXPLAIN SELECT * FROM user_info WHERE `name` is null               

执行数据1 会发现sql①②走索引，③不走索引

执行数据2 会发现sql⑥走索引，④⑤不走索引

二、B+树数据排列规定

1、聚簇索引索引：

①页面中的记录是依照主键值进行排序的；

②B+树每一层节点（页面）都是依照页中记录的主键值大小进行排序的；

③B+树叶子节点对应的页面中存储的是残缺的用户记录（就是一条记录中蕴含咱们定义的所有列值，还蕴含一些InnoDB本人增加的一些暗藏列）；

2、二级索引：

①页面中的记录是依照给定的索引列的值进行排序的。

②B+树每一层节点（页面）都是依照页中记录的给定的索引列的值进行排序的。

③B+树叶子节点对应的页面中存储的只是索引列的值 + 主键值。

二级索引值能为空。那对于索引列值为NULL的二级索引记录，在B+树的哪个地位呢？

在B+树的最右边。如下图

至于为什么,InnoDB是这样的规定：SQL中的NULL值是列中最小的值

什么时候索引又不失效了呢？

比照数据1和数据2两个数据中null值的数量不一样，当null值占多数时is not null 和!=走索引 ,is null不走索引了，数据2刚好相同。

预计大家都能看出什么来了。带索引字段应用null做判断是否走索引与数据量无关,归纳起来就是老本问题(对于mysql索引扫描成本计算详细分析倡议大家能够去看一下掘金小册《mysql是怎么运行的:从根上了解mysql》)。

索引(二级索引)扫描老本:

1、读取索引记录老本

2、反查主键索引查找残缺数据老本即回表

如果查问读取的二级索引越多那么须要回表查问的次数就会越多，达到肯定的比例就会变成全副查问了，也就是下面null 查问时索引有时不失效的起因。

综上MySQL中决定使不应用某个索引执行查问的根据是老本大小。而不是在WHERE子句中用了IS NULL、IS NOT NULL、!=这些条件

三、如何让like‘%字符串%’，‘字符串%’时走索引

通常状况下咱们应用like %%、%确实不会走索引 然而并不代表就肯定不能走索引，咱们对下面表中name和age建设复合索引

                        explain select name from user_info where name like '%a%';           1 SIMPLE user_info index idx_n_a 53 6 16.67 Using where; Using index           explain select name,age from user_info where name like '%a%';           1 SIMPLE user_info index idx_n_a 53 6 16.67 Using where; Using index           以下两个例子是查问了不在复合索引中的列进而造成全表扫描           explain select name,age,address from user_info where name like '%a%';           1 SIMPLE user_info ALL 6 16.67 Using where           explain select * from user_info where name like '%a%';           1 SIMPLE user_info ALL 6 16.67 Using where               

所以like走不走索引并不是相对的，要看应用条件！