由delete導致的逾時已到期問題

標籤：style   blog   http   color   使用   os   strong   資料   

 

1. 問題

　　開發人員反映應用程式中一條簡單的delete語句執行報“逾時已到期”錯誤。delete語句形式如下：

　　delete * from table_1 where [email protected]

2. 分析

　　1）驗證delete檢索欄位是否有索引

　　首先我想到的是檢索欄位 id 列上是否有索引，即是否能很快找到這條待刪除的語句。

　　查看錶的索引列表後，發現id上是存在索引的，而且是叢集索引。

　　單獨執行 select * from table_1 where [email protected] 走的是叢集索引尋找，速度是非常快的

　　所以不是因為檢索欄位缺失索引導致的

　　2）驗證是否存在阻塞

　　接下來猜測是不是發生了阻塞，即delete語句等待其他會話釋放KEY上的鎖以獲得X鎖來執行刪除

　　使用sys.sysprocesses查詢當前delete工作階段狀態，發現並未阻塞

　　3）查看delete語句的預估執行計畫

　　前兩步驗證完畢後，越發覺得有點無從下手的感覺。拋下自己所謂的經驗，先看下delete語句執行計畫吧

　　因為語句執行逾時，不能查看真正的執行計畫，所以查看估計的執行計畫來分析問題。

　　以在AdventureWorks2012測試刪除為例

　　執行delete from Person.Person where BusinessEntityID=6，執行計畫部分為：

　　

3. 結論　　

　　從執行計畫中，發現了問題原因：

      刪除資料的表被其他表所引用，SQLServer在刪除被參考資料表資料時，會檢查參考資料表是否存在引用值記錄，以保證資料的參照完整性。

　　而目前參考資料表在外鍵欄位上沒有索引，導致使用索引掃描來尋找，並且參考資料表記錄數在百萬以上，導致刪除逾時

4. 處理

　　在參考資料表的外鍵欄位上增加非叢集索引

5. 思考

　　1）應用程式的物理刪除資料是否合理及必須呢？是否可以通過增加刪除標記或者單據狀態之類，來實現邏輯刪除呢？

　　2）參考資料表中欄位的外鍵是否必須建立呢？看了一些應用系統，如用友、金蝶的系統，表中的外鍵欄位很少。外鍵欄位過多對插入刪除的速度會有一定的影響。

　　3）如果建立了外鍵約束的話，參考資料表的外鍵欄位和被參考資料表的主鍵欄位應該最好要建立索引

　　如有不對的地方，歡迎拍磚，謝謝！O(∩_∩)O