MySQL 索引详解（B+数、二级索引）

MySQL 索引详解

1.概述

1.1 简介

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

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

1.2 优缺点

1.2.1 优点

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

（1）：因为各个不同存储介质的读取速度，磁盘的 IO 对于我们来说，可能 0.01s 很快，但是对于 CPU 来说可能处理同样数据的时间为 0.001s 两者的时间差距是几百倍的，因此对于数据库操作来说，要尽量减少磁盘 IO 的次数，这也是创建索引这样数据结构的一个重要原因。

1.2.2 缺点

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

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

2.B+ 树

2.1 B 树（B-树）

B树（B-tree）是一种树状数据结构，它能够存储数据、对其进行排序并允许以 O(logn) 的时间复杂度运行进行查找、顺序读取、插入和删除的数据结构。B树，概括来说是一个节点可以拥有多于 2 个子节点的二叉查找树。与自平衡二叉查找树不同，B-树为系统最优化大块数据的读和写操作。

2.1.1 定义

• 根节点至少有两个子节点
• 每个节点有 M-1 个key，并且以升序排列
• 位于 M-1 和 M key 的子节点的值位于 M-1 和 M key 对应的Value之间
• 其它节点至少有 M/2 个子节点

和普通的二叉搜索树最大的不同在于，二叉搜索树的子节点只能有两个，而 B 数不是。

2.2 概述

是 B-Tree 的改进版本，同时也是数据库索引索引所采用的存储结构。数据都在叶子节点上，并且增加了顺序访问指针，每个叶子节点都指向相邻的叶子节点的地址。相比 B-Tree 来说，进行范围查找时只需要查找两个节点，进行遍历即可。而 B-Tree 需要获取所有节点，相比之下 B+Tree 效率更高。

通常来说除叶子节点以外，节点记录的是每一个节点的位置，第一层记录100个节点，第二层记录1000个节点：

• 如果B+树只有1层，也就是只有1个用于存放用户记录的节点，最多能存放 100 条记录

• 如果B+树有2层，最多能存放 1000×100=10,0000 条记录。

• 如果B+树有3层，最多能存放 1000×1000×100=1,0000,0000 条记录。

• 如果B+树有4层，最多能存放 1000×1000×1000×100=1000,0000,0000 条记录。相当多的记

录！！！

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

注：只有叶子节点才会存储真实的数据，非叶子节点不会存储数据，只会存储节点的指向。

3.索引

3.1 常见索引概念

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

3.2 聚簇索引

3.2.1 概述

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

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

2. B+树的叶子节点存储的是完整的用户记录

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

3.2.2 优点

• 数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快

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

• 按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的 IO 操作

3.2.3 缺点

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

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

• 二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据

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

3.3.1 补充概念：回表

对于二级索引，在子节点上并只记录了主键的值和部分数据，因此如果要查询这条记录的其他数据，就需要带着这个主键到聚簇索引中进行查询，这个过程称为：回表。

3.3.2 概述

二级索引、联合索引，就是在建立索引 B+ 树时，数据叶子节点上并没有记录所有的记录数据，而只是记录了主键和记录中的部分数据，依靠主键和部分数据建立的索引 B+ 树，称为二级索引，联合索引是二级索引的一种，只是在除了主键以外在节点上保存的数据多少问题。

3.4 InnoDB 的 B+ 树索引的注意事项

1. 根页面位置万年不动

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

   作为查询主键的索引必须具有唯一性

3. 一个页面最少存储2条记录

3.5 MyISAM 中的索引方案

MyISAM 引擎使用 B+Tree 作为索引结构，叶子节点的 data 域存放的是 数据记录的地址。也就是说在 MyISAM 中的索引不会保存记录中具体的数据而是保存的指向数据的地址。

3.6 MyISAM 与 InnoDB 索引对比

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

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

3.7 索引的代价

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

• 空间上的代价

  每建立一个索引都要为它建立一棵 B+ 树，每一棵 B+ 树的每一个节点都是一个数据页，一个页默认会占用 16KB 的存储空间，一棵很大的 B+ 树由许多数据页组成，那就是很大的一片存储空间。

• 时间上的代价

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

4.InnoDB 数据存储结构

4.1 磁盘与内存交互基本单位：页

InnoDB 将数据划分为若干个页，InnoDB 中页的大小默认为 16KB 。 以页作为磁盘和内存之间交互的基本单位，也就是一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中。也就是说，在数据库中，不论读一行，还是读多行，都是将这些行所在的页进行加载。也就是说，数据库管理存储空间的基本单位是页（Page），数据库 I/O 操作的最小单位是页。一个页中可以存储多个行记录。

页a、页b、页c … 页n这些页可以不在物理结构上相连，只要通过双向链表相关联即可。每个数据页中的记录会按照主键值从小到大的顺序组成一个单向链表 ，每个数据页都会为存储在它里边的记录生成一个页目录 ，在通过主键查找某条记录的时候可以在页目录中 使用二分法 快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录。

4.2 页的内部结构

页如果按类型划分的话，常见的有 数据页（保存 B+ 树节点） 、系统页 、Undo页和事务数据页等。

数据页是我们最常使用的页。

数据页的 16KB 大小的存储空间被划分为七个部分，分别是文件头（File Header）、页头（Page Header）、最大最小记录（Infimum+supremum）、用户记录（User Records）、空闲空间（Free Space）、页目录（Page Directory）和文件尾（File Tailer）。页结构的示意图如下所示：

4.2 页的上层结构

另外在数据库中，还存在着区（Extent）、段（Segment）和表空间（Tablespace）的概念。行、页、区、段、表空间的关系如下图所示：

4.2.1 区

B+ 树的每一层中的页都会形成一个双向链表，如果是以页为单位来分配存储空间的话，双向链表相邻的两个页之间的 物理位置可能离得非常远。我们介绍B+树索引的适用场景的时候特别提到范围查询只需要定位到最左边的记录和最右边的记录，然后沿着双向链表一直扫描就可以了，而如果链表中相邻的两个页物理位置离得非常远，就是所谓的 随机I/O 。再一次强调，磁盘的速度和内存的速度差了好几个数量级， 随机I/O是非常慢的，所以我们应该尽量让链表中相邻的页的物理位置也相邻，这样进行范围查询的时候才可以使用所谓的顺序I/O 。

引入区的概念，一个区就是在物理位置上连续的 64个页。因为 InnoDB 中的页大小默认是 16KB，所以
一个区的大小是 64*16KB= 1MB。在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照区为单位分配，甚至在表中的数据特别多的时候，可以一次性分配多个连续的区。虽然可能造成一点点空间的浪费 （数据不足以填充满整个区），但是从性能角度看，可以消除很多的随机I/O，功大于过！

4.2.2 段

对于范围查询，其实是对 B+ 树叶子节点中的记录进行顺序扫描，而如果不区分叶子节点和非叶子节点，统统把节点代表的页面放到申请到的区中的话，进行范围扫描的效果就大打折扣了。

所以 InnoDB 对 B+树的叶子节点和非叶子节点进行了区别对待，也就是说叶子节点有自己独有的区，非叶子节点也有自己独有的区。

存放叶子节点的区的集合就算是一个段（ segment ），存放非叶子节点的区的集合也算是一个段。也就是说一个索引会生成2个段，一个 叶子节点段，一个非叶子节点段。

除了索引的叶子节点段和非叶子节点段之外，InnoDB 中还有为存储一些特殊的数据而定义的段，比如回滚段。所以，常见的段有数据段、索引段、回滚段。数据段即为B+树的叶子节点，索引段即为B+树的非叶子节点。

在 InnoDB 存储引擎中，对段的管理都是由引擎自身所完成，DBA 不能也没有必要对其进行控制。这从一
定程度上简化了DBA对于段的管理。

段其实不对应表空间中某一个连续的物理区域，而是一个逻辑上的概念，由若干个零散的页面以及一些完整的区组成。

4.2.3 表空间

独立表空间，即每张表有一个独立的表空间，也就是数据和索引信息都会保存在自己的表空间中。独立
的表空间（即：单表）可以在不同的数据库之间进行 迁移。

空间可以回收（DROP TABLE 操作可自动回收表空间；其他情况，表空间不能自己回收）。如果对于统计分析或是日志表，删除大量数据后可以通过：alter table TableName engine=innodb;回收不用的空间。对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。