SQL Server索引的维护 - 索引碎片、填充因子 <第三篇>
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了SQL Server索引的维护 - 索引碎片、填充因子 <第三篇>，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含14058字，纯文字阅读大概需要21分钟。
内容图文
当数据库增长，页拆分，然后删除数据时，就会产生碎片。从增长的方面看，平衡树处理得很不错。但是对于删除方面，它并没有太大的作用。最终可能会出现这种情况，一个页上有一条记录，而另一个页上有几个记录。在这种情况下，一个页上保存的数据量只是它能够保存总数据量的一小部分。
1、碎片会造成空间的浪费，SQL Server每次会分配一个区段，如果一个页上只有一条记录，则仍然会分配整个区段。
2、散布在各处的数据会造成数据检索时的额外系统开销。为了获取需要的10行记录，SQL Server不是只加载一个页，而是可能必须加载10个页来获取相同的信息。并不只是读取行导致了这一结果，在读取行前，SQL Server必须先读取页。更多的页意味着更多的工作量。
但是碎片也不只是有坏处，比如一个插入非常频繁的表就很喜欢碎片，因为在插入数据时几乎不用担心页拆分的问题。所以大量的碎片意味着较差的读取性能，但也意味着极好的插入性能。
关于碎片的理解，找到了数据库牛人CareySon的这篇文章 T-SQL查询高级—SQL Server索引中的碎片和填充因子 ，在这里消化一下
碎片分两种，外部碎片和内部碎片
外部碎片：
外部碎片指的是页拆分而产生的碎片。如向表中插入一行，而这一行导致现有的页空间无法容纳新插入的行，则导致页拆分。
新的页不断随数据的增长而产生，而聚集索引要求行之间连续，所以如果聚集索引不是自增列，页拆分后和原来的页在磁盘上并不连续-这就是外部碎片。 由于页拆分，导致数据在页之间的移动，所以如果插入更新等操作经常需要分页，则会大大消耗IO资源，造成性能下降。 对于查找连说，在有特定搜索条件，如where子句有很细的限制或者返回无序结果集时，外部碎片并不会对性能产生影响。但如果要返回扫描聚集索引而且查找连续页面时，外部碎片就会产生性能上的影响。所以当要读取相同的数连续的数据时需要扫描更多的页，更多的区。而且连续数据不能预读，造成额外的物理读，增加磁盘IO。通常，外部碎片过多会造成频繁的区切换。
如果页面连续排序，预读功能可以提前读取页面而不需要太多的磁头移动。
内部碎片：
内部碎片是页拆分后，导致索引页的数据并不满，有空行。同样读取一个索引页，却只能拿到x%的数据。
--新建一张表
CREATE TABLE Person
(
Id int,
Name char(999),
Addr varchar(10)
)
--聚集索引
CREATE CLUSTERED INDEX CIX ON Person(Id)
--插入8条数据
DECLARE @var INT
SET @var=100
WHILE(@var < 900)
BEGIN
INSERT INTO Person(Id,Name,Addr)
VALUES(@var,‘xx‘,‘‘)
SET @var = @var+100
END
这个表每个行由int(4字节),char(999字节)和varchar(10字节组成），所以每行为1003个字节,则8行占用空间1003*8=8024字节加上一些内部开销，可以容纳在一个页面中。(原来这个表和数据搞得还挺巧的)。
执行查看语句：
SELECT page_count,avg_page_space_used_in_percent,record_count,avg_record_size_in_bytes,avg_fragmentation_in_percent,fragment_count
FROM sys.dm_db_index_physical_stats
(DB_ID(‘Nx‘),object_id(‘dbo.Person‘),NULL,NULL,‘sampled‘)
示例如下：
其中page_count是查看占用了多少个页，而第二个参数表示该页空间的使用率。因此从以上信息可以获得，这8条数据是放在一个页上，而且该页的空间使用率已经是百分之百了。
现在将其中一行的Addr改长一点：
UPDATE Person SET Addr = ‘广东广州‘ where Id = 100
则再执行检查索引语句：
可以看到，这个表已经有了两页，页面平均使用为50%左右。但是明显也造成了碎片，在列avg_fragmentation_in_percent上可以看到，碎片大约为50%。
页拆分后的示意图如下：
这个时候，继续插入数据，碎片会上升。在又插入了至达到48条记录后，碎片程度如下：
这个时候，执行一个查询计划，查看下IO性能：
可以看到I/O下降了不少。
二、元数据函数sys.dm_db_index_physical_stats分析碎片
SQL Server提供了一种特殊的元数据函数sys.dm_db_index_physical_stats，它有助于确定数据库中的页和区段有多满。然后用该信息作出一些维护数据库的决策。
该函数语法如下：
sys.dm_db_index_physical_stats(
{<database id> | NULL | 0 | DEFAULT},
{ <object id> | NULL | 0 | DEFAULT },
{ <index id> } | NULL | 0 | -1 | DEFAULT },
{ <partition no> | NULL | 0 | DEFAULT },
{ <mode> | NULL | DEFAULT }
)
下面假设从SmartScan中获取所有的索引信息：
DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N‘Nx‘);
SET @object_id = OBJECT_ID(N‘Account‘)
SELECT database_id,object_id,index_id,index_depth,avg_fragmentation_in_percent,page_count
FROM sys.dm_db_index_physical_stats(@db_id,@object_id,NULL,NULL,NULL);
下面看看统计信息的说明：
|
列名 |
数据类型 |
说明 | ||
|---|---|---|---|---|
|
database_id |
smallint |
表或视图的数据库 ID。 |
||
|
object_id |
int |
索引所在的表或视图的对象 ID。 |
||
|
index_id |
int |
索引的索引 ID。 0 = 堆。 |
||
|
partition_number |
int |
所属对象内从 1 开始的分区号；表、视图或索引。 1 = 未分区的索引或堆。 |
||
|
index_type_desc |
nvarchar(60) |
索引类型的说明： HEAP CLUSTERED INDEX NONCLUSTERED INDEX PRIMARY XML INDEX SPATIAL INDEX XML INDEX |
||
|
alloc_unit_type_desc |
nvarchar(60) |
对分配单元类型的说明： IN_ROW_DATA LOB_DATA ROW_OVERFLOW_DATA LOB_DATA 分配单元包含类型为text、ntext、image、varchar(max)、nvarchar(max)、varbinary(max) 和 xml 的列中所存储的数据。 ROW_OVERFLOW_DATA 分配单元包含类型为 varchar(n)、nvarchar(n)、varbinary(n) 和sql_variant 的列（已推送到行外）中所存储的数据。 |
||
|
index_depth |
tinyint |
索引总级别数。 1 = 堆，或 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元。 |
||
|
index_level |
tinyint |
索引的当前位于B树结构中的级别。 0 表示索引叶级别、堆以及 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元。 大于 0 的值表示非叶索引级别。 index_level 在索引的根级别中属于最高级别。 仅当 mode = DETAILED 时才处理非叶级别的索引。 |
||
|
avg_fragmentation_in_percent |
float |
索引的逻辑碎片，或 IN_ROW_DATA 分配单元中堆的区碎片。 此值按百分比计算，并将考虑多个文件。 0 表示 LOB_DATA 和 ROW_OVERFLOW_DATA 分配单元。 如果是堆表且mode模式 为 Sampled 时，为 NULL。如果碎片小于10%~20%，碎片不太可能会成为问题，如果索引碎片在20%~40%，碎片可能成为问题，但是可以通过索引重组来消除索引解决，大规模的碎片(当碎片大于40%)，可能要求索引重建。 |
||
|
fragment_count |
bigint |
IN_ROW_DATA 分配单元的叶级别中的碎片数。 对于索引的非叶级别，以及 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元，为 NULL。 对于堆，当 mode 为 SAMPLED 时，为 NULL。 |
||
|
avg_fragment_size_in_pages |
float |
IN_ROW_DATA 分配单元的叶级别中的一个碎片的平均页数。 对于索引的非叶级别，以及 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元，为 NULL。 对于堆，当 mode 为 SAMPLED 时，为 NULL。 |
||
|
page_count |
bigint |
索引或数据页的总数。 对于索引，表示 IN_ROW_DATA 分配单元中 b 树的当前级别中的索引页总数。 对于堆，表示 IN_ROW_DATA 分配单元中的数据页总数。 对于 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元，表示该分配单元中的总页数。 |
||
|
avg_page_space_used_in_percent |
float |
所有页中使用的可用数据存储空间的平均百分比。 对于索引，平均百分比应用于 IN_ROW_DATA 分配单元中 b 树的当前级别。 对于堆，表示 IN_ROW_DATA 分配单元中所有数据页的平均百分比。 对于 LOB_DATA 或 ROW_OVERFLOW DATA 分配单元，表示该分配单元中所有页的平均百分比。 当 mode 为 LIMITED 时，为 NULL。 |
||
|
record_count |
bigint |
总记录数。 对于索引，记录的总数应用于 IN_ROW_DATA 分配单元中 b 树(包括非叶子数据页的数量)的当前级别。 对于堆，表示 IN_ROW_DATA 分配单元中的总记录数。
对于 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元，表示整个分配单元中总记录数。 当 mode 为 LIMITED 时，为 NULL。 |
||
|
ghost_record_count |
bigint |
分配单元中将被虚影清除任务删除的虚影记录数。 对于 IN_ROW_DATA 分配单元中索引的非叶级别，为 0。 当 mode 为 LIMITED 时，为 NULL。 |
||
|
version_ghost_record_count |
bigint |
由分配单元中未完成的快照隔离事务保留的虚影记录数。 对于 IN_ROW_DATA 分配单元中索引的非叶级别，为 0。 当 mode 为 LIMITED 时，为 NULL。 |
||
|
min_record_size_in_bytes |
int |
最小记录大小（字节）。 对于索引，最小记录大小应用于 IN_ROW_DATA 分配单元中 b 树的当前级别。 对于堆，表示 IN_ROW_DATA 分配单元中的最小记录大小。 对于 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元，表示整个分配单元中的最小记录大小。 当 mode 为 LIMITED 时，为 NULL。 |
||
|
max_record_size_in_bytes |
int |
最大记录大小（字节）。 对于索引，最大记录的大小应用于 IN_ROW_DATA 分配单元中 b 树的当前级别。 对于堆，表示 IN_ROW_DATA 分配单元中的最大记录大小。 对于 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元，表示整个分配单元中的最大记录大小。 当 mode 为 LIMITED 时，为 NULL。 |
||
|
avg_record_size_in_bytes |
float |
平均记录大小（字节）。 对于索引，平均记录大小应用于 IN_ROW_DATA 分配单元中 b 树的当前级别。 对于堆，表示 IN_ROW_DATA 分配单元中的平均记录大小。 对于 LOB_DATA 或 ROW_OVERFLOW_DATA 分配单元，表示整个分配单元中的平均记录大小。 当 mode 为 LIMITED 时，为 NULL。 |
||
|
forwarded_record_count |
bigint |
堆中具有指向另一个数据位置的转向指针的记录数。 （在更新过程中，如果在原始位置存储新行的空间不足，将会出现此状态。） 除 IN_ROW_DATA 分配单元外，对于堆的其他所有分配单元都为 NULL。 当 mode = LIMITED 时，对于堆为 NULL。 |
||
|
compressed_page_count |
bigint |
压缩页的数目。
|
分析小表的碎片
不要过分关注小表的sys.dm_db_index_physical_stats输出。对于少于8个页面的小表或者索引，SQL Server使用混合区。例如，如果一个表仅包含两个页面，SQL Server从一个混合区中分配两个页面，二不是分配一个区给该表。混合区也可以包含其他小表或索引的页面。
跨越多个混合区的页面分布可能导致你相信在表或索引中有大量的外部碎片，而实际上这是SQL Server的设计，因而是可接受的。
先来建一张表如下，3个int字段，1个char(2000)字段。平均尺寸为4+4+4+2000=2012字节，8KB的页面最多包含4行。在添加了28行之后，创建一个聚集索引来从屋里上排列行并将碎片减少到最低限度。
咋一看，好像碎片非常厉害。实际上并不是这么回事。
分析如下：
- avg_fragmentation_in_percent:尽管这个索引可能跨越多个区，这里看到碎片的情况并不是外部碎片的迹象，因为该索引保存在混合区上。
- avg_page_space_used_in_percent:这说明所有或大部分县市在page_count中的7个页面中的数据存储状况良好。几乎满了，99点几。这消除了逻辑碎片的可能性。
- fragment_count:这说明数据有碎片并且保存在多于一个区上，但是因为它的长度小于8个页面，SQL Server对存储该数据的地点没有很多选择。
尽管有上述引起误导的数值，一个少于8个页面的小表(或索引)不可能从去除碎片的工作中获益，因为它保存在混合区上。
索引说明：
三、关于碎片的解决方法
1.删除索引并重建
这种方式有如下缺点：
索引不可用：在删除索引期间，索引不可用。
阻塞：卸载并重建索引会阻塞表上所有的其他请求，也可能被其他请求所阻塞。
对于删除聚集索引，则会导致对应的非聚集索引重建两次(删除时重建，建立时再重建，因为非聚集索引中有指向聚集索引的指针)。
唯一性约束：用于定义主键或者唯一性约束的索引不能使用DROP INDEX语句删除。而且，唯一性约束和主键都可能被外键约束引用。在主键卸载之前，所有引用该主键的外键必须首先被删除。尽管可以这么做，但这是一种冒险而且费时的碎片整理方法。
基于以上原因，不建议在生产数据库，尤其是非空闲时间不建议采用这种技术。
2.使用DROP_EXISTING语句重建索引
为了避免重建两次索引，使用DROP_EXISTING语句重建索引，因为这个语句是原子性的，不会导致非聚集索引重建两次，但同样的，这种方式也会造成阻塞。
CREATE UNIQUE CLUSTERED INDEX IX_C1 ON t1(c1)
WITH (DROP_EXISTING = ON)
缺点:
阻塞：与卸载重建方法类似，这种技术也导致并面临来自其他访问该表(或该表的索引)的查询的阻塞问题。
使用约束的索引：与卸载重建不同，具有DROP_EXISTING子句的CREATE INDEX语句可以用于重新创建使用约束的索引。如果该约束是一个主键或与外键相关的唯一性约束，在CREATE语句中不能包含UNIQUE。
具有多个碎片化的索引的表：随着表数据产生碎片，索引常常也碎片化。如果使用这种碎片整理技术，表上所有索引都必须单独确认和重建。
3.使用ALTER INDEX REBUILD语句重建索引
使用这个语句同样也是重建索引，但是通过动态重建索引而不需要卸载并重建索引.是优于前两种方法的，但依旧会造成阻塞。可以通过ONLINE关键字减少锁，但会造成重建时间加长。
阻塞：这个依然有阻塞问题。
事务回滚：ALTER INDEX REBUILD完全是一个原子操作，如果它在结束前停止，所有到那时为止进行的碎片整理操作都将丢失，可以通过ONLINE关键字减少锁，但会造成重建时间加长。
4.使用ALTER INDEX REORGANIZE
这种方式不会重建索引，也不会生成新的页，仅仅是整理叶级数据，不涉及非叶级，当遇到加锁的页时跳过，所以不会造成阻塞。但同时，整理效果会差于前三种。
4种索引整理技术比较：
| 特性/问题 | 卸载并重建索引 | DROP_EXISTING | ALTER INDEX REBUILD | ALTER INDEX REORGANIZE |
| 在聚集索引碎片整理时，重建非聚集索引 | 两次 | 无 | 无 | 无 |
| 丢失索引 | 是 | 无 | 无 | 无 |
| 整理具有约束的索引的碎片 | 高度复杂 | 复杂性适中 | 简单 | 简单 |
| 同时进行多个索引的碎片整理 | 否 | 否 | 是 | 是 |
| 并发性 | 低 | 低 | 中等，取决于冰法用户活动 | 高 |
| 中途撤销 | 因为不使用事务，存在危险 | 进程丢失 | 进程丢失 | 进程被保留 |
| 碎片整理程度 | 高 | 高 | 高 | 中到低 |
| 应用新的填充因子 | 是 | 是 | 是 | 否 |
| 更新统计 | 是 | 是 | 是 | 否 |
四、填充因子FILLFACTOR
重建索引能够解决碎片的问题，但是重建索引的代码一来需要经常操作，二来会造成数据阻塞，影响使用。在数据比较少的情况下，重建索引代价很快，但是当索引比较大的时候，例如超过100M，那么重建索引的时间会非常长。
填充因子的作用是控制索引叶子页面中的空闲空间数量。说白了就是预留一些空间给INSERT和UPDATE。如果知道表上有很多的INSERT查询或者索引键列上有足够的UPDATE查询，可以预先使用填充因子来增加索引叶子页面的空闲空间已最小化页面分割。如果表示只读的，可以创建一个高填充因子来减少索引页面的数量。
默认的填充因子为0，这意味着页面将被100%充满。
填充因子的概念可以理解为预留一定的空间存放插入和更新新增加的数据，以避免页拆分：
可以看出，使用填充因子会减少更新或者插入时的分页次数，但由于需要更多的页，则会对应的损失查找性能.
填充因子值的选择：
如何设置填充因子的值并没有一个公式或者理念可以准确的设置。使用填充因子虽然可以减少更新或者插入时的分页，但同时因为需要更多的页，所以降低了查询的性能和占用更多的磁盘空间.如何设置这个值进行trade-off需要根据具体的情况来看.
具体情况要根据对于表的读写比例来看,我这里给出我认为比较合适的值:
- 当读写比例大于100：1时，不要设置填充因子，100%填充
- 当写的次数大于读的次数时，设置50%-70%填充
- 当读写比例位于两者之间时80%-90%填充
SQL Server索引的维护 - 索引碎片、填充因子 <第三篇>
标签：
本文系统来源：http://www.cnblogs.com/feng-NET/p/4541734.html
内容总结
以上是互联网集市为您收集整理的SQL Server索引的维护 - 索引碎片、填充因子 <第三篇>全部内容，希望文章能够帮你解决SQL Server索引的维护 - 索引碎片、填充因子 <第三篇>所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。