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

阳光穿透心脏的1/2处 2023-01-11 01:24 213阅读 0赞

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

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

![5b95d6870a9dc339161d221938a9b808.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 \`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 ：

![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][]

![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 时测试数据：

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 \* 时：

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

![8f900a89c6347c561fdf2122f13be562.png][]

![961ddebeb323a10fe0623af514929fc1.png][]

CREATE TABLE\`t\_user\` (

\`id\`int(11) NOT NULLAUTO\_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 NULLAUTO\_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,使用isnull ,is not null，

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

EXPLAINselect \* from t\_user1 where \`name\` is null; 使用索引

当不使用select\* 时 ： EXPLAIN select name from t\_user1 where \`name\` is not null; 使用索引

EXPLAINselect name from t\_user1 where \`name\` is null; 使用索引

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

EXPLAINselect name,age from t\_user1 where \`name\` is null; 使用索引

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

当字段值不能为null 时，EXPLAINselect \* from t\_user where \`name\` is not null; 不使用索引；

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

当select索引字段 时 ： EXPLAINselect name from t\_user where \`name\` is not null; 使用索引

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

当select索引字段+其他字段时： EXPLAIN select name,age from t\_user where \`name\` is not null; 不使用索引

EXPLAINselect name,age from t\_user where \`name\` is null; 不使用索引

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

[5b95d6870a9dc339161d221938a9b808.png]: /images/20221119/bc50b7b2429644089bf64f6d6b609454.png
[1a9fa498ffbaa3815cb4e10c3187d988.png]: /images/20221119/541bba70b08f4ab08286184a924b6b1c.png
[8ff9ebee42aabacca5ee7066f9993677.png]: /images/20221119/43f30f795c0e4bd393630997dc3f9a26.png
[c215f7bcc7483699d51843769b5c60e7.png]: /images/20221119/590321d9a6b6487d94277792d9c13560.png
[0bcb06886772ebaf5ca72b031b53ebab.png]: /images/20221119/3fd0352e1dd14737ad8fd11aefa9068d.png
[e35bc508fc969da2a1bec89de78391bd.png]: /images/20221119/670c50a120e94a2396049e30086cd9c3.png
[1d4d59e8fe68a14487f7bb064d8ceddd.png]: https://img-blog.csdnimg.cn/img_convert/1d4d59e8fe68a14487f7bb064d8ceddd.png
[6ce66f1d4ae997be3e110bb2f57eb73f.png]: https://img-blog.csdnimg.cn/img_convert/6ce66f1d4ae997be3e110bb2f57eb73f.png
[a3d6c6c81e3413c427569e886c5e342b.png]: https://img-blog.csdnimg.cn/img_convert/a3d6c6c81e3413c427569e886c5e342b.png
[d4dd71f406d70d268d102e6c66aac1ef.png]: https://img-blog.csdnimg.cn/img_convert/d4dd71f406d70d268d102e6c66aac1ef.png
[38015caa65303d949a0064d27437c333.png]: https://img-blog.csdnimg.cn/img_convert/38015caa65303d949a0064d27437c333.png
[57c309f80492ebdafbda456c980e1993.png]: https://img-blog.csdnimg.cn/img_convert/57c309f80492ebdafbda456c980e1993.png
[475ead4963acfd3524e8816282cd94c1.png]: https://img-blog.csdnimg.cn/img_convert/475ead4963acfd3524e8816282cd94c1.png
[8f900a89c6347c561fdf2122f13be562.png]: https://img-blog.csdnimg.cn/img_convert/8f900a89c6347c561fdf2122f13be562.png
[961ddebeb323a10fe0623af514929fc1.png]: https://img-blog.csdnimg.cn/img_convert/961ddebeb323a10fe0623af514929fc1.png