魅力峰值

导航

sql联合索引左匹配的原则、索引失效的情况,以及索引创建的原则

文章来自:https://www.cnblogs.com/aaabbbcccddd/p/14864982.html

https://blog.csdn.net/Alice_whj/article/details/122794075

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

 

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

从本质上来说,联合索引也是一个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>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+树上进行有序的查找,查找首字母符合要求的数据。所以有些时候可以用到索引。
二、%号放右边
是匹配字符串尾部的数据,我们上面说了排序规则,尾部的字母是没有顺序的,所以不能按照索引顺序查询,就用不到索引。
三、两个%%号
这个是查询任意位置的字母满足条件即可,只有首字母是进行索引排序的,其他位置的字母都是相对无序的,所以查找任意位置的字母是用不上索引的。

 

数据库联合索引失效

联合索引的结构
假设这是一个多列索引(col1, col2,col3),对于叶子节点

联合索引(col1, col2,col3)也是一棵B+Tree,其非叶子节点存储的是第一个关键字的索引,而叶节点存储的则是三个关键字col1、col2、col3三个关键字的数据,且按照col1、col2、col3的顺序进行排序。

col1表示的是年龄,col2表示的是姓氏,col3表示的是名字
在这里插入图片描述
如图,联合索引(年龄, 姓氏,名字),叶节点上data域存储的是三个关键字的数据。且是按照年龄、姓氏、名字的顺序排列的。

因此我对联合索引结构的理解就是B+Tree是按照第一个关键字进行索引,然后在叶子节点上按照第一个关键字、第二个关键字、第三个关键字…进行排序。

问:为什么遇到范围查询列之后的列索引失效
答:如上图 select * from table where col1=1 and col2 between ‘李’ and ‘黄’ and col3 = ‘安’;col3失效,因为前两个查询条件后锁定前5行,而对于前5行,col3已经不是有序排列,不符合索引的要求了。

在建立联合索引时,都遵循从左往右的优先级,最左优先,当出现范围查询(> < between like等等)时停止匹配。

复合索引失效的几种情况:
1、违背最左缀原则:
a、b、c建立索引
有效索引:
① a
②a,b
③a,b,c
解释:复合索引建立的存储结构,第二个是依赖于在第一个索引使用的基础上,以此类推。
2、like模糊查询,使用最左精确匹配l ike ‘name%’ ,其他方式全表扫描。
3、条件查询中使用 OR、> 、<、!=、between and、where age+1=10、 where name is null 等
在这里插入图片描述

总结:
1.联合索引的使用在写where条件的顺序无关,mysql查询分析会进行优化而使用索引。但是减轻查询分析器的压力,最好和索引的从左到右的顺序一致。
2.使用等值查询,多列同时查询,索引会一直传递并生效。因此等值查询效率最好。
3.索引查找遵循最左侧原则。但是遇到范围查询列之后的列索引失效。
4.排序也能使用索引,合理使用索引排序,避免出现file sort。

在这里插入图片描述

posted on 2022-08-10 23:38  魅力峰值  阅读(805)  评论(0编辑  收藏  举报