精彩牛耳,用心缔造

牛耳教育-Java开发稳步进阶之不容忽视的“索引”

作者: 编辑: 来源: 发布日期: 2020.03.30
信息摘要:
作为Java工程师,MySQL数据库的灵活运用是必须掌握的技能。而“索引”作为数据库的两大神器之一,有很多初学Java的同学在接触时感觉无从…

作为Java工程师,MySQL数据库的灵活运用是必须掌握的技能。而“索引”作为数据库的两大神器之一,有很多初学Java的同学在接触时感觉无从下手,接下来我们把MySQL数据库索引相关的知识点仔细梳理一遍。

一、覆盖索引

如果一个索引包含了满足SQL语句中字段与条件的数据,那么它就叫做覆盖索引。覆盖索引只需要在一棵索引树上就能获取SQL所需的所有列数据,无需回表,速度更快。

     那这里提出了一个概念“回表”。在讲回表时,我们需要同时了解聚集索引和辅助索引。

二、回表

1

第一步在辅助索引 B+树中检索 , 到达其叶子节点获取对应的主键;

第二步使用主键在主索引 B+树种再执行一次 B+树检索操作, 最终到达叶子节点即可获取整行数据。

     我们能很清晰的看出来,左边的是聚集索引,右边的是辅助索引,上图告诉我们如果回表查询的话,会先在辅助索引中找到主键值,再去聚集索引中找到全部的信息。在InnoDB 中,表数据文件本身就是按 B+Tree 组织的一个索引结构,这棵树的叶点data 域保存了完整的数据记录。这个索引的 key 是数据表的主键,因此 InnoDB 表数据文件本身就是主索引(聚集索引)。流程大概如下:

如果定义了主键,那么主键就是聚集索引

如果没有定义主键,那么第一个not NULL unique列是聚集索引

如果都没有,则InnoDb会创建一个隐藏row-id作为聚集索引

而辅助索引就是我们平常用到的非主键索引。

三、利用覆盖索引优化sql

   我们通过覆盖索引查询想要的数据字段都是索引键值的一部分,直接存放在索引的子叶层级,不需要通过辅助索引来一次回表查询,效率很高。

     覆盖索引不仅仅只包含你写在WHERE条件内的字段,而且还包含所有SELECT 需要的字段,以及在GROUP BY 或ORDER BY 子句内的字段。

     那么我可以怎么通过覆盖索引来优化我们的sql呢?

 

1、全表查询SQL优化、可以把需要查询的列加入索引

select count(name) from user; 比如这个很常见的查询数量的sql,我们就可以把user加入索引,这样就可以免去回表查询 ALERT TABLE user ADD index index_name(name);

2、列表查询回表优化

select id,name,sex from user where name='fawaikuangtuzhangsan'

ALERT TABLE user ADD index index_name_sex(name,sex);

将单列索引(name)升级为联合索引(name, sex),即可避免回表。

3、根据实际情况通过将所有查询的列加入覆盖索引以达成防止回表的目的

覆盖索引可以完美的解决二级索引回表查询问题,但是前提是一定得注意查询时候索引的最左侧匹配原则。

四、最左匹配原则

 最左优先,以最左边的为起点任何连续的索引都能匹配上就是最左匹配原则。如果是联合索引,那么key也由多个列组成,同时,索引只能用于查找key是否存在(相等),遇到范围查询 (><betweenlike左匹配)等就不能进一步匹配了,后续退化为线性查找。

ALTER  TABLE  `ryc` ADD INDEX index_a_b_c(`a`,`b`,`c`);

select * from ryc where a = '1' and b = '2' and c = '3' 

select * from ryc where b = '2' and a = '1' and c = '3' 

select * from ryc where c = '3' and b = '2' and a = '1' 

select * from ryc where a = '3' and b = '2' 

select * from ryc where a = '3'

这些情况搜索不影响查询结果,它们都是从最左边开始,同时因为Mysql中有查询优化器,会自动优化查询顺序。但记住一般需要按顺序写。

select * from ryc where b = '3' and c = '2' 

select * from ryc where b = '3'

这些没有从最左边开始,最后查询没有用到索引,用的是全表扫描。

select * from ryc where a = '3' and c = '2'

这种情况就只命中了a的索引,c还是得老老实实的去扫描

     如果遇到了><之类的范围查询,在范围查询之前,如果满足最左匹配原则,那么就可以按索引走,在范围查询之后的就不管了。

五、B+

 B+树最核心的特点:

只有叶子节点保存数据

多路非二叉

增加了相邻接点的指向指针

B+树的非叶子节点的每一个关键字对应一个指针,而关键字则是子树的最大或最小值

 通过这些特点,我们就能总结出B+树的优点:

B+树相比B树的存储效率更高B+树是多级索引,与Redis中跳跃表非常相似,最下一层是所有关键码的全集,因此可以把此层形成顺序的双链链表,正因为在B+树里面非叶层节点不需要存储额外的指向磁盘的指针。

B+树的高度更小,即B+树的检索效率更高。

B+树支持范围检索,因为最下方是链表,和分页查询简直是天作之和

插入和删除更为方便B+树里面,关键码的个数和子节点的个数是对等的,所以从记忆角度来说,B+树更方便记忆使用,而B树则需要时刻注意节点数和关键码的对应关系。

当然,相比于B树,B+树还是有一些缺点的。比如只有叶子节点才存放数据,这也就意味着查找B+树就必须到叶子节点才能返回结果。

六、InnoDB索引实现

InnoDB ,表数据文件本身就是按 B+树组织的一个索引结构,这棵树的叶点data 域保存了完整的数据记录。这个索引的 key 是数据表的主键,因此 InnoDB 表数据文件本身就是主索引。

     所以我们可以得出一个关键的信息:

InnoDB 要求表必须有主键,最好是一个自增的主键。

     因为 InnoDB 数据文件本身是一棵B+,非单调的主键会造成在插入新记录时数据文件为了维持 B+Tree 的特性而频繁的分裂调整,十分低效,而使用自增字段作为主键则是一个很好的选择。

     如果表使用自增主键,每次插入新的记录,记录就会按顺序添加到当前索引节点的后续位置。当一页写满则自动开辟一个新的页。

     InnoDB 的辅助索引 data 域存储相应记录主键的值而不是地址。也就是说,InnoDB 的所有辅助索引都引用主键作为 data 域。聚集索引这种实现方式使得按主键的搜索十分高效,但是辅助索引搜索需要检索两遍索引:首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录(回表)。

牛耳教育
【相关推荐】
牛耳推荐资讯
牛耳成长故事丨你是我见过的最差的学生,也是最好的学生

牛耳成长故事丨你是我见过的最差的学生,也是最好的学生

第一次见到小陈,是在我们教学楼的了楼梯口,他一边吃着包子嘴里还停的念叨着:“糟糕了,第一天上课就迟到,早餐也没吃完,太倒霉了。”当时我看到这个学生的时候心里就在想:”这不会是我们校区的学生吧?“第二次见到小陈,是在咨询办公室里。他一个人关着门在咨询老师的办公室里,身体还斜靠在椅子,手机放着很大......
2020-05-15
做好这四件事,线上学习依然可以高效

做好这四件事,线上学习依然可以高效

一场突如其来的疫情似乎让一切放慢了脚步,开学时间延迟,但是“延迟不延学”,深圳、广州、武汉、杭州、北京,湖北、云南、江苏……疫情期间,全国20多省市,通过腾讯教育为学校搭建在线课堂。目前,已有百万教师使用腾讯教育在线课堂,助力数千万学生“不停学”。不仅如此,钉钉更是因为线上教学的原因“惨遭一星好......
2020-05-14
长沙Java培训-长沙Java培训学校

长沙Java培训-长沙Java培训学校

长沙Java培训班与企业停止专业对口的项眼关作,帮助学生挑前顺应严重剧烈的计算机工程设计,把实际常识的教育与实践项眼的展开融为一体,使得学习效力更高。按照长沙Java培训机构讲授品质凹凸比较是比较专业的,由于不但针对长沙Java培训学校的讲授方面,还摸清了各家培训机构的教育重点方向。长沙Java培训学校课程安......
2020-05-08

咨询热线

400-0731-162
  
    • *

    • *