第十章——维护索引（2）——填充因子

前言：

       在第九章中，已经介绍了如何使用索引，当一个索引创建时，以B-Tree格式存放数据，拥有根节点、中间节点、叶子节点。叶子节点是最底层的节点，在聚集索引中，包含了实际数据，而每个数据页有8KB。

      当表中的数据的增删改发生时，会尝试把数据插入到合适的数据页中。比如有一个聚集索引在SSN上，当插入一个新的SSN数时。SQLServer会尝试把数据插入到合适的数据页，假设SSN从2开始，此时在最后的数据页中找到这个页面是以SSN开始的，SQLServer将会仅仅在这个页中插入新行。如果你的8K页面已经满了，或者没有足够的空间来存放新的SSN，此时就会发生分页，数据页会一分为二，或者分成更多的页，分页的数量根据新数据的大小。如果新数据不大，那么新数据将只会使数据页拆分成两个，且每个页只有一半是有数据的。否则，将要动用更多的页。SQLServer会尝试在同一级别的数据页中，优先从右边插入新数据。

      当有足够的空间存放新数据时，进程不需要等待页面拆分和I/O完成。这里有一个很重要的开销，页面拆分会锁住整个页，拒绝其他操作使用这个页，直到进程完成。

      此时，填充因子就成为关注点。填充因子决定你的页在初始化情况。假设你填10，那么你的数据页将在10%填满的时候就会分页。当你超过10%时，会保持90%的页面空余，然后创建新页给其他数据。

      现在，当你插入新数据时，不用担心由于也分裂导致I/O开销，因为你有90%的空间用于存放新数据，所以如果你的填充因子是一个小值，可以降低由于页分裂而造成的I/O开销，有助于你更快地写数据。

      总之，填充因子是一个双刃剑，合理的使用能提高性能。对写入操作的性能提升往往会对读取操作的性能有降低效果。填充因子仅仅在创建或重建一个索引时有影响，在日常的DML操作中，页依然会100%填满。

 

准备工作：

在学习索引的填充因子如何设置钱，有必要找出当前的填充因子值，以便决定当前的填充因子是否适用和应该使用什么新的值来代替，这些需要根据实际环境而定。这里有两部分需要检查的：

1、  sys.indexes：这个目录视图可以用于查看当前填充因子：

--查看索引的填充因子
SELECT  OBJECT_NAME(object_id) AS TableName ,
        name AS IndexName ,
        type_desc ,
        fill_factor
FROM    sys.indexes
WHERE   type_desc <> 'HEAP'

 

2、  sys.configurations：这个目录视图可以用于查看服务器上的默认填充因子的值：

SELECT  [Description] ,
        value_in_use
FROM    sys.configurations
WHERE   name = 'fill factor (%)'

 

 

如果默认值为0，代表不会保留任何空余空间在页上。

 

分析：

当你决定填充因子需要改变的时候，可以使用以下脚本来更改：

--把填充因子修改成%
ALTER INDEX [idx_refno] ON [dbo].[ordDemo]
REBUILD WITH (FILLFACTOR=80)
GO
--修改服务器的默认填充因子的值：
sp_configure 'show advanced options',1
GO
RECONFIGURE
GO
--改成%
sp_configure 'fill factor',90
GO
RECONFIGURE
GO

 

        当填充因子为90的时候，在数据页初始化的时候，会有90%的空间初始化填满，只保留10%的空间供以后使用，如果执行DML命令时，会找到空间给新数据插入，从而避免分页的I/O开销。

 

扩展信息：

        对于静态表，高的填充因子（90~100%）会比较好，对于那些需要同时更新的表，或者一直在增长的表同样有效。如果高频率更新的表，使用较低的值会比较好，如70~80%。

        如果表上自增列有聚集索引，把填充因子设为100%没有任何问题，因为每个数据都会插到最后一页的最后一行。不会存在在中间插入数据的情况。