一、初始化
mysql版本:5.7
这些东西在8.0以后有一部分会有优化
下面的东西都是实验出来的,说的都是结论,没有贴图,感兴趣的可以自己动手实验一下,欢迎讨论
初始化sql:
CREATE TABLE `wystest0322` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`sc` int(11) DEFAULT NULL,
`one` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `one_idx` (`one`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=66 DEFAULT CHARSET=utf8mb4;
# 插入14条记录
INSERT INTO `wystest`.`wystest0322` (`id`, `sc`, `one`) VALUES (1, 1, 1);
INSERT INTO `wystest`.`wystest0322` (`id`, `sc`, `one`) VALUES (2, 1, 1);
INSERT INTO `wystest`.`wystest0322` (`id`, `sc`, `one`) VALUES (3, 1, 1);
INSERT INTO `wystest`.`wystest0322` (`id`, `sc`, `one`) VALUES (4, 1, 1);
INSERT INTO `wystest`.`wystest0322` (`id`, `sc`, `one`) VALUES (5, 1, 1);
INSERT INTO `wystest`.`wystest0322` (`id`, `sc`, `one`) VALUES (6, 1, 1);
INSERT INTO `wystest`.`wystest0322` (`id`, `sc`, `one`) VALUES (7, 1, 1);
INSERT INTO `wystest`.`wystest0322` (`id`, `sc`, `one`) VALUES (8, 1, 1);
INSERT INTO `wystest`.`wystest0322` (`id`, `sc`, `one`) VALUES (9, 1, 1);
INSERT INTO `wystest`.`wystest0322` (`id`, `sc`, `one`) VALUES (10, 1, 1);
INSERT INTO `wystest`.`wystest0322` (`id`, `sc`, `one`) VALUES (11, 1, 1);
INSERT INTO `wystest`.`wystest0322` (`id`, `sc`, `one`) VALUES (12, 1, 1);
INSERT INTO `wystest`.`wystest0322` (`id`, `sc`, `one`) VALUES (13, 1, 1);
INSERT INTO `wystest`.`wystest0322` (`id`, `sc`, `one`) VALUES (14, 1, 1);
二、is null 、is not null
非主键索引, 没有索引覆盖
is not null , 在 9 条 null时候,就开始扫描全表了(type 为 all), 在 10 条null 的时候,还是走的索引 type 为 range
is null , 在 10 条null 的时候,就开始扫描全表了(type为 all),在 9 条null 的时候,还是走的索引 type 为 ref
非主键索引, 有索引覆盖
Is not null , 无论怎么样都会走索引,不过没有null为0的时候,type 是 index ,其他的时候都是 range
Is null, 无论怎么样都会走索引,而且 type 一直都是 ref
主键索引,没有索引覆盖
is not null 的 type 为 all
is null 的 type 为 null
主键索引,有索引覆盖,type一定是 null 或者 all
Is null 的 type 是 null
is not null 的 type 是 index, 扫描的是 one_idx 索引的索引树
主键is null type 为 null 的原因
主键索引一定不为null, 所以压根儿不会去执行它。
type 为 null 的含义:MySQL不访问任何表,索引,直接返回结果
is null 和 is not null 能走索引的原因
InnoDB
的规定:
We define the SQL null to be the smallest possible value of a field.
也就是说他们把SQL中的NULL
值认为是列中最小的值。即所有null值的数据在一起,是可以使用索引的,脑海里呈现索引树想象一下
三、!=
非主键,有覆盖索引,一定会走索引,如果是非覆盖索引,目前来看是一定不会走索引(有可能会走,但是要看数据量,但是我没有复现)
主键索引,一定不会走索引,因为主键索引是不会有重复的,走索引几乎也是要走全表的,划不来
四、总结
在is null 和 is not null 中,如果是非主键索引,没有索引覆盖的话,是否走索引是在null 值的数据量的,is null 在 null 少的时候,走索引, null多则不走索引,is not null 相反
同时也可以发现 索引覆盖对是否走索引的影响很大,主键索引和非主键索引对是否走索引、什么方式走索引的影响也很大