• 欢迎访问 winrains 的个人网站!
  • 本网站主要从互联网整理和收集了与Java、网络安全、Linux等技术相关的文章,供学习和研究使用。如有侵权,请留言告知,谢谢!

索引中一些易忽视的点

数据库 winrains 来源:南橘ryc 5个月前 (04-30) 47次浏览

导言

从接触java到现在也有差不多两年时间了,两年时间,从一名连java有几种数据结构都不懂超级小白,到现在懂了一点点的进阶小白,学到了不少的东西。知识越分享越值钱,我这段时间总结(包括从别的大佬那边学习,引用)了一些平常学习和面试中的重点(自我认为),希望给大家带来一些帮助

这是这篇文章的思维导图,因为用的是免费版的软件,所以有不少水印,需要的可以问我要

南橘的思维导图

从索引开始

为什么会选择从索引开始,大概是我最近建了不少的表吧,其中一个负责做配置表同时负责了两个业务**(有不少通用字段,而且表内容很少很少,或许最多就30行吧)**,因主键和索引纠结了一会 (其实根本不需要纠结),所以就决定先分享(复习)一下索引的内容。

1、覆盖索引

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

既然提到了回表,那么我先讲一下什么叫回表。 说到了回表,又不得不提一下聚集索引和辅助索引 我得去大佬的文章找一下图片方便大家理解 (引用自大佬架构师之路的文章)

1.1 回表

在这里插入图片描述
  1. 第一步在辅助索引 B+树中检索 , 到达其叶子节点获取对应的主键
  2. 第二步使用主键在主索引 B+树种再执行一次 B+树检索操作, 最终到达叶子节点即可获取整行数据。

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

  • 如果定义了主键,那么主键就是聚集索引
  • 如果没有定义主键,那么第一个not NULL unique列是聚集索引
  • 如果都没有,则InnoDb会创建一个隐藏row-id作为聚集索引

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

1.2 利用覆盖索引优化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、其他的都差不多,都是通过将所有查询的列加入覆盖索引以达成防止回表的目的,当然,这个东西需要看情况用,不然长长的索引列也会带来长长的麻烦,比如我开头提到的表就完全不需要

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

2、最左匹配原则

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

比如我建立了联合索引abc

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还是得老老实实的去扫描

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

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

这种情况就满足了最左匹配原则的查询

select * from ryc where a > 3 and b = '2' and c<7

这种情况就只有a能够查索引了,bc都需要全表扫描了

如果是order by排序的情况,那么又有一些不同

select * from ryc order by b,c,a limit 10;

大家觉得这种情况能不能用到索引?

在这里插入图片描述

很遗憾,没有,不过这个 filesort 并不是说通过磁盘文件进行排序,而只是告诉我们进行了一个排序操作而已,当然,using filesort不一定引起mysql的性能问题。但是如果查询次数非常多,那么每次在mysql中进行排序,还是会有影响的

select * from ryc order by a limit 10; 
select * from ryc order by a,b limit 10; 
select * from ryc where a =1 order by b,c limit 10;

这些情况是可以用到覆盖索引的,因为他实现了最左匹配原则,至于为什么,请大家思考一下

B+树

其实,B+树非常好理解,我去大佬哪边拿(读书人的事情)一张图片来康康(引用自https://baijiahao.baidu.com/s?id=1655665549235267594&wfr=spider&for=pc)

在这里插入图片描述

B+树最核心的特点:

  • 只有叶子节点保存数据
  • 多路非二叉
  • 增加了相邻接点的指向指针
  • B+树的非叶子节点的每一个关键字对应一个指针,而关键字则是子树的最大,或者最小值

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

  • B+树相比B树的存储效率更高。B+树其实是多级索引,这种结构与Redis中跳跃表是非常的相似(以后会讲),最下一层是所有关键码的全集,因此可以把此层形成顺序的双链链表,正因为在B+树里面非叶层节点不需要存储额外的指向磁盘的指针,所以相比之前的B树,B+树存储效率更高。
  • B+树的高度更小,也就是说B+树的检索效率更高
  • B+树支持范围检索,因为最下方是链表,和分页查询简直是天作之和
  • 插入和删除更为方便,其实流程和B树类似,但B+树里面,关键码的个数和子节点的个数是对等的,所以从记忆角度来说,B+树更方便记忆使用,而B树则需要时刻注意节点数和关键码的对应关系。

当然,相比于B树,其实B+树还是有一些缺点的,比如只有叶子节点才存放数据,所以不会出现那种灵机一动,这也就意味着查找B+树,就必须到叶子节点才能返回结果。

4、InnoDB 索引实现

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

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

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

因为 InnoDB 数据文件本身是一棵B+树,非单调的主键会造成在插入新记录时数据文件为了维持 B+Tree 的特性而频繁的分裂调整,十分低效,而使用自增字段作为主键则是一个很好的选择。如果表使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页。 InnoDB 的辅助索引 data 域存储相应记录主键的值而不是地址。换句话说,InnoDB 的所有辅助索引都引用主键作为 data 域。 聚集索引这种实现方式使得按主键的搜索十分高效,但是辅助索引搜索需要检索两遍索引:首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录。(回表)

作者:南橘ryc

来源:https://juejin.im/post/5e7c9de051882535d41abb91


版权声明:文末如注明作者和来源,则表示本文系转载,版权为原作者所有 | 本文如有侵权,请及时联系,承诺在收到消息后第一时间删除 | 如转载本文,请注明原文链接。
喜欢 (1)