小面试官教你 MySQL——引擎、索引和算法

amc

MySQL 引擎、索引和算法

弄懂了 MySQL 的基本 CURD 操作之后,下一个必须掌握的知识就是 MySQL 的索引。

我在面试中,经常喜欢针对 MySQL 的知识由浅入深地问下去,了解候选人对 MySQL 知识的了解到了哪一个层级。上一篇文章中的那些知识太基础了,我是不会拿来问的。因此我会问的第一个问题必然是 MySQL 的索引。

关于 MySQL 的索引,我大致会问下面几个问题:

  1. 你知道 InnoDB 索引所使用的算法是什么吗?
  2. 为什么 InnoDB 要使用 B+ 树而不是其他的数据结构呢?
  3. 在 InnoDB 中,是不是必须要有主键?如果建表的时候不指定主键会怎样?
  4. InnoDB 的主键和索引有什么区别?

要回答这两个问题,我们需要了解下面几个知识:引擎、索引、树

MySQL 索引的背景知识

MySQL 的引擎

MySQL 在设计之初,就允许嵌入不同的引擎。数据库的核心算法实际上是由引擎来实现的。早期 MySQL 数据库有以下三个主流引擎:

  1. MyISAM: 这是 MySQL 5.5 之前的默认引擎。由于其不支持事务处理,因此在新的系统中基本上没什么人用了。
  2. InnoDB: 这是 MySQL 5.6 以及之后的默认引擎。如果你不知道应该选什么引擎的话,选它基本没错。
  3. Memory: 这是一个特殊的引擎,该引擎存取的数据,全部放在内存中,不会落入磁盘。因此当数据库宕机或重启后,数据就会丢失。自从 Redis 兴起之后,memory 引擎也式微了。

由于新系统中几乎都选用了 InnoDB 引擎,因此下文中如无特别说明,则指的均为 InnoDB 引擎下的软件原理和行为。

存储系统中的 “页”

按照参考资料1 InnoDB 引擎的关键特性包括以下内容:

  1. 插入缓冲(Insert Buffer)
  2. 两次写(Double Write)
  3. 自适应哈希索引(Adapitve Hash Index)
  4. 异步 IO(Async IO)
  5. 刷新临接页(Flush Neighbor Page)

可以看到五个特性中,有四个特性是和存储直接相关的。学过计算机组成原理的话就会知道,计算机存储,根据其与 CPU 的距离由近到远有以下几个:

  1. 寄存器
  2. 缓存
  3. 内存
  4. 硬盘

其中寄存器对于程序员来说一般是不需要关注的;缓存无法直接在程序中影响和操作。对于绝大部分的计算机程序所操作的存储为内存和硬盘。操作系统读取内存和硬盘的时候,基本上以 “页” 为单位进行操作的。

为什么需要以 “页” 为单位操作呢?咱们先看内存:内存其实是完全可以随机读取的,也就是说 CPU 如果想要读取哪一个地址上的数据,那么一条指令就可以取到。但是应用程序上存取的不是实际内存,而是虚拟内存。而操作系统映射虚拟内存,只能以页为单位进行映射。因此,即便你操作的是内存,还是请自觉尽量对齐页。

重点则是硬盘。硬盘包括两种类型,一种是磁盘,也就是以磁性元件来存储数据的介质;另一种是所谓的 SSD,也就是固态硬盘。关于磁盘的读取原理和过程,我之前写过两篇文章《高性能磁盘 I/O 开发学习笔记 -- 硬件原理篇》和《高性能磁盘 I/O 开发学习笔记 -- 软件手段篇》。简单而言,由于硬件原理的限制,硬盘的读写有以下两个特点:

  1. 慢: 磁盘需要转才能转到对应的位置;SSD 好很多,但也比不上内存,毕竟要从长长的总线加载到内存中呢
  2. 块: 在软件中使用的 page,在硬件届经常是可以对应到 block。磁盘和 SSD 的数据修改都是以 block 为单位的

索引的原理

MySQL 定位的是大量数据的数据存储。每一个表中存储的数据目标是百万行起跳;数据数据结构较为简单,索引效率高的话,千万也没有问题。实际使用中,也有上亿的场景。这就像一个图书馆,我们需要对每一本书进行标记和索引,这样在查找书目(数据)时,才能够高效地查询到所需要的数据。

索引的原理,本质上就是解决快速查找和快速修改的目的。其次则是解决非常纠结的硬盘写入流程,整个过程中还需要各种防止崩溃和宕机——毕竟 MySQL 的数据一致性要求是很高的。

作为 MySQL,经常需要关注的数据结构有以下几个:哈希表、B-树、B+树。

哈希表

哈希(hash)算法相信大家都了解了,本文就不赘述。哈希算法的时间复杂度为 O(1)。在 MySQL 中,前文提到的三个主要引擎只有 Memory 引擎在索引中使用了哈希算法。那为什么其他引擎不是用这个算法呢?因为其他引擎需要考虑落地硬盘的问题啊。
哈希的算法虽然简单,但是哈希表在实际应用中需要考虑表的扩容和缩容的问题。当哈希表需要扩容/缩容的时候,整个表中的所有元素都可能需要重新排列。Memory 引擎是不落磁盘的,不 care。但即便是 Memory,也不适合存储大量数据。实际上在现实使用中,Memory 的使用场景已经不断被压缩,大部分已经被 Redis 所取代了。

B树

B树的原理其实相对而言比较简单,它就是一棵树。B树相比起最基本的树结构来说,比较特别的就是树的分裂和合并。主要就是在数据库的内容增加和减少的时候所发生。具体的过程读者可以查阅网上相关的资料,非常多。
B树的特点是:

  • 每一个节点可以多路分叉,不是二叉树。查询效率上比起二叉树而言肯定是较弱的。
  • 在其每一个节点上都会存储数据

不是 MySQL 的 MongoDB 使用的就是 B树。那么这里的问题是:为什么采用 B树,而不是搜索效率更高的红黑树呢?(面试考点注意!)

  1. 首先,B树每一个节点是有一定长度的,在引擎的设计中,会充分利用这一特性,结合前文所提到的 “页”,将同一个节点放在同一页中,大大提高硬盘的存取效率
  2. 其次,首先,红黑树在插入的过程中,经常会出现节点的旋转,旋转次数多了之后,可能导致附近的节点分散分布在硬盘的多个页中。那么在数据落地的时候,就会大大降低效率,并且提高失败的风险

需要注意的是,B树有时候也被称为B-树,但是有些文章中B树指的又不是B-树,而是二叉树(Binary Tree)。读者在识别这些用词的时候,要结合上下文区分。

B+树

B+树是本文的重点,因为 InnoDB 使用的树结构就是B+树。一个B+树的示意结构图如下:

看起来和B树是很像的,但是实际上有两个非常关键的差异:

  1. B树的数据不仅存在叶子结点中,分支节点也存储。但是B+树的数据仅仅存储在叶子结点中,分支节点仅保存索引。如果要查询到数据,那么必须查到叶子结点才能查到。
  2. B树的各个节点之间除了父子关系之外,不会有其他的关系。但是B+树的叶子节点之间,还有双向链表相互连接。这一点的好处是,对于设计遍历操作,或者是 offset - limit 的操作,能够大大地提高搜索效率

InnoDB 索引的分类

前文提到,InnoDB 所使用的算法是B+树;B+树上的非叶子结点存储的只是数据结构的索引,用于定位子结点用的,而不是 “数据库索引”。

那么问题来了:InnoDB B+ 树的叶子结点保存的是什么呢?这就引出了第一个分类:

按存储内容区分

Clustered Index,中文翻译不一,有 “聚簇索引”、“聚集索引”、“聚类索引” 等。聚簇索引指的是在叶子结点上,存储的数据就是完整的 MySQL 的一行数据。

那么在B+树的内部,用什么来索引叶子结点呢?答案是主键(main key)。在实际应用中,很大一部分的表在创建的时候都会把第一列定义为 int 或者 bigint 类型,并且指定为 auto increment类型并设定为主键。这是一个非常通用而且非常保险的做法。我们联系一下前文 B+ 树的特性就可以发现,如果针对这个自增ID直接进行查询、或者是以自增ID为条件进行大于、小于等范围操作,都非常高效。

那么如果在建表的时候不明确指定自增ID的话,会怎样呢?B+树失效?
对于 MyISAM 引擎来说,主键不是必须的,如果不指定主键,那就没有主键。但是在 InnoDB 中主键是必要的,如果不指定主键的话,那么 InnoDB 会隐含地添加一个 24 位宽的整型ID作为主键。但这会导致这个整型 ID 不可见,导致相关的一些操作比如 last inserted id 变得没有意义。因此在实际操作中我们还是需要显式地指定主键。

对于 InnoDB 来说,聚簇索引可以等同于就是主键的索引。

Secondary Index,中文翻译也不一,有 “非聚簇索引”、“辅助索引”、“二级索引” 等。在非聚簇索引的叶子结点上,存储的是对应的那一行 MySQL 数据的主键。

如果通过非聚簇索引,也就是除了主键以外的字段查找到了条目之后,此时 InnoDB 仅仅拿到了两个数据:一个是当前节点的索引列的值;另一个是主键。如果客户端还请求了其他数据的话,那么 InnoDB 需要再到当前表的聚簇索引中进行查阅。这个动作称为 “回表” 查询。

按组成逻辑区分

按照组成逻辑区分的话,InnoDB 索引可以分为:

  • 主键索引: 这就是前文提到的聚簇索引
  • 单列索引: 除了主键之外的非聚簇索引的最简单的模式
  • 联合索引: 顾名思义,就是多列的索引
  • 唯一索引: 这是单列索引和联合索引的特例,不同的就是在整个表中仅在符合单列或者多列条件所指定的同一个/一组值的数据行,仅允许存在一条

在这里需要特别说明的是联合索引。笔者之前一直以为联合索引就是索引了一个字段之后,在得到的结果中再对下一个字段进行索引。但后来查阅资料之后才知道其实并不是。

当创建一个联合索引时,索引中的每一个字段的值,都会在索引的数据结构中出现。这里我觉得这篇文章讲得已经非常准确和简要了,读者可以直接参阅。

覆盖索引

“覆盖索引” 并不是一种索引的类别,而是一种查询情况。前文提到过,在大部分按照索引进行的查询时,还需要进行回表查询从而得到客户端所需要的其他字段。但是前文也提到,如果你查询的字段,当前的索引已经完全覆盖了,那么这个时候 InnoDB 不会再进行多余的回表查询,而是在非聚簇索引查询中就直接把字段返回了。这个现象就称为 “覆盖索引”(covering index)。

空间索引

InnoDB 在 5.7.4 labs 版本中开始支持对空间索引的支持。简单而言,我们平时的索引就是一个纬度的,比如一个数字x。而空间索引则是对一个空间坐标系的索引,比如 (x, y) 或者是 (x, y, z)。

InnoDB 的索引采用 R 树,读者感兴趣的话可以参阅相关资料进一步学习。在大部分的应用场景中,如果不涉及地理数据的话,空间索引我们用得还是比较少的。

回答面试题

好了,前面的面试题,我们就可以大致地回答出来了:

问:InnoDB 索引所使用的算法是什么?
答:B+树
问:为什么 InnoDB 要使用 B+ 树而不是其他的数据结构呢?
答:相比起红黑树,B树的节点以页为单位,而页则与硬盘中的页相互绑定,因此可以优化硬盘存取的效率
相比起红黑树,B树的深度比较稳定,查找的耗时比较可预期——这个其实是B树的分裂和旋转策略所决定的,读者可以进一步阅读资料了解
相比起B树,B+树的叶子结点之间包含双向链表,可以极大地优化遍历类和 offset - limit 类查询的耗时
InnoDB 在使用 B+树中,使用了非聚簇索引,这一算法可以极大地减少索引所占的空间,从而大大减少索引占用的内存和硬盘空间,提高索引重建效率
其实这个答案不唯一,读者如果感兴趣还可以进一步阅读参考资料
问:在 InnoDB 中,是不是必须要有主键?如果建表的时候不指定主键会怎样?
答:前文已经回答了:主键是必须有的,如果不指定的话,InnoDB 会自动创建一个6字节的自增ID
问:InnoDB 的主键和索引有什么区别?
答:InnoDB 的主键是一种特殊的索引,也就是聚簇索引;而其他的索引都是非聚簇索引。区别就是聚簇索引上保存的是完整的一行数据,而非聚簇索引上保存的是索引值以及主键

参考资料


本文章采用 知识共享署名-非商业性使用-相同方式共享 4.0 国际许可协议 进行许可。

原作者: amc,欢迎转载,但请注明出处。

原文标题:小面试官教你 MySQL——引擎、索引和算法

发布日期:2020-11-09

原文链接:https://cloud.tencent.com/developer/article/1336510,也是本人的博客

阅读 215

后台 / 嵌入式全栈之路
曾经的嵌入式 / 后台开发一枚,现在开始走向架构。本专栏没有高深技术,只讲基础组件、工具,请放心食用

电子和互联网深耕多年,拥有丰富的嵌入式和服务器开发经验。现于负责腾讯云未来社区后台开发

792 声望
82 粉丝
0 条评论

电子和互联网深耕多年,拥有丰富的嵌入式和服务器开发经验。现于负责腾讯云未来社区后台开发

792 声望
82 粉丝
宣传栏