mysql 优化之 is null ，is not null 索引使用测试

 关于mysql优化部分，有很多网友说尽量避免使用is null, is not null,select * 等，会导致索引失效，性能降低？那是否一定收到影响呢？真的就不会使用索引了吗？

 

本文的测试数据库版本为5.7.18，不同版本得出的结果可能会有所不同：

 

 

本文测试的两张表数据如下：

CREATE TABLE `t_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 `t_user` (`id`, `name`, `age`, `sex`) VALUES ('1', 'jemis', '20', '男');
INSERT INTO `t_user` (`id`, `name`, `age`, `sex`) VALUES ('2', 'fox', '20', '男');
INSERT INTO `t_user` (`id`, `name`, `age`, `sex`) VALUES ('3', 'tony', '20', '男');

CREATE  UNIQUE  INDEX indexName ON t_user(name(20));

　　一、索引字段不能为空的情况下：

   测试select *  结合is null 和 is not null ：

 

 

 

 

 

可以得出：

      EXPLAIN select * from t_user where `name` is not null; 不使用索引；
      EXPLAIN select * from t_user where `name` is null; 不使用索引；

 查询索引字段的情况下：

 

 

 

 

 

 

 

  有以上可以得出查询索引字段时：

        EXPLAIN select name from t_user where `name` is not null; 使用索引 

        EXPLAIN select name from t_user where `name` is null; 不使用索引

 

select 索引字段加 非索引字段时：

  

 

 

 

 

 可以得出：当select索引字段+其他字段时：

　　　　 EXPLAIN select name,age from t_user where `name` is not null; 不使用索引

　　　　EXPLAIN select name,age from t_user where `name` is null; 不使用索引

    

由此可见，当索引字段不可以为null 时,只有使用is not null 并且返回的结果集中只包含索引字段时，才使用索引

 

二、当索引字段可以为null 时测试数据：

CREATE TABLE `t_user1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sex` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

INSERT INTO `springtest`.`t_user1` (`id`, `name`, `age`, `sex`) VALUES ('1', 'jemis', '20', '男');
INSERT INTO `springtest`.`t_user1` (`id`, `name`, `age`, `sex`) VALUES ('2', 'fox', '20', '男');
INSERT INTO `springtest`.`t_user1` (`id`, `name`, `age`, `sex`) VALUES ('3', 'tony', '20', '男');

CREATE  UNIQUE  INDEX indexName ON t_user1(name(20));

 当索引字段可以为null,使用is null ,is not null：

 

当select * 时：

 

 

查询语句select *：EXPLAIN select * from t_user1 where `name` is not null;; 不使用索引
                 EXPLAIN select * from t_user1 where `name` is null; 使用索引

 

select 索引字段：

 

 

 

  select 索引字段的结论为：

           EXPLAIN select name from t_user1 where `name` is not null; 使用索引
            EXPLAIN select name from t_user1 where `name` is null; 使用索引

 

  select 索引字段 + 非索引字段 为：

    

 

 

 

 

当select索引字段+其他字段时： EXPLAIN select name,age from t_user1 where `name` is not null;不使用索引，会导致索引失效
                                                    EXPLAIN select name,age from t_user1 where `name` is null; 使用索引

 

 

总结以上情形可知：1、当索引字段不可以为null 时,只有使用is not null 返回的结果集中只包含索引字段时，才使用索引

 

 

                              2、当索引字段可以为空时，使用 is null 不影响覆盖索引，但是使用 is not null 只有完全返回索引字段时才会使用索引

 

最后附上本文的测试完整版sql以及结论：

CREATE TABLE `t_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 `springtest`.`t_user` (`id`, `name`, `age`, `sex`) VALUES ('1', 'jemis', '20', '男');
INSERT INTO `springtest`.`t_user` (`id`, `name`, `age`, `sex`) VALUES ('2', 'fox', '20', '男');
INSERT INTO `springtest`.`t_user` (`id`, `name`, `age`, `sex`) VALUES ('3', 'tony', '20', '男');
CREATE  UNIQUE  INDEX indexName ON t_user(name(20));


CREATE TABLE `t_user1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sex` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

INSERT INTO `springtest`.`t_user1` (`id`, `name`, `age`, `sex`) VALUES ('1', 'jemis', '20', '男');
INSERT INTO `springtest`.`t_user1` (`id`, `name`, `age`, `sex`) VALUES ('2', 'fox', '20', '男');
INSERT INTO `springtest`.`t_user1` (`id`, `name`, `age`, `sex`) VALUES ('3', 'tony', '20', '男');


CREATE  UNIQUE  INDEX indexName ON t_user1(name(20));



当字段值可以为null,使用is null ,is not null，
查询语句：EXPLAIN select * from t_user1  where `name` is not null;;  不使用索引
          EXPLAIN select * from t_user1  where `name` is  null;   使用索引
          
当不使用select * 时 ： EXPLAIN select name from t_user1  where `name` is not null;  使用索引
                       EXPLAIN select name from t_user1  where `name` is  null; 使用索引
                       
                       
当select索引字段+其他字段时： EXPLAIN select name,age from t_user1  where `name` is not null;不使用索引，会导致索引失效
                              EXPLAIN select name,age from t_user1  where `name` is  null;   使用索引
                              
从以上可以看出，当索引字段可以为空时，使用 is null 不影响覆盖索引，但是使用 is not null 只有完全返回索引字段时才会使用索引




当字段值不能为null 时，EXPLAIN select *  from t_user  where `name` is not  null; 不使用索引；
                       EXPLAIN select * from t_user  where `name` is  null;  不使用索引；
                       
当select索引字段 时 ：  EXPLAIN select name from t_user  where `name` is not null; 使用索引 
                        EXPLAIN select name from t_user  where `name` is  null;  不使用索引
                        
当select索引字段+其他字段时： EXPLAIN select name,age from t_user  where `name` is  not null; 不使用索引
                              EXPLAIN select name,age from t_user  where `name` is  null;   不使用索引
                              
                              
由此可见，当索引字段不可以为null 时,只有使用is not null 返回的结果集中只包含索引字段时，才使用索引