SQL Server索引进阶第十三篇：Insert,Update,Delete语句-程序员宅基地

  索引设计是数据库设计中比较重要的一个环节，对数据库的性能其中至关重要的作用，但是索引的设计却又不是那么容易的事情，性能也不是那么轻易就获取到的，很多的技术人员因为不恰当的创建索引，最后使得其效果适得其反，可以说“成也索引，败也索引”。 本系列文章来自Stairway to SQL Server Indexes ,然后经过我们团队的理解和整理发布在agilesharp，希望对广大的技术朋友在如何使用索引上有所帮助。

    从本系列文章的第十篇到第十二篇讲述了索引的内部结构以及索引结构改变所带来的影响。在本篇文章中，我们继续来看由INSERT,DELETE,UPDATE以及MERGE操作对索引数据所产生的影响。首先让我们分别来看上面每个单独语句所产生的影响，再谈对于上面几种操作都产生影响的主题：行数据修改与索引数据修改。

系列文章索目录：

SQL Server索引进阶第一篇：索引介绍
SQL Server索引进阶第二篇：深入非聚集索引
SQL Server索引进阶第三篇：聚集索引
SQL Server索引进阶第四篇：页和区

SQL Server索引进阶第五篇：索引包含列
SQL Server索引进阶第六篇：书签
SQL Server索引进阶第七篇：过滤的索引

SQL Server索引进阶第八篇：唯一索引
SQL Server索引进阶第九篇：解读执行计划
SQL Server索引进阶第十篇：索引的内部结构
SQL Server索引进阶第十一篇：索引碎片分析与解决（上）
SQL Server索引进阶第十一篇：索引碎片分析与解决（中）-碎片发生原理深度剖析
SQL Server索引进阶第十二篇：索引的创建，修改和删除
SQL Server索引进阶第十三篇：Insert,Update,Delete语句
SQL Server索引进阶第十四篇：索引统计
SQL Server索引进阶第十五篇：索引的最佳实践

INSERT
    在第十一篇关于索引碎片的文章中，我们已经介绍了插入语句带来的影响，这里只是做一个总结性介绍，更详细的内容请翻回第十一篇。

    无论是将数据插入堆表或是聚集索引表，表上的每一个索引都需要对应添加一个条目，当然过滤索引可能除外。插入时，SQL Server根据索引键从跟节点一路向下找到叶子节点，找到叶子节点之后，SQL Server首先查看页内空间是否足够，如果页内空间足够，SQL Server就会将数据插入到页中。

    当然了，SQL Server也有可能遇到页已满的情况，这时，SQL Server会从分配结构找找到一个空闲页，接下来的操作取决于所插入数据的索引键的顺序，根据这个顺序，SQL Server会做下面三种操作中的一种:

• 当随机顺序:通常情况下，SQL Server将页内比较大的大约一半的值由已满的页移动到新页中，然后再将插入的数据插入到合适的页。这个操作会使得一个满页变为两个半满的页。如果继续插入数据，这两个页也会慢慢变满，然后再进行页分裂。慢慢的，每页都由半满向满的方向增长，这时页内平均的数据会维持在75%。
• 当升序排序:当SQL Server发现新插入的数据按顺序应该存储在当前满页的最后，则SQL Server会将这个条目插入到新页中，注意，仅仅是这一个条目。接下来插入的数据如果还是按照索引的顺序插入，则继续上面的步骤。因此几乎不存在页分裂的情况，因此内部碎片会保持在最小。
• 当降序排序:与上面的情况相反，当SQL Server发现新插入的数据应该存在页的第一个位置时，SQL Server就认为索引是降序排序的，也仅仅只将这一条数据插入到新页中。内部碎片也几乎不存在。

    将数据插入进页后，还需要做一些额外工作，比如说指向逻辑相邻页的前后链表指针需要更新，并且页分裂后还需要将一行数据提到父节点，也就是非叶子节点，非叶子节点也满时，最终导致非叶子节点的页分裂。

DELETE  

    当从表中删除一行后，表上的索引中相关条目也需要被删除。和INSERT一样，对于每一个索引，SQL Server都会从跟节点向下直到找到叶子节点。当找到叶子节点之后，SQL Server可能会马上删除这条数据，也可能不马上删除，而是在页中的标志位设置这页已经被删除。这种逻辑上被删除但物理上还存在术语称为:虚影记录（GHOST Record）,在接下来合适的时机，SQL Server才会删除虚影记录,我将在本篇文章后面进行详细阐述。

    当表中的数据被标为虚影记录时，这条记录就会被接下来的任何查询所无视。虽然逻辑上这条记录已经被删除，但物理上依然存在。虚影记录的数量可以在sys.dm_db_index_physical_stats这个DMV中进行查看。

    虚影记录是由于性能和并发的原因被引入，这不仅提高了DELETE语句的性能，如果DELETE被回滚(Rollback),也同样会提升性能。当回滚数据时，虚影记录仅仅需要将标志位改回来，而不是重新根据日志再创建一条记录。

    虚影记录何时被删除取决于多个因素：其中很多因素已经超出了本系列文章的讨论范围。正式因为因素众多，所以很难知道SQL Server何时真正的删除虚影记录。下面是一些影响虚影记录的因素:

• 如果存在行级锁，则被删除的索引条目会被标记为虚影记录
• 如果存在5000行以上的行锁，往往会被升级为表锁
• 使用行版本这种乐观并发控制也会造成虚影记录
• 事务完成之前虚影记录不会被删除
• SQL Server通过ghost-cleanup线程来删除虚影记录。但删除的时机却无法预料，DELETE操作不会影响ghost-cleanup的行为，但会将新的虚影记录加到待删除的虚影记录队列末尾，而这个线程定期清理这些记录
• ghost-cleanup线程大概每5秒被唤醒一次，每次唤醒大概清理10页虚影记录，这个值会随着SQL Server版本的不同而不同
• 你可以通过调用_clean_db_free_space 或 sp_clean_db_file _free_space来强制删除虚影记录

     换句话说，当你删除一行时，虽然逻辑上这行没了，但实际上它们并没有被删除，直到SQL Server认为满足安全条件来实际删除这行。

一个虚影记录的例子

    为了更好的理解虚影记录，我们使用一个有20000行记录的非聚集索引，使得数据填充满行，我们使用事务删除大约一半的数据但不Commit.然后通过Sys.dm_index_physical_stats来观察索引的使用情况，可以看到一部分数据被实际删除，而部分数据变为虚影记录。再然后我们提交这个事务，过一段时间就可以看到虚影记录被删除。
    下面做两个实验，第一种是每页删除一半的行，另一种是删除索引前一半的页。
    每次删除完数据时候，我们使用下面的视图来看虚影记录。只有最右边的列可以看到虚影记录的数据，视图代码如下。

1. USE AdventureWorks;
2. GO
3. IF EXISTS (SELECT *
4. FROM sys.objects
5. WHERE name = 'viewTestIndexInfo' and type = 'V')
6. BEGIN
7. DROP VIEW dbo.viewTestIndexInfo
8. END
9. GO
10. CREATE VIEW dbo.viewTestIndexInfo
11. AS
12. SELECT IX.name as 'Name'
13. , PS.index_level as 'Level'
14. , PS.page_count as 'Pages'
15. , PS.avg_page_space_used_in_percent as 'Page Fullness (%)'
16. , PS.ghost_record_count as 'Ghost Records'
17. FROM sys.dm_db_index_physical_stats( db_id(), object_id('dbo.FragTest')
18. , default, default
19. , 'DETAILED') PS
20. JOIN sys.indexes IX
21. ON IX.object_id = PS.object_id AND IX.index_id = PS.index_id
22. WHERE IX.name = 'PK_FragTest_PKCol';
23. GO

复制代码

代码1.查看碎片的视图

代码2用于创建测试表并载入数据，我们按照索引的顺序载入20000条数据。
（译者注，这段代码作者搞错了，把代码1复制了一遍，我根据自己对文章的理解写了下面的创建表和载入测试数据的代码，由于代码2是我根据上下文意思写的，后面的截图可能和作者截图中内容有偏差，所以我按照自己的截图结果来）

1. CREATE TABLE dbo.FragTest
2. (PKCol int)
3. DECLARE @index INT
4. SET @index=1
6. WHILE(@index <=20000)
7. BEGIN
8. INSERT INTO dbo.FragTest(pkcol) VALUES(@index)
9. SET @index=@index+1
10. END
11. CREATE CLUSTERED INDEX PK_FragTest_PKCol ON dbo.FragTest(pkcol)

复制代码

代码2.创建表并载入测试数据

测试数据加载完执行，执行SELECT * FROM dbo.viewTestIndexInfo就可以看到如图1所示的结果。

1301.jpg(15.06 K)

9/9/2012 11:21:20 AM

图1.几乎页满的索引

运行代码3所示的代码，从事务中进行隔行删除。

1. BEGIN TRANSACTION
3. DELETE DBO.FragTest
4. WHERE PKCol % 2 = 0;
6. SELECT *
7. FROM dbo.viewTestIndexInfo;
8. GO

复制代码

代码3.隔行删除

结果如图2所示。

1302.jpg(13.14 K)

9/9/2012 11:21:20 AM

图2.索引中包含了虚影记录

    当DELETE语句开始执行时，行锁会加在6228条记录上，当这些记录被删除后，生成6228条虚影记录，由于存在的行锁过多，锁会升级成表锁。这时实际上已经删除了10000-6228=3372条数据，这3372条数据的缺失造成页面使用百分比从大于97%降低到79%左右。
    接下来我们Commit上面未完成的事务，此时后台的ghost cleanup线程就会清除虚影记录，过几秒后，我们可以看到如图3所示，虚影记录被删除，页的使用百分比降低到大约48.6%左右。

1303.jpg(13.19 K)

9/9/2012 11:21:20 AM

图3.事务提交后页面使用的百分比

另一个版本虚影记录的例子  
     当虚影记录从叶节点删除后，可能造成页中不存在任何数据。此时这个页就可能被释放。下面我们来看这种情况。
    对于这个例子，我们从新运行代码2中创建和载入测试数据的例子。
    现在开始删除索引中前半部分数据，如代码4所示。

1. BEGIN TRANSACTION
3. DELETE DBO.FragTest
4. WHERE PKCol <= 20000 / 2;
6. SELECT *
7. FROM dbo.viewTestIndexInfo;
8. GO

复制代码

代码4.删除前10000条数据

此时得到结果如图4所示。

1304.jpg(14.88 K)

9/9/2012 11:21:20 AM

图4.删除前半部分数据后的结果

    同样，大约6000条数据变为虚影记录，另外10000-6220=3780条数据被实际删除。由于被删除的记录是在物理上连续的，所以一些空页被释放，图4的页数由之前的33降低到27.
    接着，我们COMMIT事务，然后再来看页数，如图5所示。

1305.jpg(15.07 K)

9/9/2012 11:21:20 AM

图5.提交事务后页数占用由27降低到18

    前一半记录从索引中删除之后，没有记录的页被释放并不再属于索引，其中在索引中间位置的一页，其中只删除了一半左右的行，因此继续存在，其它页中页满程度保持不变。
    那为什么还存在一条虚影记录呢？因为第一个叶子节点的地址，和索引的根节点一样，都是存在系统的metadata中，因此一旦分配了，叶子的第一个节点和最后一个节点永远不会释放。ghost-cleanup线程会在第一个页中留下虚影记录以保证这个页不会被删除。
    对于非叶子节点来说，删除意味着直接删除而不会留下虚影记录。如果一个非叶子节点的页中不存在数据，则会被释放，并删除其父节点对其的指针。
    根节点不属于这个模式应用之列，根节点永远不会被删除。即使索引中不存在任何数据，根节点还是不会被删除，对于每个索引来说都至少需要存在一页，这一页就是根节点。

UPDATE

    当表中的数据更新时，索引条目就需要被修改。SQL Server修改数据分为两种方式，一种是直接UPDATE，另一种是先DELETE再INSERT,SQL Server通常情况下会尽量直接UPDATE,但在特定情况下无法直接UPDATE的时候，SQL Server只能先DELETE再INSERT，这几种情况如下:

• 更新需要修改索引键列的值，需要这一列在索引中重新定位
• 更新可变列的值导致页无法容纳这个更新
• 表上存在DML触发器

    如果列上被修改的数据包括索引键，则这行数据需要在索引中改变位置，这就需要这条记录在当前位置被删除并插入到新的位置。当然，如果改变后的位置和当前位置在同一个页中，则可以直接UPDATE。SQL Server需要从根节点开始向下找到叶子节点两次，一次是查找当前位置，另一次是查找新插入的位置。
    另外，如果被修改的数据包括索引键，则所有对应的非聚集索引的书签值也需要改变。
    如果修改的数据不包括索引键，则行在索引的位置不会被改变，但条目的大小可能会被改变，如果当前的页无法容纳下新的行，则先DELETE后UPDATE。

MERGE

    MERGE操作在SQL Server 2008之后被引入，功能强大、灵活、有用。但实际上，MERGE背后是生成与之等效的INSERT,UPDATE,DELETE语句。使用MERGE语句所带来的影响和其生成的这三个DML语句所带来的影响效果相同。

一次性更新索引

    当INSERT,UPDATE,DELETE语句执行在单一行时，SQL Server直接执行这个操作并修改与之对应的非聚集索引，但如果是一次性操作在多行时，SQL Server将会有两个选择:
    一行一行的更新，每更新一行则修改对应的索引
    或
    一行一行的更新，但不直接修改对应的索引，而是将这个修改列表挂起缓存，当所有的行更新完毕后，再根据缓存队列修改索引。

    上面第二种方式就是所谓的一次性更新索引(index-at-a-time update)。对于INSERT,UPDATE,DELETE语句来说都可能应用到这种方式。
    SQL Server查询优化器来决定使用那种方式进行更新，一次性更新的行越多，则第二种方式被使用的概率越大。
    为了演示这点，我们创建如下代码，如代码5所示。

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

复制代码

代码5.测试表

    然后插入一条数据，如代码6所示。

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

复制代码

代码6.插入一条数据
下面来看图6的执行计划，可以看出只有一个插入，因为涉及的数据量非常少。

1306.jpg(6.19 K)

9/9/2012 11:21:20 AM

图6.单一表插入的执行计划

    但是一次性批量插入数据情况就不一样了,这次我们通过代码7批量插入数据

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

复制代码

代码7.批量插入数据

下面来看图7这个查询计划，包含了多个操作。主要是将带插入数据排序后插入索引。

1307.jpg(40.41 K)

9/9/2012 11:21:20 AM

图7.批量插入

    索然这个执行计划看上去很复杂，将挂起的修改和更新进行排序再插入索引，但这种方法效率更高，因为连续的索引条目被直接插入索引。这种方式使得索引的索引碎片更少。

总结

    将一条数据插入的索引根据插入数据的键值可能导致三种碎片方式中的一种。
    从索引中删除条目，包括聚集索引中删除条目，有可能不直接删除条目，取而代之标记为虚影记录。虚影记录只会在叶子节点中存在，SQL Server会在一段时间后删除虚影记录，但必须在事务完成之后。
    更新索引条目可能直接删除，也可能删除后再插入。如果底层表没有DML触发器或是更新不会导致索引条目的增加和位置的改变，则UPDATE语句会直接更新。
    如果数据修改语句涉及大量的行，SQL Server将会使用一次性更新索引，先更新了表中的信息，再排序这些更改一次性插入索引。