【翻译】SQL Server索引进阶：第十三级，插入，更新，删除

本文是SQL Server索引进阶系列（Stairway to SQL Server Indexes）的一部分。

在第十级到十二级中，我们看了索引的内部结构，以及改变结构造成的影响。在本文中，继续查看Insert，update，delete和merge造成的影响。首先，我们单独看一下这四个命令。

插入INSERT

当向表中插入一行数据的时候，不管表是堆表还是聚集索引表，肯定会在表的索引中插入一个入口，过滤索引除外。这么做的时候，SQL Server使用索引键的值从根页到叶子层页，到达叶子层页之后，检查页的可用空间，如果有足够的空闲空间，新的入口就会被插入适当的位置。

最终，SQL Server可能会试图向一个已经没有空间的页插入入口信息。这时候，SQL Server就会查询位置结构，找一个有空闲空间的页。一旦找到，就会做三件事，每一件都和要插入的索引键的顺序有关：

随机序列：正常情况，SQL Server会将满页的一半的入口移动的一个空页，然后将新入口插入合适的页，这就产生了两个用了一半的页。如果你的应用继续插入数据，但是不删除数据，这两个页将会从用了一半的状态变成满页，然后再被分成两个半页，然后再次变成满页，这样周而复始，循环往复。每页的充满率大概是75%。

增序序列：SQL Server发现新的入口需要插入满页的最后面，就会新建一页，然后插入这个新入口，新页再次满了的话，就再新建一页。一旦一页满了之后，他就一直是满的，所以内部碎片很小，甚至没有。

降序序列：相反，如果SQL Server发现新的入口需要插入满页的开始，也会新建一页，插入新的入口，但是由于是降序，内部碎片接近100%。

删除DELETE

当从表中删除一行的时候，对应的索引入口会从索引中删除。对每一个索引，SQL Server为了查找入口，都要从根页导航到叶子层页。一旦SQL Server发现入口，就会做两件事：立即删除入口，或者是在行的头部设置标记，使得入口变成ghost record，在适当的时候，就会删除ghost record。

Ghost record在查询的时候，会被忽略。它们只是在物理上还存在，逻辑上已经不存在了。一个索引的ghost record的数量可以通过系统函数sys.dm_db_index_physical_stats来获取。

SQL Server没有立即删除是出于性能和并发管理的需要。不仅仅是删除本身的性能，也包括随后的事务回滚性能。上面的做法使得回滚一个删除操作是很容易的，相比较从事务日志中重新创建记录而言。

下面的因素会影响删除的处理过程：

• 如果行被锁定，删除的索引会变成ghost record。
• 如果执行的过程需要锁定5000行数据，行级别的锁会升级为表级别的锁。
• 作为并发技术，行版本的使用，也会导致出现ghost record。
• 直到事务完成，才会删除ghost record。
• SQL Server的后台线程ghost-cleanup负责删除ghost record，但是，什么时候删除也是不可预期的。删除操作本身并不通知ghost-cleanup线程去这么做，随后的页扫描会将包含ghost record的页加入一个列表，ghost-cleanup线程会定期的处理这个列表。
• ghost-cleanup线程大约每5秒钟唤醒一次。每次会清理10页。这些数字都是可以设置的。
• 你可以通过sp_clean_db_free_space或者sp_clean_db_file_free_space来强制清理，将会删除整个数据库或者数据文件中的ghost record。

换句话说，当你删除数据行的时候，逻辑上讲已经删除了。如果没有被理解删除，只要SQL Server认为是安全的，他们就会被删除。

更新UPDATE

当更新表中数据行的时候，需要修改索引的入口。对于每一个索引入口，SQL Server会执行就地的更新，或者是删除再插入。只要有可能，SQL Server还是会使用就地更新。但是，也有一些情况不能就地更新，SQL Server就会执行删除紧跟着插入。下面是一些这方面的原因：

• 更新要修改键列，导致索引的入口需要重新分配。
• 更新要修改很多列，导致入口不合适在当前页。
• 在表上有DML的触发器。

如果修改的列是索引键的一部分，入口的位置肯定要变化。入口会从旧的位置删除，以新键的顺序在新的位置插入入口。大部分情况，都会再删除之后执行插入。如果新的位置和旧的位置在同一页，有可能会就地更新。SQL Server会从根页到叶子层跑上两次，一次用来查找当前的入口位置，一次用来决定入口的新位置。

如果修改的列是聚集索引的一部分，所有的非聚集索引都需要更新，因为他们的标签是由聚集索引的键组成的。

如果修改的不是索引键的一部分，入口的位置不会改变。但是，入口的大小可能会改变。如果页中不够空间存放新的入口，更新就会变成删除再插入。

合并MERGER

在SQL Server 2008中引入了合并操作，很强大，很灵活，很好。合并操作会产生插入，更新，删除语句。合并和你写insert，udpate，delete产生的效果一样，所以在本系列中没有介绍。

1. MERGE 目标表 
2.  
3. USING 源表 
4.  
5. ON 匹配条件 
6.  
7. WHEN MATCHED THEN 
8.  
9.    语句 
10.  
11. WHEN NOT MATCHED THEN 
12.  
13.    语句; 

以上是MERGE的最最基本的语法，语句执行时根据匹配条件的结果，如果在目标表中 找到匹配记录则执行WHEN MATCHED THEN后面的语句，如果没有找到匹配记录则执行WHEN NOT MATCHED THEN后面的语句。注意源表可以是表，也可以是一个子查询语句。

格外强调一点，MERGE语句最后的分号是不能省略的!

1. MERGE ProductNew AS d 
2. USING 
3.     Product 
4. AS s 
5. ON s.ProductID = d.ProductId 
6.     WHEN NOT MATCHED THEN 
7.             INSERT( ProductID,ProductName,Price) 
8.                     VALUES(s.ProductID,s.ProductName,s.Price); 

1. MERGE ProductNew AS d 
2. USING 
3.     Product 
4. AS s 
5. ON s.ProductID = d.ProductId 
6. WHEN NOT MATCHED THEN 
7.     INSERT( ProductID,ProductName,Price) 
8.         VALUES(s.ProductID,s.ProductName,s.Price) 
9. WHEN MATCHED THEN 
10.     UPDATE SET d.ProductName = s.ProductName, d.Price = s.Price; 

一次性更新索引Index-at-a-Time Update

当执行插入，更新，删除语句操作表的一行的时候，SQL Server肯定会修改数据，然后修改索引。在执行完插入，更新，删除数据之后，SQL Server有两个选择：

• 对每一行，执行完操作之后，都去修改索引。
• 对每一行，执行完操作之后，对每个索引，将修改信息挂起在一个集合中。等所有的行都执行完操作之后，在执行挂起的索引修改集合。

第二种叫做“一次性更新索引”，是插入，更新，删除操作的一个选项。

SQL Server查询优化器将会决定采用哪一种来优化性能。如果修改的是表中的大部分行，很有可能会使用第二种。

为了证明，我们新建一张表，包含两个索引。

1. USE AdventureWorks; 
2. GO 
3. IF EXISTS (SELECT * 
4.        FROM sys.objects 
5.          WHERE name = 'FragTestII' and type = 'U') 
6. BEGIN 
7.   DROP TABLE dbo.FragTestII; 
8. END 
9. GO 
10. CREATE TABLE dbo.FragTestII 
11.    ( 
12.     PKCol  int not null 
13.    , InfoCol nchar(64) not null 
14.    , CONSTRAINT PK_FragTestII_PKCol primary key nonclustered (PKCol) 
15.    ); 
16. GO 
17. CREATE INDEX IX_FragTestII_InfoCol 
18.      ON dbo.FragTestII (InfoCol); 
19. GO 

先执行一个插入一条记录的语句。

1. INSERT dbo.FragTestII 
2. VALUES (100000, 'XXXX'); 

上图的执行计划，只是显示了插入数据的过程，没有显示索引更新的信息。这是因为，上面的情况下，索引的更新是行更新的一部分。

当时，当我们插入大量数据的时候，执行计划就会不一样了。

我们先构造一个20000条记录的FragTest表，然后将FragTest的数据批量插入FragTestII表。

1. CREATE TABLE dbo.FragTest 
2.    ( 
3.     PKCol  int IDENTITY(1,1)  not null 
4.    , InfoCol nchar(64) not null 
5.    , CONSTRAINT PK_FragTest_PKCol primary key nonclustered (PKCol) 
6.    ); 
7. GO 
8.  
9. DECLARE @index INT 
10. SET @index=0 
11.  
12. WHILE (@index<20000) 
13. BEGIN 
14.     INSERT INTO dbo.FragTest(InfoCol)VALUES('123') 
15.      
16.     SET @index=@index +1 
17.      
18. END 

1. INSERT dbo.FragTestII 
2.   SELECT PKCol, InfoCol 
3.   FROM dbo.FragTest; 

执行计划就是上面的样子，包含很多的操作。一类操作是表中插入数据。有两个排序，每个都包含一个插入索引的操作。

尽管是一个复杂的执行计划，排序和更新挂起的索引的单独执行的，但也是一个高效的执行计划。相比随即添加索引，有顺序的添加索引，产生的碎片会更少。

结论

在索引中插入入口会导致三种碎片，这依赖于插入入口的顺序。

从索引中删除入口，包括从聚集索引中删除，可能会立即删除入口。也可能会创建ghost record使得索引入口成为逻辑删除。ghost只是存在于叶子层。SQL Server在事务完成之后，才会删除ghost record。

更新索引可能会立即就地更新，也可能是删除后在插入。如果表中没有DML的触发器，如果更新没有重新分配入口，或者增加入口的大小，通常还是会就地更新的。

如果数据修改语句影响的是大量的行，SQL Server可能会选择一次性更新索引，先修改表，然后在更新每个索引。

本文转自 virusswb 51CTO博客，原文链接：http://blog.51cto.com/virusswb/990469，如需转载请自行联系原作者