关于 mysql 数据存储，你了解多少？
前言
大家都知道 MySQL 的数据都是保存在磁盘的，那具体是保存在哪个文件呢？
MySQL 存储的行为是由存储引擎实现的，MySQL 支持多种存储引擎，不同的存储引擎保存的文件自然也不同。
InnoDB 是我们常用的存储引擎，也是 MySQL 默认的存储引擎。本文主要以 InnoDB 存储引擎展开讨论。
InnoDB简介
InnoDB是一个将表中的数据存储到磁盘上的存储引擎。而真正处理数据的过程是发生在内存中的，所以需要把磁盘中的数据加载到内存中，如果是处理写入或修改请求的话，还需要把内存中的内容刷新到磁盘上。
而我们知道读写磁盘的速度非常慢，和内存读写差了几个数量级。
所以当我们想从表中获取某些记录时，InnoDB存储引擎需要一条一条的把记录从磁盘上读出来么？
想要了解这个问题，我们首先需要了解InnoDB的存储结构是怎样的。
InnoDB采取的方式是：将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位innodb_page_size选项指定了MySQL实例的所有InnoDB表空间的页面大小。
这个值是在创建实例时设置的，之后保持不变。有效值为64KB，32KB，16KB(默认值 )，8kB和4kB。也就是在一般情况下，一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中。
InnoDB 行格式
我们平时是以记录为单位来向表中插入数据的，这些记录在磁盘上的存放方式也被称为行格式或者记录格式。
一行记录可以以不同的格式存在InnoDB中，行格式分别是compact、redundant、dynamic和compressed行格式。
可以在创建或修改的语句中指定行格式：
-- 创建数据表时,显示指定行格式
CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称;
-- 创建数据表时,修改行格式
ALTER TABLE 表名 ROW_FORMAT=行格式名称;
-- 查看数据表的行格式
show table status like '';mysql5.0之前默认的行格式是redundant，mysql5.0之后的默认行格式为compact ， 5.7之后的默认行格式为dynamic
compact 格式
记录的额外信息
记录的额外信息：分别是变长字段长度列表、NULL值列表和记录头信息
1：变长字段长度列表
mysql中支持一些变长数据类型（比如VARCHAR(M)、TEXT等），它们存储数据占用的存储空间不是固定的，而是会随着存储内容的变化而变化。
在Compact行格式中，把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位，从而形成一个变长字段长度列表，各变长字段数据占用的字节数按照列的顺序逆序存放
变长字段长度列表中只存储值为 非NULL 的列内容占用的长度，值为 NULL 的列的长度是不储存的 。
并不是所有记录都有这个 变长字段长度列表 部分，比方说表中所有的列都不是变长的数据类型的话，这一部分就不需要有
2：NULL值列表
NULL值列表：Compact格式会把所有可以为NULL的列统一管理起来，存在一个NULL值列表，如果表中没有允许为NULL的列，则NULL值列表也不复存在了。
为什么要有NULL值列表？
表中的某些列可能存储NULL值，如果把这些NULL值都放到记录的真实数据中存储会很浪费空间，所以Compact行格式把这些值为NULL的列统一管理起来，存储到NULL值列表中，它的处理过程是这样的：
首先统计表中允许存储NULL的列有哪些。
根据列的实际值，用0或者1填充NULL值列表，1代表该列的值为空，0代表该列的值不为空。
如果表中没有允许存储 NULL 的列，则 NULL值列表 也不存在了。
3：记录头信息
| 名称 | 大小(单位:bit) | 描述 |
|---|---|---|
| 预留位1 | 1 | 未使用 |
| 预留位2 | 1 | 未使用 |
| delete_mask | 1 | 标记改记录是否被删除 |
| min_rec_mask | 1 | B+树非叶子节点中最小记录都会添加该标记 |
| n_owned | 4 | 当前记录拥有的记录数 |
| heap_no | 13 | 当前记录在记录堆的位置信息 |
| record_type | 3 | 记录类型 0：普通记录/1：B+树非叶子节点记录/2：最小记录/3：最大记录 |
| next_record | 16 | 下一条记录的相对位置 |
redundant 格式
与compact 格式相比, 没有了 变长字段列表以及 NULL值列表, 取而代之的是 记录了所有真实数据的偏移地址表 ，偏移地址表 是倒序排放的, 但是计算偏移量却还是正序开始的从row_id作为第一个, 第一个从0开始累加字段对应的字节数。
在记录头信息中, 大部分字段和compact 中的相同，但是对比compact多了。
n_field(记录列的数量)、1byte_offs_flag(字段长度列表每一列占用的字节数)，少了record_type字段。
因为redundant是mysql 5.0 以前就在使用的一种格式, 已经非常古老, 使用频率非常的低，这里就不过多表述。
dynamic 格式
在现在 mysql 5.7 的版本中,使用的格式就是 dynamic。
dynamic 和 compact 基本是相同的，只有在溢出页的处理上面,有所不同。
在compact行格式中，对于占用存储空间非常大的列，在记录的真实数据处只会存储该列的前768个字节的数据，把剩余的数据分散存储在几个其他的页中，然后记录的真实数据处用20个字节存储指向这些页的地址，从而可以找到剩余数据所在的页。
这种在本记录的真实数据处只会存储该列的前768个字节的数据和一个指向其他页的地址，然后把剩下的数据存放到其他页中的情况就叫做行溢出，存储超出768字节的那些页面也被称为溢出页（uncompresse blob page）。
dynamic中会直接在真实数据区记录 20 字节 的溢出页地址, 而不再去额外记录一部分的数据了。
行溢出临界点
MySQL中规定一个页中至少存放两行记录。
简单理解：因为B+树的特性，如果不存储至少2条记录，则这个B+树是没有意义的，形不成一个有效的索引。
每个页除了存放我们的记录以外，也需要存储一些额外的信息，大概132个字节。
每个记录需要的额外信息是27字节。假设一个列中存储的数据字节数为n，如要要保证该列不发生溢出，则需要满足：132 + 2×(27 + n) y，则max不变，min=mid。依此类推。
举例：我们想找主键值为6的记录，过程是这样的计算中间槽的位置：(0+3)/2=1，所以查看槽1对应记录的主键值为4，因为4 3，所以这就不符合下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值的要求，所以在插入主键值为3的记录的时候需要伴随着一次记录移动，也就是把主键值为4的记录移动到页2中，然后再把主键值为3的记录插入到页1中。
最后形成如图所示。
这个过程叫做页分裂。
真实数据存储中，数据页的编号并不是连续的，当我们在test表中插入多条记录后，可能是这样的效果：
因为这些16KB的页在物理存储上可能并不挨着，所以如果想从这么多页中根据主键值快速定位某些记录所在的页，我们需要给它们做个目录，每个页对应一个目录项，每个目录项由页中记录的最小主键值和页号组成。
我们为上面几个页做目录，则如图：
比方说我们想找主键值为5的记录，具体查找过程分两步：
1：先从目录项中根据二分法快速确定出主键值为5的记录在目录2中（因为 4 < 5 < 7），它对应的数据页是页23。
2：再根据前边说的在页中查找记录的方式去页23中定位具体的记录。
这个目录有一个别名，称为索引。
InnoDB中的索引方案
在InnoDB中复用了之前存储用户记录的数据页来存储目录项，为了和用户记录做一下区分，我们把这些用来表示目录项的记录称为目录项记录。
用record_type来区分普通的用户记录还是目录项记录。
如果表中的数据太多，以至于一个数据页不足以存放所有的目录项记录，会再多整一个存储目录项记录的页。
所以如果此时我们再向上图中插入一条主键值为10的用户记录的话：
在查询时我们需要定位存储目录项记录的页，但是这些页在存储空间中也可能不挨着，如果我们表中的数据非常多则会产生很多存储目录项记录的页，那我们怎么根据主键值快速定位一个存储目录项记录的页呢？
其实也简单，为这些存储目录项记录的页再生成一个更高级的目录，就像是一个多级目录一样，大目录里嵌套小目录，小目录里才是实际的数据，所以现在各个页的示意图就是这样子：
用户记录其实都存放在B+树的最底层的节点上，这些节点也被称为叶子节点或叶节点，其余用来存放目录项的节点称为非叶子节点或者内节点，其中B+树最上边的那个节点也称为根节点。
聚簇索引
我们上边介绍的B+树本身就是一个目录，或者说本身就是一个索引。
它有两个特点：
1：使用记录主键值的大小进行记录和页的排序
2：B+树的叶子节点存储的是完整的用户记录。
我们把具有这两种特性的B+树称为聚簇索引，所有完整的用户记录都存放在这个聚簇索引的叶子节点处。
这种聚簇索引并不需要我们在MySQL语句中显式的使用INDEX语句去创建，InnoDB存储引擎会自动的为我们创建聚簇索引。
另外有趣的一点是，在InnoDB存储引擎中，聚簇索引就是数据的存储方式（所有的用户记录都存储在了叶子节点），也就是所谓的索引即数据，数据即索引。
二级索引
这个B+树与上边介绍的聚簇索引有几处不同：
使用记录a2列的大小进行记录和页的排序
页内的记录是按照a2列的大小顺序排成一个单向链表。
各个存放用户记录的页也是根据页中记录的a2列大小顺序排成一个双向链表。
存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的a2列大小顺序排成一个双向链表。
B+树的叶子节点存储的并不是完整的用户记录，而只是a2列+主键这两个列的值。
目录项记录中不再是主键+页号的搭配，而变成了a2列+页号的搭配。
索引的代价
1：空间上的代价每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会占用16KB的存储空间。
2：时间上的代价每次对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引。
B+树每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表。
不论是叶子节点中的记录，还是内节点中的记录（也就是不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单向链表。
而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位，页面分裂、页面回收等操作来维护好节点和记录的排序。