MySQL 8 的 Serialized Dictionary Information (SDI)
2023-10-19 09:52 abce 阅读(702) 评论(0) 收藏 举报
除了将数据库对象的元数据存储在数据目录中，mysql还支持以序列化的格式进行存储。即序列化数据目录信息(SDI)，SDI是表和表空间对象的序列化元数据。
innodb将SDI数据存储在自己的表空间中；ndbcluster将SDI数据存储在ndb目录中；其它存储引擎将SDI数据存储在.SDI文件中。
SDI数据以json格式生成。
SDI数据存在于innodb的所有表空间中，临时表空间和undo表空间除外。表空间中的SDI记录只是用来描述表空间中的表和表空间对象。
SDI数据跟着ddl操作或check table for upgrade操作更新。升级mysql server到新的版本不会更新SDI数据。
SDI数据的存在提供了元数据冗余。例如，如果数据字典不可用，可以从表空间文件中提取字典对象元数据。可以使用工具ibd2sdi直接从innodb的表空间文件中抽取出元数据信息。
在表空间文件中包含SDI数据会增加表空间文件的大小。对于innodb，一条SDI记录需要一个索引页，默认大小为 16KB。不过，SDI数据在存储时会进行压缩，以减少存储空间占用。不同的存储引擎，处理.SDI文件的方式也不同。
对于分区的innodb表，有多个表空间组成，SDI数据存储在第一个分区表空间文件中。
mysql server使用内部的api创建和维护SDI记录。
import table命令根据.SDI文件中信息导入myisam表。
ibd2sdi工具
ibd2sdi工具可以应用于独立表空间文件(*.ibd)、通用表空间文件(*.ibd)、系统表空间文件(*.ibdata),以及数据目录表空间(mysql.ibd)
ibd2sdi可以在线、离线使用。在ddl、回滚、以及undo日志purge操作时执行ibd2sdi可能会有短暂的读不到sdi数据。
ibd2sdi从指定的表空间执行未提交的SDI读取。不会访问重做日志和撤销日志。
帮助查看
$ ibd2sdi --help
ibd2sdi Ver 8.0.30 for Linux on x86_64 (MySQL Community Server - GPL)
Copyright (c) 2015, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Usage: ibd2sdi [-v] [-c <strict-check>] [-d <dump file name>] [-n] filename1 [filenames]
See http://dev.mysql.com/doc/refman/8.0/en/ibd2sdi.html for usage hints.
-h, --help Display this help and exit.
-v, --version Display version information and exit.
-d, --dump-file=name
Dump the tablespace SDI into the file passed by user.
Without the filename, it will default to stdout
-s, --skip-data Skip retrieving data from SDI records. Retrieve only id
and type.
-i, --id=# Retrieve the SDI record matching the id passed by user.
-t, --type=# Retrieve the SDI records matching the type passed by
user.
-c, --strict-check=name
Specify the strict checksum algorithm by the user.
Allowed values are innodb, crc32, none.
-n, --no-check Ignore the checksum verification.
-p, --pretty Pretty format the SDI output.If false, SDI would be not
human readable but it will be of less size
(Defaults to on; use --skip-pretty to disable.)
Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
--------------------------------- ----------------------------------------
dump-file (No default value)
skip-data FALSE
id 0
type 0
strict-check crc32
no-check FALSE
pretty TRUE
ibd2sdi支持多文件的表空间，比如系统表空间，但同一时刻只能在一个表空间上执行。比如：
ibd2sdi ibdata1 ibdata2
测试
>create database abce; Query OK, 1 row affected (0.00 sec) >use abce; Database changed >CREATE TABLE t1 (c VARCHAR(16) NOT NULL); Query OK, 0 rows affected (0.02 sec) >CREATE TABLE t2 (c VARCHAR(16) NOT NULL DEFAULT "myabc"); Query OK, 0 rows affected (0.01 sec) # cd abce/ # ls -ltr total 224 -rw-r----- 1 mysql mysql 114688 Oct 17 17:13 t1.ibd -rw-r----- 1 mysql mysql 114688 Oct 17 17:13 t2.ibd # ibd2sdi -d t1.sdi t1.ibd # ls -ltr total 236 -rw-r----- 1 mysql mysql 114688 Oct 17 17:13 t1.ibd -rw-r----- 1 mysql mysql 114688 Oct 17 17:13 t2.ibd -rw------- 1 root root 9501 Oct 17 17:14 t1.sdi
可以看到列c的default_value是非空的，实际上两个表中都对c列做了填充：
# ibd2sdi t1.ibd | grep -m1 '\"default_value\"' | cut -b34- | sed -e s/,// "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAA=" # ibd2sdi t2.ibd | grep -m1 '\"default_value\"' | cut -b34- | sed -e s/,// "BW15YWJjAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAA="
可以使用json解释器工具jq来解析ibd2sdi的输出结果：
# cat t2.sdi |jq
[
"ibd2sdi",
{
"type": 1,
"id": 697,
"object": {
"mysqld_version_id": 80030,
"dd_version": 80023,
"sdi_version": 80019,
"dd_object_type": "Table",
"dd_object": {
"name": "t2",
"mysql_version_id": 80030,
"created": 20231017091332,
"last_altered": 20231017091332,
"hidden": 1,
"options": "avg_row_length=0;encrypt_type=N;explicit_encryption=0;key_block_size=0;keys_disabled=0;pack_record=1;stats_auto_recalc=0;stats_sample_pages=0;",
"columns": [
{
"name": "c",
"type": 16,
"is_nullable": false,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": false,
"is_virtual": false,
"hidden": 1,
"ordinal_position": 1,
"char_length": 64,
"numeric_precision": 0,
"numeric_scale": 0,
"numeric_scale_null": true,
"datetime_precision": 0,
"datetime_precision_null": 1,
"has_no_default": false,
"default_value_null": false,
"srs_id_null": true,
"srs_id": 0,
"default_value": "BW15YWJjAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAA=",
"default_value_utf8_null": false,
"default_value_utf8": "myabc",
"default_option": "",
"update_option": "",
"comment": "",
"generation_expression": "",
"generation_expression_utf8": "",
"options": "interval_count=0;",
"se_private_data": "table_id=1355;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 1,
"column_type_utf8": "varchar(16)",
"elements": [],
"collation_id": 255,
"is_explicit_collation": false
},
{
"name": "DB_ROW_ID",
"type": 10,
"is_nullable": false,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": false,
"is_virtual": false,
"hidden": 2,
"ordinal_position": 2,
"char_length": 6,
"numeric_precision": 0,
"numeric_scale": 0,
"numeric_scale_null": true,
"datetime_precision": 0,
"datetime_precision_null": 1,
"has_no_default": false,
"default_value_null": true,
"srs_id_null": true,
"srs_id": 0,
"default_value": "",
"default_value_utf8_null": true,
"default_value_utf8": "",
"default_option": "",
"update_option": "",
"comment": "",
"generation_expression": "",
"generation_expression_utf8": "",
"options": "",
"se_private_data": "table_id=1355;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 1,
"column_type_utf8": "",
"elements": [],
"collation_id": 63,
"is_explicit_collation": false
},
{
"name": "DB_TRX_ID",
"type": 10,
"is_nullable": false,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": false,
"is_virtual": false,
"hidden": 2,
"ordinal_position": 3,
"char_length": 6,
"numeric_precision": 0,
"numeric_scale": 0,
"numeric_scale_null": true,
"datetime_precision": 0,
"datetime_precision_null": 1,
"has_no_default": false,
"default_value_null": true,
"srs_id_null": true,
"srs_id": 0,
"default_value": "",
"default_value_utf8_null": true,
"default_value_utf8": "",
"default_option": "",
"update_option": "",
"comment": "",
"generation_expression": "",
"generation_expression_utf8": "",
"options": "",
"se_private_data": "table_id=1355;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 1,
"column_type_utf8": "",
"elements": [],
"collation_id": 63,
"is_explicit_collation": false
},
{
"name": "DB_ROLL_PTR",
"type": 9,
"is_nullable": false,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": false,
"is_virtual": false,
"hidden": 2,
"ordinal_position": 4,
"char_length": 7,
"numeric_precision": 0,
"numeric_scale": 0,
"numeric_scale_null": true,
"datetime_precision": 0,
"datetime_precision_null": 1,
"has_no_default": false,
"default_value_null": true,
"srs_id_null": true,
"srs_id": 0,
"default_value": "",
"default_value_utf8_null": true,
"default_value_utf8": "",
"default_option": "",
"update_option": "",
"comment": "",
"generation_expression": "",
"generation_expression_utf8": "",
"options": "",
"se_private_data": "table_id=1355;",
"engine_attribute": "",
"secondary_engine_attribute": "",
"column_key": 1,
"column_type_utf8": "",
"elements": [],
"collation_id": 63,
"is_explicit_collation": false
}
],
"schema_ref": "abce",
"se_private_id": 1355,
"engine": "InnoDB",
"last_checked_for_upgrade_version_id": 0,
"comment": "",
"se_private_data": "",
"engine_attribute": "",
"secondary_engine_attribute": "",
"row_format": 2,
"partition_type": 0,
"partition_expression": "",
"partition_expression_utf8": "",
"default_partitioning": 0,
"subpartition_type": 0,
"subpartition_expression": "",
"subpartition_expression_utf8": "",
"default_subpartitioning": 0,
"indexes": [
{
"name": "PRIMARY",
"hidden": true,
"is_generated": false,
"ordinal_position": 1,
"comment": "",
"options": "",
"se_private_data": "id=797;root=4;space_id=292;table_id=1355;trx_id=622891;",
"type": 2,
"algorithm": 2,
"is_algorithm_explicit": false,
"is_visible": true,
"engine": "InnoDB",
"engine_attribute": "",
"secondary_engine_attribute": "",
"elements": [
{
"ordinal_position": 1,
"length": 4294967295,
"order": 2,
"hidden": true,
"column_opx": 1
},
{
"ordinal_position": 2,
"length": 4294967295,
"order": 2,
"hidden": true,
"column_opx": 2
},
{
"ordinal_position": 3,
"length": 4294967295,
"order": 2,
"hidden": true,
"column_opx": 3
},
{
"ordinal_position": 4,
"length": 4294967295,
"order": 2,
"hidden": true,
"column_opx": 0
}
],
"tablespace_ref": "abce/t2"
}
],
"foreign_keys": [],
"check_constraints": [],
"partitions": [],
"collation_id": 255
}
}
},
{
"type": 2,
"id": 297,
"object": {
"mysqld_version_id": 80030,
"dd_version": 80023,
"sdi_version": 80019,
"dd_object_type": "Tablespace",
"dd_object": {
"name": "abce/t2",
"comment": "",
"options": "autoextend_size=0;encryption=N;",
"se_private_data": "flags=16417;id=292;server_version=80030;space_version=1;state=normal;",
"engine": "InnoDB",
"engine_attribute": "",
"files": [
{
"ordinal_position": 1,
"filename": "./abce/t2.ibd",
"se_private_data": "id=292;"
}
]
}
}
}
]
输出结果的可读性仍然是很差的。
好在有人开发了工具sdi2ddl，可以将结果显示成show create table语句的效果。不过，不建议在生产环境使用该工具。
下载主页：
https://github.com/altmannmarcelo/sdi2ddl
浙公网安备 33010602011771号