MySQL中給一張千萬甚至更大量級的表添加欄位一直是比較頭疼的問題，遇到此情況通常該如果處理？本文通過常見的三種場景進行案例說明。

1、 環境準備

資料庫版本： 5.7.25-28（Percona 分支）

伺服器配置：  3台centos 7虛擬機，配置均為2CPU  2G記憶體

資料庫架構： 1主2從的MHA架構（為了方便主從切換場景的演示，如開啟GTID，則兩節點即可），關於MHA搭建可參考此文 MySQL高可用之MHA集群部署

準備測試表：  創建一張2kw記錄的表，快速創建的方法可以參考快速創建連續數

本次對存儲過程稍作修改，多添加幾個欄位，存儲過程如下：

DELIMITER $$
CREATE  PROCEDURE `sp_createNum`(cnt INT )
BEGIN
    DECLARE i INT  DEFAULT 1;
    DROP TABLE  if exists  tb_add_columns;
    CREATE TABLE if not exists tb_add_columns(id int primary key,col1 int,col2 varchar(32));
    INSERT INTO tb_add_columns(id,col1,col2) SELECT i  as id ,i%7 as col1,md5(i) as col2;
    
    WHILE i < cnt DO
      BEGIN
        INSERT INTO tb_add_columns(id,col1,col2) SELECT id + i   as id ,( id + i) %7 as col1,md5( id + i) as col2  FROM tb_add_columns WHERE id <=cnt - i ;
        SET i = i*2;
      END;
    END WHILE;
END$$
DELIMITER ;

調用存儲過程，完成測試表及測試數據的創建。

mysql> call sp_createNum(20000000);

 2.  直接添加欄位

使用場景: 在系統不繁忙或者該表訪問不多的情況下，如符合ONLINE DDL的情況下，可以直接添加。

模擬場景： 創建一個測試腳本，每10s訪問該表隨機一條記錄，然後給該表添加欄位

訪問腳本如下：

#!/bin/bash
# gjc

for i in  {1..1000000000}                    # 訪問次數1000000000，按需調整即可
do
    id=$RANDOM                          #生成隨機數    
    mysql -uroot -p'123456' --socket=/data/mysql3306/tmp/mysql.sock  -e "select  a.*,now() from  testdb.tb_add_columns a where id = "$id     # 訪問數據
    sleep 10                            #  暫停10s
done

運行腳本

sh  test.sh

 給表添加欄位

mysql> alter table  testdb.tb_add_columns add col3 int;

  此時，訪問正常。

 附ONLINE DDL的場景如下，建議DBA們必須弄清楚

（圖片轉載於https://blog.csdn.net/finalkof1983/article/details/88355314）

 （圖片轉載於https://blog.csdn.net/finalkof1983/article/details/88355314）

3.   使用工具線上添加

雖然Online DDL添加欄位時，表依舊可以讀寫，但是生產環境使用場景中對大表操作使用最多的還是使用工具pt-osc或gh-ost添加。

本文主要介紹 pt-osc(pt-online-schema-change) 來添加欄位，該命令是Percona Toolkit工具中的使用頻率最高的一種

關於Percona Toolkit的安裝及主要使用可以參考  五分鐘學會Percona Toolkit 安裝及使用

添加欄位

root@mha1 ~]# pt-online-schema-change --alter "ADD COLUMN  col4  int" h=localhost,P=3306,p=123456,u=root,D=testdb,t=tb_add_columns,S=/data/mysql3306/tmp/mysql.sock  --charset=utf8mb4 --execute

主要過程如下：

1> Cannot connect to A=utf8mb4,P=3306,S=/data/mysql3306/tmp/mysql.sock,h=192.168.28.132,p=...,u=root
1> Cannot connect to A=utf8mb4,P=3306,S=/data/mysql3306/tmp/mysql.sock,h=192.168.28.131,p=...,u=root
No slaves found.  See --recursion-method if host mha1 has slaves.  #  因為使用的是socket方式連接資料庫 且未配置root遠程連接賬號，所以會有此提示

# A software update is available:
Operation, tries, wait:
  analyze_table, 10, 1                                     
  copy_rows, 10, 0.25                                       
  create_triggers, 10, 1　　　　　　　　　　　　　　　　　　　　  
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `testdb`.`tb_add_columns`...
Creating new table...                                     #  創建中間表，表名為"_原表名_new"
Created new table testdb._tb_add_columns_new OK.           
Altering new table...                                     #  修改表，也就是在新表上添加欄位，因新表無數據，因此很快加完
Altered `testdb`.`_tb_add_columns_new` OK.                  
2020-06-20T12:23:43 Creating triggers...                  #  創建觸發器，用於在原表拷貝到新表的過程中原表有數據的變動（新增、修改、刪除）時，也會自動同步至新表中
2020-06-20T12:23:43 Created triggers OK.
2020-06-20T12:23:43 Copying approximately 19920500 rows... # 拷貝數據，資料庫量是統計信息里的，不准確
Copying `testdb`.`tb_add_columns`:  11% 03:50 remain       #  分批拷貝數據（根據表的size切分每批拷貝多少數據），拷貝過程中可以用show processlist看到對應的sql
Copying `testdb`.`tb_add_columns`:  22% 03:22 remain
Copying `testdb`.`tb_add_columns`:  32% 03:10 remain
Copying `testdb`.`tb_add_columns`:  42% 02:45 remain
Copying `testdb`.`tb_add_columns`:  51% 02:21 remain
Copying `testdb`.`tb_add_columns`:  62% 01:48 remain
Copying `testdb`.`tb_add_columns`:  72% 01:21 remain
Copying `testdb`.`tb_add_columns`:  81% 00:53 remain
Copying `testdb`.`tb_add_columns`:  91% 00:24 remain
2020-06-20T12:28:40 Copied rows OK.                       # 拷貝數據完成
2020-06-20T12:28:40 Analyzing new table...                # 優化新表
2020-06-20T12:28:40 Swapping tables...                    # 交換表名，將原表改為"_原表名_old",然後把新表表名改為原表名
2020-06-20T12:28:41 Swapped original and new tables OK.    
2020-06-20T12:28:41 Dropping old table...                 #  刪除舊表（也可以添加參數不刪除舊表）
2020-06-20T12:28:41 Dropped old table `testdb`.`_tb_add_columns_old` OK.
2020-06-20T12:28:41 Dropping triggers...                  # 刪除觸發器
2020-06-20T12:28:41 Dropped triggers OK.
Successfully altered `testdb`.`tb_add_columns`.            # 完成

修改過程中，讀寫均不受影響，大家可以寫個程式包含讀寫的

註：  無論是直接添加欄位還是用pt-osc添加欄位，首先都得拿到該表的元數據鎖，然後才能添加（包括pt-osc在創建觸發器和最後交換表名時都涉及），因此，如果一張表是熱表，讀寫特別頻繁或者添加時被其他會話占用，則無法添加。

例如： 鎖住一條記錄

用pt-osc添加欄位，會發現一直卡在創建觸發器那一步

 此時查看對應的SQL正在等待獲取元數據鎖

換成直接添加也一樣，例如

 當達到鎖等待後將會報錯放棄添加欄位

mysql> alter table  testdb.tb_add_columns add col5 int;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

對於此情況，需等待系統不繁忙情況下添加，或者使用後續的在從庫創建再進行主從切換

4  先在從庫修改，再進行主從切換

使用場景： 如果遇到上例中一張表數據量大且是熱表（讀寫特別頻繁），則可以考慮先在從庫添加，再進行主從切換，切換後再將其他幾個節點上添加欄位。

先在從庫添加（本文在備選節點添加）

mysql> alter table  testdb.tb_add_columns add col5 int;
Query OK, 0 rows affected (1 min 1.91 sec)
Records: 0  Duplicates: 0  Warnings: 0

進行主從切換

使用MHA腳本進行線上切換

masterha_master_switch  --conf=/etc/masterha/app1.conf --master_state=alive  --orig_master_is_new_slave --new_master_host=192.168.28.131  --new_master_port=3306

切換完成後再對其他節點添加欄位

/* 原主庫上添加192.168.28.128  */
mysql>  alter table  testdb.tb_add_columns add col5 int;
Query OK, 0 rows affected (1 min 8.36 sec)
Records: 0  Duplicates: 0  Warnings: 0

/* 另一個從庫上添加192.168.28.132  */
mysql>  alter table  testdb.tb_add_columns add col5 int;
Query OK, 0 rows affected (1 min 8.64 sec)
Records: 0  Duplicates: 0  Warnings: 0

這樣就完成了欄位添加。

5.  小結

生產環境MySQL添加或修改欄位主要通過如下三種方式進行，實際使用中還有很多註意事項，大家要多多總結。

• 直接添加

如果該表讀寫不頻繁，數據量較小（通常1G以內或百萬以內），直接添加即可（可以瞭解一下online ddl的知識）

•  使用pt_osc添加

如果表較大 但是讀寫不是太大，且想儘量不影響原表的讀寫，可以用percona tools進行添加，相當於新建一張添加了欄位的新表，再降原表的數據複製到新表中，複製歷史數據期間的數據也會同步至新表，最後刪除原表，將新表重命名為原表表名，實現欄位添加

•  先在從庫添加 再進行主從切換

如果一張表數據量大且是熱表（讀寫特別頻繁），則可以考慮先在從庫添加，再進行主從切換，切換後再將其他幾個節點上添加欄位