詳解MySQL8.0原子DDL語法

01 原子DDL介紹

    原子DDL語句將數(shù)據(jù)字典更新、存儲(chǔ)引擎操作和與DDL操作相關(guān)聯(lián)的二進(jìn)制日志寫入合并到單個(gè)原子操作中。該操作要么提交，對(duì)數(shù)據(jù)字典、存儲(chǔ)引擎和二進(jìn)制日志保留適用的更改，要么回滾。

    在MySQL8.0中，原子DDL操作這一特性，支持表相關(guān)操作，例如create table、drop table等，也支持非表相關(guān)操作，例如create routine、drop trigger等。

其中：

    支持的表操作包含：

drop、create、alter(操作對(duì)象是databases, tablespaces, tables, and indexes)語法、truncate語法

    支持的非表操作包含：

create、drop、alter(操作對(duì)象是trigger、event、views、)

帳戶管理語句：用戶和角色的create、alter、drop和rename語句，以及grant和revoke語句

    需要注意的是：跟表相關(guān)的DDL操作，需要保證存儲(chǔ)引擎是Innodb的，非表相關(guān)的操作，則沒有要求。

有些SQL語句不支持原子DDL，例如：

1、非Innodb存儲(chǔ)引擎的表操作

2、install plugin和uninstall plugin操作(安裝插件)

3、 install component和uninstallcomponent語句

4、create server、alter server和drop server語句(該語句是FEDERATED存儲(chǔ)引擎使用的，可暫時(shí)忽略)

02 部分DDL操作的執(zhí)行行為變化

    原子操作的執(zhí)行行為變化，跟數(shù)據(jù)字典的組織結(jié)構(gòu)變化有關(guān)，在MySQL8.0 之前，Data Dictionary除了存在與.FRM, .TRG, .OPT 文件外，還存在于系統(tǒng)表中（MyISAM 非事務(wù)引擎表中），在MySQL8.0 ，Data Dictionary 全部存在于Data Dictionary Storage Engine（即 InnoDB表中），這使crash recovery 維持原子性成為了可能。下面的圖描述了數(shù)據(jù)字典的結(jié)構(gòu)變化。

    在MySQL8.0之前，數(shù)據(jù)字典結(jié)構(gòu)如下：

MySQL8.0之后，數(shù)據(jù)字典變?yōu)椋?/p>

下面來看2個(gè)具體的語法變化：

(1) Drop語法的變化：

    我們給數(shù)據(jù)庫里面同時(shí)創(chuàng)建test1的表，并沒有test2的表，然后執(zhí)行drop table test1，test2；觀察結(jié)果。

MySQL5.7表現(xiàn)：

mysql> create table test1(id int);
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+----------------+
| Tables_in_yeyz |
+----------------+
| t1    |
| t2    |
| t3    |
| test1   |
+----------------+
4 rows in set (0.00 sec)

mysql> drop table test1,test2;
ERROR 1051 (42S02): Unknown table 'yeyz.test2'
mysql> show tables;
+----------------+
| Tables_in_yeyz |
+----------------+
| t1    |
| t2    |
| t3    |
+----------------+
3 rows in set (0.00 sec)

MySQL8.0的表現(xiàn)：

mysql> create table test1(id int);
Query OK, 0 rows affected (0.17 sec)

mysql> show tables;
+----------------+
| Tables_in_yeyz |
+----------------+
| test1   |
+----------------+
1 row in set (0.00 sec)

mysql> drop table test1,test2;
ERROR 1051 (42S02): Unknown table 'yeyz.test2'
mysql> show tables;
+----------------+
| Tables_in_yeyz |
+----------------+
| test1   |
+----------------+
1 row in set (0.00 sec)

可以看到，MySQL8.0中，當(dāng)沒有test2的時(shí)候，并沒有刪除test1這個(gè)表，它將整個(gè)語句完全回滾；而MySQL5.7中，誤刪除了test1這個(gè)表，沒有將整個(gè)語句完全回滾。

基于這種處理機(jī)制的不同，因此，我們?cè)谑褂肕ySQL5.7版本和MySQL8.0版本做主從復(fù)制的時(shí)候，如果使用了類似上面的語句，就會(huì)發(fā)生報(bào)錯(cuò)。因?yàn)槎叩膱?zhí)行行為已經(jīng)不一樣了。要想解決這個(gè)問題，需要使用drop table if not exists語法，同樣的，針對(duì)drop database、drop trigger等一系列操作，處理方法類似。還有一點(diǎn)值得注意，如果一個(gè)數(shù)據(jù)庫中的所有表都是innodb的，那么drop database才是原子的，否則，drop database不是原子的。

(2) Create Table...Select 語法：

    從MySQL 8.0.21開始，在支持原子DDL的存儲(chǔ)引擎上，當(dāng)使用基于row的復(fù)制模式時(shí)，CREATE TABLE...SELECT...，該語句作為一個(gè)事務(wù)記錄在二進(jìn)制日志中。之前的版本中，它被記錄為兩個(gè)事務(wù)，一個(gè)用于create表，另一個(gè)用于insert數(shù)據(jù)。兩個(gè)事務(wù)之間或插入數(shù)據(jù)時(shí)發(fā)生服務(wù)器故障可能導(dǎo)致復(fù)制了一張空表。通過引入原子DDL支持，CREATE TABLE ...SELECT語句現(xiàn)在對(duì)于基于行的復(fù)制是安全的，并且允許與基于GTID的復(fù)制一起使用。

03 DDL 操作的log如何查看？

    為了支持DDL操作的redo和rollback，InnoDB將DDL日志寫入mysql.innodb_ddl_log表中，這個(gè)表存在于數(shù)據(jù)字典表空間中，如果用戶想要看這個(gè)表里面的內(nèi)容，需要打開參數(shù)：

mysql> show variables like '%innodb_print_ddl_logs%'; 
+-----------------------+-------+
| Variable_name   | Value |
+-----------------------+-------+
| innodb_print_ddl_logs | OFF |
+-----------------------+-------+
1 row in set (0.01 sec)

然后就可以在error log日志中看到ddl操作的日志了。相關(guān)日志如下：

[Note] [000000] InnoDB: DDL log insert : [DDL record: DELETE SPACE, id=18, thread_id=7,
space_id=5, old_file_path=./test/t1.ibd]
[Note] [000000] InnoDB: DDL log delete : by id 18
[Note] [000000] InnoDB: DDL log insert : [DDL record: REMOVE CACHE, id=19, thread_id=7,
table_id=1058, new_file_path=test/t1]
[Note] [000000] InnoDB: DDL log delete : by id 19
[Note] [000000] InnoDB: DDL log insert : [DDL record: FREE, id=20, thread_id=7,
space_id=5, index_id=132, page_no=4]
[Note] [000000] InnoDB: DDL log delete : by id 20
[Note] [000000] InnoDB: DDL log post ddl : begin for thread id : 7
[Note] [000000] InnoDB: DDL log post ddl : end for thread id : 7

 mysql.innodb_ddl_log這個(gè)表的刷盤時(shí)機(jī)不受innodb_flush_logs_at_trx_commit參數(shù)的影響，這么做的目的是為了避免數(shù)據(jù)文件被DDL操作修改了，但是對(duì)應(yīng)的redo log還沒有刷新到磁盤，導(dǎo)致恢復(fù)或者回滾的時(shí)候報(bào)錯(cuò)。

   最后，我們介紹下整個(gè)原子DDL操作的幾個(gè)階段：

1、準(zhǔn)備階段：創(chuàng)建需要的對(duì)象，寫入DDL log到mysql.innodb_ddl_log表，DDl log定義了如何前滾和回滾DDL操作

2、執(zhí)行階段：執(zhí)行DDL的操作流程

3、提交階段：更新數(shù)據(jù)字典，并提交數(shù)據(jù)字典事務(wù)

4、Post-DDL階段：從mysql.innodb_ddl_log表重放并刪除DDL日志。為了確?？梢园踩貓?zhí)行回滾而不會(huì)引起不一致，在此最后階段執(zhí)行磁盤上的文件操作，例如重命名或刪除數(shù)據(jù)文件。此階段還將從mysql.innodb_dynamic_metadata數(shù)據(jù)字典表中刪除動(dòng)態(tài)元數(shù)據(jù)，以用于DROP TABLE，TRUNCATE TABLE和其他重建表的DDL操作。

以上就是詳解MySQL8.0原子DDL語法的詳細(xì)內(nèi)容，更多關(guān)于MySQL8.0原子DDL語法的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！