管理和刪除大表中的數據

我有以下情況：

一張巨大的桌子，也是目前系統中使用最多的桌子。巨大的意思是它的行數很大，實際磁碟大小也很大，因為它有 2 個字節的數組列可以保存我們系統中的文件。

問題是當我需要從中刪除大量行時。首先，我只嘗試了一個 delete within語句，但是當我決定重新啟動數據庫時，它凍結了整個表（用它來利用系統）大約 45 分鐘。

然後我嘗試刪除 10 個系統資料庫塊，並嘗試刪除 8k+ 行。它真的很慢，從那個表中獲取數據的地方也很慢，當它刪除大約 700 行時，我啟動了另一個使用同一張表的程序，並出現了死鎖。

我應該如何從這張表中進行這些批量刪除？我考慮過將表分成 2 份，一份用於包含大部分錶卷的 2 字節數組，另一份用於其餘資訊（例如 varchars、整數、日期時間等簡單的東西）。在這些刪除案例中這樣做對我有好處嗎？或者行數仍然會導致表上的這些鎖？

SQL 的版本是 Microsoft SQL Server Standard（64 位）12.0.5000.0。

先來幾篇文章：

• 將大型刪除操作分成塊
• 清除數據時延遲的持久性

您沒有提到 SQL Server 的版本，所以我不確定第二個是否與您相關，但可能與其他讀者有關。我將專注於前者。

通常，刪除大量數據會導致一系列症狀：

1. 受影響的行越多 = 鎖升級的可能性越大
2. 更長的事務 = 冗長的阻塞（有時是死鎖，正如您所發現的）
3. 對事務日誌的大量寫入 = 寫入延遲（包括增長事件）

所以我在那篇文章中的想法是通過刪除“塊”來鎖定更少的行——每個刪除操作（或有限數量的刪除操作）都將在一個事務中，從而減少鎖定的行數、事務的長度以及對日誌的影響。

所以我可能會建議嘗試這樣的事情：

SELECT key_column
 INTO #work
 FROM dbo.big_table
 WHERE -- however you identify rows to delete;

CREATE CLUSTERED INDEX x ON #work(key_column);

DECLARE @rc  int = 1, 
 @counter   int = 1,
 @batchsize int = 100; -- this may or may not be an optimal chunk size

WHILE @rc > 0
BEGIN
 BEGIN TRANSACTION;

 DELETE TOP (@batchsize) t
   FROM dbo.big_table AS t
   INNER JOIN #work AS x
   ON t.key_column = x.key_column;

 SET @rc = @@ROWCOUNT;

 COMMIT TRANSACTION;

 SET @counter = @counter + 1;

 IF @counter % 10 -- or maybe 100 or 1000
 BEGIN
   CHECKPOINT; CHECKPOINT;
 END
END

檢查點確保可以重新使用事務中的日誌空間（第二個確保日誌正確“環繞”）。在您的完整恢復環境中，您只需要一個BACKUP LOG命令……或者您可以考慮切換到簡單，如果您可以將此維護工作與您可以重新啟動日誌鏈的定期安排的完整備份非常接近。

訣竅是找到平衡日誌重用和檢查點或日誌備份必須做的工作的最佳點。（在我的文章中，檢查點的頻率實際上導致整體操作變慢，這可能沒關係，如果它真的像後台程序一樣需要多長時間並不重要 - 問題不在於持續時間，而在於乾擾. 我只是沒有在最佳位置釣魚。）

批處理大小有效地限制了任何給定事務中鎖定的行數，並減少了該事務所需的工作量。如果你有足夠現代的版本，你可以在這裡使用延遲耐久性；最大的風險是您失去了一筆交易，但是由於您“失去”的是數據刪除，因此您並沒有真正失去任何東西。

如果您要刪除表中超過幾個 % 的行，則通常更快：

1. CREATE TABLE table_temp as select * from table where ID IN（要保留的記錄）
2. 將目前表更改為 table_old
3. 將 table_temp 更改為表
4. 刪除 table_old

引用自：https://dba.stackexchange.com/questions/173317