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

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 的主键是一种特殊的索引，也就是聚簇索引；而其他的索引都是非聚簇索引。区别就是聚簇索引上保存的是完整的一行数据，而非聚簇索引上保存的是索引值以及主键

参考资料

• MySQL技术内幕 - InnoDB存储引擎(第2版)
• b树和b+树的区别
• 平衡二叉树、B树、B+树、B*树 理解其中一种你就都明白了
• 联合索引在B+树上的结构
• 联合索引在B+树上的存储结构及数据查找方式
• MySQL索引背后的数据结构及算法原理
• 我以为我对Mysql索引很了解，直到我遇到了阿里的面试官
• R树 - 维基百科，自由的百科全书
• Mysql空间索引
• 空间索引 - 各数据库空间索引使用报告
• 地理空间数据Geometry在MySQL中使用（一）

---

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

原作者： amc，欢迎转载，但请注明出处。

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

发布日期：2020-11-09

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