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

最新推荐文章于 2024-08-14 02:18:27 发布
最新推荐文章于 2024-08-14 02:18:27 发布
阅读量641 收藏 1
点赞数
文章标签： mysql is null走索引
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_33973421/article/details/113127741
版权

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

露月微凉
关注
MySQL Order By索引优化方法
09-11
以下是一些能够使用索引优化`ORDER BY`的例子： 1. 查询按索引完全顺序排列的数据： ```sql SELECT * FROM t1 ORDER BY key_part1, key_part2, ...; ``` 2. 当`WHERE`子句中包含索引的一部分，且`ORDER BY`按照...
什么是回表查询
热门推荐
我真的睡不够
09-11 2万+
什么是回表查询？ 这先要从InnoDB的索引实现说起，InnoDB有两大类索引： 聚集索引(clustered index) 普通索引(secondary index) InnoDB聚集索引和普通索引有什么差异？ InnoDB聚集索引的叶子节点存储行记录，因此， InnoDB必须要有，且只有一个聚集索引： （1）如果表定义了PK，则PK就是聚集索引； （2）如果表没有定义PK，则第一个not NULL unique列是聚集索引； （3）否则，InnoDB会创建一个隐..
mysql索引类型：FULLTEXT、NORMAL、SPATIAL、UNIQUE的详细介绍
qq_27615455的博客
12-16 3184
前面已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。任何事物都是有两面性的，Hash 索引也一样，虽然 Hash 索引效率高，但是 Hash 索引本身由于其特殊性也带来了很多限制和弊端，主要有以下这些。
mysql is not null执行很慢
最新发布
weixin_34540951的博客
08-14 63
我整理的一些关于【MySQL,SQL】的项目学习资料（附讲解～～）和大家一起分享、学习一下：https://d.51cto.com/yOSbkRMySQL 查询优化：处理 “IS NOT NULL” 的慢查询问题 在数据库开发中，性能优化是一个重要的任务，而 “IS NOT NULL” 查询慢的问题常常困扰着很多开发者。...
mysql之索引类型
半生@浮名的博客
06-19 2191
1.normal(普通索引)：是普通索引，没有限制。 2.UNIQUE(唯一索引)：是唯一的，不允许重复的索引。索引列的值必须唯一，但是允许为空值。 3.FULLTEXT(全文索引)：是全文搜索的索引。主要用来查找文本（索引列的值）中的关键字 4.spatial(空间索引)：是对空间数据类型的字段建立的索引。MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTR...
详细介绍mysql索引类型：FULLTEXT、NORMAL、SPATIAL、UNIQUE
weixin_51793146的博客
03-23 4302
原文 mysql索引类型：FULLTEXT、NORMAL、SPATIAL、UNIQUE的详细介绍 Normal 普通索引Unique 唯一索引Full Text 全文索引SPATIAL 空间索引btree索引和hash索引的区别在实际操作过程中，应该选取表中哪些字段作为索引？
mysql单列索引和多列索引_mysql索引类型 normal, unique, full text
weixin_39533432的博客
01-03 534
问题1：mysql索引类型normal，unique，full text的区别是什么？normal：表示普通索引unique：表示唯一的，不允许重复的索引，如果该字段信息保证不会重复例如身份证号用作索引时，可设置为uniquefull textl: 表示 全文搜索的索引。 FULLTEXT 用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。总结，...
MySQL 函数索引的优化方案
01-19
很多开发人员在使用MySQL时经常会在部分列上进行函数计算等，导致无法走索引，在数据量大的时候，查询效率低下。针对此种情况本文从MySQL5.7 及MySQL8.0中分别进行不同方式的优化。 1、 MySQL5.7 MySQL5.7版本中不...
MySQL的or、in、union与索引优化
10-11
### MySQL的or、in、union与索引优化 在数据库查询优化中，索引的使用至关重要，它能显著提升查询速度。本文将基于一个具体的业务场景来探讨在MySQL中使用`union all`、`in`、`or`以及负向查询（如`!=`）时如何有效...
mysql优化之like和=性能详析
09-08
MySQL优化之LIKE与=性能详析 在数据库查询中，LIKE和=是两种常见的比较操作符，它们在功能上有着显著的区别，同时也涉及到性能优化的问题。LIKE主要用于模糊查询，允许在查询条件中使用通配符，而=则用于精确匹配。...
MYSQL中常用的强制性操作（例如强制索引）
09-10
在MySQL数据库中，为了优化SQL查询性能，开发者有时需要对查询进行强制性的操作。这些操作类似于Oracle数据库中的提示（hint）功能，可以帮助调整查询计划，确保数据库按照特定的方式执行查询。以下是一些在MySQL中...
MySQL索引类型Normal、Unique和Full Text的讲解
09-09
今天小编就为大家分享一篇关于MySQL索引类型Normal、Unique和Full Text的讲解，小编觉得内容挺不错的，现在分享给大家，具有很好的参考价值，需要的朋友一起跟随小编来看看吧
mysql 索引 normal_谈谈Mysql索引优化不得不提防的坑
weixin_33940733的博客
02-27 695
谈谈Mysql索引优化不得不提防的坑逍遥子 码农沉思录前言在之前的文章《聊聊Mysql优化之索引优化》中，笔者简单介绍了Mysql索引优化的原理和一些使用场景，然而Mysql索引优化的内容还远远不止这些。在实际工作中，我们有时候会碰到明明已经建了索引，但是查询速度还是上不去的问题，这时候就要当心了，有可能你的查询语句根本就没使用到索引，因为Mysql索引在某些情况下会失效，今天我将为大家介绍下M...
mysql 索引类型 FULLTEXT NORMAL SPATIAL UNIQUE 区别
nbspzs的专栏
03-28 1008
B-Tree 索引可以在等值查找（例如 `WHERE column = value`）、范围查找（例如 `WHERE column > value`）、排序和分组等操作中提高查询效率。综上所述，这些索引类型各自适用于不同的场景和数据类型，您可以根据应用程序的需求和数据特性来选择合适的索引类型来优化查询性能。- UNIQUE 索引可以用于确保表中的某些列的数值唯一性，类似于主键约束，但可以允许 NULL 值。- NORMAL 索引也称为 B-Tree 索引，它是最常见的索引类型。
MySQL 中的索引类型
不想做一只咸鱼的博客
03-17 1111
mysql索引类型的区分
mysql回表查询
weixin_42043101的博客
10-10 4974
通俗的讲就是，如果索引的列在 select 所需获得的列中（因为在 mysql 中索引是根据索引列的值进行排序的，所以索引节点中存在该列中的部分值）或者根据一次索引查询就能获得记录就不需要回表，如果 select 所需获得列中有大量的非索引列，索引就需要到表中找到相应的列的信息，这就叫回表。 根据这个概念，当你使用 Explain 执行查询计划时，当结果中 Extra 出现了 using ind...
mysql 索引 normal_mysql索引类型 normal, unique, full text
weixin_42520025的博客
01-18 2329
问题1：mysql索引类型normal，unique，full text的区别是什么？normal：表示普通索引unique：表示唯一的，不允许重复的索引，如果该字段信息保证不会重复例如身份证号用作索引时，可设置为uniquefull textl: 表示 全文搜索的索引。 FULLTEXT 用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。总结，...
MySQL 的索引
彳亍
11-04 2625
在 MySQL 中，索引（index）也叫做键（key），它是存储引擎用于快速找到记录的一种数据结构。 索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对性能的影响就愈发重要。 索引优化是对查询性能优化最有效的手段。
索引类型(normal，unique，full text)和索引方法(Hash,BTree)的区别
Durian大圣
10-11 1万+
mysql索引类型normal，unique，full text的区别是什么？ normal：表示普通索引 unique：表示唯一的，不允许重复的索引，如果该字段信息保证不会重复例如身份证号用作索引时，可设置为unique full textl: 表示 全文搜索的索引。 FULLTEXT 用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。 ...
mysql索引十条数据走索引快还是不走索引快 为什么
05-22
然而，如果查询条件不合适，或者表中数据量很小，走索引就可能不如不走索引快。这是因为索引也有一定的开销，比如需要占用额外的存储空间、需要维护索引数据结构等。如果查询条件不太精确，或者表中数据量很少，使用...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值