從SQL Server刪除大資料說開去

對於資料庫中刪除資料，你說要注意什麼呢？代碼怎樣寫？

多數同學看到這兩個問題，想都不想就說，就一個Delete語句，注意刪除的條件不要刪除錯了就是，有什麼好注意的！

是的，可以我再問一下，刪除動作是會寫日誌的，你放日誌的磁碟夠空間嗎？

本文的內容：

1. 我先舉個實際的工作問題；

2. 整理T－SQL的刪除資料的語句和寫法；

3. 解決這個工作中的問題和效果總結。

先舉個我實際工作中的案例，我們在這樣的一個應用，每天定時收集一些資料寫入資料庫中，資料庫中有一批表（十幾個）存放這些資料，由於資料量很大，三個月的單表資料在1億以上，所以，我們只用表儲存90天的資料，用於做什麼我們在這裡不關心好不好。在每天都有一個Job去刪除90天以前的資料。由於這是很早前，我的前輩所做的，現在人已不在這公司了。這些表所有的動作就以下的語句來刪除： 

 

  
DELETE FROM dbo.S5_BinTest_Detail      
  
         where BinTestID in (SELECT BinTestID      
  
           from dbo.S5_BinTest_Info      
  
            where TS <dateadd(dd,-90,getdate())     
  
       )     
  
        and datediff(dd,(select min(TS) from S5_BinTest_Info),TS)<=0    
  
         DELETE from dbo.S5_BinTest_Info where TS <dateadd(dd,-90,getdate())   
  
        and datediff(dd,(select min(TS) from S5_BinTest_Info),TS)<=0 
 

這隻是一次只刪除一天的資料，也就是日期最早的，90天以前的。昨晚就收到了以下的伺服器警示：

我看到平時伺服器上各磁碟的空間使用方式如下，D盤是記錄檔所在的盤，平時大概有95G的可用空間；

E盤是資料檔案存放的盤，平時有170G的可用空間。由於資料每天的新增和刪除的量都基本平衡，所以也就沒有多大的變化。

現在看到資料庫檔案已有121G這麼大。多個億層級的表了。

平時的記錄檔幾百M。

這警示是D盤小於40G，那就是說記錄檔增長了45G以上，那時也正好是刪除資料的Job在執行。在我檢查這些刪除動作的語句時，發現了問題，就是沒有考慮刪除大資料時日誌增長與磁碟空間的關係。這樣下去，可能那一次就掛了。

再說說刪除資料的方式：

對於刪除資料，T-SQL提供了兩個從表中刪除資料行的語句：Delete和TRUNCATE.

DELETE 語句是標準的SQL語句，它用於根據指定的謂詞（條件）從表中刪除資料。這個標準的語句只有兩個子句：用於指定目標表名的FROM子句和用於指定謂調整條件的WHERE子句。只有能讓謂詞條件計算結果為TRUE的行才會被刪除。

例如：

 

  
DELETE from dbo.S5_BinTest_Info where TS <dateadd(dd,-90,getdate())； 
 

這是刪除表S5_BinTest_Info中時間欄位TS早於90天的資料。

DELETE語句採用的是完整模式的Tlog，當刪除大量資料時，可能會花費大量的時間還有需要大量的日誌存放空間。

TRUNCATE語句不是標準的SQL語句，它用於刪除表中所有的行。與DELETE語句不同，TRUNCATE不需要加條件，如：

 

  
TRUNCATE TABLE dbo.S5_BinTest_Info; 
 

和DELETE語句相比，TRUNCATE具有以最小模式記錄日誌和優點。和DELETE語句的完整模式在效能上有巨大的差異。TRUNCATE的速度最快。當表中有識別欄位時，DELETE不會改變識別欄位的值，TRUNCATE則會重設為最初的種子值。

再說說基於聯結的DELETE,T-SQL支援一種基於聯結的DELETE文法，這不是一種標準的SQL文法。聯結本身就有過慮的作用，因為它有一個基於謂詞的過濾器（ON子句）。通過聯結可以訪問另一個表中相關行的屬性（列），並在WHERE子句中引用這些屬性，這就意味著可以根據對另一個表中相關行的屬性定義的過慮器來刪除表中的資料行。例如：

 

  
DELETE FROM S5  
  
FROM dbo.S5_BinTest_Info AS S5  
  
JOIN dbo.S5_BinTest_Info_Dtl AS DTL  
  
ON S5.ID=DTL.ID  
  
WHERE DTL.QTY=1; 
 

這和SELECT語句非常相似，DELETE語句在邏輯上第一個處理的子句是FROM子句（第二行FROM dbo.S5_BinTest_Info AS S5的這個），接著處理WHERE子句，最後才是DELETE子句。

這也可以用查詢子句來實現同樣的處理：

 

  
DELETE FROM dbo.S5_BinTest_Info  
  
WHERE EXISTS(SELECT 1 FROM dbo.S5_BinTest_Info_Dtl AS DTL  
  
WHERE S5_BinTest_Info .ID=DTL.ID AND DTL.QTY=1); 
 

這裡的查詢子句的方式是標準的SQL語句，我更喜歡使用標準SQL。

再回到我這個工作中的問題，我想用分批刪除的方式來處理。一次刪除合理資料的記錄，多刪除幾次就可以了。

由於我為裡是有ID的，所以一次刪除一個ID號的記錄，以下是更改後的迴圈方式實現源碼。

 

  
--2012-03-30,因刪除大資料問題，以下更改為分批刪除的方式實現     
  
  DECLARE @MINID INT;   
  
DECLARE @N INT;    
  
   --取出要刪除的90天前的記錄的ID    
  
 SELECT BinTestID into #S5ID     
  
           from dbo.S5_BinTest_Info      
  
            where TS <dateadd(dd,-90,getdate());     
  
   --以要刪除的ID數量為迴圈變數，因為ID號可能不連續    
  
SELECT @N=(SELECT COUNT(1) FROM #S5ID);   
  
    WHILE (@N>0)    
  
  BEGIN    
  
     -- 一次刪除一個ID對應的資料    
  
     SELECT @MINID=MIN(BinTestID) FROM #S5ID;    
  
  DELETE FROM dbo.S5_BinTest_Detail   
  
            where BinTestID=@MINID;    
  
     DELETE from dbo.S5_BinTest_Info   
  
          where BinTestID=@MINID;   
  
     --從暫存資料表中去除已刪除的ID號    
  
     DELETE #S5ID WHERE BinTestID=@MINID;  
  
    --更改剩餘要刪除的ID數，這是迴圈變數    
  
   SELECT @N=(SELECT COUNT(1) FROM #S5ID);    
  
   END   
  
  DROP TABLE #S5ID; 
 

如果對於沒有ID的資料表，我們可以用TOP的方式來刪除。

我使用這樣的方式執行時，日誌基本沒有增長，因為刪除一次很少的資料，成功後會釋放，再使用。

您，刪除資料時考慮文法和條件還有大量資料的日誌增長空間問題了嗎？