快捷導(dǎo)航

SQL百萬(wàn)千萬(wàn)級(jí)最大表添加字段的實(shí)現(xiàn)

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

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

1. 直接添加字段

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

ALTER TABLE your_table ADD COLUMN new_column datatype;

2. 使用 pt-online-schema-change

pt-online-schema-change 是 Percona Toolkit 中的一個(gè)工具，可以在線修改表的結(jié)構(gòu)，而不會(huì)長(zhǎng)時(shí)間鎖定表。

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

3. 分區(qū)表

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

4. 復(fù)制表

可以通過復(fù)制表的方法來(lái)實(shí)現(xiàn)：

創(chuàng)建新表，并在創(chuàng)建時(shí)添加所需的新列。
將舊表的數(shù)據(jù)復(fù)制到新表中。
在適當(dāng)?shù)臅r(shí)候，將舊表重命名為備份表，并將新表重命名為原始表。

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

-- 2. 復(fù)制數(shù)據(jù)
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. 分步添加字段

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

添加字段。
通過批量更新逐步填充新字段的數(shù)據(jù)。

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

-- 2. 分批更新
SET @batch_size = 10000;  -- 每次更新的行數(shù)
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;

-- 輸出總共更新的行數(shù)
SELECT @total_updated;