億級大表線上不鎖表變更字段與索引

摘要：在業界中有一個比較成熟的工具，針對大表的場景，可以線上進行Alter變更，且不會出現鎖表的風險。除此之外，它還有其他的一些優點，讓我們開始探索吧。

大家在日常工作中，往往需要對資料庫的表結構做變更，一般涉及到增删字段，修改字段屬性等ALTER的操作。然而，在大表場景下，特别是千萬級、億級的大表，如果處理不當。這些操作往往會引發鎖表的巨大隐患，特别是在生産環境中，一旦在變更表結構過程中，出現了長時間鎖表，會導緻使用者産生的資料長時間無法正常變更到表中，進而導緻服務功能異常，結果将是災難性的。

一般執行這種Alter類型的變更，我們可能有以下的想法：

1、停服，在停服期間做表結構的變更，自然就可以防止對使用者産生影響。但是，很多場景是不允許停服的。并且如果表的資料量達到上億，那麼需要停服時間可能需要十幾個小時，甚至更長，這是極不現實的；

2、淩晨執行，在使用者較少的時間段内，做變更，盡量減少對使用者産生影響。但是如果出現鎖表的話，萬一有使用者使用服務，服務将不可用；

3、使用換表，但是缺點是複制資料到新表期間，如果使用者在這期間做了update或delete操作，且資料發生在已經複制完成的部分，那麼将無法感覺到這部分資料，導緻丢失掉使用者的操作資料，風險太大；

4、使用存儲過程，缺點是執行時間會很久，且有可能影響到使用者的DDL操作。因為為了防止每次循環修改時，鎖住太多資料行，我們需要控制每次更新資料的行數，粒度不能太大，否則很有可能會鎖住使用者正在操作的資料行。

那麼針對以上實際的需求，就沒有一個很好的工具，來解決我們的痛點嗎？其實在業界中，就有一個比較成熟的工具，針對大表的場景，可以線上進行Alter變更，且不會出現鎖表的風險。除此之外，它還有其他的一些優點，讓我們開始探索吧。

pt-online-schema-change是Percona-toolkit一員，通過改進原生ddl的方式，達到不鎖表線上修改表結構的效果。在Percona的官網中，關于pt-osc工具，也特别提到了ALTER表不會出現鎖表的特性。

針對上面談到的避免鎖表、感覺使用者更新删除動作等，ps-osc工具是怎麼解決的呢？

1、建立一個跟原表一模一樣的新表，命名方式為'_正式表名_new';

2、使用alter語句将要變更的内容在新建立的新表上做變更，避免了對原表的alter操作；

3、在原表中建立3個觸發器，分别是insert、update和delete，主要是用于原表在往新表複制資料時，如果使用者有DDL操作，觸發器能夠将在這期間出現的DDL操作資料也寫入到新表中，確定新表的資料是最新的，不會丢失掉使用者的新操作資料；

4、按塊拷貝資料到新表，拷貝過程對資料行持有S鎖；

5、重命名，将原表重命名為老表，命名為“_正式表名_old”，将新表重命名為正式表，可通過配置決定執行完成後是否删除掉老表；

6、删除3個觸發器；

在linux系統中安裝步驟：

pt-osc工具使用起來很簡單，直接在linux指令行輸入pt-osc格式的指令，即可直接執行。

以Mysql資料庫增加一個名字是MARK的字段為例：

在上面的語句中：

1、user和password分别為資料庫執行變更操作的使用者名、密碼，需要高權限；

2、host為資料庫的IP位址；

3、port為資料庫的端口号；

4、alter後面跟上具體的alter語句；

5、D為database名字；

6、t為要執行變更的表名；

7、no-drop-old-table就是不要删除

8、charset，字元集，使用utf8；

9、max-load，在複制資料時，工具會監控資料庫中正在運作的線程數，如果大于配置的Threads_running值，那麼會暫停複制，直到小于該值。以此防止對資料庫造成較大壓力，影響現網業務正常使用；

10、critical-load，預設為50，在每個塊之後檢查SHOW GLOBAL STATUS，與max-load不同的是，如果負載太高，,直接中止，而不是暫停。可根據自己資料庫情況斟酌配置門檻值；

注意：在--alter後面跟着的變更語句中，列名不可以加`符号，否則會出現報錯。如--alter "ADD COLUMN MARK TINYINT NULL DEFAULT 1 COMMENT 'mark source region is 1';"，MARK字段加了`符号，就會出現錯誤，COMMENT後面有`符号無影響。

下面是使用pt-osc工具，實際執行一個作業時，列印出來的資訊。為了安全起見，部分日志資訊做了隐藏忽略。

前面介紹了很多pt-osc的優點，以及良好的特性。那麼實際使用效果到底怎麼樣呢？在測試環境中，專門做了一個測試，讓大家有更加直覺的感受。

在測試庫中，準備了一張1600萬資料的大表，目标為對大表添加一個字段，分别使用存儲過程和pt-osc工具，進行測試。

首先使用存儲過程做測試，為防止鎖表，每次隻更新200行。整個變更從開始到完成，需要耗費90分鐘。其實，存儲過程在執行過程中，如果恰好使用者也在DDL操作存儲過程正在變更的資料行，還有可能會鎖住使用者的資料，導緻使用者不能變更成功。

pt-osc從開始執行到變更完成，耗時7分鐘左右，速度非常快。在執行的過程中，測試環境的服務連接配接到該資料庫，并執行多個會操作該表的任務，整個過程中，任務能夠正常執行，未出現異常情況。

ps-osc的上述優點，在現網環境的不停服等要求下，能夠優雅地幫助我們實施變更，且保證在變更期間，資料庫不會受到鎖表、過載等的影響，進而保證了業務能夠正常運轉。