由delete导致的超时已过期问题

 

1. 问题

　　开发人员反映应用程序中一条简单的delete语句执行报“超时已过期”错误。delete语句形式如下：

　　delete * from table_1 where id=@value

2. 分析

　　1）验证delete检索字段是否有索引

　　首先我想到的是检索字段 id 列上是否有索引，即是否能很快找到这条待删除的语句。

　　查看表的索引列表后，发现id上是存在索引的，而且是聚集索引。

　　单独执行 select * from table_1 where id=@value 走的是聚集索引查找，速度是非常快的

　　所以不是因为检索字段缺失索引导致的

　　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