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

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

在資料庫管理中，當我們面對龐大的資料表時，進行結構修改常常會遇到效能問題。本文將探討如何處理一個資料量達到千萬級別的資料表，並在不造成系統超時的情況下完成結構修改。

問題背景

當資料表的記錄數量達到千萬級別時，直接執行 ALTER TABLE 指令可能會導致操作超時，影響系統的正常運作。這是因為 MySQL 在執行 ALTER TABLE 時，會鎖定整個資料表，導致其他查詢無法進行，同時也會消耗大量系統資源。

解決方案

為了解決這個問題，我們可以採用一種漸進式的方法來修改資料表結構。這個方法主要包含以下步驟：

1. 建立一個具有新結構的空資料表
2. 複製原始資料到新資料表
3. 重命名資料表
4. 清理舊資料表

讓我們來看看具體的 SQL 實現：

-- 步驟 1：建立新的資料表結構
CREATE TABLE new_table LIKE original_table;
ALTER TABLE new_table ADD COLUMN new_column INT;

-- 步驟 2：分批複製資料
INSERT INTO new_table 
SELECT *, NULL 
FROM original_table 
WHERE id > @last_id 
ORDER BY id 
LIMIT 10000;

-- 步驟 3：重命名資料表
RENAME TABLE original_table TO old_table, new_table TO original_table;

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

詳細解說

步驟 1：建立新的資料表結構

首先，我們建立一個與原始資料表結構相同的新資料表，然後加入我們想要的新欄位。這樣可以確保新資料表的結構與原始資料表一致，同時包含了我們需要的修改。

步驟 2：分批複製資料

這是整個過程中最關鍵的步驟。我們使用一個迴圈（這裡沒有顯示迴圈結構），每次複製一小部分資料到新的資料表中。@last_id 是一個變數，用來記錄上一次複製的最後一筆資料的 ID。每次迴圈都會更新這個變數，確保下一次複製時能夠接著上次的進度繼續。

LIMIT 10000 限制了每次複製的記錄數量，這個數字可以根據系統的效能和需求進行調整。較小的數值會增加迴圈次數但減少每次操作的負擔，較大的數值則相反。

步驟 3：重命名資料表

當所有資料都複製完成後，我們使用 RENAME TABLE 指令來交換新舊資料表的名稱。這個操作幾乎是瞬時完成的，不會對系統造成太大負擔。

步驟 4：刪除舊的資料表

最後，我們可以安全地刪除舊的資料表，釋放儲存空間。

優點

1. 減少系統負擔：通過分批處理，避免了一次性操作大量數據造成的系統負擔。
2. 不影響正常業務：在複製過程中，原始資料表仍然可以正常使用，不會影響日常操作。
3. 安全性高：如果在過程中出現問題，可以隨時中斷操作，不會影響原有數據。
4. 彈性大：可以根據系統負載調整每次處理的數據量，找到最佳平衡點。

注意事項

1. 在執行這個過程時，需要確保有足夠的磁碟空間來存儲額外的資料表。
2. 整個過程可能需要較長時間，建議在系統負載較低的時候進行。
3. 在複製過程中，如果原始資料表有新的寫入操作，可能需要額外的機制來同步這些新數據。

結論

通過這種方法，我們成功地在不影響系統正常運作的情況下，完成了對大型資料表的結構修改。這種技巧不僅適用於新增欄位，也可以應用在其他類型的結構修改上。在處理大型資料庫時，採用這種漸進式的方法可以大大提高操作的安全性和效率。

SQL實戰實現

-- 步驟 1: 刪除現有的新表格（如果存在）
IF OBJECT_ID('WaterLevelGaugeHistory_New', 'U') IS NOT NULL
    DROP TABLE WaterLevelGaugeHistory_New;

-- 步驟 2: 重新創建新表格，記得將 ID 設置為 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: 創建一個用於跟蹤進度的臨時表格
CREATE TABLE #ProcessedRows (
    BatchID INT IDENTITY(1,1) PRIMARY KEY,
    LastProcessedID INT,
    RowsProcessed INT,
    ProcessedTime DATETIME DEFAULT GETDATE()
);

-- 步驟 4: 分批複製數據
DECLARE @BatchSize INT = 50000; -- 減小批次大小
DECLARE @LastProcessedID INT = 0;
DECLARE @RowsAffected INT;
DECLARE @TotalRowsProcessed INT = 0;
DECLARE @TotalRowsOld INT;
DECLARE @TotalRowsNew INT;
DECLARE @StartTime DATETIME = GETDATE();

-- 獲取原表的總行數
SELECT @TotalRowsOld = COUNT(*) FROM WaterLevelGaugeHistory;

WHILE @LastProcessedID IS NOT NULL
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;
        
        -- 插入一批數據到新表格
        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
        IF @RowsAffected > 0
        BEGIN
            SELECT @LastProcessedID = MAX(ID)
            FROM WaterLevelGaugeHistory
            WHERE ID > @LastProcessedID AND ID <= @LastProcessedID + @BatchSize;
            
            -- 記錄進度
            INSERT INTO #ProcessedRows (LastProcessedID, RowsProcessed)
            VALUES (@LastProcessedID, @RowsAffected);
        END
        ELSE
        BEGIN
            SET @LastProcessedID = NULL; -- 結束循環
        END

        COMMIT TRANSACTION;

        -- 輸出進度
        PRINT 'Processed ' + CAST(@RowsAffected AS NVARCHAR(10)) + ' rows. Last ID: ' + ISNULL(CAST(@LastProcessedID AS NVARCHAR(20)), 'NULL') + 
              '. Total Processed: ' + CAST(@TotalRowsProcessed AS NVARCHAR(20)) + 
              '. Elapsed Time: ' + CAST(DATEDIFF(SECOND, @StartTime, GETDATE()) AS NVARCHAR(10)) + ' seconds';

         -- 可選：新增一個小的延遲以減少資源使用
        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 ID: ' + ISNULL(CAST(@LastProcessedID AS NVARCHAR(20)), 'NULL');

        -- 繼續下一個批次，而不是退出循環
        SET @LastProcessedID = ISNULL(@LastProcessedID + 1, 0);
    END CATCH
END

-- 步驟 5: 驗證數據驗證數據
SELECT @TotalRowsNew = COUNT(*) FROM WaterLevelGaugeHistory_New;

IF @TotalRowsOld = @TotalRowsNew
BEGIN
    -- 步驟 6: 重新命名表格
    EXEC sp_rename 'WaterLevelGaugeHistory', 'WaterLevelGaugeHistory_Old';
    EXEC sp_rename 'WaterLevelGaugeHistory_New', 'WaterLevelGaugeHistory';
    
    -- 步驟 7: 刪除舊表格（可選，取決於您的需求）
    -- DROP TABLE WaterLevelGaugeHistory_Old
    
    PRINT '表格更新成功完成。';
    PRINT '總行數: ' + CAST(@TotalRowsNew AS NVARCHAR(20));
END
ELSE
BEGIN
    PRINT '數據驗證失敗，請檢查並重試。';
    PRINT '原表行數: ' + CAST(@TotalRowsOld AS NVARCHAR(20));
    PRINT '新表行數: ' + CAST(@TotalRowsNew AS NVARCHAR(20));
    PRINT '處理的行數: ' + CAST(@TotalRowsProcessed AS NVARCHAR(20));
    
    -- 輸出進度詳情，幫助診斷問題
    SELECT TOP 100 * FROM #ProcessedRows ORDER BY BatchID DESC;
END

-- 清理
DROP TABLE #ProcessedRows;