跟孙哥学java
首先，我们清楚MySQL服务器上负责对数据的读取和写入的工作都是由存储引擎来完成的。 不同的存储引擎实现不同的特性，真实数据在不同存储引擎中存放的格式⼀般是不同的。 常见的存储引擎有InnoDB，MyISAM，Memory。其中默认的存储引擎就是InnoBD，所以本文主要介绍InnoDB的数据存储的结构。
InnoDB页
InnoDB是⼀个将表中的数据存储到磁盘上的存储引擎，所以即使关机后重启我们的数据还是存在的。⽽真正处理数据的过程是发⽣在内存中的，所以需要把磁盘中的数据加载到内存中，如果是处理写⼊或修改请求的话，还需要把内存中的内容刷新到磁盘上。
但是，读写磁盘的速度很慢，跟内存的读写差距很大。如果我们存储引擎把数据一条条从磁盘读取，一条条的更新，这个操作将会非常的慢，所以InnoDB采取的方法是：
将数据分成多个也，每页存取多多行数据，以页作为磁盘与内存交互的基本单位。InnoDB一页大小一般为16KB.也就是一次最少从磁盘读取16KB到内存，一次最少跟新16KB数据到磁盘
InnoDB行格式
我们平时记录数据是以行为单位向表中插入数据，这些记录在磁盘上的存放⽅式也被称为⾏格式或者记录格式。设计InnoDB存储引擎有4种不同类型的⾏格式，分别是Compact、Redundant、Dynamic和Compressed⾏格式
指定行格式的语法
在创建表/修改表可以指定行的格式。
CREATE TABLE 表名 (列的信息) ROW_FORMAT=⾏格式名称
ALTER TABLE 表名 ROW_FORMAT=⾏格式名称
-如下
create table mytable(
id int,
name varchar(10)
) row_format=Compact;
创建一个表的行格式为Compact
alter table mytable row_format=Redundant;
修改表的行格式为Redundant
CREATE TABLE table1 (
c1 VARCHAR(10),
c2 VARCHAR(10) NOT NULL,
c3 CHAR(10),
c4 VARCHAR(10)
) CHARSET=ascii ROW_FORMAT=COMPACT;
INSERT INTO table1(c1, c2, c3,
c4) VALUES('aaaa', 'bbb', 'cc', 'd'), ('eeee',
'fff', NULL, NULL);
COMPACT行格式
变长字段列表
对于table1
c1、c2、c4列都是VARCHAR(10)类型的，也就是变⻓的数据类型，所以这三个列的值的⻓度都需要保存
在记录开头处,因为是ASCII字符集，所以每个字符需要一个字节来编码
所以这行数据对应的变长字段列表应该为
010304
因为是按照列的逆序存放，c4中d占一个字节为01，c3为char(10)定长类型，不需要存放，c2 bbb为三个字节，所以为03，c1为 aaaa是四个字节，所以为04
上述一行数据存存储的字符串的字节数都较小，一个字节就可以用来表示（0-255）
如果一个变长字段的varchar(m) m>255时，一个字节就表示不了了。
让我们看看InnoDB是怎么解决的这个问题
:::info
InnoDB有一套专门解决这个问题的规则
我们先声明 W,M,L的意思
W：一个字符最多使用的字节数，ASCII为1，gbk为2，UTF-8为3
M：表示varchar（M），一个字符最多存储的字符数（不是字节哦）
L：变长字段实际存储的字节数
第一种情况：
当WM<=255,这个时候，一个字节就可以表示字符串占用的字节数，也就是在创建表的时候，如果指定变长字符最大的存储字节数不大于255，那么就用一个字节来表示字符占用的字节数
第二种情况：
如果MW>255
这里又可以分用一个字节来表示，或用两个字节来表示
如果L<127 用一个字节来表示
如果L>127 用两个字节来表示
因为1个字节有8位，其中后面7位用来存储占用的字节数，第一位用来判断表示这个字符占用的字节数用一个字节来表示还是两个子节来表示-->如果第一个位是0，那么就表示该字节就是一个单独的字段长度，如果第一位为1，那么就是表示，该字节+后一字节才表示该字段所占的字节数。
:::
需要注意的一点是：
变长字段列表不会存储为NULL的列的长度，因为是否为NULL会由记录额外信息中的NULL值列表来表示
上面两条信息的变长字段列表为：
01 03 04
03 04
第二行中的c4 为null的长度信息不会记录在变长字段列表中
NULL值列表
1.允许存储NULL的列表有：
主键列、被NOT NULL修饰的列都是不可以存储NULL值的
所以table1中 c2是not null，所以它的信息不会存储到null值列表
2.如果表中没有允许的null列，那么null值列表也不存在了，如果存在允许null列，就会由一个二进制位按照列的顺序逆序排列
- 二进制的值为1，代表该列为null
- 二进制的值为0，代表该列不为null
对于这两条信息，它们的null值列表为
0000 0000
0000 0110
因为null值列表必须用整数个字节的位来表示--> 只有三位列允许null值，所以一个字节中，前5 位都进行补0。如果一个表中由9个字段允许为null值，那么记录null值列表就需要2个字节来表示
记录头信息
除了变⻓字段⻓度列表、NULL值列表之外，还有⼀个⽤于描述记录 的记录头信息，它是由固定的5个字节组成。
记录真实的数据
对于我们table1中除了字段c1,c2,c3,c4，MYSQL会为每个记录默认添加一些列。 主要有
| 列名 | 是否必须 | 占用空间 | 描述 |
|---|---|---|---|
| row_id | 否 | 6个字节 | ⾏ID，唯⼀标识⼀条记录，做主键 |
| transaction_id | 是 | 6个字节 | 事务ID |
| roll_poninter | 是 | 7个字节 | 回滚指针 |
在mysql中，这⼏个列的真正名称其实是：DB_ROW_ID、DB_TRX_ID、DB_ROLL_PTR，我们为了美观才写成了row_id、transaction_id和roll_pointer。
:::info
InnoDB的主键生成策略优先级：
用户自定义主键>第一个Unique字段>row_id
如果用户定义了主键或定义了Unique键，就不会生成row_id这一列
InnoDB存储引擎会为每条记录都添加 transaction_id 和roll_pointer 这两个列，但是 row_id 是可选的（在没有⾃定义主键以及Unique键的情况下才会添加该列）
:::
我们创建的table1 使用ASCII字符集，所以
1.0x61616161 就表示 'aaaa' 0x626262 就表示'bbb' 0x64'就是表示'd'
2.其中c3是char(10)类型的，它实际存储的字符是'cc' 应该占两个字节为'ox6363'
但是c3列仍然占用了10个字节的空间，也就是剩下8个字节的空间都是0x20，也就是空格字符填充的
3.第二条记录c3和c4是null值，它们并没有存储在真实数据中，而是存储在前面null值列表了，从而节省了内存空间。
CHAR(M）列的存储格式
对于table1而言，c1,c2,c4都是变长字段，所以它们的长度会逆序的存在字段长度列表中
也就是这样的”
但是这只是因为我们存储的数据的字符集为ASCII，每个字符都是由一个字节来表示，如果采用变长的字符集，一个字符需要的字节数就不确定了，比如gbk占1-2个字节，utf-8占1-3个字节
如果我们修改table1的c3的字符集为变长字符集的话，c3列长度也会存储在变长字段长度列表中
ALTER TABLE table1 MODIFY
COLUMN c3 CHAR(10) CHARACTER SET utf8;
修改后
这意味着：对于CHAR(M）类型的列来说，当列采用的定长字符集时，该列占用的字节数不会加入到变长字段长度列表汇总，而采用变长字符集的时候，该列占用的字节数会加入到变长字符集列表中。
对于utf-8字符集编码的char（10）来说，该字段存储的数据占字节长度的范围是10-30，就是存储一个空的字符串也会占10个字节。
Redundant行格式
这个格式在MySQL 5.0之前用的一种
和Compact的主要区别是记录的额外信息，它只有字段长度偏移列表，没有NULL值列表和字段长度列表，这意味着，所有列的长度信息都会按照逆序存储在字段长度偏移列表中。
比如一条信息的字段长度偏移列表就是
25 24 1A 17 13 0C 06
第一列row_id 占6个字节 偏移量为 06
第二列transaction_id 占6个字节 偏移量为 0C
第三列 roll_pointer 占7个字节 偏移量为 13
第四列 c1列的值占4个字节 偏移量为 17
第五列 c2列的值占3个字节 偏移量为 1A
第六列 c3列的值占10个字节 偏移量为 24
第七列 c4列的值占1个字节 偏移量为 25
- 记录头信息
Redundant⾏格式的记录头信息占⽤6字节，48个⼆进制位，
这些⼆进制位代表的意思如下：
table1中记录的头信息是
00 00 10 0F 00 BC
根据这六个字节可以计算出各个属性的值，如下
预留位1：0x00
预留位2：0x00
delete_mask: 0x00
min_rec_mask: 0x00
n_owned: 0x00
heap_no: 0x02
n_field: 0x07
1byte_offs_flag: 0x01
next_record:0xBC
与Compact⾏格式的记录头信息对⽐来看，有两处不同： Redundant⾏格式多了n_field和1byte_offs_flag这两个属性。 Redundant⾏格式没有record_type这个属性。 因为Redundant行格式是没有NULL值列表的，所以的NULL值信息都存在字段偏移列表中。 比如第二条记录的字段偏移列表为 A4 A4 1A 17 13 0C 06 可以看到 c4列和 c3列都为A4，也就是 c4列的长度为0，也就意味着为NULL值， 而CHAR（10）字段 记录的真实部分是10个字节。
CHAR(M)列的存储格式
Comapact行格式中CHAR(M)类型会根据字段的字符集是否为变长的字符集，来选择是否将该字段的长度的存储在字段长度列中。ASCII是固定字符集，char（10）固定式10个字节，不会存储在变长字段列表中，而utf-8是变长字符集 ，char（10）占的字节数可能是10-30，所以它的字符长度会存储在变长字段列表。 而对于Redundant行格式就很干脆，对于utf-8变长字符集，char(10)类型所占的字节数就是固定为30了， gbk 固定为20个字节。由此来看，Redundant行格式的char（m）类型的列不会产生碎片。
行溢出数据
VARCHAR(M)最多能存储的数据： 我们知道对于VARCHAR(M)类型的列最多可以占⽤65535个字节。其中的M代表该类型最多存储的字符数量 如果使用ascii字符集的话，一个字符代表一个字节，我们看看varchar（65535）是否可行：
mysql> create table varcahr_size_demo(
-> c varchar(65535)
-> )charset=ascii row_format=Compact;
ERROR 1118 (42000): Row size too large.
The maximum row size for the used table type,
not counting BLOBs, is 65535. This includes storage overhead, check the manual.
You have to change some columns to TEXT or BLOBs
mysql>
报错了，mysql告诉我们，这个行数据的大小不符合一条数据占用的最大存储空间，占用的字节数不能超过65535，它推荐我们使用TEXT和BLOB的类型。 那我们为什么不能存储65535个字节数的字符的呢？ 这是因为65535字节除了列本身的数据以外，还包括其他数据。 一个varchar(m)类型的列，需要占用三部分的存储空间：
- 真实数据
- 真实数据占用字节的长度
- null值标识，如果not null可以不用这部分
因为真实数据的字节长度占2个字节，null值标识占1个字节，所以，真实数据最多占的字节数为65532
create table varcahr_size_demo(
c varchar(65532)
)charset=ascii row_format=Compact;
创建成功了~
如果对这个字段加上 not null标识的话，那么真实数据可以存储的大小为65533个字节
drop table varcahr_size_demo;
create table varcahr_size_demo(
c varchar(65533) not null
)charset=ascii row_format=Compact;
如果varchar(m)使用的不是ASCII字符集，使用GBK/UTF-8呢？
drop table varcahr_size_demo;
create table varcahr_size_demo(
c varchar(65532)
)charset=gbk row_format=Compact;
create table varcahr_size_demo(
c varchar(65532)
)charset=utf8 row_format=Compact;
从执⾏结果中可以看出，如果VARCHAR(M)类型的列使⽤的不是ascii字符集，那M的最⼤取值取决于该字符集表示⼀个字符最多需要的字节数。在列的值允许为NULL的情况下，gbk字符集表示⼀个字符最多需要2个字符，那在该字符集下，M的最⼤取值就是32766（也就是：65532/2），也就是说最多能存储32766个字
符；utf8字符集表示⼀个字符最多需要3个字符，那在该字符集下，M的最⼤取值就是21844，就是说最多能存储21844（也就是：65532/3）个字符。
create table varcahr_size_demo(
c varchar(21844)
)charset=utf8 row_format=Compact;
记录中的数据太多产生的溢出
为什么会溢出？因为InnoDB存储数据是一页页的存储，而一页的数据最大为16KB，当我们存储的数据为65532个字节的时候，它占了64KB，一页也就存不下了 我们以ascii字符集为例
CREATE TABLE varchar_size_demo(
c VARCHAR(65532)
) CHARSET=ascii ROW_FORMAT=Compact;
INSERT INTO varchar_size_demo(c) VALUES(REPEAT('a', 65532));
--其中的REPEAT('a', 65532)是⼀个函数调⽤，它表示⽣成⼀个把字符'a'重复65532次的字符串。
在Compacat和Reduntant格式中，对于占用存储空间非常大的列来说，它们在记录真实数据的时候，只会记录一部分数据，其他数据存储在其他的页中，然后记录的真实数据出用20个字节来表示指向这些页的地址。（当然这20个字节中还包括这些分散在其他⻚⾯中的数据的占⽤的字节数），从而找到剩下的数据
如图所示
从图可以看出，对于Compact和Reduntant行格式来说，记录真实数据处，只会存储该列前768个字节的数据和指向其他页的地址。然后把剩下的数据存
放到其他⻚中，这个过程也叫做⾏溢出，存储超出768字节的那些⻚⾯也被称为溢出⻚。
最后需要注意的是，不只是 VARCHAR(M) 类型的列，其他的TEXT 、 BLOB 类型的列在存储数据⾮常多的时候也会发⽣⾏溢出。
行溢出的临界点
那发⽣⾏溢出的临界点是什么呢？也就是说在列存储多少字节的数据时就会发⽣⾏溢出？
其中MySQL规定：一个页中至少要存放两行记录，至于为什么，以后再说，现在看看这个规定造成的影响。
varchar_size_demo表为例，它只有⼀个列c，我们往这个表中插⼊两条记录，每条记录最少插⼊多少字节的数据才会⾏溢出的现象呢？这得分析⼀下⻚中的空间都是如何利⽤的。
:::info 首先： 每个页除了存放我们的记录意外，也会存储其他额外信息，这些加起来为136个字节，其他的空间都可以用来存储记录。 每个记录需要的额外信息为27个字节。 这27个字节包括下边这些部分： 2个字节⽤于存储真实数据的⻓度 1个字节⽤于存储列是否是NULL值 5个字节⼤⼩的头信息 6个字节的row_id列 6个字节的transaction_id列 7个字节的roll_pointer列 假设一个列中存储的数据字节数为n，那么发生溢出的现象就需要满足 136+2*（27+n)>16384=16*1024 解得n>8098 也就是说如果一个列中存储数据不大于8098个字节，就不会发生溢出。 不过这个8098个字节的结论只是针对只有⼀个列的varchar_size_demo表来说的，如果表中有多个列，那上边的式⼦和结论都需要改⼀改了，所以重点就是：你不⽤关注这个临界点是什么，只要知道如果我们想⼀个⾏中存储了很⼤的数据时，可能发⽣⾏溢出的现象。
:::
Dynamic和Compressed行格式
Dynamic和Compressed⾏格式，我现在使⽤的MySQL版本是5.7，它的默认⾏格式就是Dynamic，这俩
⾏格式和Compact⾏格式挺像，只不过在处理⾏溢出数据时有点⼉分歧，它们不会在记录的真实数据处存储字段真实数据的前768个字节，⽽是把所有的字节都存储到其他⻚⾯中，只在记录的真实数据处
存储其他⻚⾯的地址，就像这样：
简单的说，就是在真实数据处，Compact会存储数据的前768个字节，加溢出页的地址
而对于Dynamic来说，它会在真实数据处，存放其他所有溢出页的地址。
Compressed⾏格式和Dynamic不同的⼀点是，Compressed⾏格式会采⽤压缩算法对⻚⾯进⾏压缩，以节省空间。
CHAR(M)中M过大的情况
CHAR(M)可以存储的最大字节长度等于 该列使用的字符集需要的最大字节数和M的乘积 。 如果某一列使用的是CHAR(M),并且它可以存储的最大字节长度超过768字节，那么不论使用哪种行格式，InnoBD都会把该列当成长字段来看待，比如说采用 utf8mb4 的CHAR(255)类型的列会被当成长字段看待，因为 4*255>768
总结
- 页是mysql中磁盘与内存交互的基本单位，也是mysql管理存储 空间的基本单位
- 指定和修改行格式的语法
create table 表明(信息) row_format=行格式名称
alter table 表名 row_format=行格式名称
- InnoDB支持的4种行格式
- Compact
- Redundant
- Dynamic
- Compressed
