学习
实践
活动
工具
TVP
写文章
专栏首页用户7621540的专栏索引失效原理,终于有人讲明白了

索引失效原理,终于有人讲明白了

前言

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

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

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

单值索引B+树图

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

联合索引

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

MySQL技术内幕 InnoDB存储引擎 第2版

从本质上来说,联合索引也是一个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字段的。

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

范围查询右边失效原理

举例

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

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

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

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

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

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

开始分析

一、%号放右边(前缀)

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

二、%号放左边

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

三、两个%%号

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

总结

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

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

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

文章分享自微信公众号:
IT老哥

本文参与 腾讯云自媒体分享计划 ,欢迎热爱写作的你一起参与!

作者:IT老哥
原始发表时间:2020-09-17
如有侵权,请联系 cloudcommunity@tencent.com 删除。
登录 后参与评论
0 条评论

相关文章

  • 终于有人把搜索引擎讲明白了

    全世界每年产生1EB到2EB (1EB≈1018B)信息,相当于地球上每个人每年大概产生250MB信息。其中,纸质信息仅占所有信息的0.03%。静态网页有上百亿...

    华章科技
  • 终于有人把DevOps讲明白了

    导读:DevOps中的Dev指的是Development(开发),Ops指的是Operations(运维),用一句话来说,DevOps就是打通开发运维的壁垒,实...

    华章科技
  • 终于有人将TDMQ讲明白了

    TDMQ 是腾讯云基于 Apache Pulsar 开源项目开发的消息队列产品,主打金融等行业应用,适用于对消息通讯要求高可靠、强一致的场景。TDMQ 在保障高...

    腾讯专有云
  • 终于有人把Knative讲明白了

    导读:Knative是Google在2018的Google Cloud Next大会上发布的一款基于Kubernetes的Serverless框架。

    华章科技
  • 终于有人把数据挖掘讲明白了

    导读:数据挖掘是一种发现知识的手段。数据挖掘要求数据分析师通过合理的方法,从数据中获取与挖掘项目相关的知识。

    华章科技
  • 终于有人把云计算讲明白了

    导读:对于云计算的阐述,我们将从云计算概念、原理、分类、特点和应用这5个方面展开。

    华章科技
  • 终于有人把数据挖掘讲明白了

    数据挖掘(Data Mining)应该是一门大家都听说过,但又不太容易说清楚的课程。在数据科学领域,乃至在更大的计算机科学领域,数据挖掘就好比山东蓝翔,大家不一...

    木羊
  • 终于有人把线性回归讲明白了

    导读:本文将介绍什么是回归问题、解决回归问题的基本思路步骤和用机器学习模型解决回归问题的基本原理,以及如何用线性模型解决回归问题。

    CDA数据分析师
  • 终于有人把辛普森悖论讲明白了

    在做重大决策时,我们总会参考一些统计数据,比如高考前关注学校的录取率,择业时参考各个行业的就业率等。统计数字可以帮助我们比较这些对象的优劣,做出更加合理的决定。...

    华章科技
  • 终于有人把 Elasticsearch 原理讲透了!

    搜索是现代软件必备的一项基础功能,而 Elasticsearch 就是一款功能强大的开源分布式搜索与数据分析引擎。

    芋道源码
  • 终于有人把 Elasticsearch 原理讲透了!

    搜索是现代软件必备的一项基础功能,而 Elasticsearch 就是一款功能强大的开源分布式搜索与数据分析引擎。

    Spark学习技巧
  • 终于有人把标签设计讲明白了

    导读:标签是根据业务场景的需求,通过对目标对象(含静态、动态特性)运用抽象、归纳、推理等算法得到的高度精练的特征标识,用于差异化管理与决策。

    华章科技
  • XGBoost超详细推导,终于有人讲明白了!

    相信看到这篇文章的各位对XGBoost都不陌生,的确,XGBoost不仅是各大数据科学比赛的必杀武器,在实际工作中,XGBoost也在被各大公司广泛地使用。

    石晓文
  • 终于有人把Scrapy爬虫框架讲明白了

    导读:Scrapy由Python语言编写,是一个快速、高层次的屏幕抓取和Web抓取框架,用于抓取Web站点并从页面中提取出结构化的数据。Scrapy用途广泛,可...

    华章科技
  • XGBoost超详细推导,终于有人讲明白了!

    相信看到这篇文章的各位对XGBoost都不陌生,的确,XGBoost不仅是各大数据科学比赛的必杀武器,在实际工作中,XGBoost也在被各大公司广泛地使用。

    Datawhale
  • 终于有人把 CountDownLatch,CyclicBarrier,Semaphore 说明白了!

    在 JUC 下包含了一些常用的同步工具类,今天就来详细介绍一下,CountDownLatch,CyclicBarrier,Semaphore 的使用方法以及它们...

    用户1263954
  • 终于有人将进程间通信讲明白了

    进程间通信(Inter-Process Communication,IPC)则是多进程协作的基础。一般而言,IPC至少需要两方(如两个进程)参与。根据信息流动的...

    肉眼品世界
  • 终于有人把 CountDownLatch,CyclicBarrier,Semaphore 说明白了!

    在 JUC 下包含了一些常用的同步工具类,今天就来详细介绍一下,CountDownLatch,CyclicBarrier,Semaphore 的使用方法以及它们...

    敖丙

扫码关注腾讯云开发者

领取腾讯云代金券