SQL Server索引--管理及维护（碎片及填充因子）

　　由于索引中的数据按序存储，所以随着时间的推移，由于页拆分或值更改，将引起数据值在索引内部的移动。为了随着管理索引碎片，需要定期执行维护任务。

    一、索引碎片

　　由于重复的写操作，随着时间推移，操作系统上的文件会产生碎片。虽然索引也会产生碎片，单它与文件碎片略有不同。索引建立之后，所有的索引键值按序写入索引内的页面。

　　如果表中删除了某行，则索引中相应的条目也要删除。该过程将在索引页上形成一个“空洞”。而SQL SERVER并不回收这样的空间，因为查找并重新使用一个“空洞”的代价过高。

　　如果索引所基于的表中某个值发生了改变，SQL SERVER必须将相应的条目移动到正确的位置，同样此操作也会留下“空洞”。当索引页被填满，需要进行页拆分时，您就“获得”了附加的索引碎片。 随着时间的推移，一张表上经历了许多变更，大量数据被更改，此时，索引中将出现碎片。

    二、填充因子

　　为控制索引的碎片现象，可以采用一种所谓的“填充因子”（fall factor）的选项。提供填充因子选项是为了优化索引数据存储和性能。

　　当创建或重新生成（重建）索引时，填充因子的值可确定每个叶级页上要填充数据的空间百分比，以便在每一页上保留一些剩余空间作为以后扩展索引的可用空间。

　　例如，指定填充因子的值为 80 表示每个叶级页上将有 20% 的空间保留为空，以便随着向基础表中添加数据而为扩展索引提供空间。在索引行之间保留可用空间，而不是在索引的末尾保留。

　　填充因子的值是 1 到 100 之间的百分比，服务器范围的默认值为 0，这表示将完全填充叶级页。

　　只有在创建或重新生成了索引后，才会应用填充因子设置。SQL Server 数据库引擎并不会在页中动态保持指定的可用空间百分比。如果试图在数据页上保持额外的空间，将有背于使用填充因子的本意，因为随着数据的输入，数据库引擎将不得不在每一页上进行页拆分，以保持填充因子所指定的可用空间百分比。

　　2.1性能注意事项--页拆分

　　正确选择填充因子值可提供足够的空间，以便随着向基础表中添加数据而扩展索引，从而降低页拆分的可能性。如果向已满的索引页添加新行，数据库引擎将把大约一半的行移到新页中，以便为该新行腾出空间。这种重组称为页拆分。页拆分可为新记录腾出空间，但是执行页拆分可能需要花费一定的时间，此操作会消耗大量资源。此外，它还可能造成碎片，从而导致 I/O 操作增加。如果经常发生页拆分，可通过使用新的或现有的填充因子值来重新生成索引，从而重新分发数据。

　　虽然采用较小的非零填充因子值可减少随着索引的增长而拆分页的需求，但是索引将需要更多的存储空间，并且会降低读取性能。即使对于面向许多插入和更新操作的应用程序，数据库的读取次数一般也会超过数据库写入次数的 5 到 10 倍。因此，指定一个不同于默认值的填充因子会降低数据库的读取性能，而降低量与填充因子设置的值成反比。例如，当填充因子的值为 50 时，数据库的读取性能会降低两倍。读取性能降低是因为索引包含较多的页，因此增加了检索数据所需的磁盘 I/O 操作。

　　2.2将数据添加到表的末尾

　　如果新数据在表中均匀分布，则不是 0 或 100 的非零填充因子对性能有利。但是，如果所有数据都添加到表的末尾，则不会填充索引页中的可用空间。例如，如果索引键列是 IDENTITY 列，则新行的键将总是增加，并且索引行在逻辑意义上将添加到索引的末尾。如果将用加长行的大小的数据来更新现有行，则请使用小于 100 的填充因子。每页上的额外字节将有助于把行中的额外长度造成的页拆分降低到最小限度。

 　　三、消除索引碎片

　　无论何时对基础数据执行插入、更新或删除操作，SQL Server 数据库引擎都会自动维护索引。随着时间的推移，这些修改可能会导致索引中的信息分散在数据库中（含有碎片）。当索引包含的页中的逻辑排序（基于键值）与数据文件中的物理排序不匹配时，就存在碎片。碎片非常多的索引可能会降低查询性能，导致应用程序响应缓慢。您可以通过重新组织索引或重新生成索引来修复索引碎片。决定使用哪种碎片整理方法的第一步是分析索引以确定碎片程度。

avg_fragmentation_in_percent 值修复语句

> 5% 且 < = 30%

ALTER INDEX REORGANIZE

> 30%

ALTER INDEX REBUILD WITH (ONLINE = ON)*

　　重新生成索引可以联机执行，也可以脱机执行。重新组织索引始终联机执行。若要获得与重新组织选项相似的可用性，应联机重新生成索引。这些值提供了一个大致指导原则，用于确定应在 ALTER INDEX REORGANIZE 和 ALTER INDEX REBUILD 之间进行切换的点。不过，实际值可能会随情况而变化。必须要通过试验来确定最适合您环境的阈值。

　　非常低的碎片级别（小于 5%）不应通过这些命令来解决，因为删除如此少量的碎片所获得的收益始终远低于重新组织或重新生成索引的开销

　　四、重新组织索引

　　若要重新组织一个或多个索引，可以使用带 REORGANIZE 子句的 ALTER INDEX 语句。重新组织索引是通过对叶页进行物理重新排序，使其与叶节点的逻辑顺序（从左到右）相匹配，从而对表或视图的聚集索引和非聚集索引的叶级别进行碎片整理。使页有序可以提高索引扫描的性能。索引在分配给它的现有页内重新组织，而不会分配新页。如果索引跨多个文件，将一次重新组织一个文件，不会在文件之间迁移页。

　　重新组织还会压缩索引页。如果还有可用的磁盘空间，将删除此压缩过程中生成的所有空页。压缩基于 sys.indexes 目录视图中的填充因子值。重新组织进程使用最少的系统资源。而且，重新组织是自动联机执行的。该进程不持有长期阻塞锁，所以不会阻止运行查询或更新。

　　索引碎片不太多时，可以重新组织索引。不过，如果索引碎片非常多，重新生成索引则可以获得更好的结果。

　　五、重新生成索引

　　重新生成索引将删除该索引并创建一个新索引。此过程中将删除碎片，通过使用指定的或现有的填充因子设置压缩页来回收磁盘空间，并在连续页中对索引行重新排序（根据需要分配新页）。这样可以减少获取所请求数据所需的页读取数，从而提高磁盘性能。

　　A. 重新生成索引

以下示例将重新生成单个索引。

1 ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
2 REBUILD;
View Code

　　B. 重新生成表的所有索引并指定选项

下面的示例指定了 ALL 关键字。这将重新生成与表相关联的所有索引。其中指定了三个选项。

1 ALTER INDEX ALL ON Production.Product
2 REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
3               STATISTICS_NORECOMPUTE = ON);
View Code

　　C. 通过 LOB 压缩重新组织索引

下面的示例重新组织单个聚集索引。因为该索引在叶级别包含 LOB 数据类型，所以该语句还会压缩所有包含该大型对象数据的页。请注意，不必指定 WITH (LOB_Compaction) 选项，因为默认值为 ON。

1 ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto
2 REORGANIZE ;
View Code

 

 

 

posted @ 2019-01-22 10:10  ywwzhy  阅读(627)  评论(0)  编辑  收藏  举报