mysql存储字典,详解MySQL-8.0数据字典

1. 引言

数据字典(Data Dictionary)中存储了诸多数据库的元数据信息如图1所示，包括基本Database, table, index, column, function, trigger, procedure，privilege等；以及与存储引擎相关的元数据，如InnoDB的tablespace, table_id, index_id等。MySQL-8.0在数据字典上进行了诸多优化，本文将对其进行逐一介绍。

图1

2. MySQL-8.0之前的数据字典

俗话说知己知彼，方能百战不殆。在介绍MySQL-8.0的数据字典前，我们先一起回顾一下MySQL-8.0之前的数据字典。

2.1 Data Dictionary 分布位置

图2

如图2所示，旧的数据字典信息分布在server层，mysql库下的系统表和InnoDB内部系统表三个地方，其中保存的信息分别如下所示：server层文件.frm files: Table metadata files.

.par files: Partition definition files. InnoDB stopped using partition definition files in MySQL 5.7 with the introduction of native partitioning support for InnoDB tables.

.TRN files: Trigger namespace files.

.TRG files: Trigger parameter files.

.isl files: InnoDB Symbolic Link files containing the location of file-per-table tablespace files created outside of the data directory.

.db.opt files: Database configuration files. These files, one per database directory, contained database default character set attributes.

mysql库下的系统表

mysql.user mysql.db mysql.proc mysql.event等

show tables in mysql；

InnoDB内部系统表SYS_DATAFILES

SYS_FOREIGN

SYS_FOREIGN_COLS

SYS_TABLESPACES

SYS_VIRTUAL

2.2 存在的问题数据字典分散存储，维护管理没有统一接口

MyISAM系统表易损坏

DDL没有原子性，server层与innodb层数据字典容易不一致

文件存储数据字典扩展性不好

通过information_schema查询数据字典时生成临时表不友好

3. MySQL-8.0的数据字典

鉴于旧数据字典的种种缺点，MySQL-8.0对数据字典进行了较大的改动：把所有的元数据信息都存储在InnoDB dictionary table中，并且存储在单独的表空间mysql.ibd里，其架构如图3所示。下面逐一介绍各项改变的细节。

图3

3.1 存储结构

MySQL下的原有系统表由MyISAM转为了InnoDB表，没有了proc和event表，直接改存到了dictionary table中。在debug模式下，可用如下指令查看dictionary tables：SET SESSION debug='+d,skip_dd_table_access_check';select name from mysql.tables where hidden='System' and type='BASE TABLE';

+------------------------------+

| name                         |

+------------------------------+

| dd_properties                |

| innodb_dynamic_metadata      |

| innodb_ddl_log               |

| catalogs                     |

| character_sets               |

| collations                   |

| column_statistics            |

| column_type_elements         |

| columns                      |

| events                       |

| foreign_key_column_usage     |

| foreign_keys                 |

| index_column_usage           |

| index_partitions             |

| index_stats                  |

| indexes                      |

| parameter_type_elements      |

| parameters                   |

| resource_groups              |

| routines                     |

| schemata                     |

| st_spatial_reference_systems |

| table_partition_values       |

| table_partitions             |

| table_stats                  |

| tables                       |

| tablespace_files             |

| tablespaces                  |

| triggers                     |

| view_routine_usage           |

| view_table_usage             |

+------------------------------+

31 rows in set (0.01 sec)

3.2 Dictionary Object Cache

数据字典表信息可以通过全局的cache进行缓存。show variables like '%definition%';

+---------------------------------+-------+

| Variable_name                   | Value |

+---------------------------------+-------+

| schema_definition_cache         | 256   |

| stored_program_definition_cache | 256   |

| table_definition_cache          | 2000  |

| tablespace_definition_cache     | 256   |

+---------------------------------+-------+table_definition_cache：存储表定义

schema_definition_cache： 存储schema定义

stored_program_definition_cache：存储proc和func定义

tablespace_definition_cache： 存储tablespace定义

另外还有character,collation, event, column_statistics也有cache，不过其大小硬编码不可配置：class Shared_dictionary_cache

{

...

Shared_multi_map m_abstract_table_map;

Shared_multi_map m_charset_map;

Shared_multi_map m_collation_map;

Shared_multi_map m_column_stat_map;

Shared_multi_map m_event_map;

Shared_multi_map m_resource_group_map;

Shared_multi_map m_routine_map;

Shared_multi_map m_schema_map;

Shared_multi_map m_spatial_reference_system_map;

Shared_multi_map m_tablespace_map;

...

}

3.3 Information_schema

图4

information_schema的变化如图4所示，主要包括以下几个方面：

1. information_schema部分表名变化Old NameNew NameINNODB_SYS_COLUMNSINNODB_COLUMNS

INNODB_SYS_DATAFILESINNODB_DATAFILES

INNODB_SYS_FIELDSINNODB_FIELDS

INNODB_SYS_FOREIGNINNODB_FOREIGN

INNODB_SYS_FOREIGN_COLSINNODB_FOREIGN_COLS

INNODB_SYS_INDEXESINNODB_INDEXES

INNODB_SYS_TABLESINNODB_TABLES

INNODB_SYS_TABLESPACESINNODB_TABLESPACES

INNODB_SYS_TABLESTATSINNODB_TABLESTATS

INNODB_SYS_VIRTUALINNODB_VIRTUAL

2. 通过information_schema查询时不再需要生成临时表获取，而是直接从数据字典表获取show create table SCHEMATA\G

*************************** 1. row ***************************

View: SCHEMATA

Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`mysql.infoschema`@`localhost` SQL SECURITY DEFINER VIEW `SCHEMATA` AS select `cat`.`name` AS `CATALOG_NAME`,`sch`.`name` AS `SCHEMA_NAME`,`cs`.`name` AS `DEFAULT_CHARACTER_SET_NAME`,`col`.`name` AS `DEFAULT_COLLATION_NAME`,NULL AS `SQL_PATH` from (((`mysql`.`schemata` `sch` join `mysql`.`catalogs` `cat` on((`cat`.`id` = `sch`.`catalog_id`))) join `mysql`.`collations` `col` on((`sch`.`default_collation_id` = `col`.`id`))) join `mysql`.`character_sets` `cs` on((`col`.`character_set_id` = `cs`.`id`))) where can_access_database(`sch`.`name`)

character_set_client: utf8

collation_connection: utf8_general_ci

1 row in set (0.00 sec)

3. 不需要像以前一样扫描文件夹获取数据库列表，不需要打开frm文件获取表信息，而是直接从数据字典表获取

4. information_schema查询以view的形式展现，更利于优化器优化查询EXPLAIN SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1';

+----+-------------+-------+------------+--------+--------------------+------------+---------+-------------------------+------+----------+-------------+

| id | select_type | table | partitions | type   | possible_keys      | key        | key_len | ref                     | rows | filtered | Extra       |

+----+-------------+-------+------------+--------+--------------------+------------+---------+-------------------------+------+----------+-------------+

|  1 | SIMPLE      | cat   | NULL       | index  | PRIMARY            | name       | 194     | NULL                    |    1 |   100.00 | Using index |

|  1 | SIMPLE      | sch   | NULL       | eq_ref | PRIMARY,catalog_id | catalog_id | 202     | mysql.cat.id,const      |    1 |   100.00 | Using index |

|  1 | SIMPLE      | tbl   | NULL       | eq_ref | schema_id          | schema_id  | 202     | mysql.sch.id,const      |    1 |   100.00 | Using where |

|  1 | SIMPLE      | stat  | NULL       | const  | PRIMARY            | PRIMARY    | 388     | const,const             |    1 |   100.00 | Using index |

|  1 | SIMPLE      | ts    | NULL       | eq_ref | PRIMARY            | PRIMARY    | 8       | mysql.tbl.tablespace_id |    1 |   100.00 | Using index |

|  1 | SIMPLE      | col   | NULL       | eq_ref | PRIMARY            | PRIMARY    | 8       | mysql.tbl.collation_id  |    1 |   100.00 | Using index |

+----+-------------+-------+------------+--------+--------------------+------------+---------+-------------------------+------+----------+-------------+

6 rows in set, 1 warning (0.00 sec)

3.4 优点

1. 去掉server层的元数据文件，元数据统一存储到InnoDB数据字典表，易于管理且crash-safe

2. 支持原子DDL

3. information_schema查询更高效

4. Serialized Dictionary Information (SDI)

MySQL8.0不仅将元数据信息存储在数据字典表中，同时也冗余存储了一份在SDI中。对于非InnoDB表，SDI数据在后缀为.sdi的文件中，而对于innodb，SDI数据则直接存储与ibd中，如以下例子所示：create table t1(c1 int) engine=InnoDB;

create table t2(c1 int) engine=MyISAM;

ll test/

-rw-r----- 1 root root 114688 2月  22 17:47 t1.ibd

-rw-r----- 1 root root   1495 2月  22 17:47 t2_337.sdi

-rw-r----- 1 root root      0 2月  22 17:47 t2.MYD

-rw-r----- 1 root root   1024 2月  22 17:47 t2.MYI

select id from mysql.tables where name='t2';

+-----+

| id  |

+-----+

| 337 |

+-----+

4.1 非事务表

上述例子中MyISAM表t2的SDI为test/t2_337.sdi，其中337为table_id, t2_337.sdi可以直接打开，数据是json格式(cat test/t2_337.sdi)：{

"mysqld_version_id": 80015,

"dd_version": 80014,

"sdi_version": 1,

"dd_object_type": "Table",

"dd_object": {

"name": "t2",

"mysql_version_id": 80015,

"created": 20190222094723,

"last_altered": 20190222094723,

"hidden": 1,

"options": "avg_row_length=0;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0;",

"columns": [{

"name": "c1",

"type": 4,

"is_nullable": true,

"is_zerofill": false,

"is_unsigned": false,

"is_auto_increment": false,

"is_virtual": false,

"hidden": 1,

"ordinal_position": 1,

"char_length": 11,

"numeric_precision": 10,

"numeric_scale": 0,

"numeric_scale_null": false,

"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": "interval_count=0;",

"se_private_data": "",

"column_key": 1,

"column_type_utf8": "int(11)",

"elements": [],

"collation_id": 255,

"is_explicit_collation": false

}],

"schema_ref": "test",

"se_private_id": 18446744073709551615,

"engine": "MyISAM",

"last_checked_for_upgrade_version_id": 0,

"comment": "",

"se_private_data": "",

"row_format": 1,

"partition_type": 0,

"partition_expression": "",

"partition_expression_utf8": "",

"default_partitioning": 0,

"subpartition_type": 0,

"subpartition_expression": "",

"subpartition_expression_utf8": "",

"default_subpartitioning": 0,

"indexes": [],

"foreign_keys": [],

"partitions": [],

"collation_id": 255

}

}

4.2 InnoDB事务表

上述例子中的InnoDB表t1的SDI则可以通过工具ibd2sdi可以解析出来(ibd2sdi test/t1.ibd)：["ibd2sdi"

,

{

"type": 1,

"id": 336,

"object":

{

"mysqld_version_id": 80015,

"dd_version": 80014,

"sdi_version": 1,

"dd_object_type": "Table",

"dd_object": {

"name": "t1",

"mysql_version_id": 80015,

"created": 20190222094723,

"last_altered": 20190222094723,

"hidden": 1,

"options": "avg_row_length=0;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0;",

"columns": [

{

"name": "c1",

"type": 4,

"is_nullable": true,

"is_zerofill": false,

"is_unsigned": false,

"is_auto_increment": false,

"is_virtual": false,

"hidden": 1,

"ordinal_position": 1,

"char_length": 11,

"numeric_precision": 10,

"numeric_scale": 0,

"numeric_scale_null": false,

"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": "interval_count=0;",

"se_private_data": "table_id=1059;",

"column_key": 1,

"column_type_utf8": "int(11)",

"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=1059;",

"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=1059;",

"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=1059;",

"column_key": 1,

"column_type_utf8": "",

"elements": [],

"collation_id": 63,

"is_explicit_collation": false

}

],

"schema_ref": "test",

"se_private_id": 1059,

"engine": "InnoDB",

"last_checked_for_upgrade_version_id": 0,

"comment": "",

"se_private_data": "",

"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=140;root=4;space_id=2;table_id=1059;trx_id=2569;",

"type": 2,

"algorithm": 2,

"is_algorithm_explicit": false,

"is_visible": true,

"engine": "InnoDB",

"elements": [

{

"ordinal_position": 1,

"length": 4294967295,

"order": 2,

"column_opx": 1

},

{

"ordinal_position": 2,

"length": 4294967295,

"order": 2,

"column_opx": 2

},

{

"ordinal_position": 3,

"length": 4294967295,

"order": 2,

"column_opx": 3

},

{

"ordinal_position": 4,

"length": 4294967295,

"order": 2,

"column_opx": 0

}

],

"tablespace_ref": "test/t1"

}

],

"foreign_keys": [],

"partitions": [],

"collation_id": 255

}

}

}

,

{

"type": 2,

"id": 7,

"object":

{

"mysqld_version_id": 80015,

"dd_version": 80014,

"sdi_version": 1,

"dd_object_type": "Tablespace",

"dd_object": {

"name": "test/t1",

"comment": "",

"options": "",

"se_private_data": "flags=16417;id=2;server_version=80015;space_version=1;state=normal;",

"engine": "InnoDB",

"files": [

{

"ordinal_position": 1,

"filename": "./test/t1.ibd",

"se_private_data": "id=2;"

}

]

}

}

}

]

SDI在ibd中实际是以表(BTree)的形式存储的。建表时会通过btr_sdi_create_index建立SDI的BTree，同时会向BTree插入table和tablespace的SDI信息，表的结构如下：create table SDI_$TABLESPACEID(type int,

id int,

compressed_len int,

uncompressed_len int,

data blob not null,

primary key(type,id));dd::sdi::store

-->dd::sdi_tablespace::store_tsp_sdi // store tablespace SDI

-->dict_sdi_set

-->ib_sdi_set

-->ib_cursor_insert_row

-->dd::sdi_tablespace::store_tbl_sdi // store table SDI

-->ib_sdi_set

-->ib_sdi_set

-->ib_cursor_insert_row

4.3 其他表空间的SDI

ibd2sdi mysql.ibd，可以查看所以mysql下的表，包括new dictionary和mysql下的普通表。需要注意的是ibdata1中不存放SDI信息，使用ibd2sdi解析它会出现以下提示：[INFO] ibd2sdi: SDI is empty.

4.4 import

import (import table *.sdi)只支持MyISAM表，InnoDB不支持。由于SDI不包含trigger信息，所以import也不会导入trigger信息，trigger需额外处理。

5. Data Dictionary存取实现class Storage_adapter {

/**

Drop a dictionary object from persistent storage.

*/

template

static bool drop(THD *thd, const T *object);

/**

Store a dictionary object to persistent storage.

*/

template

static bool store(THD *thd, T *object);

}

例如create table 会涉及到mysql.tablespaces,mysql.tablespace_files, mysql.tables, mysql.indexes, mysql.columns,mysql.index_column_usage等。create table的过程如图5所示：

图5mysql_create_table

-->mysql_create_table_no_lock

-->create_table_impl

-->rea_create_base_table

-->dd::cache::Dictionary_client::store<:table> // mysql.tables

-->dd::cache::Storage_adapter::store<:table>

-->dd::Weak_object_impl::store

-->dd::Raw_new_record::insert // store mysql.tables

-->handler::ha_write_row

-->ha_innobase::write_row

-->dd::Table_impl::store_children

-->dd::Abstract_table_impl::store_children // store  mysql.columns

-->dd::Collection<:column>::store_items

-->dd::Collection<:index>::store_items // store mysql.indexes

-->dd::Weak_object_impl::store

-->dd::Index_impl::store_children

-->dd::Collection<:index_element>::store_items // store mysql.index_column_usage

-->dd::sdi::store //store table SDI

-->ha_create_table

-->handler::ha_create

-->ha_innobase::create

-->innobase_basic_ddl::create_impl

-->create_table_info_t::create_table_update_global_dd

-->dd_set_autoinck

-->dd_create_implicit_tablespace

-->dd_create_tablespace

-->dd::cache::Dictionary_client::store<:tablespace>

-->dd::cache::Storage_adapter::store<:tablespace>

-->dd::Weak_object_impl::store // store mysql.tablespace

-->dd::Tablespace_impl::store_children

-->dd::Collection<:tablespace_file>::store_items

-->dd::Tablespace_file_impl::store // store tablespace_files

-->dd::sdi::store     // store tablespace SDI

-->dd::cache::Dictionary_client::update<:table> // 更新innodb引擎相关元数据

-->dd::cache::Storage_adapter::store<:table>

-->dd::Weak_object_impl::store

下面以表t1为例，演示create table在DD中的数据分布：create table t1(c1 int primary key, c2 int) engine=innodb;

select s.name as schema_name, t.* from mysql.tables t, mysql.schemata s where s.id=t.schema_id and s.name='test' and t.name='t1'\G

*************************** 1. row ***************************

schema_name: test

id: 374

schema_id: 5

name: t1

type: BASE TABLE

engine: InnoDB

mysql_version_id: 80015

row_format: Dynamic

collation_id: 255

comment:

hidden: Visible

options: avg_row_length=0;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0;

se_private_data: NULL

se_private_id: 1096

tablespace_id: NULL

partition_type: NULL

partition_expression: NULL

partition_expression_utf8: NULL

default_partitioning: NULL

subpartition_type: NULL

subpartition_expression: NULL

subpartition_expression_utf8: NULL

default_subpartitioning: NULL

created: 2019-03-12 19:30:46

last_altered: 2019-03-12 19:30:46

view_definition: NULL

view_definition_utf8: NULL

view_check_option: NULL

view_is_updatable: NULL

view_algorithm: NULL

view_security_type: NULL

view_definer: NULL

view_client_collation_id: NULL

view_connection_collation_id: NULL

view_column_names: NULL

last_checked_for_upgrade_version_id: 0

select * from mysql.indexes where table_id= 374\G

*************************** 1. row ***************************

id: 299

table_id: 374

name: PRIMARY

type: PRIMARY

algorithm: BTREE

is_algorithm_explicit: 0

is_visible: 1

is_generated: 0

hidden: 0

ordinal_position: 1

comment:

options: flags=0;

se_private_data: id=177;root=4;space_id=39;table_id=1096;trx_id=9996;

tablespace_id: 43

engine: InnoDB

select id, name, type from mysql.columns where table_id= 374;

+------+-------------+---------------------+

| id   | name        | type                |

+------+-------------+---------------------+

| 4025 | c1          | MYSQL_TYPE_LONG     |

| 4026 | c2          | MYSQL_TYPE_LONG     |

| 4028 | DB_ROLL_PTR | MYSQL_TYPE_LONGLONG |

| 4027 | DB_TRX_ID   | MYSQL_TYPE_INT24    |

+------+-------------+---------------------+

select * from mysql.index_column_usage where index_id=299;

+----------+------------------+-----------+--------+-------+--------+

| index_id | ordinal_position | column_id | length | order | hidden |

+----------+------------------+-----------+--------+-------+--------+

|      299 |                1 |      4025 |      4 | ASC   |      0 |

|      299 |                2 |      4027 |   NULL | ASC   |      1 |

|      299 |                3 |      4028 |   NULL | ASC   |      1 |

|      299 |                4 |      4026 |   NULL | ASC   |      1 |

+----------+------------------+-----------+--------+-------+--------+

select * from mysql.tablespaces where name='test/t1';

+----+---------+---------+----------------------------------------------------------------------+---------+--------+

| id | name    | options | se_private_data                                                      | comment | engine |

+----+---------+---------+----------------------------------------------------------------------+---------+--------+

| 43 | test/t1 | NULL    | flags=16417;id=39;server_version=80015;space_version=1;state=normal; |         | InnoDB |

+----+---------+---------+----------------------------------------------------------------------+---------+--------+

select * from mysql.tablespace_files where tablespace_id=43;

+---------------+------------------+---------------+-----------------+

| tablespace_id | ordinal_position | file_name     | se_private_data |

+---------------+------------------+---------------+-----------------+

|            43 |                1 | ./test/t1.ibd | id=39;          |

+---------------+------------------+---------------+-----------------+

select * from mysql.tablespaces a, mysql.tablespace_files b where a.id=b.tablespace_id and a.name='test/t1';

+----+---------+---------+----------------------------------------------------------------------+---------+--------+---------------+------------------+---------------+-----------------+

| id | name    | options | se_private_data                                                      | comment | engine | tablespace_id | ordinal_position | file_name     | se_private_data |

+----+---------+---------+----------------------------------------------------------------------+---------+--------+---------------+------------------+---------------+-----------------+

| 43 | test/t1 | NULL    | flags=16417;id=39;server_version=80015;space_version=1;state=normal; |         | InnoDB |            43 |                1 | ./test/t1.ibd | id=39;          |

+----+---------+---------+----------------------------------------------------------------------+---------+--------+---------------+------------------+---------------+-----------------+

drop table是create table的逆过程，不再具体分析。

6. Initialize

图6

mysqld --initialize的源码流程如图6所示。具体过程为：dd::Dictionary_impl::init

-->bootstrap::initialize

-->DDSE_dict_init

-->innobase_ddse_dict_init

-->innobase_init_files //创建mysql.ibd

-->initialize_dictionary

-->create_dd_schema/initialize_dd_properties/create_tables

-->DDSE_dict_recover // 创建mysql/innodb_system tablespace

-->flush_meta_data

-->dd::cache::Storage_adapter::store()   //

-->dd::Weak_object_impl::store

-->dd::Table_impl::store_attributes

-->dd::cache::Storage_adapter::core_store  // 存储到Object_registry m_core_registry;

-->dd::sdi::store

-->populate_tables

-->get_dml

-->update_properties

-->update_versions

7. Atomic DDL

7.1 Atomic DDL

定义：DDL所涉及的以下更改操作是原子的，这些更改操作要么都提交，要么都回滚。data dictionary

storage engine

binary log

只有InnoDB engine支持Atomic DDL，以下操作不支持：Table-related DDL statements that involve a storage engine other than InnoDB.

INSTALL PLUGIN and UNINSTALL PLUGIN statements.

INSTALL COMPONENT and UNINSTALL COMPONENT statements.

CREATE SERVER, ALTER SERVER, and DROP SERVER statements.

7.2 DDL log

DDL过程中操作DD事物表是原子的，而DDL过程中也会操作文件，创建和释放BTree以及修改DD cache，这些操作不是原子的。为了实现atomic DDL， DDL过程中对文件操作和Btree操作等记录日志，这些日志会记录到DD表mysql.innodb_ddl_log中。日志有以下几个类型：enum class Log_Type : uint32_t {

/** Smallest log type */

SMALLEST_LOG = 1,

/** Drop an index tree */

FREE_TREE_LOG = 1,

/** Delete a file */

DELETE_SPACE_LOG,

/** Rename a file */

RENAME_SPACE_LOG,

/** Drop the entry in innodb_dynamic_metadata */

DROP_LOG,

/** Rename table in dict cache. */

RENAME_TABLE_LOG,

/** Remove a table from dict cache */

REMOVE_CACHE_LOG,

/** Alter Encrypt a tablespace */

ALTER_ENCRYPT_TABLESPACE_LOG,

/** Biggest log type */

BIGGEST_LOG = ALTER_ENCRYPT_TABLESPACE_LOG

};mysql.innodb_ddl_log 表结构如下：CREATE TABLE `innodb_ddl_log` (

`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

`thread_id` bigint(20) unsigned NOT NULL,

`type` int(10) unsigned NOT NULL,

`space_id` int(10) unsigned DEFAULT NULL,

`page_no` int(10) unsigned DEFAULT NULL,

`index_id` bigint(20) unsigned DEFAULT NULL,

`table_id` bigint(20) unsigned DEFAULT NULL,

`old_file_path` varchar(512) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,

`new_file_path` varchar(512) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `thread_id` (`thread_id`);

将DDL分为以下几个阶段, Prepare记录DDL log，Post-DDL会replay log来提交或回滚DDL操作，同时也并清理DDL log。Prepare: Create the required objects and write the DDL logs to the mysql.innodb_ddl_log table. The DDL logs define how to roll forward and roll back the DDL operation.

Perform: Perform the DDL operation. For example, perform a create routine for a CREATE TABLE operation.

Commit: Update the data dictionary and commit the data dictionary transaction.

Post-DDL: Replay and remove DDL logs from the mysql.innodb_ddl_log table. To ensure that rollback can be performed safely without introducing inconsistencies, file operations such as renaming or removing data files are performed in this final phase. This phase also removes dynamic metadata from the mysql.innodb_dynamic_metadata data dictionary table for DROP TABLE, TRUNCATE TABLE, and other DDL operations that rebuild the table.

7.3 Atomic DDL Examples

7.3.1 drop table

以drop table为例，drop 过程中会删除ibd文件，也会从mysql.innodb_dynamic_metadata 中删除相应记录。

在preppare阶段只是记录日志，没有真正删除。如果drop过程成功, innobase_post_ddl才从mysql.innodb_ddl_log中读取记录去replay，replay_delete_space_log/replay_drop_log会真正执行删除, replay完也会清理ddl log；如果drop过程失败，rollback时mysql.innodb_ddl_log的记录也回滚了，innobase_post_ddl时没有记录需要replay。mysql_rm_table

-->mysql_rm_table_no_locks

-->drop_base_table

-->ha_delete_table

-—>handler::ha_delete_table

-->ha_innobase::delete_table

-->innobase_basic_ddl::delete_impl

-->row_drop_table_for_mysql

-->Log_DDL::write_drop_log               // 记录删innodb_dynamic_metadata日志

-—>Log_DDL::write_delete_space_log       // 记录删ibd日志

-->dd::drop_table

-->dd::cache::Dictionary_client::drop<:table>

-->dd::cache::Storage_adapter::drop<:table>

-->dd::sdi::drop

-->innobase_post_ddl

-->Log_DDL::post_ddl

-->Log_DDL::replay_by_thread_id

-->Log_DDL::replay

—>Log_DDL::replay_delete_space_log // post-ddl 真正删除innodb_dynamic_metadata

—>Log_DDL::replay_drop_log         // post-ddl 真正删除ibd

-->delete_by_ids

-->DDL_Log_Table::remove

7.3.2 create table

drop table post_ddl阶段执行的redo操作，而create table post ddl执行的是rollback操作。create table prepare阶段会真正的创建ibd，BTree，修改DD share cache， 同时记录相应的log到mysql.innodb_ddl_log中。create_table

-->Log_DDL::write_delete_space_log

-->Log_DDL::write_remove_cache_log

-->Log_DDL::write_free_tree_log

如果DDL成功commit，在post-DDL阶段，DDL log记录被清理了，不需要replay。如果DDL失败rollback，在post-DDL阶段，DDL log清理操作也回滚了，需要replay， relay会rollback前面的创建ibd，BTree，以及修改DD share cache。

如果create table过程中发生crash， 重启后会读取ddl log完成ddl的回滚。write_delete_space_log

{

// 插入记录时在内部开启trx执行

insert_delete_space_log(nullptr, id, thread_id, space_id, file_path,

dict_locked);

// 清理记录用DDL自身的trx

delete_by_id(trx, id, dict_locked);

}

如果create table过程中发生crash， 重启后会读取ddl log完成ddl的回滚。init_server_components

-->ha_post_recover

-->post_recover_handlerton

-->innobase_post_recover

-->Log_DDL::recover

-->Log_DDL::replay_all

-->Log_DDL::replay

-->replay_delete_space_log/replay_remove_cache_log/replay_free_tree_log

-->delete_by_ids

-->DDL_Log_Table::remove

7.3.3 truncate table

truncate 先rename 为临时ibd，然后drop临时ibd，再重建表。rename会记录ddl log, 参考write_rename_space_log函数，删除重建也会记录ddl log, 同前面介绍的create/drop table, 可以做到原子。rollback时通过日志将临时ibd重命名为原ibd，参考replay_rename_space_log函数。Sql_cmd_truncate_table::truncate_table

-->ha_create_table

-->handler::ha_create

-->ha_innobase::create

-->ha_innobase::truncate_impl

-->innobase_truncate<:table>::exec

-->innobase_truncate

-->rename_tablespace . // t1.ibd rename to #sql-ib1084-513656514.ibd

-->fil_rename_tablespace

-->Fil_shard::space_rename

-->Log_DDL::write_rename_space_log

-->innobase_basic_ddl::delete_impl

-->row_drop_table_for_mysql

-->Log_DDL::write_delete_space_log// drop #sql-ib1084-513656514.ibd

-->innobase_basic_ddl::create_impl

-->create_table_info_t::create_table

-->create_table_info_t::create_table_def

-->row_create_table_for_mysql

-->dict_build_table_def

-->dict_build_tablespace_for_table

-->Log_DDL::write_delete_space_log // drop t1.ibd

-->Log_DDL::write_remove_cache_log

-->dict_create_index_tree_in_mem

-->Log_DDL::write_free_tree_log

-->innobase_post_ddl

-->Log_DDL::post_ddl

-->Log_DDL::replay_by_thread_id

-->Log_DDL::replay

-->Log_DDL::replay_delete_space_log // drop #sql-ib1084-513656514.ibd

--> Log_DDL::replay_drop_log

-->delete_by_ids

-->DDL_Log_Table::remove

7.4 Atomic DDL带来的变化

drop 多表或多用户时，如果个别失败，整个DDL都会回滚，且不会记录binlog；而在MySQL8.0以前, 部分DDL会成功且整个DDL会记录binlog。mysql> show tables;

+----------------+

| Tables_in_test |

+----------------+

| t1             |

+----------------+

mysql> drop table t1,tt;

ERROR 1051 (42S02): Unknown table 'test.tt'

mysql> show tables;

+----------------+

| Tables_in_test |

+----------------+

| t1             |

+----------------+

show binlog events;

+------------------+-----+----------------+-----------+-------------+-----------------------------------------+

| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                    |

+------------------+-----+----------------+-----------+-------------+-----------------------------------------+

| mysql-bin.000001 |   4 | Format_desc    |         1 |         124 | Server ver: 8.0.15-debug, Binlog ver: 4 |

| mysql-bin.000001 | 124 | Previous_gtids |         1 |         155 |                                         |

+------------------+-----+----------------+-----------+-------------+-----------------------------------------+

2 rows in set (0.00 sec)

8. Persistent Autoinc

MySQL8.0以前自增值没有持久化，重启时通过select MAX(id)的方式获取当前自增值，这种方式自增值会重复利用。MySQL8.0开始支持自增值持久化，通过增加redo日志和Data Dictonary 表mysql.innodb_dynamic_metadata来实现持久化。

每次insert/update更新自增值时会将自增值写到redo日志中，参考dict_table_autoinc_log函数，日志格式如下：MLOG_TABLE_DYNAMIC_META

id,

version,

PM_TABLE_AUTO_INC,

autoinc

同时dict_table_t增加了新的变量autoinc_persisted, 在每次checkpoint时会将autoinc_persisted存储到表mysql.innodb_dynamic_metadata中。

dict_table从dictionary cache淘汰时也会将autoinc_persisted持久化到mysql.innodb_dynamic_metadata中。log_checkpointer

-->log_consider_checkpoint

-->dict_persist_to_dd_table_buffer

-->dict_table_persist_to_dd_table_buffer_low

-->Persisters::write

-->AutoIncPersister::write

-->DDTableBuffer::replace

dict_table从dictionary cache淘汰时也会将autoinc_persisted持久化到mysql.innodb_dynamic_metadata中。

crash重启时，先从mysql.innodb_dynamic_metadata获取持久化的自增值，再从redo日志中读取最新的自增值, 参考MetadataRecover::parseMetadataLog，并通过MetadataRecover::apply更新到table->autoinc。dict_table_remove_from_cache_low

-->dict_table_persist_to_dd_table_buffer_low

-->Persisters::write

-->AutoIncPersister::write

-->DDTableBuffer::replace

crash重启时，先从mysql.innodb_dynamic_metadata获取持久化的自增值，再从redo日志中读取最新的自增值, 参考MetadataRecover::parseMetadataLog，并通过MetadataRecover::apply更新到table->autoinc。

9. Upgrade

MySQL-8.0不支持跨版本升级，只能从5.7升级到8.0，不支持5.5,5.6直接升级到8.0。升级需要注意的问题：原mysql5.7 mysql库下不能存在dictinary table同名的表

不支持老版本(5.6之前)的数据类型decimal，varchar, data/datetime/timestamp， 通过check table xxx for upgrade可以检测

non-native 分区表不支持

不支持5.0之前的trigger，5.0之前的trigger没有definer

foreign key constraint name 不能超过64字节

view的column不能超过255 chars

enum 类型不能超过255 chars.

frm需与InnoDB系统表一致

一些空间函数如PointFromText需修改为ST_POINTFROMTEXT