搞定面试官 - 可以介绍一下 MySQL InnoDB 引擎的索引模型嘛？

大家好，我是啊粥。

接下来的几天我们会开启一个全新的系列文章，那就是搞定面试官系列，我会把常见的面试知识通过这个专栏写出来，比如我们常见的 Java、MySQL、Redis、MQ 以及其他的一些技术框架。

现在最先开启的是 MySQL 系列，今天先来分享我们最常见的一个面试问题，那就是关于 MySQL 的索引。

相信很多人在面试中会遇到关于 MySQL 索引的相关知识，从 MySQL 的架构到索引模型，然后再到表设计，SQL 优化等等。

首先，我们来看下索引是什么？

索引概述

索引是一种帮助 MySQL 高效获取数据的有序数据结构。

它的出现就是为了提高数据的查询效率，就像书的目录一样。如果一本书没有目录，那我们就必须一页一页的去找我们自己想要的东西。

但是一旦有了目录，那我们就可以快速的通过目录了解书的全貌，然后再根据具体的页码找到我们自己想要的东西。

MySQL 的索引就是类似的作用，帮助我们快速且高效的获取数据。

索引的常见模型

索引的实现有不同的方式，本质上可以提高查询效率的数据结构有很多种，这里先介绍三种比较常见的数据结构：

• 哈希表

• 有序数组

• 树形结构

哈希表

以键值（key-value）形式存储数据的结构。哈希的方式非常简单，是用一个哈希函数把 key 换算成一个确定的位置，然后把 value 放在数组的这个位置。

因为哈希算法的原因，多个 key 可能会存在 Hash 冲突，这个时候一般会使用拉链法来解决冲突，也就是相同 Hash 值的拉出一个链表。

哈希表这种结果用来做等值查询速度非常快，但是因为它是无序的，如果是用来做区间查询的话，会非常慢。

比如，我们有一张用户的姓名和身份照号的表，如下是使用哈希表的方式存储：

有序数组

以上数据如果使用有序数组来存储的话，他的结构是：

有序数组可以使用二分法很快进行数据检索，时间复杂度是 O(log(N))。但是更新数据会比较麻烦，因为要维持顺序，所以每次数据插入需要移动的元素太多了，成本太高。

所以，有序数组只适用于静态存储引擎。

搜索树

同理，使用二叉树来存储的话，结构如下

二叉搜索树的特点是：父节点左子树所有结点的值小于父节点的值，右子树所有结点的值大于父节点的值。这样如果你要查 id_card_03 的话，按照图中的搜索顺序就是按照 USERA -> USERC -> USERF -> USER_03 这个路径得到。

这个时间复杂度是 O(log(N))。

当然为了维持 O(log(N)) 的查询复杂度，就需要保持这棵树是平衡二叉树。为了做这个保证，更新的时间复杂度也是 O(log(N))。

二叉树是搜索效率最高的，但是实际上大多数的数据库存储却并不使用二叉树。其原因是，索引不止存在内存中，还要写到磁盘上。

N 叉树由于在读写上的性能优点，以及适配磁盘的访问模式，已经被广泛应用在数据库引擎中了。

但是，二叉树也是分很多种的，比如有 B-Tree、B+Tree 以及红黑树等等。

一般来说，一棵 100 万节点的平衡二叉树，树高 20。一次查询可能需要访问 20 个数据块。在机械硬盘时代，从磁盘随机读一个数据块需要 10 ms 左右的寻址时间。也就是说，对于一个 100 万行的表，如果使用二叉树来存储，单独访问一个行可能需要 20 个 10 ms 的时间，这个查询可真够慢的。

所以，我们需要找到另一种更加适合用来做 MySQL 索引引擎的数据结构。

Innodb 的索引模型

InnoDB 使用了 B+ 树作为索引结构，所有的元素都会出现在叶子节点上，同时，叶子节点之间会通过双向链表连接。

InnoDB 使用 B+ Tree 使用索引结构主要有以下原因：

• 相比于二叉树，B+ 数层级更少，搜索效率更高。

  • 以 InnoDB 的一个整数字段索引为例，这个 N 差不多是 1200。这棵树高是 4 的时候，就可以存 1200 的 3 次方个值，这已经 17 亿了。考虑到树根的数据块总是在内存中的，一个 10 亿行的表上一个整数字段的索引，查找一个值最多只需要访问 3 次磁盘。其实，树的第二层也有很大概率在内存中，那么访问磁盘的平均次数就更少了。

• B 树的叶子节点以及非叶子节点，都会保存数据，这样会导致一页中存储的键值减少，指针跟着减少，要保存同样多的数据，只能增加树的高度，导致搜索性能降低。

在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。

每一个索引在 InnoDB 里面对应一棵 B+ 树。

假设表中 T1 ~ T5 的 (id,age) 值分别为 (1,10)、(2,20)、(3,30)、(5,50) 和 (6,60)，两棵索引树的示例示意图如下。

主键索引树

二级索引树

从图中不难看出，两个索引输的叶子结点内容是不一样的。根据叶子节点的内容，可以把索引类型分为主键索引和非主键索引。

主键索引的叶子节点存的是整行数据。而非主键索引的叶子结点存储的是主键的值。

在 InnoDB 里，主键索引也被称为聚簇索引（clustered index），非主键索引也被称为二级索引（secondary index）。

基于主键索引和普通索引的查询有什么区别？

如果语句是 select * from test where id = 5，即主键查询方式，则只需要搜索 id 这棵 B+ 树；

如果语句是 select * from test where age = 50，即普通索引查询方式，则需要先搜索 age 索引树，得到 id 的值为 5，再到 id 索引树搜索一次。

这个过程称为回表，也就是说，基于非主键索引的查询需要多扫描一棵索引树。

因此，我们在应用中应该尽量使用主键查询。

索引维护的开销

B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护。

以上面这个图为例，如果插入新的行 id 值为 7，则只需要在 T5 的记录后面插入一个新记录。

如果新插入的 ID 值为 4，就相对麻烦了，需要在逻辑上挪动后面的数据，空出位置给 4，B+ 树要做结构变化，维持平衡和节点顺序。

而更糟的情况是，如果 T5 所在的数据页已经满了，根据 B+ 树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去，这个过程称为页分裂。

在这种情况下，性能自然会受影响。

除了影响性能外，页分裂操作还会影响数据页的利用率，因为原本放在一个页的数据，现在分到两个页中，整体空间利用率会降低大约 50%。

同理，有分裂就有合并。

当相邻两个页由于删除了数据，利用率很低之后，会将**数据页做合并，**合并的过程，可以认为是分裂过程的逆过程。将利用率低的页数据进行合并之后，另一个数据叶会被标记为可复用供后续数据利用，

所以我们为什么建议使用自增主键，因为自增主键的插入数据模式，正好符合了我们前面提到的递增插入的场景。

每次插入一条新记录，都是追加操作，所以不涉及到挪动其他记录，也不会触发叶子节点的分裂。反之，如果是使用业务逻辑的字段做主键，则往往没法保证有序插入，这样写数据成本会相对较高。

好了，我们做个简单的总结：

MySQL 选取了 B+ 树作为它的索引模型来存储数据，不但可以合理的利用磁盘特性，而且可以很方便的做范围查询，同时，因为 B+ 树在非叶子节点不存储数据，只在叶子节点存储数据，同等高度的 B+ 树和 B 树，B + 树可以存更多的数据，反之，同等的数据量，B+ 树高度更低，假设每次节点搜索都是一次磁盘 IO 的话，那么，B+ 树可以花更少的时间来获取到所需要的数据。

MySQL 的索引模型我们今天就先介绍到这里，文末留给大家一个问题，你有没有遇到过，你明明执行 delete 命令把表里的数据都删除了，但是表文件的大小却没有发生变化的情况？

如果有的话，欢迎留言我们一起讨论，答案在下一篇文章中揭晓。

我是啊粥，公号同名，关注我，我们一起在技术海洋中向上生长。

我是啊粥，公号同名，关注我，我们一起在技术海洋中向上生长。