本文目錄一覽：
- 1、常用的DDL、DML操作屬於Mysql邏輯體系里的哪一層
- 2、數據庫主從DDL是什麼
- 3、關於mysql 56 哪些操作可以進行online ddl 詳解
- 4、讓ddl sql到mysql數據庫中執行很久時間，有沒有這樣的辦法的
- 5、mysql 存儲過程 DDL 參數
- 6、mysql 對一個大表做在線ddl，怎麼進行實施的才能儘可能降低影響
常用的DDL、DML操作屬於Mysql邏輯體系里的哪一層
屬於數據庫層。
MySQL支持DML(數據操作語言)、DDL(數據定義語言)、存儲過程、視圖、觸發器、自定義函數等多種SQL語言接口。
數據庫主從DDL是什麼
談到MySQL數據庫主從同步延遲原理，得從mysql的數據庫主從複製原理說起，mysql的主從複製都是單線程的操作，主庫對所有DDL和DML產生binlog，binlog是順序寫，所以效率很高，slave的Slave_IO_Running線程到主庫取日誌，效率很比較高，下一步，問題來了，slave的Slave_SQL_Running線程將主庫的DDL和DML操作在slave實施。DML和DDL的IO操作是隨即的，不是順序的，成本高很多，還可能可slave上的其他查詢產生lock爭用，由於Slave_SQL_Running也是單線程的，所以一個DDL卡主了，需要執行10分鐘，那麼所有之後的DDL會等待這個DDL執行完才會繼續執行，這就導致了延時。有朋友會問：「主庫上那個相同的DDL也需要執行10分，為什麼slave會延時？」，答案是master可以並發，Slave_SQL_Running線程卻不可以。
關於mysql 56 哪些操作可以進行online ddl 詳解
實際測試中add primary key 和修改字段類型是需要copy tmp table的並且阻塞dml操作，另外在5617版本之前時候用alter table table_name engine=innodb 是需要 copy table的並且也阻塞dml。 本來打算翻譯成中文，後來發現這些英文實在是太簡單了關於mysql 56 哪些操作可以進行online ddl 詳解
讓ddl sql到mysql數據庫中執行很久時間，有沒有這樣的辦法的
具體操作如下：
先看看看event 事件是否開啟
show variables like ‘%sche%’;
如沒開啟，則開啟。需要數據庫超級權限
set global event_scheduler =1;
創建存儲過程 update_a (註：就是你要執行的sql語句)
mysql create procedure update_a() update a set a.y_avg=(select avg(b.youhao) from b where a.a_id=b.a_id);
創建一個定時任務：event e_updateA
mysql create event if not exists e_updateA
– on schedule every 60 second —設置60秒執行一次
– on schedule at date_add(now(),interval 1 minute) —在一分鐘後執行
– on completion preserve
– do call update_a(); —執行update_a()存儲過程
創建Event之後，sql語句就定時執行一次。
關閉事件任務
mysql alter event e_updateA ON
– COMPLETION PRESERVE DISABLE;
開啟事件任務
mysql alter event e_updateA ON
– COMPLETION PRESERVE ENABLE;
mysql 存儲過程 DDL 參數
MySQL8.0 開始支持原⼦ DDL(atomic DDL)，數據字典的更新，存儲引擎操作，寫⼆進制日誌結合成了一個事務。在沒有原⼦DDL之前，DROP TABLE test1,test2；如遇到server crash，可能會有test1被drop了，test2沒有被drop掉。下面來看下在MySQL8.0之前和MySQL8.0 數據字典的區別
在MySQL8.0 之前，Data Dictionary除了存在與.FRM, .TRG, .OPT ⽂件外，還存在於系統表中（MyISAM ⾮事務引擎表中），在MySQL8.0 ，Data Dictionary 全部存在於Data Dictionary Storage Engine（即 InnoDB表中），這使crash recovery 維持原⼦性成為了可能
存儲引擎⽀持
目前，只有InnoDB存儲引擎⽀持原子DDL，為了實現原子DDL，Innodb要寫DDL logs 到 mysql.innodb_ddl_log 表，這是⼀個隱藏在mysql.ibd 數據字典表空間⾥的數據字典表。要看mysql.innodb_ddl_log 中的內容，需要
SET GLOBAL LOG_ERROR_VERBOSITY=3;（MySQL 8.0 默認為2，error log 記錄Errors and
warnings，不不記錄notes）
SET GLOBAL innodb_print_ddl_logs=1;
CREATE TABLE t1 (c1 INT) ENGINE = InnoDB;
查看error log
[Note] [MY-011066] InnoDB: DDL log insert : [DDL record: DELETE SPACE, id=30,
thread_id=25, space_id=9, old_file_path=./test/t1.ibd]
[Note] [MY-011066] InnoDB: DDL log delete : by id 30
[Note] [MY-011066] InnoDB: DDL log insert : [DDL record: REMOVE CACHE, id=31,
thread_id=25, table_id=1066, new_file_path=test/t1]
[Note] [MY-011066] InnoDB: DDL log delete : by id 31
[Note] [MY-011066] InnoDB: DDL log insert : [DDL record: FREE, id=32, thread_
id=25, space_id=9, index_id=143, page_no=4]
[Note] [MY-011066] InnoDB: DDL log delete : by id 32
[Note] [MY-011066] InnoDB: DDL log post ddl : begin for thread id : 25
[Note] [MY-011066] InnoDB: DDL log post ddl : end for thread id : 25
原子DDL 操作步驟
準備：創建所需的對象並將DDL⽇志寫入 mysql.innodb_ddl_log表中。DDL日誌定義了如何前滾和回滾DDL操作。
執行：執⾏DDL操作。例如，為CREATE TABLE操作執⾏創建。
提交：更新數據字典並提交數據字典事務。
Post-DDL：重播並從mysql.innodb_ddl_log表格中刪除DDL⽇志。為確保回滾可以安全執⾏⽽不引⼊不⼀致性，在此最後階段執⾏⽂件操作（如重命名或刪除數據文件）。這一階段還從 mysql.innodb_dynamic_metadata的數據字典表刪除的動態元數據為了DROP TABLE，TRUNCATE和其它重建表的DDL操作。
⽆論事務是提交還是回滾，DDL日誌都會mysql.innodb_ddl_log在Post-DDL階段重播並從表中刪除 。mysql.innodb_ddl_log如果服務器在DDL操作期間暫停，DDL⽇志應該只保留在表中。在這種情況下，DDL⽇志會在恢復後重播並刪除。
在恢復情況下，當服務器重新啟動時，可能會提交或回退DDL事務。如果在重做⽇志和⼆進制日誌中存在DDL操作的提交階段期間執⾏的數據字典事務，則該操作被認為是成功的並且被前滾。否則，在InnoDB重放數據字典重做日誌時回滾不完整的數據字典事務 ，並且回滾DDL事務。
原⼦DDL ⽀持類型
• DROP TABLES , all tables dropped or none
• DROP SCHEMA, all entities in the schema are dropped, or none
• Note that atomic DDL statements will be rolled back or committed even in case of crash, e.g. RENAME TABLES
• CREATE TABLE would be successfully committed or rolled back (no orphan ibd left)
• TRUNCATE TABLE (including InnoDB tables with FTS AUX tables) would be successfully committed or rolled back
• RENAME TABLES, all or none
• ALTER TABLE successful or not done
示例
請點擊輸入圖片描述
請點擊輸入圖片描述
結論
在MySQL8.0之前，alter table 操作在server crash的情況下，會遺留.frm,.ibd文件。MySQL8.0 能實現原⼦DDL（包括 DROP TABLE, DROP SCHEMA, CREATE TABLE, TRUNCATE TABLE, ALTER TABLE），alter table 操作，在server crash的情況下，不會遺留.frm，.ibd臨時文件。讓我們⼀起期待MySQL8.0 GA的到來吧！
mysql 對一個大表做在線ddl，怎麼進行實施的才能儘可能降低影響
可以採用中間表。假設你原始表名是「test」，那麼步驟如下
建立一個和「test」一樣表結構的新表，表名為test_new。create table test_new like test;
將test表中數據拷貝到test_new中。insert into test_new select * from test;
在test_new上執行ddl操作
最後將執行過ddl更新的test_new表改名為test，原test表改名為test_old。Rename table test to test_old, test_new to test;
確認檢查無誤後drop掉test_old表
如果test表很大，在第二步會消耗很長時間，那麼第二步可以以主鍵ID為準，採用分段導入，一次導入比如5000條數據，多次導入，這樣不會對生產環境造成太大影響，假設test表上有自增主鍵「form_id”，那麼上面第二步命令變為：
insert into test_new select * from test where form_id between ‘1’ and ‘5000’;
insert into test_new select * from test where form_id between ‘5001’ and ‘10000’;
.
.
.
原創文章，作者：NR0SP，如若轉載，請註明出處：https://www.506064.com/zh-hk/n/129294.html
微信掃一掃 
支付寶掃一掃 