InnoDB索引数据结构

原文地址：http://www.linzichen.cn/article/1564657142856679424

索引概述及优缺点

索引是存储引擎用于快速找到数据记录，对表中一列或多列值进行排序的结构。不同存储引擎所采用索引的数据结构也是不同的。例如InnoDB和MyISAM默认的是索引是BTree，Memory默认索引是Hash等。

概述

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

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

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

优点

1、提高数据检索效率，降低数据库IO成本。

2、通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。

3、在实现数据的参考完整性方面，可以加速表之间的链接。对于有依赖关系的子表和父表联合查询时，可以提高查询速度。

4、在使用分组或排序进行查询时，可以减少查询中分组和排序的时间，降低CPU消耗等。

缺点

1、创建和维护索引要 耗费时间，并且数据量越多，耗时也越多。

2、索引需要占用磁盘物理空间，存储在磁盘上，如果有大量索引，那么索引文件可能比数据文件更快达到最大文件尺寸。

3、降低更新表的速度。当对表中数据进行写操作时，索引也要动态地维护，降低了数据的维护速度。

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

为什么索引可以提高查询速度，接下来将做一个浅简的剖析。

InnoDB中索引的推演

先来一个精准匹配的例子：

select [列名列表] from 表名 where 列名 = xxx ; 

没有索引的查找

在mysql中，磁盘与内存之间交互的基本单位是 页 ,无论是读取一行还是多行数据，都是将这些数据所在的页进行加载。在InnoDB中页的默认大小是 16KB。每个页都会有一个页目录，用来记录和排序数据。

在有主键时，每个页会以主键生成页目录；
在无主键时，每个页会以非空唯一索引生成页目录；
在无主键无唯一索引时，MySQL会自动为InnoDB表生成一个隐藏字段作为主键。

在一个页中查找

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

• 以主键为搜索条件

  在建表时，mysql会把主键作为聚簇索引来记录和排序。所以在使用主键进行搜索时，可以在页目录中使用 二分法 快速定位到对应的数据，时间复杂度为 O(logn)。

• 以其他列为搜索条件

  在数据页中没有对非主键索引建立页目录，所以无法通过二分法快速定位。只能从 最小记录 开始遍历 单链表 中的每条记录，时间复杂度为 O(n)。

在多个页中查找

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

1、定位到记录所在的页；

2、从所在页中查找对应的记录。

在没有索引的情况下，并不能快速定位到记录所在的页，所以只能从 第一页 沿着 双向链表 一直往下找，然后在每一个页中再根据单页中的查找方法去查找。因为要遍历所有的数据页及页中的数据，所以在没有索引的情况下显然是非常耗时的。

设计索引

mysql> create table index_demo(
    -> c1 int,
    -> c2 int,
    -> c3 char(1),
    -> primary key (c1)
    -> ) row_format = Compact;
Query OK, 0 rows affected (0.07 sec)

这个表使用 Compact 行格式来实际存储数据，简化一下 index_demo 表的行格式示意图：

record_type ：记录头信息的一项属性，表示记录类型，0表示普通记录、 2表示最小记录、 3表示最大记录、 1 暂时用不到，下面再说。

next_record ：记录头信息的一项属性，表示下一条地址相对于本条记录的偏移量，这里用箭头来表明下一条记录是谁。

各个列值 ：这里只记录 index_demo 表中的三个列，c1、c2、c3。

其他信息 ：除了上述3中信息以外的所有信息，包括其他隐藏列的值以及记录的额外信息。

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

mysql> insert into index_demo values
    -> (1, 4, 'u'),
    -> (3, 9, 'd'),
    -> (5, 3, 'y');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

页10： 表示数据页的页号。
左侧的2： record_type 就是2，表示最小记录侧，指向最小记录。
中间的0： record_type 就是0，表示普通记录，这里按照主键进行排序，依次指向下一条记录。
右侧的3： record_type 就是3，表示最大记录侧。

一个简单的索引设计方案

在根据某个搜索条件查找记录时，因为并不知道目标记录在哪个数据页中，所以会依次遍历所有的数据页来获取我们想要的数据。如果想快速定位到目标数据在哪个数据页中，我们可以针对数据页来创建目录项。

这里假设一个数据页中，最大存储3条数据，所以再往 index_demo 中增加数据时，因为页10中已经存在3条数据了，所以此时会再分配一个新的数据页：

mysql> insert into index_demo values  (4, 4, 'a');
Query OK, 1 row affected (0.01 sec)

新分配的数据页编号并不是连续的，上下页之间通过建立 双向链表 来维护关系。但此时页10中最大主键值是5，页28中最大主键是4，在数据页中，要求下一页的主键值必须大于上一页的主键值，所以在插入 主键为4的这条记录时，需要伴随着一次 记录移动，把主键为5的移动到页28中，把主键为4的移动到页10中，这个过程示意图如下：

这个过程表明在对记录进行增删改的过程中，必须通过一些诸如 记录移动 的操作来始终保持 下页主键值大于上页主键值的状态。我们把这个操作称作 页分裂。

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

虽然页与页之间有了关系，但是我们仍然不知道 所查找的记录在哪个数据页中，所以再给数据页创建目录项的时候，每个目录项需要包含以下两个部分：

• 每个页中最小的主键值，我们用key来表示。
• 页号，我们用page_no表示。

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

以 页28为例，它对象 目录项2，该目录项中包含该页的 页号28以及改页中最小的主键值5,我们只需要把几个目录项在物理存储器上连续存储（比如：数组），就可以实现根据主键值快速找到某条记录的功能了。

比如：查找主键值为20 的记录，具体查找过程分为两步：

1、先从目录项中，根据 二分法 快速确定出主键值为 20 的记录在 目录项3 中（12 < 20 < 209），它对应的页号是 页9.

2、找到数据页了，再根据前面所说的在页中查找记录的方式去 页9中定位具体的记录。

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

InnoDB中索引的方案

• 迭代一次：目录项页

上面案例为了在根据主键进行查找时，采用二分法快速定位具体的目录项，随着数据越来越多，如果所有的目录项都在物理磁盘上连续存储，这样会存在几个问题：

1、InnoDB 使用页作为管理存储空间的基本单位，最多保证16KB的连续存储空间，随着表中数据量的增加，需要非常大的连续存储空间才能把所有的目录项都放下，这对记录庞大的表是不现实的。

2、我们经常会对 记录进行增删操作，假设把某一数据页中的数据全部删除，那此数据页的目录项也就不会存在了，意味着此目录项后面的所有目录项，都需向前移动，牵一发而动全身的操作效率很差。

所以我们需要一种可以 灵活管理所有目录项 的方式。因为目录项的结构跟数据页的结构很像，只不过目录项中的两个列是 最小主键 和 页号 而已，为了用数据页做一下区分，我们把这些目录项的数据称为 目录项记录。

在InnoDB中，区分数据目录 和 目录项记录的区别是根据头信息里的 record_type ，目录项记录的 record_type 是 1。

至此，记录头信息里的 record_type 属性，各个取值代表的意思如下：

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

把前面使用到的目录项放到页中的示意图：

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

目录项记录的 record_type 是1，用户记录的 record_type 是0。
目录项记录只有主键值和编号 两个列，用户记录的列是表中的字段，用户自己定义的，此外还有InnoDB自己添加的隐藏列。
目录项记录页 和 用户记录页 都会为主键值生成页目录，在按照主键值查找时都可以使用二分法来快速定位到数据。

此时再查找主键为 20的记录，它的过程是：

1、在目录项记录页中，通过二分法，12 < 20 < 209，所以目标记录在 页编号为9 的记录页中。

2、在页9中，再次通过二分法，定位到主键值为 20 的用户记录。

• 迭代两次：多个目录项页

一个目录项记录页最多也只能存储16KB的数据，随着数据量增加，一个目录项页无法存储所有的用户记录页。所以就需要分配多个目录项记录页，每一个目录项页管理多个用户记录页。

此时再根据主键查找值为20的数据时，它的过程为：

1、确定目录项记录页。

根据上图，存储目录项的页有两个，页30 和 页32，页30的主键值范围是[1, 320]，页32的主键值范围是不小于320，所以主键值为20的记录在 页30的目录项页中。

2、通过目录项记录页确定目标记录所在的用户记录页。

3、在用户记录页中，定位到具体记录。

• 迭代三次次：多个目录项页的记录页

用户记录页多了以后，会分配不同的目录项记录页来管理。

同理，当目录项记录页多了以后，也会分配再更高层级的 目录项记录页的页。就像是一个多级目录一样，大目录嵌套小目录，最底层的小目录里才是实际的用户数据，所以现在各个页的示意图：

如图，生成了一个更高级目录 页33，管理着两个 目录项记录页 页30 和 页32 。

随着表中记录的增加，这个目录的层级会继续增加，如果简化一下，那个可以用下面这个图来描述它，这个结构，就是InnoDB的数据结构：B+树。

• 最终结构：B+树

无论是存放 用户记录的数据页，还是存放 目录项记录 的数据页，它们都是存放在B+树这个数据结构中了。这些页，称之为 节点。实际的用户记录都存放在B+树最底层的节点上，被称作 叶子节点，其余用来存放 目录项的节点，成为 非叶子节点 或者 内节点。B+树最上面的那个节点也称作 根节点。

一般情况下，开发中用到的B+数层级不会超过4层,实际上一个叶子结点可以存储非常多的用户记录。假如 一个用户记录页存放 100条用户记录，一个 目录项记录页可以存放 1000条目录项（目录项记录只存放主键id 和 页码，所需空间极小）,那么：

• 一层存放 100条记录。
• 两层存放 100 * 1000 条记录。
• 三层存放 100 * 1000 * 1000 条记录。
• 四层存放 100 * 1000 * 1000 * 1000 条记录。

4层已经是极为庞大的数据量了，那么在通过主键值查询的时候，最多只需要做 4 个页面内的查找（3个目录项页和一个用户记录页），又因每个页内都有页目录，所以页面内也可以通过二分法实现快速定位记录。

常见索引概念

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

聚簇索引

特点

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

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

2、B+树的 叶子节点存储的是完整的用户记录（存储了所有列的值，包含隐藏列）。

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

优点

1、数据访问更快，聚簇索引将索引和数据保存在一个B+树中，因此从聚簇索引中获取出去比非聚簇索引更快。

2、聚簇索引对于主键的 排序查找 和 范围查找速度非常快。

3、按照聚簇索引的排列顺序，查询一定范围数据的时候，由于数据都是紧密连接，MySQL不需要从多个数据块中获取数据，节省了大量的IO操作。

缺点

1、插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此对应InnoDB表，一般都会定义一个自增的ID列为主键。

2、更新主键的代价很高，这将会导致被更新的行移动。因此对于InnoDB表，一般定义为主键不可更新。

限制

1、对于MySQL目前只有InnoDB数据引擎支持聚簇索引，而MyISAM等并不支持。

2、由于数据物理存储排序方式只能有一种，所以每个表只能有一个聚簇索引。一般情况下是该表的主键。

3、为了充分利用聚簇索引的聚簇特性，所以InnoDB表的主键尽量用有序的id，不建议用无序的，比如UUID、MD5、字符串等作为主键无法保证数据的顺序增长。

说白了，聚簇索引就是针对主键搭建起来的B+树结构。

非聚簇索引（二级、辅助索引）

聚簇索引的B+树是针对主键值搜索的，那如果我们希望用其他的字段值搜索时，仍有索引的效果，我们可以为其他的字段创建索引。这种非主键值索引，称作 非聚簇索引。

我们可以根据需要，创建多个辅助索引。每个辅助索引都是一棵独立的B+树，不同B+树中的数据采用不同的排序规则。

红色项的页码。
绿色是页头的record_type。
蓝色是c2字段，也就是辅助索引的值。
黄色是主键索引的值。

辅助索引不同与聚簇索引的地方：

1、页的记录是根据c2字段排序的单项链表。
2、用户记录页通过c2字段 顺序排序的双向列表。
3、目录项记录页分为不同层次，在同一层次中根据页中目录项记录的c2值排序排成的双向链表。
** 4、辅助索引的叶子节点中，存放的不是用户记录，而是c2列的值 + 主键值。
5、目录项记录不再是 主键 + 页号，而是 c2列值 + 页号。

此时如果想通过c2字段查找记录的话，就可以使用上面的B+树了，以查找c2 = 4 的记录为例：

1、确定 目录项记录页。
根据根页面，也就是页44，可以定位到 目录项记录所在的页为 页42（2 < 4 < 9）。

2、根据 目录项记录页 确定用户记录真实所在的页。
在页42中快速定位到存储用户记录的页，但是由于c2 列没有唯一性约束，所以c2列的值为 4 的记录可能分布在多个数据页中，又因为 2 < 4 <= 4，所以确定实际用户记录在 页34 和 页35 中。

3、到 页35 和 页35 中，定位到了具体记录，因为叶子节点中存储了 c2值和主键的值，所以我们想要获取记录，需要根据主键值，再去聚簇索引中走一遍完整的查找过程。

概念：回表

因为辅助索引的叶子节点不存储所有用户记录，而是只有辅助索引列的值 + 主键值，所以需要根据主键值，再回到 聚簇索引中去查找真实的用户记录。这个过程称为 回表。

联合索引

以多个列值的大小为排序规则建立的B+树成为 联合索引，本质上也是一个非聚簇索引，比如我们给 c2 和 c3 的大小进行排序，这里包含两层含义：

1、先把各个记录和页按照c2列进行排序。

2、在c2值相同的情况下，再采用c3 列进行排序。

• 每条 目录项记录 是由c2、c3 和 页号组成的，先按照c2排序，如果相等，再按照c3排序。

• 叶子节点 存储的是 c2、c3 和 主键的值。

小结

1、聚簇索引的叶子节点存储的就是数据记录，非聚簇索引的叶子节点存储的是数据位置。非聚簇索引不会影响数据表的物理存储顺序。

2、一个表只能有一个聚簇索引，因为只能有一种排序存储的方式，但可以有多个非聚簇索引。

3、使用聚簇索引的时候，数据的查询效率更高，但如果对数据进行写操作，效率会比非聚簇索引低。

InnoDB的B+树索引注意事项

根页面永远不动

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

1、每当为某个表创建一个B+树索引（聚簇索引默认生成）的时候，都会为这个索引创建一个根节点页面。最开始表中没有数据的时候，每个B+树索引对应的根节点中既没有没用记录，也没有目录项记录。

2、随后向表中插入记录时，先把用户记录存储到这个 根节点中。

3、当根节点中的可用 空间用完时继续插入记录，此时会将根节点中的所有记录复制到一个新分配的页，比如页a中，然后对这个新页进行页分裂操作，得到另一个新的页，比如 页b。这时新插入的记录根据键值（聚簇索引中的主键值，辅助索引中索引列的值）的大小就会被分配到 页a或者页b中，而 根节点便升级为存储目录项记录的页。

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

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

B+树索引的内节点中目录项记录的内容是 索引列 + 页号的搭配，但是这个搭配对于辅助索引来说有点不严谨。比如在index_demo表的数据为：

c1	c2	c3
1	1	‘u’
3	1	‘d’
5	1	‘y’
7	1	‘a’

我们为c2字段创建辅助索引，如果目录项记录的内容只是 索引列 + 页号 搭配的话，那么此时的B+树应该为：

如果此时插入一条新的记录 9、1、'c' ，那么在修改这个辅助索引时便遇到了问题：

由于页3中存储的目录项记录是由c2 + 页号构成，所以两条目录项记录对应的c2列的值都是 1，而新插入的这条记录的c2列的值也是1，那么这条新插入的记录不知道放在 页4中还是页5中。

所以我们需要保证 在B+树的同一层内节点的目录项记录除页号这个字段以外是唯一的。所以对于辅助索引的内节点的目录项记录的内容实际上是由三个部分构成的：

1、索引列的值。

2、主键值。

3、页号。

也就是在之前的基础上，把主键值也添加到辅助索引内节点的目录项记录中了，这样可以保证每一层节点中所有目录项记录，除了页号字段以外，其余都是唯一的。所以此时的示意图应该是：

这样在插入 9、1、'c' 的时候，先把新纪录的c2值和页3中的各个目录项记录的 c2值做比较，如果相同，可以接着比较主键值。因为 7 < 9 ，所以能够定位到该数据最终被存放在 页5中。

一个页最少存储2条记录

InnoDB中一个数据页至少可以存放两条记录。

总结

本文从一个简单的查询，通过数据的存储方式（是以页为基本单位存储在磁盘上），引出 页的概念。随着数据不断的增加，页在不断进行页分列，为了更好的管理这些页，引出了 目录项的概念，最终升级为 B+树。

在页中，有个重要的头信息 record_type 用来区分记录的类型，比如 0 是代表用户记录、1代表目录项记录等。

又介绍了常见的索引概念：聚簇索引、非聚簇索引、联合索引等，介绍了几种索引的区别。
聚簇索引的叶子节点是存放的用户数据，它的目录项是 主键值+页码。
非聚簇索引的叶子节点存放的是 索引列值 + 页码，它的目录项是索引列之 + 主键值 + 页码。在根据此索引查找数据时，需要注意有 回表操作。

最后介绍了一下 InnoDB中 B+树的注意事项，以及InnoDB为什么推荐使用自增列做主键、为什么说InnoDB索引即数据等问题也就到此结束了。至于其他的存储引擎的索引，后面有时间可以再单出出一个文章，比如 MyISAM中的数据结构是怎样的、索引搜索为什么需要回表等。