导读
除了在数据字典中有元数据信息外, mysql还在ibd里面存储了该数据文件对应的表的元数据信息.这部分信息就叫做 Serialized Dictionary Information (SDI). 数据格式是我们常见的json格式.
如果是myisam存储引擎. SDI则是个单独的文件(tbl_name.sdi).
如果是innodb存储引擎, 则和数据放一起, 在第0-1个segment中, 相当于一行特殊的数据(只有text字段). 如果是直接在8.0环境创建的表,则通常位于第3页(原本cluster index的root page位置).
我们可以使用ibd2sdi工具去解析innodb表的sdi信息(general tablespace貌似不行), 解析出来的结果如下:
各key是什么意思呢? 并没有找到相关的文档说明, 所以我们本文主要就是来看各个key的含义.
SDI
之前我们说了sdi实际上就是json格式的数据. 大概结构如下:
本次环境基于mysql 8.0.28
[
"dd_object":{
"name":'表名',
"options":"表属性, 比如是否加密,统计信息等",
"check_constraints":"约束",
"collation_id":"表的排序规则(字符集)",
"columns":"字段",
"comment":"注释",
"created":"创建时间(utc) 每次更新也会变化",
"default_partitioning":"",
"default_subpartitioning":"",
"engine":"InnoDB",
"engine_attribute":"",
"foreign_keys":"外键",
"hidden":"",
"indexes":"索引",
"last_altered":"上一次alter时间",
"last_checked_for_upgrade_version_id":"mysql的旧版本号?",
"mysql_version_id":"50040",
"partition_expression":"",
"partition_expression_utf8":"",
"partition_type":"",
"partitions":"",
"row_format":2, # 2:DYNAMIC 3:COMPRESSED 4:REDUNDANT 5:COMPACT
"schema_ref":"库名",
"se_private_data":"",
"se_private_id":"",
"secondary_engine_attribute":"",
"subpartition_expression":"",
"subpartition_expression_utf8":"",
"subpartition_type":"",
},
"dd_object_type":"Table",
"dd_version":数据字段版本,比如80023
"mysqld_version_id":mysql的版本,比如80028,
"sdi_version":sdi版本, 比如80019
]
dd_object
dd_object就是主要的sdi信息了.
name
表名字
options
表的属性, 比如: "options":"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;"
encrypt_type 表示是否加密 N:不加密 Y:加密
pack_record checksum table时候需要的, 是否只有int类型. 感兴趣的可以看在这篇
详情可以查看官网CREATE TABLE部分中的table_option
table_option: {
AUTOEXTEND_SIZE [=] value
| AUTO_INCREMENT [=] value
| AVG_ROW_LENGTH [=] value
| [DEFAULT] CHARACTER SET [=] charset_name
| CHECKSUM [=] {0 | 1}
| [DEFAULT] COLLATE [=] collation_name
| COMMENT [=] 'string'
| COMPRESSION [=] {'ZLIB' | 'LZ4' | 'NONE'}
| CONNECTION [=] 'connect_string'
| {DATA | INDEX} DIRECTORY [=] 'absolute path to directory'
| DELAY_KEY_WRITE [=] {0 | 1}
| ENCRYPTION [=] {'Y' | 'N'}
| ENGINE [=] engine_name
| ENGINE_ATTRIBUTE [=] 'string'
| INSERT_METHOD [=] { NO | FIRST | LAST }
| KEY_BLOCK_SIZE [=] value
| MAX_ROWS [=] value
| MIN_ROWS [=] value
| PACK_KEYS [=] {0 | 1 | DEFAULT}
| PASSWORD [=] 'string'
| ROW_FORMAT [=] {DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT}
| START TRANSACTION
| SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
| STATS_AUTO_RECALC [=] {DEFAULT | 0 | 1}
| STATS_PERSISTENT [=] {DEFAULT | 0 | 1}
| STATS_SAMPLE_PAGES [=] value
| tablespace_option
| UNION [=] (tbl_name[,tbl_name]...)
}
check_constraints
约束相关的信息, 比如
"check_constraints": [ { "name": "test_ibd2sql_ddl_01_chk_1", "state": 2, "check_clause": "KChgaW50X2NvbGAgPiAwKSBhbmQgKGB0aW55aW50X2NvbGAgPiAwKSk=", "check_clause_utf8": "((`int_col` > 0) and (`tinyint_col` > 0))" } ],
check_clause是check表达式的base64格式. 我们也可以直接在INFORMATION_SCHEMA.CHECK_CONSTRAINTS中查询这个约束信息
(root@127.0.0.1) [db1]> select * from INFORMATION_SCHEMA.CHECK_CONSTRAINTS where CONSTRAINT_NAME='test_ibd2sql_ddl_01_chk_1'\G
*************************** 1. row ***************************
CONSTRAINT_CATALOG: def
CONSTRAINT_SCHEMA: db1
CONSTRAINT_NAME: test_ibd2sql_ddl_01_chk_1
CHECK_CLAUSE: ((`int_col` > 0) and (`tinyint_col` > 0))
1 row in set (0.00 sec)
(root@127.0.0.1) [db1]>
collation_id
排序规则id, 每个排序规则对应一个字符集. 我们可以使用show collection查看当前数据库支持的排序规则.
(root@127.0.0.1) [db1]> show collation where id=33;
+-----------------+---------+----+---------+----------+---------+---------------+
| Collation | Charset | Id | Default | Compiled | Sortlen | Pad_attribute |
+-----------------+---------+----+---------+----------+---------+---------------+
| utf8_general_ci | utf8 | 33 | Yes | Yes | 1 | PAD SPACE |
+-----------------+---------+----+---------+----------+---------+---------------+
1 row in set (0.00 sec)
常见的排序规则有:
utf8mb4_bin 46
utf8mb4_0900_ai_ci 255
当然我们也可以使用show charset查看字符集相关信息
(root@127.0.0.1) [db1]> show charset where Charset='utf8';
+---------+---------------+-------------------+--------+
| Charset | Description | Default collation | Maxlen |
+---------+---------------+-------------------+--------+
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
+---------+---------------+-------------------+--------+
maxlen=3表示utf8最多使用3字节表示某个字符. (其实就是utf8mb3中 mb3的意思: most bytes 3)
columns
表的字段, 这信息就非常多了, 待会拎出去单独讲.
comment
表的注释
created
创建时间, 实际上修改表结构之后也会变化, 所以不是真正的表创建时间. (该信息是不包含时区信息的, 东八区记得+8才是实际时间). 那么表的实际创建时间该怎么查看呢?
我们之前不是解析过xfs文件系统么, inode中的di_crtime即代表文件创建时间(很可惜正常途径无法查看, 但又不可惜, 因为我们的xfs_recovery_v0.3.py可以查看.)
10:43:43 [root@ddcw21 ibd2sql-main]#ibd2sdi /data/mysql_3314/mysqldata/db1/t20250120_2.ibd | grep -E 'created|last_altered' "created": 20250120021154, "last_altered": 20250120021154, 10:43:50 [root@ddcw21 ibd2sql-main]#ll -i /data/mysql_3314/mysqldata/db1/t20250120_2.ibd 278370822 -rw-r----- 1 mysql mysql 114688 Jan 20 10:11 /data/mysql_3314/mysqldata/db1/t20250120_2.ibd 10:43:57 [root@ddcw21 ibd2sql-main]# 10:44:08 [root@ddcw21 ibd2sql-main]#python3 xfs_recovery_v0.3.py /dev/mapper/centos-root 278370821 | grep time di_atime:2025-01-20 10:04:31 498909594 di_crtime:2025-01-20 09:51:35 438942877 di_ctime:2025-01-20 10:04:29 631909674 di_mtime:2025-01-20 10:04:29 631909674
default_partitioning
分区类型? 1:list,range 3:key,hash 0:没得分区
default_subpartitioning
同default_partitioning 只不过是子分区
engine
存储引擎名字, 通常是innodb
engine_attribute
存储引擎的属性, 8.0.21开始支持的, 没使用过.
foreign_keys
外键信息. 比如:
"foreign_keys": [ { "name": "test_ibd2sql_ddl_01_ibfk_1", "match_option": 1, "update_rule": 1, "delete_rule": 1, "unique_constraint_name": "PRIMARY", "referenced_table_catalog_name": "def", "referenced_table_schema_name": "db1", "referenced_table_name": "test_ibd2sql_ddl_00", "elements": [ { "column_opx": 0, "ordinal_position": 1, "referenced_column_name": "id" } ] } ],
referenced_table_schema_name 外键所在的schema
referenced_table_name 外键所在的table
elements 具体的外键字段(字段位置,字段名字)
indexes
索引也是各大头, 待会拎出来单独讲
last_altered
同created
last_checked_for_upgrade_version_id
上次检查或者升级时mysql单独版本?
mysql_version_id
mysql的版本号, 比如80028
partition_expression
分区的表达式: 比如
"partition_expression": "year(`age_y`)"
partition_expression_utf8
同partition_expression
partition_type
分区类型.
1: hash
3: key
7: range
8: list
partitions
具体的分区内容了, 比如
"partitions": [ { "name": "p0", "parent_partition_id": 18446744073709551615, "number": 0, "se_private_id": 54626, "description_utf8": "", "engine": "InnoDB", "comment": "", "options": "", "se_private_data": "", "values": [], "indexes": [ { "options": "", "se_private_data": "id=56968;root=4;space_id=50579;table_id=54626;trx_id=58748295;", "index_opx": 0, "tablespace_ref": "ibd2sql_t1/ddcw_test_p_hash#p#p0" } ], "subpartitions": [] }, { "name": "p1", "parent_partition_id": 18446744073709551615, "number": 1, "se_private_id": 54627, "description_utf8": "", "engine": "InnoDB", "comment": "", "options": "", "se_private_data": "", "values": [], "indexes": [ { "options": "", "se_private_data": "id=56969;root=4;space_id=50580;table_id=54627;trx_id=58748295;", "index_opx": 0, "tablespace_ref": "ibd2sql_t1/ddcw_test_p_hash#p#p1" } ], "subpartitions": [] }, { "name": "p2", "parent_partition_id": 18446744073709551615, "number": 2, "se_private_id": 54628, "description_utf8": "", "engine": "InnoDB", "comment": "", "options": "", "se_private_data": "", "values": [], "indexes": [ { "options": "", "se_private_data": "id=56970;root=4;space_id=50581;table_id=54628;trx_id=58748295;", "index_opx": 0, "tablespace_ref": "ibd2sql_t1/ddcw_test_p_hash#p#p2" } ], "subpartitions": [] }, { "name": "p3", "parent_partition_id": 18446744073709551615, "number": 3, "se_private_id": 54629, "description_utf8": "", "engine": "InnoDB", "comment": "", "options": "", "se_private_data": "", "values": [], "indexes": [ { "options": "", "se_private_data": "id=56971;root=4;space_id=50582;table_id=54629;trx_id=58748295;", "index_opx": 0, "tablespace_ref": "ibd2sql_t1/ddcw_test_p_hash#p#p3" } ], "subpartitions": [] } ],
name: 分区名字
parent_partition_id: 分区id
number: 位置,顺序
se_private_id: 表id
indexes: 这个分区对于的ibd文件的相关信息,(主要是spaceid,和root)
subpartition: 子分区相关信息.
row_format
行格式 2:DYNAMIC 3:COMPRESSED 4:REDUNDANT 5:COMPACT
目前这4种格式我们都解析过的. 有兴趣的可以看下之前的文章, 当然后面也会重新梳理一下.
schema_ref
对于的库名字
se_private_data
一些内部信息, 比如online ddl. 8.0.28及其之前添加新字段时(alter table db1.t20250120_2 add column name varchar(200), ALGORITHM=INSTANT), 会有:"se_private_data": "instant_col=1;"
se_private_id
表id 每张表都有各id. 内部使用的, 比如刚才分区表那里就有表的id. 在使用alter table import tablespace的时候也会查看这个table id
secondary_engine_attribute
二级存储引擎属性?
subpartition_expression
子分区表达式, 和分区表达式类似.
subpartition_expression_utf8
同 subpartition_expression
subpartition_type
子分区类型
dd_object_type
dd对象的类型, 通常是table
dd_version
dd的版本, 比如: 80023
mysqld_version_id
mysql的版本, 同dd_object[‘mysql_version_id’]
sdi_version
sdi的版本, 比如80019
COLUMNS
接下来看看关键的字段信息. 结构是list, 每个字段一个元素. 每个列的属性都是一样多的, 只不过值不一样. 各元素 的顺序是逻辑顺序, 即表的字段顺序, 实际存储顺序按索引中的顺序为主. 隐藏列通常放到后面
name
字段名字, 有几个特殊的名字需要注意下:
DB_ROW_ID 如果没得主键(也没得唯一索引), 就会自动创建这个字段来作为主键.
DB_TRX_ID 事务ID
DB_ROLL_PTR 回滚指针, 对应的undo log (怎么计算对应的undo log,我们之前也讲过的, 有兴趣的可以翻翻)
!hidden!_dropped_v2_p3_id 被删除的列. v2:被删除之后的row_version, p3:被删除之前的位置(rowid,trxid,rollptr,id),可以推断之前是第一个列
my_row_id 如果启用了sql_generate_invisible_primary_key, 并且没有指定主键, 就会自动创建个my_row_id (和之前的db_row_id好像差不多哎)
type
字段类型. 该类型更偏向innodb的类型. 和frm里面的类型差别也很大
INNODB_TYPE = {
2: 'tinyint',
3: 'smallint',
4: 'int',
5: 'float',
6: 'double',
9: 'bigint',
10:'mediumint',
14:'year',
15:'date',
16:'varbinary', #varchar
17:'bit',
18:'timestamp',
19:'datetime',
20:'time',
21:'decimal',
22:'enum',
23:'set',
24:'tinyblob', #tinytext
25:'mediumblob', #mediumtext
26:'longblob', #longtext
27:'blob', #text
29:'char', # not binary 虽然和char都是29, 但存储方式不同.... -_-
30:'geom', # 坐标之力
31:'json',
32:'vector' # 向量
}
my_row_id:9
DB_TRX_ID: 10
DB_ROLL_PTR:9
DB_ROW_ID:10
is_nullable
字段能否为空, 即非空约束
is_zerofill
对于int之类的类型, 是否要使用0填充. 只是显示问题,不影响数据存储
is_unsigned
对于int之类可能有符号的类型, 是否是无符号
is_auto_increment
是否自增
is_virtual
是否是虚拟列, 即生成列. 分为2种:
STORED: 数据存储在磁盘上. 解析的时候要注意
VIRTUAL: 是通过计算出来的值, 不存储在磁盘上.
hidden
是否隐藏
2: 隐藏, 常见于DB_ROW_ID等内部字段(被删除的字段也是2). 要显示出来时会加上 /*!80023 INVISIBLE */
1: 不隐藏, 正常显示的字段
ordinal_position
字段的相对为主, 就是list中的位置, 但是是从1开始计数.
char_length
对于int等不变类型来说, 就是字符长度, 比如int(11)
对于varchar等变长类型来说,就是字节长度, 比如varchar(200)就是"char_length": 600
numeric_precision
数字类型精度, 比如decimal(20,3) ,则numeric_precision:20
numeric_scale
小数部分, 比如decimal(20,3), 则numeric_scale:3
numeric_scale_null
是否存在数字精度信息,
false: 存在精度, 通常是数字类型
true: 不存在, 通常是非数字类型, 比如varchar
datetime_precision
时间类型精度, mysqlfrm就是没有去计算这部分信息…
比如datetime(5), 则"datetime_precision": 5
datetime_precision_null
是否存在时间精度问题, 存在时间精度的往往是涉及到秒之后的小数部分. 目前有datetime, time, timestamp这3种类型有时间精度.
has_no_default
是否没得默认值, mysql创建字段时,不指定默认值,则默认值为空, 相当于至少有个DEFAULT NULL. 也就是始终有默认值, 即has_no_default=false.
default_value_null
默认值是否为空.
srs_id_null
srs_id是否为空. (空间坐标字段使用的)
srs_id
srs_id的值. 比如create table db1.t20250120_geom(a point SRID 4326 default null); 则"srs_id": 4326
default_value
字段的默认值, 是base64格式的.
default_value_utf8_null
字段默认值是否为空, 就是是否需要读default_value_utf8
default_value_utf8
字段默认值的utf8格式.
default_option
一些字段的选项. 比如"default_option": "CURRENT_TIMESTAMP"
update_option
一些更新时的属性. 比如create table t20250120_update(id int, last_update datetime default current_timestamp on update current_timestamp); 则"update_option": "CURRENT_TIMESTAMP" 拼接DDL的时候要注意.
13:50:02 [root@ddcw21 ibd2sql-main]#python3 main.py /data/mysql_3314/mysqldata/db1/t20250120_update.ibd CREATE TABLE IF NOT EXISTS `db1`.`t20250120_update`( `id` int NULL, `last_update` datetime DEFAULT (CURRENT_TIMESTAMP) ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci ;
comment
字段的注释
generation_expression
生成表达式, 对于生成列而言的. 比如create table t20250120_gen(id int, id2 int GENERATED ALWAYS AS (id+1)); 则
"generation_expression": "(`id` + 1)"
generation_expression_utf8
同generation_expression
options
一些选项, 比如"options": "interval_count=3;" 中的interval_count表示的是这个set/enum有3个元素. frm中也是有这些定义的.
int_count is the number of unique ENUM and SET definitions
对于bit类型, 还会看见: treat_bit_as_char=1
se_private_data
一些内部信息, 比如"physical_pos=4;version_added=1;version_dropped=2;"表示
physical_pos=4: 物理位置:4 (主键中的位置)
version_added=1 添加这个字段之后,row_version=1
version_dropped=2 删除这个字段之后, row_version=2
没得主键的时候,基本上可以确定这个字段是第2个字段(rowid,trxid,rollptr,col1,col2). 并且是online ddl添加来的, 然后接着又删除了.
没得row_version信息时,还会有tableid (添加row_version信息后就把tableid干掉了?感觉像是bug)
engine_attribute
存储引擎的一些属性, 类似SECONDARY_ENGINE_ATTRIBUTE?
secondary_engine_attribute
存储引擎的又一个属性?
column_key
这个字段所在索引的类型.
1: 不是索引
2: 主键
3: 唯一索引
4: 普通索引
一个字段可能同时在多个索引里面, 按照主键,唯一索引这样的顺序来.
column_type_utf8
字段类型的utf8格式, 比如varchar(200)
elements
set/enum中的元素, 比如
"column_type_utf8": "enum('a','b','c')", "elements": [ { "name": "YQ==", "index": 1 }, { "name": "Yg==", "index": 2 }, { "name": "Yw==", "index": 3 } ],
这里面的name是base64格式. index则是该值的顺序.
collation_id
排序规则, 每个字段都有自己的字符集和排序规则. 如果没有显示指定, 则是继承table的.
is_explicit_collation
是否是显示指定的排序规则. 不管是否和默认值相同. 比如
(root@127.0.0.1) [db1]> show create table t20250120_27;
+--------------+-----------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------------+-----------------------------------------------------------------------------------------------+
| t20250120_27 | CREATE TABLE `t20250120_27` (
`id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
+--------------+-----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
(root@127.0.0.1) [db1]> create table t20250120_28(id int);
Query OK, 0 rows affected (0.01 sec)
(root@127.0.0.1) [db1]> show create table t20250120_28;
+--------------+-----------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------------+-----------------------------------------------------------------------------------------------+
| t20250120_28 | CREATE TABLE `t20250120_28` (
`id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
+--------------+-----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
这两张表看起来是完全一样的. 但是sdi中的is_explicit_collation却不同
INDEXES
终于轮到索引了. 索引和字段类似的. 只是各个key不同. mysql数据存储是按照主键来存储的. 即使没有手动指定主键, 也会自动创建主键.
name
索引的名字, PRIMARY就代表是主键. 其它索引名字随意, 不指定索引时,通常是字段名字
hidden
这个索引是否可见. 即是否在DDL中能看见. 不指定主键的时候, 创建的就是hidden=True (也就是rowid是主键)
全文索引也是hidden的
is_generated
是否是自动生成的. 比如某个字段是外键, 就会自动给它建一个索引, 就是is_generated
ordinal_position
索引的位置. 也是从1开始.
comment
索引的注释
options
一些选项, 比如"options": "flags=0;gipk=1;" 就表示是自动创建的这个索引(GIPK)
se_private_data
索引的一些内部信息, 比如 "id=10205;root=4;space_id=10052;table_id=11114;trx_id=138107;"
id:索引id
root:索引的root page位置
space_id: 索引位于哪个表空间内
table_id: 索引对于的表id
type
索引类型. 也可以叫索引前缀
1: 主键
2: 唯一键
3: 普通索引
4: FULLTEXT
5: SPATIAL
algorithm
索引算法? 2:btree+ 3:SPATIAL 5:FULLTEXT
is_algorithm_explicit
是否显示指定的索引算法.
is_visible
索引是否可见. /*!80000 INVISIBLE */
engine
存储引擎
engine_attribute
存储引擎的属性
secondary_engine_attribute
存储引擎的又一个属性
elements
具体的索引字段. 如果是主键索引, 要包含主键和剩下的普通字段. 如果是其它索引,要包含索引值和主键值(即使是rowid). 如果是前缀索引, 则索引的完整值位于剩下字段位置.
ordinal_position: 这个元素/字段位于这个索引的哪个位置, 从1开始
length: 索引字段长度. 如果小于实际字段长度,则为前缀索引
order: 索引的顺序. 2:升序 3:降序
hidden: 是否隐藏. 可以用来区分这个字段是索引还是PK/剩余字段. True:普通字段/主键, False:索引字段
column_opx: 对应的哪个字段(从0开始算…)
tablespace_ref
这个索引对应的表
总结
mysql的sdi信息差不多就是这些了, 根据这些信息就可以人工拼接出相关的DDL了. 就是有丢丢麻烦, 所以我们可以使用现成的工具. 之前有讲过可以使用哪些工具.
不复杂, 但是量多. 而且很多信息平时几乎用不上.
对于general tablespace的表, 可能无法使用ibd2sdi去解析.可以使用我们之前解析mysql.ibd时候的脚本.
