SQLServer性能调优3之索引（Index）的维护 - 数据库编程

前言

前一篇的文章介绍了通过建立索引来提高数据库的查询性能，这其实只是个开始。后续如果缺少适当的维护，你先前建立的索引甚至会成为拖累，成为数据库性能的下降的帮凶。

查找碎片

消除碎片可能是索引维护最常规的任务，微软官方给出的建议是当碎片等级为 5% - 30% 之间时采用 REORGANIZE 来“重整”索引，如果达到 30% 以上则使用 REBUILD 来“重建”索引。决定采用何种手段和操作时机可能需要考虑许多的因素，以下4条是你必须要考虑的：

备份的计划服务器的负载磁盘剩余空间回复(Recovery) 模型

PS：虽然碎片与性能紧密相关，但某些特定情况下他可以被忽略。比如你有一张带有聚集索引的表，几乎所有针对该表的处理仅仅是根据主键取出一条数据。该场合下碎片的影响可以忽略不计。

那么怎样确定某个索引的碎片状况呢？使用系统函数sys.dm_db_index_physical_stats 及系统目录 sys.Indexes，示例脚本如下：

-- 获取指定表（示例：ordDemo）上所有索引的信息
SELECT
  sysin.name as IndexName
  ,sysIn.index_id
  ,func.avg_fragmentation_in_percent
  ,func.index_type_desc as IndexType
  ,func.page_count
FROM
  sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'ordDemo'), NULL, NULL, NULL) AS func
JOIN
  sys.indexes AS sysIn
ON
  func.object_id = sysIn.object_id AND func.index_id = sysIn.index_id

-- 聚集索引的 Index_id 为 1
-- 非聚集索引为 Index_id>1
-- 以下脚本用 WHERE 子句进行了筛选（剔除了没有索引的表）
-- 该脚本返回数据库所有的索引，可能花费较长时间！
SELECT
  sysin.name as IndexName
  ,sysIn.index_id
  ,func.avg_fragmentation_in_percent
  ,func.index_type_desc as IndexType
  ,func.page_count
FROM
  sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS func
JOIN
  sys.indexes AS sysIn
ON
  func.object_id = sysIn.object_id AND func.index_id = sysIn.index_id
WHERE sysIn.index_id>0;

输出截图如下

示例数据库的碎片为0，这是因为碎片是在执行增删改时产生的，我们的数据库还没有做过类似操作。

填充因子

前面提到过数据以8KB 数据页的方式存放在数据库中，假设你有一张建立了聚集索引的表，每当有数据插入时，数据库会根据主键找到插入位置（数据页）并写入信息。如果该数据页已经满了或者不够空间存放新的数据，数据库会建立一个新的8KB 数据页，而这个新建的过程会造成I/O消耗。

填充因子用来减少这种情况的发生，如果你设定填充因子为10，那么你的数据初始仅使用8KB 数据页中的10%，当插入新纪录时基本不用担心会发生多余的I/O消耗，因为数据页中预留了90%的空间。

填充因子也是把双刃剑，他在增加写操作性能的同时，降低了读操作的性能。

【填充因子仅当建立索引或重建（rebuildi）索引时起作用，对于一般的DML操作无效（数据页总是填充到100%）】

以下脚本帮助你了解索引的填充因子值：

SELECT
  OBJECT_NAME(OBJECT_ID) AS TableName
  ,Name as IndexName
  ,Type_Desc
  ,Fill_Factor
FROM
  sys.indexes
WHERE
  -- 这里通过WHERE筛选来仅仅表示聚集索引和非聚集索引
  type_desc<>'HEAP'

你还可以查看数据服务器上默认的填充因子值：

SELECT
  Description
  ,Value_in_use
FROM
  sys.configurations
WHERE
  Name ='fill factor (%)'

PS：0表示不保留任何预留空间。

通过以下脚本来设置填充因子的值：

ALTER INDEX [idx_refno] ON [ordDemo]
REBUILD WITH (FILLFACTOR= 80)
GO

-- 如果要设定服务器上的默认值，使用以下脚本
Sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO

sp_configure 'fill factor', 90
GO
RECONFIGURE
GO

在一张静态表（偶然更新）的表上建议采用较大的填充因子（90%以上），在读写频繁的表上建议采用较低的填充因子（70% - 80%）。特别的，当你的聚集索引建立在一个自增字段上时，设定填充因子为100%也没有问题，因为新插入的数据总是在所有数据的最后，不会发生插入记录与记录之间的情况。

重建（REBUILD）索引来提高索引效率

重建索引的作用顾名思义，他带来的好处包括消除碎片，统计值(statistics)更新，数据页中物理排序顺序的对齐。另外他还会根据填充因子来压缩数据页，（如果必要的话）新增数据页。好处一箩筐，只是这个操作非常耗资源，会花费相当长的时间。如果你决定开始重建索引，你还需要知道他有两种工作模式：

离线模式：这是默认的重建索引模式，它将锁定表直到重建完成。如果表很大，会导致用户（好几个小时都）无法使用该表。相比在线模式来说离线模式工作更快，消耗的TempDb的空间更小。

在线模式：如果客观条件不允许你锁定表，你就只能选择在线模式，这将耗费更多的时间和服务器资源。值得一提的是如果你的表包含了varchar (max), nvarchar (max), text 类型字段的话，将无法在该模式下进行重建索引。

【提示：该模式选择仅在开发版/企业版中支持，其他版本默认使用离线模式！】

以下是重建索引的示例脚本：

-- 在线模式下重建索引 idx_refno
ALTER INDEX [idx_refno] ON [ordDemo]
REBUILD WITH (FILLFACTOR=80, ONLINE=ON)
GO

-- 离线模式下重建索引 idx_refno
ALTER INDEX [idx_refno] ON [ordDemo]
REBUILD WITH (FILLFACTOR=80, ONLINE=OFF)
GO

-- 重建 ordDemo 表上的所有索引
ALTER INDEX ALL ON [ordDemo]
REBUILD WITH (FILLFACTOR=80, ONLINE=OFF)
GO

-- 重建索引 idx_reno （DROP_EXISTING=ON）
CREATE CLUSTERED I

SQLServer性能调优3之索引（Index）的维护(一)

前言

查找碎片

填充因子

重建（REBUILD）索引来提高索引效率