怎么在MySQL8.0中實現一個原子DDL語法

怎么在MySQL8.0中實現一個原子DDL語法？相信很多沒有經驗的人對此束手無策，為此本文總結了問題出現的原因和解決方法，通過這篇文章希望你能解決這個問題。

01 原子DDL介紹

    原子DDL語句將數據字典更新、存儲引擎操作和與DDL操作相關聯的二進制日志寫入合并到單個原子操作中。該操作要么提交，對數據字典、存儲引擎和二進制日志保留適用的更改，要么回滾。

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

其中：

    支持的表操作包含：

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

    支持的非表操作包含：

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

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

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

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

1、非Innodb存儲引擎的表操作

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

3、 install component和uninstallcomponent語句

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

02 部分DDL操作的執行行為變化

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

    在MySQL8.0之前，數據字典結構如下：

MySQL8.0之后，數據字典變為：

下面來看2個具體的語法變化：

(1) Drop語法的變化：

    我們給數據庫里面同時創建test1的表，并沒有test2的表，然后執行drop table test1，test2；觀察結果。

MySQL5.7表現：

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的表現：

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中，當沒有test2的時候，并沒有刪除test1這個表，它將整個語句完全回滾；而MySQL5.7中，誤刪除了test1這個表，沒有將整個語句完全回滾。

基于這種處理機制的不同，因此，我們在使用MySQL5.7版本和MySQL8.0版本做主從復制的時候，如果使用了類似上面的語句，就會發生報錯。因為二者的執行行為已經不一樣了。要想解決這個問題，需要使用drop table if not exists語法，同樣的，針對drop database、drop trigger等一系列操作，處理方法類似。還有一點值得注意，如果一個數據庫中的所有表都是innodb的，那么drop database才是原子的，否則，drop database不是原子的。

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

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

03 DDL 操作的log如何查看？

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

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操作的日志了。相關日志如下：

[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這個表的刷盤時機不受innodb_flush_logs_at_trx_commit參數的影響，這么做的目的是為了避免數據文件被DDL操作修改了，但是對應的redo log還沒有刷新到磁盤，導致恢復或者回滾的時候報錯。

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

1、準備階段：創建需要的對象，寫入DDL log到mysql.innodb_ddl_log表，DDl log定義了如何前滾和回滾DDL操作

2、執行階段：執行DDL的操作流程

3、提交階段：更新數據字典，并提交數據字典事務

4、Post-DDL階段：從mysql.innodb_ddl_log表重放并刪除DDL日志。為了確保可以安全地執行回滾而不會引起不一致，在此最后階段執行磁盤上的文件操作，例如重命名或刪除數據文件。此階段還將從mysql.innodb_dynamic_metadata數據字典表中刪除動態元數據，以用于DROP TABLE，TRUNCATE TABLE和其他重建表的DDL操作。

看完上述內容，你們掌握怎么在MySQL8.0中實現一個原子DDL語法的方法了嗎？如果還想學到更多技能或想了解更多相關內容，歡迎關注億速云行業資訊頻道，感謝各位的閱讀！