MySQL 8 的 Serialized Dictionary Information (SDI)

2023-10-19 09:52 abce 阅读(473) 评论(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支持多文件的表空间，比如系统表空间，但同一时刻只能在一个表空间上执行。比如：

1	`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的输出结果：

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

# 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;"
          }
        ]
      }
    }
  }
]