当前位置: 首页 > 科技观察

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

时间:2023-03-21 00:57:37 科技观察

索引失效的底层原理分析。时隔这么多年,终于有人解释清楚了。什么情况下索引会失效。比如:不遵循最好的左前缀规则,范围查询右侧会失效,like查询不使用索引等等。但是没有人会告诉你索引失效的原理是什么。哥哥今天就告诉你,让你知道。当然,我们需要知道为什么。Single-valueindexB+treegraphsingle-valueindex在B+树结构中,一个节点只存储一个键值对联合索引来启动一个图,一个联合索引由图的a字段和b字段组成数据库。联合索引本质上也是一颗B+树。与单值索引不同的是,联合索引的键值对不是1,而是大于1。a,b排序分析aorder:1,1,2,2,3,3border:1,2,1,4,1,2可以发现a字段是有序排列,b字段是无序排列(因为B+树只能选择一个字段来构建有序树)一不小心,你会发现当a相等时,b字段是有序的。想一想,我们在编程中通常对两个字段进行排序,是不是应该先按第一个字段排序,如果第一个字段相等,再用第二个字段排序。B+树中也使用了这种排序方式。分析最佳左前缀的原理首先我们举个例子,遵循最佳左前缀的规则select*fromtestTablewherea=1andb=2分析如下:首先,a字段在B+树上是有序的,所以我们可以用二分查找法定位到一个=1的位置。其次,当a确定后,b相对有序。因为有顺序,b=2的位置也可以通过二分查找的方式找到。我们来看一个不遵循最佳左前缀的例子select*fromtestTablewhereb=2分析如下:让我们回忆一下b有顺序的前提:当a确定的时候。既然你的a是飞的,那b的顺序肯定是不能确定的。在无序的B+树上,不能使用二分查找来定位b字段。所以这个时候,并没有使用索引。你明白吗?范围查询select*fromtestTablewherea>1andb=2右边失败原理的一个例子分析如下:首先,a字段在B+树上是有序的,所以可以用二分查找的方式定位到1,然后取出所有大于1的数据,一个可以用Index。b有序的前提是a是定值,所以现在a的值大于1,可能有10个a大于1,也可能有100个a。在大于1的部分a的B+树中,b字段是无序的(开头的一张图),所以在无序的B+树中无法通过二分查找来查询b,b也不能使用索引。like索引失败的原理wherenamelike"a%"wherenamelike"%a%"wherenamelike"%a"我们先来了解一下%%放在右边的用途,表示查询以"a"开头的数据,如:abctwo%%,表示查询数据中包含“a”的数据,如:cab,cba,abc左边的%,表示以“a”结尾的查询数据,如cbawhy%在右边而且有时候索引%可以用右边调用:prefix%%调用:infix%左边调用:suffix是的,这里还是bestleftprefixrule的概念。可以看到上面的B+树是由字符串组成的。字符串排序方法:先按第一个字母排序,如果第一个字母相同,则按第二个字母排序。..以此类推开始分析1.把%号放在右边(前缀)。因为B+树的索引顺序是按照首字母的大小排序的,所以前缀匹配也是首字母。因此,可以对B+树进行有序查找,找到首字母符合要求的数据。所以有时可以使用索引。2.把%号放在左边,匹配字符串末尾的数据。我们在上面提到了排序规则。末尾的字母没有顺序,所以不能按照索引顺序查询,也不会使用索引。3、两个%%符号用于搜索任意位置的字母是否满足条件。只有第一个字母被索引和排序。其他位置的字母是相对无序的,所以查找任何位置的字母都不用索引。的。综上所述,这里为大家分析一些经典的索引失败案例,希望能引发大家的思考,通过这些案例,可以了解其他情况下索引失败的原理。后面会讲到如何通过索引查询数据的全过程,以及InnoDB和MyISAM引擎底层索引实现的区别。授人以鱼不如授人以渔。这一刻,弟弟觉得自己很特别。