解析MySQL8.0新特性——事務(wù)性數(shù)據(jù)字典與原子DDL
更新時(shí)間：2020年08月25日 14:47:36 作者：Bright@DBA
這篇文章主要介紹了MySQL8.0新特性——事務(wù)性數(shù)據(jù)字典與原子DDL的相關(guān)資料，幫助大家更好的理解和學(xué)習(xí)MySQL8.0感興趣的朋友可以了解下
前言
事務(wù)性數(shù)據(jù)字典與原子DDL，是MySQL 8.0推出的兩個(gè)非常重要的新特性，之所以將這兩個(gè)新特性放在一起，是因?yàn)閮烧呙芮邢嚓P(guān)，事務(wù)性數(shù)據(jù)字典是前提，原子DDL是一個(gè)重要應(yīng)用場(chǎng)景。
MySQL 8.0之前的數(shù)據(jù)字典
MySQL 8.0之前的數(shù)據(jù)字典，主要由以下三部分組成：
（1）操作系統(tǒng)文件
db.opt：數(shù)據(jù)庫元數(shù)據(jù)信息
frm：表元數(shù)據(jù)信息
par：表分區(qū)元數(shù)據(jù)信息
TRN/TRG：觸發(fā)器元數(shù)據(jù)信息
ddl_log.log：DDL過程中產(chǎn)生的元數(shù)據(jù)信息
（2）mysql庫下的非InnoDB系統(tǒng)表
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系統(tǒng)表
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)
我們可以看到，數(shù)據(jù)字典被分布到多個(gè)地方，一方面不利于元數(shù)據(jù)統(tǒng)一管理，另一方面容易造成數(shù)據(jù)的不一致(由于操作系統(tǒng)文件、非InnoDB系統(tǒng)表均不支持事務(wù)，執(zhí)行DDL操作無法保證ACID)。
MySQL 8.0的數(shù)據(jù)字典
為了解決上述問題，MySQL 8.0將數(shù)據(jù)字典統(tǒng)一改進(jìn)為InnoDB存儲(chǔ)引擎存儲(chǔ)，具體分為兩部分：
（1）數(shù)據(jù)字典表：存放最重要的元數(shù)據(jù)信息，位于mysql庫下，存儲(chǔ)在mysql共享表空間(mysql.ibd)
（2）其他系統(tǒng)表：存放輔助的元數(shù)據(jù)信息，位于mysql庫下，存儲(chǔ)在mysql共享表空間(mysql.ibd)
數(shù)據(jù)字典表
數(shù)據(jù)字典表是不可見，既不能通過select訪問，也不會(huì)出現(xiàn)在show tables或information.schema.tables結(jié)果里；嘗試訪問會(huì)報(bào)以下錯(cuò)誤：
mysql> select * from mysql.tables limit 10; ERROR 3554 (HY000): Access to data dictionary table 'mysql.tables' is rejected.
不過，在debug模式下，是可以訪問這些隱藏的數(shù)據(jù)字典表的；我們重新編譯安裝(過程略)，并以debug模式啟動(dòng)進(jìn)程，再次嘗試訪問，結(jié)果如下：
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)
其他系統(tǒng)表
其他系統(tǒng)表，可以通過show tables或information_schema.tables查看，均以改進(jìn)為InnoDB存儲(chǔ)引擎(general_log、slow_log例外，這兩張表并未記錄元數(shù)據(jù)信息，只是用于記錄日志)：
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)
數(shù)據(jù)字典視圖
剛剛提到，數(shù)據(jù)字典表只能在debug模式下訪問，那么在生產(chǎn)環(huán)境中，我們應(yīng)該怎么去獲取元數(shù)據(jù)信息呢？答案是通過information_schema庫下的數(shù)據(jù)字典視圖。和Oracle數(shù)據(jù)庫的設(shè)計(jì)理念一樣，將元數(shù)據(jù)信息存放在基表中(x$、$)，然后通過視圖(v$、dba_/all_/user_)的方式提供給用戶查詢；MySQL數(shù)據(jù)庫也是如此，將元數(shù)據(jù)信息存放在mysql庫的數(shù)據(jù)字典表中隱藏起來，然后提供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)
數(shù)據(jù)字典緩存
為了減少磁盤IO，提高訪問效率，MySQL 8.0引入了數(shù)據(jù)字典緩存。數(shù)據(jù)字典緩存是一塊全局共享區(qū)域，通過LRU算法進(jìn)行內(nèi)存管理，具體包括：
tablespace definition cache partition：用于緩存表空間定義對(duì)象；大小限制由參數(shù)tablespace_definition_cache決定。 schema definition cache partition：用于緩存模式定義對(duì)象；大小限制由參數(shù)schema_definition_cache決定。 table definition cache partition：用于緩存表定義對(duì)象；大小限制由參數(shù)max_connections決定。 stored program definition cache partition：用于緩存存儲(chǔ)過程定義對(duì)象；大小限制由參數(shù)stored_program_definition_cache決定。 character set definition cache partition：用于緩存字符集定義對(duì)象；硬編碼限制256個(gè)。 collation definition cache partition：用于緩存排序規(guī)則定義對(duì)象；硬編碼限制256個(gè)。
原子DDL
首先，了解一下什么是原子性？原子性是指，一個(gè)事務(wù)執(zhí)行要么全部成功，要么全部失敗。
在MySQL 8.0之前，由于不支持原子DDL，在服務(wù)進(jìn)程異常掛掉或服務(wù)器異常宕機(jī)的情況下，有可能會(huì)導(dǎo)致數(shù)據(jù)字典、存儲(chǔ)引擎結(jié)構(gòu)、二進(jìn)制日志之間的不一致。
在MySQL 8.0中，數(shù)據(jù)字典均被改造成InnoDB存儲(chǔ)引擎表，原子DDL也被引入進(jìn)來。原子DDL是將數(shù)據(jù)字典更新、存儲(chǔ)引擎操作、二進(jìn)制日志寫入放到同一個(gè)事務(wù)里執(zhí)行，要么全部成功提交，要么全部失敗回滾。
接下來，我們還是先通過一個(gè)例子，來了解一下原子DDL。在這個(gè)例子中，DROP TABLE t1, t2屬于同一個(gè)事務(wù)；在5.7版本中，出現(xiàn)了一個(gè)事務(wù)部分、成功部分失敗的情況，即DROP TABLE t1成功、DROP TABLE t2失??；但在8.0版本中，因?yàn)镈ROP TABLE t2失敗，導(dǎo)致整個(gè)事務(wù)全部失敗回滾；這個(gè)例子就很好地體現(xiàn)了原子性和非原子性的區(qū)別。
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 | +----------------+
在對(duì)原子DDL有初步了解后，接下來介紹一下具體過程：
（1）prepare：創(chuàng)建需要的對(duì)象，并將ddl日志寫入到mysql.innodb_ddl_log；ddl日志記錄了如何前滾和回滾ddl操作。
（2）perform：執(zhí)行ddl操作。
（3）commit：更新數(shù)據(jù)字典并提交。
（4）post-ddl：重放和刪除ddl日志。只有在實(shí)例異常宕機(jī)情況下，ddl日志才會(huì)繼續(xù)保存在mysql.innodb_ddl_log；在在實(shí)例重啟后，進(jìn)行實(shí)例恢復(fù)階段，ddl日志會(huì)重放和刪除；如果第3步-數(shù)據(jù)字典更新已經(jīng)成功提交，并寫入redo log和binlog，那么ddl操作成功；否則，ddl操作失敗，并根據(jù)ddl日志進(jìn)行回滾
最后，再介紹一下，怎么查看DDL日志？
其中一個(gè)方法，是在debug級(jí)別下，訪問表mysql.innodb_ddl_log進(jìn)行查看(不推薦)
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) );
另一個(gè)辦法，是可以將DDL日志打印到error log進(jìn)行查看(推薦)
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
總結(jié)
MySQL 8.0對(duì)于數(shù)據(jù)字典的改進(jìn)，帶來了很多好處，包括元數(shù)據(jù)統(tǒng)一管理、數(shù)據(jù)字典緩存、information_schema性能提升、原子DDL等等。
以上就是解析MySQL8.0新特性——事務(wù)性數(shù)據(jù)字典與原子DDL的詳細(xì)內(nèi)容，更多關(guān)于MySQL8.0新特性的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！
您可能感興趣的文章:
- MySQL 8.0數(shù)據(jù)字典的初始化與啟動(dòng)流程
- Mysql?json類型字段Java+Mybatis數(shù)據(jù)字典功能的實(shí)踐方式
- 詳解PyMySQL插入字典類型的數(shù)據(jù)
- Mysql生成數(shù)據(jù)字典的原理與實(shí)例
- Python保存dict字典類型數(shù)據(jù)到Mysql并自動(dòng)創(chuàng)建表與列
- Python3自動(dòng)生成MySQL數(shù)據(jù)字典的markdown文本的實(shí)現(xiàn)
- php生成mysql的數(shù)據(jù)字典
- MySQL 8.0數(shù)據(jù)字典緩存管理機(jī)制解析
相關(guān)文章
這篇文章主要給大家介紹了關(guān)于mysql語句如何插入含單引號(hào)或反斜杠的值的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧2019-02-02
mysql 5.7.10 winx64安裝配置方法圖文教程(win10)
這篇文章主要為大家分享了mysql 5.7.10 winx64安裝配置方法圖文教程，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2017-01-01
Datagrip2020下載MySQL驅(qū)動(dòng)失敗的問題
這篇文章主要介紹了Datagrip2020下載MySQL驅(qū)動(dòng)失敗的問題，本文通過圖文并茂的形式給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2020-09-09
這篇文章主要給大家介紹了關(guān)于MYSQL字符串強(qiáng)轉(zhuǎn)的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-12-12
MySQL數(shù)據(jù)庫作為關(guān)系型數(shù)據(jù)庫中的佼佼者,因其體積小,速度快,成本低,不僅受到了市場(chǎng)的極大追捧,也受到了廣大程序員的青睞,下面這篇文章主要給大家介紹了關(guān)于windows安裝MySQL到D盤的超詳細(xì)步驟,需要的朋友可以參考下2023-03-03
MYSQL必知必會(huì)讀書筆記第三章之顯示數(shù)據(jù)庫
MySQL是一種開放源代碼的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)（RDBMS），MySQL數(shù)據(jù)庫系統(tǒng)使用最常用的數(shù)據(jù)庫管理語言--結(jié)構(gòu)化查詢語言（SQL）進(jìn)行數(shù)據(jù)庫管理。接下來通過本文給大家介紹MYSQL必知必會(huì)讀書筆記第三章之顯示數(shù)據(jù)庫，感興趣的朋友參考下吧2016-05-05
MySQL數(shù)據(jù)庫遷移data文件夾位置詳細(xì)步驟
這篇文章主要介紹了MySQL數(shù)據(jù)庫遷移data文件夾詳細(xì)步驟,需要的朋友可以參考下2014-03-03
最近因?yàn)閿?shù)據(jù)庫讀的請(qǐng)求增加，出現(xiàn)了比較嚴(yán)重的讀寫鎖問題，由于主從分離，主服務(wù)器很快的執(zhí)行完了寫入的操作，但從庫由于有大量的select的查詢，會(huì)被這些來自主輔同步的update,insert嚴(yán)重堵塞，最后造成所有的Mysql從庫負(fù)載迅速上升。2011-09-09
mysql 動(dòng)態(tài)生成測(cè)試數(shù)據(jù)
mysql 動(dòng)態(tài)生成測(cè)試數(shù)據(jù)的語句，方便測(cè)試數(shù)據(jù)。2009-08-08
計(jì)算機(jī)二級(jí)考試MySQL知識(shí)點(diǎn) mysql alter命令
這篇文章主要為大家詳細(xì)介紹了計(jì)算機(jī)二級(jí)考試MySQL知識(shí)點(diǎn)，詳細(xì)介紹了mysql中alter命令的使用方法，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2017-08-08