【译】索引进阶（十二）：SQL SERVER中的索引碎片【中篇】

原文链接：传送门。 

 

 为了讨论碎片产生的原因，以及避免和移除索引碎片的技术，我们必须从本进阶系列后续将介绍的两个章节借用一些知识点：创建/更新索引的知识，以及向一个索引表插入数据行的相关知识。

当我们讲解这些信息的时候，记住本节背景部分提到的内容，仅当你的应用是进行索引扫描时，碎片是不被期望的（不管是索引的完全扫描还是部分扫描）。当你的应用程序仅仅返回一个索引键值的少量数目的条目时，碎片其实并不是个问题。如同列表3所示的5行数据的查询。

SELECT *
FROM Sales.SalesOrderDetail
WHERE SalesOrderID = 56080;
GO

  列表3：一个高选择性的查询

 当你在一个已经填充了数据行的表上创建或者重建索引的时候，会发生磁盘空间的重分配，导致了一几乎不包含外部碎片的索引。内部碎片的数值取决于你为 FILLFACTOR选项指定的值，它决定了页的覆盖率。在后续的数据更新中，SQL SERVER不会做任何努力来包含这种页覆盖率，随着数据行被插入表，结果条目会被插入索引页中，这些页会变得更加饱和。

最后SQL SERVER会尝试向已经充满的也插入一个条目，当这发生时，SQL SERVER将搜索它的分配架构来找到一个空的页，一旦SQL SERVER找到一个空页，它会做三件事中的一个，这决定于新的索引键被插入的顺序。

随机序列：通常SQL SERVER从一个充满的也移动一半的条目到空的页，然后将新条目插入到合适的页中，因此产生了两个半满的页，这种技术称之为页的分割，同时也是这种类型的索引被称之为平衡树索引的原因。因为有很小的可能性新的页会在物理上紧邻着旧的页，这会导致外部碎片的增加，如果你的程序不断的插入行而不删除行，这两个半满的页又会充满，然后又被分割为两个半满的页，一段时间之后，每个页都在半满与充满之间循环，一次又一次的，导致了内部碎片（平均也覆盖率）大约为75%。

升序：然而，如果SQL SERVER注意到新的条目是满页的最后一个条目，它便会假定数据行是以和索引一样的顺序插入表的，在这种模式下，如果索引是建立在LastName 列上，一个“Kimmel”条目被放置在页的末尾，SQL SERVER推测“Kimmel” 条目会被另一个“Kimmel”条目跟着，或许是一个“Kinder”行，因此它将新行，并且仅仅是新行放置在空的页中。当插入开始时，如果表是空的，不管逻辑上还是物理上，新页是索引的最后一个页。因此外部碎片保持为0，如果SQL SERVER的假定是正确的，数据行以索引键的顺到达，一个充满页不会被分割，一旦一个页充满了，它就会一直充满，导致了很少的或者没有内部碎片。

降序：相反的，如果SQL SERVER注意到新条目是充满页的第一个条目，它便会假设数据行是以降序的方式被插入数据表的，在这种情况下， SQL SERVER假定“Kimmel”行被另一个“Kimmel”行或者“Kimato”行跟着，又一次的，它将新条目，并进将新条目放置在空页中，外部碎片的增长与随机序列模式是一样的，而内部碎片是与升序模式一样，几乎是100%。

当一个新条目被放置在新页中，一些清理工作会被进行。四个上一页，下一页指针分布在三个页中，它们必须被更新，并且一个指向新页的条目会被插入更改级别的索引层级中，而这反过来会导致那个层级的页分割。

因此，虽然页分割是有益的，它们能够保持索引的平衡，而不必要的页分割还是需要被避免的，这个主题本节后续会进行讲解。

我们可以用三个简单的示例来验证以上提到的三种行为，每一个都会用到一个两列的，并且在其中一个列上具有索引的表，你可以运行列表4的代码来创建它。

USE AdventureWorks;
GO
CREATE TABLE dbo.FragTest
( PKCol    INT NOT NULL
, InfoCol  NCHAR(64) NOT NULL
, CONSTRAINT PK_FragTest_PKCol PRIMARY KEY NONCLUSTERED (PKCol) 
);
GO

  列表4：带有一个索引的简单表
在各个例子中，我们以三种可能的顺序中的一种向表中插入50000行数据：随机，升序，降序。在每个加载的结尾，我们使用列表5的查询来查看碎片信息：

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.avg_fragmentation_in_percent AS 'External Fragmentation (%)'
, PS.fragment_count AS 'Fragments'
, PS.avg_fragment_size_in_pages AS 'Avg Fragment Size'
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

    列表5：碎片查询

是否具有非聚集索引无关紧要，如果是一个聚集索引，那么结果是类似的。

随机插入行

对于我们的第一个例子来说，我们使用列表6显示的技术，插入具有随机索引键值的50000行数据，然后查看结果碎片，如图6所示。

TRUNCATE TABLE dbo.FragTest;
GO
DECLARE @limit INT;    SET @limit = 50000;
DECLARE @counter INT;  SET @counter = 1;
DECLARE @key INT;
SET NOCOUNT ON;WHILE @counter <= @limit
BEGIN
SET @key = CONVERT(INT, (RAND() * 1000000));
BEGIN TRY
INSERT dbo.FragTest VALUES (@key, 'AAAA');
SET @counter = @counter + 1;
END TRY
BEGIN CATCH
END CATCH;
END;
GO

   列表6：随机序列插入

随机插入的碎片结果，如图6所示，是我们所期望的结果。输出结果告诉我们平均的叶子节点页稍微少于75%的覆盖率，它同时也告诉我们这个索引是完全碎片化的，也就是说，每个页都有自己的碎片，意味着没有一个下一页指针指向物理上紧邻的页。

   图6：随机插入导致的碎片

 

以升序插入行

下一步，我们更改我们的批处理使得其以递增的索引键值来插入行，如同列表7所示：

TRUNCATE TABLE dbo.FragTest;
GO
DECLARE @limit INT;    SET @limit = 50000;
DECLARE @counter INT;  SET @counter = 1;
SET NOCOUNT ON;
WHILE @counter <= @limit
BEGIN
BEGIN TRY
INSERT dbo.FragTest VALUES (@counter, 'AAAA');
SET @counter = @counter + 1;
END TRY
BEGIN CATCH
END CATCH;
END;
GO

    列表7：升序插入

插入数据行之后，我们再一次运行列表5的查询，得到了图7的结果。表明页被很好的组织起来。并且其外部碎片几乎为0，SQL SERVER 通过读取一个区便可以扫描整个索引，设置一次IO可以读取多个区，而那些IO可以通过预读完成。

  图7：升序插入的碎片

以降序插入行

接下来，我们更新我们的批处理命令，使其以递减的索引键值插入行，如列表8所示：

TRUNCATE TABLE dbo.FragTest;
GO
DECLARE @limit INT;    SET @limit = 50000;
DECLARE @counter INT;  SET @counter = 1;
SET NOCOUNT ON;
WHILE @counter <= @limit
BEGIN
BEGIN TRY
INSERT dbo.FragTest VALUES (@limit - @counter, 'AAAA');
SET @counter = @counter + 1;
END TRY
BEGIN CATCH
END CATCH;
END;
GO

 列表8：递减的序列插入

再一次的，在插入数据之后，我们允许列表5的查询，并且检查结果，显示在图8：

  图8：递减的序列插入时候的碎片

我们可以看到也是充满的，但是文件是完全碎片化的。这后者事实稍微有点误解，因为索引的页是连续的，但是索引序列上的第一页是物理文件上的最后一个页，每一个下一页指针指向物理上的前一个页，因此导致了索引的高外部碎片率。

降序插入是很少见的，通常是从外部数据源通过日期或者金额以降序的方式请求数据的结果。

每当你参加一个降序插入的模式时，确保在创建索引的时候指定DESC关键字，如同列表9所示：

CREATE INDEX IX_SalesOrderHeader_OrderDate ON Sales.SalesOrderHeader
(
OrderDate DESC
, SalesOrderID
);
GO

  列表9：创建一个降序索引

 这将索引变成了一个升序插入模式的索引，因此减少了外部碎片问题。

 删除/更新数据行

因此数据的插入顺序，不管其是随机的，升序或者降序，都会影响索引的碎片，但是大部分的数据行不会永远的停留在数据表中，最后它们都会被删除，这也会影响索引的碎片。

更新也能够影响索引碎片，但仅仅是如下情况之一：

一个列被更新参与到索引键中。

或者：

更新导致了可变宽度列的大小增加，以致于索引键不再适合一个页大小。

在以上的情况，更新会被当做删除之后的再插入来对待。

因此，此刻我们需要查看通用的 删除/插入模式的组合以及它们导致的碎片。通过了解了这些模式，你便可以应用你的数据库索引和应用程序数据更新的知识来预期即将产生的碎片。对于任何索引来说，如果这个碎片是不可接受的，你便可以应用本章后续出现以及后续章节出现的知识来防止或者调整那个碎片。

 

本节我们介绍了碎片产生的原因，下一节我们将介绍如何防止产生碎片以及一些通用的碎片模式。