?關(guān)于mysql優(yōu)化部分，有很多網(wǎng)友說盡量避免使用is null, is not null,select * 等，會導(dǎo)致索引失效，性能降低？那是否一定收到影響呢？真的就不會使用索引了嗎？

?

本文的測試數(shù)據(jù)庫版本為5.7.18，不同版本得出的結(jié)果可能會有所不同：

?

?

本文測試的兩張表數(shù)據(jù)如下：

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 *? 結(jié)合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 并且返回的結(jié)果集中只包含索引字段時，才使用索引

?

二、當索引字段可以為null 時測試數(shù)據(jù)：

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 索引字段的結(jié)論為：

? ? ? ? ? ?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;不使用索引，會導(dǎo)致索引失效
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? EXPLAIN select name,age from t_user1 where `name` is null; 使用索引

?

?

總結(jié)以上情形可知：1、當索引字段不可以為null 時,只有使用is not null 返回的結(jié)果集中只包含索引字段時，才使用索引

?

?

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 2、當索引字段可以為空時，使用 is null 不影響覆蓋索引，但是使用 is not null 只有完全返回索引字段時才會使用索引

?

最后附上本文的測試完整版sql以及結(jié)論：

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;不使用索引，會導(dǎo)致索引失效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 返回的結(jié)果集中只包含索引字段時，才使用索引 View Code

?

轉(zhuǎn)載于:https://www.cnblogs.com/cheng21553516/p/11450765.html

總結(jié)

以上是生活随笔為你收集整理的mysql 优化之 is null ，is not null 索引使用测试的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。