我可以从概念上理解在插入的情况下会发生什么,因为sql Server必须将条目写入与新行匹配的每个索引中,但更新和删除对我来说更加模糊,因为我无法完全理解数据库引擎必须要做的.
我们以DELETE为例,假设我有以下模式(请原谅伪sql)
TABLE Foo col1 int,col2 int,col3 int,col4 int PRIMARY KEY (col1,col2) INDEX IX_1 col3 INCLUDE col4
现在,如果我发表声明
DELETE FROM Foo WHERE col1=12 AND col2 > 34
我理解引擎必须做什么才能更新表(如果你愿意,还可以使用聚簇索引).设置索引以便于查找要删除的行范围并执行此操作.
但是,此时它还需要更新IX_1,我给它的查询没有给数据库引擎找到要更新的行的明显有效方法.它是否被迫在此时进行完整的索引扫描？引擎是否首先从聚簇索引中读取行并对索引生成更智能的内部删除？
如果我更好地理解幕后发生的事情,我可以帮助我解决这个问题,但我想我的真正问题是这个.我有一个数据库,它花了很多时间在删除,我试图弄清楚我能做些什么.
当我显示删除的执行计划时,它只显示表Foo上的“Clustered Index Delete”条目,该条目在详细信息部分列出了需要更新的其他索引但我没有得到相关成本的任何指示这些其他指数.
解决方法
它不必进行全面扫描,因为：
>您的查询将使用聚集索引来查找行
>行包含其他索引值(c3)
>使用其他索引值(c3)和聚簇索引值(c1,c2),它可以找到另一个索引中的匹配条目.
(注意：我在解释文档方面遇到了麻烦,但我认为你的案例中的IX_1可以定义为它也在c1,c2上排序.由于这些已经存储在索引中,因此使用它们是完全合理的.更有效地查找记录,例如更新和删除.)
所有这些都需要付出代价.对于每个匹配的行：
>它必须读取行,找出c3的值
>它必须在非聚集索引中找到(c3,c1,c2)的条目
>它也必须从那里删除条目.
此外,虽然范围查询在您的情况下对聚簇索引有效(线性访问,在找到匹配后),但维护其他索引很可能会导致对每个匹配行随机访问它们.随机访问的成本远高于仅从给定匹配开始枚举B+ tree叶节点的成本.
鉴于上述查询,在非聚集索引维护上花费的时间更多 – 数量在很大程度上取决于col1 = 12 AND col2>选择的记录数量. 34
谓语.
我的猜测是,成本在概念上与你没有二级索引相同但是例如一个单独的表,将(c3,c2)保存为聚簇键中的唯一列,并使用(c3,c2)为每个匹配的行执行DELETE.显然,索引维护是sql Server内部的并且速度更快,但从概念上讲,我猜上面的内容很接近.
以上意味着索引的维护成本将保持非常接近,因为每个二级索引中的条目数相同(记录数),并且删除只能在每个索引上逐个进行.
如果您需要索引,性能方面,取决于已删除记录的数量,您可能最好安排删除,删除索引(删除期间未使用的索引) – 删除之前和之后添加它们.根据受影响的记录数量,重建indexes might be faster.
