[MYSQL] frm2sdi (2) sdi内容讲解

[MYSQL] frm2sdi (2) sdi内容讲解

导读

除了在数据字典中有元数据信息外, mysql还在ibd里面存储了该数据文件对应的表的元数据信息.这部分信息就叫做 Serialized Dictionary Information (SDI). 数据格式是我们常见的json格式.

如果是myisam存储引擎. SDI则是个单独的文件(tbl_name.sdi).

如果是innodb存储引擎, 则和数据放一起, 在第0-1个segment中, 相当于一行特殊的数据(只有text字段). 如果是直接在8.0环境创建的表,则通常位于第页(原本cluster index的root page位置).

我们可以使用ibd2sdi工具去解析innodb表的sdi信息(general tablespace貌似不行), 解析出来的结果如下:

各key是什么意思呢? 并没有到相关的文档说明, 所以我们本文主要就是来看各个key的含义.

SDI

之前我们说了sdi实际上就是json格式的数据. 大概结构如下:

本次环境基于mysql 8.0.28

[
	"dd_object":{
                "name":'表名',
                "opti":"表属性, 比如是否加密,统计信息等",
                "check_ctraints":"约束",
                "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":"",
                "partiti":"",
                "row_format":2, # 2:DYAMIC :COMPRESSED 4:REDUDAT 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":数据字段版本,比如8002
        "mysqld_version_id":mysql的版本,比如80028,
        "sdi_version":sdi版本, 比如80019
]

dd_object就是主要的sdi信息了.

name

表名字

opti

表的属性, 比如: "opti":"avg_row_length=0;encrypt_type=;key_block_size=0;keys_disabled=0;pack_record=1;stats_auto_recalc=0;stats_sample_pages=0;"

encrypt_type 表示是否加密 :不加密 Y:加密

pack_record checksum table时候需要的, 是否只有int类型. 感兴趣的可以看在这篇

详情可以查看CREATE TABLE部分中的table_option

table_option: {
    AUTOEXTED_SIZE [=] value
  | AUTO_ICREMET [=] value
  | AVG_ROW_LEGTH [=] value
  | [DEFAULT] CHARACTER SET [=] charset_name
  | CHECKSUM [=] {0 | 1}
  | [DEFAULT] COLLATE [=] collation_name
  | COMMET [=] 'string'
  | COMPRESSIO [=] {'ZLIB' | 'LZ4' | 'OE'}
  | COECTIO [=] 'connect_string'
  | {DATA | IDEX} DIRECTORY [=] 'absolute path to directory'
  | DELAY_KEY_WRITE [=] {0 | 1}
  | ECRYPTIO [=] {'Y' | ''}
  | EGIE [=] engine_name
  | EGIE_ATTRIBUTE [=] 'string'
  | ISERT_METHOD [=] { O | FIRST | LAST }
  | KEY_BLOCK_SIZE [=] value
  | MAX_ROWS [=] value
  | MI_ROWS [=] value
  | PACK_KEYS [=] {0 | 1 | DEFAULT}
  | PASSWORD [=] 'string'
  | ROW_FORMAT [=] {DEFAULT | DYAMIC | FIXED | COMPRESSED | REDUDAT | COMPACT}
  | START TRASACTIO 
  | SECODARY_EGIE_ATTRIBUTE [=] 'string'
  | STATS_AUTO_RECALC [=] {DEFAULT | 0 | 1}
  | STATS_PERSISTET [=] {DEFAULT | 0 | 1}
  | STATS_SAMPLE_PAGES [=] value
  | tablespace_option
  | UIO [=] (tbl_name[,tbl_name]...)
}

check_ctraints

约束相关的信息, 比如

        "check_ctraints": [
            {
                "name": "test_ibd2sql_ddl_01_chk_1",
                "state": 2,
                "check_clause": "KChgaW50X2vbGAgPiAwKSBhbmQgKGB0aW55aW50X2vbGAgPiAwKSk=",
                "check_clause_utf8": "((`int_col` > 0) and (`tinyint_col` > 0))"
            }
        ],

check_clause是check表达式的base64格式. 我们也可以直接在IFORMATIO_SCHEMA.CHECK_COSTRAITS中查询这个约束信息

(root@127.0.0.1) [db1]> select * from IFORMATIO_SCHEMA.CHECK_COSTRAITS where COSTRAIT_AME='test_ibd2sql_ddl_01_chk_1'\G

*************************** 1. row ***************************
COSTRAIT_CATALOG: def
 COSTRAIT_SCHEMA: db1
   COSTRAIT_AME: 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=;
+-----------------+---------+----+---------+----------+---------+---------------+
| Collation       | Charset | Id | Default | Compiled | Sortlen | Pad_attribute |
+-----------------+---------+----+---------+----------+---------+---------------+
| utf8_general_ci | utf8    |  | 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   |       |
+---------+---------------+-------------------+--------+

maxlen=表示utf8最多使用字节表示某个字符. (其实就是utf8mb中 mb的意思: most bytes )

columns

表的字段, 这信息就非常多了, 待会拎出去单独讲.

comment

表的注释

created

创建时间, 实际上修改表结构之后也会变化, 所以不是真正的表创建时间. (该信息是不包含时区信息的, 东八区记得+8才是实际时间). 那么表的实际创建时间该怎么查看呢?

我们之前不是解析过xfs文件系统么, inode中的di_crtime即代表文件创建时间(很可惜正常途径无法查看, 但又不可惜, 因为我们的xfs_recovery_v0..py可以查看.)

10:4:4 [root@ddcw21 ibd2sql-main]#ibd2sdi /data/mysql_14/mysqldata/db1/t20250120_2.ibd | grep -E 'created|last_altered'
        "created": 20250120021154,
        "last_altered": 20250120021154,
10:4:50 [root@ddcw21 ibd2sql-main]#ll -i /data/mysql_14/mysqldata/db1/t20250120_2.ibd
27870822 -rw-r----- 1 mysql mysql 114688 Jan 20 10:11 /data/mysql_14/mysqldata/db1/t20250120_2.ibd
10:4:57 [root@ddcw21 ibd2sql-main]#
10:44:08 [root@ddcw21 ibd2sql-main]#python xfs_recovery_v0..py /dev/mapper/centos-root 27870821 | grep time
di_atime:2025-01-20 10:04:1 498909594
di_crtime:2025-01-20 09:51:5 48942877
di_ctime:2025-01-20 10:04:29 61909674
di_mtime:2025-01-20 10:04:29 61909674

default_partitioning

分区类型? 1:list,range :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_ctraint_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

: key

7: range

8: list

partiti

具体的分区内容了, 比如

"partiti": [
            {
                "name": "p0",
                "parent_partition_id": 1844674407709551615,
                "number": 0,
                "se_private_id": 54626,
                "description_utf8": "",
                "engine": "InnoDB",
                "comment": "",
                "opti": "",
                "se_private_data": "",
                "values": [],
                "indexes": [
                    {
                        "opti": "",
                        "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"
                    }
                ],
                "subpartiti": []
            },
            {
                "name": "p1",
                "parent_partition_id": 1844674407709551615,
                "number": 1,
                "se_private_id": 54627,
                "description_utf8": "",
                "engine": "InnoDB",
                "comment": "",
                "opti": "",
                "se_private_data": "",
                "values": [],
                "indexes": [
                    {
                        "opti": "",
                        "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"
                    }
                ],
                "subpartiti": []
            },
            {
                "name": "p2",
                "parent_partition_id": 1844674407709551615,
                "number": 2,
                "se_private_id": 54628,
                "description_utf8": "",
                "engine": "InnoDB",
                "comment": "",
                "opti": "",
                "se_private_data": "",
                "values": [],
                "indexes": [
                    {
                        "opti": "",
                        "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"
                    }
                ],
                "subpartiti": []
            },
            {
                "name": "p",
                "parent_partition_id": 1844674407709551615,
                "number": ,
                "se_private_id": 54629,
                "description_utf8": "",
                "engine": "InnoDB",
                "comment": "",
                "opti": "",
                "se_private_data": "",
                "values": [],
                "indexes": [
                    {
                        "opti": "",
                        "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#p"
                    }
                ],
                "subpartiti": []
            }
        ],

name: 分区名字

parent_partition_id: 分区id

number: 位置,顺序

se_private_id: 表id

indexes: 这个分区对于的ibd文件的相关信息,(主要是spaceid,和root)

subpartition: 子分区相关信息.

row_format

行格式 2:DYAMIC :COMPRESSED 4:REDUDAT 5:COMPACT

目前这4种格式我们都解析过的. 有兴趣的可以看下之前的文章, 当然后面也会重新梳理一下.

schema_ref

对于的库名字

se_private_data

一些内部信息, 比如online ddl. 8.0.28及其之前添加新字段时(alter table _2 add column name varchar(200), ALGORITHM=ISTAT), 会有:"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对象的类型, 通常是table

dd的版本, 比如: 8002

mysql的版本, 同dd_object'mysql_version_id'

sdi的版本, 比如80019

COLUMS

接下来看看关键的字段信息. 结构是list, 每个字段一个元素. 每个列的属性都是一样多的, 只不过值不一样. 各元素 的顺序是逻辑顺序, 即表的字段顺序, 实际存储顺序按索引中的顺序为主. 隐藏列通常放到后面

字段名字, 有几个特殊的名字需要注意下:

DB_ROW_ID 如果没得主键(也没得唯一索引), 就会自动创建这个字段来作为主键.

DB_TRX_ID 事务ID

DB_ROLL_PTR 回滚指针, 对应的undo log (怎么计算对应的undo log,我们之前也讲过的, 有兴趣的可以翻翻)

!hidden!_dropped_v2_p_id 被删除的列. v2:被删除之后的row_version, p:被删除之前的位置(rowid,trxid,rollptr,id),可以推断之前是第一个列

my_row_id 如果启用了sql_generate_invisible_primary_key, 并且没有指定主键, 就会自动创建个my_row_id (和之前的db_row_id好像差不多哎)

字段类型. 该类型更偏向innodb的类型. 和frm里面的类型差别也很大

IODB_TYPE = {
        2: 'tinyint',
        : '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',
        2:'set',
        24:'tinyblob', #tinytext
        25:'mediumblob', #mediumtext
        26:'longblob', #longtext
        27:'blob', #text
        29:'char', # not binary 虽然和char都是29, 但存储方式不同.... -_-
        0:'geom', # 坐标之力
        1:'json',
        2:'vector' # 向量
}

my_row_id:9

DB_TRX_ID: 10

DB_ROLL_PTR:9

DB_ROW_ID:10

字段能否为空, 即非空约束

对于int之类的类型, 是否要使用0填充. 只是显示问题,不影响数据存储

对于int之类可能有符号的类型, 是否是无符号

是否自增

是否是虚拟列, 即生成列. 分为2种:

STORED: 数据存储在磁盘上. 解析的时候要注意

VIRTUAL: 是通过计算出来的值, 不存储在磁盘上.

是否隐藏

2: 隐藏, 常见于DB_ROW_ID等内部字段(被删除的字段也是2). 要显示出来时会加上 /!8002 IVISIBLE /

1: 不隐藏, 正常显示的字段

字段的相对为主, 就是list中的位置, 但是是从1开始计数.

对于int等不变类型来说, 就是字符长度, 比如int(11)

对于varchar等变长类型来说,就是字节长度, 比如varchar(200)就是"char_length": 600

数字类型精度, 比如decimal(20,) ,则numeric_precision:20

小数部分, 比如decimal(20,), 则numeric_scale:

是否存在数字精度信息,

false: 存在精度, 通常是数字类型

true: 不存在, 通常是非数字类型, 比如varchar

时间类型精度, mysqlfrm就是没有去计算这部分信息....

比如datetime(5), 则"datetime_precision": 5

是否存在时间精度问题, 存在时间精度的往往是涉及到秒之后的小数部分. 目前有datetime, time, timestamp这种类型有时间精度.

是否没得默认值, mysql创建字段时,不指定默认值,则默认值为空, 相当于至少有个DEFAULT ULL. 也就是始终有默认值, 即has_no_default=false.

默认值是否为空.

srs_id是否为空. (空间坐标字段使用的)

srs_id的值. 比如create table _geom(a point SRID 426 default null); 则"srs_id": 426

字段的默认值, 是base64格式的.

字段默认值是否为空, 就是是否需要读default_value_utf8

字段默认值的utf8格式.

一些字段的选项. 比如"default_option": "CURRET_TIMESTAMP"

一些更新时的属性. 比如create table t20250120_update(id int, last_update datetime default current_timestamp on update current_timestamp); 则"update_option": "CURRET_TIMESTAMP" 拼接DDL的时候要注意.

1:50:02 [root@ddcw21 ibd2sql-main]#python main.py /data/mysql_14/mysqldata/db1/t20250120_update.ibd 
CREATE TABLE IF OT EXISTS `db1`.`t20250120_update`(
    `id` int ULL,
    `last_update` datetime DEFAULT (CURRET_TIMESTAMP) O UPDATE CURRET_TIMESTAMP
) EGIE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci ;

字段的注释

生成表达式, 对于生成列而言的. 比如create table t20250120_gen(id int, id2 int GEERATED ALWAYS AS (id+1)); 则

"generation_expression": "(`id` + 1)"

同generation_expression

一些选项, 比如"opti": "interval_count=;" 中的interval_count表示的是这个set/enum有个元素. frm中也是有这些定义的.

int_count is the number of unique EUM and SET definiti

对于bit类型, 还会看见: treat_bit_as_char=1

一些内部信息, 比如"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)

存储引擎的一些属性, 类似SECODARY_EGIE_ATTRIBUTE?

存储引擎的又一个属性?

这个字段所在索引的类型.

1: 不是索引

2: 主键

: 唯一索引

4: 普通索引

一个字段可能同时在多个索引里面, 按照主键,唯一索引这样的顺序来.

字段类型的utf8格式, 比如varchar(200)

set/enum中的元素, 比如

                "column_type_utf8": "enum('a','b','c')",
                "elements": [
                    {
                        "name": "YQ==",
                        "index": 1
                    },
                    {
                        "name": "Yg==",
                        "index": 2
                    },
                    {
                        "name": "Yw==",
                        "index": 
                    }
                ],

这里面的name是base64格式. index则是该值的顺序.

排序规则, 每个字段都有自己的字符集和排序规则. 如果没有显示指定, 则是继承table的.

是否是显示指定的排序规则. 不管是否和默认值相同. 比如

(root@127.0.0.1) [db1]> show create table t20250120_27;
+--------------+-----------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                  |
+--------------+-----------------------------------------------------------------------------------------------+
| t20250120_27 | CREATE TABLE `t20250120_27` (
  `id` int DEFAULT ULL
) EGIE=InnoDB DEFAULT CHARSET=utf8mb |
+--------------+-----------------------------------------------------------------------------------------------+
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 ULL
) EGIE=InnoDB DEFAULT CHARSET=utf8mb |
+--------------+-----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

这两张表看起来是完全一样的. 但是sdi中的is_explicit_collation却不同

IDEXES

终于轮到索引了. 索引和字段类似的. 只是各个key不同. mysql数据存储是按照主键来存储的. 即使没有手动指定主键, 也会自动创建主键.

索引的名字, PRIMARY就代表是主键. 其它索引名字随意, 不指定索引时,通常是字段名字

这个索引是否可见. 即是否在DDL中能看见. 不指定主键的时候, 创建的就是hidden=True (也就是rowid是主键)

全文索引也是hidden的

是否是自动生成的. 比如某个字段是外键, 就会自动给它建一个索引, 就是is_generated

索引的位置. 也是从1开始.

索引的注释

一些选项, 比如"opti": "flags=0;gipk=1;" 就表示是自动创建的这个索引(GIPK)

索引的一些内部信息, 比如 "id=10205;root=4;space_id=10052;table_id=11114;trx_id=18107;"

id:索引id

root:索引的root page位置

space_id: 索引位于哪个表空间内

table_id: 索引对于的表id

索引类型. 也可以叫索引前缀

1: 主键

2: 唯一键

: 普通索引

4: FULLTEXT

5: SPATIAL

索引算法? 2:btree+ :SPATIAL 5:FULLTEXT

是否显示指定的索引算法.

索引是否可见. /!80000 IVISIBLE /

存储引擎

存储引擎的属性

存储引擎的又一个属性

具体的索引字段. 如果是主键索引, 要包含主键和剩下的普通字段. 如果是其它索引,要包含索引值和主键值(即使是rowid). 如果是前缀索引, 则索引的完整值位于剩下字段位置.

ordinal_position: 这个元素/字段位于这个索引的哪个位置, 从1开始

length: 索引字段长度. 如果小于实际字段长度,则为前缀索引

order: 索引的顺序. 2:升序 :降序

hidden: 是否隐藏. 可以用来区分这个字段是索引还是PK/剩余字段. True:普通字段/主键, False:索引字段

column_opx: 对应的哪个字段(从0开始算...)

这个索引对应的表

总结

mysql的sdi信息差不多就是这些了, 根据这些信息就可以人工拼接出相关的DDL了. 就是有丢丢麻烦, 所以我们可以使用现成的工具. 之前有讲过可以使用哪些工具.

不复杂, 但是量多. 而且很多信息平时几乎用不上.

对于general tablespace的表, 可能无法使用ibd2sdi去解析.可以使用我们之前解析mysql.ibd时候的脚本.

参考:

.0/en/create-table.html