暂无图片
暂无图片
3
暂无图片
暂无图片
暂无图片
首页 / [MYSQL] 业务DDL对应的系统表的DML是啥?

[MYSQL] 业务DDL对应的系统表的DML是啥?

原创 大大刺猬 2025-12-19
868

导读

在通过系统表拼接用户表DDL的时候, 发现新建一张表(CREATE TABLE)时, 系统表中会额外存在一份被标记为deleted的数据. 这就让人好奇执行DDL时,系统表是如何更新的?

不难想象用户执行的DDL是对系统表的DML操作,那么究竟是怎么对应的呢? 如何观察呢?

binlog记录的是DDL,看不到系统表的DML; 后面有机会可以试试redo/undo

怎么观察

我们先观察业务DML. 主要使用gdb和ibd2sql来观察, 前者观察过程,后者观察结果.

准备测试表:

create table t20251219(id int, name varchar(200));
insert into t20251219 values(1,'ddcw');

使用gdb打断点

gdb -p `pidof mysqld-debug`
break ha_innobase::update_row
break ha_innobase::delete_row
break ha_innobase::write_row

# 触发断点之后,可以查看下表名
p this->table.alias

执行DML操作(注:有gtid,且自动提交)

insert into t20251219 values(2,'ddcw22');

观察断点情况:

(gdb) break ha_innobase::update_row
Breakpoint 1 at 0x4519917: file ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc, line 9173.
(gdb) break ha_innobase::delete_row
Breakpoint 2 at 0x4519fe5: file ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc, line 9327.
(gdb) break ha_innobase::write_row
Breakpoint 3 at 0x45177d2: file ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc, line 8434.
(gdb) c
Continuing.
[Thread 0x7f84606c9700 (LWP 16117) exited]
[Switching to Thread 0x7f84605ab700 (LWP 16157)]

Breakpoint 3, ha_innobase::write_row (this=0x7f82b9422628, record=0x7f82b84e82d8 "\374\002")
    at ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc:8434
8434	../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc: No such file or directory.
(gdb) p this->table.alias
$1 = 0x7f82b92b2780 "t20251219"
(gdb) c
Continuing.
[Switching to Thread 0x7f8300ff9700 (LWP 16122)]

Breakpoint 3, ha_innobase::write_row (this=0x7f82d414f688, 
    record=0x7f82d4008298 "5ea62ea9-bab3-11f0-84ec-000c2980c11e", ' ' <repeats 72 times>, "S")
    at ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc:8434
8434	in ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc
(gdb) p this->table.alias
$2 = 0x7f82d4076ef0 "gtid_executed"
(gdb) c
Continuing.

我们发现触发了两次’ha_innobase::write_row’, 第一次是表’t20251219’, 第二次是表’gtid_executed’.

然后我们使用ibd2sql查看下该表的数据情况

[root@ddcw21 ibd2sql-ibd2sql-v2.x]#python3 main.py /data/mysql_8019/mysqldata/db1/t20251219.ibd --sql
INSERT INTO `db1`.`t20251219` VALUES (1,'ddcw');
INSERT INTO `db1`.`t20251219` VALUES (2,'ddcw22');
[root@ddcw21 ibd2sql-ibd2sql-v2.x]#
[root@ddcw21 ibd2sql-ibd2sql-v2.x]#python3 main.py /data/mysql_8019/mysqldata/db1/t20251219.ibd --sql --delete
[root@ddcw21 ibd2sql-ibd2sql-v2.x]#

只有’–sql’的时候有数据,’–sql --delete’的时候没有数据.说明该表只有insert时候的数据,不存在delete之后再insert的情况.

综上可得:DML中insert对应的就是write_row对应的表.

多么朴实无华的结论. update和delete分别对应update_row和delete_row. 我就不再去测试了.

DDL对应系统表的DML

我们只考虑常见的几种DDL,毕竟人类的力量是有限的,所以我不做人了.

打断点的部分就不再赘述了. 由于系统表涉及的表较多, 所以我们要使用ibd2sql记录下DDL之前的系统表的数据,做完DDL之后,再记录下当前的系统表数据,并做对比. 后续的DDL都按照这个步骤来.

# 打断点
break ha_innobase::update_row
break ha_innobase::delete_row
break ha_innobase::write_row

# 记录当前系统表的数据
python3 main.py /data/mysql_8019/mysqldata/mysql.ibd --sql | sort > /tmp/t20251219_01_before_ddl_non.sql
python3 main.py /data/mysql_8019/mysqldata/mysql.ibd --sql --delete | sort > /tmp/t20251219_01_before_ddl_deleted.sql # 我们还记录了一份被标记为删除的数据, 因为有些DDL会涉及到系统表的delete操作

# 执行DDL
ALTER TABLE ...

# 查看表名和其它信息,并continue
p this->table.alias # 或者p m_prebuilt->table.name

# 再次记录系统表的数据
python3 main.py /data/mysql_8019/mysqldata/mysql.ibd --sql | sort > /tmp/t20251219_01_after_ddl_non.sql
python3 main.py /data/mysql_8019/mysqldata/mysql.ibd --sql --delete | sort > /tmp/t20251219_01_after_ddl_deleted.sql

# 对比系统表的差异
diff /tmp/t20251219_01_before_ddl_non.sql /tmp/t20251219_01_after_ddl_non.sql # 可查看最终的数据差异
diff /tmp/t20251219_01_before_ddl_deleted.sql /tmp/t20251219_01_after_ddl_deleted.sql # 可查看一些过程中的细菌

gdb看到的和ibd2sql看到的还能相互验证!

新建表 CREATE TABLE

按照上面的步骤, 我们得到CREATE TABLE db1.t20251219_01(id int);时gdb看到的表更新情况如下(第一次就看下完整的, 后面就省略这部分了):

(gdb) break ha_innobase::update_row
Breakpoint 1 at 0x4519917: file ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc, line 9173.
(gdb) break ha_innobase::delete_row
Breakpoint 2 at 0x4519fe5: file ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc, line 9327.
(gdb) break ha_innobase::write_row
Breakpoint 3 at 0x45177d2: file ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc, line 8434.
(gdb) c
Continuing.
[Switching to Thread 0x7f84605ab700 (LWP 16157)]

Breakpoint 3, ha_innobase::write_row (this=0x7f82b806e1d8, record=0x7f82b806f8a8 "\370\377\377")
    at ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc:8434
8434	../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc: No such file or directory.
(gdb) p this->table.alias
$1 = 0x7f82b809b950 "tables"
(gdb) c
Continuing.

Breakpoint 3, ha_innobase::write_row (this=0x7f82b803a788, record=0x7f82b803be58 " \317", <incomplete sequence \366>)
    at ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc:8434
8434	in ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc
(gdb) p this->table.alias
$2 = 0x7f82b800c210 "columns"
(gdb) c
Continuing.

Breakpoint 3, ha_innobase::write_row (this=0x7f82b803a788, record=0x7f82b803be58 "0\317", <incomplete sequence \367>)
    at ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc:8434
8434	in ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc
(gdb) p this->table.alias
$3 = 0x7f82b800c210 "columns"
(gdb) c
Continuing.

Breakpoint 3, ha_innobase::write_row (this=0x7f82b803a788, record=0x7f82b803be58 "0\317", <incomplete sequence \367>)
    at ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc:8434
8434	in ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc
(gdb) p this->table.alias
$4 = 0x7f82b800c210 "columns"
(gdb) c
Continuing.

Breakpoint 3, ha_innobase::write_row (this=0x7f82b803a788, record=0x7f82b803be58 "0\317", <incomplete sequence \367>)
    at ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc:8434
8434	in ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc
(gdb) p this->table.alias
$5 = 0x7f82b800c210 "columns"
(gdb) c
Continuing.

Breakpoint 3, ha_innobase::write_row (this=0x7f82b8058d78, record=0x7f82b805a448 "\377")
    at ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc:8434
8434	in ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc
(gdb) p this->table.alias
$6 = 0x7f82b80353c0 "indexes"
(gdb) c
Continuing.

Breakpoint 3, ha_innobase::write_row (this=0x7f82b804fdf8, record=0x7f82b80514c8 "\377A\001")
    at ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc:8434
8434	in ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc
(gdb) p this->table.alias
$7 = 0x7f82b8026a20 "index_column_usage"
(gdb) c
Continuing.

Breakpoint 3, ha_innobase::write_row (this=0x7f82b804fdf8, record=0x7f82b80514c8 "\377A\001")
    at ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc:8434
8434	in ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc
(gdb) p this->table.alias
$8 = 0x7f82b8026a20 "index_column_usage"
(gdb) c
Continuing.

Breakpoint 3, ha_innobase::write_row (this=0x7f82b804fdf8, record=0x7f82b80514c8 "\377A\001")
    at ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc:8434
8434	in ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc
(gdb) p this->table.alias
$9 = 0x7f82b8026a20 "index_column_usage"
(gdb) c
Continuing.

Breakpoint 3, ha_innobase::write_row (this=0x7f82b804fdf8, record=0x7f82b80514c8 "\377A\001")
    at ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc:8434
8434	in ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc
(gdb) p this->table.alias
$10 = 0x7f82b8026a20 "index_column_usage"
(gdb) c
Continuing.

Breakpoint 3, ha_innobase::write_row (this=0x7f82b811d7a8, record=0x7f82b811ee78 <incomplete sequence \374>)
    at ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc:8434
8434	in ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc
(gdb) p this->table.alias
$11 = 0x7f82b80eb8f0 "tablespaces"
(gdb) c
Continuing.

Breakpoint 3, ha_innobase::write_row (this=0x7f82b82689d8, record=0x7f82b826a0a8 "\376\357\001")
    at ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc:8434
8434	in ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc
(gdb) p this->table.alias
$12 = 0x7f82b81fb6b0 "tablespace_files"
(gdb) c
Continuing.

Breakpoint 1, ha_innobase::update_row (this=0x7f82b806e1d8, old_row=0x7f82b8077628 "\370\377\377~\001", 
    new_row=0x7f82b806f8a8 "\350\377\377~\001") at ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc:9173
9173	in ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc
(gdb) p this->table.alias
$13 = 0x7f82b809b950 "tables"
(gdb) c
Continuing.

Breakpoint 1, ha_innobase::update_row (this=0x7f82b803a788, old_row=0x7f82b803d818 " \317\366^\020", 
    new_row=0x7f82b803be58 " \317\364^\020") at ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc:9173
9173	in ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc
(gdb) p this->table.alias
$14 = 0x7f82b800c210 "columns"
(gdb) c
Continuing.

Breakpoint 1, ha_innobase::update_row (this=0x7f82b803a788, old_row=0x7f82b803d818 "0\317\367_\020", 
    new_row=0x7f82b803be58 "0\317\365_\020") at ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc:9173
9173	in ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc
(gdb) p this->table.alias
$15 = 0x7f82b800c210 "columns"
(gdb) c
Continuing.

Breakpoint 1, ha_innobase::update_row (this=0x7f82b803a788, old_row=0x7f82b803d818 "0\317\367`\020", 
    new_row=0x7f82b803be58 "0\317\365`\020") at ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc:9173
9173	in ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc
(gdb) p this->table.alias
$16 = 0x7f82b800c210 "columns"
(gdb) c
Continuing.

Breakpoint 1, ha_innobase::update_row (this=0x7f82b803a788, old_row=0x7f82b803d818 "0\317\367a\020", 
    new_row=0x7f82b803be58 "0\317\365a\020") at ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc:9173
9173	in ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc
(gdb) p this->table.alias
$17 = 0x7f82b800c210 "columns"
(gdb) c
Continuing.

Breakpoint 1, ha_innobase::update_row (this=0x7f82b8058d78, old_row=0x7f82b805be08 "\377A\001", 
    new_row=0x7f82b805a448 "\371A\001") at ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc:9173
9173	in ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc
(gdb) p this->table.alias
$18 = 0x7f82b80353c0 "indexes"
(gdb) c
Continuing.

Breakpoint 1, ha_innobase::update_row (this=0x7f82b804fdf8, old_row=0x7f82b80514e8 "\377A\001", 
    new_row=0x7f82b80514c8 "\377A\001") at ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc:9173
9173	in ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc
(gdb) p this->table.alias
$19 = 0x7f82b8026a20 "index_column_usage"
(gdb) c
Continuing.

Breakpoint 1, ha_innobase::update_row (this=0x7f82b804fdf8, old_row=0x7f82b80514e8 "\377A\001", 
    new_row=0x7f82b80514c8 "\377A\001") at ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc:9173
9173	in ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc
(gdb) p this->table.alias
$20 = 0x7f82b8026a20 "index_column_usage"
(gdb) c
Continuing.

Breakpoint 1, ha_innobase::update_row (this=0x7f82b804fdf8, old_row=0x7f82b80514e8 "\377A\001", 
    new_row=0x7f82b80514c8 "\377A\001") at ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc:9173
9173	in ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc
(gdb) p this->table.alias
$21 = 0x7f82b8026a20 "index_column_usage"
(gdb) c
Continuing.

Breakpoint 1, ha_innobase::update_row (this=0x7f82b804fdf8, old_row=0x7f82b80514e8 "\377A\001", 
    new_row=0x7f82b80514c8 "\377A\001") at ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc:9173
9173	in ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc
(gdb) p this->table.alias
$22 = 0x7f82b8026a20 "index_column_usage"
(gdb) c
Continuing.
[Switching to Thread 0x7f8300ff9700 (LWP 16122)]

Breakpoint 3, ha_innobase::write_row (this=0x7f82d414f688, 
    record=0x7f82d4008298 "5ea62ea9-bab3-11f0-84ec-000c2980c11e", ' ' <repeats 72 times>, "V")
    at ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc:8434
8434	in ../../../mysql-8.0.19/storage/innobase/handler/ha_innodb.cc
(gdb) p this->table.alias
$23 = 0x7f82d4076ef0 "gtid_executed"
(gdb) c
Continuing.

我们稍微整理下就是:

ha_innobase::write_row	tables
ha_innobase::write_row	columns
ha_innobase::write_row	columns
ha_innobase::write_row	columns
ha_innobase::write_row	columns
ha_innobase::write_row	indexes
ha_innobase::write_row	index_column_usage
ha_innobase::write_row	index_column_usage
ha_innobase::write_row	index_column_usage
ha_innobase::write_row	index_column_usage
ha_innobase::write_row	tablespaces
ha_innobase::write_row	tablespace_files
ha_innobase::update_row	tables
ha_innobase::update_row	columns
ha_innobase::update_row	columns
ha_innobase::update_row	columns
ha_innobase::update_row	columns
ha_innobase::update_row	indexes
ha_innobase::update_row	index_column_usage
ha_innobase::update_row	index_column_usage
ha_innobase::update_row	index_column_usage
ha_innobase::update_row	index_column_usage
ha_innobase::write_row	gtid_executed

看到先是insert了tables,columns,indexes,index_column_usage,tablespaces,tablespace_files然后有更新了tables,columns,indexes,index_column_usage,最后再insert下gtid_executed就完整了新增一张表的操作.

  1. 为什么插入字段时有4次呢? 明明只有1列啊!

    我们建的表没有主键,于是系统自动给了个rowid做主键,还有2列是trxid和rollptr.

  2. 为啥insert之后还要再update一下呢?

    只有看到更新的内容之后才能确定, 这就是我们还要记录系统表数据更新的原因. 是时候来看我们的ibd2sql的了

系统表DDL前后最终数据变化情况如下:

[root@ddcw21 ibd2sql-ibd2sql-v2.x]#diff /tmp/t20251219_01_before_ddl_non.sql /tmp/t20251219_01_after_ddl_non.sql 
3587a3588,3591
> INSERT INTO `mysql`.`columns` VALUES (4190,382,'id',1,'MYSQL_TYPE_LONG',1,0,0,11,10,0,null,33,0,null,null,null,null,0,0,null,null,'','Visible','interval_count=0;','table_id=1547;','','int',null,0);
> INSERT INTO `mysql`.`columns` VALUES (4191,382,'DB_ROW_ID',2,'MYSQL_TYPE_INT24',0,0,0,6,0,null,null,63,0,null,null,null,null,0,0,null,null,'','SE',null,'table_id=1547;','','',null,0);
> INSERT INTO `mysql`.`columns` VALUES (4192,382,'DB_TRX_ID',3,'MYSQL_TYPE_INT24',0,0,0,6,0,null,null,63,0,null,null,null,null,0,0,null,null,'','SE',null,'table_id=1547;','','',null,0);
> INSERT INTO `mysql`.`columns` VALUES (4193,382,'DB_ROLL_PTR',4,'MYSQL_TYPE_LONGLONG',0,0,0,7,0,null,null,63,0,null,null,null,null,0,0,null,null,'','SE',null,'table_id=1547;','','',null,0);
5237a5242
> INSERT INTO `mysql`.`gtid_executed` VALUES ('5ea62ea9-bab3-11f0-84ec-000c2980c11e',86,86);
9177a9183
> INSERT INTO `mysql`.`index_column_usage` VALUES (321,1,4191,null,'ASC',1);
9179a9186,9188
> INSERT INTO `mysql`.`index_column_usage` VALUES (321,2,4192,null,'ASC',1);
> INSERT INTO `mysql`.`index_column_usage` VALUES (321,3,4193,null,'ASC',1);
> INSERT INTO `mysql`.`index_column_usage` VALUES (321,4,4190,null,'ASC',1);
9829a9839
> INSERT INTO `mysql`.`indexes` VALUES (321,382,'PRIMARY','UNIQUE','BTREE',0,1,0,1,1,'',null,'id=1160;root=4;space_id=490;table_id=1547;trx_id=14349;',495,'InnoDB');
10881c10891
< INSERT INTO `mysql`.`innodb_dynamic_metadata` VALUES (12,0,0x02905d);
---
> INSERT INTO `mysql`.`innodb_dynamic_metadata` VALUES (12,0,0x029061);
10884c10894
< INSERT INTO `mysql`.`innodb_dynamic_metadata` VALUES (19,0,0x028140);
---
> INSERT INTO `mysql`.`innodb_dynamic_metadata` VALUES (19,0,0x028141);
10890,10891c10900,10901
< INSERT INTO `mysql`.`innodb_dynamic_metadata` VALUES (29,0,0x02817d);
< INSERT INTO `mysql`.`innodb_dynamic_metadata` VALUES (31,0,0x0281ee);
---
> INSERT INTO `mysql`.`innodb_dynamic_metadata` VALUES (29,0,0x02817e);
> INSERT INTO `mysql`.`innodb_dynamic_metadata` VALUES (31,0,0x0281ef);
10893c10903
< INSERT INTO `mysql`.`innodb_dynamic_metadata` VALUES (5,0,0x0287fb);
---
> INSERT INTO `mysql`.`innodb_dynamic_metadata` VALUES (5,0,0x0287fe);
14955a14966,14968
> INSERT INTO `mysql`.`innodb_index_stats` VALUES ('db1','t20251219_01','GEN_CLUST_INDEX','2025-12-19 13:53:26','n_diff_pfx01',0,1,'DB_ROW_ID');
> INSERT INTO `mysql`.`innodb_index_stats` VALUES ('db1','t20251219_01','GEN_CLUST_INDEX','2025-12-19 13:53:26','n_leaf_pages',1,null,'Number of leaf pages in the index');
> INSERT INTO `mysql`.`innodb_index_stats` VALUES ('db1','t20251219_01','GEN_CLUST_INDEX','2025-12-19 13:53:26','size',1,null,'Number of pages in the index');
14962,14965c14975,14978
< INSERT INTO `mysql`.`innodb_index_stats` VALUES ('mysql','gtid_executed','PRIMARY','2025-12-19 13:38:54','n_diff_pfx01',1,1,'source_uuid');
< INSERT INTO `mysql`.`innodb_index_stats` VALUES ('mysql','gtid_executed','PRIMARY','2025-12-19 13:38:54','n_diff_pfx02',5,1,'source_uuid,interval_start');
< INSERT INTO `mysql`.`innodb_index_stats` VALUES ('mysql','gtid_executed','PRIMARY','2025-12-19 13:38:54','n_leaf_pages',1,null,'Number of leaf pages in the index');
< INSERT INTO `mysql`.`innodb_index_stats` VALUES ('mysql','gtid_executed','PRIMARY','2025-12-19 13:38:54','size',1,null,'Number of pages in the index');
---
> INSERT INTO `mysql`.`innodb_index_stats` VALUES ('mysql','gtid_executed','PRIMARY','2025-12-19 13:53:35','n_diff_pfx01',1,1,'source_uuid');
> INSERT INTO `mysql`.`innodb_index_stats` VALUES ('mysql','gtid_executed','PRIMARY','2025-12-19 13:53:35','n_diff_pfx02',7,1,'source_uuid,interval_start');
> INSERT INTO `mysql`.`innodb_index_stats` VALUES ('mysql','gtid_executed','PRIMARY','2025-12-19 13:53:35','n_leaf_pages',1,null,'Number of leaf pages in the index');
> INSERT INTO `mysql`.`innodb_index_stats` VALUES ('mysql','gtid_executed','PRIMARY','2025-12-19 13:53:35','size',1,null,'Number of pages in the index');
15445a15459
> INSERT INTO `mysql`.`innodb_table_stats` VALUES ('db1','t20251219_01','2025-12-19 13:53:26',0,1,0);
15448c15462
< INSERT INTO `mysql`.`innodb_table_stats` VALUES ('mysql','gtid_executed','2025-12-19 13:38:54',5,1,0);
---
> INSERT INTO `mysql`.`innodb_table_stats` VALUES ('mysql','gtid_executed','2025-12-19 13:53:35',7,1,0);
21737a21752
> INSERT INTO `mysql`.`tablespace_files` VALUES (495,1,'./db1/t20251219_01.ibd','id=490;');
22219a22235
> INSERT INTO `mysql`.`tablespaces` VALUES (495,'db1/t20251219_01','encryption=N;','flags=16417;id=490;server_version=80019;space_version=1;state=normal;','','InnoDB');
22567a22584
> INSERT INTO `mysql`.`tables` VALUES (382,5,'t20251219_01','BASE TABLE','InnoDB',80019,'Dynamic',33,'','Visible','avg_row_length=0;encrypt_type=N;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0;',null,1547,null,null,null,null,null,null,null,null,null,'2025-12-19 13:52:56','2025-12-19 13:52:56',null,null,null,null,null,null,null,null,null,null,0);

虽然gdb的时候我们只看到了insert和update,但我们还得看下哪些数据是被标记为删除的, 毕竟系统表的更新不一定都走ha_innobase

[root@ddcw21 ibd2sql-ibd2sql-v2.x]#diff /tmp/t20251219_01_before_ddl_deleted.sql /tmp/t20251219_01_after_ddl_deleted.sql
311d310
< INSERT INTO `mysql`.`columns` VALUES (4179,380,'id',1,'MYSQL_TYPE_LONG',1,0,0,11,10,0,null,33,0,null,null,null,null,0,0,null,null,'','Visible','interval_count=0;','table_id=1546;','','int',null,0);
313d311
< INSERT INTO `mysql`.`columns` VALUES (4180,380,'name',2,'MYSQL_TYPE_VARCHAR',1,0,0,600,0,null,null,33,0,null,null,null,null,0,0,null,null,'','Visible','interval_count=0;','table_id=1546;','','varchar(200)',null,0);
315d312
< INSERT INTO `mysql`.`columns` VALUES (4181,380,'DB_ROW_ID',3,'MYSQL_TYPE_INT24',0,0,0,6,0,null,null,63,0,null,null,null,null,0,0,null,null,'','SE',null,'table_id=1546;','','',null,0);
317d313
< INSERT INTO `mysql`.`columns` VALUES (4182,380,'DB_TRX_ID',4,'MYSQL_TYPE_INT24',0,0,0,6,0,null,null,63,0,null,null,null,null,0,0,null,null,'','SE',null,'table_id=1546;','','',null,0);
319a316,319
> INSERT INTO `mysql`.`columns` VALUES (4190,382,'id',1,'MYSQL_TYPE_LONG',1,0,0,11,10,0,null,33,0,null,null,null,null,0,0,null,null,'','Visible','interval_count=0;',null,'','int',null,0);
> INSERT INTO `mysql`.`columns` VALUES (4191,382,'DB_ROW_ID',2,'MYSQL_TYPE_INT24',0,0,0,6,0,null,null,63,0,null,null,null,null,0,0,null,null,'','SE',null,null,'','',null,0);
> INSERT INTO `mysql`.`columns` VALUES (4192,382,'DB_TRX_ID',3,'MYSQL_TYPE_INT24',0,0,0,6,0,null,null,63,0,null,null,null,null,0,0,null,null,'','SE',null,null,'','',null,0);
> INSERT INTO `mysql`.`columns` VALUES (4193,382,'DB_ROLL_PTR',4,'MYSQL_TYPE_LONGLONG',0,0,0,7,0,null,null,63,0,null,null,null,null,0,0,null,null,'','SE',null,null,'','',null,0);
571d570
< INSERT INTO `mysql`.`gtid_executed` VALUES ('5ea62ea9-bab3-11f0-84ec-000c2980c11e',70,70);
969d967
< INSERT INTO `mysql`.`index_column_usage` VALUES (319,5,4180,null,'ASC',1);
1181d1178
< INSERT INTO `mysql`.`indexes` VALUES (319,380,'PRIMARY','UNIQUE','BTREE',0,1,0,1,1,'',null,'id=1159;root=4;space_id=489;table_id=1546;trx_id=14309;',494,'InnoDB');
1182a1180
> INSERT INTO `mysql`.`indexes` VALUES (321,382,'PRIMARY','UNIQUE','BTREE',0,1,0,1,1,'',null,null,null,'InnoDB');
2848d2845
< INSERT INTO `mysql`.`tables` VALUES (380,5,'t20251219','BASE TABLE','InnoDB',80019,'Dynamic',33,'','Visible','avg_row_length=0;encrypt_type=N;key_block_size=0;keys_disabled=0;pack_record=1;stats_auto_recalc=0;stats_sample_pages=0;',null,1546,null,null,null,null,null,null,null,null,null,'2025-12-19 13:24:21','2025-12-19 13:24:21',null,null,null,null,null,null,null,null,null,null,0);
2849a2847
> INSERT INTO `mysql`.`tables` VALUES (382,5,'t20251219_01','BASE TABLE','InnoDB',80019,'Dynamic',33,'','Visible','avg_row_length=0;encrypt_type=N;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0;',null,null,null,null,null,null,null,null,null,null,null,'2025-12-19 13:52:56','2025-12-19 13:52:56',null,null,null,null,null,null,null,null,null,null,0);

注意我们是使用的diff比较的, 那么被删除部分新增出来的就是被删除的, 消失了的就是被purge回收了的. 汇总上面的信息我们得到:

mysql.columns:
	insert: id,DB_ROW_ID,DB_TRX_ID,DB_ROLL_PTR
	delete: id,DB_ROW_ID,DB_TRX_ID,DB_ROLL_PTR
mysql.gtid_executed:
	insert: ('5ea62ea9-bab3-11f0-84ec-000c2980c11e',86,86)
mysql.index_column_usage:
	insert: idxid=321,ordinal_position=1,2,3,4
mysql.indexes:
	insert: idxid=321,PRIMARY KEY
	delete: idxid=321,PRIMARY KEY
mysql.innodb_dynamic_metadata
	update:table_id=12(columns),19(indexes),29(tables),31(tablespaces),5(innodb_ddl_log)
mysql.innodb_index_stats:
	insert:t20251219_01:GEN_CLUST_INDEX:n_diff_pfx01,n_leaf_pages,size
	insert:gtid_executed:PRIMARY.....
mysql.innodb_table_stats:
	insert:t20251219_01
	insert:gtid_executed...
mysql.tablespace_files:
	insert:./db1/t20251219_01.ibd
mysql.tables:
	insert:table_id=382;table_name=t20251219_01
	delete:table_id=382;table_name=t20251219_01

ibd2sql看到更新的数据要比gdb看到的多了统计信息(innodb_index_stats,innodb_table_stats)和动态元数据信息(innodb_dynamic_metadata). 而且tables,columns,indexes中均存在一份看起来"一模一样"的被标记为deleted的数据, 难道这是gdb中看到的update? 也就是说: update在特殊情况下会转为delete+insert?

虽然看起来一模一样, 但实际上是更新的se_private_id,se_private_data之类的信息.

我们来测试下,更新一个表某个字段的时候是否存在被转为delete+insert.

# 就使用系统表吧
python3 main.py /data/mysql_8019/mysqldata/mysql.ibd --set table=tables --schema db1 --table t20251219_22 --ddl disable-keys --sql --limit 60 | grep -v '  CONSTRAINT ' | sed '/last_checked_for_upgrade_version_id/c  last_checked_for_upgrade_version_id int unsigned NOT NULL,PRIMARY KEY(id)' | mysql -h127.0.0.1 -P8019 -p123456

# 然后update瞅瞅,看是否有被标记为delete的数据
mysql -h127.0.0.1 -P8019 -p123456 -e "update db1.t20251219_22 set se_private_id=1547 where id=55;"

[root@ddcw21 ibd2sql-ibd2sql-v2.x]#python3 main.py /data/mysql_8019/mysqldata/db1/t20251219_22.ibd --sql --delete| wc -l
20

发现存在被标记为删除的居然有20条数据,其中也是包含我们那条数据的.那为啥呢? 难道是页分裂重组之类的?

我们再简单验证下吧(感觉都偏题了…)

-- 启用分裂重组页的监控
set global innodb_monitor_enable='index_page_splits';
set global innodb_monitor_enable='index_page_reorg%';

-- 触发页分裂, 就上面操作再来一遍
...

-- 查看页分裂情况 
(root@127.0.0.1) [db1]> select name,count,STATUS,comment from information_schema.innodb_metrics where name like 'index_page_%' and STATUS='enabled'; -- 初始化数据的时候
+-----------------------------+-------+---------+-------------------------------------------------+
| name                        | count | STATUS  | comment                                         |
+-----------------------------+-------+---------+-------------------------------------------------+
| index_page_splits           |     8 | enabled | Number of index page splits                     |
| index_page_reorg_attempts   |     2 | enabled | Number of index page reorganization attempts    |
| index_page_reorg_successful |     2 | enabled | Number of successful index page reorganizations |
+-----------------------------+-------+---------+-------------------------------------------------+
3 rows in set (0.00 sec)

(root@127.0.0.1) [db1]> select name,count,STATUS,comment from information_schema.innodb_metrics where name like 'index_page_%' and STATUS='enabled'; -- update数据的时候
+-----------------------------+-------+---------+-------------------------------------------------+
| name                        | count | STATUS  | comment                                         |
+-----------------------------+-------+---------+-------------------------------------------------+
| index_page_splits           |     8 | enabled | Number of index page splits                     |
| index_page_reorg_attempts   |     3 | enabled | Number of index page reorganization attempts    |
| index_page_reorg_successful |     3 | enabled | Number of successful index page reorganizations |
+-----------------------------+-------+---------+-------------------------------------------------+
3 rows in set (0.00 sec)

还可以使用ibd2sql来观察,观察各页数据的情况:python3 main.py /data/mysql_8019/mysqldata/db1/t20251219_25.ibd --sql --log | grep -E ‘INSERT|READ PAGE ID:’ | awk -F ‘,’ ‘{print $1}’ 然后执行DDL之后再次观察(建议再查看下被标记为删除的数据)

确实是数据页分裂合并导致的. 系统表单行数据太大了,很容易就触发.

综上,我们得到了一个关于CREATE TABLE的清晰逻辑:

-- 系统表记录业务表的信息
INSERT INTO `TABLES` values(tableid,...)
INSERT INTO `COLUMNS` values(...),(),(),()
INSERT INTO `indexes` values(...)
INSERT INTO `index_column_usage` values(...),(),(),()
INSERT INTO `tablespaces` values(...)
INSERT INTO `tablespace_files` values(...)
UPDATE `TABLES` set se_private_id=....
UPDATE `COLUMSN` set se_private_data=....
UPDATE `COLUMNS`..
UPDATE `index_column_usage`...

-- 更新gtid信息
INSERT `gtid_executed` values()

-- 更新动态元数据
UPDATE innodb_dynamic_metadata where id=...

-- 更新统计信息
INSERT INTO innodb_table_stats()
INSERT INTO innodb_index_stats()

新增字段 ALTER TABLE ADD

新增字段是存在ONLINE DDL的, 且有inplace和instant算法的区分,(甚至还有8.0.13-28时instant和>=8.0.29时instant的区分). 我们就只看8.0.19新增字段时的instant,inplace和copy效果.

copy

DDL如下:

alter table t20251219_01 add column name varchar(20),algorithm=copy;

结论如下: 基本上就是建个临时表,变更完之后,再把旧表干掉

-- 创建临时表文件
INSERT INTO `mysql`.`tablespaces` VALUES (502,'db1/#sql-5a7a_8'
INSERT INTO `mysql`.`tablespace_files` VALUES (502,1,'./db1/t20251219_01.ibd','id=497;');

-- 更新旧表的信息
ha_innobase::update_row	tablespaces
ha_innobase::update_row	tablespace_files
ha_innobase::update_row	tables
ha_innobase::update_row	columns * 4 (4个字段,所以是 旧表)
ha_innobase::update_row	index_column_usage

-- 添加临时表信息
ha_innobase::write_row	tables
ha_innobase::write_row	columns * 5 (5个字段, 是新表)
ha_innobase::write_row	index_column_usage * 5 (新表)

-- 更新临时表信息
ha_innobase::update_row	tablespaces
ha_innobase::update_row	tablespace_files
ha_innobase::update_row	tables
ha_innobase::update_row	columns * 5(新表)
ha_innobase::update_row	index_column_usage * 5 (新表)

-- 删除旧表信息
ha_innobase::delete_row tablespace_files
ha_innobase::delete_row tablespaces
ha_innobase::delete_row index_column_usage * 4
ha_innobase::delete_row indexes 
ha_innobase::delete_row columns * 4
ha_innobase::delete_row tables

inplace

然后我们来瞅瞅inplace的逻辑(注意,此时表已经有2个字段了哟)

alter table t20251219_01 add column age int,algorithm=inplace;

一通操作之后得到如下逻辑:

-- 删除旧的表空间信息
ha_innobase::delete_row	tablespace_files	VALUES (502,1,'./db1/t20251219_01.ibd','id=497;')
ha_innobase::delete_row	tablespaces			VALUES (502,'db1/t20251219_01'

-- 添加新的表空间信息
ha_innobase::write_row	tablespaces			VALUES (503,'db1/t20251219_01'
ha_innobase::write_row	tablespace_files	VALUES (503,1,'./db1/t20251219_01.ibd','id=498;')

-- 删除旧的表信息
ha_innobase::delete_row	index_column_usage * 5
ha_innobase::delete_row	indexes
ha_innobase::delete_row	columns * 5
ha_innobase::delete_row	tables

-- 添加信息的表信息
ha_innobase::write_row	tables
ha_innobase::write_row	columns * 6
ha_innobase::write_row	indexes
ha_innobase::write_row	index_column_usage * 6

看起来就是系统表的一些update, 虽然是使用insert+delete做的. 并没有涉及到临时文件之类的, 毕竟是inplace嘛.

instant(8.0.19)

最后再来瞅瞅Instant的效果(现在已经有3个字段了)

alter table t20251219_01 add column birthday date,algorithm=instant;

又一通操作后我们得到如下逻辑:

-- 删除旧的表信息
ha_innobase::delete_row	index_column_usage * 6
ha_innobase::delete_row	indexes
ha_innobase::delete_row	columns * 6
ha_innobase::delete_row	tables

-- 添加新的表信息
ha_innobase::write_row	tables
ha_innobase::write_row	columns * 7
ha_innobase::write_row	indexes
ha_innobase::write_row	index_column_usage * 7

instant和inplace差不多, 区别就是不需要修改tabpespace之类的信息, 也就是只修改元数据信息即可.

删除字段 ALTER TABLE ADD COLUMN

删除字段也有instant(>=8.0.29),inplace,copy几种. 篇幅有限,就只看inplace吧(我测试版本是8.0.19,也没法看instant).

alter table t20251219_01 drop column age,algorithm=inplace;

再次操作后得到:

-- 删除旧的表空间信息
ha_innobase::delete_row	tablespace_files	VALUES (503,1,'./db1/t20251219_01.ibd'
ha_innobase::delete_row	tablespaces			VALUES (503,'db1/t20251219_01'

-- 添加新的表空间信息
ha_innobase::write_row	tablespaces			VALUES (504,1,'./db1/t20251219_01.ibd'
ha_innobase::write_row	tablespace_files	VALUES (504,'db1/t20251219_01'

-- 删除旧的表信息
ha_innobase::delete_row	index_column_usage * 7
ha_innobase::delete_row	indexes
ha_innobase::delete_row	columns * 7
ha_innobase::delete_row	tables

-- 添加信息的表信息
ha_innobase::write_row	tables
ha_innobase::write_row	columns * 6
ha_innobase::write_row	indexes
ha_innobase::write_row	index_column_usage * 6

和添加字段是反过来的.

新增索引 ALTER TABLE ADD INDEX

然后我们瞅瞅新增二级索引 (现在是没得主键/索引,字段有3个的情况)

alter table t20251219_01 add index(id),algorithm=inplace;

一通操作后得到:

-- 删除旧的表信息
ha_innobase::delete_row	index_column_usage * 6
ha_innobase::delete_row	indexes				VALUES (333,392,'PRIMARY','UNIQUE'
ha_innobase::delete_row	columns * 6
ha_innobase::delete_row	tables

-- 添加新的表信息
ha_innobase::write_row	tables
ha_innobase::write_row	columns * 6
ha_innobase::write_row	indexes				VALUES (334,393,'PRIMARY','UNIQUE'
ha_innobase::write_row	index_column_usage * 6

-- 新增的索引(普通索引是字段加上主键, 所以这里的index_column_usage是2)
ha_innobase::write_row	indexes				VALUES (335,393,'id','MULTIPLE'
ha_innobase::write_row	index_column_usage * 2

看起来和instant新增字段差不多, 就是多了个索引信息.

为啥不直接添加,非要删除后再添加呢? 不懂,但尊重

删除索引 ALTER TABLE DROP INDEX

刚才加了个索引, 我们现在就来给它删掉

alter table t20251219_01 drop index id,algorithm=inplace;

得到如下:

-- 删除旧的信息
ha_innobase::delete_row	index_column_usage * 8 (主键(6)和二级索引(2))
ha_innobase::delete_row	indexes * 2
ha_innobase::delete_row	columns * 6
ha_innobase::delete_row	tables

-- 插入新的信息
ha_innobase::write_row	tables
ha_innobase::write_row	columns * 6
ha_innobase::write_row	indexes
ha_innobase::write_row	index_column_usage * 6

和添加索引的逻辑一样.

清空表 TRUNCATE TABLE

虽然我们的表是空的,但我们也要测试下truncate操作(非空的话还有业务SQL的干扰)

truncate table t20251219_01;

效果如下:

-- 清空表空间信息
ha_innobase::delete_row	tablespace_files
ha_innobase::delete_row	tablespaces

-- 插入新的表空间信息
ha_innobase::write_row	tablespaces
ha_innobase::write_row	tablespace_files

-- 更新表信息
ha_innobase::update_row	tables  -- 修改的就是se_private_id之类的信息
ha_innobase::update_row	columns * 6
ha_innobase::update_row	indexes
ha_innobase::update_row	index_column_usage * 6

官网说truncate逻辑上和drop+create一样, 我们之前也验证过,确实inode信息会变. 但也就只有inode信息变化了. 从上面信息我们得到truncate实际上就是换了个表空间而已.

ibd2sql发现的tables变化如下: 只有se_private_id从1556变成了1557, 对应的就是columns表的se_private_data字段

< INSERT INTO `mysql`.`tables` VALUES (394,5,'t20251219_01','BASE TABLE','InnoDB',80019,'Dynamic',33,'','Visible','avg_row_length=0;encrypt_type=N;key_block_size=0;keys_disabled=0;pack_record=1;stats_auto_recalc=0;stats_sample_pages=0;',null,1556,null,null,null,null,null,null,null,null,null,'2025-12-19 17:18:21','2025-12-19 17:18:21',null,null,null,null,null,null,null,null,null,null,0);
---
> INSERT INTO `mysql`.`tables` VALUES (394,5,'t20251219_01','BASE TABLE','InnoDB',80019,'Dynamic',33,'','Visible','avg_row_length=0;encrypt_type=N;key_block_size=0;keys_disabled=0;pack_record=1;stats_auto_recalc=0;stats_sample_pages=0;',null,1557,null,null,null,null,null,null,null,null,null,'2025-12-19 17:18:21','2025-12-19 17:18:21',null,null,null,null,null,null,null,null,null,null,0);

删除表 DROP TABLE

测试差不多了, 就来看看drop表的效果吧. 其实猜都能猜到只有delete

drop table t20251219_01;

-- 删除表相关信息
ha_innobase::delete_row	tablespace_files
ha_innobase::delete_row	tablespaces
ha_innobase::delete_row	index_column_usage * 6
ha_innobase::delete_row	indexes
ha_innobase::delete_row	columns * 6
ha_innobase::delete_row	tables

没错,的确只有delete信息.

总结

虽然已经省略很多细节了(比如怎么找断点的),也只查看一些常见的DDL,但篇幅还是较长,gtid,统计信息和动态元数据信息就省略了.

tables columns indexes index_column_usage tablespaces tablespace_files
创建表 IU IU IU IU IU IU
新增字段(copy) ID ID ID ID ID ID
新增字段(inpalce) ID ID ID ID ID ID
新增字段(instant) ID ID ID ID
删除字段(inplace) ID ID ID ID ID ID
添加索引(inplace) ID ID ID ID
删除索引(inplace) ID ID ID ID
清空表(truncate) U U U U ID ID
删除表(drop) D D D D D D

D:delete,U:update,I:insert

创建表对应的系统表DML是insert+update, 删除表是delete. 剩下的基本上都是delete+insert的方式来更新系统表

参考:

https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_monitor_enable

https://dev.mysql.com/doc/refman/8.0/en/information-schema-innodb-metrics-table.html

https://dev.mysql.com/doc/dev/mysql-server/latest/classha__innobase.html

https://dev.mysql.com/doc/refman/8.0/en/data-dictionary-schema.html

https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html

墨力计划mysql
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

文章被以下合辑收录

PYTHON解析MYSQL（共65篇）
用python解析mysql协议,模拟mysql连接,解析binlog,redolog,ibd文件等

评论