Mysql复习计划（二）- 存储引擎、页结构、索引

前言

在上一章节，讲到了Mysql中SQL的执行流程，对于Mysql而言。这一类不涉及到真实数据存储的功能将其划分为Mysql Server。

而存储数据的功能划分为存储引擎，也就是本章节要讲的。

一. 存储引擎

查看当前的Mysql提供什么存储引擎，命令如下：

show ENGINES;

结果如下：

• Transaction代表：当前存储引擎是否支持事务。
• XA代表：是否支持分布式事务。
• Savepoints：是否支持保存点。

Mysql在5.5之前默认的存储引擎为MyISAM，在之后呢，则默认为InnoDB引擎。

1.1 InnoDB引擎和MyISAM引擎的区别

InnoDB的亮点用一句话来说就是：具备外键支持功能的事务存储引擎。

• 对比MyISAM，InnoDB写的处理效率要差一点，并且占用更多的磁盘空间来保存数据和索引。
• MyISAM只缓存索引，不缓存真实数据。InnoDB两者都缓存，因此对内存的要求比较高。
• 事务方面：InnoDB支持，MYISAM不支持。即崩溃后可以通过事务来回滚。
• 行表锁：InnoDB支持行锁，操作时只锁一行，不会对其他行有影响，适合高并发。MyISAM支持表锁，不适合高并发。
• MyISAM针对数据的统计有额外的常数存储，因此使用count(*)的查询效率很高。
• MyISAM数据文件结构：.frm存储表结构，.MYD存储数据，.MYI存储索引。InnoDB数据文件结构：frm（Mysql8.0后整合到ibd文件中了），ibd存储索引和数据。

1.2 索引

Mysql中使用索引，目的是为了减少磁盘的IO次数，加快查询效率。Mysql中对索引的定义为：索引是帮助Mysql高效获取数据的数据结构。

索引的本质是数据结构，满足特定的查找算法，这些数据结构以某种方式指向数据，因此可以在这些数据结构的基础上实现高效查找算法。

索引的优点：

1. 类似图书馆检索，提高数据检索的效率，降低数据库IO成本。
2. 创建唯一索引，保证数据库表中每行数据的唯一性。
3. 加速表和表之间的连接。
4. 在使用分组和排序的时候，可以显著减少查询中分组和排序的时间，降低CPU的消耗。 （建立索引的时候，已经保证数据有效，那么order by排序的时候，效率当然很高）

索引的缺点：

1. 创建和维护索引需要消耗时间。数据量越高，维护的成本越大。
2. 索引需要占用磁盘空间。
3. 索引大大提高查询速度，但是也降低了更新表的速度，对于表数据的删除，增加，索引也需要动态地维护。

现在来看下索引的一些常见概念。

1.2.1 聚簇索引☆

一种数据存储方式（所有的记录存储在叶子节点），也就是索引即数据，数据即索引。通俗点就是主键。

聚簇的概念：表示数据行和相邻的键值聚簇的存储在一起。

特点：

1.使用记录主键值的大小进行记录和页的排序，含义：

• 页内的记录是按照主键的大小顺序排序成一个单向链表。
• 各个数据页之间则根据页中记录的主键大小顺序排成双向链表。
• 数据页的相关信息又由目录项记录，分为不同的层次，同一个层次中的页根据页中目录项纪录的主键大小排成双向链表。

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

---

优点：

• 数据访问更快。因为聚簇索引将索引和数据保存到同一个B+树中。
• 聚簇索引对于主键的排序和范围查找速度很快。
• 按照聚簇索引排序，进行范围查找的时候，由于数据之间紧密相连，因此数据库不用从多个数据块中去提取数据，节省大量IO操作。

---

缺点：

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

限制：

1. MyISAM不支持聚簇索引。
2. 由于数据物理存储排序方式只能有一种，因此每个表最多只能有一个聚簇索引。
3. 若没有定义主键，InnoDB会选择非空的唯一索引来代替。若不存在这样的索引，InnoDB会隐式定义一个主键作为聚簇索引。

聚簇索引的B+树结构图如下：

注意：

• 最下层的叶子节点，存储的数据都是真实的用户数据（按照主键大小排序）

1.2.2 非聚簇索引

如果除了主键，我们还希望以别的列作为搜索条件，那么这个时候可以多建几颗B+树。也就是非聚簇索引，也可以说是二级索引。

结构图如下：假设我们以列c2作为非聚簇索引。

在非聚簇索引的B+树结构图中，可以发现和聚簇索引结构有这么几个不同：

比较内容	聚簇索引	非聚簇索引
页内的记录（记录之间单项链表）按照什么排序？	按照主键	c2列的大小
存放用户记录的页（页和页之间双向链表），按照页中的什么来排序？	主键	c2列的大小
存放目录项记录的页，同一个层次（双向链表连接），按照页中目录项记录的什么来排序？	主键	c2列的大小
重点：B+树的叶子节点存储的数据	主键+完整的用户数据	c2列的值+主键

也因此：

1. 如果我们根据非聚簇索引去查找某个用户的所有数据，找到了B+树当中的叶子节点的某条记录。
2. 由于非聚簇索引叶子节点存储的是 （非聚簇索引对应的值+ 主键），此时并不包含用户相关的数据。
3. 因此还需要根据主键，来再进行一次查询。

而步骤三的这个过程，也就是所谓的 回表。也就是说，根据非聚簇索引去查询一个用户的完整信息，需要用到两颗B+树，进行2次查询。

---

问题：为什么需要进行回表操作呢？干脆把用户信息也存储到非聚簇索引的叶子节点上不就好了。

回答：

1. 首先，用户数据所占的内存空间较大，占存储资源。
2. 而且一张表可以有多个非聚簇索引，那么如果一张表有10个非聚簇索引，那等于有11颗B+树（包括主键的）的叶子节点都存储了用户信息，这不就重复了吗？

1.2.3 联合索引

即同时以多个列的大小作为排序规则。比如让B+树以c2和c3列的大小进行排序：

• 此时会将各个记录和页按照c2列进行排序。
• 在记录的c2列相同的情况下，再根据c3列进行排序。

联合索引本质上和非聚簇索引一样，叶子节点存储的记录，以上面为例就是c2+c3+主键构成。

需要注意的点就是：

1. 为c2列和c3列建立联合索引，只会建立一颗B+树。
2. 为c2列，c3列建立非聚簇索引，会建立两颗B+树。

1.3 InnoDB中B+树索引的注意事项☆

第一点：根页面（B+树根节点）万年不动，InnoDB中B+树的数据结构是通过该根节点不断的进行页分裂得来的。

形成过程如下：

1. 每当为表创建一个B+树索引，都会创建一个根节点页面。随后向表中插入记录时，先将用户记录存储到根节点中。
2. 当根节点中的可用空间用完的时候，此时再插入记录。会将根节点中的所有记录复制到一个新分配的页，例如页A。
3. 然后对这个A进行页分裂，得到另一个新页B。
4. 此时新插入的记录会根据键值的大小，分配到页A或者页B中，而根节点升级为存储目录项记录的页。
5. 以此类推，不断分裂，形成一层层的节点，形成B+树。

---

第二点：内节点中目录项记录的唯一性。

第三点：一个页中最少存储2条记录。

1.4 MyISAM索引实现

MyISAM引擎也是用B+树来作为索引结构的，不过其叶子节点保存的数据是数据记录的地址。

MyISAM索引原理：

1. 首先我们知道MyISAM将索引和数据分开存储。将表中的记录按照记录的插入顺序单独存储一个文件中（数据文件）。而数据文件并不会划分为若干个数据页，也不会按照主键大小进行排序（按照插入顺序）。所以查找的时候不能在这样的数据结构上使用二分查找。
2. 使用MyISAM的表将索引信息存储到一个专门的索引文件。MyISAM会单独为表的主键创建一个索引，其叶子节点存储的是主键值+数据记录地址。

例如：

1.4.1 MyISAM和InnoDB对比

MyISAM的索引存储都是非聚簇的（毕竟不是直接保存的数据本身），而InnoDB中包含一个聚簇索引。

在InnoDB中，只需要根据主键值进行一次查找就能找到对应的记录。而MyISAM中，需要进行一次回表操作。

InnoDB的数据文件本身就是索引文件，而MyISAM的索引文件和数据文件是分离的。

InnoDB的非聚簇索引叶子节点的data域存储的是记录的主键值，而MyISAM中存储的是数据的存储地址。

MyISAM的回表操作非常快，因为拿着地址的偏移量直接到文件中读取数据。而InnoDB中是通过获取主键之后，再去B+树进行查找。（比不过直接用地址去访问）

1.4.2 根据存储引擎的实现来优化索引

1. 不建议使用过长的字段作为主键。 原因：所有非聚簇索引都引用了主键索引，过长的主键索引会导致非聚簇索引变得过大。
2. 使用自增字段作为主键。 原因：InnoDB数据存储结构为一颗B+树，非单调的主键插入时，会导致数据文件为了维持B+树的特性而频繁的分裂调整。十分低效。

二. InnoDB数据存储结构

InnoDB是Mysql的默认存储引擎，因此着重学习InnoDB的数据存储结构。

2.1 页

页是磁盘和内存交互的一个基本单位。InnoDB中将数据划分为若干页，而默认大小为16KB。

• 一次最少从磁盘中读取16KB的内容到内存中。
• 一次最少把内存中的16KB内容刷新到磁盘中。

在数据库中，不论是读取一行，还是读取多行，都是将这些数据行所在的页进行加载。 因为数据库管理存储空间的基本单位是页，数据库IO操作的最小单位也是页。

页结构（Block）概述：页与页之间可以不在物理结构上相连，只通过双向链表关联即可。每个页中的记录则按照主键值大小顺序组成单向链表。每个数据页都会为存储在里面的记录生成一个页目录，通过主键查找的时候使用二分法进行定位。

如图：

可以使用命令来查看数据库页的大小：

show VARIABLES like '%innodb_page_size%'

2.1.1 页的上层结构

在页外还存在着以下几种结构：

• 区（Extent）：一个区会分配64个连续的页，因此一个区的大小为1MB。
• 段（Segment）：段由一个或者多个区组成，段作为数据库中的分配单位，不同类型的数据库对象以不同的段形式存在。
• 表空间（Tablespace）：一个段只能属于一个表空间，数据库由一个或者多个表空间组成，表空间从管理上可分为系统表空间、用户表空间、撤销表空间、临时表空间。
• 数据行：页中的一条条数据。

回顾Tip：

• InnoDB中采用系统表空间模式：数据信息和索引信息存储到ibdata1中。
• 采用独立表空间模式：data目录下中产生xx.ibd文件。

结构图如下：

其中，区有4种类型：

1. 空闲区 FREE：没有用到该区的任何页。
2. 有剩余空间的碎片区 Free_FRAG：表示碎片区中存在可用的页。
3. 没有剩余空间的碎片区 FULL_FRAG：碎片区中的所有页都被使用。
4. 附属于某个段的区 FSEG：每一个索引都会生成叶子节点段（数据段）和非叶子节点段（索引段）。那么这两个段附属于该索引。

---

问题1：区存在的意义是什么？

首先背景：

1. B+树的每一层，页与页之间会形成一个双向链表。如果以页为单位分配存储空间的话，双向链表相邻两个页之间的物理位置可能离得非常远。
2. 在我们进行索引的范围查找的时候，一般是定位到最左边的记录和最右边的记录，然后沿着双向链表进行扫描。
3. 若链表中相邻页的物理位置隔得很远，那么此时扫描就成了随机IO。随机IO的效率远远不及顺序IO。

因此引入区解决这样的问题：

1. 一个区在物理位置上有64个连续的页，也就是1MB。表中数据量大的时候，不再以页为单位分配空间了，而是以区为单位进行分配。
2. 区的目的就是消除了大量的随机IO的发生。

---

问题2：段存在的意义是什么？

背景：

1. 范围查找的时候，也就是对B+树叶子节点的记录进行顺序扫描。
2. 若不区分叶子节点和非叶子节点，都将其对应的页放入到申请的区中的话，范围扫描的效率就很低了。

因此引入段的概念：

1. 对B+树的叶子节点和非叶子节点进行区别对待。叶子节点有自己的区，非叶子节点有自己的区。
2. 而存放叶子/非叶子节点的区的集合就是一个段。 ，也就是一个索引会生成2个段。
3. 常见的段有：回滚段，数据段（叶子节点），索引段（非叶子节点）。
4. 注意：段并不是一个连续的物理区域，而是一个逻辑上的概念。一个段可能由若干个零散的页和完整的区构成。

---

问题3：什么是碎片区？

背景：

1. 一个表使用InnoDB存储引擎，最多一个聚簇索引。一个索引生成2个段。（数据段和索引段）
2. 段以区为单位申请存储空间，一个区=1MB，那么一张表=2个段=2MB。
3. 但是如果一个只存储了几条数据的小表，也需要2MB的存储空间吗？

为了考虑以完整的区为单位分配给某个段，但是数据量较小的表太浪费存储空间的这种情况。引入碎片区的概念：

1. 一个碎片区中，里面的页可以用于不同的目的，来自不同的段。甚至不属于任何段。
2. 碎片区直属于表空间。

因此Mysql为某个段分配存储空间的策略如下：

• 刚开始向表中插入数据的时候，段是从 某个碎片区 的 单个页 为单位来分配内存空间。
• 当段已经占用了32个碎片区页面之后，就会申请完整的区为单位来分配存储空间。

---

2.1.2 页的内部结构

页其实也有类型的划分：

• 数据页（保存B+树节点）。
• 系统页。
• Undo页。
• 事务数据页。

其中数据页是我们最常用和接触最多的，其中16KB大小的存储空间划分为七个部分：

1. 文件头：占用38字节，描述页的信息。
2. 页头：占用56字节，描述页的状态信息。
3. 最大最小记录：占用26字节，最大和最小记录（虚拟的行记录）。
4. 用户记录：存储行记录内容。
5. 空闲空间：页中还没有被使用的空间。
6. 页目录：存储用户记录的相对位置。
7. 文件尾：校验页是否完整。

结构图如下：

---

一般分为三个部分：

第一部分：校验完整性部分

1.File Header 文件头：保存了页的编号、页的类型、 还有两个指针指向前后页（双向链表）。当前页的校验和，页最后被修改时对应的日志序列位置LSN。

校验和：对于一个很长的字符串，通过某种算法计算出一个比较短的值来代表该字符串，这个较短的值就是校验和。
若两个字符串之间的比较，其校验和不一致，那么两个字符串本身就是不一样的。

那么Mysql中的校验和有什么用？

1. 检验一个页是否完整（同步过程发生中断），此时通过比较文件尾的校验和和文件头的校验和。
2. 若两个值不一致或者文件头尾的LSN不一致，说明页的传输有问题。
3. 一般来说，一个页面在内存中修改，在同步之前就会将其校验和计算出来，因为文件头会先写入磁盘。

2.File Trailer 文件尾：保存了页的校验和、以及页最后被修改时对应的日志序列位置LSN。

---

第二部分：数据记录部分

1.Free Space 空闲空间：每当插入一条记录，都会从空闲空间中申请一个记录大小的空间，划分到User Records部分。当空闲空间全部被用完，此时需要申请新的页。

2.User Records 用户记录：按照指定的行格式，一条条地摆在User Records 部分，记录之间以单链表的形式关联。

3.Infimum+Supremum 最小最大记录：即主键的最小最大值。其实是个虚拟节点（详见下文行格式章节中的记录头信息部分）

第三部分：目录和头部分

1.Page Directory 页目录。

1. 会将所有的记录分成多个组，包括最小和最大记录，但是不包括标记为已删除的记录。
2. 第一组，只包含最小记录。
3. 最后一组，即最大记录所在的分组，有1-8条记录。
4. 其余的组的数量在4-8条之间。
5. 而每个组的最后一条记录的记录头信息，会存储改组有多少条记录。
6. 其中，每个组的地址偏移量，称之为槽。

2.Page Header 页头部：

1. 用于记录每个数据页中存储的记录状态信息。
2. 保存了本页有多少条记录、第一条记录的地址，页中有多少个槽（组）等信息。

2.1.3 从数据页的角度来看B+树的查询原理

一颗B+树按照节点类型分为两个部分：

1. 叶子节点：B+树的最底层节点，高度为0，存储行记录。
2. 非叶子节点：节点高度大于0，存储索引键和页的指针。

---

问题1：B+树是如何进行记录检索的？

回答：

1. 若通过B+树的索引来查询，从根开始，逐层检索，直到找到叶子节点。
2. 即找到对应的数据页，此时将页加载到内存中。
3. 页目录中的槽，采用二分查找的方式找到一个记录分组。
4. 在分组中通过链表遍历的方式查找记录。

---

2.2 数据行

我们插入到数据库中的数据都是以行为单位，这些记录在磁盘上的存储方式称之为行格式。 InnoDB提供了4种不同类型的行格式：

1. Compact。
2. Redundant。
3. Dynamic。
4. Compressed。

查看数据库默认的行格式：

show VARIABLES like '%innodb_default_row_format%'

指定行格式：

create table user2(id int,name VARCHAR(10)) row_format=compact;
# 查看指定表的行格式类型
show table status like 'user2'

2.2.1 Compact 行格式

Compact 行格式下，一条完整的记录可以被分为4大部分：

1. 变长字段长度列表。
2. Null值列表。
3. 记录头信息。
4. 记录的真实数据。

第一部分：变长字段长度列表

什么叫变长字段呢？

诸如varchar，varbinary，text等类型。这种字段中存出多少个字节的数据并不是固定的，因此在存储真实数据的时候，需要将这些数据占用的字节数保存下来。

第二部分：Null值列表

将可以为Null的列统一管理起来，形成个列表。存储格式如下：

• 二进制位的值为1：该列的值为null。
• 二进制为的值为0：该列的值不为null。

第三部分：记录头信息

记录头信息：

此时插入3条数据后

记录头信息中包含：

1. delete_mark：标记着当前记录是否被删除。0代表没有，1代表被删除。

2. min_rec_mark：B+树每层非叶子结点中的最小记录都会有这个标记，为1。

3. record_type：表示当前的记录类型。0：普通记录。1：表示B+树非叶子节点记录。2：最小记录。3：最大记录。在回顾下上文的图：
   
   最左侧是最小记录代表2，最右侧最大记录代表3。

4. heap_no：表示当前记录在本页当中的具体位置。

5. n_owned：页目录中每个组的 最后一条记录的头信息会存储该组有多少条记录，作为n_owned字段。

6. next_record：当前记录到下一条记录的地址偏移量。（行记录之间通过该字段来形成单链表的结构）

---

问题1：为什么被删除的记录不是直接删掉，而是通过delete_mark去记录删除标记呢？

回答： 因为移出某条数据之后，其他记录在磁盘上就需要重新排列，导致性能消耗。实际上，所有被删除的记录会组成一个垃圾链表，在这个链表所占用的空间叫做可重用空间，之后有新记录插入到表中的时候，会进行空间的替换。

---

问题2：为什么记录的位置，以上面案例图为例，为何从2开始，记录0和1呢？

回答：Mysql会自动给每个页都添加两个记录，其作为伪记录，分别代表最小记录和最大记录。其heap_no值分别为0和1。

---

第四部分：记录的真实数据

该部分，除了我们自定义的一些用户真实数据列。还会有3个隐藏列：

1. row_id：行id，唯一标识。
2. transation_id：事务id。
3. roll_pointer：回滚指针。

2.2.2 Dynamic/Compressed 行格式

首先先来讲个概念：

行溢出：InnoDB可以将一条记录中的某些数据存储在真正的数据页面之外。

下面我们用个案例来表述什么是行溢出。例如，我们创建一张表：

CREATE TABLE varchar_size_demo ( 
	c VARCHAR ( 65535 ) 
) charset = ASCII row_format = compact;

结果如下，报错：

首先我们可以发现，Varchar类型的字段，其大小最高是65535，那我指定的也是65535，也没超过限制，为啥报错呢？我们将其改成65532试试：

CREATE TABLE varchar_size_demo ( 
	c VARCHAR ( 65532 ) 
) charset = ASCII row_format = compact;

可见成功了。

原因是什么呢？

1. 首先Varchar类型的字段最大是65535字节。
2. 创建一个65532字节，创建成功。此时此刻的公式为。
3. 65535 = 65532 + 2个字节的变长字段的长度 + 1字节的Null标识

比如：我们如果指定了Not Null，那么此时可以再多一个字节用来保存数据。

CREATE TABLE varchar_size_demo1 ( 
	c VARCHAR ( 65533 ) not null
) charset = ASCII row_format = compact;

我们知道，一个页的大小是16KB，也就是16384字节，但是一张表的Varchar类型，最多可以存储65533个字节，这样会出现一个数据页存放不了一条记录，这种现象也就是行溢出。

在Compact和Reduntant行格式中，对于这类数据，在记录真实数据的时候，只会存储该列的一部分数据，剩余的部分则分散存储在其他的几个页中进行分页存储。

Dynamic/Compressed行格式对于处理行溢出有着不同的操作：

• Dynamic/Compressed两种行格式，对于这种超大的数据，采用完全行溢出的方式。记录真实数据的时候，只记录（存储了溢出的数据的页）页地址指针。
• Compressed还有个功能：存储的行数据会以zlib算法进行压缩。

2.2.3 Reduntant 行格式

不同于Compact行格式，Reduntant的首部是一个字段长度偏移列表（Compact是变长字段长度列表）。

Reduntant行格式的记录头信息与Compact相比有这么几个不同：

• Reduntant中，多了两个字段n_fields和1byte_offs_flag，分别存储记录中列的数量、字段长度偏移链表每个列对应的偏移量。
• Reduntant中没有record_type属性。

2.3 数据页加载的三种方式

首先，Mysql在磁盘等物理层面的地方存储，以 数据页 形式进行存放。当其加载到Mysql中，我们称之为 缓存页。

如果缓存池中没有该页数据，那么缓冲池有3种去读取数据。

1.内存读取：若该数据存在于内存中，执行时间在1ms左右。

2.随机读取：若数据不存在内存中，则需要在磁盘上对该页进行查找，整体时间大概在10ms左右。其中时间分配大概如下：

• 6ms：磁盘的实际繁忙时间。
• 3ms：对可能发生的排队时间的估计值。
• 1ms：数据传输时间。将页从磁盘服务器缓冲区传输到数据库缓冲区中。

3.顺序读取：一种批量读取的方式。我们请求的数据在磁盘上往往是相邻存储的。顺序读取帮助我们批量读取页。