在SQL Server中，如何快速洗掉大表中的資料呢？ 回答這個問題前，我們必須弄清楚背景關系環境和以及結合實際、具體的需求，不同場景有不同的應對方法，
1： 整張表的資料全部洗掉
如果是整張表的資料全部清空、洗掉，這種場景倒是非常簡單，TRUNCATE TABLE肯定是最快的， 反而用DELETE處理的話，就是一個糟糕的策略，
2： 大表中洗掉一部分資料
對于場景1、非常簡單，但是很多實際業務場景，并不能使用TRUNCATE這種方法，實際情況可能只是洗掉表中的一部分資料或者進行資料歸檔后的洗掉，假設我們遇到的表為TEST，需要洗掉TEST表中的部分資料，那么首先我們需要對表的資料量和被洗掉的資料量做一個匯總統計，具體，我們應該采用下面方法：
· 檢查表的資料量，以及要洗掉的資料量，然后計算洗掉的比例，
sp_spaceused 'dbo.TEST';
SELECT COUNT(*) AS DELETE_RCD WHERE TEST WHERE ......<洗掉條件>
2.1 洗掉大表中絕大部分的資料，但是這個絕大部分怎么定義不好量化，所以我們這里就量化為60%，如果洗掉的資料比例超過60%，就采用下面方法：
1： 新建表TEST_TMP
2: 將要保留的資料轉移到TEST_TMP
3： 將原表TEST重命名為TEST_OLD， 而將TEST_TMP重命名為TEST
4： 檢查相關的觸發器、約束，進行觸發器或約束的重命名
5： 核對操作是否正確后，原表（TEST_OLD）要么TRUANCATE后，再DROP掉，要么保留一段時間，保險起見，
注：至于這個比例60%是怎么來的，這個完全是個經驗值，有簡單的測驗，但是沒有很精確和科學的概率統計驗證，
另外，還要考慮業務情況，如果一直有應用程式訪問這個表，其實這種方式也是比較麻煩的，因為涉及資料的一致性，業務中斷等等很多情況，但是，如果程式較少訪問，或者在某個時間段沒有訪問，那么完全可以采用這種方法，
2.2 洗掉大表中部分資料，如果比例不超過60%
1：先洗掉或禁用無關索引（無關索引，這里指執行計劃不用到的索引，這里是指對當前DELETE陳述句無用的索引），因為DELETE操作屬于DML操作，而且大表的索引一般也非常大，大量DELETE將會對索引進行維護操作，產生大量額外的IO操作，
2：用小批量,分批次洗掉（批量洗掉比一次性洗掉性能要快很多），不要一次性洗掉大量資料，一次性洗掉大量記錄，會導致鎖的粒度范圍很大，并且鎖定的時間非常長，而且還可能產生阻塞，嚴重影響業務等等，而且資料庫的事務日志變得非常大，執行的時間變得超長，性能非常糟糕，
批量洗掉時，到底一次性洗掉多少數量的記錄數，SQL效率最高呢？ 這個真沒有什么規則計算，個人測驗對比過，一次洗掉10000或100000，沒有發現什么特別規律，（有些你發現的“規律”，換個案例，發現不一樣的結果，這個跟環境有關，有時候可能是一個經驗值），不過一般用10000，在實際操作程序，個人建議可以通過做幾次實驗對比后，選擇一個合適的值即可，
案例1：
DECLARE @delete_rows INT;
DECLARE @delete_sum_rows INT =0;
DECLARE @row_count INT=100000
WHILE 1 = 1 BEGINDELETE TOP ( @row_count )
FROM dbo.[EmployeeDayData]WHERE WorkDate < CONVERT(DATETIME, '2012-01-01 00:00:00',120);
SELECT @delete_rows = @@ROWCOUNT;
SET @delete_sum_rows +=@delete_rows IF @delete_rows = 0 BREAK; END;SELECT @delete_sum_rows;
案例2：
DECLARE @r INT;
DECLARE @Delete_ROWS BIGINT;SET @r = 1;SET @Delete_ROWS =0WHILE @r > 0BEGINBEGIN TRANSACTION;
DELETE TOP (10000) -- this will change
YourSQLDba..YdYarnMatch
WHERE Remark='今日未入' and Operation_Date<CONVERT(datetime, '2019-05-30',120);
SET @r = @@ROWCOUNT;
SET @Delete_ROWS += @r;COMMIT TRANSACTION;
PRINT(@Delete_ROWS);END
該表有下面兩個索引
USE [YourSQLDba]GOIF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[YdYarnMatch]') AND name = N'IX_YdYarnMatch_N2')
DROP INDEX [IX_YdYarnMatch_N2] ON [dbo].[YdYarnMatch] WITH ( ONLINE = OFF )
GOUSE [YourSQLDba]GOCREATE NONCLUSTERED INDEX [IX_YdYarnMatch_N2] ON [dbo].[YdYarnMatch]
(
[Job_No] ASC, [GK_No] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GOUSE [YourSQLDba]GOIF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[YdYarnMatch]') AND name = N'IX_YdYarnMatch_N1')
DROP INDEX [IX_YdYarnMatch_N1] ON [dbo].[YdYarnMatch] WITH ( ONLINE = OFF )
GOUSE [YourSQLDba]GOCREATE NONCLUSTERED INDEX [IX_YdYarnMatch_N1] ON [dbo].[YdYarnMatch]
(
[Operation_Date] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
重點：實踐證明，如果新建一個索引，能夠避免批量洗掉程序中執行計劃走全表掃描，也能大大加快洗掉的速度，個人對這個案例進行了測驗、驗證，發現加上合適索引后，讓DELETE陳述句走Index Seek后，洗掉效率確實大大提升，
洗掉索引IX_YdYarnMatch_N2，保留索引IX_YdYarnMatch_N1，但是發現SQL執行計劃走全表掃描，執行SQL時，洗掉非常慢
洗掉索引IX_YdYarnMatch_N1，重新創建索引IX_YdYarnMatch_N1后，執行計劃走Index Seek，洗掉效率大大提示，
CREATE NONCLUSTERED INDEX [IX_YdYarnMatch_N1] ON [dbo].[YdYarnMatch]
(
[Operation_Date] ASC ,
Remark
)
注意：此處索引名相同，但是索引對應的欄位不一樣，
所以正確的做法是：
1：先洗掉或禁用無關索引（對當前DELETE陳述句無用的索引），洗掉前生成對應的SQL，以便完成資料洗掉后，重新創建索引，注意，前提是在操作階段，這個操作不會影回應用，否則應重新考慮，
2：檢查測驗當前SQL的執行計劃，能否創建合適的索引，加快DELETE操作，如上面例子所示
3：批量回圈洗掉記錄，
4：在ORACLE資料庫中，有些表的設定可以減少對應DML操作的日志生成量，但是SQL Server沒有這些功能，但是要及時關注或調整事務日志的備份情況，
如果我們能將將資料庫的恢復模式設定為SIMPLE，那么可以減少日志備份引起的額外的IO開銷，但是很多生產環境不能切換用戶資料庫的恢復模式，
其實說了這么多，SQL Server中大表快速洗掉索引的方法就是將一次性洗掉改成分批洗掉，逐次提交而已，其它的方式都是一些輔助方式而已，另外，如果你想親自做一些細節測驗，建議參考博客https://sqlperformance.com/2013/03/io-subsystem/chunk-deletes
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/7403.html
標籤：SQL Server