【译】索引进阶（十五）：插入，更新，删除索引

此文为翻译，由于笔者语言及技术水平有限，疏漏在所难免，欢迎建议指正。

原文链接：传送门。

在之前的章节，我们看到了索引的内部结构以及这种结构的改变所带来的影响。在本章节我们继续这一主题，检查INSERT，DELETE，UPDATE和MERGE语句的影响。

首先我们分别看下这四个命令，然后我们包含一个对于所有三个命令都可用的主题：每行数据更新以及每个索引数据的更新。

插入

通过在第十一章节介绍索引碎片的主题，我们将枪口瞄准了插入语句。我们所说的东西这儿有一个概述，如果需要一个更完整的论述，以及示例代码，请参见第十一章节-索引碎片。

当一行数据被插入表的时候，不管这个表是一个堆或者是一个聚集索引，一个条目便会被插入到这个表的各个索引中（对于过滤索引可能会有所例外）。当插入索引条目的时候，SQL SERVER使用数据行的索引键值从索引的跟节点遍历到叶子节点。当它到达了叶子层级节点，它检查叶子页的可用空间，如果那个也具有足够的可用空间，新的索引条目便会插入到这个页的合适的位置。

事实上SQL SERVER会尝试将一个索引条目插入到一个已经满了的页中。当发生这种事情的时候，SQL SERVER会查询它的存储空间分配结构来找到一个空的页，一旦找到了一个空的页，它会做如下三件事的一件，其依赖于新的索引键被插入的顺序。

随机序列：通常，SQL SERVER从满的页里移动一半的条目到空的页。然后将新条目插入到合适的页。因此产生了两个半满的页，如果你的应用程序继续插入却并不删除数据行，这两个也实际上也会从半满增长到满，然后它们各自又会分裂为半满的页。随着时间的流逝，每个页都从今半满到满一次一次的循环，最终导致了平均页覆盖率大概为75%。

升序序列：然而，如果SQL SERVER注意到新条目是满页的最后一个条目，它会假设数据行是以索引键相同的顺序来插入数据表，因此，它将新的数据行放置在空的页中，如果SQL SERVER的假设是正确的，数据将会以索引键的顺序到达，后续的数据行都会属这个相同的页。这个页便会被填充，导致另一个新页被分配，当一个页充满后，它便停留在充满额的状态，这样便会产生很少的内部碎片甚至没有内部碎片。

降序顺序：相反的，如果SQL SERVER注意到新条目是所充满页的第一个条目，它便会假设数据行是以降序的顺序插入数据库表的，再一次，它将新条目放置在空的页中。此时的内部碎片和升序序列模式的内部碎片是相同的：几乎是100%。

当新条目被放置到页中，一些清理工作需要被完成，跨越三个页的四个前一页/后一页指针肯定会被更新，并且指向新页的一个条目会被插入更高层级的索引页中，而这，反过来又会导致在那个页层级上的页分裂。

删除

当一个数据行被从表中删除，它的交互索引条目也会从索引中移除。再一次的，对于每一个索引，SQL SERVER从根节点导航到叶子叶子层级的页并且找到对应的条目。一旦SQL SERVER 找到相应的条目，它便会做如下两件事之一：要么立即移除这个条目，要么在表头中设置一个标准位（bit），使得这个条目成为索引中的幽灵记录，这种技术称为条目幽灵化（ghosting the entry），在恰当且合适的时机，幽灵记录将会被移除。这个在本章节后续会介绍。

这个表后续的所有查询将会忽略索引中幽灵列的存在，在物理上来看，幽灵列的确存在，但是逻辑上它们并不存在。索引中的幽灵列的数量可以通过sys.dm_db_index_physical_stats这个系统函数来获取到。

正是因为性能和并发管理的原因，SQL SERVER将一个条目标记为后续删除而不是立即删除它。不仅仅是DELETE语句本身的性能，还有后续的事务回滚的性能都将从幽灵化的列中受益，毕竟，通过反标记一个条目来回滚一个DELETE操作，要比通过事务日志来重建这个条目要容易的多。

是否幽灵化一个索引条目的决定，以及在它被确实删除之前流逝的时间的长度受到很多因素的影响，它们中的许多概念都不在本系列的主题之内。这些多样的因素使得我们很难预言是否一个DELETE 操作会确实移除一个条目，或者是幽灵化一个条目，亦或两个兼有。而这，反过来又使得我们很难预言一个DELETE语句对于一个索引碎片的即刻影响。

影响DELETE处理过程的因素部分列表如下所示：

如果行级锁生效，那么将会幽灵化一个条目。

如果在DELETE语句的执行过程中，需要5000个锁，那么锁通常便会升级为表级锁。

行版本代替锁的使用作为并发机制，可能会导致删除行的幽灵化。

幽灵化的记录在事务结束之前绝不会被真正移除。

SQL SERVER的后台幽条目清理线程会移除幽灵行，然而，准确说何时它将进行清理是不可预知的。DELETE语句本身并不会通知后台幽灵清理线程一个幽灵行的存在。而是后续的页扫描将这些页加到包含幽灵列的列表中，这个列表将周期性的被清理线程所处理。

幽灵清理线程大概每5秒钟唤醒一次，每次执行会清理10个页。这个数字可能会在后续的发布中发生改变。

你可以通过执行sp_clean_db_free_space 或 sp_clean_db_file _free_space 这两个存储过程来强制进行幽灵列额清理。它们将会从整个数据库或者指定的数据库文件中移除幽灵列。

换句话说，当你删除数据行时，逻辑上来讲，它们不存在了。而如果它们没有被立即移除，当条件允许SQL SERVER来进行处理的时候，它们将会被彻底删除。

一个关于幽灵记录的示例

为了演示幽灵记录，我们用上面提到的顺序插入模式来加载一个具有非聚集索引的包含20000条数据的表，这将产生一个页完全充满的表。然后，在一个未被提交的事务中，我们删除一半的表数据然后检查结果。使用 sys.dm_db_index_physical_stats这个系统视图我们将看到一些删除的数据已经被移除而另一些记录则被幽灵化，最后，我们将事务提交并再次检查这个表，也许要检查好几次，直至幽灵记录已经被移除。

这个示例中我们做了两个变化，其中一个我们隔一行删除一行，因此会删除所有页的一半数据行，另一个我们删除条目的前一半数据行，因此会删除一半页的数据行。

在各个DELETE之后，为了查看幽灵记录以及碎片的存在，我们使用了一个与之前使用过的查询相似的查询。然而仅仅是这次，最右边的列会告诉我们索引中幽灵记录的数量，因为我们要使用这个查询来访正在被打开的事务修改的索引，我们必须在事务正在使用的相同数据库连接上执行查询。为了简短起见，我们创建一个名为 dbo.viewTestIndexInfo的视图，如列表1所示，我们在测试脚本中从这个视图中进行查询。

USE AdventureWorks;
GO
IF EXISTS (SELECT *
       FROM sys.objects
      WHERE name = 'viewTestIndexInfo' and type = 'V')
BEGIN
  DROP VIEW dbo.viewTestIndexInfo
END
GO
CREATE VIEW dbo.viewTestIndexInfo
AS
SELECT IX.name as 'Name'
   , PS.index_level as 'Level'
   , PS.page_count as 'Pages'
   , PS.avg_page_space_used_in_percent as 'Page Fullness (%)'
   , PS.ghost_record_count as 'Ghost Records'
 FROM sys.dm_db_index_physical_stats( db_id(), object_id('dbo.FragTest')
                   , default, default
                   , 'DETAILED') PS
 JOIN sys.indexes IX
  ON IX.object_id = PS.object_id AND IX.index_id = PS.index_id
 WHERE IX.name = 'PK_FragTest_PKCol';
GO

View Code

列表1：碎片报告视图

列表2展示了用来创建表并加载我们的表的代码。它以索引顺序排列，。并具有20000行数据，它同样与我们在第十一章节（原文）所用过的示例类似。

USE AdventureWorks;
GO
IF EXISTS (SELECT *
       FROM sys.objects
      WHERE name = 'viewTestIndexInfo' and type = 'V')
BEGIN
  DROP VIEW dbo.viewTestIndexInfo
END
GO
CREATE VIEW dbo.viewTestIndexInfo
AS
SELECT IX.name as 'Name'
   , PS.index_level as 'Level'
   , PS.page_count as 'Pages'
   , PS.avg_page_space_used_in_percent as 'Page Fullness (%)'
   , PS.ghost_record_count as 'Ghost Records'
 FROM sys.dm_db_index_physical_stats( db_id(), object_id('dbo.FragTest')
                   , default, default
                   , 'DETAILED') PS
 JOIN sys.indexes IX
  ON IX.object_id = PS.object_id AND IX.index_id = PS.index_id
 WHERE IX.name = 'PK_FragTest_PKCol';
GO

列表2：以充满页的方式加载一个表/索引

当表被加载之后，立即执行“SELECT * FROM dbo.viewTestIndexInfo”，它会暴露我们的表的非聚集索引的大小以及页的覆盖率，如图1所示：

{图片未上传}

图1：具有充满页的索引

运行列表3所示的代码，会开始一个事务，隔一行删除一个数据行，然后从视图中进行查询。

BEGIN TRANSACTION
 
DELETE DBO.FragTest
 WHERE PKCol % 2 = 0;
 
SELECT *
 FROM dbo.viewTestIndexInfo;
GO

View Code

列表3：删除隔行数据

其结果显示在图2：

{图2未上传}

图2：具有幽灵记录的索引

当DELETE语句开始执行的时候，行级别锁被放置并且删除幽灵化的索引条目，在4972 行数据被删除之后，便会产生4972个幽灵记录，这么多的行级锁存在导致锁将升级为表级锁。所有后续的删除，10000 – 4972 = 5028行数据，导致索引条目直接被移除而不是被幽灵化。这5028 个索引条目的移除将平均页覆盖率减少至73%。

提交这个事务会使得删除列可以被清理（ghost cleanup）。在其后的某个时刻，运行列表1的视图将暴露图3所示的页碎片。页的数量仍然是一样的，但是现在页是处于一个半满的状态，验证了幽灵列已经从表中移除。

{图3未上传}

图3：:事务被提交之后的索引

另一个变更

当幽灵记录被从叶子节点中移除的时候，那些叶子节点或许会变得完全为空，当这种情况发生的时候，这个叶子节点便会被释放，而与之交互的最低非叶子节点的指针，也就是一级节点的指针，页会被删除，我们用第二个示例来检查这种行为。

对于这个变更，我们再次运行列表2的代码来加载表。在删除任何行以产生图1的结果之前，我们从列表1的视图进行查询。

现在我们用列表4所示的代码删除了数据行的前一半，再一次的，删除操作是在一个打开的事务中完成的。

BEGIN TRANSACTION
 
DELETE DBO.FragTest
 WHERE PKCol <= 20000 / 2;

列表4：删除前一半的数据行

前一半的数据行被删除之后我们从列表1的视图进行查询，暴露了如涂所示的图片：

{图片4未上传}

图4：一半条目被删除之后的索引

再一次的，大约5000个索引条目被幽灵化，而其余的被移除，因为移除的条目是连续的，整个页便会变为空从而被释放。因而页的数量会便少而其每页的平均页覆盖率保持较高的水平。

当我们提交了这个事务，再从列表1的视图中进行查询的时候，我们得到了图5所示的结果：

{图5未上传}

图5：事务被提交之后的索引:

这些页处于索引的开始位置，那些所有的条目都被移除的页已经被释放并且不再是索引的一部分。一个处于索引中间位置的页，会包含一些高键值的待删除行，这些数据行将会被删除。而其他也不包含待删除的数据并会保持其页的充满。

为什么有一个幽灵记录仍然存在？因为叶子层级的第一个页的地址和根节点的地址一样，都会存储在系统的元数据表中，因此，一旦重新分配，这两个页绝不会被取消分配。幽灵清理线程小小照顾，在第一页留下一个幽灵记录，以保证那个页不会被取消。

非叶子层级的条目总是被立即移除而不是被幽灵化，如果删除非叶层级的条目导致它的页变为空，那么这个页便会被释放，而处于更高层的指针页会被一并删除。

就像刚才提到的那样，根节点不遵从这个模式，它从来不会被删除，即使整个索引已经完全为空了，那个根节点也会保留下来。对于每个索引来说，肯定有一个最高层的页，那个页便是根节点页。

更新

当表中的一行数据被更新时，索引条目或许会需要更新。对于每个索引条目，SQL SERVER会将这个更新要么作为一个原地更新，要么作为一个被INSERT紧随着的DELETE。每当可能的时候，SQL SERVER都会使用一个原地更新的方式。然而有一些情况不能使用原地更新的方式，那么SQL SERVER将不得不将这个更新作为一个“删除之后跟着的插入”操作。这里有一个通常原因的列表：

这个UPDATE语句会更新索引键列，导致条目在索引中会重新定位。

这个UPDATE会更新一个可变长度的列，导致这个条目不再适合在这个页上。

在表上定义了一个DMLAQ触发器。

如果被更新的列是索引键的一部分，那么在索引中条目的位置便会发生变化。条目肯定会被从它的旧的位置移除并基于索引键顺序插入索引中新的位置，在大部分情况下，这个UPDATE会被作为一个DELETE之后的 INSERT而完成。如果新的位置和旧的位置都处于同一个页，那么这个UPDATE会成为一个原地更新而完成，SQL SERVER会从根节点到叶子节点遍历两次，一次为了找到条目的当前版本，另一次为了找到新版本的正确的位置。

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

如果正在修改的列是索引的非键列，索引的条目位置将保持不变。然而条目的大小或许不会保持不变。如果这个页对于新版本的条目来说还具有足够的空间，这个UPDATE便会以一个 DELETE之后的INSERT来完成。

合并

MERGE操作在SQL SERVER 2008被引入，它功能强大，稳定，并且有益。在这个知识范围内，MERGE产生了INSERT, UPDATE, DELETE语句。执行MERGE语句对你的索引产生的影响和你手动执行INSERT, UPDATE, DELETE产生的影响相同。因为这个原因，MERGE语句不需要额外的知识来讲解。

一次性索引更新

当一个数据更新语句插入，删除，更新一个单独的数据行时，SQL SERVER必须对这个数据行做出更新，然后更新各个索引以反映这个变化。但是当一个数据更新语句插入，删除，更新多个数据行时，SQL SERVER会有两个选择：

对于每一个行，它将应用这个更新并更新各个索引。

或者：

对于每一行来说，它将应用这个更改，然后，对于每个索引来说，它将更改信息添加到一个待更改的列表中，而不是实际进行更改。一旦所有的数据行都已经被处理，每个索引的待更新的列表便会排序为各个索引序列，然后应用到各个索引之中。

第二种技术称为“一次性索引”更新（index-at-a-time ）或者宽更新（wide updating），它是INSERT, UPDATE, DELETE操作的一个选择。

SQL SERVER 查询优化器将决定是否一个宽更新将会提升性能。表的更新的数据行的百分比越大，宽更新便更有可能是被选择的技术。

为了演示这个，我们创建一个与之前用过的表类似的表，但它有两个索引，如列表4所示：

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

列表4：测试表

我们向表中插入一个单独的数据，如列表5所示，并检查它的执行计划。

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

列表5：单独的数据行插入

这个执行计划，如图6所示，显示这个对于表数据的插入是作为一个过程处理。但是它并没有显示更新索引的单独的过程。这是因为，在这个例子中，索引的更新是作为数据行插入过程的一部分来完成的。

{图片6未上传}

图6：单行数据插入执行计划

然而，当我们执行一个插入语句，如果其涉及了大量的数据行我们或许会得到一个不同的计划。

为了演示这个，我们用20000行数据来加载我们的原始表，如列表2所示，然后使用列表6所示的语句将这些数据行插入我们的新表：

INSERT dbo.FragTestII
  SELECT PKCol, InfoCol
  FROM dbo.FragTest;

列表6：多行数据插入

最终产生的执行计划，如图7所示，包含了许多操作因子，其中之一便是插入数据行到新表，紧跟着是两个排序，每一个排序都紧跟着将条目插入到索引中。

{图7未上传}

图7：多行数据插入执行计划

虽然这是更复杂的执行计划，将待更改的条目进行排序，并且将各个索引单独更新，是更高效的计划，并且，因为索引条目被以索引键序列来添加，每个索引将比条目随机插入包含更少的碎片。

结论

将条目插入索引会导致三种碎片模式中的一个，这取决于插入条目的顺序。

从一个索引（包含聚集索引）中删除条目，或许不会立即从索引中移除条目。相反的，它会创建幽灵化记录以将这些条目标记为逻辑删除，幽灵化仅仅发生在叶子层级。SQL SERVER将在后续某个时刻移除幽灵记录，但不会等到它们的事务被完成。

索引条目的更新会被作为一个原地更新或者是被INSERT跟随的DELETE来完成。如果底层表没有一个DML触发器，并且如果这个UPDATE不会重新分配也不会增加索引条目的大小，那么一个原地更新通常会发生。

如果索引更新语句将影响大量的数据行，SQL SERVER会选择一个“一次性索引”更新（index-at-a-time UPDATE）,首先更新表，然后给各个索引分别以各自的索引键序列应用更新。

【完结】

posted on 2019-04-21 20:48 钱行慕阅读(1332) 评论(0) 编辑收藏举报

刷新页面返回顶部

钱行慕

导航

公告

【译】索引进阶（十五）：插入，更新，删除索引