萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> 資料庫綜合

SQL Server 快速刪除海量資料實戰:TRUNCATE、分區切換、最小日誌三大策略

場景痛點

  • 磁碟空間緊張,不想將復原模式改為 SIMPLE (需保留完整日誌鏈)。
  • 需清除大量歷史資料 (億列級),DELETE 產生大量交易日誌、鎖升級、執行緒阻塞。
  • 有條件刪除 (如 WHERE Time < '2006-03-10') 無法直接用 TRUNCATE。

策略一:全表清空 → TRUNCATE TABLE (極速、最小日誌)

TRUNCATE TABLE BigTable;
  • DDL 操作,僅記錄頁面釋放,不記錄逐列刪除。
  • 重設 IDENTITY 種子。
  • 需權限:ALTER TABLE 權限。
  • 無法用於被外鍵參考的表 (需先刪除 FK)。

策略二:條件式保留資料 → 「選出保留 → 截斷 → 改名」三步曲

-- 1. 選出需保留的資料到新表 (最小日誌需 SIMPLE/BULK_LOGGED 模式)
SELECT * INTO TableName_New FROM TableName WHERE Time >= '2006-03-10';

-- 2. 截斷原表 (瞬間完成)
TRUNCATE TABLE TableName;

-- 3. 重命名新表為原表名
EXEC sp_rename 'TableName_New', 'TableName';

優勢: 避開大量 DELETE 日誌爆炸,總耗時 ≈ 複製保留資料時間 + 毫秒級截斷。

注意: SELECT INTO 在 FULL 復原模式下仍會完整記錄日誌,建議暫時切換至 BULK_LOGGED 模式執行,完成後切回 FULL 並立即備份日誌。

策略三:分區表切換 (Enterprise Edition 專屬、零停機、元資料級操作)

-- 前提:表已建立分區函數/方案,按時間欄位分區
-- 1. 建立空的暫存表,結構、索引、檢查約束與目標分區完全一致
CREATE TABLE StagingTable (...) ON PartitionScheme(PartitionColumn);

-- 2. 切換分區至暫存表 (瞬間,僅更新元資料)
ALTER TABLE BigTable SWITCH PARTITION 5 TO StagingTable;

-- 3. 刪除暫存表 (或歸檔)
DROP TABLE StagingTable;  -- 或 TRUNCATE 保留結構供歸檔
  • 完全不產生資料移動日誌,僅修改系統目錄指標。
  • 不鎖定線上查詢,真正零停機。
  • 需 Enterprise/Developer Edition,且表需預先分區。

策略四:批次刪除 (無分區、無法停機、FULL 復原模式)

DECLARE @BatchSize INT = 5000, @RowsAffected INT = 1;
WHILE @RowsAffected > 0
BEGIN
    DELETE TOP (@BatchSize) FROM BigTable
    WHERE Time < '2006-03-10';
    SET @RowsAffected = @@ROWCOUNT;
    -- 可選:CHECKPOINT (SIMPLE 模式) 或 BACKUP LOG (FULL 模式) 釋放日誌空間
    WAITFOR DELAY '00:00:00.1'; -- 降低鎖爭用
END;

配合 TOP (N) 分批、頻繁備份日誌/檢查點,控制單次交易大小與日誌增長。

方案選擇決策樹

  1. 需清空整表 → TRUNCATE
  2. 需保留部份資料、可接受短暫鎖表、有 Enterprise Edition → 分區切換
  3. 需保留部份資料、無分區、可切換復原模式 → 選出保留→截斷→改名
  4. 其餘情況 → 批次 DELETE + 日誌管理
copyright © 萬盛學電腦網 all rights reserved