共计 2445 个字符，预计需要花费 7 分钟才能阅读完成。
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 走不走索引并不是相对的，要看应用条件！
