mysql查询条件包含IS NULL、IS NOT NULL、!=、like %* 、like %*%,不能使用索引查询,只能使用全表扫描,是真的吗???

不知道是啥原因也不知道啥时候, 江湖上流传着这么一个说法 mysql查询条件包含IS NULL、IS NOT NULL、!=、like %* 、like %*%,不能使用索引查询,只能使用全表扫描。

刚入行时我也是这么认为的,还奉为真理!

但是时间工作中你会发现还是走索引啊!下面我们来一一探究其中的奥秘。

一、首先验证一下是会走索引的

创建一个表,结构如下:

create table user_info(

id int PRIMARY key auto_increment,

name varchar(16) default '',

age tinyint default 0,

address varchar(32) default '',

PRIMARY KEY (`id`),

KEY `name` (`name`),

KEY `address_2` (`address`,`name`)

);

ALTER TABLE user_info ADD INDEX (NAME);

ALTER TABLE user_info ADD INDEX (address);

数据1

INSERT INTO user_info(NAME,age,address)

VALUES (9,9,'shenzhen9');

BEGIN

DECLARE i INT DEFAULT 1000;

WHILE i < 9000 DO

INSERT INTO user_info (`NAME`, `age`, `address`)

VALUES

(NULL, i , SUBSTRING(MD5(RAND()),1,10) ) ;

SET i = i+ 1 ;

END WHILE ;

① EXPLAIN SELECT * FROM user_info WHERE `name` IS NOT NULL

② EXPLAIN SELECT * FROM user_info WHERE `name` !='9'

③ EXPLAIN SELECT * FROM user_info WHERE `name` is null

数据2

INSERT INTO user_info(NAME,age,address)

VALUES (null,9,'shenzhen9');

BEGIN

DECLARE i INT DEFAULT 1000;

WHILE i < 9000 DO

INSERT INTO user_info (`NAME`, `age`, `address`)

VALUES

(REPLACE(UUID(),'-',''), i , SUBSTRING(MD5(RAND()),1,10) ) ;

SET i = i+ 1 ;

END WHILE ;

④ EXPLAIN SELECT * FROM user_info WHERE `name` IS NOT NULL

⑤ EXPLAIN SELECT * FROM user_info WHERE `name` !='9'

⑥ EXPLAIN SELECT * FROM user_info WHERE `name` is null

执行数据1 会发现sql①②走索引,③不走索引

执行数据2 会发现sql⑥走索引,④⑤不走索引

二、B+树数据排列规则

1、聚簇索引索引:

①页面中的记录是按照主键值进行排序的;

②B+树每一层节点(页面)都是按照页中记录的主键值大小进行排序的;

③B+树叶子节点对应的页面中存储的是完整的用户记录(就是一条记录中包含我们定义的所有列值,还包含一些InnoDB自己添加的一些隐藏列);

2、二级索引:

①页面中的记录是按照给定的索引列的值进行排序的。

②B+树每一层节点(页面)都是按照页中记录的给定的索引列的值进行排序的。

③B+树叶子节点对应的页面中存储的只是索引列的值 + 主键值。

二级索引值能为空。那对于索引列值为NULL的二级索引记录,在B+树的哪个位置呢?

在B+树的最左边。如下图

至于为什么,InnoDB是这样的规定:SQL中的NULL值是列中最小的值

什么时候索引又不生效了呢?

对比数据1和数据2两个数据中null值的数量不一样,当null值占多数时is not null 和!=走索引 ,is null不走索引了,数据2刚好相反。

估计大家都能看出什么来了。带索引字段使用null做判断是否走索引与数据量有关,归纳起来就是成本问题(关于mysql索引扫描成本计算详细分析建议大家可以去看一下掘金小册《mysql是怎样运行的:从根上理解mysql》)。

索引(二级索引)扫描成本:
1、读取索引记录成本
2、反查主键索引查找完整数据成本即回表

如果查询读取的二级索引越多那么需要回表查询的次数就会越多,达到一定的比例就会变成全部查询了,也就是上面null 查询时索引有时不生效的原因。

综上MySQL中决定使不使用某个索引执行查询的依据是成本大小。而不是在WHERE子句中用了IS NULL、IS NOT NULL、!=这些条件

三、如何让like‘%字符串%’,‘字符串%’时走索引

通常情况下我们使用like %*%、%*的确不会走索引 但是并不代表就一定不能走索引,我们对上面表中name和age建立复合索引

explain select name from user_info where name like '%a%';
SIMPLE user_info index idx_n_a 53 6 16.67 Using where; Using index
explain select name,age from user_info where name like '%a%';
SIMPLE user_info index idx_n_a 53 6 16.67 Using where; Using index

以下两个例子是查询了不在复合索引中的列进而造成全表扫描

explain select name,age,address from user_info where name like '%a%';

SIMPLE user_info ALL 6 16.67 Using where
explain select * from user_info where name like '%a%';

SIMPLE user_info ALL 6 16.67 Using where

所以like走不走索引并不是绝对的,要看使用条件!
                        
原文链接:https://blog.csdn.net/weixin_29454029/article/details/113127748

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:/a/469006.html

如若内容造成侵权/违法违规/事实不符,请联系我们进行投诉反馈qq邮箱809451989@qq.com,一经查实,立即删除!

相关文章

IDEA集成Github

1.分析工程到 Gitee 新建一个项目初始化本地库添加到暂存区添加到本地库 在 Gitee 上可以查看到刚才提交的内容 2 .将本地代码 push 到远程库 新建一个码云仓库新建一个项目初始化本地库添加到暂存区添加到本地库 到码云仓库查看 3.pull 拉取远程库到本地库 在码云…

git基础命令(三)之远程命令

目录 基础概念origin git clonegit remote add 添加远程存储库git remote 显示远程存储库列表git pushgit pushgit push origin mastergit push origin --allgit push -f origin mastegit push origin --tags git fetch获取远程仓库的更新查看远程分支的更新情况拉取特定远程分…

【IntelliJ IDEA】Idea版本控制修改文件后要让文件夹也随之变颜色的操作步骤

idea怎么让修改文件后所在的文件夹也跟着变色 新版本旧版本不一样 旧版本 新版本 全局设置 刚才的setting设置只是对当前项目&#xff0c;想对以后的每个项目都设置上&#xff0c;就要在这个设置里面再设置一下。 效果图

2、字节码文件详解

2.1 Java虚拟机的组成 Java虚拟机主要分为以下几个组成部分&#xff1a; 类加载子系统&#xff1a;核心组件类加载器&#xff0c;负责将字节码文件中的内容加载到内存中。运行时数据区&#xff1a;JVM管理的内存&#xff0c;创建出来的对象、类的信息等等内容都会放在这块区域…

2.牛客---字符串中的最后一个字符的长度(Java版)

链接如下: https://www.nowcoder.com/practice/8c949ea5f36f422594b306a2300315da?tpId37&tqId21224&ru/exam/oj 描述 计算字符串最后一个单词的长度&#xff0c;单词以空格隔开&#xff0c;字符串长度小于5000。&#xff08;注&#xff1a;字符串末尾不以空格为结尾&…

openGauss学习笔记-245 openGauss性能调优-SQL调优-典型SQL调优点-算子级调优

文章目录 openGauss学习笔记-245 openGauss性能调优-SQL调优-典型SQL调优点-算子级调优245.1 算子级调优245.1.1 算子级调优介绍245.1.2 算子级调优示例 openGauss学习笔记-245 openGauss性能调优-SQL调优-典型SQL调优点-算子级调优 SQL调优是一个不断分析与尝试的过程&#x…

Sora 发布的意义能和 ChatGPT 相比吗?

个人觉得&#xff0c;Sora 的发布弥补了ChatGPT语言模型在视频内容领域的不足&#xff0c;简单来说&#xff0c;这两个模型均有自己的优势&#xff0c;ChatGPT是一种语言模型&#xff0c;可以理解和解释自然语言&#xff0c;而Sora是文字到视频转化的应用&#xff0c;将文本内容…

Python库Gym:打开机器学习与强化学习的大门

Python库Gym&#xff1a;打开机器学习与强化学习的大门 强化学习作为人工智能领域的重要分支&#xff0c;已经在各种领域展现出了巨大的潜力。为了帮助开发者更好地理解和应用强化学习算法&#xff0c;Python库Gym应运而生。Gym提供了一个开放且易于使用的环境&#xff0c;供开…

String类型详解

1. Java为何要创造String类 在C语言中,是没有String这个类型的,通常使用字符数组中存放一个个字符,再加上最后一个\0来表示/存放一个字符串.也可以使用一个字符指针指向字符串的首元素,直到遇到\0停止,再加上C语言头文件string.h中封装的函数,对于字符串的操作已经够用了. Java…

VUE3 十种组件通信的方式(附详细代码)

props 用途&#xff1a;可以实现父子组件、子父组件、甚至兄弟组件通信 父组件 <template><div><Son :money"money"></Son></div> </template><script setup lang"ts"> import Son from ./son.vue import { re…

企业公关新闻稿怎么写?媒介盒子揭秘

企业公关稿的目的在于宣传企业&#xff0c;化解企业负面舆论&#xff0c;想要写好新闻稿需要企业有专业的素养&#xff0c;深厚的笔力才能将新闻稿写得让读者愿意看&#xff0c;又能塑造企业正面形象&#xff0c;今天媒介盒子就来和大家聊聊&#xff1a;企业公关新闻稿怎么写。…

全氟己酮灭火片是什么?盘点自动灭火片的优缺点

全氟己酮灭火片是什么&#xff1f;全氟己酮灭火片是一种新型灭火材料&#xff0c;通过特殊的纳米微胶囊压缩技术&#xff0c;将环保高效的全氟己酮灭火剂压缩存储成薄片状&#xff0c;一旦温度达到一定阙值或发生明火&#xff0c;内部的全氟己酮灭火剂就会突破薄膜材料喷射出来…

防火墙的原理和配置

“防火墙”一词起源于建筑领域&#xff0c;用来隔离火灾&#xff0c;阻止火势从一个区域蔓延到另一个区域。引入到通信领域&#xff0c;防火墙这一具体设备通常用于两个网络之间有针对性的、逻辑意义上的隔离。这种隔离是选择性的&#xff0c;隔离“火”的蔓延&#xff0c;而又…

智慧物业是什么?房地产智慧物业大屏怎么做?

随着科技的飞速发展&#xff0c;智能化、信息化已经成为社会进步的显著标志。在这一背景下&#xff0c;智慧物业应运而生&#xff0c;成为现代社区管理的重要工具。它不仅提升了物业管理的效率&#xff0c;也为居民提供了更加便捷、安全的生活环境。 一、智慧物业是什么 智慧…

Vue+SpringBoot打造用户画像活动推荐系统

目录 一、摘要1.1 项目介绍1.2 项目录屏 二、功能模块2.1 数据中心模块2.2 兴趣标签模块2.3 活动档案模块2.4 活动报名模块2.5 活动留言模块 三、系统设计3.1 用例设计3.2 业务流程设计3.3 数据流程设计3.4 E-R图设计 四、系统展示五、核心代码5.1 查询兴趣标签5.2 查询活动推荐…

C++中虚表是什么

定义 虚函数&#xff08;Virtual Function&#xff09; 定义&#xff1a;类中使用virtual 关键字修饰的函数 叫做虚函数 语法&#xff1a; class Base { public:virtual void show() { cout << "Base show" << endl; } }; 虚函数表&#xff08;Virtual…

软考 网工 每日学习打卡 2024/3/18

学习内容 第8章 网络安全 本章主要讲解网络安全方面的基础知识和应用技术。针对考试应该掌握诸如数据加密、报文认 证、数字签名等基本理论&#xff0c;在此基础上深入理解网络安全协议的工作原理&#xff0c;并能够针对具体的 网络系统设计和实现简单的安全解决方案。 本章共有…

SpringBoot项目前端Vue访问后端(图片静态资源) 配置

静态资源配置 Configuration public class WebMvcConfig extends WebMvcConfigurationSupport {Value("${file.save-path}")private String fileSavePath;Overrideprotected void addResourceHandlers(ResourceHandlerRegistry registry) {//映射本地文件夹registry…

NineData推出SQL开发专业版:10数据源永久免费,助力企业数据库研发提效!

在数据库开发领域&#xff0c;选择合适的工具和服务至关重要。而NineData作为新一代的云原生智能数据管理平台&#xff0c;提供了数据复制、SQL 开发、数据备份以及数据对比等多种功能&#xff0c;帮助用户轻松实现混合云、多云数据源的统一管理。采用SaaS模式&#xff0c;无需…

QML入门

First Step with QML 每个QML文件都包含两部分&#xff1a;一个import部分和一个对象声明部分。对用户界面来说&#xff0c;最常用的类型和函数都定义在QtQuick 这个模块里。 编写HelloWorld 先从编程世界起点HelloWorld开始&#xff0c;这里&#xff0c;我们先不建立Qt Pro…