mysql 存儲(chǔ)過(guò)程 DDL 參數(shù)

MySQL8.0 開始支持原? DDL(atomic DDL)，數(shù)據(jù)字典的更新，存儲(chǔ)引擎操作，寫?進(jìn)制日志結(jié)合成了一個(gè)事務(wù)。在沒有原?DDL之前，DROP TABLE test1,test2；如遇到server crash，可能會(huì)有test1被drop了，test2沒有被drop掉。下面來(lái)看下在MySQL8.0之前和MySQL8.0 數(shù)據(jù)字典的區(qū)別

弓長(zhǎng)嶺網(wǎng)站制作公司哪家好，找成都創(chuàng)新互聯(lián)！從網(wǎng)頁(yè)設(shè)計(jì)、網(wǎng)站建設(shè)、微信開發(fā)、APP開發(fā)、成都響應(yīng)式網(wǎng)站建設(shè)等網(wǎng)站項(xiàng)目制作，到程序開發(fā)，運(yùn)營(yíng)維護(hù)。成都創(chuàng)新互聯(lián)公司2013年成立到現(xiàn)在10年的時(shí)間，我們擁有了豐富的建站經(jīng)驗(yàn)和運(yùn)維經(jīng)驗(yàn)，來(lái)保證我們的工作的順利進(jìn)行。專注于網(wǎng)站建設(shè)就選成都創(chuàng)新互聯(lián)。

在MySQL8.0 之前，Data Dictionary除了存在與.FRM, .TRG, .OPT ?件外，還存在于系統(tǒng)表中（MyISAM ?事務(wù)引擎表中），在MySQL8.0 ，Data Dictionary 全部存在于Data Dictionary Storage Engine（即 InnoDB表中），這使crash recovery 維持原?性成為了可能

存儲(chǔ)引擎?持

目前，只有InnoDB存儲(chǔ)引擎?持原子DDL，為了實(shí)現(xiàn)原子DDL，Innodb要寫DDL logs 到 mysql.innodb_ddl_log 表，這是?個(gè)隱藏在mysql.ibd 數(shù)據(jù)字典表空間?的數(shù)據(jù)字典表。要看mysql.innodb_ddl_log 中的內(nèi)容，需要

SET GLOBAL?LOG_ERROR_VERBOSITY=3;（MySQL 8.0 默認(rèn)為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 操作步驟

準(zhǔn)備：創(chuàng)建所需的對(duì)象并將DDL?志寫入 mysql.innodb_ddl_log表中。DDL日志定義了如何前滾和回滾DDL操作。

執(zhí)行：執(zhí)?DDL操作。例如，為CREATE TABLE操作執(zhí)?創(chuàng)建。

提交：更新數(shù)據(jù)字典并提交數(shù)據(jù)字典事務(wù)。

Post-DDL：重播并從mysql.innodb_ddl_log表格中刪除DDL?志。為確?；貪L可以安全執(zhí)??不引?不?致性，在此最后階段執(zhí)??件操作（如重命名或刪除數(shù)據(jù)文件）。這一階段還從 mysql.innodb_dynamic_metadata的數(shù)據(jù)字典表刪除的動(dòng)態(tài)元數(shù)據(jù)為了DROP TABLE，TRUNCATE和其它重建表的DDL操作。

?論事務(wù)是提交還是回滾，DDL日志都會(huì)mysql.innodb_ddl_log在Post-DDL階段重播并從表中刪除 。mysql.innodb_ddl_log如果服務(wù)器在DDL操作期間暫停，DDL?志應(yīng)該只保留在表中。在這種情況下，DDL?志會(huì)在恢復(fù)后重播并刪除。

在恢復(fù)情況下，當(dāng)服務(wù)器重新啟動(dòng)時(shí)，可能會(huì)提交或回退DDL事務(wù)。如果在重做?志和?進(jìn)制日志中存在DDL操作的提交階段期間執(zhí)?的數(shù)據(jù)字典事務(wù)，則該操作被認(rèn)為是成功的并且被前滾。否則，在InnoDB重放數(shù)據(jù)字典重做日志時(shí)回滾不完整的數(shù)據(jù)字典事務(wù) ，并且回滾DDL事務(wù)。

原?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

示例

請(qǐng)點(diǎn)擊輸入圖片描述

請(qǐng)點(diǎn)擊輸入圖片描述

結(jié)論

在MySQL8.0之前，alter table 操作在server crash的情況下，會(huì)遺留.frm,.ibd文件。MySQL8.0 能實(shí)現(xiàn)原?DDL（包括 DROP TABLE, DROP SCHEMA, CREATE TABLE, TRUNCATE TABLE, ALTER TABLE），alter table 操作，在server crash的情況下，不會(huì)遺留.frm，.ibd臨時(shí)文件。讓我們?起期待MySQL8.0 GA的到來(lái)吧！

Mysql Online DDL 和 pt-ost 、gh-ost 簡(jiǎn)要

在 Mysql 5.6 之前版本中 ， 如果要修改一個(gè)表的ddl信息 ，需要鎖表 。

具體步驟如下:

下面是Mysql官方文檔對(duì)于DDL操作的總結(jié):

可以使用 Alter 語(yǔ)句支持 DDL 特性 ，比如可以用 LOCK = NONE 無(wú)鎖變更。

percona是一個(gè)開源產(chǎn)品 , 是管理Mysql的工具。

PT-OSC(Percona Toolkit Online Schema Change)

Percona Toolkit 包含很多 mysql 管理的功能 ，現(xiàn)在要說(shuō)的是 online-schema-change上

PT-OSC 原理是建表 ，使用觸發(fā)器同步數(shù)據(jù) ，然后原子性rename。

這樣可以支持在線無(wú)鎖，不停機(jī)Online-DDL 。

具體步驟如下：

Percona 有一些限制和缺陷 ，根據(jù)它的原理 ，原表不能存在觸發(fā)器 ，這玩意是唯一。另外原表必須存在PK或者UK。另外就是觸發(fā)器的問題了，觸發(fā)器帶來(lái)性能開銷，并且無(wú)法停止，那我就不能控制我同步的開關(guān)和速度。

但是gh-ost說(shuō)它可以。

go-ost基于bin-log同步 ， 基于binlog肯定都是偽裝成一個(gè)replica。

由于使用單線程回放binlog來(lái)替換觸發(fā)器，所以增量DML回放效率不如觸發(fā)器，因?yàn)閜t-osc的增量回放并發(fā)度是與業(yè)務(wù)DML并發(fā)度相同的，是多線程的。

相對(duì)于percona的優(yōu)勢(shì)是:

因?yàn)槌龅奶砹?，然后percona 和 gh-ost等等開源產(chǎn)品已經(jīng)大規(guī)模實(shí)踐了，Mysql就更加沒什么實(shí)踐案例和經(jīng)驗(yàn)了，大家就不太愿意嘗試或者遷移了。

大廠來(lái)說(shuō)基本上都是平臺(tái)封裝了，類似idb ，會(huì)把無(wú)鎖變更細(xì)節(jié)屏蔽了，只需要提工單就可以了 ，但是底層基本上也是建表同步rename個(gè)思路。

小公司的話，可以使用percona 、 go-ost 等工具。

MySQL 8.0 Online DDL和pt-osc、gh-ost深度對(duì)比分析

Mysql Online DDL

pt-online-schema-change

gh-ost

MySQL5.6在線表結(jié)構(gòu)變更（online ddl）總結(jié)

mysql 8 新特性一 ALGORITHM=INSTANT 實(shí)現(xiàn)的快速DDL

參考

在線DDL之 快速增加列（秒級(jí)別的），并不會(huì)造成業(yè)務(wù)抖動(dòng)。該功能自 MySQL 8.0.12 版本引入，是由騰訊游戲DBA團(tuán)隊(duì)貢獻(xiàn)，我國(guó)程序員還是挺厲害的嘛。注意一下，此功能只適用于 InnoDB 表。實(shí)際上MySQL 5.7就已支持 Online DDL，雖說(shuō)大部分 DDL 不影響對(duì)表DML操作，但是依然會(huì)消耗非常多的時(shí)間，且占用額外的磁盤空間，并會(huì)造成主從延遲，或者影響表的查詢速度。有了這個(gè)ALGORITHM=INSTANT 就可應(yīng)對(duì)瞬息萬(wàn)變的需求了。。

ALGORITHM=INSTANT 目前對(duì)6種ddl有效：

實(shí)際試驗(yàn)下，使用 mysql5.7的INPLACE 算法 時(shí)間: 52s。

使用 Instant Add Column ，時(shí)間:0.39 s。 果然是秒級(jí)別添加

當(dāng)然我們不需要顯式指定algorithm=instant;mysql會(huì)優(yōu)先使用INSTANT算法來(lái)進(jìn)行ddl的；若顯式指定algorithm=instant 同時(shí)目標(biāo)ddl不支持就會(huì)報(bào)錯(cuò)。如下，DROP COLUMN 時(shí)指定則報(bào)錯(cuò)

添加或刪除virtual 列

添加或刪除列默認(rèn)值

修改 ENUM 定義

修改索引類型

重命名表，好像和5.7的INPLACE算法也沒啥時(shí)間上的區(qū)別。INPLACE的rename table已經(jīng)足夠快了

還有一些特殊情況不能使用ALGORITHM=INSTANT的：

Instant Add Column只能將新字段添加到表的尾巴上，不能添加到中間!

不支持壓縮表，即該表行格式不能是 COMPRESSED。

不支持包含全文索引的表；不支持臨時(shí)表；不支持那些在數(shù)據(jù)字典表空間中創(chuàng)建的表。這些就不一一驗(yàn)證了。平時(shí)操作時(shí)要注意下！

mysql online ddl

看過(guò)這些文章：

文章之后，我覺得 mysql 5.x 的 online ddl 只是在 prepare 階段、commit 階段會(huì)有寫鎖，但是在真正執(zhí)行的階段都是讀鎖，不會(huì)阻塞讀寫。online ddl 過(guò)程如下：

1、拿 MDL 寫鎖

2、降級(jí)成 MDL 讀鎖

3、真正做 DDL

4、升級(jí)成 MDL 寫鎖

5、釋放 MDL 鎖。

1，2，4，5 階段沒有表沖突，執(zhí)行時(shí)間非常短，只是第3階段占用了 DDL 的絕大部分時(shí)間，這個(gè)期間表可以正常讀寫數(shù)據(jù)。

最后，還想說(shuō)一點(diǎn)，通過(guò) DBA 得知，8.0 加列只需要一秒，牛逼

技術(shù)分享 | MySQL 并行 DDL

隨著 MySQL 版本的不斷更新，對(duì) DDL 操作的支持也在不斷的完善和更新：比如從 MySQL 5.6 引入 Online DDL ，在 MySQL 5.7 對(duì) Online DDL 進(jìn)一步完善，到現(xiàn)在的 8.0 版本，則對(duì) DDL 的實(shí)現(xiàn)重新進(jìn)行了設(shè)計(jì)，比如 DDL 操作支持原子特性，在 MySQL 8.0.27 引入并行 DDL 。本篇就來(lái)探究一下 MySQL 8.0.27 的并行 DDL 對(duì)于 DDL 操作速度的提升。

MySQL 8.0.14 引入了 innodb_parallel_read_threads 變量來(lái)控制掃描聚簇索引的并行線程。MySQL 8.0.27 引入了 innodb_ddl_threads 變量來(lái)控制用于創(chuàng)建二級(jí)索引時(shí)的并行線程數(shù)量，此參數(shù)一般和一并引入的 innodb_ddl_buffer_size 一起使用，innodb_ddl_buffer_size 用于指定進(jìn)行并行 DDL 操作時(shí)能夠使用的 buffer 大小，buffer 是在所有的 DDL 并行線程中平均分配的，所以一般如果調(diào)大 innodb_ddl_threads 變量時(shí)，也需要調(diào)大 innodb_ddl_buffer_size 的大小。

innodb_ddl_threads 、innodb_ddl_buffer_size 和 innodb_parallel_read_threads 的默認(rèn)大小分別為：

接下來(lái)測(cè)試一下調(diào)大 innodb_ddl_threads 、innodb_ddl_buffer_size 和 innodb_parallel_read_threads 參數(shù)值對(duì) DDL 操作的性能提升。

首先創(chuàng)建一張 5000 萬(wàn)的表：

分別測(cè)試不同的線程數(shù)量和緩沖區(qū)大小的 DDL 操作時(shí)間，例如：

通過(guò)不斷調(diào)整相關(guān)參數(shù)得到以下結(jié)果：

可以看到，隨著并發(fā)線程的增多和 buffer 的增加，DDL 操作所占用的資源也越多，而 DDL 操作所花費(fèi)的時(shí)間則越少。不過(guò)通過(guò)對(duì)比資源的消耗和 DDL 速度的提升比例，最合理的并行線程數(shù)量為4-8個(gè)，而 buffer 大小可以根據(jù)情況進(jìn)行調(diào)整。

參考鏈接：

mysql 對(duì)一個(gè)大表做在線ddl，怎么進(jìn)行實(shí)施的才能盡可能降低影響

可以采用中間表。假設(shè)你原始表名是“test”，那么步驟如下

建立一個(gè)和“test”一樣表結(jié)構(gòu)的新表，表名為test_new。create table test_new like test;

將test表中數(shù)據(jù)拷貝到test_new中。insert into test_new select * from test;

在test_new上執(zhí)行ddl操作

最后將執(zhí)行過(guò)ddl更新的test_new表改名為test，原test表改名為test_old。Rename table test to test_old, test_new to test;

確認(rèn)檢查無(wú)誤后drop掉test_old表

如果test表很大，在第二步會(huì)消耗很長(zhǎng)時(shí)間，那么第二步可以以主鍵ID為準(zhǔn)，采用分段導(dǎo)入，一次導(dǎo)入比如5000條數(shù)據(jù)，多次導(dǎo)入，這樣不會(huì)對(duì)生產(chǎn)環(huán)境造成太大影響，假設(shè)test表上有自增主鍵“form_id"，那么上面第二步命令變?yōu)椋?/p>

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';

.

.

.