MySQL 高级 —— 索引实现的思考

引言

最近看了一个公开课,是有关MySQL对索引设计的思考。详细讲解了几种索引实现的设计思考与利弊辨析,讨论了为什么MySQL默认情况下会使用B树索引,B+树索引又对B树做了哪些结构改进。

本片博客通过个人的学习理解和总结,由几种简单的索引数据结构,逐渐展开对B+树索引的探究和思考。

一、常见的索引实现

索引是帮助MySQL 高效获取数据的有序数据结构。它可以在几十毫秒,最多几百毫秒内快速定位数据在磁盘中的位置。它并非是MySQL中特有的概念,实际上很多地方都有索引思想,如 Java 中的 HashMap 其本身就可以理解为一种索引。

索引有以下几种常见的可供实现的数据结构:

  1. 二叉树
  2. 红黑树
  3. Hash表
  4. B-Tree

1.1 二叉树实现下的索引

二叉树可能是最容易被想到用作索引实现的一种方式,实际其他很多索引的实现都是二叉树的变种。下图是一个普通的二叉树实现索引的基本方式:

如上图,左侧是表中记录,右侧是建立在 col2 上的索引,由根节点向下,每个节点的左子节点要小于自身,右节点要大于自身,以此来形成一种有序的结构。每个节点不仅存储 col2 列上的值,同时还会指向其对应记录的磁盘地址。如col2 = 89,其磁盘地址为 0x77。

由于表数据存储于磁盘上,且表数据不可能全部加载到内存中供程序查询,在没有建立索引的情况下,如果想要找到 col2 = 89 这条记录,就必须从表中的第一行记录开始,执行6次磁盘IO 操作,而建立了 col2 列的索引后,col2 = 89 只需要从索引的根节点开始执行1次磁盘IO即可找到对应记录,避免了全表扫描,从而极大地降低了磁盘IO,提高了性能。

1.2 普通二叉树索引的弊端

还是以下图为例,如果我们的索引建立在 col1 上,思考索引查找会有怎样的问题?

根据表中的数据,col1 本身是呈现一种递增的态势,建立索引的过程也是从表中第一条数据开始,以此将列值放入二叉树中,那么上图中的数据就会组织成如下图所示的二叉树索引结构:

只有单右侧的二叉树,已经退化为一个链表,而链表对于查找操作来说是个非常糟糕的结构,完全无法提升搜索性能。

这是因为普通的二叉树并没有元素平衡分配规则,对树的高度做有效的控制,才会出现这种情况。所以 MySQL并没有采用这种简单的二叉树结构。

1.3 红黑树索引

红黑树对于了解 Java 8 HashMap 底层实现的开发者并不陌生。Java 中的 HashMap实现由 Java 7 “数组 + 链表”的组合变为了如今的“数组 + 红黑树”,链表变为红黑树,就是对查找性能的重大革新。

红黑树也是二叉树的一种,确切的说应该是平衡二叉树的一个子类。所谓平衡二叉树,就是利用一定的平衡规则,将元素尽可能地分配到根节点的两侧,尽量弥补普通二叉树在单边增长情况下退化为链表的缺陷。

我们来看下实现了 Java 8 HashMap的红黑树会如何为 col1 建立索引。

首先,为红黑树添加col1 = 1:

然后,为红黑索引树添加 col1 = 2,从根节点开始,由于2 >= 1,继续查找根节点右子树,又由于右侧无任何元素,直接插入:

继续添加 col1 = 3,从根节点开始,3 >= 1,查找根节点右子树,3 >= 2,继续查找右子树,右侧无元素,插入元素,由于节点和父节点都是红色,且节点本身是右子节点,父节点也是右子节点,根据红黑树的调整规则,旋转以调整额外的红色节点:

依据红黑树的插入规则,最后生成的索引结构为:

可以看到,经过红黑树的优化,一定程度上解决了二叉树退化链表的问题。

虽然利用红黑树这种平衡的特性,但MySQL 依然没有使用红黑树作为索引的实现。

这是因为,红黑树依然无法有效控制树的高度。

当表中的数据多达上百万、千万的级别,对某个列建立索引,意味着需要有上百万个索引值。这样庞大的树结构高度可能会达到20 ~ 30,即便是在内存中对树节点进行检索,依然可能需要搜索20 到 30 次才能够找到对应的索引值。这就是MySQL 索引实现的又一个不得不重视的因素——树的高度。

很遗憾,红黑树对树的高度不可控,无法满足 MySQL 对索引的要求。

1.4 B树与B+树

为了降低树的高度,树中节点必须横向扩展以容纳足够的索引值。

B-Tree(B树,也叫多叉平衡树)具有以下一些重要特点:

  1. 所有元素不重复;
  2. 叶子节点具有相同的深度,叶子节点指针为空;
  3. 节点中的数据从左到右递增排列。

结构简图如下所示:

B树索引在使用的时候,会先把根节点整个加载到内存中,然后在根节点中随机查找。

比如,我们需要查找 49 这个节点,那么先将根节点整个加载到内存,发现49在15和56之间,那么会将对应的节点整个加载到内存,再重复上述步骤,直到找到49。

以col1为例,构建B树索引(假设树最大高度为3)如下:

最大高度为4时,col1的B树索引如下:

B树会根据设定的最大深度(即树高度)来调整每个节点所能容纳的元素个数,因此,根据最大深度的不同,可能特定的B树也会有不同的结构。

那每个节点最大容量是多少呢?

内存与磁盘的IO交互有一个数据量限制,一般是4KBytes。因此,MySQL为了将节点加载到内存的效率最大化,设置一个文件的默认“分页大小”,它恰好是读取量限制的4倍,即16K:

SHOW GLOBAL STATUS LIKE 'Innodb_page_size';

Variable_name     Value   
----------------  -----
Innodb_page_size  16384   

实际上,MySQL并没有安全遵照B树来设计索引,而是采用了B树的变种——B+树。

B+树的重要特性有如下这些:

  1. 非叶子节点不存储data,只存储索引(冗余),可以放更多的索引;
  2. 叶子节点包含所有索引值;
  3. 叶子节点用指针连接,提高区间访问的性能。

我们需要关注MySQL的B+树索引对 B树的两步优化。

第一点是将所有元素的数据部分移动到了叶子节点上,非叶子节点不存储任何地址数据。

这是因为考虑到每个节点不能太大,MySQL对其限制为16K,因此,在大小限制的情况下,将数据的存储空间让出,那么每个节点就可以容纳更多的“索引”,从而极大的提高了索引树每个节点的数据容量。

以主键为 bigint 类型为例,我们计算一下B+树的索引容量。

非叶子节点每个元素后面会有一个指向下一个节点的地址,MySQL底层设置该值大小为6Bytes,而bigint类型的大小是 8Bytes,因此非叶子节点中的每个索引元素占8 + 6 = 14 Bytes,根据前面的说明,每个节点大小为16KBytes,那么一个非叶子节点最多可以容纳16 * 1024/14 = 1170个索引元素。

对于一个最大深度为3的B+树,在深度为3 的叶子节点上,每个元素的数据大约1K(实际上大部分建立索引的元素远不会有这么大),根据每个节点最大16K的限制条件,那么每个叶子节点最多可以容纳16个索引元素,因此,这个B+树的最大容量可以为1170*1170*16 ≈2000+万,而实际上B+树的存储容量要比这个数字大很多。

B+树的叶子节点包含全部索引元素,而其他非叶子节点中的索引元素是一种“冗余”元素,它们也是索引元素,但不具备存储具体磁盘地址的功能,只作为构建B+树的查找路径的功能型存在。

第二点是所有叶子节点之间会使用双向指针连接,而B树本身所有叶子节点中的索引元素从左到右依次递增,那么根据这样的结构,如果有一个条件WHERE col >20,那么MySQL只需要定位一次索引元素的位置,就可以快速获取该索引元素后面的全部索引元素,极大的降低了索引搜索的次数。

1.5 Hash索引

Hash算法会对目标参数进行散列,从而得到一串“随机的”编码。

MySQL同样支持Hash索引,与树型索引不同,Hash索引可以快速定位到具体的索引值,其性能是树型索引远远赶不上的。

当我们通过 WHERE 子句查询某个列值的时候,比如:WHERE col1 = 89,MySQL会首先分析该列是否有建立的索引,如果col1建立了一个Hash索引,那么MySQL会对89进行同样的散列操作,获得该记录存储的磁盘地址,然后直接取得数据。

但是为什么绝大多数情况下我们不会用到Hash索引呢?

这是因为通过Hash算法建立的索引对于范围查询,完全排不上用场。

所以,根据这样的特性,如果确定某个列只会用到精确查找,那么可以考虑使用Hash索引来进一步提升性能,当然,这个秘密武器可能只有在极少的情况下才能派上用场了。

二、MySQL存储引擎的B+树索引

B-Tree对索引列是顺序组织存储的,所以很适合查找范围数据。

2.1 InnoDB 索引实现

InnoDB 是以聚集的方式实现索引的。

  1. 表数据文件本身就是按 B+Tree 组织的一个索引文件
  2. 聚集索引-叶子节点包含了完整的数据记录
  3. 为什么InnoDB 必须有主键,并且推荐整型的自增主键?
  4. 为什么非主键索引结构叶子节点存储的是主键值?(一致性和节省存储空间)

InnoDB会将表数据以及索引都存储在 .ibd 文件内,其内部结构如下图示例:

InnoDB索引是一个典型的聚集索引,所谓聚集索引就是叶子节点中的索引不仅存储了索引元素,同时也包含了索引值所在行的其他列值。而MyISAM的索引与数据是存储在两个文件中(数据存在.MYD,索引存在.MYI),因此索引元素只存储了对应的数据地址,是非聚集型索引。由于聚集所有会存储数据信息,所以查找效率要比非聚集索引高。

InnoDB必须为表添加一个主键列,这样才能通过主键列将表数据组织成一个B+树,这也是由于InnoDB聚集索引的存储形式所决定的。如果开发者没有给InnoDB表添加主键,MySQL会自动从表的第一列开始寻找适合作为主键的列,如果无法找到,那么就在后台自动为表添加一个类似主键的rowid列,以维护B+树结构。

InnoDB表建议使用整型作为主键,因为这种整型数据维护的B+树,可以快速进行索引的比较并定位,如果使用类似UUID的字符串作为主键,那么不论是维护主键树还是查找主键,都会一定程度上限制索引元素比较的速度,从而影响整体的性能。

那为什么又建议使用自增主键呢?这是因为B+树的所有叶子节点中的所有元素要求必须从左到右依次递增,为了维护这样的结构,如果在主键中有非递增的情况,就必须向中间插入索引元素保持递增,这就可能会涉及到结构的旋转、冗余节点的提升、树结构的平衡等多项操作,严重影响B+索引树创建的效率。

2.2 MyISAM索引实现

MyISAM索引文件和数据文件是分离的(非聚集)

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页