Mysql中IS NULL、IS NOT NULL不能走索引?

关于MySQL查询条件中的IS NULL和IS NOT NULL不能使用索引的说法,实则不然。文章通过实验验证了IS NULL和IS NOT NULL在一定条件下可以走索引。B+树的结构解释了为何NULL值在索引中会被放在最左边。索引是否生效取决于查询成本,当NULL值占比高时,IS NULL可能不走索引。此外,文中探讨了LIKE '%字符串%'情况下的索引使用策略,指出在特定情况下,即使包含通配符,索引也可能被利用。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

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⑥走索引,④⑤不走索引

技术分享 || Mysql中IS NULL、IS NOT NULL不能走索引?

 

技术分享 || Mysql中IS NULL、IS NOT NULL不能走索引?

 

二、B+树数据排列规则

1、聚簇索引索引:

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

②B+树每一层节点(页面)都是按照页中记录的主键值大小进行排序的;

③B+树叶子节点对应的页面中存储的是完整的用户记录(就是一条记录中包含我们定义的所有列值,还包含一些InnoDB自己添加的一些隐藏列);

2、二级索引:

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

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

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

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

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

技术分享 || Mysql中IS NULL、IS NOT NULL不能走索引?

 

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

什么时候索引又不生效了呢?

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

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

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走不走索引并不是绝对的,要看使用条件!

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值