目录
MySQL服务器负责对表中数据的读取和写入工作的部分是存储引擎，真实数据在不同存储引擎中存放的格式一般是不同的。InnoDB是MySQL默认的存储引擎，主要了解它的数据存储结构即可。
4.1 InnoDB页简介
InnoDB是一个将表中的数据存储到磁盘上的存储引擎，所以即使关机后重启数据还是存在的。而真正处理数据的过程是发生在内存中的，所以需要把磁盘中的数据加载到内存中，如果是写入或修改请求的话，还需要把内存中处理后的内容刷新到磁盘上。
问题：磁盘读写速度相较于内存非常慢，需要提高获取某些记录的效率。
解决：
InnoDB采用将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为16KB
即一次最少从磁盘中读取16KB内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中。
4.2 InnoDB行格式
平时是以记录为单位来向表中插入数据的，这些记录在磁盘上的存放方式也被称为 行格式 或者 记录格式。InnoDB有4种不同类型的行格式，分别是Compact、Redundant、Dynamic 和 Compressed行格式。
4.2.1 指定行格式的语法
创建或者修改表的语句中指定 行格式：
CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称
ALTER TABLE 表名 ROW_FORMAT=行格式名称
插入两条数据：
下面看下各个行格式下的存储方式的差异。
4.2.2 COMPACT行格式
一条完整的记录其实可以被分为 记录的额外信息 和 记录的真实数据 两大部分。
4.2.2.1 记录的额外信息
是服务器为了描述这条记录不得不额外添加的一些信息，包含了变长字段长度列表、NULL值列表、记录头信息。
-
变长字段长度列表
MySQL支持一些变长的数据类型，比如VARCHAR(M)、VARBINARY(M)、各种TEXT类型，各种BLOB类型，可以把拥有这些数据类型的列称为变长字段。这些变长字段占用的存储空间分为两部分：
- 真正的数据内容
- 占用的字节数
在Compact行格式中，把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位，从而形成一个变长字段长度列表，各变长字段数据占用的字节数按照列的顺序逆序存放。
以上述两条记录为例，因为是ascii字符集，每个字符只需要1个字节来进行编码，所以第一条记录：
占用的字节数如果比较多，可能需要2个字节来表示这个数，具体用1还是2个字节来表示规则如下：
W：字符集中表示一个字符最多需要使用的字节数；
M：这个字段最多能存储的字符数，所以最多占用的字节数就是M×W；
L：这条记录这个字段的字符串实际占用的字节数；
规则：
- 若M×W ≤ 255，则使用1个字节来表示真正字符串占用的字节数；
- 若M×W > 255，
- 若L ≤ 127，则用1个字节；
- 若L > 127，则用2个字节；
总结就是：若该可变字段允许存储的最大字节数( M×W ) 超过255字节并且真实存储的字节数（L）超过127字节，则使用2个字节，否则使用1个字节。 另外：变长字段长度列表中只存储值为非null的列内容占用的长度，值为null的列的长度是不存储的。
并不是所有记录都有这个 变长字段长度列表 部分，如果表中所有的列都不是变长的数据类型的话这一部分就不需要有。
-
NULL值列表
表中某些列可能存储NULL值，若把这些null值都放到 记录的真实数据 中存储会很占地方，Compact行格式把这些值为NULL的列统一管理起来，存储到NULL值列表中，过程如下：
-
首先统计表中允许存储NULL的列有哪些。
主键列、被NOT NULL修饰的列都是不可以存储NULL值的，所以统计的时候不会算进去。
-
若表中没有允许存储NULL的列，则NULL值列表也就不存在了。否则将每个存储NULL的列对应一个二进制位，二进制位按照顺序逆序排列，值为1表示该列值为NULL，为0表示不为NULL。
record_format_demo表中有3个允许为NULL的列，对应的NULL值列表如下：
-
MySQL规定NULL值列表必须用整数个字节的位表示，若使用的二进制位的个数不是整数个字节，则在高位补0，上述record_format_demo表中效果如下：
若有9个允许为NULL，则这个记录的NULL值列表部分就需要2个字节来表示。
对上述第一条记录来说：
第二条记录：
所以两条记录的NULL值列表如下：
-
-
记录头信息
由固定的5个字节组成，40个位，不同位代表不同的意思：
这些二进制位代表的详细信息如下：
名称 大小（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 表示下一条记录的相对位置 目前先混个脸熟，后续等用到的时候再回头看。
4.2.2.2 记录的真实数据
对于record_format_demo表来说，记录的真实数据除了c1、c2、c3、c4这几个自己定义的列数据外，MySQL会为每个记录默认添加一些列（也称为隐藏列），具体如下：
| 列名 | 是否必须 | 占用空间 | 描述 |
|---|---|---|---|
| DB_ROW_ID | 否 | 6字节 | 行ID，唯一标识一条记录 |
| DB_TRX_ID | 是 | 6字节 | 事务ID |
| DB_ROLL_PTR | 是 | 7字节 | 回滚指针 |
**InnoDB表对主键的生成策略：**优先使用用户自定义主键作为主键，若用户没有定义主键，则选取一个Unique键作为主键，若也没有，则InnoDB会为表默认添加一个名为DB_ROW_ID的隐藏列作为主键。而另外两个是一定会生成的。上述record_format_demo的记录如下：
注意点：
- 表使用的是ascii字符集，所以0x61616161 就表示字符串 ‘aaaa’ ， 0x626262 就表示字符串 ‘bbb’ ，以此类推。
- c3列是char(10)类型的，虽然只占用2个字节，但是仍占用了10个字节的空间，除真实数据以外的8哥字节都用空格字符填充。
- 第二条记录中的c3和c4列的值都为NULL，被存储在了NULL值列表中，不再冗余存储，节省空间。
4.2.2.3 CHAR(M)列的存储格式
record_format_demo表的c3列是CHAR(10)，在Compact行格式下只会把变长类型的列的长度逆序存到 变长字段长度列表 中，如下：
但是这只是因为采用了ascii字符集，这是一个定长字符集，即一个字符采用固定的一个字节，若采用变长的字符集（即一个字符需要的字节数不确定，如gbk用1-2个字节，utf8用1-3个字节），c3列的长度也会被存储到变长字段长度列表中，如下：
即：对于CHAR(M)类型的列来说，当列采用的是定长字符集时，该列占用的字节数不会被加到变长字段长度列表，而如果采用变长字符集时，该列占用的字节数也会被加到变长字段长度列表。
4.2.3 Redundant行格式
是MySQL5.0之前的一种行格式，已经比较老了。
与Compact行格式的区别：
-
字段长度偏移列表
会把该条记录中所有列（包括隐藏列）的长度信息都按照逆序存储到 字段长度偏移列表 中。它采用两个相邻数值的差值来计算各个列值的长度。
-
记录头信息
占用6字节，48个二进制位，代表的意思如下：
名称 大小（bit） 描述 预留位1 1 没有使用 预留位2 1 没有使用 delete_flag 1 标识该记录是否被删除 min_rec_flag 1 B+树的每层非叶子节点中的最小的目录项记录都会添加该标记 n_owned 4 表示当前记录拥有的记录数 heap_no 13 表示当前记录在记录堆的位置信息 n_field 10 表示记录中列的数量 1byte_offs_flag 1 标识字段长度偏移列表中每个列对应的偏移量是使用1字节还是2字节表示的 next_record 16 表示下一条记录的相对位置
字段长度偏移列表 实质上是存储每个列中的值占用的空间在 记录的真实数据 处结束的位置，拿record_format_demo第一条记录为例，0x06代表第一个列在记录的真实数据第6哥字节处结束，0x0C表示第二个列在记录的真实数据第12个字节处结束。。。。最后一列对应的偏移量值为0x25，意味着最后一列在记录的真实数据第37个字节处结束，意味着整条记录的真实数据实际上占用了37个字节。
每列的偏移量可以占用1个字节或2个字节来存储：（1byte_offs_flag）
- 当记录的真实数据占用的字节数不大于127时，每个列对应的偏移量占用1个字节；
- 当记录的真实数据占用的字节数大于127，但不大于32767时，每个列对应的偏移量占用2个字节。
若真实数据大于32767，则视为溢出，在本页中只保留前768个字节和20个字节的溢出页面地址。因为字段长度偏移列表只需要记录每个列在本页面中的偏移量就好了，所以每个列使用2个字节来存储偏移量就够了。
Redundant行格式中NULL值的处理：没有NULL值列表，所以将列对应的偏移量值的第一个比特位作为是否为NULL的依据，该位也可以被称之为NULL比特位。
CHAR(M)列的存储格式：不管该列使用的字符集是啥，只要是使用 CHAR(M) 类型，占用的真实数据空间就是该字符集表示一个字符最多需要的字节数和 M 的乘积。
4.2.4 行溢出数据
4.2.4.1 VARCHAR(M)最多能存储的数据
对于 VARCHAR(M) 类型的列最多可以占用 65535 个字节，其中的 M 代表该类型最多存储的字符数量。使用ascii字符集看下VARCHAR(65535)是否可用：
从报错信息中可以看出，MySQL对一条记录占用的最大存储空间是有限制的，除了BLOB或者TEXT类型的列之外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过65535个字节。这个65535个字节除了列本身的数据之外，还包括一些其他的数据，我们为了存储一个VARCHAR(M)类型的列，其实需要3部分存储空间：
- 真实数据；
- 真实数据占用字节的长度；
- NULL值标识，如果该列有NOT NULL属性则可以没有这部分存储空间；
若该VARCHAR类型的列没有NOT NULL属性，那最多只能存储65532个字节的数据，因为真实数据的长度可能占用2个字节，NULL值标识需要占用1个字节：
若有NOT NULL属性，那最多只能存储65533个字节的数据。
若VARCHAR(M)类型的列使用的不是ascii字符集：
M 的最大取值取决于该字符集表示一个字符最多需要的字节数。在列的值允许为 NULL 的情况下， gbk 字符集表示一个字符最多需要 2 个字节，那在该字符集下， M 的最大取值就是 32766 （也就是：65532/2），也就是说最多能存储 32766 个字符；utf8 字符集表示一个字符最多需要 3 个字节，那在该字符集下， M 的最大取值就是 21844 ，就是说最多能存储 21844 （也就是：65532/3）个字符。
📌上述所言在列的值允许为NULL的情况下，gbk字符集下M的最大取值就是32766，utf8字符集下M的最大取值就是21844，这都是在表中只有一个字段的情况下说的，一定要记住一个行中的所有列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过65535个字节！
4.2.4.2 记录中的数据太多产生的溢出
其中的 REPEAT(‘a’, 65532) 是一个函数调用，它表示生成一个把字符 ‘a’ 重复 65532 次的字符串。
前边说过， MySQL 中磁盘和内存交互的基本单位是 页 ，也就是说 MySQL 是以 页 为基本单位来管理存储空间的，我们的记录都会被分配到某个 页 中存储。而一个页的大小一般是 16KB ，也就是 16384 字节，而一个 VARCHAR(M) 类型的列就最多可以存储 65532 个字节，这样就可能造成一个页存放不了一条记录的尴尬情况。
在Compact和Redundant行格式中，对于占用存储空间非常大的列，在记录的真实数据处只会存储该列的一部分数据，把剩余的数据分散存储在几个其他的页中，然后记录的真实数据处用20个字节存储指向这些页的地址（20个字节中还包括这些分散在其他页面中的数据的占用字节数），从而可以找到剩余数据所在页，如图所示：
不只是VARCHAR(M)列，其他的TEXT、BLOB类型的列在存储数据非常多的时候也会发生 行溢出。
4.2.4.3 行溢出的临界点
列在存储多少字节的数据时会发生行溢出？
MySQL规定一个页中至少存放两行记录。页中的空间都是如何利用的？
- 每个页除了存放真实记录外，还需要存储一些额外的信息，乱七八糟的额外信息加起来需要136个字节的空间，其他空间都可以被用来存储记录。
- 每个记录需要的额外空间是27字节：
- 2个字节用于存储真实数据的长度
- 1个字节用于存储列是否是NULL值
- 5个字节大小的头信息
- 6个字节的 row_id 列
- 6个字节的 transaction_id 列
- 7个字节的 roll_pointer 列
假设一个列中存储的数据字节数为n，那么varchar_size_demo 表插入两条记录会发生行溢出现象时需要满足这个式子：
136 + 2×(27 + n) > 16384，得到 n > 8098。
重点就是：你不用关注这个临界点是什么，只要知道如果我们想一个行中存储了很大的数据时，可能发生 行溢出 的现象。
4.2.5 Dynamic和Compressed行格式
这俩行格式和 Compact 行格式挺像，只不过在处理 行溢出 数据时有点儿分歧，它们不会在记录的真实数据处存储字段真实数据的前 768 个字节，而是把所有的字节都存储到其他页面中，只在记录的真实数据处存储其他页面的地址，就像这样：
Compressed 行格式和 Dynamic 不同的一点是， Compressed 行格式会采用压缩算法对页面进行压缩，以节省空间。
扫一扫
1194
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
