精华内容
下载资源
问答
  • 联合索引在B+Tree上的存储结构及数据查找方式

    千次阅读 多人点赞 2020-08-14 09:49:45
    联合索引查找方式 为什么会有最左前缀匹配原则 在分享这篇文章之前,我在网上查了关于MySQL联合索引在B+树上的存储结构这个问题,翻阅了很多博客和技术文章,其中有几篇讲述的与事实相悖。具体如下: 很多...

    最困难的事情就是认识自己!

    个人网站,欢迎访问!

    前言:

    本篇文章主要是阐述下 联合索引 在 B+Tree 上的实际存储结构。

    本文主要讲解的内容有:

    • 联合索引在B+树上的存储结构

    • 联合索引的查找方式

    • 为什么会有最左前缀匹配原则

    在分享这篇文章之前,我在网上查了关于MySQL联合索引在B+树上的存储结构这个问题,翻阅了很多博客和技术文章,其中有几篇讲述的与事实相悖。具体如下:

    很多博客中都是说:联合索引在B+树上的 非叶子节点 中只会存储 联合索引 中的第一个索引字段 的值,联合索引的其余索引字段的值只会出现在 B+树 的 叶子节点 中 。(其实这句话是不对的)

    如下图,就是 错误的 联合索引的 B+树 存储结构图:

    庆幸的是通过查询发现有一条是来自思否社区的关于【联合索引 在 B+Tree 上的存储结构?】问答,有答主回答了这个问题,并贴出了一篇文章和一张图以及一句简单的描述。 PS:贴出的文章链接已经打不开了

    所以在这样的条件下本篇文章就诞生了。

    联合索引存储结构:

    下面就引用思否社区的这个问答来展开我们今天要讨论的联合索引的存储结构的问题。

    来自思否的提问,联合索引的存储结构
    (https://segmentfault.com/q/1010000017579884)
    有码友回答如下:

    联合索引 bcd , 在索引树中的样子如下图 , 在比较的过程中 ,先判断 b 再判断 c 然后是 d :

    由于回答只有这么一张图一句话,可能会让大家有点看不懂,所以我们就借助前人的肩膀用这个例子来更加细致的讲探寻一下联合索引在B+树上的存储结构吧。

    首先,有一个T1表, 然后表T1有字段a,b,c,d,e,其中a是主键,除e为varchar其余为int类型,并创建了一个联合索引idx_t1_bcd(b,c,d),然后b、c、d三列作为联合索引,在B+树上的结构正如上图所示。联合索引的所有索引列都出现在索引数上,并依次比较三列的大小。上图树高只有两层不容易理解,下面是假设的表数据以及我对其联合索引在B+树上的结构图的改进。 PS:基于InnoDB存储引擎。

    index(b、c、d)联合索引在B+树上的结构图如下:

    T1表中的数据如下图:( 上图 B+树 中的数据就来自下图

    通过这俩图我们心里对联合索引在B+树上的存储结构就有了个大概的认识。下面用我的语言为大家解释一下吧。

    我们先看T1表,他的主键暂且我们将它设为整型自增的 ,InnoDB会使用主键索引在B+树维护索引和数据文件,然后我们创建了一个联合索引(b,c,d)也会生成一个索引树,同样是B+树的结构,只不过它的 data部分 存储的是联合索引所在行记录的主键值 (上图叶子节点紫色背景部分) 。为什么是 主键值,而不是 整个行记录呢? 因为这个 联合索引 是个 非聚簇索引

    好了大致情况都介绍完了。下面我们结合这俩图来解释一下。

    对于联合索引来说只不过比单值索引多了几列,而这些索引列全都出现在索引树上。对于联合索引,存储引擎会首先根据第一个索引列排序,如上图我们可以单看第一个索引列,如,1 1 5 12 13…它是单调递增的;如果第一列相等则再根据第二列排序,依次类推就构成了上图的索引树,上图中的1 1 4 ,1 1 5以及13 12 4, 13 16 1, 13 16 5就可以说明这种情况。

    联合索引具体查找步骤:

    当我们的SQL语言可以应用到索引的时候,比如 select * from T1 where b = 12 and c = 14 and d = 3 ;也就是T1表中a列为4的这条记录。

    查找步骤具体如下:

    1. 存储引擎首先从根节点(一般常驻内存)开始查找,第一个索引的第一个索引列为1,12大于1,第二个索引的第一个索引列为56,12小于56,于是从这俩索引的中间读到下一个节点的磁盘文件地址(此处实际上是存在一个指针的,指向的是下一个节点的磁盘位置)。
    2. 进行一次磁盘IO,将此节点值加载后内存中,然后根据第一步一样进行判断,发现 数据都是匹配的,然后根据指针将此联合索引值所在的叶子节点也从磁盘中加载后内存,此时又发生了一次磁盘IO,最终根据叶子节点中索引值关联的 主键值
    3. 根据主键值 回表 去主键索引树(聚簇索引)中查询具体的行记录。

    联合索引的最左前缀原则:

    之所以会有最左前缀匹配原则和联合索引的索引构建方式及存储结构是有关系的。

    首先我们创建的idx_t1_bcd(b,c,d)索引,相当于创建了(b)、(b、c)(b、c、d)三个索引,看完下面你就知道为什么相当于创建了三个索引。

    我们看,联合索引是首先使用多列索引的第一列构建的索引树,用上面idx_t1_bcd(b,c,d)的例子就是优先使用b列构建,当b列值相等时再以c列排序,若c列的值也相等则以d列排序。我们可以取出索引树的叶子节点看一下。

    索引的第一列也就是b列可以说是从左到右单调递增的,但我们看c列和d列并没有这个特性,它们只能在b列值相等的情况下这个小范围内递增,如第一叶子节点的第1、2个元素和第二个叶子节点的后三个元素。

    由于联合索引是上述那样的索引构建方式及存储结构,所以联合索引只能从多列索引的第一列开始查找。所以如果你的查找条件不包含b列如(c,d)、(c)、(d)是无法应用缓存的,以及跨列也是无法完全用到索引如(b,d),只会用到b列索引。

    这就像我们的电话本一样,有名和姓以及电话,名和姓就是联合索引。在姓可以以姓的首字母排序,姓的首字母相同的情况下,再以名的首字母排序。

    如:

    M
        毛 不易   178********
        马 化腾   183********
        马 云     188********
    Z
        张 杰     189********
        张 靓颖   138********
        张 艺兴   176********  
    

    我们知道名和姓是很快就能够从姓的首字母索引定位到姓,然后定位到名,进而找到电话号码,因为所有的姓从上到下按照既定的规则(首字母排序)是有序的,而名是在姓的首字母一定的条件下也是按照名的首字母排序的,但是整体来看,所有的名放在一起是无序的,所以如果只知道名查找起来就比较慢,因为无法用已排好的结构快速查找。

    到这里大家是否明白了为啥会有最左前缀匹配原则了吧。

    实践:

    如下列举一些SQL的索引使用情况:

    select * from T1 where b = 12 and c = 14 and d = 3;-- 全值索引匹配 三列都用到
    select * from T1 where b = 12 and c = 14 and e = 'xml';-- 应用到两列索引
    select * from T1 where b = 12 and e = 'xml';-- 应用到一列索引
    select * from T1 where b = 12  and c >= 14 and e = 'xml';-- 应用到一列索引及索引条件下推优化
    select * from T1 where b = 12  and d = 3;-- 应用到一列索引  因为不能跨列使用索引 没有c列 连不上
    select * from T1 where c = 14  and d = 3;-- 无法应用索引,违背最左匹配原则
    
    

    后记:

    到这里MySQL索引的联合索引的存储结构及查找方式就讲完了,本人能力有限,也是站着前人的肩膀上创作的此文,因为看到搜索引擎的搜索结果前几个技术文章中有存在讲述不清或讲述有误的地方,所以自己才总结出这篇文章分享给大家,如有不对的地方一定要指正哦,谢谢了。

    通过本文了解到了联合索引的存储结构及查找方式,那在项目中该怎么创建索引呢?请参考此文:项目中该如何创建索引?

    不要忘记留下你学习的足迹 [点赞 + 收藏 + 评论]嘿嘿ヾ

    一切看文章不点赞都是“耍流氓”,嘿嘿ヾ(◍°∇°◍)ノ゙!开个玩笑,动一动你的小手,点赞就完事了,你每个人出一份力量(点赞 + 评论)就会让更多的学习者加入进来!非常感谢! ̄ω ̄=

    个人原创Java技术文公众号,欢迎大家关注;关注后如果 不香 ,来捶我啊!嘿嘿。。。。。。

    展开全文
  • 1.学习了mysql联合索引,以及联合索引使用的注意事项。 联合索引:MySQL中使用多个字段同时建立一个索引联合索引。 在联合索引中,如果想要命中索引,需要按照建立索引时的字段顺序一次使用,否则无法命中索引。 在...

    1.学习了mysql联合索引,以及联合索引使用的注意事项。
    联合索引:MySQL中使用多个字段同时建立一个索引联合索引。
    在联合索引中,如果想要命中索引,需要按照建立索引时的字段顺序一次使用,否则无法命中索引。

    在建立联合索引的时候应该注意索引列的顺序,一般情况下,将查询需求频繁或者字段选择性高的列放在前面。
    具体原因为:
    MySQL使用索引时需要索引有序,如建立了"name,age,school"的联合索引,索引的排序为: 先按照name排序,如果name相同,则按照age排序,如果age的值也相等,则按照school进行排序。
    当进行查询时,此时索引仅仅按照name严格有序,因此必须首先使用name字段进行等值查询,之后对于匹配到的列而言,其按照age字段严格有序,此时可以使用age字段用做索引查找,以此类推。因此在建立联合索引的时候应该注意索引列的顺序,一般情况下,将查询需求频繁或者字段选择性高的列放在前面。此外可以根据特例的查询或者表结构进行单独的调整。
    优点:前缀索引能使索引更小,更快的有效办法。
    缺点:mysql无法使用其前缀索引做ORDER BY和GROUP BY,也无法使用前缀索引做覆盖扫描。

    展开全文
  • 联合索引在B+树上的存储结构及数据查找方式

    千次阅读 多人点赞 2020-02-29 12:12:01
    能坚持别人不能坚持的,才能拥有别人未曾拥有的。 关注编程大道公众号,让我们一同坚持心中所想,一起成长!...但都是基于单值索引,由于文章篇幅原因也只是在文末略提了一下联合索引,并没有大篇幅的展...

    能坚持别人不能坚持的,才能拥有别人未曾拥有的。
    关注BiggerBoy公众号,让我们一起成长,感谢各位的支持。
    拒绝白嫖,如果对你有帮助,请三连,如果可以请关注公众号:BiggerBoy。谢谢支持!哈哈

    引言

    上一篇文章《MySQL索引那些事》主要讲了MySQL索引的底层原理,且对比了B+Tree作为索引底层数据结构相对于其他数据结构(二叉树、红黑树、B树)的优势,最后还通过图示的方式描述了索引的存储结构。但都是基于单值索引,由于文章篇幅原因也只是在文末略提了一下联合索引,并没有大篇幅的展开讨论,所以这篇文章就单独去讲一下联合索引在B+树上的存储结构。

    本文主要讲解的内容有:

    • 联合索引在B+树上的存储结构
    • 联合索引的查找方式
    • 为什么会有最左前缀匹配原则

    在分享这篇文章之前,我在网上查了关于MySQL联合索引在B+树上的存储结构这个问题,翻阅了很多博客和技术文章,其中有几篇讲述的与事实相悖。庆幸的是看到搜索引擎列出的有一条是来自思否社区的问答,有答主回答了这个问题,贴出一篇文章和一张图以及一句简单的描述。PS:贴出的文章链接已经打不开了。

    所以在这样的条件下这篇文章就诞生了。

    联合索引的存储结构

    下面就引用思否社区的这个问答来展开我们今天要讨论的联合索引的存储结构的问题。

    来自思否的提问,联合索引的存储结构
    (https://segmentfault.com/q/1010000017579884)
    有码友回答如下:


    联合索引 bcd , 在索引树中的样子如图 , 在比较的过程中 ,先判断 b 再判断 c 然后是 d ,

    由于回答只有一张图一句话,可能会让你有点看不懂,所以我们就借助前人的肩膀用这个例子来更加细致的讲探寻一下联合索引在B+树上的存储结构吧。

    首先,表T1有字段a,b,c,d,e,其中a是主键,除e为varchar其余为int类型,并创建了一个联合索引idx_t1_bcd(b,c,d),然后b、c、d三列作为联合索引,在B+树上的结构正如上图所示。联合索引的所有索引列都出现在索引数上,并依次比较三列的大小。上图树高只有两层不容易理解,下面是假设的表数据以及我对其联合索引在B+树上的结构图的改进。PS:基于InnoDB存储引擎。

    bcd联合索引在B+树上的结构图

    bcd联合索引在B+树上的结构图


    T1表

    通过这俩图我们心里对联合索引在B+树上的存储结构就有了个大概的认识。下面用我的语言为大家解释一下吧。

    我们先看T1表,他的主键暂且我们将它设为整型自增的(PS:至于为什么是整型自增上篇文章有详细介绍这里不再多说),InnoDB会使用主键索引在B+树维护索引和数据文件,然后我们创建了一个联合索引(b,c,d)也会生成一个索引树,同样是B+树的结构,只不过它的data部分存储的是联合索引所在行的主键值(上图叶子节点紫色背景部分),至于为什么辅助索引data部分存储主键值上篇文章也有介绍,感兴趣或还不知道的可以去看一下。

    好了大致情况都介绍完了。下面我们结合这俩图来解释一下。

    对于联合索引来说只不过比单值索引多了几列,而这些索引列全都出现在索引树上。对于联合索引,存储引擎会首先根据第一个索引列排序,如上图我们可以单看第一个索引列,如,1 1 5 12 13…他是单调递增的;如果第一列相等则再根据第二列排序,依次类推就构成了上图的索引树,上图中的1 1 4 ,1 1 5以及13 12 4,13 16 1,13 16 5就可以说明这种情况。

    联合索引的查找方式

    当我们的SQL语言可以应用到索引的时候,比如 select * from T1 where b = 12 and c = 14 and d = 3; 也就是T1表中a列为4的这条记录。存储引擎首先从根节点(一般常驻内存)开始查找,第一个索引的第一个索引列为1,12大于1,第二个索引的第一个索引列为56,12小于56,于是从这俩索引的中间读到下一个节点的磁盘文件地址,从磁盘上Load这个节点,通常伴随一次磁盘IO,然后在内存里去查找。当Load叶子节点的第二个节点时又是一次磁盘IO,比较第一个元素,b=12,c=14,d=3完全符合,于是找到该索引下的data元素即ID值,再从主键索引树上找到最终数据。

    最左前缀匹配原则

    之所以会有最左前缀匹配原则和联合索引的索引构建方式及存储结构是有关系的。

    首先我们创建的idx_t1_bcd(b,c,d)索引,相当于创建了(b)、(b、c)(b、c、d)三个索引,看完下面你就知道为什么相当于创建了三个索引。

    我们看,联合索引是首先使用多列索引的第一列构建的索引树,用上面idx_t1_bcd(b,c,d)的例子就是优先使用b列构建,当b列值相等时再以c列排序,若c列的值也相等则以d列排序。我们可以取出索引树的叶子节点看一下。

    索引的第一列也就是b列可以说是从左到右单调递增的,但我们看c列和d列并没有这个特性,它们只能在b列值相等的情况下这个小范围内递增,如第一叶子节点的第1、2个元素和第二个叶子节点的后三个元素。

    由于联合索引是上述那样的索引构建方式及存储结构,所以联合索引只能从多列索引的第一列开始查找。所以如果你的查找条件不包含b列如(c,d)、(c)、(d)是无法应用缓存的,以及跨列也是无法完全用到索引如(b,d),只会用到b列索引。

    这就像我们的电话本一样,有名和姓以及电话,名和姓就是联合索引。在姓可以以姓的首字母排序,姓的首字母相同的情况下,再以名的首字母排序。

    如:

    M
        毛 不易   178********
        马 化腾   183********
        马 云     188********
    Z
        张 杰     189********
        张 靓颖   138********
        张 艺兴   176********  
    

    我们知道名和姓是很快就能够从姓的首字母索引定位到姓,然后定位到名,进而找到电话号码,因为所有的姓从上到下按照既定的规则(首字母排序)是有序的,而名是在姓的首字母一定的条件下也是按照名的首字母排序的,但是整体来看,所有的名放在一起是无序的,所以如果只知道名查找起来就比较慢,因为无法用已排好的结构快速查找。

    到这里大家是否明白了为啥会有最左前缀匹配原则了吧。

    实践

    如下列举一些SQL的索引使用情况

    select * from T1 where b = 12 and c = 14 and d = 3;-- 全值索引匹配 三列都用到
    select * from T1 where b = 12 and c = 14 and e = 'xml';-- 应用到两列索引
    select * from T1 where b = 12 and e = 'xml';-- 应用到一列索引
    select * from T1 where b = 12  and c >= 14 and e = 'xml';-- 应用到bc两列列索引及索引条件下推优化
    select * from T1 where b = 12  and d = 3;-- 应用到一列索引  因为不能跨列使用索引 没有c列 连不上
    select * from T1 where c = 14  and d = 3;-- 无法应用索引,违背最左匹配原则
    

    后记

    到这里MySQL索引的联合索引的存储结构及查找方式就讲完了,本人能力有限,也是站着前人的肩膀上创作的此文,因为看到搜索引擎的搜索结果前几个技术文章中有存在讲述不清或讲述有误的地方,所以自己才总结出这篇文章分享给大家,如有不对的地方一定要指正哦,谢谢了。

    这篇文章断断续续利用工作之余画图加写作用了两三天,主要内容就是上面这些了。不可否认,这篇文章在一定程度上有纸上谈兵之嫌,因为我本人对MySQL的使用属于菜鸟级别,更没有太多数据库调优的经验,在这里高谈阔论实属惭愧。就当是我个人的一篇学习笔记了。

    另外,MySQL索引及知识非常广泛,本文只是涉及到其中一部分。如与排序(ORDER BY)相关的索引优化及覆盖索引(Covering index)的话题本文并未涉及,同时除B-Tree索引外MySQL还根据不同引擎支持的哈希索引、全文索引等等本文也并未涉及。如果有机会,希望再对本文未涉及的部分进行补充吧。

    创作不易,如果对你有帮助,请不要吝啬你的赞,这对我是很大的鼓励~


    拒绝白嫖,如果对你有帮助,请三连,如果可以请关注公众号:BiggerBoy。谢谢支持!哈哈
    觉得觉得对你有帮助,请点赞哦↓↓↓分享给更多的人看

    扫码关注公众号。第一时间阅读最新文章,你的关注是对我最大的支持!~~

    你可能感兴趣的文章:

    [需求设计]从一个小需求感受Redis的独特魅力

    Redis的各种数据类型到底能玩出什么花儿?

    Redis分布式锁实战(手把手教你实现Redis分布式锁)

    你真的了解Redis的发布订阅?

    使用Redis不考虑这些问题,还叫会用Redis?缓存穿透,缓存击穿,缓存雪崩,热点Key问题

    高并发场景下缓存+数据库双写不一致问题分析与解决方案设计

    什么?我往Redis里写的数据怎么没了?

    展开全文
  • 主要介绍了MySQL联合索引用法,结合实例形式分析了MySQL联合索引的具体定义与使用方法,需要的朋友可以参考下
  • 验证mysql联合索引最左原则

    千次阅读 2018-01-29 10:39:20
    如果你接触过数据库,应该听说过某些列上建立索引能够加快查找速度,如果研究更深入一点的人,可能还听说过联合索引,那么索引为什么能够加快查找速度呢?下面说说我的简单理解。 索引 试想一下,把1~10000这...

    前言

    如果你接触过数据库,应该听说过某些列上建立索引能够加快查找速度,如果研究更深入一点的人,可能还听说过联合索引,那么索引为什么能够加快查找速度呢?下面说说我的简单理解。

    索引

    试想一下,把1~10000这10000个数字打乱顺序存储在数组中,如果要找到5000这个数字在哪,那就得从数组第0个元素开始,一次遍历找到5000这个数,运气好了1次找到,运气不好需要查询10000个数,可是如果把这10000个数作为map的key,每个数存在数组中的位置作为value,存储在map结构中很快就能找到,通常情况下要比直接遍历快的多。

    其实这里的map充当的是一个索引的作用,我们知道map存储数据时使用树形结构,会根据要查找的值和当前节点比较,来确定继续查找左分支还是右分支,而数据库中的索引充当的也是这样的作用,mysql中的索引是BTree结构(多路搜索树),就是利用建立索引的列中的所有值建立了一棵树,通过有序的树形查找一般要比全局搜索快多了吧!

    联合索引

    简单了解了一下索引的含义,那么什么是联合索引呢?其实mysql数据库中的索引不止可以建立在一个列上,它可以将一个索引同时建立在说多个列上,也就是我们所说的联合索引,联合索引的作用特别大,有时会超过单列索引,至于什么时候建立单列索引,什么时候建立联合索引同样是个很复杂的问题,在此不做描述。有兴趣的读者可以自行搜索一下。

    最左原则

    当你在多个列上建立一个索引时,那么怎样的查找才能利用索引加快速度呢?说到这我们先建立一个带有索引的表格,具体的分析一下什么叫做索引的最左原则。

    CREATE TABLE IF NOT EXISTS `test_index`(
      `id` int(4) NOT NULL AUTO_INCREMENT,
      `a` int(4) NOT NULL DEFAULT '0',
      `b` int(4) NOT NULL DEFAULT '0',
      `c` int(4) NOT NULL DEFAULT '0',
      `data` int(4) NOT NULL DEFAULT '0',
      PRIMARY KEY  (`id`),
      KEY `union_index` (`a`,`b`,`c`)
    )ENGINE=InnoDB ROW_FORMAT=DYNAMIC  DEFAULT CHARSET=binary;

    分析上述建表语句,创建了一个名为test_index的数据库表格,然后在a、b、c三列上建立了联合索引,而最左原则指的就是当你建立了这样一个索引的时候,通过条件 (a), (a,b), (a,b,c) 这三种条件查询的时候都可以利用索引加快速度,所以在建立索引的时候要把最常用的条件列放到联合索引的最左边,接下来我们来验证一下,工具就是mysql的explain命令。

    测试环境

    Welcome to the MySQL monitor. Commands end with ; or \g.
    Your MySQL connection id is 13
    Server version: 5.7.21-log MySQL Community Server (GPL)

    Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.

    验证过程

    1. 首先以列a作为条件查询数据,我们看到 type: ref 表示引用查找, key_len: 4 表示索引长度为4,也就是利用上了索引来进行查找

      mysql> explain select data from test_index where a = 1\G
      *************************** 1. row ***************************
              id: 1
      select_type: SIMPLE
           table: test_index
      partitions: NULL
            type: ref
      possible_keys: union_index
             key: union_index
         key_len: 4
             ref: const
            rows: 70
        filtered: 100.00
           Extra: NULL
      1 row in set, 1 warning (0.01 sec)
    2. 然后以列b作为条件查询数据,可以看到type: ALL表示全表查找, key_len: NULL 表示没有索引,也就说明如果只使用b作为查询条件,不能利用索引来加快查找速度

      mysql> explain select data from test_index where b = 1\G
      *************************** 1. row ***************************
              id: 1
      select_type: SIMPLE
           table: test_index
      partitions: NULL
            type: ALL
      possible_keys: NULL
             key: NULL
         key_len: NULL
             ref: NULL
            rows: 716173
        filtered: 10.00
           Extra: Using where
      1 row in set, 1 warning (0.00 sec)
    3. 接着以列c作为条件查询数据,可以看到type: ALL表示全表查找, key_len: NULL 表示没有索引,情况与用b作为条件一样,只使用c作为查询条件也不能利用索引来加快查找速度

      mysql> explain select data from test_index where c = 1\G
      *************************** 1. row ***************************
              id: 1
      select_type: SIMPLE
           table: test_index
      partitions: NULL
            type: ALL
      possible_keys: NULL
             key: NULL
         key_len: NULL
             ref: NULL
            rows: 716173
        filtered: 10.00
           Extra: Using where
      1 row in set, 1 warning (0.00 sec)
    4. 现在来测一下使用a、b作为条件的情况,我们看到 type: ref 表示引用查找, key_len: 8 表示索引长度为8,也就是说我们利用上了a、b联合索引来进行查找

      mysql> explain select data from test_index where a = 1 and b = 1\G
      *************************** 1. row ***************************
              id: 1
      select_type: SIMPLE
           table: test_index
      partitions: NULL
            type: ref
      possible_keys: union_index
             key: union_index
         key_len: 8
             ref: const,const
            rows: 1
        filtered: 100.00
           Extra: NULL
      1 row in set, 1 warning (0.00 sec)
    5. 紧接着来测一下使用a、c作为条件的情况,我们看到 type: ref 表示引用查找, key_len: 4 表示索引长度为4,这就奇怪了,按照最左原则来说,a、c上是不会建立索引的,为什么会有索引长度呢?其实与a、b上的索引一比较我们就能发现,a、c上的索引长度只有4,而且单独的c上是没有索引的,所以4字节长度的索引只能是a上的,也就是说这种情况我们只使用了a列上的索引来进行查找

      mysql> explain select data from test_index where a = 1 and c = 1\G
      *************************** 1. row ***************************
              id: 1
      select_type: SIMPLE
           table: test_index
      partitions: NULL
            type: ref
      possible_keys: union_index
             key: union_index
         key_len: 4
             ref: const
            rows: 70
        filtered: 10.00
           Extra: Using index condition
      1 row in set, 1 warning (0.00 sec)
    6. 为了进一步验证上面的想法,这一次测一下使用b、c作为条件的情况,我们看到 type: ALL 表示全表查找, key_len: NULL 表示没有索引可以使用,按照最左原则来说,b列上没有索引,c列上也没有索引,同时b、c的上也不存在联合索引,所以使用b、c作为查询条件时无法利用联合索引

      mysql> explain select data from test_index where b = 1 and c = 1\G
      *************************** 1. row ***************************
              id: 1
      select_type: SIMPLE
           table: test_index
      partitions: NULL
            type: ALL
      possible_keys: NULL
             key: NULL
         key_len: NULL
             ref: NULL
            rows: 716173
        filtered: 1.00
           Extra: Using where
      1 row in set, 1 warning (0.00 sec)
    7. 测试完两个条件的情况,接下来测试一下使用a、b、c作为条件的情况,我们看到 type: ref 表示引用查找, key_len: 12 表示索引长度为12,这完全符合联合索引的最左原则,同时使用3个条件查询可以利用联合索引

      mysql> explain select data from test_index where a = 1 and b = 1 and c = 1\G
      *************************** 1. row ***************************
              id: 1
      select_type: SIMPLE
           table: test_index
      partitions: NULL
            type: ref
      possible_keys: union_index
             key: union_index
         key_len: 12
             ref: const,const,const
            rows: 1
        filtered: 100.00
           Extra: NULL
      1 row in set, 1 warning (0.00 sec)
    8. 测试进行到现在,我测试了所有的情况吗?不是的!还可以颠倒顺序啊,我原来一直以为联合索引是有顺序的,结果测试后才发现,利用索引的条件符合“交换律”,也就是下面这种情况也能利用a、b上的联合索引,索引长度为8

      mysql> explain select data from test_index where b = 1 and a = 1\G
      *************************** 1. row ***************************
              id: 1
      select_type: SIMPLE
           table: test_index
      partitions: NULL
            type: ref
      possible_keys: union_index
             key: union_index
         key_len: 8
             ref: const,const
            rows: 1
        filtered: 100.00
           Extra: NULL
      1 row in set, 1 warning (0.00 sec)
    9. 再来试试这种情况,按照最左原则,c上没有建立索引,a上有索引,c、a没有建立联合索引,所以只能使用a上的索引进行查找,结果索引长度只有4,验证了我们的想法,联合查询条件使用索引时满足“交换律”

      mysql> explain select data from test_index where c = 1 and a = 1\G
      *************************** 1. row ***************************
              id: 1
      select_type: SIMPLE
           table: test_index
      partitions: NULL
            type: ref
      possible_keys: union_index
             key: union_index
         key_len: 4
             ref: const
            rows: 70
        filtered: 10.00
           Extra: Using index condition
      1 row in set, 1 warning (0.00 sec)
      
    10. 接下来几种交换顺序的情况(c,b)、(a,c,b)、(c,b,a)等,大家可以自己进行验证,到此为止,mysql联合索引的最左原则也就验证结束了!

    总结

    1. 联合索引的最左原则就是建立索引KEY union_index (a,b,c)时,等于建立了(a)、(a,b)、(a,b,c)三个索引,从形式上看就是索引向左侧聚集,所以叫做最左原则,因此最常用的条件应该放到联合索引的组左侧。

    2. 利用联合索引加速查询时,联合查询条件符合“交换律”,也就是where a = 1 and b = 1 等价于 where b = 1 and a = 1,这两种写法都能利用索引KEY union_index (a,b,c)

    3. 遇到这种不确定的问题还是需要实际测试一下,简单的调整一下索引顺序可能会极大的提升效率哦!

    展开全文
  • mysql普通索引以及联合索引介绍

    千次阅读 2019-03-16 17:57:45
    mysql普通索引以及联合索引介绍 命名规则:表名_字段名 1、需要加索引的字段,要在where条件中 2、数据量少的字段不需要加索引 3、如果where条件中是OR关系,加索引不起作用 4、符合最左原则 ...
  • 数据库索引:联合索引基本知识

    千次阅读 2015-03-27 11:26:06
    数据库索引:联合索引基本知识
  • SQL索引查找与索引扫描

    千次阅读 2017-11-20 23:34:07
    本文导读:虽然都是通过索引取到相应数据,但是两者在过程上还是有区别的,索引扫描与表扫描差不多,都是把索引从开始扫描到结束,而索引查找就不一样了,会根据你查询的字符,定位到索引的局部位置,然后再开始查找...
  • Mysql索引查找原理及调优1.1 常见查找方法举例1.1.1 顺序查找(linear search )1.1.2 二分查找1.1.3 二叉排序树查找1.1.4 哈希散列法(哈希表)1.2 MyISAM实现索引1.2.1 MyISAM实现索引 介绍1.2.2 MyISAM索引的原理图...
  • mysql联合索引详解

    万次阅读 多人点赞 2019-03-30 15:18:56
    联合索引又叫复合索引。 b+tree结构如下: 每一个磁盘块在mysql中是一个页,页大小是固定的,mysql innodb的默认的页大小是16k,每个索引会分配在页上的数量是由字段的大小决定。当字段值的长度越长,每一页上的...
  • mysql 联合索引

    2015-12-31 11:38:10
    联合索引又叫复合索引。对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 ...
  • 索引也分为很多种(聚集、非聚集、联合索引等),数据结构主要有哈希索引和B+树等,哈希索引在单个查询性能上很强大,但不适合做范围查询。以下讨论主要是建立在B+树索引上面的。 索引一般采用B+树的数据结构,B+树...
  • mysql联合索引的理解

    2018-07-19 23:19:16
    命名规则:表名_字段名1、需要加索引的字段,要在where条件中2、数据量少的字段不需要加索引3、...联合索引又叫复合索引。对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只...
  • 联合索引的树结构、最左匹配原则、如何选择合适的索引列顺序、索引下推图文讲解
  • 联合索引存储结构
  • MySQL B+树如何实现联合索引 “同学,你来画一下MySQL的B+树如何实现联合索引的?” “额,这个嘛……这个……俺不晓得……” 之前大言不惭说对MySQL还算了解的我今天被这个问题糊的一脸懵逼,本着对问题的求知和...
  • MySQL联合索引原理解析

    千次阅读 2018-12-09 13:36:00
    什么是MySQL联合索引 联合索引又叫复合索引,是MySQL的InnoDB引擎中的一个索引方式,如果一个系统频繁地使用相同的几个字段查询结果,就可以考虑建立这几个字段的联合索引来提高查询效率。 如何建立索引 举个例子: ...
  • mysql联合索引的数据结构

    千次阅读 2020-08-05 16:16:53
    联合索引查找方式 为什么会有最左前缀匹配原则 在分享这篇文章之前,我在网上查了关于MySQL联合索引在B+树上的存储结构这个问题,翻阅了很多博客和技术文章,其中有几篇讲述的与事实相悖。庆幸的是看到搜索引擎列...
  • 索引6:联合索引的最左匹配原则

    千次阅读 2020-02-13 18:17:48
    联合索引的最左匹配原则 什么是最左匹配原则? 举例 索引列A和列B 建立联合索引 index(A,B)(A,B顺序有序) explain select X from XX where A=’‘and B=’’ 走 ab索引 explain select X from XX where A=’‘走 ab...
  • 联合索引的基本知识

    千次阅读 2017-05-08 12:01:54
    0.预备   假设我们有表 user (id,name)列  1.联合索引是个什么东西
  • 【推荐】mysql联合 索引(复合索引)的探讨

    万次阅读 多人点赞 2019-02-16 22:43:06
    Mysql联合 索引(复合索引)的使用原则 命名规则:表名_字段名 需要加索引的字段,要在where条件中。 数据量少的字段不需要加索引。最窄的字段放在键的左边。 如果where条件中是OR关系,必须所有的or条件都必须...
  • 如果你接触过数据库,应该听说过某些列上建立索引能够加快查找速度,如果研究更深入一点的人,可能还听说过联合索引,那么索引为什么能够加快查找速度呢?下面说说我的简单理解。 索引 试想一下,把1~10000这10000个...
  • Mysql数据库联合索引使用 简介

    千次阅读 2017-07-16 13:55:34
    1.联合索引是个什么东西 我们知道,对于表的单列(如id)数据,是可以建立索引的,对于多列(id和name组合,或者,name和id组合),也可以建立索引。联合索引,也称之为组合索引。 先来看单列索引的逻辑结构。 由此得出...
  • mysql主键索引、非主键索引、联合索引、覆盖索引、最左匹配、索引下推
  •  微博的关注,比如userID是很普通的用户,而一些明星是follerID用户,现在有三种查询条件,分别是查找普通用户关注了那些大咖,哪些大咖被关注,哪一个用户关注了哪一个大咖,该怎么写sql语句?该怎么用索引   ...
  • B-Tree索引详解及联合索引使用

    千次阅读 2018-04-04 15:04:16
    B-Tree索引原理详解部分转载自:http://zsuil.com/?p=1184一.B-tree索引详解B-tree索引(或Balanced Tree),是一种很普遍的数据库索引结构,oracle默认的索引类型(本文也主要依据oracle来讲)。其特点是定位高效、...
  • mysql联合索引查询优化

    千次阅读 2018-09-18 12:21:46
    在利用B-Tree索引进行查询的过程中,有几点注意事项,我们以表A进行说明。其中表A的定义如下:  create table A(id int auto_increment primary key, name varchar(10), age tinyint, sex enum('男','女'), birth ...
  • 转载 : ... mysql联合索引 命名规则:表名_字段名1、需要加索引的字段,要在where条件中2、数据量少的字段不需要加索引3、如果where条件中是OR关系,加索引不起作用4、符合最左原则 ...
  • 聚集索引、非聚集索引索引、复合索引或覆盖索引) 1.聚集索引 可以理解为主键 一个表只能有一个聚集索引 主键的作用就是把「表」的数据格式转换成「索引(平衡树)」的格式放置 加了主键(聚集索引)后查询...
  • mysql中 myisam,innodb默认使用的是 Btree索引,至于btree的数据结构是怎样的都不重要, 只需要知道结果,既然是索引那这个数据结构最后是排好序;就像新华字典他的目录就是按照a,b,c…这样排好序的; 所以你在找...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 34,423
精华内容 13,769
关键字:

联合索引的查找过程