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

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

是的，可以我再問一下，刪除動(dòng)作是會(huì)寫日志的，你放日志的磁盤夠空間嗎？

本文的內(nèi)容：

1. 我先舉個(gè)實(shí)際的工作問題；

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

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

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

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ù)器報(bào)警：

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

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

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

平時(shí)的日志文件幾百M(fèi)。

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

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

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

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

例如：

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

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

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

TRUNCATE語句不是標(biāo)準(zhǔn)的SQL語句，它用于刪除表中所有的行。與DELETE語句不同，TRUNCATE不需要加條件，如：

TRUNCATE TABLE dbo.S5_BinTest_Info; 

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

再說說基于聯(lián)接的DELETE,T-SQL支持一種基于聯(lián)接的DELETE語法，這不是一種標(biāo)準(zhǔn)的SQL語法。聯(lián)接本身就有過慮的作用，因?yàn)樗幸粋€(gè)基于謂詞的過濾器（ON子句）。通過聯(lián)接可以訪問另一個(gè)表中相關(guān)行的屬性（列），并在WHERE子句中引用這些屬性，這就意味著可以根據(jù)對(duì)另一個(gè)表中相關(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語句在邏輯上第一個(gè)處理的子句是FROM子句（第二行FROM dbo.S5_BinTest_Info AS S5的這個(gè)），接著處理WHERE子句，最后才是DELETE子句。

這也可以用查詢子句來實(shí)現(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); 

這里的查詢子句的方式是標(biāo)準(zhǔn)的SQL語句，我更喜歡使用標(biāo)準(zhǔn)SQL。

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

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

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

如果對(duì)于沒有ID的數(shù)據(jù)表，我們可以用TOP的方式來刪除。

我使用這樣的方式執(zhí)行時(shí)，日志基本沒有增長，因?yàn)閯h除一次很少的數(shù)據(jù)，成功后會(huì)釋放，再使用。

您，刪除數(shù)據(jù)時(shí)考慮語法和條件還有大量數(shù)據(jù)的日志增長空間問題了嗎？