MySQL InnoDB Engine--数据页存储和DML操作

MySQL 5.7.28 社区版
CentOS release 6.10

MySQL Undo参数配置：
innodb_undo_tablespaces = 1
innodb_default_row_format = dynamic

## 创建测试表
CREATE TABLE `TB001` (
  `ID` VARCHAR(20) NOT NULL,
  `C1` VARCHAR(20) NOT NULL,
  `C2` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `IDX_C1` (`C1`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

## 第一次插入数据
INSERT INTO `TB001`(ID,C1,C2)VALUES(‘AA0001‘,‘BB0001‘,‘CC0001‘),(‘AA0002‘,‘BB0002‘,‘CC0002‘),(‘AA0003‘,‘BB0003‘,‘CC0003‘);

## 第二次插入数据
INSERT INTO `TB001`(ID,C1,C2)VALUES(‘AA0004‘,‘BB0004‘,‘CC0004‘);

## 查看数据
SELECT * FROM TB001;
+--------+--------+--------+
| ID     | C1     | C2     |
+--------+--------+--------+
| AA0001 | BB0001 | CC0001 |
| AA0002 | BB0002 | CC0002 |
| AA0003 | BB0003 | CC0003 |
| AA0004 | BB0004 | CC0004 |
+--------+--------+--------+

## 主键索引叶子节点数据
06 06 06 00 00 10 00 27 41 41 30 30 30 31 00 00 00 00 05 41 AE 00 00 00 70 01 10 42 42 30 30 30 31 43 43 30 30 30 31 
06 06 06 00 00 18 00 27 41 41 30 30 30 32 00 00 00 00 05 41 AE 00 00 00 70 01 1E 42 42 30 30 30 32 43 43 30 30 30 32 
06 06 06 00 00 20 00 27 41 41 30 30 30 33 00 00 00 00 05 41 AE 00 00 00 70 01 2C 42 42 30 30 30 33 43 43 30 30 30 33 
06 06 06 00 00 28 FF 7B 41 41 30 30 30 34 00 00 00 00 05 46 B1 00 00 00 73 01 10 42 42 30 30 30 34 43 43 30 30 30 34

## 索引IDX_C1叶子节点数据
06 06 00 00 10 00 13 42 42 30 30 30 31 41 41 30 30 30 31 
06 06 00 00 18 00 13 42 42 30 30 30 32 41 41 30 30 30 32 
06 06 00 00 20 00 13 42 42 30 30 30 33 41 41 30 30 30 33 
06 06 00 00 28 FF B8 42 42 30 30 30 34 41 41 30 30 30 34

## 更新C1列并导致数据变化
UPDATE TB001 SET C1=‘BBBB01‘ WHERE ID=‘AA0001‘;
## 更新C1列但不导致数据变化
UPDATE TB001 SET C1=‘BB0002‘ WHERE ID=‘AA0002‘;
## 更新C2列并导致数据变化
UPDATE TB001 SET C2=‘CCCC03‘ WHERE ID=‘AA0003‘;
## 更新C2列但不导致数据变化
UPDATE TB001 SET C2=‘CC0004‘ WHERE ID=‘AA0005‘;

SELECT * FROM TB001;
+--------+--------+--------+
| ID     | C1     | C2     |
+--------+--------+--------+
| AA0001 | BBBB01 | CC0001 |
| AA0002 | BB0002 | CC0002 |
| AA0003 | BB0003 | CCCC03 |
| AA0004 | BB0004 | CC0004 |
+--------+--------+--------+

## 主键索引叶子节点数据
06 06 06 00 00 10 00 27 41 41 30 30 30 31 00 00 00 00 05 4F 38 00 00 00 91 04 6C 42 42 42 42 30 31 43 43 30 30 30 31 
06 06 06 00 00 18 00 27 41 41 30 30 30 32 00 00 00 00 05 41 AE 00 00 00 70 01 1E 42 42 30 30 30 32 43 43 30 30 30 32 
06 06 06 00 00 20 00 27 41 41 30 30 30 33 00 00 00 00 05 52 3A 00 00 00 7C 03 D1 42 42 30 30 30 33 43 43 43 43 30 33 
06 06 06 00 00 28 FF 7B 41 41 30 30 30 34 00 00 00 00 05 46 B1 00 00 00 73 01 10 42 42 30 30 30 34 43 43 30 30 30 34

## 索引IDX_C1叶子节点数据
06 06 20 00 10 00 00 42 42 30 30 30 31 41 41 30 30 30 31 
06 06 00 00 18 00 13 42 42 30 30 30 32 41 41 30 30 30 32 
06 06 00 00 20 00 13 42 42 30 30 30 33 41 41 30 30 30 33 
06 06 00 00 28 00 13 42 42 30 30 30 34 41 41 30 30 30 34 
06 06 00 00 30 FF A5 42 42 42 42 30 31 41 41 30 30 30 31