從SQL Server刪除大數(shù)據(jù)說開去

mybook564 2014-04-12

展開全文

對于數(shù)據(jù)庫中刪除數(shù)據(jù)，你說要注意什么呢？代碼怎樣寫？

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

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

本文的內(nèi)容：

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

2. 整理T－SQL的刪除數(shù)據(jù)的語句和寫法；

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

先舉個我實際工作中的事例，我們在這樣的一個應(yīng)用，每天定時收集一些數(shù)據(jù)寫入數(shù)據(jù)庫中，數(shù)據(jù)庫中有一批表（十幾個）存放這些數(shù)據(jù)，由于數(shù)據(jù)量很大，三個月的單表數(shù)據(jù)在1億以上，所以，我們只用表保存90天的數(shù)據(jù)，用于做什么我們在這里不關(guān)心好不好。在每天都有一個Job去刪除90天以前的數(shù)據(jù)。由于這是很早前，我的前輩所做的，現(xiàn)在人已不在這公司了。這些表所有的動作就以下的語句來刪除：

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

這只是一次只刪除一天的數(shù)據(jù)，也就是日期最早的，90天以前的。昨晚就收到了以下的服務(wù)器報警：

我看到平時服務(wù)器上各磁盤的空間使用情況如下，D盤是日志文件所在的盤，平時大概有95G的可用空間；

E盤是數(shù)據(jù)文件存放的盤，平時有170G的可用空間。由于數(shù)據(jù)每天的新增和刪除的量都基本平衡，所以也就沒有多大的變化。

現(xiàn)在看到數(shù)據(jù)庫文件已有121G這么大。多個億級別的表了。

平時的日志文件幾百M。

這報警是D盤小于40G，那就是說日志文件增長了45G以上，那時也正好是刪除數(shù)據(jù)的Job在執(zhí)行。在我檢查這些刪除動作的語句時，發(fā)現(xiàn)了問題，就是沒有考慮刪除大數(shù)據(jù)時日志增長與磁盤空間的關(guān)系。這樣下去，可能那一次就掛了。

再說說刪除數(shù)據(jù)的方式：

對于刪除數(shù)據(jù)，T-SQL提供了兩個從表中刪除數(shù)據(jù)行的語句：Delete和TRUNCATE.

DELETE 語句是標準的SQL語句，它用于根據(jù)指定的謂詞（條件）從表中刪除數(shù)據(jù)。這個標準的語句只有兩個子句：用于指定目標表名的FROM子句和用于指定謂調(diào)整條件的WHERE子句。只有能讓謂詞條件計算結(jié)果為TRUE的行才會被刪除。

例如：

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

這是刪除表S5_BinTest_Info中時間字段TS早于90天的數(shù)據(jù)。

DELETE語句采用的是完整模式的日志處理，當刪除大量數(shù)據(jù)時，可能會花費大量的時間還有需要大量的日志存放空間。

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

TRUNCATE TABLE dbo.S5_BinTest_Info;

和DELETE語句相比，TRUNCATE具有以最小模式記錄日志和優(yōu)點。和DELETE語句的完整模式在性能上有巨大的差異。TRUNCATE的速度最快。當表中有標識列時，DELETE不會改變標識列的值，TRUNCATE則會重置為最初的種子值。

再說說基于聯(lián)接的DELETE,T-SQL支持一種基于聯(lián)接的DELETE語法，這不是一種標準的SQL語法。聯(lián)接本身就有過慮的作用，因為它有一個基于謂詞的過濾器（ON子句）。通過聯(lián)接可以訪問另一個表中相關(guān)行的屬性（列），并在WHERE子句中引用這些屬性，這就意味著可以根據(jù)對另一個表中相關(guān)行的屬性定義的過慮器來刪除表中的數(shù)據(jù)行。例如：

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子句。

這也可以用查詢子句來實現(xiàn)同樣的處理：

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。

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

由于我為里是有ID的，所以一次刪除一個ID號的記錄，以下是更改后的循環(huán)方式實現(xiàn)源碼。

--2012-03-30,因刪除大數(shù)據(jù)問題，以下更改為分批刪除的方式實現(xiàn)     
  DECLARE @MINID INT;   
DECLARE @N INT;    
   --取出要刪除的90天前的記錄的ID    
 SELECT BinTestID into #S5ID     
           from dbo.S5_BinTest_Info      
            where TS <dateadd(dd,-90,getdate());     
   --以要刪除的ID數(shù)量為循環(huán)變量，因為ID號可能不連續(xù)    
SELECT @N=(SELECT COUNT(1) FROM #S5ID);   
    WHILE (@N>0)    
  BEGIN    
     -- 一次刪除一個ID對應(yīng)的數(shù)據(jù)    
     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數(shù)，這是循環(huán)變量    
   SELECT @N=(SELECT COUNT(1) FROM #S5ID);    
   END   
  DROP TABLE #S5ID;