MySQL 索引的存储结构

Author: ACatSmiling

Since: 2024-07-22

索引的存储结构

为什么使用索引

索引是存储引擎用于快速找到数据记录的一种数据结构。 常将索引比作教科书的目录部分（实际上不是完全一样），通过目录找到对应文章的页码，便可快速定位到需要的文章。MySQL 中也是一样的道理，进行数据查找时，首先查看查询条件是否命中某条索引，符合则通过索引查找相关数据，如果不符合则需要全表扫描，即一条一条的查找记录，直到找到与条件符合的记录。

如上图所示，数据库没有索引的情况下，数据分布在硬盘不同的位置上面，读取数据时，摆臂需要前后摆动查找数据，这样操作非常耗时。如果数据顺序摆放，也需要从 1 到 6 行按顺序读取，这样就相当于进行了 6 次 I/O 操作，依旧非常耗时。如果不借助任何索引结构来帮助快速定位数据，假设查找 Col2 = 89 这条记录，就需要逐行查找、比较。从 Col2 = 34 开始，进行比较，发现不是，继续下一行。当前的表只有不到 10 行数据，但是如果表很大，有上千万条数据，就意味着要做很多次磁盘 I/O 才能找到。因为要查找 Col2 = 89 这条记录，CPU 必须先去磁盘查找这条记录，找到之后加载到内存，再对数据进行处理。这个过程最耗时间的就是磁盘 I/0（涉及到磁盘的旋转时间（速度较快）和磁盘的寻道时间（速度慢，耗时））。

假如给数据使用二叉树这样的数据结构进行存储，如下图所示：

对字段 Col2 添加索引，就相当于在磁盘上为 Col2 维护了一个索引的数据结构，即这个二叉搜索树。二叉搜索树的每个节点存储的是(K, V) 结构，key 是 Col2，value 是该 key 所在行的文件指针（地址）。比如：该二叉搜索树的根节点就是 (34, 0x07)。对 Col2 添加了索引之后，这时再去查找 Col2 = 89 这条记录，会先去查找该二叉搜索树（二叉树的遍历查找）。读 34 到内存，因为 89 > 34，继续右侧数据，读 89 到内存，因为 89 = 89，找到数据并返回。然后，根据找到的节点的 value 快速定位到要查找的记录对应的地址。通过上述过程，可以发现，只需要查找两次（两次磁盘 I/O），就可以定位到记录的地址，查询速度也就提高了。

这就是为什么要使用索引的原因，其目的就是为了减少磁盘 I/O 的次数，加快查询的速度。

索引及其优缺点

索引概述

MySQL 官方对索引的定义为：索引（Index）是帮助 MySQL 高效获取数据的数据结构。

索引的本质：索引是数据结构。可以简单理解为"排好序的快速查找数据结构"，满足特定查找算法。这些数据结构以某种方式指向数据， 这样就可以在这些数据结构的基础上实现高级查找算法。

索引是在存储引擎中实现的，因此，每种存储引擎的索引，不一定完全相同，并且每种存储引擎不一定支持所有索引类型。同时，存储引擎可以定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少 16 个索引，总索引长度至少为 256 字节。有些存储引擎支持更多的索引数和更大的索引长度。

优点

  • 类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的 I/O 成本，这也是创建索引最主要的原因。
  • 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
  • 在实现数据的参考完整性方面，可以加速表和表之间的连接。换句话说，对于有依赖关系的子表和父表联合查询时，可以提高查询速度。
  • 在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间，降低 CPU 的消耗。

缺点

增加索引也有许多不利的方面，主要表现在如下几个方面：

  • 创建索引和维护索引要耗费时间，并且随着数据量的增加，所耗费的时间也会增加。
  • 索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间， 存储在磁盘上 ，如果有大量的索引，索引文件就可能比数据文件更快达到最大文件尺寸。
  • 虽然索引大大提高了查询速度，同时却会降低更新表的速度。当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

因此，选择使用索引时，需要综合考虑索引的优点和缺点。

Tips：索引可以提高查询的速度，但是会影响插入记录的速度。这种情况下，最好的办法是先删除表中的索引，然后插入数据，插入完成后再创建索引。

InnoDB 中索引的推演

索引之前的查找

先来看一个精确匹配的例子：

SELECT [列名列表] FROM 表名 WHERE 列名 = xxx;
在一个页中的查找

假设目前表中的记录比较少，所有的记录都可以被存放到一个页中，在查找记录的时候，可以根据搜索条件的不同分为两种情况：

  • 以主键作为搜索条件：
    • 可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录，即可快速找到指定的记录。
  • 以其他列作为搜索条件：
    • 因为在数据页中没有对非主键列建立所谓的页目录，所以无法通过二分法快速定位相应的槽。这种情况下，只能从最小记录开始，依次遍历单链表中的每条记录，然后对比每条记录，是不是符合搜索条件。很显然，这种查找的效率是非常低的。
在很多页中的查找

大部分情况下，表中存放的记录都是非常多的，需要很多的数据页来存储这些记录。在很多页中查找记录，可以分为两个步骤：

  • 第一步：定位到记录所在的页。
  • 第二步：从记录所在的页中查找相应的记录。

在没有索引的情况下，不论是根据主键列或者其他列的值进行查找，由于并不能快速的定位到记录所在的页，所以只能从第一个页沿着双向链表一直往下找，然后在每一个页中根据上面的查找方式去查找指定的记录。因为要遍历所有的数据页，所以这种方式显然是超级耗时的。如果一个表有一亿条记录呢？此时索引应运而生。相比一本书的目录，索引更像是图书馆中的书架结构，每一个书架相当于一个数据页，书架上的每一本书相当于表中的一条记录。

设计索引

新建表：

mysql> CREATE TABLE index_demo(
    -> c1 INT,
    -> c2 INT,
    -> c3 CHAR(1),
    -> PRIMARY KEY(c1)
    -> ) ROW_FORMAT = Compact;
Query OK, 0 rows affected (0.03 sec)

这个新建的 index_demo 表中有 2 个 INT 类型的列，1 个 CHAR(1) 类型的列，而且规定了 c1 列为主键，这个表使用 Compact 行格式来实际存储记录。这里简化了 index_demo 表的行格式示意图：

只在示意图里展示记录的这几个部分：

  • record_type：记录头信息的一项属性，表示记录的类型，0 表示普通记录、2 表示最小记录、3 表示最大记录、1 暂时还没用过，后面讲。
  • next_record：记录头信息的一项属性，表示下一条地址相对于本条记录的地址偏移量，后面演示用箭头来表明下一条记录是谁。
  • 各个列的值 ：这里只记录在 index_demo 表中的三个列，分别是 c1 、 c2 和 c3。
  • 其他信息 ：除了上述 3 种信息以外的所有信息，包括其他隐藏列的值以及记录的额外信息。

将记录格式示意图的其他信息项暂时去掉并把它竖起来的效果就是这样：

把一些记录放到页里的示意图就是：

一个简单的索引设计方案

在根据某个搜索条件查找一些记录时为什么要遍历所有的数据页呢？因为各个页中的记录并没有规律，所以并不知道搜索条件会匹配哪些页中的记录，因此不得不依次遍历所有的数据页。所以如果想快速的定位到需要查找的记录在哪些数据页中该咋办？此时，可以为快速定位记录所在的数据页建立一个目录，而建这个目录必须完成下边这些事：

1. 下一个数据页中用户记录的主键值，必须大于上一个页中用户记录的主键值。

假设：每个数据页最多能存放 3 条记录（实际上一个数据页非常大，可以存放很多记录），有了这个假设之后，向 index_demo 表插入 3 条记录：

mysql> INSERT INTO index_demo VALUES(1, 4, 'u'), (3, 9, 'd'), (5, 3, 'y');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

那么这些记录已经按照主键值的大小，串联成一个单向链表了，如图所示：

从图中可以看出，index_demo 表中的 3 条记录，都被插入到编号为 10 的数据页中了。此时，再插入一条记录：

mysql> INSERT INTO index_demo VALUES(4, 4, 'a');
Query OK, 1 row affected (0.01 sec)

因为页 10 最多只能放 3 条记录，所以不得不再分配一个新页：

注意，新分配的数据页编号可能并不是连续的。它们只是通过维护着上一个页和下一个页的编号而建立了链表关系。另外，页 10 中用户记录最大的主键值是 5，而页 28 中有一条记录的主键值是 4，因为 5 > 4，所以这就不符合下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值的要求，所以在插入主键值为 4 的记录的时候，需要伴随着一次记录移动，也就是把主键值为 5 的记录移动到页 28 中，然后再把主键值为 4 的记录插入到页 10 中，这个过程的示意图如下：

这个过程表明了在对页中的记录进行增删改操作的过程中，必须通过一些诸如记录移动的操作，来始终保证这个状态一直成立：下一个数据页中用户记录的主键值，必须大于上一个页中用户记录的主键值。而这个过程，称为页分裂。

2. 给所有的页建立一个目录项。

由于数据页的编号可能是不连续的，所以在向 index_demo 表中插入许多条记录后，可能是这样的效果：

因为这些16 KB的页在物理存储上是不连续的，所以如果想从这么多页中根据主键值快速定位某些记录所在的页，需要给它们做个目录，每个页对应一个目录项，每个目录项包括下边两个部分：

  • 页的用户记录中最下的主键值，用key表示。
  • 页号，用page_no表示。

所以，为上边几个页做好的目录，就像这个样子：

以页 28 为例，它对应目录项 2，这个目录项中包含着该页的页号 28，以及该页中用户记录的最小主键值 5。只需要把几个目录项在物理存储器上连续存储（比如：数组），就可以实现根据主键值快速查找某条记录的功能了。比如，查找主键为 20 的记录，具体查找过程分为两步：

  • 第一步，先从目录项中根据二分法快速确定出主键值为 20 的记录在目录项 3 中（因为 12 < 20 < 209），它对应的页是页 9。
  • 第二步，再根据前边说的在页中查找记录的方式，去页 9 中定位具体的记录。

至此，针对数据页做的简易目录就搞定了。这个目录有一个别名，称为索引。

InnoDB 中的索引方案

1. 迭代一次：目录项纪录的页。

前面称为一个简单的索引设计方案，是因为为了在根据主键值进行查找时，使用二分法快速定位具体的目录，而假设所有目录项都可以在物理存储器上连续存储，但是这样做有几个问题：

  • InnoDB 是使用页来作为管理存储空间的基本单位，最多能保证16 KB的连续存储空间，而随着表中记录数量的增多，需要非常大的连续的存储空间，才能把所有的目录项都放下，这对记录数量非常多的表是不现实的。
  • 同时，也会时常对记录进行增删操作，假设把页 28 中的记录都删除了，那意味着目录项 2 也就没有存在的必要了，这就需要把目录项 2 后面的目录项都向前移动一下，这样牵一发而动全身的操作效率很差。

所以，需要一种可以灵活管理所有目录项的方式。而目录项实际上跟普通的用户记录差不多，只不过目录项中的两个列是主键和页号而已，为了和用户记录做一下区分，把这些用来表示目录项的记录称为目录项记录。那 InnoDB 是怎么区分一条记录是普通的用户记录还是目录项记录呢？答案是使用记录头信息里的record_type属性，它的各个取值含义如下：

  • 0：普通的用户记录。
  • 1：目录项记录。
  • 2：最小记录。
  • 3：最大记录。

然后，把前边使用到的目录项放到数据页中的样子就是这样：

从图中可以看出来，新分配了一个编号为 30 的页来专门存储目录项记录。

目录项记录和普通的用户记录的对比：

  • 不同点：
    • record_type 值不同：目录项记录的 record_type 值是1，而普通用户记录的 record_type 值是0。
    • 列数不同：目录项记录只有主键值和页的编号两个列，而普通的用户记录的列是用户自己定义的，可能包含很多列，另外还有 InnoDB 自己添加的隐藏列。
    • 了解：记录头信息里还有一个叫min_rec_mask的属性，只有在存储目录项记录的页中的主键值最小的目录项记录的 min_rec_mask 值为 1，其他别的记录的 min_rec_mask 值都是 0。
  • 相同点：
    • 两者用的是一样的数据页，都会为主键值生成Page Directory（页目录），从而在按照主键值进行查找时可以使用二分法来加快查询速度。

现在以查找主键为 20 的记录为例，根据某个主键值去查找记录的步骤，可以大致拆分成下边两步：

  • 第一步：先到存储目录项记录的页，也就是页 30 中通过二分法快速定位到对应目录项，因为 12 < 20 < 209 ，所以定位到对应的记录所在的页就是页 9。
  • 第二步：再到存储用户记录的页 9 中根据二分法快速定位到主键值为 20 的用户记录。

2. 迭代两次：多个目录项纪录的页。

虽然说目录项记录中只存储主键值和对应的页号，比用户记录需要的存储空间小多了，但是毕竟一个页只有 16 KB 大小，能存放的目录项记录也是有限的，如果表中的数据太多，以至于一个数据页不足以存放所有的目录项记录，该如何处理呢？

这里假设一个存储目录项记录的页最多只能存放 4 条目录项记录，所以如果此时再向上图中插入一条主键值为 320 的用户记录，那就需要分配一个新的存储目录项记录的页：

从图中可以看出，插入了一条主键值为 320 的用户记录之后，需要两个新的数据页：

  • 为存储该用户记录而新生成了页 31。
  • 因为原先存储目录项记录的页 30 的容量已满（前边假设只能存储 4 条目录项记录），所以不得不需要一个新的页 32 来存放页 31 对应的目录项。

现在因为存储目录项记录的页不止一个，所以如果想根据主键值查找一条用户记录大致需要 3 个步骤，以查找主键值为 20 的记录为例：

  • 第一步：确定目录项记录页。现在的存储目录项记录的页有两个，即页 30 和页 32，又因为页 30 表示的目录项的主键值的范围是 [1, 320)，页 32 表示的目录项的主键值不小于 320 ，所以主键值为 20 的记录对应的目录项记录在页 30 中。
  • 第二步：通过目录项记录页确定用户记录真实所在的页。在一个存储目录项记录的页中通过主键值定位一条目录项记录的方式说过了。
  • 第三步：在真实存储用户记录的页中定位到具体的记录。

3. 迭代三次：目录项记录页的目录页。

现在问题来了，在上面这个查询步骤的第一步中，需要定位存储目录项记录的页，但是这些页是不连续的，如果表中的数据非常多则会产生很多存储目录项记录的页，那怎么根据主键值快速定位一个存储目录项记录的页呢？答案是为这些目录项记录的页再生成一个更高级的目录，就像是一个多级目录一样，大目录里嵌套小目录，小目录里才是实际的数据，所以现在各个页的示意图就是这样子：

如图，生成了一个存储更高级目录项的页 33，这个页中的两条记录分别代表页 30 和页 32，如果用户记录的主键值在 [1, 320) 之间，则到页 30 中查找更详细的目录项记录，如果主键值不小于 320 的话，就到页 32 中查找更详细的目录项记录。

随着表中记录的增加，这个目录的层级会继续增加，如果简化一下，可以用下边这个图来描述它：

这个数据结构，它的名称是B+Tree。

4. B+Tree

不论是存放普通的用户记录的数据页，还是存放目录项记录的数据页，现在都把它们存放到 B+Tree 这个数据结构中了，所以也称这些数据页为节点。从图中可以看出，实际的普通的用户记录其实都存放在 B+Tree 的最底层的节点上，这些节点也称为叶子节点，其余用来存放目录项记录的节点称为非叶子节点或者内节点，其中 B+Tree 最上边的那个节点也成为根节点。

一个 B+Tree 的节点其实可以分成好多层，规定最下边的那层，也就是存放普通的用户记录的那层为第 0 层，之后依次往上加。之前做了一个非常极端的假设：存放普通的用户记录的页最多存放 3 条记录，存放目录项记录的页最多存放 4 条记录。其实真实环境中一个页存放的记录数量是非常大的，假设所有存放用户记录的叶子节点代表的数据页可以存放 100 条普通的用户记录，所有存放目录项记录的内节点代表的数据页可以存放 1000 条目录项记录，那么：

  • 如果 B+Tree只有 1 层，也就是只有 1 个用于存放普通的用户记录的节点，则最多能存放 100 条普通的用户记录。
  • 如果 B+Tree有 2 层，则最多能存放 1000 × 100 = 10,0000 条普通的用户记录。
  • 如果 B+Tree有 3 层，则最多能存放 1000 × 1000 × 100 = 1,0000,0000 条普通的用户记录。
  • 如果 B+Tree有 4 层，则最多能存放 1000 × 1000 × 1000 × 100 = 1000,0000,0000 条普通的用户记录。这是相当多的记录！！！

你的表里能存放 1000,0000,0000 条记录吗？所以一般情况下，我们用到的 B+Tree 都不会超过 4 层，那通过主键值去查找某条记录最多只需要在 4 个页面内查找（查找 3 个目录项页和 1 个用户记录页），又因为在每个页面内有所谓的Page Directory（页目录），所以在页面内也可以通过二分法实现快速定位记录。

常见的索引概念

索引按照物理实现方式，索引可以分为 2 种：聚簇（聚集）索引和非聚簇（非聚集）索引。我们也把非聚集索引称为二级索引或者辅助索引。

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式（所有的普通的用户记录都存储在叶子节点），也就是所谓的索引即数据，数据即索引。

术语 "聚簇" 表示数据行和相邻的键值聚簇的存储在一起。

特点：

  • 使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：

    • 页内的记录是按照主键的大小顺序排成一个单向链表。
    • 各个存放普通的用户记录的页，也是根据页中用户记录的主键大小顺序排成一个双向链表。
    • 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。

  • B+Tree 的叶子节点存储的是完整的用户记录。

    • 所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。

我们把具有这两种特性的 B+Tree 称为聚簇索引，所有完整的普通的用户记录都存放在这个聚簇索引的叶子节点上。这种聚簇索引并不需要在 MySQL 语句中显式的使用 INDEX 语句去创建，InnoDB 存储引擎会自动的创建聚簇索引。

优点：

  • 数据访问更快，因为聚簇索引将索引和数据保存在同一个 B+Tree 中，因此从聚簇索引中获取数据比非聚簇索引更快。
  • 聚簇索引对于主键的排序查找和范围查找速度非常快。
  • 按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的 I/O 操作。

缺点：

  • 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于 InnoDB 表，一般都会定义一个自增的 ID 列为主键。
  • 更新主键的代价很高，因为将会导致被更新的行移动。因此，对于 InnoDB 表，一般定义主键为不可更新。
  • 二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。

限制：

  • 对于 MySQL 数据库目前只有 InnoDB 存储引擎支持聚簇索引，MyISAM 存储引擎不支持聚簇索引。
  • 由于数据物理存储排序方式只能有一种，所以每个 MySQL 的表只能有一个聚簇索引。一般情况下，就是该表的主键。
  • 如果没有定义主键，InnoDB 会选择非空的唯一索引代替。如果没有这样的索引，InnoDB 会隐式的定义一个主键来作为聚簇索引。
  • InnoDB 只聚集在同一个页面中的记录。包含相邻键值的页面可能相距甚远。如果已经设置了主键为聚簇索引，必须先删除主键，然后才能添加想要的聚簇索引，最后恢复设置主键即可。
  • 为了充分利用聚簇索引的聚簇特性，所以 InnoDB 表的主键尽量选用有序的顺序 ID，而不建议使用无序的 ID，比如 UUID，MD5，HASH，字符串列等这种无法保证数据的顺序增长的字段作为主键。
非聚簇索引（二级索引、辅助索引）

上边介绍的聚簇索引，只能在搜索条件是主键值时才能发挥作用，因为 B+Tree 中的数据都是按照主键进行排序的。那如果需要以其他的列作为搜索条件该怎么办呢？肯定不能是从头到尾沿着链表依次遍历用户记录一遍。

答案：可以多建几颗 B+Tree，不同的 B+Tree 中的数据采用不同的排序规则。比方说用 c2 列的大小作为数据页、页中记录的排序规则，再建一颗 B+Tree，效果如下图所示：

这个 B+Tree 与上边介绍的聚簇索引有几处不同：

  • 使用记录 c2 列的大小进行记录和页的排序，这包括三个方面的含义：
    • 页内的记录是按照 c2 列的大小顺序排成一个单向链表。
    • 各个存放用户记录的页，也是根据页中记录的 c2 列的大小顺序排成一个双向链表。
    • 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的 c2 列的大小顺序排成一个双向链表。
  • B+Tree 的叶子节点存储的并不是完整的用户记录，而只是 c2 列 + 主键这两个列的值。
  • 目录项记录中不再是主键 + 页号的搭配，而是 c2 列 + 页号的搭配。

所以，如果现在想通过 c2 列的值查找某些记录的话，就可以使用刚刚建好的这个 B+Tree 了。以查找 c2 列的值为 4 的用用户记录为例，查找过程如下：

  • 确定目录项记录。
    • 根据根页面，也就是页 44，可以快速定位到目录项记录所在的页为页 42（因为 2 < 4 < 9）。
  • 通过目录项记录页确定用户记录真实所在的页。
    • 在页 42 中，可以快速定位到实际存储用户记录的页，但是由于 c2 列并没有唯一性约束，所以 c2 列的值为 4 的记录可能分布在多个数据页中，又因为 2 < 4 ≤ 4，所以确定实际存储用户记录的页在页 34 和页 35 中。
  • 在真实存储用户记录的页中定位到具体的记录。
    • 到页 34 和页 35 中定位到具体的记录。
  • 因为这个 B+Tree 的叶子节点中的记录只存储了 c2 和 c1（即主键）这两个列，所以必须再根据主键值去聚簇索引中再查找一边完整的用户记录。

概念：回表。

根据这个以 c2 列大小排序的 B+Tree 只能确定要查找记录的主键值，所以如果想根据 c2 列的值查找到完整的用户记录的话，仍然需要到聚簇索引中再查一遍，这个过程称为回表。也就是根据 c2 列的值查询一条完整的用户记录需要使用到 2 棵 B+Tree！

问题：为什么还需要一次回表操作呢？直接把完整的用户记录放到叶子节点不行吗？

回答：如果把完整的用户记录放到叶子节点确实可以不用回表，但是这样会占据大量存储空间，相当于每建立一颗 B+Tree 都需要把所有的用户记录再拷贝一份，有点太浪费存储空间。

因为这种按照非主键列建立的 B+Tree 需要一次回表操作才可以定位到完整的用户记录，所以这种 B+Tree 也被称为二级索引（Secondary Index），或者辅助索引。由于使用的是 c2 列的大小作为 B+Tree 的排序规则，所以也称这个 B+Tree 是为 c2 列建立的索引。

非聚簇索引的存在不影响数据在聚簇索引中的组织，所以一张表可以有多个非聚簇索引。

聚簇索引 vs 非聚簇索引

聚簇索引与非聚簇索引的原理不同，在使用上也有一些区别：

  • 聚簇索引的叶子节点存储的就是用户的数据记录，非聚簇索引的叶子节点存储的是数据位置。非聚簇索引不会影响数据表的物理存储顺序。
  • 一个表只能有一个聚簇索引，因为只能有一种排序存储的方式，但可以有多个非聚簇索引，也就是多个索引目录提供数据检索。
  • 使用聚簇索引的时候，数据的查询效率高。聚簇索引列进行更新、插入或删除操作，那么整个簇将被更新。这意味着聚簇索引对于需要频繁修改其数据的表可能不太适合。
联合索引

我们也可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引，比如想让 B+Tree 按照 c2 和 c3 列的大小进行排序，这个包含两层含义：

  • 先把各个记录和页按照 c2 列进行排序。
  • 在记录的 c2 列相同的情况下，采用 c3 列进行排序。

为 c2 和 c3 列建立的索引的示意图如下：

如图所示，需要注意以下几点：

  • 每条目录项记录都是由 c2、c3 和页号这三个部分组成，各条记录先按照 c2 列的值进行排序，如果记录的 c2 列相同，则按照 c3 列的值进行排序。
  • B+Tree 叶子节点处的用户记录由 c2、c3 和主键 c1 列组成。

注意一点，以 c2 和 c3 列的大小为排序规则建立的 B+Tree 称为联合索引，本质上也是一个二级索引。它的意思与为 c2 和 c3 列分别建立索引的表述是不同的，不同点如下：

  • 建立联合索引只会建立如上图一样的 1 棵 B+Tree。
  • 为 c2 和 c3 列分别建立索引，会分别以 c2 和 c3 列的大小为排序规则建立 2 棵 B+Tree。

InnoDB 的 B+Tree 索引的注意事项

根页面位置万年不动

上面介绍 B+Tree 索引的时候，为了理解上的方便，先把存储用户记录的叶子节点画出来，然后接着画存储目录项记录的内节点，实际上 B+Tree 的形成过程是这样的：

  • 每当为每个表创建一个 B+Tree 索引（聚簇索引不是人为创建的，默认就有）的时候，都会为这个索引创建一个根节点页面。最开始表中没有数据的时候，每个 B+Tree 索引对应的根节点中既没有用户记录，也没有目录项记录。
  • 随后向表中插入用户记录时，先把用户记录存储到这个根节点中。
  • 当根节点中的可用空间用完并继续插入记录时，此时会将根节点中的所有记录复制到一个新分配的页，比如页 a 中，然后对这个新页进行页分裂操作，得到另一个新页，比如页 b。这时，新插入的记录根据键值（也就是聚簇索引中的主键值，二级索引中对应的索引列的值）的大小就会被分配到页 a 或者页 b 中，而根节点便升级为存储目录项记录的页。

这个过程特别注意的是：一个 B+Tree 索引的根节点自诞生之日起，便不会再移动。这样只要对某个表建立一个索引，那么它的根节点的页号便会被记录到某个地方，然后凡是 InnoDB 存储引擎需要用到这个索引的时候，都会从那个固定的地方取出根节点的页号，从而来访问这个索引。

内节点中目录项记录的唯一性

我们知道 B+Tree 索引的内节点中目录项记录的内容是索引列 + 页号的搭配，但是这个搭配对于二级索引来说有点不严谨。以 index_demo 表为例，假设这个表中的数据是这样的：

c1 c2 c3
1 1 'u'
3 1 'd'
5 1 'y'
7 1 'a'

如果二级索引中目录项记录的内容只是索引列 + 页号的搭配的话，那么为 c2 列建立索引后的 B+Tree 应该长这样：

如果想新插入一行记录，其中 c1、c2 和 c3 的值分别是：9、1、'c'，那么在修改这个为 c2 列建立的二级索引对应的 B+Tree 时便碰到了大问题：由于页 3 中存储的目录项记录是由 c2 列 + 页号的值构成的，页 3 中的两条目录项记录对应的 c2 列的值都是 1，而新插入的这条记录的 c2 列的值也是 1，那这条新插入的记录到底应该放到页 4 中，还是应该放到页 5 中呢？

为了让新插入的记录能找到自己在哪个页里，需要保证在 B+Tree 的同一层内节点的目录项记录除页号这个字段以外是唯一的。所以对于二级索引的内节点的目录项记录的内容，实际上是由三个部分构成的：

  • 索引列的值。
  • 主键值。
  • 页号。

也就是把主键值也添加到二级索引内节点中的目录项记录了，这样就能保证 B+Tree 每一层节点中各条目录项记录除页号这个字段外是唯一的，所以为 c2 列建立二级索引后的示意图实际上应该是这样子的：

这样再插入 (9, 1, 'c') 时，由于页 3 中存储的目录项记录是由 c2 列 + 主键 + 页号的值构成的，可以先把新纪录的 c2 列的值和页 3 中各目录项记录的 c2 列的值作比较，如果 c2 列的值相同的话，可以接着比较主键值，因为 B+Tree 同一层中不同目录项记录的 c2 列 + 主键的值肯定是不一样的，所以最后肯定能定位唯一的一条目录项记录。在本例中，最后确定新纪录应该被插入到页 5 中。

一个页面最少存储两条记录

一个 B+Tree 只需要很少的层级就可以轻松存储数亿条记录，查询效率也高。这是因为 B+Tree 本质上是一个大的多层级目录，没经过一个目录时都会过滤掉许多无效的子目录，直到最后访问到存储真实数据的目录。那如果一个大的目录中只存放一个子目录是什么效果呢？那就是目录层级变的非常多，而且最后的那个存放真实数据的目录中只能存放一条记录。因此，InnoDB 的一个数据页至少应存储两条记录。

MyISAM 中的索引方案

使用 B+Tree 索引的存储引擎如表所示：

索引/存储引擎 MyISAM InnoDB Memory
B-Tree 索引 支持 支持 支持

上表 MySQL 官方文档提到的 B-Tree，实际上就是指 B+Tree。

实际上，即使多个存储引擎都支持同一种类型的索引，但它们的实现原理也是不同的。InnoDB 和 MyISAM 默认的索引是 B+Tree 索引，而 Memory 默认的索引是 Hash 索引。

MyISAM 存储引擎使用 B+Tree 作为索引结构，叶子节点的 data 域存放的是数据记录的地址。

MyISAM 索引的原理

在 InnoDB 中索引即数据，也就是聚簇索引的那棵 B+Tree的叶子节点中已经把所有完整的用户记录都包含了，而 MyISAM 的索引方案虽然也使用树形结构，但是却将索引和数据分开存储。下图是 MyISAM 索引的原理图：

  • 将表中的记录按照记录的插入顺序单独存储到一个文件中，称为数据文件。这个文件并不划分为若干个数据页，有多少记录就往这个文件中塞多少记录。由于在插入数据时候并没有刻意按照主键大小排序，所以不能在这些数据上使用二分法进行查找。
  • 使用 MyISAM 存储引擎的表会把索引信息另外存储到一个称为索引文件的文件中，MyISAM 会单独为表的主键创建一个索引，只不过在索引的叶子节点中存储的不是完整的用户记录，而是主键值 + 数据记录地址的组合。

这里设表一共有三列，假设以 Col1 为主键，上图是一个 MyISAM 表的主索引（Primary Key）示意。可以看出 MyISAM 的索引文件仅仅保存数据记录的地址。在 MyISAM 中，主键索引和二级索引（Secondary Key）在结构上没有任何区别，只有主键索引要求 key 是唯一的，而二级索引的 key 可以重复。如果在 Col2 上建立一个二级索引，则此索引的结构如下图：

同样也是一棵 B+Tree，data 域保存数据记录的地址。因此，MyISAM 中索引检索的算法为：首先按照 B+Tree 搜索算法搜索索引，如果指定的 Key 存在，则取出其 data 域的值，然后以 data 域的值为地址，读取相应数据记录。

MyISAM VS InnoDB

MyISAM 的索引方式都是 "非聚簇" 的，与 InnoDB 包含 1 个聚簇索引是不同的。小结两种引擎中索引的区别：

  • 在 InnoDB 存储引擎中，我们只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录，而在 MyISAM 中却需要进行一次回表操作，意味着 MyISAM 中建立的索引相当于全部都是二级索引。
  • InnoDB 的数据文件本身就是索引文件，而 MyISAM 索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。
  • InnoDB 的非聚簇索引 data 域存储相应记录主键的值，而 MyISAM 索引记录的是地址。换句话说，InnoDB 的所有非聚簇索引都引用主键作为 data 域。
  • MyISAM 的回表操作是十分快速的，因为是拿着地址偏移量直接到文件中取数据的，反观 InnoDB 是通过获取主键之后再去聚簇索引里找记录，虽然说也不慢，但还是比不上直接用地址去访问。
  • InnoDB 要求表必须有主键（MyISAM 可以没有）。如果没有显式指定，则 MySQL 系统会自动选择一个可以非空且唯一标识数据记录的列作为主键。如果不存在这种列，则 MySQL 自动为 InnoDB 表生成一个隐含字段作为主键，这个字段长度为 6 个字节，类型为长整型。

了解不同存储引擎的索引实现方式，对于正确使用和优化索引都非常有帮助。比如：

  • 示例 1：知道了 InnoDB 的索引实现方式后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有二级索引都引用主键索引，过长的主键索引会令二级索引变的过大。
  • 示例 2：用非单调的字段作为主键在 InnoDB 中不是个好主意，因为 InnoDB 数据文件本身是一颗 B+Tree，非单调的主键会造成在插入新纪录时，数据文件为了维持 B+Tree 的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

索引的代价

索引是个好东西，可不能乱建，因为它在空间和时间上都会有消耗：

  • 空间上的代价：每建立一个索引都要为它建立一棵 B+Tree，每一棵 B+Tree 的每一个节点都是一个数据页，一个页默认会占用 16 KB 的存储空间，一棵很大的 B+Tree 由许多数据页组成，那就是很大的一片存储空间。
  • 时间上的代价：每次对表中的数据进行 增、删、改 操作时，都需要去修改各个 B+Tree 索引。而且 B+Tree 每层节点都是按照索引列的值从小到大的顺序排序组成双向链表。不论是叶子节点中的记录，还是内节点中的记录（也就是不论是用户记录还是目录项记录），都是按照索引列的值从小到大的顺序而形成了一个单向链表。而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位、页面分裂、页面回收等操作来维护好节点和记录的排序。如果建了许多索引，每个索引对应的 B+Tree 都要进行相关的维护操作，会给性能拖后腿。

一个表上索引建的越多，就会占用越多的存储空间，在增删改记录的时候性能就越差，为了能建立又好又少的索引，需要了解这些索引在什么条件下起作用。

MySQL 数据结构选择的合理性

从 MySQL 的角度讲，不得不考虑一个现实的问题就是磁盘 I/O，如果能让索引的数据结构尽量减少磁盘的 I/O 操作，所消耗的时间也就越小。可以说，磁盘的 I/O 操作次数对索引的使用效率至关重要。

查找都是索引操作，一般来说索引非常大，尤其是关系型数据库，当数据量较大的时候，索引的大小有可能几个 G 甚至更大，为了减少索引在内存的占用，数据库索引是存储在外部磁盘上的。当利用索引查询的时候，不可能把整个索引全部加载到内存，只能逐一加载，那么 MySQL 衡量查询效率的标准就是磁盘 I/O 次数。

全表遍历

即从表的第一条数据，逐一查看，直到找到目标记录。因为要加载所有页到内存，进行遍历查找，非常耗时，性能极差。

Hash 结构

Hash 本身是一个函数，又被称为散列函数，它可以帮助我们大幅提升检索数据的效率。

Hash 算法是通过某种确定性的算法（比如 MD5、SHA1、SHA2、SHA3）将输入变为输出，相同的输入永远可以得到相同的输出。假设输入内容有微小偏差，在输出中通常会有不同的结果。

举例：如果想要验证两个文件是否相同，那么不需要把两份文件直接拿来比对，只需要让对方把 Hash 函数计算得到的结果告知即可，然后在本地同样对文件进行 Hash 函数的运算，最后通过比较这两个 Hash 函数的结果是否相同，就可以知道这两个文件是否相同。

加快查找速度的数据结构，常见的有两类：

  • 树：例如平衡二叉搜索树，查询/插入/修改/删除的平均时间复杂度都是 O(log2N)。
  • 哈希：例如 HashMap，查询/插入/修改/删除的平均时间复杂度都是 O(1)。

采用 Hash 进行检索的效率非常高，基本上一次检索就可以找到数据，而 B+Tree 需要自顶向下依次查找，多次访问节点才能找到数据，中间需要多次 I/O 操作，从效率来说 Hash 比 B+Tree 更快。

在哈希的方式下，一个元素 k 处于 h(k) 中，即利用哈希函数 h，根据关键字 k 计算出槽的位置，函数 h 将关键字域映射到哈希表 T[0...m-1] 的槽位上。

上图中哈希函数 h 有可能将两个不同的关键字映射到相同的位置，这叫做碰撞，在数据库中一般采用链接法来解决。在链接法中，将散列到同一槽位的元素放在一个链表中，如下图所示：

体会数组和 Hash 表的查找方面的效率区别：

// 算法复杂度为 O(n)
public static void main(String[] args) {
    int[] arr = new int[100000];
    for (int i = 0; i < arr.length; i++) {
        arr[i] = i + 1;
    }
    long start = System.currentTimeMillis();
    for (int j = 1; j <= 100000; j++) {
        for (int k : arr) {
            if (j == k) {
                break;
            }
        }
    }
    long end = System.currentTimeMillis();
    System.out.println("time： " + (end - start)); // time： 970
}

public static void main(String[] args) {
    HashSet<Integer> set = new HashSet<>(100000);
    for (int i = 0; i < 100000; i++) {
        set.add(i + 1);
    }
    long start = System.currentTimeMillis();
    for (int j = 1; j <= 100000; j++) {
        boolean contains = set.contains(j);
    }
    long end = System.currentTimeMillis();
    System.out.println("time： " + (end - start)); // time： 5
}

既然 Hash 结构效率高，那为什么索引结构要设计成树型呢？

  • 原因 1：Hash 索引仅能满足（=）、（<>）和 IN 查询，如果进行范围查询，哈希型的索引，时间复杂度会退化为 O(n)，而树型的 "有序" 特性，依然能够保持 O(log2N) 的高效率。
  • 原因 2：Hash 索引还有一个缺陷，数据的存储是没有顺序的，在 ORDER BY 的情况下，使用 Hash 索引还需要对数据重新排序。
  • 原因 3：对于联合索引的情况，Hash 值是将联合索引键合并后一起来计算的，无法对单独的一个键值或者几个索引键进行查询。
  • 原因 4：对于等值查询来说，通常 Hash 索引的效率更高，不过也存在一种情况，就是索引列的重复值如果很多，效率就会降低。这是因为遇到 Hash 冲突时，需要遍历桶中的行指针来进行比较，进而找到查询的关键字，非常耗时。所以，Hash 索引通常不会用到重复值多的列上，比如性别、年龄等。

Hash 索引适用存储引擎如表所示：

索引/存储引擎 MyISAM InnoDB Memory
HASH 索引 不支持 不支持 支持

Hash 索引的适用性：

Hash 索引存在着很多限制，相比之下在数据库中 B+Tree 索引的使用面会更广，不过也有一些场景采用 Hash 索引效率更高，比如在键值型（Key-value）数据库中，Redis 存储的核心就是 Hash 表。

MySQL 中的 Memory 存储引擎支持 Hash 存储，如果需要用到查询的临时表时，就可以选择 Memory 存储引擎，把某个字段设置为 Hash 索引，比如字符串类型的字段，进行 Hash 计算之后长度可以缩短到几个字节。当字段的重复度低，而且经常需要进行等值查询的时候，采用 Hash 索引是个不错的选择。

另外，虽然 InnoDB 本身不支持 Hash 索引，但是提供自适应 Hash 索引（Adaptive Hash Index）。什么情况下才会使用自适应 Hash 索引呢？如果某个数据经常被访问，当满足一定条件的时候，MySQL 就会把这个数据页的地址存放到 Hash 表中，这样下次查询的时候，就可以直接找到这个页面的所在位置，这样 B+Tree 也具备了 Hash 索引的优点。

采用自适应 Hash 索引的目的是方便根据 SQL 的查询条件加速定位到叶子节点，特别是当 B+Tree 比较深的时候，通过自适应 Hash 索引可以明显提高数据的检索效率。

可以通过innodb_adaptive_hash_index变量来查看是否开启了自适应 Hash，比如：

mysql> SHOW VARIABLES LIKE '%adaptive_hash_index%';
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| innodb_adaptive_hash_index       | ON    |
| innodb_adaptive_hash_index_parts | 8     |
+----------------------------------+-------+
2 rows in set (0.00 sec)

二叉搜索树

如果利用二叉树作为索引结构，那么磁盘的IO次数和索引树的高度是相关的。

二叉搜索树的特点：

  • 一个节点只能有两个子节点，也就是一个节点度不能超过 2。
  • 左子节点 < 本节点 ≤ 右子节点。

查找规则：

先看下最基础的二叉搜索树（Binary Search Tree），搜索某个节点和插入节点的规则一样，假设搜索插入的数值为 key：

  • 如果 key 大于根节点，则在右子树中进行查找；
  • 如果 key 小于根节点，则在左子树中进行查找；
  • 如果 key 等于根节点，也就是找到了这个节点，直接返回根节点即可。

举个例子，我们对数列 (34, 22, 89, 5, 23, 77, 91) 创造出来的二分搜索树，如下图所示：

但是存在特殊的情况，会导致二叉树的深度非常大。比如数列 (5, 22, 23, 34, 77, 89, 91)，创造出来的二分搜索树，就如下图所示：

上面这棵树也属于二分搜索树，但是性能上已经退化成了一条链表，查找数据的时间复杂度变成了 O(n)。同时，可以看出，第一棵树的深度是 3，也就是说最多只需要 3 次比较，就可以找到节点，而第二棵树的深度是 7，最多需要 7 次比较才能找到节点。

为了提高查询效率，就需要减少磁盘 I/O 次数。为了减少磁盘 I/O 的次数，就需要尽量降低树的高度，需要把原来 "瘦高" 的树结构变的 "矮胖"，树的每层的分叉越多越好。

AVL 树

为了解决上面二叉搜索树退化成链表的问题，人们提出了平衡二叉树（Balanced Binary Tree），又称为AVL 树（有别于 AVL 算法），它在二叉搜索树的基础上增加了约束，具有以下性质：它是一棵空树，或者它的左右两个子树的高度差的绝对值不超过 1，并且左右两个子树都是一颗平衡二叉树。

Tips：

常见的平衡二叉树有很多种，包括了平衡二叉搜索树、红黑树、数堆、伸展树。平衡二叉搜索树是最早提出来的自平衡二叉搜索树，当提到平衡二叉树时，一般指的就是平衡二叉搜索树。事实上，上面的第一棵树就属于平衡二叉搜索树，搜索时间复杂度是 O(log2n)。

数据查询的时间主要取决于磁盘 I/O 的次数，如果采用二叉树的形式，即使通过平衡二叉搜索树进行了改进，树的深度也是 O(log2n)，当 n 比较大时，深度也是比较高的。比如下图的情况：

每访问一次节点就需要进程一次磁盘 I/0 操作，对于上面的树来说，需要进行 5 次 I/O 操作。虽然平衡二叉树的效率高，但是树的深度也同样高，这就意味着磁盘 I/O 操作次数多，会影响整体数据查询的效率。

针对同样的数据，如果把二叉树改成M 叉树 (M > 2)呢？当 M = 3 时，同样的 31 个节点可以由下面的三叉树来进行存储：

可以看到，此时树的高度降低了，当数据量 N 大的时候，以及树的分叉数 M 大的时候，M 叉树的高度会远小于二叉树的高度（M > 2）。这样，就把树从 "瘦高" 变为 "矮胖"。

B-Tree

B-Tree 的英文是 Balance Tree，也就是多路平衡查找树，简写为 B-Tree（横杠表示这两个单词相连，不是减号），它的高度远小于平衡二叉树的高度。

B-Tree 的结构如下图所示：

详细说明：

B-Tree 作为多路平衡查找树，它的每一个节点最多可以包括 M 个子节点，M 称为 B-Tree 的阶 （上图 B-Tree 的阶是 3，左边小，右边大，中间的是二者之间）。B-Tree 的每个磁盘块中包括了关键字和子节点的指针。如果一个磁盘块中包括了 x 个关键字，那么指针数就是 x + 1。对于一个 100 阶的 B-Tree 来说，如果有 3 层的话，最多可以存储约 100 万的索引数据。对于大量的索引数据来说，采用 B-Tree 的结构是非常适合的，因为树的高度要远小于二叉树的高度。

一个 M 阶的 B-Tree（M > 2）有以下的特性：

  • 根节点的儿子数的范围是 [2, M]。
  • 每个中间节点包含 k - 1 个关键字和 k 个孩子，孩子的数量 = 关键字的数量 + 1，k 的取值范围为 [ceil(M/2), M]。
  • 叶子节点包括 k - 1 个关键字（叶子节点没有孩子），k 的取值范围为 [ceil(M/2), M]。
  • 假设中间节点节点的关键字为：Key[1], Key[2], …, Key[k-1]，且关键字按照升序排序，即 Key[i] < Key[i+1]。此时 k - 1 个关键字相当于划分了 k 个范围，也就是对应着 k 个指针，即为：P[1], P[2], …, P[k]，其中 P[1] 指向关键字小于 Key[1] 的子树，P[i] 指向关键字属于 (Key[i-1], Key[i]) 的子树，P[k] 指向关键字大于 Key[k-1] 的子树。
  • 所有叶子节点位于同一层。

上面那张图所表示的 B-Tree 就是一棵 3 阶的 B 树。可以看下磁盘块 2，里面的关键字为 (8, 12)，它有 3 个孩子 (3, 5)，(9, 10) 和 (13, 15)，能看到 (3, 5) 小于 8，(9, 10) 在 8 和 12 之间，而 (13, 15) 大于 12，刚好符合刚才给出的特征。

然后来看下如何用 B-Tree 进行查找。假设想要查找的关键字是 9，那么步骤可以分为以下几步：

  • 与根节点的关键字 (17, 35) 进行比较，9 小于 17 那么得到指针 P1；
  • 按照指针 P1 找到磁盘块 2，关键字为 (8, 12)，因为 9 在 8 和 12 之间，所以得到指针 P2；
  • 按照指针 P2 找到磁盘块 6，关键字为 (9, 10)，然后找到了关键字 9。

可以看出来在 B-Tree 的搜索过程中，比较的次数并不少，但如果把数据读取出来然后在内存中进行比较，这个时间就是可以忽略不计的。而读取磁盘块本身需要进行 I/O 操作，消耗的时间比在内存中进行比较所需要的时间要多，是数据查找用时的重要因素。B-Tree 相比于平衡二叉树来说磁盘 I/O 操作要少，在数据查询中比平衡二叉树效率要高。所以只要树的高度足够低，I/O 次数足够少，就可以提高查询性能。

小结：

  • B-Tree 在插入和删除节点的时候，如果导致树不平衡，就通过自动调整节点的位置来保持树的自平衡。
  • 关键字集合分布在整棵树中，即叶子节点和非叶子节点都存放数据，搜索有可能在非叶子节点结束。
  • 其搜索性能等价于在关键字全集内做一次二分查找。

B+Tree

B+Tree 也是一种多路搜索树，基于 B-Tree 做了改进，主流的 DBMS 都支持 B+Tree 的索引方式，比如 MySQL。相比于 B-Tree，B+Tree 更适合文件索引系统。

B+Tree 和 B-Tree 的差异：

  • B+Tree 有 k 个孩子的节点就有 k 个关键字，也就是孩子数量 = 关键字数，而 B-Tree 中，孩子数量 = 关键字数 + 1。
  • B+Tree 非叶子节点的关键字也会同时存在在子节点中，并且是在子节点中所有关键字的最大（或最小）。
  • B+Tree 非叶子节点仅用于索引，不保存数据记录，跟记录有关的信息都放在叶子节点中。而 B-Tree 中，非叶子节点既保存索引，也保存数据记录。
  • B+Tree 所有关键字都在叶子节点出现，叶子节点构成一个有序链表，而且叶子节点本身按照关键字的大小从小到大顺序链接。

下图就是一棵 B+Tree，阶数为 3，根节点中的关键字 1、18、 35 分别是子节点 (1, 8, 14) ，(18, 24, 31) 和 (35, 41, 53) 中的最小值，每一层父节点的关键字都会出现在下一层的子节点的关键字中，因此在叶子节点中包括了所有的关键字信息，并且每一个叶子节点都有一个指向下一个节点的指针，这样就形成了一个链表。

比如，想要查找关键字 16，B+Tree 会自顶向下逐层进行查找：

  • 16 与根节点的关键字 (1, 18, 35) 进行比较，16 在 1 和 18 之间，得到指针 P1，指向磁盘块 2；
  • 找到磁盘块 2，16 与关键字 (1, 8, 14) 进行比较，16 大于 14，得到指针 P2，指向磁盘块 7；
  • 找到磁盘块 7，关键字为 (14, 16, 17)，找到关键字 16，继而找到对应的数据。

整个过程一共进行了 3 次 I/O 操作，看起来 B+Tree 和 B-Tree 的查询过程差不多，但是 B+Tree 和 B-Tree 有个根本的差异在于，B+Tree 的中间节点不直接存储数据，这样做的好处是：

  • 首先，B+Tree 查询效率更稳定。因为 B+Tree 每次只有访问到叶子节点才能找到对应的数据，而在 B-Tree 中，非叶子节点也会存储数据，会造成查询效率不稳定的情况，有时候访问到了非叶子节点就可以找到关键字，有时候需要访问到叶子节点才能找到关键字。
  • 其次，B+Tree 的查询效率更高。这是因为通常 B+Tree 比 B-Tree 更矮胖（阶数更大，深度更低，因为 B+Tree 非叶子节点不存储数据，占据的存储空间比 B-Tree 小，同样大小的数据页，B+Tree 能够存储更多的目录项记录，整棵树也就更矮胖），查询所需要的磁盘 I/O 次数也会更少。

不仅在对单个关键字的查询上，在查询范围上，B+Tree 的效率也比 B-Tree 高。这是因为所有关键字都出现在 B+Tree 的叶子节点中，叶子节点之间会有指针，数据又是递增的，这使得范围查找可以通过指针连接查找。而在 B-Tree 中，则需要通过中序遍历才能完成查询范围的查找，效率要低很多。

B-Tree 和 B+Tree 都可以作为索引的数据结构，在 MySQL 中采用的是 B+Tree。

另外，B-Tree 和 B+Tree 各有自己的应用场景，不能说 B+Tree 完全比 B-Tree 好，反之亦然。

R-Tree

R-Tree 在 MySQL 很少使用，仅支持 geometry 数据类型 ，支持该类型的存储引擎只有 MyISAM、BDB、InnoDB、NDB、Archive 几种。举个 R-Tree 在现实领域中能够解决的例子：查找 20 英里以内所有的餐厅。如果没有 R-Tree 你会怎么解决？一般情况下我们会把餐厅的坐标 (x, y) 分为两个字段存放在数据库中，一个字段记录经度，另一个字段记录纬度。这样的话我们就需要遍历所有的餐厅获取其位置信息，然后计算是否满足要求。如果一个地区有 100 家餐厅的话，我们就要进行 100 次位置计算操作了，如果应用到谷歌、百度地图这种超大数据库中，这种方法便必定不可行了。R-Tree 就很好的解决了这种高维空间搜索问题。它把 B-Tree 的思想很好的扩展到了多维空间，采用了 B-Tree 分割空间的思想，并在添加、删除操作时采用合并、分解结点的方法，保证树的平衡性。因此，R-Tree 就是一棵用来存储高维数据的平衡树。相对于 B-Tree，R-Tree 的优势在于范围查找。

索引/存储引擎 MyISAM InnoDB Memory
R-Tree 支持 支持 不支持

思考题

思考题：为了减少 I/O，索引树会一次性加载吗？

  • 数据库索引是存储在磁盘上的，如果数据量很大，必然导致索引的大小也很大，甚至超过几个 G。
  • 当利用索引查询的时候，是不可能将全部几个 G 的索引都加载进内存的，能做的只能是：逐一加载每一个磁盘页，因为磁盘页对应着索引树的节点。

思考题：B+Tree 的存储能力如何？为何说一般查找行记录，最多只需 1 ~ 3 次磁盘 I/O？

  • InnoDB 存储引擎中页的大小默认为 16KB。一般表的主键类型为 INT，占用 4 个字节，或者 BIGINT，占用 8 个字节，指针类型也一般为 4 个或者 8 个字节，也就是说一个页（B+Tree 中的一个节点）中大概存储 16KB / (8 B + 8 B) = 1024 个键值。也就是说一个深度为 3 的 B+Tree 索引，可以维护 1024 * 1024 * 1024，即超过 10 亿条记录。（此处为估算，并假定一个数据页页存储 1024 条行记录数据）
  • 实际情况中，每个节点可能不会填满，因此在数据库中，B+Tree 的高度一般都在 2 ~ 4 层。MySQL 的 InnoDB 存储引擎在设计时，是将根节点常驻内存的，也就是说查找某一键值的行记录，最多只需要 1 ~ 3 次磁盘 I/O 操作。

思考题：为什么说 B+Tree 比 B-Tree 更适合实际应用中操作系统的文件索引和数据库索引？

  • B+Tree 的磁盘读写代价更低。
    • B+Tree 的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对 B-Tree 更小。如果把所有同一内部节点的关键字存放在同一磁盘块中，那么磁盘块所能容纳的关键字数量也越多，一次性读入内存中的需要查找的关键字也就越多，相对来说 I/O 读写次数也就降低了。
  • B+Tree 的查询效率更加稳定。
    • B+Tree 的非叶子节点并不是最终指向文件内容的节点，而只是叶子节点中关键字的索引。所以任何关键字的查找，必须走一条从根节点到叶子节点的路，所有关键字查询的路径长度相同，因而每一个数据的查询效率相当。

思考题：Hash 索引与 B+Tree 索引的区别。

  • Hash 索引不能进行范围查询，而 B+Tree 可以。这是因为 Hash 索引指向的数据是无序的，而 B+Tree 的叶子节点是个有序的链表。
  • Hash 索引不支持联合索引的最左侧原则，即联合索引的部分索引无法使用，而 B+Tree 可以。对于联合索引来说，Hash 索引在计算 Hash 值得时候，是将索引键合并后再一起计算 Hash 值，所以不会针对每个索引单独计算 Hash 值。因此，如果用到联合索引得一个或几个索引时，联合索引无法被使用。
  • Hash 索引不支持 ORDER BY 排序，而 B+Tree 可以。因为 Hash 索引指向得数据是无序的，因此无法起到排序优化的作用，而 B+Tree 的数据是有序的，可以起到对该字段 ORDER BY 排序优化的作用。
  • Hash 索引不支持模糊查询，而 B+Tree 可以。理由同上，当 B+Tree 使用 LIKE 进行模糊查询的时候，LIKE 后面后模糊查询（比如 % 结尾）可以起到优化左右。
  • InnoDB 存储引擎不支持哈希索引。

思考题：Hash 索引与 B+Tree 索引是在建索引的时候手动指定的吗？

在 MySQL 8.0 文档，有明确说明：

Table 15.1 Index Types Per Storage Engine

Storage Engine Permissible Index Types
InnoDB BTREE
MyISAM BTREE
MEMORY/HEAP HASH, BTREE
NDB HASH, BTREE (see note in text)
  • 如果使用的是 MySQL，针对 InnoDB 和 MyISAM 存储引擎，默认会采用 B+Tree，无法使用 Hash 索引。InnoDB 提供的自适应 Hash 不需要手动指定，默认开启。如果是 Memory/Heap 和 NDB 存储引擎，可以选择使用 Hash 索引。

小结

使用索引可以帮助我们从海量的数据中快速定位想要查找的数据，不过索引也存在一些不足，比如占用存储空间、降低数据库写操作的性能等，如果有多个索引，还会增加索引选择的时间。当我们使用索引时，需要平衡索引的利（提升查询效率）和弊（维护索引所需的代价）。

在实际工作中，我们还需要基于需求和数据本身的分布情况，来确定是否需要使用索引，尽管索引不是万能的，但数据量大的时候不使用索引是不可想象的，毕竟索引的本质，是帮助我们提升数据检索的效率。

附录：算法的时间复杂度

同一问题可用不同算法解决，而一个算法的质量优劣将影响到算法乃至程序的效率。算法分析的目的在于选择合适算法和改进算法。

原文链接

https://github.com/ACatSmiling/zero-to-zero/blob/main/RelationalDatabase/mysql.md

posted @ 2024-07-22 23:35  ACatSmiling  阅读(0)  评论(0)  编辑  收藏  举报