SQL百萬千萬級最大表添加字段的實現

更新時間：2025年04月24日 10:59:23 作者：灰_灰丶灰

本文主要介紹了SQL百萬千萬級最大表添加字段,需要特別小心,以避免長時間的表鎖定和潛在的性能問題,具有一定的參考價值,感興趣的可以了解一下

1. 直接添加字段

在MySQL 8.0和更高版本中，添加列的操作通常是在線的，并且不會長時間鎖定表。但是在MySQL 5.7及以下版本，添加列操作會導致表的完全重建，從而鎖定表。

ALTER TABLE your_table ADD COLUMN new_column datatype;

2. 使用 pt-online-schema-change

pt-online-schema-change 是 Percona Toolkit 中的一個工具，可以在線修改表的結構，而不會長時間鎖定表。

pt-online-schema-change --alter "ADD COLUMN new_column datatype" D=database,t=your_table --execute

3. 分區(qū)表

如果表使用了分區(qū)，可以考慮在每個分區(qū)上單獨進行操作，以減少對整個表的影響。

4. 復制表

可以通過復制表的方法來實現：

創(chuàng)建新表，并在創(chuàng)建時添加所需的新列。
將舊表的數據復制到新表中。
在適當的時候，將舊表重命名為備份表，并將新表重命名為原始表。

-- 1. 創(chuàng)建新表
CREATE TABLE new_table LIKE your_table;
ALTER TABLE new_table ADD COLUMN new_column datatype;

-- 2. 復制數據
INSERT INTO new_table SELECT *, NULL AS new_column FROM your_table;

-- 3. 重命名表
RENAME TABLE your_table TO backup_table, new_table TO your_table;

5. 分步添加字段

如果添加的字段不需要立即填充，可以分步添加字段，逐步填充數據：

添加字段。
通過批量更新逐步填充新字段的數據。

-- 1. 添加字段
ALTER TABLE your_table ADD COLUMN new_column datatype;

-- 2. 分批更新
SET @batch_size = 10000;  -- 每次更新的行數
SET @total_updated = 0;

DO
    SET @updated = (UPDATE your_table SET new_column = 'default_value' WHERE 條件 LIMIT @batch_size);
    SET @total_updated = @total_updated + @updated;
WHILE @updated > 0;
END DO;

-- 輸出總共更新的行數
SELECT @total_updated;