阅读 85

MySQL索引

零.写在前面

本文是作者复习sql索引的笔记,如果有不正确的地方还请指正,欢迎大家多多交流。

一.Sql的执行过程

1.1.查询缓存(query cache)

如果数据库开启了query cache,sql发送过来会先进行缓存查询,sql语句作为key,结果作为value。查询出结果则直接返回,没有则进行下一步。

1.2.查询优化 生成执行计划

解析sql:生成解析树,验证关键字是否正确(如select where等)

预处理:验证解析树是否合法,如表和字段是否存在,是否有权限

优化sql:判断使用哪个索引,判断多个表连接时的连接顺序。生成执行计划

注:查询优化器 一条SQL语句的查询,可以有不同的执行方案,至于最终选择哪种方案,需要通过优化器进行选择,选择执行成本最低的方案。 在一条单表查询语句真正执行之前,MySQL的查询优化器会找出执行该语句所有可能使用的方案,对比之后找出成本最低的方案。这个成本最低的方案就是所谓的执行计划。 优化过程大致如下: 1、根据搜索条件,找出所有可能使用的索引 2、计算全表扫描的代价 3、计算使用不同索引执行查询的代价 4、对比各种执行方案的代价,找出成本最低的那一个

1.3 查询结果并返回

数据库服务器将查询结果返回,如果可以缓存则会将结果进行缓存

二.索引

2.1索引是什么?

索引时一种可以加快sql查询速度的数据结构

2.2为什么需要索引

一句话,提升sql的查询速度

2.3.Mysql为什么选择B+tree结构作为索引

1、hash索引

我们先看一种索引---hash索引,以及为什么大部分数据库没有选择hash索引作为数据库的索引结构呢?

哈希索引就是采用一定的哈希算法,只需一次哈希算法即可立刻定位到相应的位置,速度非常快。本质上就是把键值换算成新的哈希值,根据这个哈希值来定位。

哈希算法时间复杂度为O(1),且不只存在于索引中,每个数据库应用中都存在该数据结构。

哈希表也为散列表,又直接寻址改进而来。在哈希的方式下,一个元素k处于h(k)中,即利用哈希函数h,根据关键字k计算出槽的位置。函数h将关键字域映射到哈希表T[0...m-1]的槽位上。计算效率非常高,可以一下便定位到位置。所以hash索引的查询效率远远高于B-Tree实现的索引。为什么拥有如此高的查询效率但是大部分数据库不适应hash索引呢? 所谓成也萧何,败也萧何。hash索引具有两面性,hash方式的查询除了高的查询效率也带来了也带来了它的局限性。主要有以下几点

  • 哈希索引没办法利用索引完成排序。
  • 在有大量重复键值的情况下,哈希索引的效率也是极低的(出现哈希碰撞问题)。
  • 不支持范围查询,仅仅能满足"=","IN"和"<=>"查询。因为经过相应的Hash算法处理之后的Hash值的大小关系,并不能保证和Hash运算前完全一样
  • hash索引中只有hash值和行数的指针,因此无法直接使用索引来避免读取行,但是因为这种索引读取快,性能影响不明显。
  • Hash索引不能利用部分索引键查询。 Hash索引在计算Hash值的时候是组合索引键合并后再一起计算Hash值,而不是单独计算Hash值

1.二叉树

为了解决上面hash索引的问题,我们引入了二叉树,利用二叉查找树,区间查询的功能已经实现了。但是,为了节省内存,我们只能把树存储在硬盘中。那么每次读取一个节点,都是一次IO。

普通二叉树的查询速度很高为O(log2(n)),但是二叉树不稳定,极限情况下会造成节点全部在一侧,所以在极限情况下相当于遍历一遍全部索引,在这样的不平衡情况下会使索引缺少稳定性。在此基础上改进,我们演变出来平衡二叉树

2020-12-03 02-06-56屏幕截图.png

2.平衡二叉树:平衡二叉树不会出现二叉树那种节点全部都在某一侧的情况,在满足二叉树的基础上,任意两个节点的两个子树的高度差不能超过1,我们将上边的数据使用平衡二叉树排列,如下图所示。

2020-12-03 02-13-21屏幕截图.png

由此图可见,平衡二叉树的结构会比二叉树好一些,不出出现类似于单向链表的情况,但是也有一个问题,如果索引数据过多导致树的高度变得很高,但是每个节点上只有一个元素,在加载索引时,不能发挥出内存与磁盘IO的优势,毕竟mysql一个内存页默认大小为16k,一次只加载一个显然有点浪费,而且,平衡二叉树在维持平衡的时候需要进行左旋和右旋,这样也消耗了性能。所以演变出来B-树

B-tree:b树的基本概念所有的叶子节点的高度都是一样,这个保证了每次查询数据的时候都是稳定的查询效率,不会因为运气的影响。B树在每个节点上是可以存储数据的,这样也会有一个问题B树的查找是不稳定的,你可以很欧,在根节点就查找到了数据,也可能是个非洲人,在叶子节点才查到数据。所有节点都可以存放数据也会造成节点上的数据过多,可能为了维持节点可以一次被内存读取 导致存放的元素的数量受影响。而且B树在范围查找方面所体现的能力也是不足的。

2020-12-03 02-24-50屏幕截图.png

首先你要知道什么B+ Tree,其实他是专门为磁盘或者其他的直接存取辅助设备设计的一种平衡查找树,在B树中,所有的节点都是按照键值的大小顺序存放在同一层的叶子节点上,由各叶子节点的指针连接。

B+树非叶子节点不存放数据,这样就可以在一个节点中存放的元素更多。在非叶子节点上的元素只作为索引来查找下一层的数据。叶子节点之间的有指向的指针,这样在我们进行范围查找的时候就会显得很容易

2020-12-03 02-34-32屏幕截图.png

所以,通过以上的演变,我们知道了为什么选择B+树而不是二叉树和平衡二叉树

三、聚集索引和非聚集索引

3.1、聚集索引和非聚集索引是什么

  • 聚集索引在叶子节点存储的是表中的数据。
  • 非聚集索引在叶子节点存储的是主键和索引列

在innoDb存储引擎中,采用b+树作为索引结构,在叶子节点中的数据存放的是行记录,找到确定的索引位置就可以获取数据,这就是聚集索引

在MyISAM引擎中,叶子节点存放的是数据记录的内存地址,查找数据就需要再进行一遍数据读取(回表),这就是非聚集索引。

举个例子

生活中我们常用的字典,想要查「阿」字,只需要翻到字典前几页,a 开头的位置,接着「啊」「爱」都会出来。也就是说,字典的正文部分本身就是一个目录,阿和它读音相关的字都在一起啦,不需要再去查其他目录来找到需要找的内容。我们 把这种正文内容本身就是一种按照一定规则排列的目录称为==聚集索引==。

如果遇到不认识的字,只能根据“偏旁部首”进行查找,然后根据这个字后的页码直接翻到某页来找到要找的字。但结合 部首目录 和 检字表 而查到的字的排序并不是真正的正文的排序方法。

image.png

比如要查“玉”字,我们可以看到在查部首之后的检字表中“玉”的页码是 587 页,然后是珏,是 251 页。很显然,在字典中这两个字并没有挨着,现在看到的连续的“玉、珏、莹”三字实际上就是他们 在非聚集索引中的排序,是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到所需要的字,但它需要两个过程,先找到目录中的结果,然后再翻到结果所对应的页码。我们 把这种目录纯粹是目录,正文纯粹是正文的排序方式称为==非聚集索引==。

四、MySQL 索引失效

我们为了高的查询效率创建了索引,但是索引不是一定都会生效,存在着某些情况我们无法使用我们创建的索引。索引便失效了。那么什么时候索引会失效呢?

4.1、违反最左匹配原则

最左匹配原则:最左优先,以最左边的为起点任何连续的索引都能匹配上,如不连续,则匹配不上。

例如:建立索引为(a,b)的联合索引,那么只查 where b = 2 则不生效。换句话说:如果建立的索引是(a,b,c),也只有(a),(a,b),(a,b,c)三种查询可以生效。

like 中以通配符开头(’%abc’) 同样会使索引失效,违背最左匹配原则

explain select * from user where name like ‘%zhangsan’;

4.2、在列上进行计算

如计算、函数、(手动或自动)类型转换等操作,会导致索引失效而进行全表扫描。

explain select * from user where left(name,3) = 'zhangsan' and age =20

4.3使用使用不等于(!= 、<>)等关键词

explain select * from user where age != 20;

4.4、索引字段为varchar类型,查询条件没添加引号

explain select * from user where name = 2000;

4.5、查询条件使用is null,is not null

在索引列上使用 IS NULL 或 IS NOT NULL操作。索引是不索引空值的,所以这样的操作不能使用索引,可以用其他的办法处理,例如:数字类型,判断大于0,字符串类型设置一个默认值,判断是否等于默认值即可。

4.6、使用or连接条件,索引失效

在索引字段上使用not,<>,!=。不等于操作符是永远不会用到索引的,因此对它的处理只会产生全表扫描。 优化方法: key<>0 改为 key>0 or key<0

五、索引下推

索引条件下推优化(Index Condition Pushdown (ICP) )是MySQL5.6添加的,用于优化数据查询。 

  • 不使用索引条件下推优化时存储引擎通过索引检索到数据,然后返回给MySQL服务器,服务器然后判断数据是否符合条件。 
  • 当使用索引条件下推优化时,如果存在某些被索引的列的判断条件时,MySQL服务器将这一部分判断条件传递给存储引擎,然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件,只有当索引符合条件时才会将数据检索出来返回给MySQL服务器。

索引条件下推优化可以减少存储引擎查询基础表的次数,也可以减少MySQL服务器从存储引擎接收数据的次数。 

这个优化技术关键的操作就是将与索引相关的条件由MySQL服务器向下传递至存储引擎,由此减少IO次数。MySQL服务器到存储引擎是向下,传递的是与索引列相关的查询条件。

适用条件

  • 需要整表扫描的情况。比如:range, ref, eq_ref, ref_or_null 。适用于InnoDB 引擎和 MyISAM 引擎的查询。(5.6版本不适用分区表查询,5.7版本后可以用于分区表查询)。
  •  对于InnDB引擎只适用于二级索引,因为InnDB的聚簇索引会将整行数据读到InnDB的缓冲区,这样一来索引条件下推的主要目的减少IO次数就失去了意义。因为数据已经在内存中了,不再需要去读取了。 
  • 引用子查询的条件不能下推。 
  • 调用存储过程的条件不能下推,存储引擎无法调用位于MySQL服务器中的存储过程。
  • 触发条件不能下推。

参考文档: juejin.cn/post/684490… www.infoq.cn/article/ojk…

文章分类
后端
文章标签