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

通過(guò)這篇文章，你能了解到下面的知識(shí)點(diǎn)。

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

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

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

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

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

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

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

ALTER TABLE tbl_name ADD COLUMN column_name column_definition

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

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

階段1: Initialization初始化

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

階段2:Execution執(zhí)行

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

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

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

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

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

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

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

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

圖片

mysql ddl 的陷阱

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

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

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

ddl 過(guò)程中從庫(kù)的延遲性

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

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

SHOW VARIABLES LIKE 'slave_parallel_workers';

online ddl Duplicate entry...錯(cuò)誤

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

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

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

官方也給出了online ddl 報(bào)錯(cuò)的場(chǎng)景，鏈接如下

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

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

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

如果不是mysql8.0 ，又想對(duì)千萬(wàn)級(jí)的大表添加字段，又要避免Duplicate entry 錯(cuò)誤，那么可以使用pt-online-schema-change這個(gè)工具。

pt-online-schema-change 工具進(jìn)行字段添加

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

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

• --max-lag

默認(rèn)1s

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

查看通過(guò)延遲時(shí)間，是通過(guò)從庫(kù)show slave status，查看Seconds_Behind_Master

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

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

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

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

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