1 引言
大家好，接着上次和大家一起學習了《MySQL DDL執行方式-Online DDL介紹》，那麼今天接着和大家一起學習另一種MySQL DDL執行方式之pt-soc。
在MySQL使用過程中，根據業務的需求對表結構進行變更是個普遍的運維操作，這些稱為DDL操作。常見的DDL操作有在表上增加新列或給某個列添加索引。
DDL定義：
Data Definition Language，即資料定義語言，那相關的定義操作就是DDL，包括：建立、修改、删除等；相關的指令有：CREATE，ALTER，DROP，TRUNCATE截斷表内容（開發期，還是挺常用的），COMMENT 為資料字典添加備注。
注意：DDL操作是隐性送出的，不能rollback，一定要謹慎哦！
下圖是執行方式的性能對比及說明：
圖1 易維平台說明圖
下面本文将對DDL的執行工具之pt-osc進行簡要介紹及分析。如有錯誤，還請各位大佬們批評指正。
2 介紹
pt-online-schema-change - ALTER tables without locking them.
pt-online-schema-change alters 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公司開發的一個非常好用的DDL工具，稱為 pt-online-schema-change，是Percona-Toolkit工具集中的一個元件，很多DBA在使用Percona-Toolkit時第一個使用的工具就是它，同時也是使用最頻繁的一個工具。它可以做到在修改表結構的同時（即進行DDL操作）不阻塞資料庫表DML的進行，這樣降低了對生産環境資料庫的影響。在MySQL5.6之前是不支援Online DDL特性的，即使在添加二級索引的時候有FIC特性，但是在修改表字段的時候還是會有鎖表并阻止表的DML操作，這樣對于DBA來說是非常痛苦的，好在有pt-online-schema-change工具在沒有Online DDL時解決了這一問題。
Percona 公司是成立于2006年，總部在美國北卡羅來納的Raleigh。由 Peter Zaitsev 和 Vadim Tkachenko創立， 這家公司聲稱他們提供的軟體都是免費的，他們的收入主要來與開源社群，企業的支援，以及使用他們軟體的公司的支付他們提供support的費用。 而實際上這家公司"壟斷"了業内最流行資料庫支援類的軟體，并且還開發了一些其他的與資料庫相關的東西。
Percona-Toolkit工具集是Percona支援資料庫人員用來執行各種MySQL、MongoDB和系統任務的進階指令行工具的集合，這些任務太難或太複雜而無法手動執行。這些工具是私有或“一次性”腳本的理想替代品，因為它們是經過專業開發、正式測試和完整記錄的。它們也是完全獨立的，是以安裝快速簡便，無需安裝任何庫。
Percona Toolkit 源自 Maatkit 和 Aspersa，這兩個最著名的 MySQL 伺服器管理工具包。它由 Percona 開發和支援。
3 工作流程
pt-osc 用于修改表時不鎖表，簡單地說，這個工具建立一個與原始表一樣的新的空表，并根據需要更改表結構，然後将原始表中的資料以小塊形式複制到新表中，然後删除原始表，然後将新表重命名為原始名稱。在複制過程中，對原始表的所有新的更改（insert，delete，update）都将應用于新表，因為在原始表上建立了一個觸發器，以確定所有新的更改都将應用于新表。有關 pt-online-schema-change 工具的更多資訊，請查閱手冊文檔 。
pt-osc大緻的工作過程如下：
1.建立一個和要執行 alter 操作的表一樣的新的空表結構(是alter之前的結構)；
2.在新表執行alter table 語句（速度應該很快）；
3.在原表中建立觸發器3個觸發器分别對應insert,update,delete操作，如果表中已經定義了觸發器這個工具就不能工作了；
4.以一定塊大小從原表拷貝資料到臨時表，拷貝過程中通過原表上的觸發器在原表進行的寫操作都會更新到建立的臨時表，保證資料不會丢失（會限制每次拷貝資料的行數以保證拷貝不會過多消耗伺服器資源，采用 LOCK IN SHARE MODE 來擷取要拷貝資料段的最新資料并對資料加共享鎖阻止其他會話修改資料，不過每次加S鎖的行數不多，很快就會被釋放）；
5.将原表Rename為old表，再把新表Rename為原表（整個過程隻在rename表的時間會鎖一下表，其他時候不鎖表）；
6.如果有參考該表的外鍵，根據alter-foreign-keys-method參數的值，檢測外鍵相關的表，做相應設定的處理（根據修改後的資料，修改外鍵關聯的子表），如果被修改表存在外鍵定義但沒有使用 --alter-foreign-keys-method 指定特定的值，該工具不予執行；
7.預設最後将舊原表删除、觸發器删除。
圖2 pt-osc工作過程示意圖
4 用法
Percona Toolkit 是成熟的，但是官方還是建議在使用前做到以下幾點：
•閱讀該工具的詳細文檔
•檢視該工具的已知“錯誤”
•在非生産伺服器上測試該工具
•備份您的生産資料并驗證備份
下載下傳安裝：
從官方網站下載下傳percona-toolkit，然後執行下面的指令進行安裝（示例）：
# 安裝依賴包
yum install perl-TermReadKey.x86_64
yum install perl-DBI
yum install perl-DBD-MySQL
yum install perl-Time-HiRes
yum install perl-IO-Socket-SSL
# 安裝percona-toolkit
rpm -ivh percona-toolkit-3.1.0-2.el7.x86_64.rpm 執行類似下面的指令修改表結構：
pt-online-schema-change --alter="add column c1 int;" --execute D=test,t=table,u=user,p=password alter參數指定修改表結構的語句，execute表示立即執行，D、t、u、p分别指定庫名、表名、使用者名和密碼，執行期間不阻塞其它并行的DML語句。pt-online-schema-change還有許多選項，具體用法可以使用pt-online-schema-change --help檢視聯機幫助。
5 限制
pt-online-schema-change也存在一些局限性：
1.在使用此工具之前，應為表定義PRIMARY KEY或唯一索引，因為它是DELETE觸發器所必需的；
2.如果表已經定義了觸發器，則不支援 pt-osc ；（注：不是不能有任何觸發器，隻是不能有針對insert、update、delete的觸發器存在，因為一個表上不能有兩個相同類型的觸發器）；
3.如果表具有外鍵限制，需要使用選項 --alter-foreign-keys-method，如果被修改表存在外鍵定義但沒有使用 --alter-foreign-keys-method 指定特定的值，該工具不予執行；
4.還是因為外鍵，對象名稱可能會改變（indexes names 等）；
5.在Galera叢集環境中，不支援更改MyISAM表，系統變量 wsrep_OSU_method 必須設定為總序隔離（Total Order Isolation，TOI）；
6.此工具僅适用于 MySQL 5.0.2 及更新版本（因為早期版本不支援觸發器）；
7.需要給執行的賬戶在 MySQL上授權，才能正确運作。（應在伺服器上授予PROCESS、SUPER、REPLICATION SLAVE全局權限以及 SELECT、INSERT、UPDATE、DELETE、CREATE、DROP、ALTER 和 TRIGGER 表權限。Slave 隻需要 REPLICATION SLAVE 和 REPLICATION CLIENT 權限。）
6 對比OnLine DDL
下面的表格是國外技術牛人進行的測試資料，是Online DDL和pt-osc對一個包含1,078,880行的表應用一些alter操作的對比結果，僅供參考：
| online ddl | pt-osc | |||||
| 更改操作 | 受影響的行 | 是否鎖表 | 時間（秒） | 受影響的行 | 是否鎖表 | 時間（秒） |
| 添加索引 | 否 | 3.76 | 所有行 | 否 | 38.12 | |
| 下降指數 | 否 | 0.34 | 所有行 | 否 | 36.04 | |
| 添加列 | 否 | 27.61 | 所有行 | 否 | 37.21 | |
| 重命名列 | 否 | 0.06 | 所有行 | 否 | 34.16 | |
| 重命名列更改其資料類型 | 所有行 | 是 | 30.21 | 所有行 | 否 | 34.23 |
| 删除列 | 否 | 22.41 | 所有行 | 否 | 31.57 | |
| 更改表引擎 | 所有行 | 是 | 25.3 | 所有行 | 否 | 35.54 |
那麼現在的問題是，我們應該使用哪種方法來執行alter語句呢？
雖然pt-osc允許對正在更改的表進行讀寫操作，但它仍然會在背景将表資料複制到臨時表，這會增加MySQL伺服器的開銷。是以基本上，如果Online DDL不能有效工作，我們應該使用 pt-sc。換句話說，如果Online DDL需要将資料複制到臨時表（algorithm=copy）并且該表将被長時間阻塞（lock=exclusive）或者在複制環境中更改大表時，我們應該使用 pt-osc工具。
pt-osc官方文檔：https://docs.percona.com/percona-toolkit/pt-online-schema-change.html
7 總結
本次和大家一起學習了解pt-online-schema-change工具，介紹了其産生的背景、基本工作流程、用法及相應的一些限制。還介紹了其與Online DDL執行方式的一些對比，如果錯誤還請指正。
目前可用的DDL操作工具包括pt-osc，github的gh-ost，以及MySQL提供的線上修改表結構指令Online DDL。pt-osc和gh-ost均采用拷表方式實作，即建立個空的新表，通過select+insert将舊表中的記錄逐次讀取并插入到新表中，不同之處在于處理DDL期間業務對表的DML操作。
到了MySQL 8.0 官方也對 DDL 的實作重新進行了設計，其中一個最大的改進是 DDL 操作支援了原子特性。另外，Online DDL 的 ALGORITHM 參數增加了一個新的選項：INSTANT，隻需修改資料字典中的中繼資料，無需拷貝資料也無需重建表，同樣也無需加排他 MDL 鎖，原表資料也不受影響。整個 DDL 過程幾乎是瞬間完成的，也不會阻塞 DML，不過目前8.0的INSTANT使用範圍較小，後續再對8.0的INSTANT做詳細介紹吧。
下一期文章将和大家一起學習、了解github的gh-ost，敬請期待哦！
作者：京東物流 劉鄧忠
來源：京東雲開發者社群