面试中常被提到的最左前缀匹配原则

最左前缀匹配原则:在MySQL确立团结索引时会遵守最左前缀匹配原则,即最左优先,在检索数据时从团结索引的最左边最先匹配。

  要想明白团结索引的最左匹配原则,先来明白下索引的底层原理。索引的底层是一颗B+树,那么团结索引的底层也就是一颗B+树,只不过团结索引的B+树节点中存储的是键值。由于构建一棵B+树只能凭据一个值来确定索引关系,以是数据库依赖团结索引最左的字段来构建。

举例:建立一个(a,b)的团结索引,那么它的索引树就是下图的样子。

面试中常被提到的最左前缀匹配原则

   可以看到a的值是有顺序的,1,1,2,2,3,3,而b的值是没有顺序的1,2,1,4,1,2。然则我们又可发现a在等值的情形下,b值又是按顺序排列的,然则这种顺序是相对的。这是由于MySQL建立团结索引的规则是首先会对团结索引的最左边第一个字段排序,在第一个字段的排序基础上,然后在对第二个字段举行排序。以是b=2这种查询条件没有办法行使索引。

  由于整个历程是基于explain效果剖析的,那接下来在了解下explain中的type字段和key_lef字段。

  1.type联接类型。下面给出种种联接类型,根据从最佳类型到最坏类型举行排序:(重点看ref,rang,index)

    system:表只有一行纪录(即是系统表),这是const类型的特例,平时不会泛起,可以忽略不计
    const:示意通过索引一次就找到了,const用于对照primary key 或者 unique索引。由于只需匹配一行数据,所有很快。若是将主键置于where列表中,mysql就能将该查询转换为一个const
    eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条纪录与之匹配。常见于主键 或 唯一索引扫描。
    注重:ALL全表扫描的表纪录最少的表如t1表
    ref:非唯一性索引扫描,返回匹配某个单独值的所有行。本质是也是一种索引接见,它返回所有匹配某个单独值的行,然而他可能会找到多个相符条件的行,以是它应该属于查找和扫描的混合体。
    range:只检索给定局限的行,使用一个索引来选择行。key列显示使用了谁人索引。一样平常就是在where语句中泛起了bettween、<、>、in等的查询。这种索引列上的局限扫描比全索引扫描要好。只需要最先于某个点,竣事于另一个点,不用扫描所有索引。
    index:Full Index Scan,index与ALL区别为index类型只遍历索引树。这通常为ALL块,应为索引文件通常比数据文件小。(Index与ALL虽然都是读全表,但index是从索引中读取,而ALL是从硬盘读取)
    ALL:Full Table Scan,遍历全表以找到匹配的行

  2.key_len显示MySQL现实决议使用的索引的长度。若是索引是NULL,则长度为NULL。若是不是NULL,则为使用的索引的长度。以是通过此字段就可推断出使用了谁人索引。

    盘算规则:

    1.定长字段,int占用4个字节,date占用3个字节,char(n)占用n个字符。

    2.变长字段varchar(n),则占用n个字符+两个字节。

    3.差别的字符集,一个字符占用的字节数是差别的。Latin1编码的,一个字符占用一个字节,gdk编码的,一个字符占用两个字节,utf-8编码的,一个字符占用三个字节。

    (由于我数据库使用的是Latin1编码的花样,以是在后面的盘算中,一个字符按一个字节算)

    4.对于所有的索引字段,若是设置为NULL,则还需要1个字节。

接下来进入正题!!!

示例:

首先建立一个表

面试中常被提到的最左前缀匹配原则

 该表中对id列.name列.age列确立了一个团结索引 id_name_age_index,现实上相当于确立了三个索引(id)(id_name)(id_name_age)。

下面先容下可能会使用到该索引的几种情形:

1.全值匹配查询时

面试中常被提到的最左前缀匹配原则

面试中常被提到的最左前缀匹配原则

面试中常被提到的最左前缀匹配原则

  通过考察上面的效果图可知,where后面的查询条件,岂论是使用(id,age,name)(name,id,age)照样(age,name,id)顺序,在查询时都使用到了团结索引,可能有同砚会疑惑,为什么底下两个的搜索条件明显没有根据团结索引从左到右举行匹配,却也使用到了团结索引? 这是由于MySQL中有查询优化器explain,以是sql语句中字段的顺序不需要和团结索引界说的字段顺序相同,查询优化器会判断纠正这条SQL语句以什么样的顺序执行效率高,最后才气天生真正的执行计划,以是岂论以何种顺序都可使用到团结索引。另外通过考察上面三个图中的key_len字段,也可说明在搜索时使用的团结索引中的(id_name_age)索引,由于id为int型,允许null,以是占5个字节,name为char(10),允许null,又使用的是latin1编码,以是占11个字节,age为int型允许null,以是也占用5个字节,以是该索引长度为21(5+11+5),而上面key_len的值也正好为21,可证实使用的(id_name_age)索引。

2.匹配最左边的列时

 

大清朝早亡了,还没有入门 Spring Boot?

面试中常被提到的最左前缀匹配原则

  该搜索是遵照最左匹配原则的,通过key字段也可知,在搜索历程中使用到了团结索引,且使用的是团结索引中的(id)索引,由于key_len字段值为5,而id索引的长度正好为5(由于id为int型,允许null,以是占5个字节)。

面试中常被提到的最左前缀匹配原则

  由于id到name是从左边依次往右边匹配,这两个字段中的值都是有序的,以是也遵照最左匹配原则,通过key字段可知,在搜索历程中也使用到了团结索引,但使用的是团结索引中的(id_name)索引,由于key_len字段值为16,而(id_name)索引的长度正好为16(由于id为int型,允许null,以是占5个字节,name为char(10),允许null,又使用的是latin1编码,以是占11个字节)。

面试中常被提到的最左前缀匹配原则

  由于上面三个搜索都是从最左边id依次向右最先匹配的,以是都用到了id_name_age_index团结索引。

  那若是不是依次匹配呢?

面试中常被提到的最左前缀匹配原则

  通过key字段可知,在搜索历程中也使用到了团结索引,但使用的是团结索引中的(id)索引,从key_len字段也可知。由于团结索引树是根据id字段建立的,但age相对于id来说是无序的,只有id只有序的,以是他只能使用团结索引中的id索引。

面试中常被提到的最左前缀匹配原则

  通过考察发现上面key字段发现在搜索中也使用了id_name_age_index索引,可能许多同砚就会疑惑它并没有遵守最左匹配原则,按道剖析索引失效,为什么也使用到了团结索引?由于没有从id最先匹配,且name单独来说是无序的,以是它确实不遵照最左匹配原则,然而从type字段可知,它虽然使用了团结索引,然则它是对整个索引树举行了扫描,正好匹配到该索引,与最左匹配原则无关,一样平常只要是某团结索引的一部分,但又不遵照最左匹配原则时,都可能会接纳index类型的方式扫描,但它的效率远不如最做匹配原则的查询效率高,index类型类型的扫描方式是从索引第一个字段一个一个的查找,直到找到相符的某个索引,与all差别的是,index是对所有索引树举行扫描,而all是对整个磁盘的数据举行全表扫描。

面试中常被提到的最左前缀匹配原则

面试中常被提到的最左前缀匹配原则

   这两个效果跟上面的是同样的原理,由于它们都没有从最左边最先匹配,以是没有用到团结索引,使用的都是index全索引扫描。

3.匹配列前缀

  若是id是字符型,那么前缀匹配用的是索引,中坠和后缀用的是全表扫描。

select * from staffs where id like 'A%';//前缀都是排好序的,使用的都是团结索引
select * from staffs where id like '%A%';//全表查询
select * from staffs where id like '%A';//全表查询

4.匹配局限值

面试中常被提到的最左前缀匹配原则

   在匹配的历程中遇到<>=号,就会住手匹配,但id自己就是有序的,以是通过possible_keys字段和key_len 字段可知,在该搜索历程中使用了团结索引的id索引(由于id为int型,允许null,以是占5个字节),且举行的是rang局限查询。

面试中常被提到的最左前缀匹配原则

  由于不遵照最左匹配原则,且在id<4的局限中,age是无序的,以是使用的是index全索引扫描。

面试中常被提到的最左前缀匹配原则

   不遵照最左匹配原则,但在数据库中id<2的只有一条(id),以是在id<2的局限中,age是有序的,以是使用的是rang局限查询。

面试中常被提到的最左前缀匹配原则

   不遵照最左匹配原则,而age又是无序的,以是举行的全索引扫描。

5.准确匹配第一列并局限匹配其他某一列

面试中常被提到的最左前缀匹配原则

  由于搜索中有id=1,以是在id局限内age是无序的,以是只使用了团结索引中的id索引。

 

原创文章,作者:870t新闻网,如若转载,请注明出处:https://www.870t.com/archives/10784.html