SQL優化：處理百萬級資料表的結構修改

一張上百萬筆的資料表，直接下 ALTER TABLE 加個欄位，結果整張表被鎖住、線上查詢全部排隊等待、應用程式逾時。這是動大表時最常踩的雷。底下記錄一個我在宜蘭下水道專案實際用過的做法：不直接改原表，而是建一張新結構的表、分批把資料搬過去，再交換表名。

先講清楚一件事：這篇前半段的概念示意用 MySQL 語法，後半段的實戰程式碼是 SQL Server（T-SQL）。兩套資料庫在「建空表」和「換表名」這兩步的語法差很多，不能照抄互換，文中會逐一標明。

為什麼不直接 ALTER TABLE

當資料表的記錄數量上到百萬、千萬級別，直接執行 ALTER TABLE 很可能撐很久甚至逾時。原因是不少結構變更會走「重建整張表」的路徑：資料庫要複製整張表的內容、期間對表加上 schema 修改鎖，其他查詢和寫入只能等。表越大，鎖的時間越長，線上服務就越可能受影響。

不同資料庫、不同版本對 online DDL 的支援程度不一樣，有些操作（例如某些情況下加欄位）其實能做到不重建表。但一旦碰到需要重建的變更，又不想冒鎖表風險，「自己控制節奏分批搬」就是個可控的選擇。

漸進式做法的四個步驟

整體流程是這樣：

1. 建立一張具有新結構的空資料表
2. 分批把原始資料複製到新表
3. 交換新舊表的名稱
4. 清理舊表

下面先用 MySQL 語法示意概念，再給 SQL Server 的完整實戰版。

MySQL 概念示意

-- 步驟 1：建立與原表結構相同的新表，再加上新欄位
CREATE TABLE new_table LIKE original_table;
ALTER TABLE new_table ADD COLUMN new_column INT;

-- 步驟 2：分批複製資料（需放進迴圈，並逐批推進游標）
-- 注意：明確列出目的表欄位清單，避免因 * 展開順序與目的表不符而報錯
INSERT INTO new_table (col1, col2, ..., new_column)
SELECT col1, col2, ..., NULL
FROM original_table
WHERE id > @last_id
ORDER BY id
LIMIT 10000;

-- 步驟 3：原子交換表名（MySQL 的 RENAME TABLE 支援單一語句多表原子改名）
RENAME TABLE original_table TO old_table, new_table TO original_table;

-- 步驟 4：刪除舊表
DROP TABLE old_table;

這裡有兩個重點，照抄前一定要懂：

第一，步驟 2 那段 INSERT **本身沒有更新 @last_id**，所以它不能單獨跑。它必須包在迴圈裡，而且每跑一批就要把 @last_id 推進到「這批實際插入的最大來源 id」。否則迴圈會永遠用同一個 @last_id 反覆複製最小的那 10000 筆，變成無窮迴圈。怎麼正確推進游標，後面 SQL Server 實戰段會完整示範。

INSERT INTO new_table SELECT *, NULL 這種寫法另有一個風險：* 展開後欄位順序固定，加上匿名 NULL 作為新欄位時，目的表的欄位對應依賴位置而非名稱。嚴格模式或欄位數量不符時直接報錯。上面的示意已改成明確列出欄位名稱，實務中請照這個寫。

第二，步驟 3 的 RENAME TABLE original_table TO old_table, new_table TO original_table 是 MySQL 專屬。MySQL 的 RENAME TABLE 保證單一語句內多張表的改名是原子的，過程中沒有任何 session 能存取這些表，中途出錯則整句失敗、不留半套狀態。這正是它適合做表名交換的原因。

但 SQL Server 沒有這個語法。T-SQL 既沒有 CREATE TABLE ... LIKE，也沒有可以一句完成新舊表原子交換的 RENAME TABLE。在 SQL Server 你只能用 sp_rename 分兩次改名，這兩次之間存在一個非原子的空窗——原表已改名、新表還沒接上的瞬間。實戰程式碼會看到這點。

步驟拆解

建新表： 先建一張和原表結構一致的空表，再把要改的欄位補上去，確保新表既保留原有欄位、又含進這次的結構調整。MySQL 用 CREATE TABLE ... LIKE；SQL Server 因為沒有這語法，實務上直接寫完整 CREATE TABLE 把欄位、型別、主鍵、IDENTITY 全部列清楚（也比較好控制新表的細節）。

分批複製： 整個流程最關鍵的一步。用迴圈每次只搬一小段資料過去，靠一個游標變數記住上次搬到哪。LIMIT / TOP 控制每批筆數：批次小，迴圈次數多但每次負擔輕；批次大則相反。要找到適合自己環境的平衡點。

交換表名： 所有資料搬完後換名。MySQL 一句原子搞定；SQL Server 分兩次 sp_rename，要意識到中間那個空窗。

清理舊表： 確認新表沒問題後，再刪掉舊表釋放空間。建議先留著舊表幾天，確定線上一切正常再 DROP。

SQL Server 實戰：游標推進這步最容易出錯

下面是宜蘭下水道專案裡，搬 WaterLevelGaugeHistory（水位計歷史資料表）時實際用的 T-SQL。在貼程式碼之前，先講清楚三個我踩過、也是這類腳本最容易寫錯的地方。

坑一：游標推進必須跟著來源表的 ID，不能跟著目的表的 IDENTITY。

最直覺的寫法是用 OUTPUT inserted.ID 把插入結果撈出來推進游標。問題是：新表的 ID 欄位是 IDENTITY(1,1)，SQL Server 自動產生的是 新的流水號（1, 2, 3...），跟來源表原始的 ID 值域完全不同。拿新表的 IDENTITY 來決定「下一批從哪裡開始撈」，等於拿蘋果對橘子——WHERE ID > @LastProcessedID 比對的是舊表的 ID，而 @LastProcessedID 裡裝的卻是新表的流水號，兩個值域沒有關係。在來源表 ID 從大數字開始（或有跳號）的情況下，這會造成大量重複插入或整批漏搬。

正確做法：直接從來源表追蹤游標。每批 INSERT 完之後，用一個獨立的 SELECT 去問「這批的上界是多少」：

-- 先取這批實際要搬的來源 ID 上界（不依賴目的表 IDENTITY）
SELECT @LastProcessedID = MAX(ID)
FROM (
    SELECT TOP (@BatchSize) ID
    FROM WaterLevelGaugeHistory
    WHERE ID > @LastProcessedID
    ORDER BY ID
) AS BatchIDs;

這樣游標推進完全根植於來源表的 ID，不管 ID 有沒有缺口、從哪個數字開始，都精準。

坑二：游標推進的範圍要和實際插入的範圍一致。

另一種常見寫法是用 ID <= @LastProcessedID + @BatchSize 來劃定每批範圍。問題在於 TOP (@BatchSize) 取的是「筆數」，而 @LastProcessedID + @BatchSize 是「ID 數值」——只有 ID 完全連續無缺口時兩者才一致。百萬級的表幾乎不可能 ID 無缺口：刪過資料、IDENTITY 跳號、replication 都會留下洞。用上面那個「先 SELECT 出這批 ID 上界」的做法，這個問題就一起消失了。

坑三：錯誤復原不能用「游標 +1 繼續」。

另一個常見寫法是 TRY...CATCH 裡出錯就 ROLLBACK、然後把游標 +1 接著跑下一批。這是錯的。ROLLBACK 之後這批一筆都沒進去，游標卻只 +1，下一批會用幾乎一樣的範圍重新撈，導致大量重疊插入、新表冒出重複列。

正確做法是：出錯就讓迴圈停下、把錯誤資訊和當時的 @LastProcessedID 印出來，人工介入查清楚再重跑。重跑時把腳本最上方的 @LastProcessedID 初始值從 0 改成印出的那個值，從中斷點繼續即可。批次複製的正確性前提就是每一批都成功，不能容忍「跳過壞掉的批次繼續」。

修正過後的完整腳本：

-- 步驟 1: 若新表已存在就先刪掉
IF OBJECT_ID('WaterLevelGaugeHistory_New', 'U') IS NOT NULL
    DROP TABLE WaterLevelGaugeHistory_New;

-- 步驟 2: 建立新表，ID 設為 IDENTITY
-- 注意：若業務需要保留原始 ID（例如有外鍵參照），
-- 需改用 SET IDENTITY_INSERT ON 並在 INSERT 欄位列表加上 ID，
-- 同時拿掉 IDENTITY 定義或另建無 IDENTITY 的對應欄位。
CREATE TABLE WaterLevelGaugeHistory_New (
    ID int IDENTITY(1,1) NOT NULL,
    stt_no nvarchar(50) NULL,
    dev_id nvarchar(50) NULL,
    measure_time datetime NULL,
    upload_time nvarchar(50) NULL,
    val decimal(10, 3) NULL,
    Voltage decimal(10, 3) NULL,
    Power nvarchar(10) NULL,
    DataLogPeriod nvarchar(50) NULL,
    Status int NULL,
    flood_season int NULL,
    cstatus nvarchar(2) NULL,
    nodata bit NULL,
    StationClass int NULL,
    CONSTRAINT PK_WaterLevelGaugeHistory_New PRIMARY KEY CLUSTERED (ID)
);

-- 步驟 3: 變數宣告
DECLARE @BatchSize INT = 50000;
-- 若從中斷點接續重跑，把 0 改成上次印出的 Last processed source ID
DECLARE @LastProcessedID INT = 0;
DECLARE @RowsAffected INT;
DECLARE @TotalRowsProcessed INT = 0;
DECLARE @TotalRowsOld INT;
DECLARE @TotalRowsNew INT;
DECLARE @StartTime DATETIME = GETDATE();
DECLARE @BatchMaxSourceID INT;

-- 原表總行數，最後用來對帳
SELECT @TotalRowsOld = COUNT(*) FROM WaterLevelGaugeHistory;

WHILE @LastProcessedID IS NOT NULL
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;

        -- 先確定這批的來源 ID 上界（根植於來源表，不依賴目的表 IDENTITY）
        SELECT @BatchMaxSourceID = MAX(ID)
        FROM (
            SELECT TOP (@BatchSize) ID
            FROM WaterLevelGaugeHistory
            WHERE ID > @LastProcessedID
            ORDER BY ID
        ) AS BatchIDs;

        IF @BatchMaxSourceID IS NULL
        BEGIN
            -- 來源已無更多資料，結束迴圈
            COMMIT TRANSACTION;
            SET @LastProcessedID = NULL;
            BREAK;
        END

        -- 插入這批資料（欄位明確對應，不用 SELECT *）
        INSERT INTO WaterLevelGaugeHistory_New (
            stt_no, dev_id, measure_time, upload_time, val, Voltage, Power,
            DataLogPeriod, Status, flood_season, cstatus, nodata, StationClass
        )
        SELECT TOP (@BatchSize)
            stt_no, dev_id, measure_time, upload_time, val, Voltage, Power,
            DataLogPeriod, Status, 0 AS flood_season, cstatus, nodata, StationClass
        FROM WaterLevelGaugeHistory
        WHERE ID > @LastProcessedID
        ORDER BY ID;

        SET @RowsAffected = @@ROWCOUNT;
        SET @TotalRowsProcessed = @TotalRowsProcessed + @RowsAffected;

        -- 游標推進到「這批來源表的最大 ID」，與目的表 IDENTITY 無關
        SET @LastProcessedID = @BatchMaxSourceID;

        COMMIT TRANSACTION;

        PRINT 'Processed ' + CAST(@RowsAffected AS NVARCHAR(10))
            + ' rows. Last source ID: ' + CAST(@LastProcessedID AS NVARCHAR(20))
            + '. Total: ' + CAST(@TotalRowsProcessed AS NVARCHAR(20))
            + '. Elapsed: ' + CAST(DATEDIFF(SECOND, @StartTime, GETDATE()) AS NVARCHAR(10)) + 's';

        -- 小延遲，降低對線上的瞬間壓力
        WAITFOR DELAY '00:00:00.1';
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        PRINT 'Error occurred: ' + ERROR_MESSAGE();
        PRINT 'Error Line: ' + CAST(ERROR_LINE() AS NVARCHAR(10));
        PRINT 'Last processed source ID: ' + ISNULL(CAST(@LastProcessedID AS NVARCHAR(20)), 'NULL');
        PRINT '重跑方式：把腳本最上方 @LastProcessedID 的初始值改成上面印出的 ID，再重新執行。';

        -- 出錯就停下，不要「+1 硬往下跑」——那會造成重複插入
        THROW;
    END CATCH
END

-- 步驟 4: 對帳
SELECT @TotalRowsNew = COUNT(*) FROM WaterLevelGaugeHistory_New;

IF @TotalRowsOld = @TotalRowsNew
BEGIN
    -- 步驟 5: 交換表名
    -- 注意：T-SQL 沒有原子的多表交換，下面兩句 sp_rename 之間有一個短暫空窗，
    -- 期間 WaterLevelGaugeHistory 這個名字暫時不存在。建議在離峰、低流量時段執行。
    EXEC sp_rename 'WaterLevelGaugeHistory', 'WaterLevelGaugeHistory_Old';
    EXEC sp_rename 'WaterLevelGaugeHistory_New', 'WaterLevelGaugeHistory';

    -- 步驟 6: 確認線上正常後再刪舊表（先留著比較保險）
    -- DROP TABLE WaterLevelGaugeHistory_Old;

    PRINT '表格更新完成。總行數: ' + CAST(@TotalRowsNew AS NVARCHAR(20));
END
ELSE
BEGIN
    PRINT '對帳失敗，請檢查後重試。';
    PRINT '原表行數: ' + CAST(@TotalRowsOld AS NVARCHAR(20));
    PRINT '新表行數: ' + CAST(@TotalRowsNew AS NVARCHAR(20));
END

幾個跟原始寫法不同、值得特別說明的點：

游標推進改成「先 SELECT 出這批來源 ID 的上界，再 INSERT」的兩步走。這樣 @LastProcessedID 永遠是來源表的真實 ID，跟目的表的 IDENTITY 流水號完全脫鉤，不管 ID 有沒有缺口都精準。這是整段最關鍵的修正。

CATCH 區塊改成 THROW 直接拋出。出錯就停，把 Last processed source ID 印出來，讓你能查明原因後把那個值填入腳本頂端的 @LastProcessedID 初始值，接續重跑，而不是讓腳本帶著錯誤狀態一路跑完。

對帳的 COUNT(*) 比對在游標邏輯正確的前提下有實際意義。如果想更嚴謹，可以對幾個關鍵欄位做 CHECKSUM_AGG 比對；但光靠筆數、不修游標邏輯，驗證結果是不可信的。

這個做法的優缺點

優點很實在：分批搬避免一次性大操作壓垮系統；複製期間原表照常服務，不影響日常查詢寫入；過程可隨時中斷，原表資料不受影響；批次大小可依負載動態調整。

但也有要付出的代價：

• 要有足夠磁碟空間同時容納兩張表。
• 整個過程通常不快，建議排在系統負載低的時段跑。
• 複製期間原表若還在持續寫入，新表會漏掉這段時間新增的資料。 這是這個做法最大的限制。實務上要嘛在維護視窗停寫、要嘛搭配額外機制（觸發器、CDC、或搬完後再補一輪增量）把空窗期的寫入同步過去。這點對線上系統是生死攸關的細節，不能跳過。

小結

「建新表 → 分批複製 → 換表名 → 清舊表」這個方向是對的，能在不長時間鎖表的前提下改大表結構。魔鬼在細節：游標要跟著來源表的 ID 走，絕對不能拿目的表的 IDENTITY 流水號當游標——兩個值域不同，拿錯了要嘛漏搬要嘛重複；錯誤復原要停下查清楚，不能 +1 硬衝；換表名在 SQL Server 不是原子操作，要挑時段；複製期間的新寫入要另外補。把這四點處理好，這套手法在百萬級表上才真的安全可用。宜蘭那個專案搬完後驗了兩天，資料筆數和 CHECKSUM 都對上，才真正放心。