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

关于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

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

View Code

参与评论 您还未登录,请先 登录 后发表或查看评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:深蓝海洋 设计师:CSDN官方博客 返回首页

打赏作者

露月微凉

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值