解析MySQL8.0新特性——事务性数据字典与原子DDL
程序员文章站
2022-05-08 09:20:43
前言事务性数据字典与原子ddl，是mysql 8.0推出的两个非常重要的新特性，之所以将这两个新特性放在一起，是因为两者密切相关，事务性数据字典是前提，原子ddl是一个重要应用场景。mysql 8.0...
前言
事务性数据字典与原子ddl，是mysql 8.0推出的两个非常重要的新特性，之所以将这两个新特性放在一起，是因为两者密切相关，事务性数据字典是前提，原子ddl是一个重要应用场景。
mysql 8.0之前的数据字典
mysql 8.0之前的数据字典，主要由以下三部分组成：
（1）操作系统文件
db.opt：数据库元数据信息
frm：表元数据信息
par：表分区元数据信息
trn/trg：触发器元数据信息
ddl_log.log：ddl过程中产生的元数据信息
（2）mysql库下的非innodb系统表
mysql> select table_schema,table_name,table_type,engine from information_schema.tables where table_schema='mysql' and engine<>'innodb'; +--------------+------------------+------------+--------+ | table_schema | table_name | table_type | engine | +--------------+------------------+------------+--------+ | mysql | columns_priv | base table | myisam | | mysql | db | base table | myisam | | mysql | event | base table | myisam | | mysql | func | base table | myisam | | mysql | general_log | base table | csv | | mysql | ndb_binlog_index | base table | myisam | | mysql | proc | base table | myisam | | mysql | procs_priv | base table | myisam | | mysql | proxies_priv | base table | myisam | | mysql | slow_log | base table | csv | | mysql | tables_priv | base table | myisam | | mysql | user | base table | myisam | +--------------+------------------+------------+--------+ 12 rows in set (0.00 sec)
（3）mysql库下的innodb系统表
mysql> select table_schema,table_name,table_type,engine from information_schema.tables where table_schema='mysql' and engine='innodb'; +--------------+---------------------------+------------+--------+ | table_schema | table_name | table_type | engine | +--------------+---------------------------+------------+--------+ | mysql | engine_cost | base table | innodb | | mysql | gtid_executed | base table | innodb | | mysql | help_category | base table | innodb | | mysql | help_keyword | base table | innodb | | mysql | help_relation | base table | innodb | | mysql | help_topic | base table | innodb | | mysql | innodb_index_stats | base table | innodb | | mysql | innodb_table_stats | base table | innodb | | mysql | plugin | base table | innodb | | mysql | server_cost | base table | innodb | | mysql | servers | base table | innodb | | mysql | slave_master_info | base table | innodb | | mysql | slave_relay_log_info | base table | innodb | | mysql | slave_worker_info | base table | innodb | | mysql | time_zone | base table | innodb | | mysql | time_zone_leap_second | base table | innodb | | mysql | time_zone_name | base table | innodb | | mysql | time_zone_transition | base table | innodb | | mysql | time_zone_transition_type | base table | innodb | +--------------+---------------------------+------------+--------+ 19 rows in set (0.00 sec)
我们可以看到，数据字典被分布到多个地方，一方面不利于元数据统一管理，另一方面容易造成数据的不一致(由于操作系统文件、非innodb系统表均不支持事务，执行ddl操作无法保证acid)。
mysql 8.0的数据字典
为了解决上述问题，mysql 8.0将数据字典统一改进为innodb存储引擎存储，具体分为两部分：
（1）数据字典表：存放最重要的元数据信息，位于mysql库下，存储在mysql共享表空间(mysql.ibd)
（2）其他系统表：存放辅助的元数据信息，位于mysql库下，存储在mysql共享表空间(mysql.ibd)
数据字典表
数据字典表是不可见，既不能通过select访问，也不会出现在show tables或information.schema.tables结果里；尝试访问会报以下错误：
mysql> select * from mysql.tables limit 10; error 3554 (hy000): access to data dictionary table 'mysql.tables' is rejected.
不过，在debug模式下，是可以访问这些隐藏的数据字典表的；我们重新编译安装(过程略)，并以debug模式启动进程，再次尝试访问，结果如下：
mysql> set session debug='+d,skip_dd_table_access_check'; mysql> select name, schema_id, hidden, type from mysql.tables where schema_id=1 and hidden='system'; +------------------------------+-----------+--------+------------+ | name | schema_id | hidden | type | +------------------------------+-----------+--------+------------+ | catalogs | 1 | system | base table | | character_sets | 1 | system | base table | | check_constraints | 1 | system | base table | | collations | 1 | system | base table | | column_statistics | 1 | system | base table | | column_type_elements | 1 | system | base table | | columns | 1 | system | base table | | dd_properties | 1 | system | base table | | events | 1 | system | base table | | foreign_key_column_usage | 1 | system | base table | | foreign_keys | 1 | system | base table | | index_column_usage | 1 | system | base table | | index_partitions | 1 | system | base table | | index_stats | 1 | system | base table | | indexes | 1 | system | base table | | innodb_ddl_log | 1 | system | base table | | innodb_dynamic_metadata | 1 | system | base table | | parameter_type_elements | 1 | system | base table | | parameters | 1 | system | base table | | resource_groups | 1 | system | base table | | routines | 1 | system | base table | | schemata | 1 | system | base table | | st_spatial_reference_systems | 1 | system | base table | | table_partition_values | 1 | system | base table | | table_partitions | 1 | system | base table | | table_stats | 1 | system | base table | | tables | 1 | system | base table | | tablespace_files | 1 | system | base table | | tablespaces | 1 | system | base table | | triggers | 1 | system | base table | | view_routine_usage | 1 | system | base table | | view_table_usage | 1 | system | base table | +------------------------------+-----------+--------+------------+ 32 rows in set (0.01 sec)
其他系统表
其他系统表，可以通过show tables或information_schema.tables查看，均以改进为innodb存储引擎(general_log、slow_log例外，这两张表并未记录元数据信息，只是用于记录日志)：
mysql> select table_schema,table_name,engine from information_schema.tables where table_schema='mysql'; +--------------+---------------------------+--------+ | table_schema | table_name | engine | +--------------+---------------------------+--------+ | mysql | columns_priv | innodb | | mysql | component | innodb | | mysql | db | innodb | | mysql | default_roles | innodb | | mysql | engine_cost | innodb | | mysql | func | innodb | | mysql | general_log | csv | | mysql | global_grants | innodb | | mysql | gtid_executed | innodb | | mysql | help_category | innodb | | mysql | help_keyword | innodb | | mysql | help_relation | innodb | | mysql | help_topic | innodb | | mysql | innodb_index_stats | innodb | | mysql | innodb_table_stats | innodb | | mysql | password_history | innodb | | mysql | plugin | innodb | | mysql | procs_priv | innodb | | mysql | proxies_priv | innodb | | mysql | role_edges | innodb | | mysql | server_cost | innodb | | mysql | servers | innodb | | mysql | slave_master_info | innodb | | mysql | slave_relay_log_info | innodb | | mysql | slave_worker_info | innodb | | mysql | slow_log | csv | | mysql | tables_priv | innodb | | mysql | time_zone | innodb | | mysql | time_zone_leap_second | innodb | | mysql | time_zone_name | innodb | | mysql | time_zone_transition | innodb | | mysql | time_zone_transition_type | innodb | | mysql | user | innodb | +--------------+---------------------------+--------+ 33 rows in set (0.00 sec)
数据字典视图
刚刚提到，数据字典表只能在debug模式下访问，那么在生产环境中，我们应该怎么去获取元数据信息呢？答案是通过information_schema库下的数据字典视图。和oracle数据库的设计理念一样，将元数据信息存放在基表中(x$、$)，然后通过视图(v$、dba_/all_/user_)的方式提供给用户查询；mysql数据库也是如此，将元数据信息存放在mysql库的数据字典表中隐藏起来，然后提供information_schema库视图给用户查询：
mysql> select table_schema,table_name,table_type,engine from information_schema.tables where table_schema='information_schema';
+--------------------+---------------------------------------+-------------+--------+
| table_schema | table_name | table_type | engine |
+--------------------+---------------------------------------+-------------+--------+
| information_schema | administrable_role_authorizations | system view | null |
| information_schema | applicable_roles | system view | null |
| information_schema | character_sets | system view | null |
| information_schema | check_constraints | system view | null |
| information_schema | collation_character_set_applicability | system view | null |
| information_schema | collations | system view | null |
| information_schema | column_privileges | system view | null |
| information_schema | column_statistics | system view | null |
| information_schema | columns | system view | null |
| information_schema | enabled_roles | system view | null |
| information_schema | engines | system view | null |
| information_schema | events | system view | null |
| information_schema | files | system view | null |
| information_schema | innodb_buffer_page | system view | null |
| information_schema | innodb_buffer_page_lru | system view | null |
| information_schema | innodb_buffer_pool_stats | system view | null |
| information_schema | innodb_cached_indexes | system view | null |
| information_schema | innodb_cmp | system view | null |
| information_schema | innodb_cmp_per_index | system view | null |
| information_schema | innodb_cmp_per_index_reset | system view | null |
| information_schema | innodb_cmp_reset | system view | null |
| information_schema | innodb_cmpmem | system view | null |
| information_schema | innodb_cmpmem_reset | system view | null |
| information_schema | innodb_columns | system view | null |
| information_schema | innodb_datafiles | system view | null |
| information_schema | innodb_fields | system view | null |
| information_schema | innodb_foreign | system view | null |
| information_schema | innodb_foreign_cols | system view | null |
| information_schema | innodb_ft_being_deleted | system view | null |
| information_schema | innodb_ft_config | system view | null |
| information_schema | innodb_ft_default_stopword | system view | null |
| information_schema | innodb_ft_deleted | system view | null |
| information_schema | innodb_ft_index_cache | system view | null |
| information_schema | innodb_ft_index_table | system view | null |
| information_schema | innodb_indexes | system view | null |
| information_schema | innodb_metrics | system view | null |
| information_schema | innodb_session_temp_tablespaces | system view | null |
| information_schema | innodb_tables | system view | null |
| information_schema | innodb_tablespaces | system view | null |
| information_schema | innodb_tablespaces_brief | system view | null |
| information_schema | innodb_tablestats | system view | null |
| information_schema | innodb_temp_table_info | system view | null |
| information_schema | innodb_trx | system view | null |
| information_schema | innodb_virtual | system view | null |
| information_schema | key_column_usage | system view | null |
| information_schema | keywords | system view | null |
| information_schema | optimizer_trace | system view | null |
| information_schema | parameters | system view | null |
| information_schema | partitions | system view | null |
| information_schema | plugins | system view | null |
| information_schema | processlist | system view | null |
| information_schema | profiling | system view | null |
| information_schema | referential_constraints | system view | null |
| information_schema | resource_groups | system view | null |
| information_schema | role_column_grants | system view | null |
| information_schema | role_routine_grants | system view | null |
| information_schema | role_table_grants | system view | null |
| information_schema | routines | system view | null |
| information_schema | schema_privileges | system view | null |
| information_schema | schemata | system view | null |
| information_schema | st_geometry_columns | system view | null |
| information_schema | st_spatial_reference_systems | system view | null |
| information_schema | st_units_of_measure | system view | null |
| information_schema | statistics | system view | null |
| information_schema | table_constraints | system view | null |
| information_schema | table_privileges | system view | null |
| information_schema | tables | system view | null |
| information_schema | tablespaces | system view | null |
| information_schema | triggers | system view | null |
| information_schema | user_privileges | system view | null |
| information_schema | view_routine_usage | system view | null |
| information_schema | view_table_usage | system view | null |
| information_schema | views | system view | null |
+--------------------+---------------------------------------+-------------+--------+
73 rows in set (0.00 sec)
mysql> show create table information_schema.tables\g
*************************** 1. row ***************************
view: tables
create view: create algorithm=undefined definer=`mysql.infoschema`@`localhost` sql security definer view `information_schema`.`tables` as select (`cat`.`name` collate utf8_tolower_ci) as `table_catalog`,(`sch`.`name` collate utf8_tolower_ci) as `table_schema`,(`tbl`.`name` collate utf8_tolower_ci) as `table_name`,`tbl`.`type` as `table_type`,if((`tbl`.`type` = 'base table'),`tbl`.`engine`,null) as `engine`,if((`tbl`.`type` = 'view'),null,10) as `version`,`tbl`.`row_format` as `row_format`,if((`tbl`.`type` = 'view'),null,internal_table_rows(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'visible'),`ts`.`se_private_data`,coalesce(`stat`.`table_rows`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) as `table_rows`,if((`tbl`.`type` = 'view'),null,internal_avg_row_length(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'visible'),`ts`.`se_private_data`,coalesce(`stat`.`avg_row_length`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) as `avg_row_length`,if((`tbl`.`type` = 'view'),null,internal_data_length(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'visible'),`ts`.`se_private_data`,coalesce(`stat`.`data_length`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) as `data_length`,if((`tbl`.`type` = 'view'),null,internal_max_data_length(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'visible'),`ts`.`se_private_data`,coalesce(`stat`.`max_data_length`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) as `max_data_length`,if((`tbl`.`type` = 'view'),null,internal_index_length(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'visible'),`ts`.`se_private_data`,coalesce(`stat`.`index_length`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) as `index_length`,if((`tbl`.`type` = 'view'),null,internal_data_free(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'visible'),`ts`.`se_private_data`,coalesce(`stat`.`data_free`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) as `data_free`,if((`tbl`.`type` = 'view'),null,internal_auto_increment(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'visible'),`ts`.`se_private_data`,coalesce(`stat`.`auto_increment`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0),`tbl`.`se_private_data`)) as `auto_increment`,`tbl`.`created` as `create_time`,if((`tbl`.`type` = 'view'),null,internal_update_time(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'visible'),`ts`.`se_private_data`,coalesce(cast(`stat`.`update_time` as unsigned),0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) as `update_time`,if((`tbl`.`type` = 'view'),null,internal_check_time(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'visible'),`ts`.`se_private_data`,coalesce(cast(`stat`.`check_time` as unsigned),0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) as `check_time`,`col`.`name` as `table_collation`,if((`tbl`.`type` = 'view'),null,internal_checksum(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'visible'),`ts`.`se_private_data`,coalesce(`stat`.`checksum`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) as `checksum`,if((`tbl`.`type` = 'view'),null,get_dd_create_options(`tbl`.`options`,if((ifnull(`tbl`.`partition_expression`,'not_part_tbl') = 'not_part_tbl'),0,1),if((`sch`.`default_encryption` = 'yes'),1,0))) as `create_options`,internal_get_comment_or_error(`sch`.`name`,`tbl`.`name`,`tbl`.`type`,`tbl`.`options`,`tbl`.`comment`) as `table_comment` from (((((`mysql`.`tables` `tbl` join `mysql`.`schemata` `sch` on((`tbl`.`schema_id` = `sch`.`id`))) join `mysql`.`catalogs` `cat` on((`cat`.`id` = `sch`.`catalog_id`))) left join `mysql`.`collations` `col` on((`tbl`.`collation_id` = `col`.`id`))) left join `mysql`.`tablespaces` `ts` on((`tbl`.`tablespace_id` = `ts`.`id`))) left join `mysql`.`table_stats` `stat` on(((`tbl`.`name` = `stat`.`table_name`) and (`sch`.`name` = `stat`.`schema_name`)))) where ((0 <> can_access_table(`sch`.`name`,`tbl`.`name`)) and (0 <> is_visible_dd_object(`tbl`.`hidden`)))
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)
数据字典缓存
为了减少磁盘io，提高访问效率，mysql 8.0引入了数据字典缓存。数据字典缓存是一块全局共享区域，通过lru算法进行内存管理，具体包括：
tablespace definition cache partition：用于缓存表空间定义对象；大小限制由参数tablespace_definition_cache决定。 schema definition cache partition：用于缓存模式定义对象；大小限制由参数schema_definition_cache决定。 table definition cache partition：用于缓存表定义对象；大小限制由参数max_connections决定。 stored program definition cache partition：用于缓存存储过程定义对象；大小限制由参数stored_program_definition_cache决定。 character set definition cache partition：用于缓存字符集定义对象；硬编码限制256个。 collation definition cache partition：用于缓存排序规则定义对象；硬编码限制256个。
原子ddl
首先，了解一下什么是原子性？原子性是指，一个事务执行要么全部成功，要么全部失败。
在mysql 8.0之前，由于不支持原子ddl，在服务进程异常挂掉或服务器异常宕机的情况下，有可能会导致数据字典、存储引擎结构、二进制日志之间的不一致。
在mysql 8.0中，数据字典均被改造成innodb存储引擎表，原子ddl也被引入进来。原子ddl是将数据字典更新、存储引擎操作、二进制日志写入放到同一个事务里执行，要么全部成功提交，要么全部失败回滚。
接下来，我们还是先通过一个例子，来了解一下原子ddl。在这个例子中，drop table t1, t2属于同一个事务；在5.7版本中，出现了一个事务部分、成功部分失败的情况，即drop table t1成功、drop table t2失败；但在8.0版本中，因为drop table t2失败，导致整个事务全部失败回滚；这个例子就很好地体现了原子性和非原子性的区别。
5.7版本： mysql> create table t1 (c1 int); mysql> drop table t1, t2; error 1051 (42s02): unknown table 'test.t2' mysql> show tables; empty set (0.00 sec) 8.0版本： mysql> create table t1 (c1 int); mysql> drop table t1, t2; error 1051 (42s02): unknown table 'test.t2' mysql> show tables; +----------------+ | tables_in_test | +----------------+ | t1 | +----------------+
在对原子ddl有初步了解后，接下来介绍一下具体过程：
（1）prepare：创建需要的对象，并将ddl日志写入到mysql.innodb_ddl_log；ddl日志记录了如何前滚和回滚ddl操作。
（2）perform：执行ddl操作。
（3）commit：更新数据字典并提交。
（4）post-ddl：重放和删除ddl日志。只有在实例异常宕机情况下，ddl日志才会继续保存在mysql.innodb_ddl_log；在在实例重启后，进行实例恢复阶段，ddl日志会重放和删除；如果第3步-数据字典更新已经成功提交，并写入redo log和binlog，那么ddl操作成功；否则，ddl操作失败，并根据ddl日志进行回滚
最后，再介绍一下，怎么查看ddl日志？
其中一个方法，是在debug级别下，访问表mysql.innodb_ddl_log进行查看(不推荐)
create table mysql.innodb_ddl_log ( id bigint unsigned not null auto_increment primary key, thread_id bigint unsigned not null, type int unsigned not null, space_id int unsigned, page_no int unsigned, index_id bigint unsigned, table_id bigint unsigned, old_file_path varchar(512) collate utf8_bin, new_file_path varchar(512) collate utf8_bin, key(thread_id) );
另一个办法，是可以将ddl日志打印到error log进行查看(推荐)
mysql> set global innodb_print_ddl_logs=on; query ok, 0 rows affected (0.00 sec) mysql> set global log_error_verbosity=3; query ok, 0 rows affected (0.00 sec) mysql> create table test(id int); query ok, 0 rows affected (0.04 sec) $ tail -100f mysql-error.log 2020-08-17t19:55:09.804345+08:00 73 [note] [my-012473] [innodb] ddl log insert : [ddl record: delete space, id=57, thread_id=73, space_id=12, old_file_path=./test/test.ibd] 2020-08-17t19:55:09.804396+08:00 73 [note] [my-012478] [innodb] ddl log delete : 57 2020-08-17t19:55:09.816850+08:00 73 [note] [my-012477] [innodb] ddl log insert : [ddl record: remove cache, id=58, thread_id=73, table_id=1069, new_file_path=test/test] 2020-08-17t19:55:09.816887+08:00 73 [note] [my-012478] [innodb] ddl log delete : 58 2020-08-17t19:55:09.820623+08:00 73 [note] [my-012472] [innodb] ddl log insert : [ddl record: free, id=59, thread_id=73, space_id=12, index_id=160, page_no=4] 2020-08-17t19:55:09.820673+08:00 73 [note] [my-012478] [innodb] ddl log delete : 59 2020-08-17t19:55:09.837695+08:00 73 [note] [my-012485] [innodb] ddl log post ddl : begin for thread id : 73 2020-08-17t19:55:09.837721+08:00 73 [note] [my-012486] [innodb] ddl log post ddl : end for thread id : 73
总结
mysql 8.0对于数据字典的改进，带来了很多好处，包括元数据统一管理、数据字典缓存、information_schema性能提升、原子ddl等等。
以上就是解析mysql8.0新特性——事务性数据字典与原子ddl的详细内容，更多关于mysql8.0新特性的资料请关注其它相关文章！