MySQL笔记3：InnoDB的索引模型

蛋不炒饭
118 阅读6分钟

上一篇笔记中介绍了MySQL为何选用B+树作为索引的数据结构，这里记录一下InnoDB的B+树索引的实现。

1. InnoDB索引结构

InnoDB从MySQL 5.5开始成为默认的存在引擎。而InnoDB采用的就是B+树结构的索引。在InnoDB中会为每个索引都建立一个B+树。

InnoDB中数据都是存放在聚簇索引中的， 所以InnoDB中表的数据文件就是索引文件，聚簇索引选取如下：

  1. 若建表的时候指定了主键，那么聚簇索引就为主键索引。
  2. 若没有主键索引，则InnoDB使用第一个 UNIQUE索引。
  3. 若也没有UNIQUE索引，则InnoDB会自动创建一个名为GEN_CLUST_INDEX的聚簇索引，索引的列为隐藏的自增的ID列。

InnoDB的B+树索引具有以下特性：

  1. 每个节点的大小为一页，页大小默认为16K，每次以页为单位进行读取。这样每个节点可以保持多个值，大大减少了树的高度，提升查询效率。同时以页为单位进行读取，提升了IO效率。
  2. 聚簇索引的页子节点存放了整行数据，非叶子节点只存放主键的值。
  3. 非聚簇索引的叶子节点存放的是索引的值和主键的值，非叶子节点只存放的是索引的值（联合索引存放其所有索引字段的值）。
  4. 叶子节点之间会构建一个链表关联起来。叶子节点包含了所有的数据且是有序的，通过链表连接起来后可以快速查询范围内的节点。

例如有一个user表，表中有主键id字段和普通索引的user_name字段，那么两个索引的B+树结构如下所示： 5_AgAABe_bA0U76cP6j9xD2pHR-5KKRs8c.png

2. 索引检索过程

为了节约存储空间，普通索引的叶子节点中存储的是对应行的主键值，这也就导致了通过普通索引查询数据时，只能查询到主键的值，要查完成的数据还需要根据主键值到聚簇索引进行查询，例如查询‘SELECT * FROM USER WHERE user_name='Bob'，查询过程如下：

  1. 从user_name索引中查询到user_name为Bob的叶子节点，取主键值15。
  2. 从主键索引中查询到主键值15的节点，取该行数据。
  3. 再回到user_name索引中查询Bob节点的下一个节点，比较值是否为Bob，如是则继续从主键索引再查数据，重复这个过程知道user_name索引下一个节点的值不为Bob。

从普通索引中查询到主键值后在到主键索引中查询的过程被称为回表。

覆盖索引：

如执行的查询操作如下：

SELECT user_name FROM USER WHERE user_name='Bob

这时要查询的字段已经存放在索引的节点中了，没有必要再回表，可以直接取索引中存储的值。这时索引‘覆盖’了我们的需求，所以这种情况称为‘覆盖索引’。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

索引下推：

若使用的联合索引，由于联合索引的节点中会存储所有其字段的值。所以在MySQL5.5之后，若要查询的字段都在联合索引中了，就会直接使用联合索引中的存在的字段的值，也不会再回表，这被称为索引下推。

3. 插入数据

B+树中的节点值是有序的，这意味着插入新值的时候可能需要挪动节点。例如下面索引树中，若是插入id为201的记录则只需在200的记录之后直接插入即可，若插入id为550的记录则需要挪动500之后的记录为它腾出空间。

6_AgAABe_bA0VXNNjhC19NXp_v_Kcoh2VZ.png

同时InnoDB的索引中每个节点的大小为一个页，若节点的数据已经满了，那么新插入数据时就需要申请一个新的页将部分数据挪到过去，这个过程被称为也分裂。在这种情况下除了性能会受到影响外，还影响数据页的使用效率，原来一个页存储的数据分到两个页中存储，存储空间使用率降低了一半。

当然有分裂就有合并。当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。

4. 创建二级索引

InnoDB中创建表时默认会为主键创建一个聚簇索引保存全量的数据，聚簇索引外的索引被称为二级索引。二级索引使用如下语句创建：

ALTER TABLE [table name] ADD INDEX [index name]([column name]);

在MySQL 5.6.7之前创建二级索引期间需要先获取到表的排他锁，这导致创建索引期间对表的DML语句都将被阻塞，直到索引创建完成。直到MySQL 5.6.7开始创建索引的操作也支持了Online DDL，二级索引的创建大概分为三个阶段：DDL prepare 阶段, DDL执行阶段和 DDL commit 阶段。

DDL prepare 阶段:

  1. 获取表的MDL互斥锁，并清理该表在TABLE_SHARE（表定义的缓存）对应的TABLE。同时分配RowLog空间，用于记录后续DDL执行期间的DML语句。
  2. 在系统表空间（ibdata1）中，插入关于新索引的元信息。
  3. 在表的ibd文件中创建索引并创建索引的B+树的根节点。
  4. 将表的MDL互斥锁降级为MDL共享锁。

DDL执行阶段：

该阶段主要从簇索中读取全量的记录，插入到新的索引中。过程如下：

  1. 分配一个sort buffer内存，用于保存从簇索中读取的数据和进行局部排序。
  2. 从簇索索引的B+树叶子层最左边一个page开始，按行逐个读取page。为了减少之后外部排序的次数，在读取记录时会做一些规模很小的排序。读取到的数据转化为逻辑记录存放到sort buffer。
  3. 当sort buffer内存放的记录满了之后，就会对sort buffer内的记录进行一次排序。sort buffer满了之后，若一个sort buffer就存下了索引的数据，则直接将sort buffer的数据插入到新的索引树中。否则需要创建一个临时文件，先将sort buffer中的数据转化为COMPACT格式并写入到临时文件中，然后并清空sort buffer。
  4. 在扫描完簇索索引之后，就得到了一个保存着所有记录的临时文件。然后进行归并排序，使临时文件中的记录达到全局有序。
  5. 最后将临时文件中的记录插入到新的索引树中。

DDL commit 阶段：

  1. 将所升级为的MDL互斥锁。
  2. 应用RowLog到新ed索引文件。
  3. 清理并刷新该表的TABLE_SHARE。
  4. 提交事务并释放锁资源。

参考资料：

avatar
文章
阅读
粉丝