[ MySql ] InnoDB存储结构

InnoDB 将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为 16 KB。也就是在一般情况下，一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中。

   MySql关系型数据库将记录以行格式的方式存储在磁盘上

可以在创建表或是修改表时改变当前表的行格式

CREATE TABLE(...) ROW_FORMAT=行格式名称;
ALTER TABLE 表名 ROW_FORMAT=行格式名称;

查看当前表的行格式

Compact 

CREATE TABLE record_format_demo (
    ->     c1 VARCHAR(10),
    ->     c2 VARCHAR(10) NOT NULL,
    ->     c3 CHAR(10),
    ->     c4 VARCHAR(10)
    -> ) CHARSET=ascii ROW_FORMAT=COMPACT;

 上述sql创建表的行格式就是Compact，显式指定了这个表的字符集为ascii，因为ascii字符集只包括空格、标点符号、数字、大小写字母和一些不可见字符（不可以插入汉字）补充两条记录：

INSERT INTO record_format_demo(c1, c2, c3, c4) VALUES('aaaa', 'bbb', 'cc', 'd'), ('eeee', 'fff', NULL, NULL);

 

变长字段长度列表: 

Compact 行格式将记录分成额外记录和真实数据两部分，针对一些变成字段，如varchar varbinary text blob 等，其存储的数据长度是不固定的，所以有必要将变成字段长度列表记录下来，InnoDB按照列的逆序，将各变长字段真实数据所占用的数据长度记录下来。

对于表中的第一条数据, 可变字段有c1,c2,c4,其长度分别为4，3，1，同时按照列的逆序，第一条记录行格式 变长字段长度列表为： 040302。 这里保存的是列数据的真实存储字节数,测试表是ascii字符集，所以一个字节就可以表示，当使用别的字符集时，如何知道变长字段长度是多少字节呢?

您首先需要知道这几点:

1. 假设某个字符集中表示一个字符最多需要使用的字节数为W，也就是使用SHOW CHARSET语句的结果中的Maxlen列，比方说utf8nm4字符集中的W就是4,utf8字符集中的W就是3，gbk字符集中的W就是2，ascii字符集中的W就是1。

2. 对于变长类型VARCHAR(M)来说，这种类型表示能存储最多M个字符（注意是字符不是字节），所以这个类型能表示的字符串最多占用的字节数就是M×W。

3. 假设实际存储的字符串占用的字节数是L。

所以确定使用1个字节还是2个字节表示真正字符串占用的字节数的规则就是这样：

• 如果M×W <= 255，那么使用1个字节来表示真正字符串占用的字节数。

  也就是说InnoDB在读记录的变长字段长度列表时先查看表结构，如果某个变长字段允许存储的最大字节数不大于255时，可以认为只使用1个字节来表示真正字符串占用的字节数。

• 如果M×W > 255，则分为两种情况：

  • 如果L <= 127，则用1个字节来表示真正字符串占用的字节数。

  • 如果L > 127，则用2个字节来表示真正字符串占用的字节数。

如果该可变字段允许存储的最大字节数（M×W）超过255字节并且真实存储的字节数（L）超过127字节，则使用2个字节，否则使用1个字节。

另外需要注意的一点是，变长字段长度列表中只存储值为 非NULL 的列内容占用的长度，第二条记录，因为c4列的值为NULL，所以第二条记录的变长字段长度列表只需要存储c1和c2列的长度即可。其中c1列存储的值为'eeee'，占用的字节数为4，c2列存储的值为'fff'，占用的字节数为3。数字4可以用1个字节表示，3也可以用1个字节表示，所以整个变长字段长度列表共需2个字节。填充完变长字段长度列表的两条记录的对比图如下：

如果某一列中没有变长字段，那么这一额外信息将不需要保存 .

NULL值列表

表中总有些字段时Null的，InnoDB将这些信息不是放在记录真实数据中，而是放在额外信息中。

1.除了主键，非Null列以外

2.如果表中没有允许存储 NULL 的列，则 NULL值列表 也不存在了，否则将每个允许存储NULL的列对应一个二进制位，二进制位按照列的顺序逆序排列，二进制位表示的意义如下：

• 二进制位的值为1时，代表该列的值为NULL。
• 二进制位的值为0时，代表该列的值不为NULL。

因为表record_format_demo有3个值允许为NULL的列，所以这3个列和二进制位的对应关系就是这样

3.MySql规定Null值列表必须以整数个字节来表示，所以，我们测试表的允许Null值的字段只有三个,将高位补5个零变成一个字节,要是有9个你也得补7个0，用两个字节来表示.

对于表中两条数据:

第一条记录对应的Null值列表为: 00000000  ,16进制为0x00;第二条记录对应的Null值列表为: 00000110,16进制为0x06  ;

记录头信息 

最后一个额外信息时记录头信息，固定为5个字节，也就是40个二进制位来表示，

 记录的真实数据

MySql的隐藏列和我们自己定义的列都存储在这部分

DB_ROW_ID  , DB_TRX_ID,DB_ROLL_PTR  ,row_id只有在表没有添加主键，甚至没有Unique键的情况下才会添加，其他两个列是默认会生成的。

注意第一条记录c3的值由于是定长类型char(10),所以需要补全0，ascii中0为0x20,第二条数据c3,c4是null,已经保存在Null值列表中了。

列c3使用的字符集是ascii,是一个定长字符集，也就是说表示一个字符采用固定的一个字节，如果采用变长的字符集（也就是表示一个字符需要的字节数不确定，比如gbk表示一个字符要1~2个字节、utf8表示一个字符要1~3个字节等，utf8bm4 1-4个字节）的话，定长字段c3列的长度也会被存储到变长字段长度列表中，比如我们修改record_format_demo表的字符集为utf8：

    对于 CHAR(M) 类型的列来说，当列采用的是定长字符集时，该列占用的字节数不会被加到变长字段长度列表，而如果采用变长字符集时，该列占用的字节数也会被加到变长字段长度列表。

另外有一点还需要注意，变长字符集的CHAR(M)类型的列要求至少占用M个字节，而VARCHAR(M)却没有这个要求。比方说对于使用utf8字符集的CHAR(10)的列来说，该列存储的数据字节长度的范围是10～30个字节。即使我们向该列中存储一个空字符串也会占用10个字节，这是怕将来更新该列的值的字节长度大于原有值的字节长度而小于10个字节时，可以在该记录处直接更新，而不是在存储空间中重新分配一个新的记录空间，导致原有的记录空间成为所谓的碎片。（Compact行格式的既想节省存储空间，又不想更新CHAR(M)类型的列产生碎片。）

 

Redundant行格式

5.0之前的行格式。

行溢出数据

VARCHAR(M)最多能存储的数据

对于VARCHAR(M)类型的列最多可以占用65535个字节。其中的M代表该类型最多存储的字符数量，如果我们使用ascii字符集的话，一个字符就代表一个字节，我们看看VARCHAR(65535)是否可用： 

    MySQL对一条记录占用的最大存储空间是有限制的，除了BLOB或者TEXT类型的列之外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过65535个字节。所以MySQL服务器建议我们把存储类型改为TEXT或者BLOB的类型。这个65535个字节除了列本身的数据之外，还包括一些其他的数据（storage overhead），比如说我们为了存储一个VARCHAR(M)类型的列，其实需要占用3部分存储空间：

• 真实数据
• 真实数据占用字节的长度
• NULL值标识，如果该列有NOT NULL属性则可以没有这部分存储空间

如果该VARCHAR类型的列没有NOT NULL属性，那最多只能存储65532个字节的数据，因为真实数据的长度可能占用2个字节，NULL值标识需要占用1个字节：

mysql> CREATE TABLE varchar_size_demo(
    ->      c VARCHAR(65532)
    -> ) CHARSET=ascii ROW_FORMAT=Compact;
Query OK, 0 rows affected (0.02 sec)

如果VARCHAR类型的列有NOT NULL属性，那最多只能存储65533个字节的数据，因为真实数据的长度可能占用2个字节，不需要NULL值标识：

mysql> DROP TABLE varchar_size_demo;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE varchar_size_demo(
    ->      c VARCHAR(65533) NOT NULL
    -> ) CHARSET=ascii ROW_FORMAT=Compact;
Query OK, 0 rows affected (0.02 sec)

如果VARCHAR(M)类型的列使用的不是ascii字符集，那会怎么样呢？来看一下：

mysql> DROP TABLE varchar_size_demo;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE varchar_size_demo(
    ->       c VARCHAR(65532)
    -> ) CHARSET=gbk ROW_FORMAT=Compact;
ERROR 1074 (42000): Column length too big for column 'c' (max = 32767); use BLOB or TEXT instead

mysql> CREATE TABLE varchar_size_demo(
    ->       c VARCHAR(65532)
    -> ) CHARSET=utf8 ROW_FORMAT=Compact;
ERROR 1074 (42000): Column length too big for column 'c' (max = 21845); use BLOB or TEXT instead

从执行结果中可以看出，如果VARCHAR(M)类型的列使用的不是ascii字符集，那M的最大取值取决于该字符集表示一个字符最多需要的字节数。在列的值允许为NULL的情况下，gbk字符集表示一个字符最多需要2个字节，那在该字符集下，M的最大取值就是32766（也就是：65532/2），也就是说最多能存储32766个字符；utf8字符集表示一个字符最多需要3个字节，那在该字符集下，M的最大取值就是21844，就是说最多能存储21844（也就是：65532/3）个字符。(这是一个字段的情况下，一个行中的所有列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过65535个字节！)

记录中的数据太多产生的溢出

以ascii字符集下的varchar_size_demo表为例，插入一条记录：

mysql> CREATE TABLE varchar_size_demo(
    ->       c VARCHAR(65532)
    -> ) CHARSET=ascii ROW_FORMAT=Compact;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO varchar_size_demo(c) VALUES(REPEAT('a', 65532));
Query OK, 1 row affected (0.00 sec)

其中的REPEAT('a', 65532)是一个函数调用，它表示生成一个把字符'a'重复65532次的字符串。前边说过，MySQL中磁盘和内存交互的基本单位是页，也就是说MySQL是以页为基本单位来管理存储空间的，我们的记录都会被分配到某个页中存储。而一个页的大小一般是16KB，也就是16384字节，而一个VARCHAR(M)类型的列就最多可以存储65532个字节，这样就可能造成一个页存放不了一条记录的尴尬情况。

对于Compact和Reduntant行格式来说，如果某一列中的数据非常多的话，在本记录的真实数据处只会存储该列的前768个字节的数据和一个指向其他页的地址，然后把剩下的数据存放到其他页中，这个过程也叫做行溢出，存储超出768字节的那些页面也被称为溢出页

 

行溢出的临界点

行溢出的临界点指的是 在列存储多少字节的数据时就会发生行溢出？

MySQL中规定一个页中至少存放两行记录.这个规定造成的影响。以上边的varchar_size_demo表为例，它只有一个列c，我们往这个表中插入两条记录，每条记录最少插入多少字节的数据才会行溢出的现象呢？这得分析一下页中的空间都是如何利用的。

• 每个页除了存放我们的记录以外，也需要存储一些额外的信息，乱七八糟的额外信息加起来需要132个字节的空间（现在只要知道这个数字就好了），其他的空间都可以被用来存储记录。

• 每个记录需要的额外信息是27字节。

  这27个字节包括下边这些部分：

  • 2个字节用于存储真实数据的长度
  • 1个字节用于存储列是否是NULL值
  • 5个字节大小的头信息
  • 6个字节的row_id列
  • 6个字节的transaction_id列
  • 7个字节的roll_pointer列

假设一个列中存储的数据字节数为n，设计MySQL的大叔规定如果该列不发生溢出的现象，就需要满足下边这个式子：

132 + 2×(27 + n) < 16384

求解这个式子得出的解是：n < 8099。也就是说如果一个列中存储的数据小于8099个字节，那么该列就不会成为溢出列，否则该列就需要成为溢出列。不过这个8099个字节的结论只是针对只有一个列的varchar_size_demo表来说的，如果表中有多个列，那上边的式子和结论都需要改一改了，所以重点就是：你不用关注这个临界点是什么，只要知道如果我们一条记录的某个列中存储的数据占用的字节数非常多时，该列就可能成为溢出列。

Dynamic和Compressed行格式

下边要介绍另外两个行格式，Dynamic和Compressed行格式，MySQL5.7的默认行格式就是Dynamic，这俩行格式和Compact行格式挺像，只不过在处理行溢出数据时有点儿分歧，它们不会在记录的真实数据处存储字段真实数据的前768个字节，而是把所有的字节都存储到其他页面中，只在记录的真实数据处存储其他页面的地址，就像这样

两者不同之处在于Compressed会对页进行压缩以节省空间.