解析MySQL8.0新特性——事务性数据字典与原子DDL
前言
事务性数据字典与原子DDL,是MySQL8.0推出的两个非常重要的新特性,之所以将这两个新特性放在一起,是因为两者密切相关,事务性数据字典是前提,原子DDL是一个重要应用场景。
MySQL8.0之前的数据字典
MySQL8.0之前的数据字典,主要由以下三部分组成:
(1)操作系统文件
db.opt:数据库元数据信息
frm:表元数据信息
par:表分区元数据信息
TRN/TRG:触发器元数据信息
ddl_log.log:DDL过程中产生的元数据信息
(2)mysql库下的非InnoDB系统表
mysql>selecttable_schema,table_name,table_type,enginefrominformation_schema.tableswheretable_schema='mysql'andengine<>'InnoDB'; +--------------+------------------+------------+--------+ |table_schema|table_name|table_type|engine| +--------------+------------------+------------+--------+ |mysql|columns_priv|BASETABLE|MyISAM| |mysql|db|BASETABLE|MyISAM| |mysql|event|BASETABLE|MyISAM| |mysql|func|BASETABLE|MyISAM| |mysql|general_log|BASETABLE|CSV| |mysql|ndb_binlog_index|BASETABLE|MyISAM| |mysql|proc|BASETABLE|MyISAM| |mysql|procs_priv|BASETABLE|MyISAM| |mysql|proxies_priv|BASETABLE|MyISAM| |mysql|slow_log|BASETABLE|CSV| |mysql|tables_priv|BASETABLE|MyISAM| |mysql|user|BASETABLE|MyISAM| +--------------+------------------+------------+--------+ 12rowsinset(0.00sec)
(3)mysql库下的InnoDB系统表
mysql>selecttable_schema,table_name,table_type,enginefrominformation_schema.tableswheretable_schema='mysql'andengine='InnoDB'; +--------------+---------------------------+------------+--------+ |table_schema|table_name|table_type|engine| +--------------+---------------------------+------------+--------+ |mysql|engine_cost|BASETABLE|InnoDB| |mysql|gtid_executed|BASETABLE|InnoDB| |mysql|help_category|BASETABLE|InnoDB| |mysql|help_keyword|BASETABLE|InnoDB| |mysql|help_relation|BASETABLE|InnoDB| |mysql|help_topic|BASETABLE|InnoDB| |mysql|innodb_index_stats|BASETABLE|InnoDB| |mysql|innodb_table_stats|BASETABLE|InnoDB| |mysql|plugin|BASETABLE|InnoDB| |mysql|server_cost|BASETABLE|InnoDB| |mysql|servers|BASETABLE|InnoDB| |mysql|slave_master_info|BASETABLE|InnoDB| |mysql|slave_relay_log_info|BASETABLE|InnoDB| |mysql|slave_worker_info|BASETABLE|InnoDB| |mysql|time_zone|BASETABLE|InnoDB| |mysql|time_zone_leap_second|BASETABLE|InnoDB| |mysql|time_zone_name|BASETABLE|InnoDB| |mysql|time_zone_transition|BASETABLE|InnoDB| |mysql|time_zone_transition_type|BASETABLE|InnoDB| +--------------+---------------------------+------------+--------+ 19rowsinset(0.00sec)
我们可以看到,数据字典被分布到多个地方,一方面不利于元数据统一管理,另一方面容易造成数据的不一致(由于操作系统文件、非InnoDB系统表均不支持事务,执行DDL操作无法保证ACID)。
MySQL8.0的数据字典
为了解决上述问题,MySQL8.0将数据字典统一改进为InnoDB存储引擎存储,具体分为两部分:
(1)数据字典表:存放最重要的元数据信息,位于mysql库下,存储在mysql共享表空间(mysql.ibd)
(2)其他系统表:存放辅助的元数据信息,位于mysql库下,存储在mysql共享表空间(mysql.ibd)
数据字典表
数据字典表是不可见,既不能通过select访问,也不会出现在showtables或information.schema.tables结果里;尝试访问会报以下错误:
mysql>select*frommysql.tableslimit10; ERROR3554(HY000):Accesstodatadictionarytable'mysql.tables'isrejected.
不过,在debug模式下,是可以访问这些隐藏的数据字典表的;我们重新编译安装(过程略),并以debug模式启动进程,再次尝试访问,结果如下:
mysql>SETSESSIONdebug='+d,skip_dd_table_access_check'; mysql>SELECTname,schema_id,hidden,typeFROMmysql.tableswhereschema_id=1ANDhidden='System'; +------------------------------+-----------+--------+------------+ |name|schema_id|hidden|type| +------------------------------+-----------+--------+------------+ |catalogs|1|System|BASETABLE| |character_sets|1|System|BASETABLE| |check_constraints|1|System|BASETABLE| |collations|1|System|BASETABLE| |column_statistics|1|System|BASETABLE| |column_type_elements|1|System|BASETABLE| |columns|1|System|BASETABLE| |dd_properties|1|System|BASETABLE| |events|1|System|BASETABLE| |foreign_key_column_usage|1|System|BASETABLE| |foreign_keys|1|System|BASETABLE| |index_column_usage|1|System|BASETABLE| |index_partitions|1|System|BASETABLE| |index_stats|1|System|BASETABLE| |indexes|1|System|BASETABLE| |innodb_ddl_log|1|System|BASETABLE| |innodb_dynamic_metadata|1|System|BASETABLE| |parameter_type_elements|1|System|BASETABLE| |parameters|1|System|BASETABLE| |resource_groups|1|System|BASETABLE| |routines|1|System|BASETABLE| |schemata|1|System|BASETABLE| |st_spatial_reference_systems|1|System|BASETABLE| |table_partition_values|1|System|BASETABLE| |table_partitions|1|System|BASETABLE| |table_stats|1|System|BASETABLE| |tables|1|System|BASETABLE| |tablespace_files|1|System|BASETABLE| |tablespaces|1|System|BASETABLE| |triggers|1|System|BASETABLE| |view_routine_usage|1|System|BASETABLE| |view_table_usage|1|System|BASETABLE| +------------------------------+-----------+--------+------------+ 32rowsinset(0.01sec)
其他系统表
其他系统表,可以通过showtables或information_schema.tables查看,均以改进为InnoDB存储引擎(general_log、slow_log例外,这两张表并未记录元数据信息,只是用于记录日志):
mysql>selecttable_schema,table_name,enginefrominformation_schema.tableswheretable_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| +--------------+---------------------------+--------+ 33rowsinset(0.00sec)
数据字典视图
刚刚提到,数据字典表只能在debug模式下访问,那么在生产环境中,我们应该怎么去获取元数据信息呢?答案是通过information_schema库下的数据字典视图。和Oracle数据库的设计理念一样,将元数据信息存放在基表中(x$、$),然后通过视图(v$、dba_/all_/user_)的方式提供给用户查询;MySQL数据库也是如此,将元数据信息存放在mysql库的数据字典表中隐藏起来,然后提供information_schema库视图给用户查询:
mysql>selecttable_schema,table_name,table_type,enginefrominformation_schema.tableswheretable_schema='information_schema'; +--------------------+---------------------------------------+-------------+--------+ |TABLE_SCHEMA|TABLE_NAME|TABLE_TYPE|ENGINE| +--------------------+---------------------------------------+-------------+--------+ |information_schema|ADMINISTRABLE_ROLE_AUTHORIZATIONS|SYSTEMVIEW|NULL| |information_schema|APPLICABLE_ROLES|SYSTEMVIEW|NULL| |information_schema|CHARACTER_SETS|SYSTEMVIEW|NULL| |information_schema|CHECK_CONSTRAINTS|SYSTEMVIEW|NULL| |information_schema|COLLATION_CHARACTER_SET_APPLICABILITY|SYSTEMVIEW|NULL| |information_schema|COLLATIONS|SYSTEMVIEW|NULL| |information_schema|COLUMN_PRIVILEGES|SYSTEMVIEW|NULL| |information_schema|COLUMN_STATISTICS|SYSTEMVIEW|NULL| |information_schema|COLUMNS|SYSTEMVIEW|NULL| |information_schema|ENABLED_ROLES|SYSTEMVIEW|NULL| |information_schema|ENGINES|SYSTEMVIEW|NULL| |information_schema|EVENTS|SYSTEMVIEW|NULL| |information_schema|FILES|SYSTEMVIEW|NULL| |information_schema|INNODB_BUFFER_PAGE|SYSTEMVIEW|NULL| |information_schema|INNODB_BUFFER_PAGE_LRU|SYSTEMVIEW|NULL| |information_schema|INNODB_BUFFER_POOL_STATS|SYSTEMVIEW|NULL| |information_schema|INNODB_CACHED_INDEXES|SYSTEMVIEW|NULL| |information_schema|INNODB_CMP|SYSTEMVIEW|NULL| |information_schema|INNODB_CMP_PER_INDEX|SYSTEMVIEW|NULL| |information_schema|INNODB_CMP_PER_INDEX_RESET|SYSTEMVIEW|NULL| |information_schema|INNODB_CMP_RESET|SYSTEMVIEW|NULL| |information_schema|INNODB_CMPMEM|SYSTEMVIEW|NULL| |information_schema|INNODB_CMPMEM_RESET|SYSTEMVIEW|NULL| |information_schema|INNODB_COLUMNS|SYSTEMVIEW|NULL| |information_schema|INNODB_DATAFILES|SYSTEMVIEW|NULL| |information_schema|INNODB_FIELDS|SYSTEMVIEW|NULL| |information_schema|INNODB_FOREIGN|SYSTEMVIEW|NULL| |information_schema|INNODB_FOREIGN_COLS|SYSTEMVIEW|NULL| |information_schema|INNODB_FT_BEING_DELETED|SYSTEMVIEW|NULL| |information_schema|INNODB_FT_CONFIG|SYSTEMVIEW|NULL| |information_schema|INNODB_FT_DEFAULT_STOPWORD|SYSTEMVIEW|NULL| |information_schema|INNODB_FT_DELETED|SYSTEMVIEW|NULL| |information_schema|INNODB_FT_INDEX_CACHE|SYSTEMVIEW|NULL| |information_schema|INNODB_FT_INDEX_TABLE|SYSTEMVIEW|NULL| |information_schema|INNODB_INDEXES|SYSTEMVIEW|NULL| |information_schema|INNODB_METRICS|SYSTEMVIEW|NULL| |information_schema|INNODB_SESSION_TEMP_TABLESPACES|SYSTEMVIEW|NULL| |information_schema|INNODB_TABLES|SYSTEMVIEW|NULL| |information_schema|INNODB_TABLESPACES|SYSTEMVIEW|NULL| |information_schema|INNODB_TABLESPACES_BRIEF|SYSTEMVIEW|NULL| |information_schema|INNODB_TABLESTATS|SYSTEMVIEW|NULL| |information_schema|INNODB_TEMP_TABLE_INFO|SYSTEMVIEW|NULL| |information_schema|INNODB_TRX|SYSTEMVIEW|NULL| |information_schema|INNODB_VIRTUAL|SYSTEMVIEW|NULL| |information_schema|KEY_COLUMN_USAGE|SYSTEMVIEW|NULL| |information_schema|KEYWORDS|SYSTEMVIEW|NULL| |information_schema|OPTIMIZER_TRACE|SYSTEMVIEW|NULL| |information_schema|PARAMETERS|SYSTEMVIEW|NULL| |information_schema|PARTITIONS|SYSTEMVIEW|NULL| |information_schema|PLUGINS|SYSTEMVIEW|NULL| |information_schema|PROCESSLIST|SYSTEMVIEW|NULL| |information_schema|PROFILING|SYSTEMVIEW|NULL| |information_schema|REFERENTIAL_CONSTRAINTS|SYSTEMVIEW|NULL| |information_schema|RESOURCE_GROUPS|SYSTEMVIEW|NULL| |information_schema|ROLE_COLUMN_GRANTS|SYSTEMVIEW|NULL| |information_schema|ROLE_ROUTINE_GRANTS|SYSTEMVIEW|NULL| |information_schema|ROLE_TABLE_GRANTS|SYSTEMVIEW|NULL| |information_schema|ROUTINES|SYSTEMVIEW|NULL| |information_schema|SCHEMA_PRIVILEGES|SYSTEMVIEW|NULL| |information_schema|SCHEMATA|SYSTEMVIEW|NULL| |information_schema|ST_GEOMETRY_COLUMNS|SYSTEMVIEW|NULL| |information_schema|ST_SPATIAL_REFERENCE_SYSTEMS|SYSTEMVIEW|NULL| |information_schema|ST_UNITS_OF_MEASURE|SYSTEMVIEW|NULL| |information_schema|STATISTICS|SYSTEMVIEW|NULL| |information_schema|TABLE_CONSTRAINTS|SYSTEMVIEW|NULL| |information_schema|TABLE_PRIVILEGES|SYSTEMVIEW|NULL| |information_schema|TABLES|SYSTEMVIEW|NULL| |information_schema|TABLESPACES|SYSTEMVIEW|NULL| |information_schema|TRIGGERS|SYSTEMVIEW|NULL| |information_schema|USER_PRIVILEGES|SYSTEMVIEW|NULL| |information_schema|VIEW_ROUTINE_USAGE|SYSTEMVIEW|NULL| |information_schema|VIEW_TABLE_USAGE|SYSTEMVIEW|NULL| |information_schema|VIEWS|SYSTEMVIEW|NULL| +--------------------+---------------------------------------+-------------+--------+ 73rowsinset(0.00sec) mysql>showcreatetableinformation_schema.tables\G ***************************1.row*************************** View:TABLES CreateView:CREATEALGORITHM=UNDEFINEDDEFINER=`mysql.infoschema`@`localhost`SQLSECURITYDEFINERVIEW`information_schema`.`TABLES`ASselect(`cat`.`name`collateutf8_tolower_ci)AS`TABLE_CATALOG`,(`sch`.`name`collateutf8_tolower_ci)AS`TABLE_SCHEMA`,(`tbl`.`name`collateutf8_tolower_ci)AS`TABLE_NAME`,`tbl`.`type`AS`TABLE_TYPE`,if((`tbl`.`type`='BASETABLE'),`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`isnull),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden`<>'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`table_rows`,0),coalesce(cast(`stat`.`cached_time`asunsigned),0)))AS`TABLE_ROWS`,if((`tbl`.`type`='VIEW'),NULL,internal_avg_row_length(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type`isnull),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden`<>'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`avg_row_length`,0),coalesce(cast(`stat`.`cached_time`asunsigned),0)))AS`AVG_ROW_LENGTH`,if((`tbl`.`type`='VIEW'),NULL,internal_data_length(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type`isnull),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden`<>'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`data_length`,0),coalesce(cast(`stat`.`cached_time`asunsigned),0)))AS`DATA_LENGTH`,if((`tbl`.`type`='VIEW'),NULL,internal_max_data_length(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type`isnull),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden`<>'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`max_data_length`,0),coalesce(cast(`stat`.`cached_time`asunsigned),0)))AS`MAX_DATA_LENGTH`,if((`tbl`.`type`='VIEW'),NULL,internal_index_length(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type`isnull),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden`<>'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`index_length`,0),coalesce(cast(`stat`.`cached_time`asunsigned),0)))AS`INDEX_LENGTH`,if((`tbl`.`type`='VIEW'),NULL,internal_data_free(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type`isnull),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden`<>'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`data_free`,0),coalesce(cast(`stat`.`cached_time`asunsigned),0)))AS`DATA_FREE`,if((`tbl`.`type`='VIEW'),NULL,internal_auto_increment(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type`isnull),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden`<>'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`auto_increment`,0),coalesce(cast(`stat`.`cached_time`asunsigned),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`isnull),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden`<>'Visible'),`ts`.`se_private_data`,coalesce(cast(`stat`.`update_time`asunsigned),0),coalesce(cast(`stat`.`cached_time`asunsigned),0)))AS`UPDATE_TIME`,if((`tbl`.`type`='VIEW'),NULL,internal_check_time(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type`isnull),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden`<>'Visible'),`ts`.`se_private_data`,coalesce(cast(`stat`.`check_time`asunsigned),0),coalesce(cast(`stat`.`cached_time`asunsigned),0)))AS`CHECK_TIME`,`col`.`name`AS`TABLE_COLLATION`,if((`tbl`.`type`='VIEW'),NULL,internal_checksum(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type`isnull),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden`<>'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`checksum`,0),coalesce(cast(`stat`.`cached_time`asunsigned),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`)))leftjoin`mysql`.`collations``col`on((`tbl`.`collation_id`=`col`.`id`)))leftjoin`mysql`.`tablespaces``ts`on((`tbl`.`tablespace_id`=`ts`.`id`)))leftjoin`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 1rowinset(0.00sec)
数据字典缓存
为了减少磁盘IO,提高访问效率,MySQL8.0引入了数据字典缓存。数据字典缓存是一块全局共享区域,通过LRU算法进行内存管理,具体包括:
tablespacedefinitioncachepartition:用于缓存表空间定义对象;大小限制由参数tablespace_definition_cache决定。 schemadefinitioncachepartition:用于缓存模式定义对象;大小限制由参数schema_definition_cache决定。 tabledefinitioncachepartition:用于缓存表定义对象;大小限制由参数max_connections决定。 storedprogramdefinitioncachepartition:用于缓存存储过程定义对象;大小限制由参数stored_program_definition_cache决定。 charactersetdefinitioncachepartition:用于缓存字符集定义对象;硬编码限制256个。 collationdefinitioncachepartition:用于缓存排序规则定义对象;硬编码限制256个。
原子DDL
首先,了解一下什么是原子性?原子性是指,一个事务执行要么全部成功,要么全部失败。
在MySQL8.0之前,由于不支持原子DDL,在服务进程异常挂掉或服务器异常宕机的情况下,有可能会导致数据字典、存储引擎结构、二进制日志之间的不一致。
在MySQL8.0中,数据字典均被改造成InnoDB存储引擎表,原子DDL也被引入进来。原子DDL是将数据字典更新、存储引擎操作、二进制日志写入放到同一个事务里执行,要么全部成功提交,要么全部失败回滚。
接下来,我们还是先通过一个例子,来了解一下原子DDL。在这个例子中,DROPTABLEt1,t2属于同一个事务;在5.7版本中,出现了一个事务部分、成功部分失败的情况,即DROPTABLEt1成功、DROPTABLEt2失败;但在8.0版本中,因为DROPTABLEt2失败,导致整个事务全部失败回滚;这个例子就很好地体现了原子性和非原子性的区别。
5.7版本: mysql>CREATETABLEt1(c1INT); mysql>DROPTABLEt1,t2; ERROR1051(42S02):Unknowntable'test.t2' mysql>SHOWTABLES; Emptyset(0.00sec) 8.0版本: mysql>CREATETABLEt1(c1INT); mysql>DROPTABLEt1,t2; ERROR1051(42S02):Unknowntable'test.t2' mysql>SHOWTABLES; +----------------+ |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步-数据字典更新已经成功提交,并写入redolog和binlog,那么ddl操作成功;否则,ddl操作失败,并根据ddl日志进行回滚
最后,再介绍一下,怎么查看DDL日志?
其中一个方法,是在debug级别下,访问表mysql.innodb_ddl_log进行查看(不推荐)
CREATETABLEmysql.innodb_ddl_log( idBIGINTUNSIGNEDNOTNULLAUTO_INCREMENTPRIMARYKEY, thread_idBIGINTUNSIGNEDNOTNULL, typeINTUNSIGNEDNOTNULL, space_idINTUNSIGNED, page_noINTUNSIGNED, index_idBIGINTUNSIGNED, table_idBIGINTUNSIGNED, old_file_pathVARCHAR(512)COLLATEUTF8_BIN, new_file_pathVARCHAR(512)COLLATEUTF8_BIN, KEY(thread_id) );
另一个办法,是可以将DDL日志打印到errorlog进行查看(推荐)
mysql>setglobalinnodb_print_ddl_logs=on; QueryOK,0rowsaffected(0.00sec) mysql>setglobalLOG_ERROR_VERBOSITY=3; QueryOK,0rowsaffected(0.00sec) mysql>createtabletest(idint); QueryOK,0rowsaffected(0.04sec) $tail-100fmysql-error.log 2020-08-17T19:55:09.804345+08:0073[Note][MY-012473][InnoDB]DDLloginsert:[DDLrecord:DELETESPACE,id=57,thread_id=73,space_id=12,old_file_path=./test/test.ibd] 2020-08-17T19:55:09.804396+08:0073[Note][MY-012478][InnoDB]DDLlogdelete:57 2020-08-17T19:55:09.816850+08:0073[Note][MY-012477][InnoDB]DDLloginsert:[DDLrecord:REMOVECACHE,id=58,thread_id=73,table_id=1069,new_file_path=test/test] 2020-08-17T19:55:09.816887+08:0073[Note][MY-012478][InnoDB]DDLlogdelete:58 2020-08-17T19:55:09.820623+08:0073[Note][MY-012472][InnoDB]DDLloginsert:[DDLrecord:FREE,id=59,thread_id=73,space_id=12,index_id=160,page_no=4] 2020-08-17T19:55:09.820673+08:0073[Note][MY-012478][InnoDB]DDLlogdelete:59 2020-08-17T19:55:09.837695+08:0073[Note][MY-012485][InnoDB]DDLlogpostddl:beginforthreadid:73 2020-08-17T19:55:09.837721+08:0073[Note][MY-012486][InnoDB]DDLlogpostddl:endforthreadid:73
总结
MySQL8.0对于数据字典的改进,带来了很多好处,包括元数据统一管理、数据字典缓存、information_schema性能提升、原子DDL等等。
以上就是解析MySQL8.0新特性——事务性数据字典与原子DDL的详细内容,更多关于MySQL8.0新特性的资料请关注毛票票其它相关文章!
声明:本文内容来源于网络,版权归原作者所有,内容由互联网用户自发贡献自行上传,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任。如果您发现有涉嫌版权的内容,欢迎发送邮件至:czq8825#qq.com(发邮件时,请将#更换为@)进行举报,并提供相关证据,一经查实,本站将立刻删除涉嫌侵权内容。