一 数据库的存储结构：页

索引结构提供了高效的检索方式，不过索引信息和数据记录都是保存在文件上的，确切的说是存储在页结构中。另一方面，索引是在引擎中实现的，MySQL服务器上的存储引擎负责对表中数据的读取和写入。不同的存储引擎中，存放的格式一般是不同的，甚至有的存储引擎，比如Memory都不用磁盘来存储数据。

由于InnnoDB是MySQL的默认存储引擎，所以本文解析的是InnoDB的存储引擎。

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

InnoDB将数据划分为若干个页，InnoDB页的大小默认为16KB。

以页作为磁盘和内存之间交互的基本单位，也就是一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中16KB内容刷到磁盘。也就是说，在数据库中，无论读一行，还是读多行，都是将这些行所在页进行加载。数据库管理存储空间的基本单位是页，数据库IO操作的最小单位是页。一个页中可以存储多个行记录。

记录是按照行来存储的，但是数据库的的读取并不是以行为单位，否则一次读取（一次IO操作）只能处理一行数据，效率会非常低。

1.2 页结构概述

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

1.3 页大小

不同的数据库管理系统的页大小不同。比如在MySQL的InnoDB存储引擎中，默认的大小是16KB，可使用以下命令查看

show variables like '%innodb_page_size';

1.4 页的上层结构

在数据库中，还存在着区、段、表空间的概念。行、页、区、段、表空间的关系如下图所示

区是比页大一级的存储结构，在InnoDB存储引擎中，一个区会分配64个连续的页。因为InnoDB中的页大小默认是16KB，所以一个区的大小是64*16KB=1MB

段由一个或多个区组成，区在文件系统中是一个连续分配的空间。不过在段中，不要求区与区之间是相邻的。段是数据库中的分配单位，不同类型的数据库对象以不同的段形式存在。当我们创建数据表、索引的时候，就会相应创建对应的段，比如创建一张表时，会创建一个表段；创建一个索引时，会创建一个索引段

表空间是一个逻辑容器，表空间存储的对象是段，在一个表空间中可以有一个或多个段，但是一个段只能属于一个表空间。数据库有一个和多个表空间组成，表空间从管理上可以划分为系统表空间，用户表空间，撤销表空间，临时表空间。

二 数据页

2.1 内部结构

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

数据页的16KB大小的存储空间被划分为7个部分，分别是文件头（File Header）、页头（Page Header）、最大最小记录（Inflimum+supremum）、用户记录（User Records）、空闲空间（Free Space）、页目录（Page Directory）和文件尾（File Tailer）

页面结构示意图如下所示：

这7个部分的作用如下

2.2 页目录

• 为什么需要页目录？

• • 在页中，记录是以单向链表的形式进行存储的。单向链表的特点就是插入、删除非常方便，但是查询效率不高，最差情况需要遍历链表上的所有节点才能完成检索
  • 因此在页结构中涉及了页目录这个模块，专门给记录做一个目录，通过二分查找发的方式进行检索，提升效率

• 需求：根据主键值查找页中的某条记录，如何实现快速查找呢？SELECT * FROM page_demo WHERE c1 = 3;

• • 方式1：顺序查找

• • • 从Infimum记录（最小记录）开始，沿着链表一直往后找，总有一天会找到（或者找不到），在找的时候还能投机取巧，因为链表中各个记录的值是按照从小到大顺序排列的，所以当链表的某个节点代表的记录的主键值大于你想要查找的主键值时，你就可以停止查找了，因为该节点后边的节点的主键值依次递增。如果一个页中存储了非常多的记录，这么查找性能很差。

• • 方式2：使用页目录，二分法查找

• • • 1、将所有的记录分成几个组，这些记录包括最小记录和最大记录，但不包括标记为“已删除”的记录
    • 2、第 1 组，也就是最小记录所在的分组只有 1 个记录

• • • • 最后一组，就是最大记录所在的分组，会有 1-8 条记录
      • 其余的组记录数量在 4-8 条之间

• • • • • 这样做的好处是，除了第 1 组（最小记录所在组）以外，其余组的记录数会尽量平分

• • • 3、在每个组中最后一条记录的头信息中会存储该组一共有多少条记录，作为 n_owned 字段
    • 4、页目录用来存储每组最后一条记录的地址偏移量，这些地址偏移量会按照先后顺序存储起来
    • 5、每组的地址偏移量也被称之为槽（slot），每个槽相当于指针指向了不同组的最后一个记录

2.2.1 页目录分组的个数如何确定？

InnoDB规定：对于最小记录所在的分组只能有1条记录，最大记录所在的分组拥有的记录条数只能在1-8条之间，剩下的分组中记录的条数范围只能在是 4-8 条之间。

• 分组是按照下边的步骤进行的：

1. 1. 初始情况下一个数据页里只有最小记录和最大记录两条记录，它们分属于两个分组。
   2. 之后每插入一条记录，都会从页目录中找到主键值比本记录的主键值大并且差值最小的槽，然后把该槽对应的记录的n_owned值加1，表示本组内又添加了一条记录，直到该组中的记录数等于8个。
   3. 在一个组中的记录数等于8个后再插入一条记录时，会将组中的记录拆分成两个组，一个组中4条记录，另一个5条记录。这个过程会在页目录中新增一个槽来记录这个新增分组中最大的那条记录的偏移量。

2.2.2 举例

2.2.3 从数据页中查询记录

在一个数据页中查找指定主键值的记录的过程分为两步

1. 1. 通过二分法确定该记录所在的槽，并找到该槽所在分组中主键值最小的那条记录
   2. 通过记录的next_record属性遍历该槽所在的组中的每一条记录

2.2.3 从B+树中查询数据

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

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

当我们从页结构来理解B+树结构的时候，可以帮助我们理解一些通过索引进行检索的原理：
1、B+树是如何进行进行检索的？

如果通过B+树的索引查询行记录，首先是从B+树的根节点开始，逐层检索，直到找到叶子节点，也就是找到数据页，将数据页加载到内存中，页目录中采用二分查找的方式找到一个粗略的记录分组（槽），然后再在分组中通过遍历链表的方式进行查找记录。

2、普通索引和唯一索引在查询效率上有什么不同吗？

创建索引的时候可以是普通索引，也可以是唯一索引，那么这两个索引的查询效率上有什么不同呢？

唯一索引就是在普通索引上增加了约束性，也就是关键字唯一，找到了关键字就停止索引。而普通索引，可能会存在用户记录中关键字相同的情况，根据页结构的原理，当我们读区一条记录时，不单独将这条记录从磁盘中读出去，而是将这个记录所在的页加载到内存中进行读取。InnoDB存储引擎的页大小为16KB，在一个页中可能存在着上千条记录，因此在普通索引的字段上进行查找也就是在内存中多几次判断下一条记录的操作，对于CPU来说，这些操作所消耗的时间上可以忽略不计的。所以对于一个索引字段进行检索，采用普通索引还是唯一索引在检索效率上并没有什么区别。

三 区、段、碎片区

3.1 为什么要有区

B+树的每一层中的页都会形成一个双向链表，如果是以页为单位来分配存储空间的话，双向链表相邻的两个页面之间的物理距离可能会离的非常远。在B+树索引的使用场景中，范围查询只需要定位到最左边或最右边的记录，然后双向链表一直扫描就可以了，而如果链表中相邻的两个页物理位置离的非常远，就是所谓的随机IO。（磁盘的速度比内存的速度差好几个量级）随机IO是非常慢的，所以我们应该让链表相邻的页，物理位置也相邻，这样进行范围查询的时候才可以使用所谓的顺序IO。

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

3.2 为什么要有段

范围查询，是对B+树叶子节点中的记录进行顺序扫描，而如果不区分叶子节点和非叶子节点，统统把节点代表的页面放到申请的区中，进行范围扫描的效果就大打折扣了。所以InnoDB对于B+树的叶子节点和非叶子节点进行区别对待，也就是说叶子节点有自己独有的区，非叶子节点也有自己独有的区。存放叶子节点的区的集合算是一个段，存放非叶子节点的区的集合也算是一个段。也就是说一个索引会生成2个段，一个叶子节点段、一个非叶子节点段。

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

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

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

3.3 为什么要有碎片区

默认情况下，一个使用InnoDB存储引擎的表只有一个聚簇索引，一个索引会生成两个段，而段是以区为单位申请存储空间，一个区默认占用1M的存储空间，所以默认情况下一个只存了几条记录的小表也需要2M的存储空间吗？以后每次添加一个索引就需要多申请2M的存储空间吗？这对于存储记录比较少的表是很大的浪费。这个问题的症结在于到现在为止，介绍的区都非常纯粹，也就是一个区被整个分配给了某一个段，或者说区中的所有页都是为了存储同一个段的数据而存在的，即使段数据不满足区中的所有页，那剩余的页也不能挪作他用。

以完整的区为单位分配给某个数据量较小的段，太浪费存储空间的情况，InnoDB提出了一个碎片区的概念。在一个碎片区中，并不是所有的页都是为了存储同一个段数据而存在的，而是碎片区的页可以存储不同段的数据，比如有些页，用于段A，有些页用于段B，有些页面甚至不属于任何一个段。碎片区直属于表空间，并不属于任何一个段。

所以为某一个段分配存储空间的策略是：

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

段是某些零散的页面以及一些完整区的集合

3.4 区的分类

区大体上可分为4类 ：

• 空闲的区（FREE）：现在还没有用到这个区中的任何页面
• 有剩余空间的碎片区（FREE_PAGE）：表示碎片区中还有可用的页面
• 没有剩余空间的碎片区（FULL_PAGE）：表示碎片区中的所有页面都被使用，没有空闲页面
• 附属于某个段的区（FSEG）：每一个索引都可以分为叶子节点段和非叶子节点段

处于FREE、FREE_PAGE、以及FULL_PAGE的三个状态的区都是独立的，直属于表空间。而处于FSEG状态的区是附属于某个段的

如果把表空间比做是集团军，段相当于师、区相当于团。
一般的团隶属于某个师，就像是处于FSEG的区全部隶属于某个段，
而处于FREE、FREE_PAGE、FULL_PAGE这三种状态的区却隶属于表空间，就像独立团直接听命于军部

四 表空间

表空间可以看做是InnoDB存储引擎逻辑结构的最高层，所有的数据都存放在表空间中

表空间是一个逻辑容器，表空间存储的对象是段，在一个表空间中可以有一个或多个段，但是一个段只能属于一个表空间。表空间数据库由一个或多个表空间组成，表空间从管理上可以划分为系统表空间，独立表空间，撤销表空间、临时表空间等

4.1 独立表空间

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

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

五 数据加载的三种方式

InnoDB从磁盘中读取数据的最小单位是页。而查询的数据id = xxx，是数据页中众多行中的一行。

对MySQL存放的数据，逻辑上称为表，在磁盘等物理层面而言都是按照数据页的形式存放的，将其加载到MySQL中，我们称之为缓存页。

如果缓存池中没有该页数据，那么缓存池有一下三种读取数据的方式，每种方式的读取效率都是不同的：

5.1 内存读取

如果该数据存在于内存中，基本上执行时间在1ms左右，效率很高

5.2 随机读取

如果数据没有在内存中，就需要在磁盘上对该页进行查找，整体时间预估在10ms，10ms中有6ms是磁盘的实际繁忙时间（包括寻道和半圈旋转时间），3ms是对可能发生的排队时间的预估值，另外还有1ms的传输时间，将页从磁盘服务器缓冲区传输数据到数据库缓冲区。10ms看起来很快，但是对于数据库来说耗时已经非常长了，并且这还是一个页的读取时间

5.3 顺序读取

顺序读取其实是一种批量读取的方式，因为我们请求的数据在磁盘上往往是相邻存储的，顺序读取可以帮我们批量读取页面，一次性加载到缓冲池中就不需要在对其他页面进行单独磁盘IO操作了。如果一个磁盘的吞吐量是40MB/s,那么对于一个16KB的页来说，一次可以顺序读取2560个页，相当于一个页的读取时间为0.4ms。采用批量读取的方式，即使是从磁盘上读取，效率也比从内存中单独读取一个页的效率高。