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

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

 

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

 

 

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

1

2

3

4

5

6

7

8

9

10

11

12

13

CREATE TABLE `t_user` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `namevarchar(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 时测试数据:

1

2

3

4

5

6

7

8

9

10

11

12

13

CREATE TABLE `t_user1` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `namevarchar(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 只有完全返回索引字段时才会使用索引

  • 1
    点赞
  • 5
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值