本文是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产生的效果一样，所以在本系列中没有介绍。
- MERGE 目标表
- USING 源表
- ON 匹配条件
- WHEN MATCHED THEN
- 语句
- WHEN NOT MATCHED THEN
- 语句;
以上是MERGE的最最基本的语法，语句执行时根据匹配条件的结果，如果在目标表中 找到匹配记录则执行WHEN MATCHED THEN后面的语句，如果没有找到匹配记录则执行WHEN NOT MATCHED THEN后面的语句。注意源表可以是表，也可以是一个子查询语句。
格外强调一点，MERGE语句最后的分号是不能省略的!
- MERGE ProductNew AS d
- USING
- Product
- AS s
- ON s.ProductID = d.ProductId
- WHEN NOT MATCHED THEN
- INSERT( ProductID,ProductName,Price)
- VALUES(s.ProductID,s.ProductName,s.Price);
- MERGE ProductNew AS d
- USING
- Product
- AS s
- ON s.ProductID = d.ProductId
- WHEN NOT MATCHED THEN
- INSERT( ProductID,ProductName,Price)
- VALUES(s.ProductID,s.ProductName,s.Price)
- WHEN MATCHED THEN
- UPDATE SET d.ProductName = s.ProductName, d.Price = s.Price;
一次性更新索引Index-at-a-Time Update
当执行插入，更新，删除语句操作表的一行的时候，SQL Server肯定会修改数据，然后修改索引。在执行完插入，更新，删除数据之后，SQL Server有两个选择：
- 对每一行，执行完操作之后，都去修改索引。
- 对每一行，执行完操作之后，对每个索引，将修改信息挂起在一个集合中。等所有的行都执行完操作之后，在执行挂起的索引修改集合。
第二种叫做“一次性更新索引”，是插入，更新，删除操作的一个选项。
SQL Server查询优化器将会决定采用哪一种来优化性能。如果修改的是表中的大部分行，很有可能会使用第二种。
为了证明，我们新建一张表，包含两个索引。
- USE AdventureWorks;
- GO
- IF EXISTS (SELECT *
- FROM sys.objects
- WHERE name = 'FragTestII' and type = 'U')
- BEGIN
- DROP TABLE dbo.FragTestII;
- END
- GO
- CREATE TABLE dbo.FragTestII
- (
- PKCol int not null
- , InfoCol nchar(64) not null
- , CONSTRAINT PK_FragTestII_PKCol primary key nonclustered (PKCol)
- );
- GO
- CREATE INDEX IX_FragTestII_InfoCol
- ON dbo.FragTestII (InfoCol);
- GO
先执行一个插入一条记录的语句。
- INSERT dbo.FragTestII
- VALUES (100000, 'XXXX');
上图的执行计划，只是显示了插入数据的过程，没有显示索引更新的信息。这是因为，上面的情况下，索引的更新是行更新的一部分。
当时，当我们插入大量数据的时候，执行计划就会不一样了。
我们先构造一个20000条记录的FragTest表，然后将FragTest的数据批量插入FragTestII表。
- CREATE TABLE dbo.FragTest
- (
- PKCol int IDENTITY(1,1) not null
- , InfoCol nchar(64) not null
- , CONSTRAINT PK_FragTest_PKCol primary key nonclustered (PKCol)
- );
- GO
- DECLARE @index INT
- SET @index=0
- WHILE (@index<20000)
- BEGIN
- INSERT INTO dbo.FragTest(InfoCol)VALUES('123')
- SET @index=@index +1
- END
- INSERT dbo.FragTestII
- SELECT PKCol, InfoCol
- FROM dbo.FragTest;
执行计划就是上面的样子，包含很多的操作。一类操作是表中插入数据。有两个排序，每个都包含一个插入索引的操作。
尽管是一个复杂的执行计划，排序和更新挂起的索引的单独执行的，但也是一个高效的执行计划。相比随即添加索引，有顺序的添加索引，产生的碎片会更少。
结论
在索引中插入入口会导致三种碎片，这依赖于插入入口的顺序。
从索引中删除入口，包括从聚集索引中删除，可能会立即删除入口。也可能会创建ghost record使得索引入口成为逻辑删除。ghost只是存在于叶子层。SQL Server在事务完成之后，才会删除ghost record。
更新索引可能会立即就地更新，也可能是删除后在插入。如果表中没有DML的触发器，如果更新没有重新分配入口，或者增加入口的大小，通常还是会就地更新的。
如果数据修改语句影响的是大量的行，SQL Server可能会选择一次性更新索引，先修改表，然后在更新每个索引。
本文转自 virusswb 51CTO博客，原文链接：http://blog.51cto.com/virusswb/990469，如需转载请自行联系原作者
