MySQL是怎样运行的（四）

第4章　从一条记录说起——InnoDB记录存储结构

4.1 准备工作

MySQL服务器上负责对表中数据的读取和写入工作的部分是存储引擎，而服务器又支持不同类型的存储引擎，真实数据在不同的存储引擎中存放的格式一般是不同的

4.2 InnoDB页简介

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

4.3 InnoDB行格式

平时以记录为单位来向表中插入数据，这些记录在磁盘上的存放方式也被称为行格式。有四种不同的行格式，分别为Compact、Redundant、Dynamic和Compressed

4.3.1 指定行格式的语法

创建或修改语句中指定行格式

 4.3.2 COMPACT行格式

 一条完整的记录可以被分为记录的额外信息和记录的真实数据两大部分

记录的额外信息

这部分信息是服务器为了描述这条记录而不得不额外添加的一些信息，这些额外信息分为3类，分别是变长字段长度列表、NULL值列表和记录头信息

变长字段长度列表

MySQL支持一些变长的数据类型，如varchar(M)、varbinary(M)、各种text类型、各种blob类型，我们可以把拥有这些数据类型的列称为变长字段，变长字段中存储多少字节的数据是不固定的，所以在存储真实数据的时候需要顺便把这些数据占用的字节数也存起来，所以这些变长字段占用的存储空间分为两部分：

• 真正的数据内容
• 占用的字节数

在Compact行格式中，把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位，从而形成一个变长字段长度列表，各变长字段数据占用的自己数按照列的顺序逆序存放

W、M和L

1. 假设某个字符集中表示一个字符最多需要使用的字节数为W，也就是使用show charset语句的结果中的Maxlen列，如utf8的字符集中的W就是3，gbk字符集中的W就是2，ASCII字符集中的W是1
2. 对于变长类型varchar(M)来说，这种类型表示能存储最多M个字符（注意是字符不是字节），所以这个类型能表示的字符串最多占用的字节数就是M*W
3. 假设它实际存储的字符串占用的字节数是L

所以确定使用1个字节还是两个字节表示真正字符串占用的字节数的规则

• 如果M*W <= 255，那么使用1个字节来表示真正字符串占用的字节数
• 如果M*W > 255，分为两种情况

      如果L <= 127，则用1个字节来表示真正字符串占用的字节数
      如果L > 127，则用2个字节来表示真正字符串占用的字节数
  
  InnoDB在读记录的变长字段长度列表时先查看表结构，如果某个变长字段允许存储的最大字节数大于255时，该怎么区分它正在读的某个字节是一个单独的字段长度还是半个字段长度呢？使用该字节的第一个二进制位作为标志位：如果该字节的第一个位为0,那该字节就是一个单独的字段长度(使用一个字节表示不大于127的二进制的第一个位都为0)，如果该字节的第一个位为1,那该字节就是半个字段长度。对于一些占用字节数非常多的字段，比方说某个字段长度大于了16KB,那么如果该记录在单个页面中无法存储时， InnoDB会把一部分数据存放到所谓的溢出页中(我们后边会唠)，在变长字段长度列表处只存储留在本页面中的长度，所以使用两个字节也可以存放下来

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

另外，变长字段长度列表中只存储非NULL的列内容占用的长度，值为NULL的列的长度是不储存的

NULL值列表

因为某些列可能存储NULL值，如果把这些NULL值都存放到记录的真实数据中存储会很占地方，所以Compact行格式把这些值为NULL的列统一管理起来，存储到NULL值列表中

• 首先统计表中允许存储NULL的列表
• 如果表中没有允许存储NULL的列，则NULL值列表也不存在了，否则将每个允许存储NULL的列对应一个二进制位，二进制位按照列的顺序逆序排列。二进制位值为1时，代表该列的值为NULL；二进制位值为0时，代表该列的值不为NULL
• MySQL规定NULL值列表必须使用整数个字节的位表示，二进制位个数不是整数个字节，在字节高位补0
• 如果一个表中有9个允许为NULL，那这个记录的NULL值列表部分就需要2个字节表示了

记录头信息（略）

由固定的5个字节组成，也就是40个二进制位，不同位代表不同意义

 记录的真实数据

记录真实数据除了自己定义的列的数据以外，还会为每个记录默认添加一些列（也称为隐藏列）

这里需要提一下InonoDB表对主键的生成策:优先使用用户自定义主键作为主键，如果用户没有定义主键，则选取一个Unque键作为主键，如果表中连 Unique键都没有定义的话，则 InnoDB会为表默认添加一个名为row_id的隐藏列作为主键。所以我们从上表中可以看出InnoDB存储引擎会为每条记录都添加 transactiohd和roll_pointer这两个列，但是row_id是可选的(在没有自定义主键以及 Unique键的情况下才会添加该列)。这些隐列的值不用我们操心， InnoDB存储引会自己帮我们生成的。

char(M)列的存储格式

因为我们的 demo表采用的是ASCII宇符集，这个宇符集是一个定长字符集，也就是说表示一个字符采用固定的一个字节，如果采用变长的字符集(也就是表示一个字需要的字节数不确定，比如gbk表示一个宇符要1~2个字节、utf8表示一个字符要1~3个字节等)的话，列的长度也会被存到变长字段长度列表中

变长字符集的char(M)类型的列要求至少占用M个字节

4.3.3 REDUNDANT行格式

是MySQL5.0之前使用的一种行格式

字段长度偏移列表

Redundant行格式的开头是字段长度偏移列表，把该条记录中所有列（包括隐藏列）的长度信息都按照逆序存储到字段长度偏移列；偏移，采用 两个相邻数值的差值来计算各个列值的长度

记录头信息

 Redundant行格式中NULL值处理

Redundant行格式没有NULL值列表

char(M)列的存储格式

不管该列使用什么字符集，占用的真实数据空间就是该字符集表示一个字符最多需要的字节数和M的乘积

4.3.4 溢出列

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

varchar(M)类型列最多可以占用65535个字节，其中M表示最多存储的字符数量

MySQL对一条记录占用的最大存储空间是有限制的，除了blob或text类型之外，其他所有列（不包括隐藏列和记录头信息）占用的字节长度加起来不超过65535个字节，如存储一个varchar类型列，其实需要占用3部分存储空间：

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

在列值允许为NULL情况下，gbk字符集下的M最大取值就是32766（65532/2），utf8字符集下M最大取值是21844（65532/3）个字符，这仅是在表只有varchar(M)这一个字段情况下的。一行所有列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过65535个字节

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

MySQL中磁盘和内存交互的基本单位是页，也就是说MySQL是以页为基本单位来管理存储空间的，我们的记录都会被分配到某个页中存储。而一个页的大小一般是16KB，也就是16384（16*1024）字节，而一个varchar类型最多可以存储65532个字节，就可能造成一个页放不下一条记录的情况

Compact和Redundant行格式来说，对于占用存储空间非常大的列，在记录的真实数据处只会存储该列的一部分数据，把剩下的数据分散存储在几个其他的页中，然后记录的真实数据处用20个字节存储指向这些页的地址（这20个字节还包括这些分散在其他页面中的数据的占用的字节数），从而找到剩余的数据所在的页

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

不只是varchar类型列，其他的text、blob类型的列在存储数据非常多的时候也会发生行溢出

行溢出的临界点

 MySQL规定一个页中至少存放两行记录

• 每页除了存放我们的记录外，也需要存储一些额外的信息，加起来需要132个字符空间，其他空间都可以被用来存储记录
• 每个记录需要额外信息是27字节
  
  2个字节用于存储 真实数据的长度
  1个字节用于存储列是否是NULL值
  5个字节大小的头信息
  6个字节的row_id列
  6个字节的transaction_id列
  7个字节的roll_pointer列

132+2*（27+n）< 16384 即 n<8099。也就是说如果一个列中存储的数据小于8099个字节，那么该列就不会成为溢出列。不过这个8099个字符结论只针对只有一个列的表，所以：不需要关注这个临界点是什么，只要知道如果一条记录的某个列存储的数据占用的字节数非常多时，就可能成为溢出列

4.3.5 DYNAMIC行格式和COMPRESSED行格式

和Compact相似，只是在处理行溢出数据时有不同，它们不会在记录的真实数据处储存真实数据的前768个字节，而是把所有的字节都存储到其他页面中，只记录其他页面的地址

Compressed行格式和Dynamic不同一点就是，Compressed行格式会采用压缩算法对页面进行压缩，以节省空间