場景介紹：

我們有一張表，專門用來生成自增ID供業務使用，表結構如下：

CREATE TABLE TB001(    ID INT IDENTITY(1,1) PRIMARY KEY,    DT DATETIME)

每次業務想要獲取一個新ID，就執行以下SQL:

INSERT INTO TB001(DT)SELECT GETDATE();SELECT @@IDENTITY

由于這些數據只需保留最近一天的數據，因此建立一個SQL作業來定期刪除數據，刪除腳本很簡單：

DELETE TOP(10000) FROM TB001WHERE DT<GETDATE()-1

作業每10秒運行一次,每天運行2個小時，最大能刪除數據720W數據。

問題：

由于前臺頁面沒有防刷機制，有惡意用戶使用程序攻擊，造成每天數據量暴增近1億(是不是我也可以出去吹下NB!!!),當前作業無法刪除這么龐大的數據，得進行調整.

解決思路：

在保證程序不修改的前提下，我們首先想到的辦法是：

1：提高單次刪除的數量，會造成鎖阻塞，阻塞嚴重就會影響到業務，這無法接受；

2：延長整個作業運行周期，研發人員擔心影響白天正常業務，要求作業只能夜里低峰區進行

3：提高刪除頻率，可以考慮，但具體頻率需要測試

由于方法2只能少量的增加，因此我們集中在方法3的測試上，由于SQL Agent Job的最小周期是10秒，因此在作業調用的腳本上修改，每次作業調用多條刪除語句，刪除語句中間使用WAITFOR來間歇執行:

DELETE FROM TB001WHERE DT<GETDATE()-1WAITFOR DELAY '0:0:05'DELETE FROM TB001WHERE DT<GETDATE()-1

測試運行時，發現對業務影響不大，因此就上線修改。

結果半夜作業運行后，研發立即收到報警，程序訪問延時嚴重，到服務器上一查，鎖等待超過500000多毫秒，sys.dm_exec_requests中顯示有300多回話等待同一個鎖資源，停掉作業后程序立馬回復正常。

讓我們來測試下這是為啥呢？

首先準備測試數據

CREATE TABLE TB001(    ID INT IDENTITY(1,1) PRIMARY KEY,    DT DATETIME)GOINSERT INTO TB001(DT)SELECT GETDATE()-1 FROM SYS.all_columnsGOINSERT INTO TB001SELECT GETDATE()-1 FROM TB001GO 13

然后嘗試刪除數據

BEGIN TRANDELETE TOP(10000)  FROM TB001WHERE DT<GETDATE()-1

查看鎖情況：

--上面事務的回話ID為55sp_lock 55

單次刪除數據太大，造成表鎖，阻塞程序插入數據，解決辦法：調整單次刪除數量

PS: SQL SERVER會在行集上獲得5000個鎖時嘗試鎖升級，同時也會在內存壓力下嘗試鎖升級。

于是我們只能嘗試更高的刪除頻率和更小的刪除批量，于是將刪除代碼修改如下：

DECLARE @ID INTSET @ID=0WHILE(@ID<100)BEGINDELETE TOP(100)  FROM TB001WHERE DT<GETDATE()-1WAITFOR DELAY '0:0:00:400'SET @ID=@ID+1END

PS: 刪除100行只是一個嘗試值，應該沒有一個最優的刪除行數，牛逼的解釋是設置該值需考慮：刪除需要掃描多少頁面/執行多次時間/表上索引數量/寫入多少日志/鎖與阻塞等等，不裝逼的解釋就是多測試直到達到滿足需求的值就好。假設平均刪除90行數據會寫60k的日志，你刪除100行導致需要兩次物理寫，這是何必呢？

使用修改后的版本測試了下，速度飛快，人生如此美好，哪還等啥，更新到生產服務器上，讓暴風雨來得更猛烈些吧?。?！

果然，這不是人生的終點，悲劇出現了，執行不穩定，本來40秒能執行完的SQL，有時候需要4分鐘才能完成，這不科學啊，我都測試好幾遍的呢?。?！

細細看看語句，不怪別人，自己寫的SQL垃圾，沒辦法，在看一遍代碼：

DELETE TOP(100)  FROM TB001WHERE DT<GETDATE()-1

這是按照業務邏輯寫的，沒有問題，但是的但是，DT上沒有索引，由于表中DT和ID都是順序增長的，按照主鍵ID的升序掃描，排在最前面的ID最小，其插入時間也最早，也是我們刪除的目標，因此只需要幾次邏輯讀便可以輕松找到滿足條件的100行數據，因此消耗也最小，但是理想很豐滿，現實很骨感，

在頻繁地運行DELETE語句后，使用SET STATISTICS IO ON來查看，同樣的執行計劃:

但是造成的邏輯IO完全不一樣，從4次到幾千次，此現象在高頻率刪除下尤其明顯（測試時可以連續運行10000次刪除查看）

嘗試其他寫法,強制走ID索引掃描：

DECLARE @ID INTSET @ID=0WHILE(@ID<10000)BEGIN;WITH T1 AS(SELECT TOP(100)* FROM TB001WHERE DT<GETDATE()-1ORDER BY ID)DELETE FROM T1SET @ID=@ID+1END

測試發現依然是同樣問題，難道無解么？

再次研究業務發現，我們可以查出一個要要刪除的最大ID，然后刪除小于這個ID的數據，而且可以避免一個潛在風險，由于DT沒有索引，當一天前的數據被清除后，如果作業繼續運行，要查找滿足條件的100行數據來進行刪除，便會對表進行一次全表掃描，消耗更龐大數量的邏輯IO。

DECLARE @MaxID INTSELECT @MaxID=MAX(ID) FROM TB001 WITH(NOLOCK)WHERE DT<GETDATE()DECLARE @ID INTSET @ID=0WHILE(@ID<10000)BEGIN;WITH T1 AS(SELECT TOP(100)* FROM TB001WHERE ID<@MaxIDORDER BY ID)DELETE FROM T1SET @ID=@ID+1END

從邏輯IO上看，性能沒有明顯提升，但是從CPU的角度來看，CPU的使用明顯降低,猜測有兩方面原因：1：日期比較消耗要大于INT(日期類似浮點數的存儲，處理需要消耗額外的CPU資源)

2：由于ID索引排序的原因，可能不需要對頁的所有數據逐行比較來判斷這些數據是否滿足條件(個人猜測，請勿當真)

由于ID是自增連續的，雖然可能有因為事務回滾或DBA干預導致不連續的情況，但這不是重點，重點是我們不一定要每次都刪除100行數據，因此我們可以按ID來進行區間刪除，拋棄TOP的方式：

DECLARE @MaxID INTDECLARE @MinID INTSELECT @MaxID=MAX(ID),@MinID=MIN(ID)FROM TB001 WITH(NOLOCK)WHERE DT<GETDATE()-1DECLARE @ID INTSET @ID=0WHILE(@ID<10000)BEGINDELETE  FROM TB001WHERE ID>=@MinID+@ID*100AND ID<@MinID+(@ID+1)*100AND ID<@MaxIDSET @ID=@ID+1END

測試發現，每次刪除的邏輯IO都很穩定且消耗很低，這才是最完美的東東?。?！

--=======================================================

總結：

本來看似一個很簡單的SQL,需要考慮很多方面，各種折騰，各種困惑，多看點基礎原理的資料，沒有壞處；大膽猜測，謹慎論證，多測試是驗證推斷的唯一辦法；

提點額外話:

1. 關于業務：在很多時候，DBA不了解業務就進行優化，是很糟糕的事情，而且很多優化的最佳地方是程序而不是數據庫，敢于否定開發人員所謂的“業務需求”也是DBA的一項必備技能。有一次優化發現，開發對上千萬數據排序分頁，問詢開發得到答復“用戶沒有輸入過濾條件”，難道用戶不輸入就不能設置點默認條件么？如果用戶查詢最新記錄，我們可以默認值查詢最近三天的數據。

2. 關于場景：有一些初學者，很期望獲得一些絕對性的推論，而不考慮場景的影響，且缺乏測試，武斷地下結論，這同樣是很可怕的事情，適合你場景的解決方案，才是最佳的解決方案。

遺留問題：

1. 針對本文提到的業務場景，還有一些其他解決方案，比如分區方式，定期進行分區切換再刪除數據，又比如使用SQL SERVER 2012中新增的“序列”；

2. 猜測上面所提到的問題根源是SQL Server刪除行的實現方式，在刪除時僅標示數據行被刪除而不是真正的從頁面刪除，在高頻率不間斷地刪除過程中，這些數據頁沒有被及時回收刪除掉，

SQL Server掃描了“本該”刪除的數據頁，造成邏輯讀較高；而使用ID的區間范圍查找，可以避免掃描到這些數據頁，直接移動到真正需要訪問的數據頁；當刪除頻率較低時(比如3秒刪除一次)，這種問題就不會出現。

--=============================

依舊是妹子：