索引失效底层原理分析,这么多年终于有人讲清楚了

标签: java mysql mysql索引 mysql索引优化 索引 | 发表时间:2020-10-16 10:40 | 作者:公众号_IT老哥
出处:https://segmentfault.com/blogs

前言

吊打面试官又来啦,今天我们讲讲MySQL 索引为什么会失效,很多文章和培训机构的教程,都只会告诉你,在什么情况下索引会失效。

比如:没遵循最佳左前缀法则、范围查询的右边会失效、like查询用不到索引等等

但是没有一个人告诉你, 索引失效的原理是什么, 老哥今天就告诉大家,让你们 知其然,还要 知其所以然

image.png

单值索引B+树图

单值索引在B+树的结构里,一个节点只存一个键值对

image.png

联合索引

开局一张图,由数据库的 a字段和 b字段组成一个 联合索引
image.png

从本质上来说,联合索引也是一个B+树,和单值索引不同的是,联合索引的键值对不是1,而是大于1个。

a, b 排序分析

a顺序:1,1,2,2,3,3

b顺序:1,2,1,4,1,2

大家可以发现a字段是有序排列,b字段是无序排列(因为B+树只能选一个字段来构建有序的树)

一不小心又会发现,在a相等的情况下,b字段是有序的。

大家想想平时编程中我们要对两个字段排序,是不是先按照第一个字段排序,如果第一个字段出现相等的情况,就用第二个字段排序。这个排序方式同样被用到了B+树里。

分析最佳左前缀原理

先举一个遵循最佳左前缀法则的例子

  select * from testTable where a=1 and b=2

分析如下:

首先a字段在B+树上是有序的,所以我们可以通过二分查找法来定位到a=1的位置。

其次在a确定的情况下,b是相对有序的,因为有序,所以同样可以通过二分查找法找到b=2的位置。

再来看看不遵循最佳左前缀的例子

  select * from testTable where b=2

分析如下:

我们来回想一下b有顺序的前提:在a确定的情况下。

现在你的a都飞了,那b肯定是不能确定顺序的,在一个无序的B+树上是无法用二分查找来定位到b字段的。

所以这个时候,是用不上索引的。大家懂了吗?
image.png

范围查询右边失效原理

举例

  select * from testTable where a>1 and b=2

分析如下:

首先a字段在B+树上是有序的,所以可以用二分查找法定位到1,然后将所有大于1的数据取出来,a可以用到索引。

b有序的前提是a是确定的值,那么现在a的值是取大于1的,可能有10个大于1的a,也可能有一百个a。

大于1的a那部分的B+树里,b字段是无序的(开局一张图),所以b不能在无序的B+树里用二分查找来查询,b用不到索引。

like索引失效原理

  where name like "a%"

where name like "%a%"

where name like "%a"

我们先来了解一下%的用途

  • %放在右边,代表查询以"a"开头的数据,如:abc
  • 两个%%,代表查询数据中包含"a"的数据,如:cab、cba、abc
  • %放在左边,代表查询以"a"为结尾的数据,如cba

为什么%放在右边有时候能用到索引

  • %放右边叫做: 前缀
  • %%叫做: 中缀
  • %放在左边叫做: 后缀

没错,这里依然是最佳左前缀法则这个概念

image.png

大家可以看到,上面的B+树是由字符串组成的。

字符串的排序方式:先按照第一个字母排序,如果第一个字母相同,就按照第二个字母排序。。。以此类推

开始分析

一、%号放右边(前缀)

由于B+树的索引顺序,是按照首字母的大小进行排序,前缀匹配又是匹配首字母。所以可以在B+树上进行有序的查找,查找首字母符合要求的数据。所以有些时候可以用到索引。

二、%号放左边

是匹配字符串尾部的数据,我们上面说了排序规则,尾部的字母是没有顺序的,所以不能按照索引顺序查询,就用不到索引。

三、两个%%号

这个是查询任意位置的字母满足条件即可,只有首字母是进行索引排序的,其他位置的字母都是相对无序的,所以查找任意位置的字母是用不上索引的。

总结

这里把一些经典的索引失效案例给大家分析了,希望能引发大家的思考,能够通过这些案例,明白其他情况索引失效的原理。

之后我们在讲讲,如何通过索引查询到数据整个流程, InnoDBMyISAM两个引擎底层索引的实现区别。

授人以鱼不如授人以渔,这一瞬间,老哥感觉自己特别的 shuai

image.png

关注我,一个自学进入大厂的高级Java开发工程师

相关 [索引 原理 分析] 推荐:

索引原理

- - ITeye博客
索引是存储引擎用于快速找到记录的一种数据结构. 也就会说索引也是一种数据结构,也占用磁盘空间. 索引是对查询优化最有效的手段,可以将查询提升几个数量级,相当牛掰啊. 1)索引大大减少了服务器需要扫描的数据量. 2)索引可以帮助服务器避免排序和临时表. 3)索引可以将随机IO变为顺序IO. 数据库索引可以想象成一本书的目录,如果想在一本书中找到某个主题,那么先到书的目录中找到这个主题,然后根据目录提供的页码,找到要找的主题.

[转][转]由浅入深探究mysql索引结构原理、性能分析与优化

- - heiyeluren的Blog
由浅入深探究mysql索引结构原理、性能分析与优化. 来源: http://www.phpben.com/?post=74. 第二部分:MYISAM和INNODB索引结构. 1、 简单介绍B-tree B+ tree树. 4、 MyisAM索引与InnoDB索引相比较. (1)     最左前缀原则.

索引失效底层原理分析,这么多年终于有人讲清楚了

- - SegmentFault 最新的文章
吊打面试官又来啦,今天我们讲讲MySQL 索引为什么会失效,很多文章和培训机构的教程,都只会告诉你,在什么情况下索引会失效. 比如:没遵循最佳左前缀法则、范围查询的右边会失效、like查询用不到索引等等. 但是没有一个人告诉你, 索引失效的原理是什么, 老哥今天就告诉大家,让你们 知其然,还要 知其所以然.

NumericField&NumericRangeQuery原理分析

- - 搜索引擎技术博客
NumericField和NumericRangeQuery是Lucene. 针对数值型区间查询的优化方案. 和NumbericRanageQuery. 的实现原理之前,对于Lucene范围查询的实现和概念可以参考博文《TermRangeQuery源码解析》一文.       从Lucene 2.9 开始,提供对数字范围的支持,然而欲使用此查询,必须使用NumericField 添加域,使用Lucene原生API:.

[原]CAS原理分析

- -
1、悲观锁:假定会发生并发冲突,屏蔽一切可能违反数据完整性的操作. 悲观锁的实现,往往依靠底层提供的锁机制;悲观锁会导致其它所有需要锁的线程挂起,等待持有锁的线程释放锁. 2、乐观锁:假设不会发生并发冲突,每次不加锁而是假设没有冲突而去完成某项操作,只在提交操作时检查是否违反数据完整性. 如果因为冲突失败就重试,直到成功为止.

loosejar原理简要分析

- - CSDN博客系统运维推荐文章
 loosejar这个小工具可以动态分析出应用中有每个jar包的实际使用情况,详情请参阅 《通过loosejar清理应用中冗余的jar包》基本原理是利用instrumentation的特性用 Instrumentation,开发者可以构建一个独立于应用程序的代理程序(Agent),用来监测和协助运行在 JVM 上的程序,甚至能够替换和修改某些类的定义.

JVM原理分析笔记

- - Java - 编程语言 - ITeye博客
1.Javac编译器的作用. 将符合Java语言规范的源代码转化成符合Java虚拟机规范的Java字节码. 2.编译器主要的几个处理阶段. 词法分析、语法分析、语义分析和代码生成,基于访问者模式来遍历语法树的过程. 二.ClassLoader. 将Class加载到JVM中,审查每个类应该由谁加载,将Class字节码重新解析成JVM统一要求的对象格式.

Java ClassLoader原理分析

- - Java - 编程语言 - ITeye博客
一、JDK默认提供的三个ClassLoader. JDK 默认提供了如下几种ClassLoader. Bootstrp加载器是用C++语言写的,它是在Java虚拟机启动后初始化的,它主要负责加载 %JAVA_HOME%/jre/lib, -Xbootclasspath参数指定的路径以及 %JAVA_HOME%/jre/classes中的类.

[转]GeoHash原理分析

- - tenfyguo的技术专栏
机机是个好动又好学的孩子,平日里就喜欢拿着手机地图点点按按来查询一些好玩的东西. 某一天机机到北海公园游玩,肚肚饿了,于是乎打开手机地图,搜索北海公园附近的餐馆,并选了其中一家用餐. 饭饱之后机机开始反思了,地图后台如何根据自己所在位置查询来查询附近餐馆的呢. 苦思冥想了半天,机机想出了个方法:计算所在位置P与北京所有餐馆的距离,然后返回距离<=1000米的餐馆.

mysql索引原理之B+/-Tree

- - CSDN博客架构设计推荐文章
索引,是为了更快的查询数据,查询算法有很多,对应的数据结构也不少,数据库常用的索引数据结构一般为B+Tree. 关于B-Tree的官方定义个人觉得比较难懂,通俗一点就是举个例子. 假如:一本英文字典,单词+详细解释组成了一条记录,现在需要索引单词,那么以单词为key,单词+详细解释为data,B-Tree就是以一个二元组{key,data}来定义一条记录.