解析MySQL8.0新特性——事务性数据字典与原子DDL

这篇文章主要介绍了MySQL8.0新特性——事务性数据字典与原子DDL的相关资料,帮助大家更好的理解和学习MySQL8.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系统表

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

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系统表

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

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结果里;尝试访问会报以下错误:

?

1

2

mysql> select * from mysql.tables limit 10;

ERROR 3554 (HY000): Access to data dictionary table 'mysql.tables' is rejected.

不过,在debug模式下,是可以访问这些隐藏的数据字典表的;我们重新编译安装(过程略),并以debug模式启动进程,再次尝试访问,结果如下:

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

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例外,这两张表并未记录元数据信息,只是用于记录日志):

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

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库视图给用户查询:

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

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算法进行内存管理,具体包括:

?

1

2

3

4

5

6

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失败,导致整个事务全部失败回滚;这个例子就很好地体现了原子性和非原子性的区别。

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

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进行查看(不推荐)

?

1

2

3

4

5

6

7

8

9

10

11

12

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进行查看(推荐)

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

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新特性的资料请关注钦钦技术栈其它相关文章!

原文链接:https://cloud.tencent.com/developer/article/1680993

版权声明:本文(即:原文链接:https://www.qin1qin.com/catagory/11719/)内容由互联网用户自发投稿贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 630367839@qq.com 举报,一经查实,本站将立刻删除。

(0)
上一篇 2022年 8月 14日 12:24:31
下一篇 2022年 8月 14日 12:24:41

软件定制开发公司

相关阅读

发表回复

登录后才能评论
通知:禁止投稿所有关于虚拟货币,币圈类相关文章,发现立即永久封锁账户ID!