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）

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進行添加，相當于新建一張添加了欄位的新表，再降原表的資料復制到新表中，復制歷史資料期間的資料也會同步至新表，最后洗掉原表，將新表重命名為原表表名，實作欄位添加

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

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

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/26200.html

標籤：MySQL

上一篇：sql三表查詢問題求大神解答

下一篇：誰會呢？

mysql大表在不停機的情況下增加欄位該怎么處理

1、 環境準備

2. 直接添加欄位

3. 使用工具在線添加

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

5. 小結

1、環境準備