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

女爷i 2022-12-31 01:13 302阅读 0赞

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

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

![5b95d6870a9dc339161d221938a9b808.png][]

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

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 12 13 </td> 
 <td> <code>CREATE</code>&nbsp;<code>TABLE</code>&nbsp;<code>`t_user` (</code> <code>&nbsp;&nbsp;</code><code>`id`&nbsp;</code><code>int</code><code>(11)&nbsp;</code><code>NOT</code>&nbsp;<code>NULL</code>&nbsp;<code>AUTO_INCREMENT,</code> <code>&nbsp;&nbsp;</code><code>`</code><code>name</code><code>`&nbsp;</code><code>varchar</code><code>(20)&nbsp;</code><code>NOT</code>&nbsp;<code>NULL</code><code>,</code> <code>&nbsp;&nbsp;</code><code>`age`&nbsp;</code><code>int</code><code>(11)&nbsp;</code><code>DEFAULT</code>&nbsp;<code>NULL</code><code>,</code> <code>&nbsp;&nbsp;</code><code>`sex`&nbsp;</code><code>varchar</code><code>(20)&nbsp;</code><code>DEFAULT</code>&nbsp;<code>NULL</code><code>,</code> <code>&nbsp;&nbsp;</code><code>PRIMARY</code>&nbsp;<code>KEY</code>&nbsp;<code>(`id`)</code> <code>) ENGINE=InnoDB AUTO_INCREMENT=4&nbsp;</code><code>DEFAULT</code>&nbsp;<code>CHARSET=utf8;</code> &nbsp; <code>INSERT</code>&nbsp;<code>INTO</code>&nbsp;<code>`t_user` (`id`, `</code><code>name</code><code>`, `age`, `sex`)&nbsp;</code><code>VALUES</code>&nbsp;<code>(</code><code>'1'</code><code>,&nbsp;</code><code>'jemis'</code><code>,&nbsp;</code><code>'20'</code><code>,&nbsp;</code><code>'男'</code><code>);</code> <code>INSERT</code>&nbsp;<code>INTO</code>&nbsp;<code>`t_user` (`id`, `</code><code>name</code><code>`, `age`, `sex`)&nbsp;</code><code>VALUES</code>&nbsp;<code>(</code><code>'2'</code><code>,&nbsp;</code><code>'fox'</code><code>,&nbsp;</code><code>'20'</code><code>,&nbsp;</code><code>'男'</code><code>);</code> <code>INSERT</code>&nbsp;<code>INTO</code>&nbsp;<code>`t_user` (`id`, `</code><code>name</code><code>`, `age`, `sex`)&nbsp;</code><code>VALUES</code>&nbsp;<code>(</code><code>'3'</code><code>,&nbsp;</code><code>'tony'</code><code>,&nbsp;</code><code>'20'</code><code>,&nbsp;</code><code>'男'</code><code>);</code> &nbsp; <code>CREATE</code>&nbsp;&nbsp;<code>UNIQUE</code>&nbsp;&nbsp;<code>INDEX</code>&nbsp;<code>indexName&nbsp;</code><code>ON</code>&nbsp;<code>t_user(</code><code>name</code><code>(20));</code> </td> 
 </tr> 
 </tbody> 
</table>

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

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

![1a9fa498ffbaa3815cb4e10c3187d988.png][]

![8ff9ebee42aabacca5ee7066f9993677.png][]

可以得出：

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

查询索引字段的情况下：

![c215f7bcc7483699d51843769b5c60e7.png][]

![0bcb06886772ebaf5ca72b031b53ebab.png][]

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

EXPLAIN select name from t\_user where \`name\` is not null; 使用索引

EXPLAIN select name from t\_user where \`name\` is null; 不使用索引

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

*  ![e35bc508fc969da2a1bec89de78391bd.png][]*

*![e35bc508fc969da2a1bec89de78391bd.png][]*

![1d4d59e8fe68a14487f7bb064d8ceddd.png][]

可以得出：*当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 时测试数据：**

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 12 13 </td> 
 <td> <code>CREATE</code>&nbsp;<code>TABLE</code>&nbsp;<code>`t_user1` (</code> <code>&nbsp;&nbsp;</code><code>`id`&nbsp;</code><code>int</code><code>(11)&nbsp;</code><code>NOT</code>&nbsp;<code>NULL</code>&nbsp;<code>AUTO_INCREMENT,</code> <code>&nbsp;&nbsp;</code><code>`</code><code>name</code><code>`&nbsp;</code><code>varchar</code><code>(20)&nbsp;</code><code>DEFAULT</code>&nbsp;<code>NULL</code><code>,</code> <code>&nbsp;&nbsp;</code><code>`age`&nbsp;</code><code>int</code><code>(11)&nbsp;</code><code>DEFAULT</code>&nbsp;<code>NULL</code><code>,</code> <code>&nbsp;&nbsp;</code><code>`sex`&nbsp;</code><code>varchar</code><code>(20)&nbsp;</code><code>DEFAULT</code>&nbsp;<code>NULL</code><code>,</code> <code>&nbsp;&nbsp;</code><code>PRIMARY</code>&nbsp;<code>KEY</code>&nbsp;<code>(`id`)</code> <code>) ENGINE=InnoDB AUTO_INCREMENT=4&nbsp;</code><code>DEFAULT</code>&nbsp;<code>CHARSET=utf8;</code> &nbsp; <code>INSERT</code>&nbsp;<code>INTO</code>&nbsp;<code>`springtest`.`t_user1` (`id`, `</code><code>name</code><code>`, `age`, `sex`)&nbsp;</code><code>VALUES</code>&nbsp;<code>(</code><code>'1'</code><code>,&nbsp;</code><code>'jemis'</code><code>,&nbsp;</code><code>'20'</code><code>,&nbsp;</code><code>'男'</code><code>);</code> <code>INSERT</code>&nbsp;<code>INTO</code>&nbsp;<code>`springtest`.`t_user1` (`id`, `</code><code>name</code><code>`, `age`, `sex`)&nbsp;</code><code>VALUES</code>&nbsp;<code>(</code><code>'2'</code><code>,&nbsp;</code><code>'fox'</code><code>,&nbsp;</code><code>'20'</code><code>,&nbsp;</code><code>'男'</code><code>);</code> <code>INSERT</code>&nbsp;<code>INTO</code>&nbsp;<code>`springtest`.`t_user1` (`id`, `</code><code>name</code><code>`, `age`, `sex`)&nbsp;</code><code>VALUES</code>&nbsp;<code>(</code><code>'3'</code><code>,&nbsp;</code><code>'tony'</code><code>,&nbsp;</code><code>'20'</code><code>,&nbsp;</code><code>'男'</code><code>);</code> &nbsp; <code>CREATE</code>&nbsp;&nbsp;<code>UNIQUE</code>&nbsp;&nbsp;<code>INDEX</code>&nbsp;<code>indexName&nbsp;</code><code>ON</code>&nbsp;<code>t_user1(</code><code>name</code><code>(20));</code> </td> 
 </tr> 
 </tbody> 
</table>

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

当select \* 时：

![6ce66f1d4ae997be3e110bb2f57eb73f.png][]

![a3d6c6c81e3413c427569e886c5e342b.png][]

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

select 索引字段：

![d4dd71f406d70d268d102e6c66aac1ef.png][]

![38015caa65303d949a0064d27437c333.png][]

select 索引字段的结论为：

EXPLAIN select name from t\_user1 where \`name\` is not null; 使用索引  
            EXPLAIN select name from t\_user1 where \`name\` is null; 使用索引

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

![57c309f80492ebdafbda456c980e1993.png][]

*![475ead4963acfd3524e8816282cd94c1.png][]*

当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以及结论：

![961ddebeb323a10fe0623af514929fc1.png][]

![复制代码][48304ba5e6f9fe08f3fa1abda7d326ab.png]

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 返回的结果集中只包含索引字段时，才使用索引

![复制代码][48304ba5e6f9fe08f3fa1abda7d326ab.png]

[5b95d6870a9dc339161d221938a9b808.png]: /images/20221120/7bb7af762d624723a5289e1a7bc6611e.png
[1a9fa498ffbaa3815cb4e10c3187d988.png]: /images/20221120/6b06d11664994a00a3d202cb5d9efe08.png
[8ff9ebee42aabacca5ee7066f9993677.png]: /images/20221120/36823b62f1024219a4e754b70d3d7e13.png
[c215f7bcc7483699d51843769b5c60e7.png]: /images/20221120/c197cf6beb344e5d8262e17313a7acae.png
[0bcb06886772ebaf5ca72b031b53ebab.png]: /images/20221120/dce49ff9f1d24f11a9492c31705c620f.png
[e35bc508fc969da2a1bec89de78391bd.png]: /images/20221120/533166d8747a49c991b43db0cb765b13.png
[1d4d59e8fe68a14487f7bb064d8ceddd.png]: /images/20221120/b976aa1bdaf541a8a22aff5bd3e77d74.png
[6ce66f1d4ae997be3e110bb2f57eb73f.png]: /images/20221120/5e0ba1dec3d8495486411b3e56638f61.png
[a3d6c6c81e3413c427569e886c5e342b.png]: /images/20221120/f045eebc36974dc9ac2e50f0886045e7.png
[d4dd71f406d70d268d102e6c66aac1ef.png]: /images/20221120/db4aa9725bc24b0198ff0d2108a921dd.png
[38015caa65303d949a0064d27437c333.png]: /images/20221120/02f2afc4a4aa42849dd8300be46b6673.png
[57c309f80492ebdafbda456c980e1993.png]: /images/20221120/ea4737e591a04955afa4f60db2e18484.png
[475ead4963acfd3524e8816282cd94c1.png]: /images/20221120/18ddd02644fe44b8b5aff855f7a5a35b.png
[961ddebeb323a10fe0623af514929fc1.png]: /images/20221120/0f84434768e04d13bc2d054e3522cbf9.png
[48304ba5e6f9fe08f3fa1abda7d326ab.png]: /images/20221120/0ef20e6e97cf45998d57a32c6f9bb205.png