程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> 從SQL Server刪除大數據說開去

從SQL Server刪除大數據說開去

編輯：關於SqlServer

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

多數同學看到這兩個問題，想都不想就說，就一個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語句采用的是完整模式的日志處理，當刪除大量數據時，可能會花費大量的時間還有需要大量的日志存放空間。

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;