mysql alter engine_MySQL InnoDB Engine--数据页存储和ALTER TABLE操作

测试环境：

MySQL 5.7.28社区版

CentOS release6.10MySQL Undo参数配置：

innodb_undo_tablespaces= 1innodb_default_row_format= dynamic

测试1：插入测试

测试脚本：

## 创建测试表

CREATE TABLE `TB001` (

`ID` varchar(20) NOT NULL,

`C1` varchar(20) NOT NULL,

PRIMARY KEY (`ID`),

KEY `IDX_C1` (`C1`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

## 第一次插入数据

INSERT INTO `TB001`(ID,C1)VALUES('AA0001','EE0001'),('AA0002','EE0002'),('AA0003','EE0003');

## 第二次插入数据

INSERT INTO `TB001`(ID,C1)VALUES('AA0004','EE0004');

主键索引叶子节点数据：

06 06 00 00 10 00 20 41 41 30 30 30 31 00 00 00 00 05 07 A7 00 00 00 69 01 10 45 45 30 30 30 31

06 06 00 00 18 00 20 41 41 30 30 30 32 00 00 00 00 05 07 A7 00 00 00 69 01 1E 45 45 30 30 30 32

06 06 00 00 20 00 20 41 41 30 30 30 33 00 00 00 00 05 07 A7 00 00 00 69 01 2C 45 45 30 30 30 33

06 06 00 00 28 FF 91 41 41 30 30 30 34 00 00 00 00 05 0C AA 00 00 00 6C 01 10 45 45 30 30 30 34

索引IDX_C1叶子节点数据：

06 06 00 00 10 00 13 45 45 30 30 30 31 41 41 30 30 30 31

06 06 00 00 18 00 13 45 45 30 30 30 32 41 41 30 30 30 32

06 06 00 00 20 00 13 45 45 30 30 30 33 41 41 30 30 30 33

06 06 00 00 28 FF B8 45 45 30 30 30 34 41 41 30 30 30 34

数据页和索引页存储为：

PS1:由于两次插入，记录AA004和记录(AA001--AA003)的事务信息不同，每条记录回滚指针都不同

PS2: 索引页上无事务信息和回滚指针

测试2：ALTER TABLE测试1

测试脚本：

ALTER TABLE TB001 ADD C2 INT;

主键叶子节点数据：

06 06 01 00 00 10 00 21 41 41 30 30 30 31 00 00 00 00 05 07 A7 00 00 00 69 01 10 45 45 30 30 30 31

06 06 01 00 00 18 00 21 41 41 30 30 30 32 00 00 00 00 05 07 A7 00 00 00 69 01 1E 45 45 30 30 30 32

06 06 01 00 00 20 00 21 41 41 30 30 30 33 00 00 00 00 05 07 A7 00 00 00 69 01 2C 45 45 30 30 30 33

06 06 01 00 00 28 FF 8D 41 41 30 30 30 34 00 00 00 00 05 0C AA 00 00 00 6C 01 10 45 45 30 30 30 34

索引IDX_C1叶子节点数据：

06 06 00 00 10 00 13 45 45 30 30 30 31 41 41 30 30 30 31

06 06 00 00 18 00 13 45 45 30 30 30 32 41 41 30 30 30 32

06 06 00 00 20 00 13 45 45 30 30 30 33 41 41 30 30 30 33

06 06 00 00 28 FF B8 45 45 30 30 30 34 41 41 30 30 30 34

数据页和索引页存储为：

对比发现，执行ALTER TABLE TB001 AD C2 INT操作后：

1、ALTER TABLE操作未导致主键索引记录的事务信息和回滚指针发生变化

2、新增C2列导致"行偏移量”新增一个字节来表示C2是否为NULL，01表示为NULL。

3、新增C2列未导致“记录列信息”发生变化，即"记录列"中无C2列对应的存储空间，因为C2列为NULL。

测试2：ALTER TABLE测试2

测试脚本：

ALTER TABLE TB001 ADD C3 INT NOT NULL DEFAULT 9999;

主键叶子节点数据：

06 06 01 00 00 10 00 25 41 41 30 30 30 31 00 00 00 00 05 07 A7 00 00 00 69 01 10 45 45 30 30 30 31 80 00 270F06 06 01 00 00 18 00 25 41 41 30 30 30 32 00 00 00 00 05 07 A7 00 00 00 69 01 1E 45 45 30 30 30 32 80 00 270F06 06 01 00 00 20 00 25 41 41 30 30 30 33 00 00 00 00 05 07 A7 00 00 00 69 01 2C 45 45 30 30 30 33 80 00 270F06 06 01 00 00 28 FF 81 41 41 30 30 30 34 00 00 00 00 05 0C AA 00 00 00 6C 01 10 45 45 30 30 30 34 80 00 27 0F

索引IDX_C1叶子节点数据：

06 06 00 00 10 00 13 45 45 30 30 30 31 41 41 30 30 30 31

06 06 00 00 18 00 13 45 45 30 30 30 32 41 41 30 30 30 32

06 06 00 00 20 00 13 45 45 30 30 30 33 41 41 30 30 30 33

06 06 00 00 28 FF B8 45 45 30 30 30 34 41 41 30 30 30 34

数据页和索引页存储为：

对比发现，执行ADD C3 INT NOT NULL DEFAULT 9999操作后：

1、新增NOT NULL列后不会导致"行偏移量"信息发生变化

2、新增INT NOT NULL DEFAULT 9999列后会在“记录列信息”中增加4字节数据并存放9999的数值。

3、记录头信息最后16bits是记录下一条记录的相对位置，由于记录总长度变长，记录头信息最后一字节发生改变。

4、ALTER TABLE同样未导致"事务信息"和"回滚指针"发生变化

PS: 十六进制值"80 00 27 0F"转换为二进制为"1000000 00000000 00100111 00001111"，第一为1表示正负值，后面的值"10011100001111"转换为10进制为9999。