SQL Server索引进阶第十一篇：索引碎片分析与解决（上）

来源：互联网发布：幼儿算术手指算法视频编辑：程序博客网时间：2024/05/22 10:42

AgileSharp数据库优化索引优化索引碎片

索引设计是数据库设计中比较重要的一个环节，对数据库的性能其中至关重要的作用，但是索引的设计却又不是那么容易的事情，性能也不是那么轻易就获取到的，很多的技术人员因为不恰当的创建索引，最后使得其效果适得其反，可以说“成也索引，败也索引”。

本系列文章来自Stairway to SQL Server Indexes，然后经过我们团队的理解和整理发布在agilesharp，希望对广大的技术朋友在如何使用索引上有所帮助。

相关有关索引碎片的问题，大家应该是听过不少，也许也很多的朋友已经做了与之相关的工作。那我们今天就来看看这个问题。

为了更好的说明这个问题，我们首先来普及一些背景知识。

知识普及

我们都知道，数据库中的每一个表要么是堆表，要么就是包含聚集索引的表，或者我们称之为有序表。如果表是一个堆表，那么在使用非聚集索引查询数据的时候，会使用书签查找去底层的数据表中去检索需要的数据，这个书签查找会通过每一个索引中包含的行标识（RID）去定位每一个底层数据表的数据行。如果表上面有聚集索引，那么在使用非聚集索引查找其他需要数据的时候，就会使用聚集索引键去定位底层的数据行。

我们也知道，索引是由索引页组成的，索引中的每一个条目包含在页中。每8个页组成一个块。

索引的层级是从底向上的，就是一个树结构，最下面的就是第0层，也是叶节点。索引中的根节点处于整个索引的最上层。

如果要扫描整个索引，那么就意味着必须要读取页节点中的每一个页（要么是数据页，要么是索引页）。其中，每个页都包含着一个指向它前面的页和一个指向它后面也的指针。之前，我们也提过：如果单看某一层节点，其实就是一个双向链表。还是上个图，大家感受一下。

我们应该知道：页（不管是数据页，还是索引页，还是其他的类型的页）处于的逻辑顺序和它的物理顺便不一定就是一样的，也就说，在A页中的指针指向了它的下一个页B，也就说A和B页在逻辑上面是一起的，但是它们在物理上面可能不一样，甚至B页和A页在物理上相隔几百个页。

如果在逻辑上面相连的页在物理存储级别相隔的越近，那么在读取这些页的时候所花的I/O成本也就越小，因为产生磁盘的磁头移动带来的延迟。相反，如果他们的物理存储顺序和逻辑顺序一致，那么SQL Server在读取的时候，就可以一次读取，因为每次会读取一个块（8个页）。

好了，普及知识之后，我们就来看看什么是碎片。

什么是索引碎片

索引碎片可以分为两类：内部索引碎片和外部索引碎片。下面我们就来具体的看看而这之前的区别以及如何检查。

内部索引碎片

每一个索引页中都包含一些索引的条目（就类似数据页包含很多的数据行一行），这一点我们在之前讲过了的。但是，很多的时候，不是每个页都包含了最大的条数。例如，一个页的大小8k，也就是4096字节，除去一些页头，页脚等，还剩下8000多字节，如果每个索引条目的大小事100字节，那么这个索引页最大就可以包含80个条目，但是很多的情况下，却没有包含这么多。

也就说，很多的时候，索引页并没有完全的填满，或者这是问题，或许这么我们特意这样的，我们后续会提到。当我们谈到索引碎片的时候，我们往往就是指这些索引页没有完全填满。或者说的更加明白一点就是：我们原本是希望页都被填满的，但是随着数据的增删改，使得索引中的数据没有填满，结果如下：

Index_Page_after_delete.png(26.79 K)

9/9/2012 11:19:53 AM

图不是很清晰，大家意会一下就行了。

我们可以使用
sys.dm_db_index_physical_stats来查看相关的内部碎片的情况，执行查询如下：

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 (%)'
FROM sys.dm_db_index_physical_stats(
DB_ID(),
OBJECT_ID('Sales.SalesOrderDetail'),
DEFAULT, DEFAULT, 'DETAILED') PS
JOIN sys.indexes IX
ON IX.OBJECT_ID = PS.OBJECT_ID AND IX.index_id = PS.index_id

复制代码

执行结果如图：

20120904184258.png(56.68 K)

9/9/2012 11:19:53 AM

我们可以看到每个索引的页面的填充情况。

下面，我们再来讲讲外部索引碎片。

外部索引碎片

理解了上面的问题，这个外部索引碎片就好理解了，最简单的说法就是：索引中的索引页的逻辑顺序和物理顺序不一致。我们通过个图对比的来看看。

9865.jpg(95.72 K)

9/9/2012 11:19:53 AM

在上图中，一个索引包含了16个页。但是这16页不是包含在2个相连的块中的，而是分布在不同的地方，因为它们之前中的一些块被其他的对象占用了。这样就导致了16个页在物理上面不连续，这就是碎片。在读取的时候，就会消耗额外的I/O。

和之前一样，我们可以使用
sys.dm_db_index_physical_stats来查看外部碎片的情况。但是这里的参数值可能要发生变化了：之前在sys.dm_db_index_physical_stats最后一个参数值是'DETAILED'，这里我们的值是LIMITED或者Default。因为外部碎片关注的是索引页之前的连续性问题，不关注每一个页中的数据，此时只是部分的扫描，没有必要全部的扫描。大家可以参看MSDN的去进一步的理解这些参数的含义。

查询如下：

SELECT IX.name AS 'Name'
, PS.index_level AS 'Level'
, PS.page_count AS 'Pages'
, 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('Sales.SalesOrderDetail'),
DEFAULT, DEFAULT, 'LIMITED') PS
JOIN sys.indexes IX
ON IX.OBJECT_ID = PS.OBJECT_ID AND IX.index_id = PS.index_id

复制代码

结果如下：

20120904184512.png(30.11 K)

9/9/2012 11:19:53 AM

除了使用脚本之外，我们还可以在SQL Server管理器中查看，在某个索引上面右键，属性，如下：

9869.jpg(87.17 K)

9/9/2012 11:19:53 AM

好，我们下一篇文章就来进一步的谈谈一下的内容：

是什么导致了碎片

如何解决碎片问题

后文更加精彩！！！