大家好，我是藍(lán)胖子，mysql對大表(千萬級數(shù)據(jù))的ddl語句，在生產(chǎn)上執(zhí)行時一定要千萬小心，一不小心就有可能造成業(yè)務(wù)阻塞，數(shù)據(jù)庫io和cpu飆高的情況。今天我們就來看看如何針對大表執(zhí)行ddl語句。

通過這篇文章，你能了解到下面的知識點。

傳統(tǒng)ddl 和online ddl的區(qū)別

mysql的ddl 經(jīng)過了幾個版本的演進，Online DDL這個特性是在MySQL5.6.7開始支持，在此之前mysql執(zhí)行ddl語句時，會生成新表，然后將原表數(shù)據(jù)復(fù)制到新表，整個過程是會阻塞DML語句的。

而online ddl 定義其實就是在執(zhí)行ddl語句時，不會阻塞dml語句，那么我們就稱這樣的ddl為online ddl。

ddl 的算法參數(shù)選項又分為 copy, Inplace, INSTANT ，其中copy就是之前傳統(tǒng)ddl執(zhí)行的過程，會阻塞dml語句。Inplace, INSTANT 算法執(zhí)行期間 都是可以執(zhí)行DML語句的，所以我們稱使用這兩種算法的ddl語句為online ddl。

!! ???? 但需要注意的是，并不是所有的ddl操作都支持這兩種算法，具體什么ddl操作類型支持什么算法需要去查閱官方文檔。

INSTANT 算法是mysql8.0 以后新加的，它能在秒級別對千萬級別的大表進行加字段操作，至于其他ddl 語句類型是不是也支持INSTANT 算法，需要去看下官網(wǎng)了，由于我們線上還是使用的mysql5.7 ，所以我還是會給予mysql5.7去進行分析。

在mysql5.7中，例如我們執(zhí)行下面的ddl 加字段的語句,

ALTER TABLE tbl_name ADD COLUMN column_name column_definition

mysql會去判斷當(dāng)前執(zhí)行的ddl語句類型能不能用online ddl inplace 方式，如果能用，那么它就會采用。

使用Inplace算法的ddl語句，執(zhí)行過程分為3個階段，

階段1: Initialization初始化

在初始化階段，服務(wù)器將考慮存儲引擎功能、語句中指定的操作以及用戶指定的ALGORITHM和LOCK選項，確定操作期間允許多少并發(fā)性。在此階段，使用一個可升級MDL讀鎖來保護當(dāng)前表定義。

階段2:Execution執(zhí)行

如果評估階段發(fā)現(xiàn)ddl語句不能使用inplace算法，則會將mdl讀鎖升級為排它鎖，阻塞DML語句執(zhí)行。并且，這個階段，會真正的執(zhí)行ddl語句。

階段3:Commit Table Definition 提交表定義

在提交表定義階段，MDL讀鎖升級為MDL排他鎖，以排除舊表定義并提交新表定義。一旦授予，獨占MDL鎖的持續(xù)時間就會很短。

可以看到如果使用inplcae 算法，只有在任務(wù)提交階段(時間很短)， ddl才會阻塞dml語句，因為任務(wù)提交階段會持有MDL 排他鎖，而DML 語句執(zhí)行時需要獲取MDL讀鎖，所以在此期間，DML語句會被阻塞。

具體哪些ddl操作類型支持Inplace 算法，可以查看官方文檔鏈接，比如下面的mysql5.7的文檔

https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html

如下圖所示，可以發(fā)現(xiàn)mysql5.7對加字段的ddl 支持inplace 算法，不過執(zhí)行期間需要rebuild table即建立新表，并且運行并發(fā)的dml語句執(zhí)行。但是改變字段數(shù)據(jù)類型ddl，則只能按copy算法進行執(zhí)行。

!! inplace 算法不是不會產(chǎn)生數(shù)據(jù)的復(fù)制，只是復(fù)制期間，不會阻塞dml語句的執(zhí)行。

圖片

mysql ddl 的陷阱

online ddl機制是否一定不會阻塞業(yè)務(wù)?

接著我們來看下ddl時使用inplcae 算法(online ddl)是不是一定不會阻塞業(yè)務(wù)，其實答案是顯而易見的，業(yè)務(wù)也有可能阻塞，因為online ddl 在提交表定義階段是會獲取MDL排他鎖的，如果有其他事務(wù)獲取了MDL讀鎖，那么online ddl 語句也會阻塞住，從而導(dǎo)致發(fā)生在ddl語句執(zhí)行時間點后面的那些需要獲取MDL鎖的sql阻塞掉。具體的操作例子可以查看mysql官方給出的一個例子，

https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-performance.html

ddl 過程中從庫的延遲性

ddl的第二個陷阱是要注意從庫的延遲性，比如mysql5.7加新列，雖然默認(rèn)可以使用inplace算法來讓dml語句不阻塞，但是建立新列還是需要表的rebuild操作，如果是大表，整個過程還是很慢的，如果從庫只開啟了一個線程去執(zhí)行主從復(fù)制，就會導(dǎo)致主從庫間出現(xiàn)極大的延遲。

解決辦法是開啟并行復(fù)制，可以用下面的語句在從庫上執(zhí)行，查看從庫是否開啟了并行復(fù)制

SHOW VARIABLES LIKE 'slave_parallel_workers';

online ddl Duplicate entry...錯誤

雖然使用inplace算法的ddl (online ddl) 可以不阻塞業(yè)務(wù)操作，但是在大表上執(zhí)行時，由于ddl過程比較長，還是有可能會出現(xiàn)Duplicate entry 錯誤。下面我來介紹下它出現(xiàn)的場景，比如一張幾千萬的表，里面有一個唯一鍵，在add column ddl期間，對表進行插入，并且插入的值剛好就觸發(fā)了唯一鍵約束。那么最后ddl再快完成的時候就會出現(xiàn)這個錯誤。

這是由于add column ddl期間，會發(fā)生表的rebuild，相當(dāng)于新建一個臨時表然后對舊表進行拷貝，但是ddl期間還是允許業(yè)務(wù)修改，插入數(shù)據(jù)，所以online ddl將執(zhí)行期間新的修改記錄到一個叫做row_log的對象里，在ddl最后階段，將mdl鎖升級為排它鎖，然后將row_log對象中的數(shù)據(jù)和新表的數(shù)據(jù)進行合并，這樣就達(dá)到了ddl期間兼容dml操作的目的。

但是應(yīng)用row log的過程是不允許報錯，如果期間發(fā)生了報錯就會導(dǎo)致ddl回滾，因為在ddl期間，記錄了相同唯一鍵的數(shù)據(jù)，所以在應(yīng)用row log的時候，產(chǎn)生了報錯。

官方也給出了online ddl 報錯的場景，鏈接如下

https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-failure-conditions.html

其實我認(rèn)為本質(zhì)原因是mysql5.7 執(zhí)行add column 的ddl時間還是太長了，在這么長時間里可能就會發(fā)生業(yè)務(wù)對相同唯一鍵的插入操作，如果能縮短ddl執(zhí)行時間應(yīng)該就能很大程度避免這種問題。

mysql8.0  在add column 時可以采用instance 算法，能達(dá)到秒級別的加新字段的操作，理論上可以避免這個錯誤。

如果不是mysql8.0 ，又想對千萬級的大表添加字段，又要避免Duplicate entry 錯誤，那么可以使用pt-online-schema-change這個工具。

pt-online-schema-change 工具進行字段添加

下面我就來簡單的介紹下pt-online-schema-change，它對表結(jié)構(gòu)的修改原理是創(chuàng)建一張新表(擁有最新的表定義)，然后在舊表上創(chuàng)建delete，update,insert的觸發(fā)器，來對增量數(shù)據(jù)進行更新，對舊表數(shù)據(jù)采取insert ignore 新表 select 老表 LOCK S 的方式進行分塊拷貝，最后拷貝完成后，在一個事務(wù)里對舊表進行刪除，新表進行重命名，這樣就完成了對表結(jié)構(gòu)的變更。

同時在變更期間，你能夠通過下面的參數(shù)控制從庫延遲

• --max-lag

默認(rèn)1s

檢查從庫延遲的時間，如果超過，則停止copy data，休息--check-interval秒后，再重新開始copy數(shù)據(jù)

查看通過延遲時間，是通過從庫show slave status，查看Seconds_Behind_Master

如果指定--check-slave-lag，該工具只檢查該服務(wù)器的延遲，而不是所有服務(wù)器。

• --check-interval
• 從庫延遲超過指定的--max-lag，中斷copy data休息的時間
• 默認(rèn)為1s

下面是pt-online-schema-change 語句執(zhí)行的完整示例,它同時會列出拷貝過程完成的百分比。

pt-online-schema-change --alter "add pkg_source tinyint(2) default 0 not null;" h=主機ip,P=端口,p=密碼,u=用戶名,D=數(shù)據(jù)庫名,t=表名  --recursion-method=none  --execute --statistics

如果你的ddl需要拷貝表，那么用pt-online-schema-change 工具再合適不過了。