pt-online-schema-change 過程詳解

MySQL 大字段的DDL操作：加字段、索引等，在5.1之前都是非常耗時耗力的，長時間鎖表，特別是會對MySQL服務產生影響，mysql在5.6版本增加了online ddl，但並不是所有的ddl都有效果，再加上5.6主從複製還是單線程，從機上重放主機大表ddl，主從複製的延遲肯定會加大，pt-online-schema-change是一款非常好用的熱修改數據表結構工具，個人認爲主要解決了2痛點：1.長時間鎖表造成服務不可用 2.主從複製延遲造成從機數據不可信任

下載安裝可以這裏不表，網上可查

使用限制：
1.表必須有主鍵
2.表上不可有after insert|delete|update 觸發器
3.表如果外鍵，除非使用 –alter-foreign-keys-method 指定特定的值，否則工具不予執行

數據準備：
CREATE TABLE e (
id int(11) NOT NULL AUTO_INCREMENT,
v1 int(11) DEFAULT NULL,
v2 int(11) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;

([email protected]) [tdb]> select * from e ;
+—-+——+——+
| id | v1 | v2 |
+—-+——+——+
| 1 | 4 | 1 |
| 3 | 3 | 3 |
| 5 | 1 | 5 |
+—-+——+——+

執行前日誌準備：
清空general.log: echo > general.log
set global general_log=1 ;

執行加索引：
pt-online-schema-change -uroot -p123456 -h172.16.178.148 –chunk-size 20000 –alter 「add key(id)」 D=tdb,t=e –execute

從輸出中可以大致瞭解執行過程：
創建一個新表，然後alter新表，然後創建觸發器，然後copy數據，然後交換表，然後刪除old表，然後刪除觸發器，最後返回成功alter的提示

那麼，具體在數據庫中是如何操作的呢，前面打開了general_log，現在去查看下general_log文件中的內容：

賬號連接進去後設置session基本的timeout和sql_mode,暫時沒有看出有和影響，然後獲取表的相應影響，應該是檢查主鍵，觸發器和是否有外鍵，最後到執行階段：

創建新表：
CREATE TABLE tdb._e_new

alter 新表：
ALTER TABLE tdb._e_new add key(id)

老表增加trigger：
CREATE TRIGGER pt_osc_tdb_e_del AFTER DELETE ON tdb.e
CREATE TRIGGER pt_osc_tdb_e_del AFTER UPDATE ON tdb.e
CREATE TRIGGER pt_osc_tdb_e_del AFTER INSERTON tdb.e

copy數據：
INSERT LOW_PRIORITY IGNORE INTO tdb._e_new (id, v1, v2) SELECT id, v1, v2 FROM tdb.e LOCK IN SHARE MODE
此處有鎖，測試中看到的是 e表中（e是小表）所有記錄加了共享鎖，只能讀不能寫，此處看不出此工具的優勢(影響在線修改)。其實此工具在大表copy數據時是分批copy， –chunk-size 設置的是copy數據的大小 ，
INSERT LOW_PRIORITY IGNORE INTO tdb._d_new (id, v1, v2, v3) SELECT id, v1, v2, v3 FROM tdb.d FORCE INDEX(PRIMARY) WHERE ((id >= ‘982118’)) AND ((id <= ‘999999’)) LOCK IN SHARE MODE
這樣鎖定就只鎖定了 id 一個範圍內的數據，不在範圍內的不受影響，所以在大表時才能體現優勢。

表rename:
RENAME TABLE tdb.e TO tdb._e_old, tdb._e_new TO tdb.e
這是一個原子操作，2表rename同時成功，且會短暫鎖表

drop old 表:
DROP TABLE IF EXISTS tdb._e_old

drop trigger:
DROP TRIGGER IF EXISTS tdb.pt_osc_tdb_e_del
DROP TRIGGER IF EXISTS tdb.pt_osc_tdb_e_upd
DROP TRIGGER IF EXISTS tdb.pt_osc_tdb_e_ins

返回執行成功

查看整個過程，只有2個位置有鎖，copy數據分批鎖數據，每次都短時間影響少量數據，rename短暫鎖表，這樣主庫上業務基本不受影響，從機的複製也沒有大事務造成延遲。