1. 引言
数据字典(Data Dictionary)中存储了诸多数据库的元数据信息如图1所示,包括基本Database, table, index, column, function, trigger, procedure,privilege等;以及与存储引擎相关的元数据,如InnoDB的tablespace, table_id, index_id等。MySQL-8.0在数据字典上进行了诸多优化,本文将对其进行逐一介绍。
图1
俗话说知己知彼,方能百战不殆。在介绍MySQL-8.0的数据字典前,我们先一起回顾一下MySQL-8.0之前的数据字典。
2.1 Data Dictionary 分布位置
图2
如图2所示,旧的数据字典信息分布在server层,mysql库下的系统表和InnoDB内部系统表三个地方,其中保存的信息分别如下所示:
鉴于旧数据字典的种种缺点,MySQL-8.0对数据字典进行了较大的改动:把所有的元数据信息都存储在InnoDB dictionary table中,并且存储在单独的表空间mysql.ibd里,其架构如图3所示。下面逐一介绍各项改变的细节。
图3
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)
数据字典表信息可以通过全局的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 |
+---------------------------------+-------+
另外还有character,collation,event,column_statistics也有cache,不过其大小硬编码不可配置:
class Shared_dictionary_cache
{
...
Shared_multi_map<Abstract_table> m_abstract_table_map;
Shared_multi_map<Charset> m_charset_map;
Shared_multi_map<Collation> m_collation_map;
Shared_multi_map<Column_statistics> m_column_stat_map;
Shared_multi_map<Event> m_event_map;
Shared_multi_map<Resource_group> m_resource_group_map;
Shared_multi_map<Routine> m_routine_map;
Shared_multi_map<Schema> m_schema_map;
Shared_multi_map<Spatial_reference_system> m_spatial_reference_system_map;
Shared_multi_map<Tablespace> m_tablespace_map;
...
}
图4
information_schema的变化如图4所示,主要包括以下几个方面:
1. information_schema部分表名变化
Old Name | New Name |
---|---|
INNODB_SYS_COLUMNS | INNODB_COLUMNS |
INNODB_SYS_DATAFILES | INNODB_DATAFILES |
INNODB_SYS_FIELDS | INNODB_FIELDS |
INNODB_SYS_FOREIGN | INNODB_FOREIGN |
INNODB_SYS_FOREIGN_COLS | INNODB_FOREIGN_COLS |
INNODB_SYS_INDEXES | INNODB_INDEXES |
INNODB_SYS_TABLES | INNODB_TABLES |
INNODB_SYS_TABLESPACES | INNODB_TABLESPACES |
INNODB_SYS_TABLESTATS | INNODB_TABLESTATS |
INNODB_SYS_VIRTUAL | INNODB_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)
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 |
+-----+
上述例子中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
}
}
["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
import (import table *.sdi)只支持MyISAM表,InnoDB不支持。由于SDI不包含trigger信息,所以import也不会导入trigger信息,trigger需额外处理。
class Storage_adapter {
/**
Drop a dictionary object from persistent storage.
*/
template <typename T>
static bool drop(THD *thd, const T *object);
/**
Store a dictionary object to persistent storage.
*/
template <typename T>
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所示:
图5
mysql_create_table
-->mysql_create_table_no_lock
-->create_table_impl
-->rea_create_base_table
-->dd::cache::Dictionary_client::store<dd::Table> // mysql.tables
-->dd::cache::Storage_adapter::store<dd::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<dd::Column*>::store_items
-->dd::Collection<dd::Index*>::store_items // store mysql.indexes
-->dd::Weak_object_impl::store
-->dd::Index_impl::store_children
-->dd::Collection<dd::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<dd::Tablespace>
-->dd::cache::Storage_adapter::store<dd::Tablespace>
-->dd::Weak_object_impl::store // store mysql.tablespace
-->dd::Tablespace_impl::store_children
-->dd::Collection<dd::Tablespace_file*>::store_items
-->dd::Tablespace_file_impl::store // store tablespace_files
-->dd::sdi::store // store tablespace SDI
-->dd::cache::Dictionary_client::update<dd::Table> // 更新innodb引擎相关元数据
-->dd::cache::Storage_adapter::store<dd::Table>
-->dd::Weak_object_impl::store
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; |
+----+---------+---------+------------------------------------------------------
图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
只有InnoDB engine支持Atomic DDL,以下操作不支持:
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.
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<dd::Table>
-->dd::cache::Storage_adapter::drop<dd::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
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的回滚。
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
Sql_cmd_truncate_table::truncate_table
-->ha_create_table
-->handler::ha_create
-->ha_innobase::create
-->ha_innobase::truncate_impl
-->innobase_truncate<dd::Table>::exec
-->innobase_truncate<Table>::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
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)
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。