1 引言

大家好，接著上次和大家一起學(xué)習(xí)了《MySQL DDL執(zhí)行方式-Online DDL介紹》，那么今天接著和大家一起學(xué)習(xí)另一種MySQL DDL執(zhí)行方式之pt-soc。

在MySQL使用過程中，根據(jù)業(yè)務(wù)的需求對表結(jié)構(gòu)進(jìn)行變更是個普遍的運(yùn)維操作，這些稱為DDL操作。常見的DDL操作有在表上增加新列或給某個列添加索引。

DDL定義：

(資料圖)

Data Definition Language，即數(shù)據(jù)定義語言，那相關(guān)的定義操作就是DDL，包括：新建、修改、刪除等；相關(guān)的命令有：CREATE，ALTER，DROP，TRUNCATE截斷表內(nèi)容（開發(fā)期，還是挺常用的），COMMENT 為數(shù)據(jù)字典添加備注。

注意：DDL操作是隱性提交的，不能rollback，一定要謹(jǐn)慎哦！

下圖是執(zhí)行方式的性能對比及說明：

圖1 易維平臺說明圖

下面本文將對DDL的執(zhí)行工具之pt-osc進(jìn)行簡要介紹及分析。如有錯誤，還請各位大佬們批評指正。

2 介紹

pt-online-schema-change- ALTER tables without locking them.

pt-online-schema-changealters a table’s structure without blocking reads or writes. Specify the database and table in the DSN. Do not use this tool before reading its documentation and checking your backups carefully.

pt-online-schema-change是Percona公司開發(fā)的一個非常好用的DDL工具，稱為 pt-online-schema-change，是Percona-Toolkit工具集中的一個組件，很多DBA在使用Percona-Toolkit時第一個使用的工具就是它，同時也是使用最頻繁的一個工具。它可以做到在修改表結(jié)構(gòu)的同時（即進(jìn)行DDL操作）不阻塞數(shù)據(jù)庫表DML的進(jìn)行，這樣降低了對生產(chǎn)環(huán)境數(shù)據(jù)庫的影響。在MySQL5.6之前是不支持Online DDL特性的，即使在添加二級索引的時候有FIC特性，但是在修改表字段的時候還是會有鎖表并阻止表的DML操作，這樣對于DBA來說是非常痛苦的，好在有pt-online-schema-change工具在沒有Online DDL時解決了這一問題。

Percona 公司是成立于2006年，總部在美國北卡羅來納的Raleigh。由 Peter Zaitsev 和 Vadim Tkachenko創(chuàng)立，這家公司聲稱他們提供的軟件都是免費(fèi)的，他們的收入主要來與開源社區(qū)，企業(yè)的支持，以及使用他們軟件的公司的支付他們提供support的費(fèi)用。而實(shí)際上這家公司"壟斷"了業(yè)內(nèi)最流行數(shù)據(jù)庫支持類的軟件，并且還開發(fā)了一些其他的與數(shù)據(jù)庫相關(guān)的東西。

Percona-Toolkit工具集是Percona支持?jǐn)?shù)據(jù)庫人員用來執(zhí)行各種MySQL、MongoDB和系統(tǒng)任務(wù)的高級命令行工具的集合，這些任務(wù)太難或太復(fù)雜而無法手動執(zhí)行。這些工具是私有或“一次性”腳本的理想替代品，因為它們是經(jīng)過專業(yè)開發(fā)、正式測試和完整記錄的。它們也是完全獨(dú)立的，因此安裝快速簡便，無需安裝任何庫。

Percona Toolkit 源自 Maatkit 和 Aspersa，這兩個最著名的 MySQL 服務(wù)器管理工?具包。它由 Percona 開發(fā)和支持。

3 工作流程

pt-osc 用于修改表時不鎖表，簡單地說，這個工具創(chuàng)建一個與原始表一樣的新的空表，并根據(jù)需要更改表結(jié)構(gòu)，然后將原始表中的數(shù)據(jù)以小塊形式復(fù)制到新表中，然后刪除原始表，然后將新表重命名為原始名稱。在復(fù)制過程中，對原始表的所有新的更改（insert，delete，update）都將應(yīng)用于新表，因為在原始表上創(chuàng)建了一個觸發(fā)器，以確保所有新的更改都將應(yīng)用于新表。有關(guān) pt-online-schema-change 工具的更多信息，請查閱手冊文檔 。

pt-osc大致的工作過程如下：

1.創(chuàng)建一個和要執(zhí)行 alter 操作的表一樣的新的空表結(jié)構(gòu)(是alter之前的結(jié)構(gòu))；

2.在新表執(zhí)行alter table 語句（速度應(yīng)該很快）；

3.在原表中創(chuàng)建觸發(fā)器3個觸發(fā)器分別對應(yīng)insert,update,delete操作，如果表中已經(jīng)定義了觸發(fā)器這個工具就不能工作了；

4.以一定塊大小從原表拷貝數(shù)據(jù)到臨時表，拷貝過程中通過原表上的觸發(fā)器在原表進(jìn)行的寫操作都會更新到新建的臨時表，保證數(shù)據(jù)不會丟失（會限制每次拷貝數(shù)據(jù)的行數(shù)以保證拷貝不會過多消耗服務(wù)器資源，采用 LOCK IN SHARE MODE 來獲取要拷貝數(shù)據(jù)段的最新數(shù)據(jù)并對數(shù)據(jù)加共享鎖阻止其他會話修改數(shù)據(jù)，不過每次加S鎖的行數(shù)不多，很快就會被釋放）；

5.將原表Rename為old表，再把新表Rename為原表（整個過程只在rename表的時間會鎖一下表，其他時候不鎖表）；

6.如果有參考該表的外鍵，根據(jù)alter-foreign-keys-method參數(shù)的值，檢測外鍵相關(guān)的表，做相應(yīng)設(shè)置的處理（根據(jù)修改后的數(shù)據(jù)，修改外鍵關(guān)聯(lián)的子表），如果被修改表存在外鍵定義但沒有使用--alter-foreign-keys-method 指定特定的值，該工具不予執(zhí)行；

7.默認(rèn)最后將舊原表刪除、觸發(fā)器刪除。

圖2 pt-osc工作過程示意圖

4 用法

Percona Toolkit 是成熟的，但是官方還是建議在使用前做到以下幾點(diǎn)：

?閱讀該工具的詳細(xì)文檔

?查看該工具的已知“錯誤”

?在非生產(chǎn)服務(wù)器上測試該工具

?備份您的生產(chǎn)數(shù)據(jù)并驗證備份

下載安裝：

從官方網(wǎng)站下載percona-toolkit，然后執(zhí)行下面的命令進(jìn)行安裝（示例）：

# 安裝依賴包yum install perl-TermReadKey.x86_64 yum install perl-DBIyum install perl-DBD-MySQLyum install perl-Time-HiResyum install perl-IO-Socket-SSL# 安裝percona-toolkitrpm -ivh percona-toolkit-3.1.0-2.el7.x86_64.rpm

執(zhí)行類似下面的命令修改表結(jié)構(gòu)：

pt-online-schema-change --alter="add column c1 int;" --execute D=test,t=table,u=user,p=password

alter參數(shù)指定修改表結(jié)構(gòu)的語句，execute表示立即執(zhí)行，D、t、u、p分別指定庫名、表名、用戶名和密碼，執(zhí)行期間不阻塞其它并行的DML語句。pt-online-schema-change還有許多選項，具體用法可以使用pt-online-schema-change --help查看聯(lián)機(jī)幫助。

5 限制

pt-online-schema-change也存在一些局限性：

1.在使用此工具之前，應(yīng)為表定義PRIMARY KEY或唯一索引，因為它是DELETE觸發(fā)器所必需的；

2.如果表已經(jīng)定義了觸發(fā)器，則不支持 pt-osc ；（注：不是不能有任何觸發(fā)器，只是不能有針對insert、update、delete的觸發(fā)器存在，因為一個表上不能有兩個相同類型的觸發(fā)器）；

3.如果表具有外鍵約束，需要使用選項--alter-foreign-keys-method，如果被修改表存在外鍵定義但沒有使用--alter-foreign-keys-method 指定特定的值，該工具不予執(zhí)行；

4.還是因為外鍵，對象名稱可能會改變（indexes names 等）；

5.在Galera集群環(huán)境中，不支持更改MyISAM表，系統(tǒng)變量 wsrep_OSU_method 必須設(shè)置為總序隔離（Total Order Isolation，TOI）；

6.此工具僅適用于 MySQL 5.0.2 及更新版本（因為早期版本不支持觸發(fā)器）；

7.需要給執(zhí)行的賬戶在 MySQL上授權(quán)，才能正確運(yùn)行。（應(yīng)在服務(wù)器上授予PROCESS、SUPER、REPLICATION SLAVE全局權(quán)限以及 SELECT、INSERT、UPDATE、DELETE、CREATE、DROP、ALTER 和 TRIGGER 表權(quán)限。Slave 只需要 REPLICATION SLAVE 和 REPLICATION CLIENT 權(quán)限。）

6 對比OnLine DDL

下面的表格是國外技術(shù)牛人進(jìn)行的測試數(shù)據(jù)，是Online DDL和pt-osc對一個包含1,078,880行的表應(yīng)用一些alter操作的對比結(jié)果，僅供參考：

online ddl	pt-osc
更改操作	受影響的行	是否鎖表	時間（秒）	受影響的行	是否鎖表	時間（秒）
添加索引	0	否	3.76	所有行	否	38.12
下降指數(shù)	0	否	0.34	所有行	否	36.04
添加列	0	否	27.61	所有行	否	37.21
重命名列	0	否	0.06	所有行	否	34.16
重命名列更改其數(shù)據(jù)類型	所有行	是	30.21	所有行	否	34.23
刪除列	0	否	22.41	所有行	否	31.57
更改表引擎	所有行	是	25.3	所有行	否	35.54

那么現(xiàn)在的問題是，我們應(yīng)該使用哪種方法來執(zhí)行alter語句呢？

雖然pt-osc允許對正在更改的表進(jìn)行讀寫操作，但它仍然會在后臺將表數(shù)據(jù)復(fù)制到臨時表，這會增加MySQL服務(wù)器的開銷。所以基本上，如果Online DDL不能有效工作，我們應(yīng)該使用 pt-sc。換句話說，如果Online DDL需要將數(shù)據(jù)復(fù)制到臨時表（algorithm=copy）并且該表將被長時間阻塞（lock=exclusive）或者在復(fù)制環(huán)境中更改大表時，我們應(yīng)該使用 pt-osc工具。

pt-osc官方文檔：https://docs.percona.com/percona-toolkit/pt-online-schema-change.html

7 總結(jié)

本次和大家一起學(xué)習(xí)了解pt-online-schema-change工具，介紹了其產(chǎn)生的背景、基本工作流程、用法及相應(yīng)的一些限制。還介紹了其與Online DDL執(zhí)行方式的一些對比，如果錯誤還請指正。

目前可用的DDL操作工具包括pt-osc，github的gh-ost，以及MySQL提供的在線修改表結(jié)構(gòu)命令Online DDL。pt-osc和gh-ost均采用拷表方式實(shí)現(xiàn)，即創(chuàng)建個空的新表，通過select+insert將舊表中的記錄逐次讀取并插入到新表中，不同之處在于處理DDL期間業(yè)務(wù)對表的DML操作。

到了MySQL 8.0 官方也對 DDL 的實(shí)現(xiàn)重新進(jìn)行了設(shè)計，其中一個最大的改進(jìn)是 DDL 操作支持了原子特性。另外，Online DDL 的 ALGORITHM 參數(shù)增加了一個新的選項：INSTANT，只需修改數(shù)據(jù)字典中的元數(shù)據(jù)，無需拷貝數(shù)據(jù)也無需重建表，同樣也無需加排他 MDL 鎖，原表數(shù)據(jù)也不受影響。整個 DDL 過程幾乎是瞬間完成的，也不會阻塞 DML，不過目前8.0的INSTANT使用范圍較小，后續(xù)再對8.0的INSTANT做詳細(xì)介紹吧。

下一期文章將和大家一起學(xué)習(xí)、了解github的gh-ost，敬請期待哦！

作者：京東物流 劉鄧忠

來源：京東云開發(fā)者社區(qū)

關(guān)鍵詞：