本文主要向大家介绍了SQLServer数据库索引优化，通过具体的内容向大家展现，希望对大家学习SQLServer数据库有所帮助。

一、索引概述

1、概念

　　可以把索引理解为一种特殊的目录。就好比《新华字典》为了加快查找的速度，提供了几套目录，分别按拼音、偏旁部首、难检字等排序，这样我们就可以方便地找到需要的字。

　　与书中的索引一样，数据库中的索引使您可以快速找到表或索引视图中的特定信息。索引包含从表或视图中一个或多个列生成的键，以及映射到指定数据的存储位置的指针。通过创建设计良好的索引以支持查询，可以显著提高数据库查询和应用程序的性能。索引可以减少为返回查询结果集而必须读取的数据量。索引还可以强制表中的行具有唯一性，从而确保表数据的数据完整性。

2. 分类

　　SQL SERVER提供了两种索引：聚集索引（Clustered Index）和非聚集索引（Nonclustered Index）。 

3. 索引B树

　　索引是按照B树结构组织的。如下图。

　　在上图中，底部是叶级（leaf level），用于保存指向数据行的指针；非叶级用于导航到下一个非叶级或者叶级，非叶级包括2部分，即顶部的根（root）和中间部分的中间级（intermediate level）。

　　假设数据页每页可以保存2条记录，索引的平均宽度为20字节，则索引的每个页（8KB）可以保存约400个行指针，理论上（排除碎片等因素）上图所示的4级树能够搜索的记录行可以达到：2*400*400*400=1.28亿。这表示查询时如果使用此索引，只需要4次I/O操作就可以导航至对应的数据行。

二、堆的物理结构

　　SQL Server 的数据组织结构为HOBT（堆或平衡树）。

　　如果数据以堆的方式组织，那么数据行不按任何特殊的顺序存储，数据页也没有任何特殊的顺序。 可以通过扫描 IAM （索引分配映射）页实现对堆的表扫描或串行读操作来找到容纳该堆的页的区。

　　从上图可见，数据页之间没有任何关系，完全依赖IAM页进行组织。对于一个查询，需要首先查询IAM页，然后根据IAM页提供的指针去遍历对应的每个区，然后返回这些区内符合查询条件的页。如果IAM页损坏，则整张表的结构被破坏，数据基本上不能被修复。 

三、堆上的索引

　　索引中的每个索引行都包含非聚集键值和行定位符。此定位符指向堆中包含该键值的数据行。

　　索引行中的行定位器是指向行的指针。该指针由文件标识符 (ID)、页码和页上的行数生成。整个指针称为行 ID (RID)。 

　　从上表可见，这是一个完整的树状结构。索引的最顶层是根，根页存放的指针指向中间级（下一级的非叶级索引）或者指向叶级。索引的最底层是叶级，只能有一个叶级，叶级页存放的指针指向真实的数据行。

四、实验[三A]：堆上的非聚集索引

1. 构建一个堆结构的表

　　创建一张没有聚集索引的表，并为这张表添加80000条记录。

create table person1 (UserID int,pwd char(20),OtherInfo char(360),modifydate datetime)
declare @i int
set @i=0
while @i<80000
begin
  insert into person1 
  select cast(floor(rand()*100000) as int), cast(floor(rand()*100000) as varchar(20)),
  cast(floor(rand()*100000) as char(360)), GETDATE()
  set @i=@i+1
end

2. 添加一个非聚集索引

　　为这个堆结构的表创建一个非聚集索引。

CREATE NONCLUSTERED INDEX IX_person1_UserID 
    ON person1 (UserID)

3. 表和索引的页面分配统计

　　使用DBCC命令查看表和索引的页面分配情况。

DBCC SHOWCONTIG ('person1') WITH ALL_INDEXES

　　显示结果如下：

DBCC SHOWCONTIG 正在扫描 'person1' 表...
表: 'person1' (245575913)；索引 ID: 0，数据库 ID: 8
已执行 TABLE 级别的扫描。
- 扫描页数................................: 4000
- 扫描区数..............................: 502
- 区切换次数..............................: 501
- 每个区的平均页数........................: 8.0
- 扫描密度 [最佳计数:实际计数].......: 99.60% [500:502]
- 区扫描碎片 ..................: 1.79%
- 每页的平均可用字节数.....................: 76.0
- 平均页密度(满).....................: 99.06%
DBCC SHOWCONTIG 正在扫描 'person1' 表...
表: 'person1' (245575913)；索引 ID: 2，数据库 ID: 8
已执行 LEAF 级别的扫描。
- 扫描页数................................: 179
- 扫描区数..............................: 23
- 区切换次数..............................: 22
- 每个区的平均页数........................: 7.8
- 扫描密度 [最佳计数:实际计数].......: 100.00% [23:23]
- 逻辑扫描碎片 ..................: 0.00%
- 区扫描碎片 ..................: 4.35%
- 每页的平均可用字节数.....................: 51.3
- 平均页密度(满).....................: 99.37%
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

　　以上结果显示，数据页共有4000页（即4,000*8=32,000KB），占用502个区（即502*64=32,128KB）；索引的叶级共有179页（即179*8=1,432KB），占用23个区（即23*64=1,72KB）。 

4. 查看索引的级数

　　根据DBCC SHOWCONTIG的结果，我们现在可以结合DMV来查看该索引的每一级是如何分布。

SELECT index_depth, index_level, record_count, page_count,
min_record_size_in_bytes as 'MinLen', max_record_size_in_bytes as 'MaxLen',
avg_record_size_in_bytes as 'AvgLen',
convert(decimal(6,2),avg_page_space_used_in_percent) as 'PageDensity'
FROM sys.dm_db_index_physical_stats (8, OBJECT_ID('person1'),2,NULL,'DETAILED')

　　注意，sys.dm_db_index_physical_stats 函数的5个参数分别表示如下意义：

　　第1个参数为该数据库的 ID。在本例中，DBCC SHOWCONTIG 已经显示了该数据库的 ID=8。也可以通过 DB_ID('DatabaseName') 类似方式取得。

　　第2个参数为该表的 ID。可以通过 OBJECT_ID('TableName') 类似方式取得。或者通过 select * from sys.objects where name='person' 查找到具体的OBJECT_ID 。

　　第3个参数为该索引的 ID。在本例中，DBCC SHOWCONTIG 已经显示了该索引的 ID=2。如果 ID=0，则指向数据页（堆或聚集索引）。NULL表示需要获得所有的索引。

　　第4个参数表示分区号。NULL表示需要获得所有分区的信息。

　　第5个参数表示希望返回的信息级别。NULL表示不返回所有的信息。

　　叶级索引的行长度为16字节，它包括：4 个字节对应于 int 列（UserID列），8 个字节对应于数据行定位符（即堆 RID），1个字节对应于索引行的行标题开销，3个字节用于NULL位图（UserID列可以为空）。

　　根页的行长度为22字节，即在叶级的行长度加6字节，这6个字节对应下一级索引页的指针。

5. 查看堆的分布

　　查看索引 ID=0 的分布，实际上就是查看堆的页面分布情况。

SELECT index_depth, index_level, record_count, page_count,
min_record_size_in_bytes as 'MinLen',max_record_size_in_bytes as 'MaxLen',
avg_record_size_in_bytes as 'AvgLen',
convert(decimal(6,2),avg_page_space_used_in_percent) as 'PageDensity'
FROM sys.dm_db_index_physical_stats (8, OBJECT_ID('person1'),0,NULL,'DETAILED')

6. 总结

五、实验[三B]：堆上的（唯一、非空值）非聚集索引

1. 构建一个堆结构的表

　　创建一张没有聚集索引的表，并为这张表添加80000条记录。注意，与前一个实验不同的是，UserID列是唯一且非空。

create table person2 (UserID int not null,pwd char(20),OtherInfo char(360),modifydate datetime)
declare @i int
set @i=0
while @i<80000
begin
  insert into person2 
  select @i, cast(floor(rand()*100000) as varchar(20)),
  cast(floor(rand()*100000) as char(360)), GETDATE()
  set @i=@i+1
end

2. 添加一个非聚集索引

　　为这个堆结构的表创建一个唯一、非聚集索引。

CREATE UNIQUE NONCLUSTERED INDEX IX_person2_UserID 
    ON person2 (UserID)

3. 查看索引的级数

　　使用DMV来查看所有的索引分布。

SELECT index_depth, index_level, record_count, page_count,
min_record_size_in_bytes as 'MinLen', max_record_size_in_bytes as 'MaxLen',
avg_record_size_in_bytes as 'AvgLen',
convert(decimal(6,2),avg_page_space_used_in_percent) as 'PageDensity'
FROM sys.dm_db_index_physical_stats (8, OBJECT_ID('person2'),NULL,NULL,'DETAILED')

六、删除堆中的数据

1. 使用delete删除所有数据

delete person2

2. 查看表的空间

　　使用DBCC SHOWCONTIG查看该表

DBCC SHOWCONTIG ('person2') WITH ALL_INDEXES

　　发现仍然占用着数据页（“扫描页数”>0）。 

DBCC SHOWCONTIG 正在扫描 'person2' 表...
表: 'person2' (261575970)；索引 ID: 0，数据库 ID: 8
已执行 TABLE 级别的扫描。
- 扫描页数................................: 296
- 扫描区数..............................: 39
- 区切换次数..............................: 38
- 每个区的平均页数........................: 7.6
- 扫描密度 [最佳计数:实际计数].......: 94.87% [37:39]
- 区扫描碎片 ..................: 7.69%
- 每页的平均可用字节数.....................: 8056.0
- 平均页密度(满).....................: 0.47%
DBCC SHOWCONTIG 正在扫描 'person2' 表...
表: 'person2' (261575970)；索引 ID: 2，数据库 ID: 8
已执行 LEAF 级别的扫描。
- 扫描页数................................: 1
- 扫描区数..............................: 1
- 区切换次数..............................: 0
- 每个区的平均页数........................: 1.0
- 扫描密度 [最佳计数:实际计数].......: 100.00% [1:1]
- 逻辑扫描碎片 ..................: 0.00%
- 区扫描碎片 ..................: 0.00%
- 每页的平均可用字节数.....................: 8078.0
- 平均页密度(满).....................: 0.20%
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

3. 使用表锁删除数据

　　往该表中添加 1 条记录，然后再使用以下命令删除。

insert person2 values (1,'abc','abc',GETDATE())

delete person2 with (TABLOCK)

　　再次使用DBCC SHOWCONTIG查看该表，可以看到占用的页数（“扫描页数”）减少了1页，即已经释放了1个页面。 

4. 收缩表的空间

　　使用以下命令将该数据库的数据文件进行收缩。

DBCC SHRINKFILE (N'db01' , 0, TRUNCATEONLY)

　　再次使用DBCC SHOWCONTIG查看该表，发现仍然占用的页数为零（“扫描页数”=0），即已经释放了所有的空间。 

5. 总结

　　SQL Server 默认在 delete 时不会加上表锁（即TABLOCK），此时堆结构的表不会释放空间给其它数据复用。

　　在删除堆中的行数据时加上TABLOCK，或者手动执行SHRINKFILE （或SHRINKDB）才能释放堆中的空闲页面。

索引优化（2）聚集索引

一、聚集索引的概念

 　　聚集索引根据数据行的键值在表或视图中排序和存储这些数据行。 索引定义中包含聚集索引列。 每个表只能有一个聚集索引，因为数据行本身只能按一个顺序排序。

　　只有当表包含聚集索引时，表中的数据行才按排序顺序存储。 如果表具有聚集索引，则该表称为聚集表。 如果表没有聚集索引，则其数据行存储在一个称为堆的无序结构中。

　　下图是聚集索引的示意图。与前文所述的非聚集索引相比，聚集索引在结构上的最大特点是：索引的叶级就是数据页。

二、实验[三C]：（非唯一）聚集索引

1. 创建非聚集索引 

　　继续使用前一篇文章的测试专用表，首先删除原有的非聚集索引，然后创建一个聚集索引。

DROP INDEX IX_person1_UserID ON person1

CREATE CLUSTERED INDEX IX_person1_UserID ON person1 (UserID)

2. 查看索引的空间分配

DBCC SHOWCONTIG ('person1') WITH ALL_INDEXES

　　结果如下：

DBCC SHOWCONTIG 正在扫描 'person1' 表...
表: 'person1' (245575913)；索引 ID: 1，数据库 ID: 8
已执行 TABLE 级别的扫描。
- 扫描页数................................: 4009
- 扫描区数..............................: 502
- 区切换次数..............................: 501
- 每个区的平均页数........................: 8.0
- 扫描密度 [最佳计数:实际计数].......: 100.00% [502:502]
- 逻辑扫描碎片 ..................: 0.37%
- 区扫描碎片 ..................: 0.80%
- 每页的平均可用字节数.....................: 44.2
- 平均页密度(满).....................: 99.45%
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

　　将上述结果与前文的非聚集索引相比，很明显可见数据页与索引页合为一体（索引ID=1）。注意，此处自动增加了9个页，后文将会说明原因。

3. 查看索引的层次

SELECT index_depth, index_level, record_count, page_count,
min_record_size_in_bytes as 'MinLen',
max_record_size_in_bytes as 'MaxLen',
avg_record_size_in_bytes as 'AvgLen',
convert(decimal(6,2),avg_page_space_used_in_percent) as 'PageDensity'
FROM sys.dm_db_index_physical_stats
  (8, OBJECT_ID('person1'),1,NULL,'DETAILED')

　　根据上表的数据，可以看到该索引共有3层。最底层 level=0 是叶级，它有4009个页面，这个叶级实际上就是包含80000行数据的数据页。非叶级共有2层，其中level=1 是中间级，它有10个页面，保存了4009个指针分别指向叶级的4009页；level=2 是根，它只有1个页面，保存了10个指针分别指向中间级的10个索引页。 

　　中间级索引的行长度为14~22个字节，根级索引的行长度也为14~22 个字节。 

4. 总结

　　索引的层次与页面结构如下图：

三、实验[三D]：（唯一）聚集索引

　　聚集索引中的数据即可以唯一，也可以不唯一，取决于定义这个索引时的 UNIQUE 设置。

　　如果未使用 UNIQUE 属性创建聚集索引，SQL Server 将向表自动添加一个 4 字节 uniqueifier 列，使每个键唯一。此列和列值仅供内部使用，用户不能查看或访问。

1. 创建测试用的表

　　创建一个新的表，并添加80000行记录。

create table person3 (UserID int not null,pwd char(20),OtherInfo char(360),modifydate datetime)
declare @i int
set @i=0
while @i<80000
begin
  insert into person3 
  select @i,cast(floor(rand()*100000) as varchar(10)),
  cast(floor(rand()*100000) as char(50)), GETDATE()
  set @i=@i+1
end

2. 检查页数量

　　使用DBCC SHOWCONTIG，查得该表的页的数量为4000页。

3. 创建唯一聚集索引

CREATE UNIQUE CLUSTERED  INDEX  IX_person3_UserID  
    ON person3 (UserID) 

4. 检查页的数量

　　再次使用DBCC SHOWCONTIG，查得该表的页的数量仍然为4000页。

5. 查看索引的层次

SELECT index_depth, index_level, record_count, page_count,
min_record_size_in_bytes as 'MinLen',
max_record_size_in_bytes as 'MaxLen',
avg_record_size_in_bytes as 'AvgLen',
convert(decimal(6,2),avg_page_space_used_in_percent) as 'PageDensity'
FROM sys.dm_db_index_physical_stats
  (8, OBJECT_ID('person3'),1,NULL,'DETAILED')

6. 总结

　　索引的层次与页面结构如下图所示：

四、聚集索引与约束

1. 主键的概念

　　主键，也称主关键字（Primary Key），是表中的一个或多个列，它的值用于惟一地标识表中的某一条记录。在两个表的关系中，主键用来在一个表中引用来自于另一个表中的特定记录。主键是一种唯一键，是表定义的一部分。

　　一个表只能包含一个主键约束。如果在其他列上建立主键，则原来的主键就会取消。定义主键后，在主键的左侧会显示一个钥匙状的图标，表示该字段已被设为主键。

　　在主键约束中定义的所有列都必须定义为 NOT NULL。 如果没有指定为 Null 性，则加入主键约束的所有列的为 Null 性都将设置为 NOT NULL。

2. 主键与索引的关系

　　创建主键将自动创建相应的唯一索引、聚集索引或非聚集索引。例如：

ALTER TABLE person 
ADD CONSTRAINT PK_person_UserID PRIMARY KEY CLUSTERED (UserID)

　　数据库在创建主键同时，会自动建立一个唯一索引。
　　如果这个表之前没有聚集索引，同时建立主键时候没有强制指定使用非聚集索引，则建立主键时候，同时建立一个唯一的聚集索引。例如，下例中将自动创建一个聚集、唯一索引。

create table person (UserID int not null,pwd char(20),modifydate datetime)

ALTER TABLE person 
ADD CONSTRAINT PK_person_UserID PRIMARY KEY (UserID)

3. 唯一约束与索引的关系

　　唯一约束（UNIQUE CONSTRAINT）与主键约束相反，除非显式指定了聚集索引，否则，默认情况下将创建唯一的非聚集索引以强制执行唯一约束。

4. 约束与索引的主要区别

　　主键约束和唯一约束的主要目的都是为了保持数据行的唯一性，不允许有重复的数据行；而聚集索引的主要目的是为了使数据按照一定的顺序进行物理排序以加快查询的速度，并且允许有重复的数据行。

索引优化（3）聚集索引上的非聚集索引

一、索引结构

　　在聚集索引上建立非聚集索引，在日常应用中经常发生。

　　非聚集索引具有独立于数据行的结构。非聚集索引包含非聚集索引键值，并且每个键值项都有指向包含该键值的数据行的指针。

　　从非聚集索引中的索引行指向数据行的指针称为行定位器。行定位器的结构取决于数据页是存储在堆中还是聚集表中，对于聚集表，行定位器是聚集索引键。

二、实验[三E]

　　继续使用上一篇文章中创建的唯一聚集索引，在此基础之上新建一个非聚集索引。

1. 创建非聚集索引

CREATE INDEX  IX_person1_UserIDModifyDate  
    ON person1 (UserID,ModifyDate)

2. 查看索引占用的空间

DBCC SHOWCONTIG ('person1') WITH ALL_INDEXES

 　　结果如下：

DBCC SHOWCONTIG 正在扫描 'person1' 表...
表: 'person1' (389576426)；索引 ID: 1，数据库 ID: 8
已执行 TABLE 级别的扫描。
- 扫描页数................................: 4000
- 扫描区数..............................: 500
- 区切换次数..............................: 499
- 每个区的平均页数........................: 8.0
- 扫描密度 [最佳计数:实际计数].......: 100.00% [500:500]
- 逻辑扫描碎片 ..................: 0.03%
- 区扫描碎片 ..................: 2.20%
- 每页的平均可用字节数.....................: 76.0
- 平均页密度(满).....................: 99.06%
DBCC SHOWCONTIG 正在扫描 'person1' 表...
已执行 LEAF 级别的扫描。
- 扫描页数................................: 179
- 扫描区数..............................: 23
- 区切换次数..............................: 22
- 每个区的平均页数........................: 7.8
- 扫描密度 [最佳计数:实际计数].......: 100.00% [23:23]
- 逻辑扫描碎片 ..................: 0.00%
- 区扫描碎片 ..................: 4.35%
- 每页的平均可用字节数.....................: 51.3
- 平均页密度(满).....................: 99.37%
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

3. 查看索引的层次

　　对于建立在聚集索引上的非聚集索引，

SELECT index_depth, index_level, record_count, page_count,
min_record_size_in_bytes as 'MinLen',
max_record_size_in_bytes as 'MaxLen',
avg_record_size_in_bytes as 'AvgLen',
convert(decimal(6,2),avg_page_space_used_in_percent) as 'PageDensity'
FROM sys.dm_db_index_physical_stats
  (8, OBJECT_ID('person1'),2,NULL,'DETAILED')

三、比较三类索引占用的页数

　　比较前面几个实验，各类索引占用的页数如下：

1. 堆

　　在实验[三A]中，堆是最原始的结构，index_id = 0，存储了 80000 条记录，占用了4000 页。

2. 聚集索引

　　聚集索引的 index_id = 1。

　　唯一聚集索引在叶级将数据页重新进行物理排序，不会额外增加数据页。由于索引宽度固定，因此在根级只占用了1个页，中间级占用了7个页。一共占用了1+7+4000=4008 页。与堆相比，非叶级的索引页多了8页。

　　非唯一聚集索引需要在后台保持数据的唯一，因此在后台增加了一个 4 字节的uniqueifier 列，有可能需要增加额外的数据页。在前面的案例中，非唯一聚集索引使用了4009页，也就是多了9个页。同时由于索引宽度的开销较大，中间级占用了10个页，加上根级占用了1个页，一共占用了1+10+4009=4020 页。与堆相比，叶级索引页（数据页）多了9页，非叶级的索引页多了11页。

3. 非聚集索引

　　堆上的非聚集索引与聚集索引上的非聚集索引，index_id >= 2，占用了相同数量的索引页面，页面数量为：179+1=180 页。

索引优化（4）索引碎片

一、碎片的产生

1. 内部碎片

　　SQL Server 是以页（8KB）为单位存储数据行和索引数据，因此索引行也不能跨页，也就导致索引页不能被完全填充。

　　在索引键偏大时，这种情况就比较明显。特别对于聚集索引而言，由于叶级索引页就是数据页，更容易导致内部碎片。例如，一张聚集索引的表，数据行固定为5KB，那么每页只能存放1行记录，相当于叶级索引页只有约60%的利用率。

2. 外部碎片

　　外部碎片指的是由于分页而产生的碎片。

　　向表中insert一个新行时（update语句在内部原理是利用一个delete语句后面紧跟一个insert语句来执行），SQL Server必须确定数据的插入位置，同时还要将相应的行插入到每个非聚集索引中。

　　当表是一个堆时，新行总是被插入到表中任意可用的空间。对于向有聚集索引的表中插入数据行和在非聚集索引中插入索引行，都必须根据新行在索引键列上的值来决定被插入的位置。这时候有3种可能：

（1）新行的位置被确定位于索引的最后。这时候，如果索引页的未尾还有空间，则直接插入新行；如果空间不足，则申请分配一个新页面并将该页面连接到B树上。

（2）新行必须插入到索引页的中间页面，并且该页面还有空间，则直接插入到该页面。

（3）新行必须插入到索引页的中间页面，但该页面已满，则发生分页，原始页面将被拆份，但又没有填满，从而产生外部碎片。

3. 拆分页

（1）拆分根页

　　如果拆分的是索引的根页，则会新分配2个页而作为索引上创建一个新的级别，根页则只有2行，分别指向新分配的页面，从而保证根页总是1页。

（2）拆分中间级页或叶级页

　　如果拆分的是索引的中间级页或叶级页，则原始页面的一半的行数被留在原始页面，另一半则被转移到新的页面上。SQL Server将尽可能使原始页与新页有差不多数量的行记录。

二、DBCC SHOWCONTIG 检查碎片程度

1. 语法

DBCC SHOWCONTIG 
[ (     { table_name | table_id | view_name | view_id }     [ , index_name | index_id ] ) ] 
[ WITH    {     [ , [ ALL_INDEXES ] ]     [ , [ TABLERESULTS ] ]     [ , [ FAST ] ]    [ , [ ALL_LEVELS ] ]     [ NO_INFOMSGS ]   }    ]

2. 示例

　　以前面的案例中，显示结果如下：

DBCC SHOWCONTIG ('person1') WITH ALL_INDEXES

====

DBCC SHOWCONTIG 正在扫描 'person1' 表...
表: 'person1' (245575913)；索引 ID: 1，数据库 ID: 8
已执行 TABLE 级别的扫描。
- 扫描页数................................: 4009
- 扫描区数..............................: 502
- 区切换次数..............................: 501
- 每个区的平均页数........................: 8.0
- 扫描密度 [最佳计数:实际计数].......: 100.00% [502:502]
- 逻辑扫描碎片 ..................: 0.37%
- 区扫描碎片 ..................: 0.80%
- 每页的平均可用字节数.....................: 44.2
- 平均页密度(满).....................: 99.45%
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

3. 结果集信息说明

（1）Extents（扫描区数）： 

　　某个索引级别或整个堆中的区数。

（2）ExtentSwitches（区切换次数）：

　　遍历表或索引的页时，DBCC 语句从一个区移动到另一个区的次数。

（3）BestCount（最佳计数）：

　　所有内容连续时的区更改理想数量。

（4）ActualCount（实际计数）：

　　遍历表或索引的页时，区更改实际数量。

（5）ScanDensity（扫描密度）：

　　这是“最佳计数”与“实际计数”的百分比。如果所有内容都是连续的，则该值为 100；如果该值小于 100，则存在一些碎片。

（6）LogicalFragmentation（逻辑扫描碎片）：

　　扫描索引的叶级页时返回的出错页的百分比。 此数与堆无关。 对于出错页，分配给索引的下一个物理页不是由当前叶级页中的“下一页”指针所指向的页。实际上，逻辑碎片是在索引的叶级别中次序混乱页面的百分比。

（7）ExtentFragmentation（区扫描碎片）：

　　扫描索引的叶级页时出错区所占的百分比。 此数与堆无关。 对于出错区，包含当前索引页的区在物理上不是包含上一个索引页的区的下一个区。实际上，区碎片是在索引的叶级别中次序混乱区的百分比。

（8）AverageFreeBytes（每页的平均可用字节数）：

　　此数字越大，则页的填充程度越低。如果索引不会有很多随机插入，则数字越小越好。此数字还受行大小影响：行越大，此数字就越大。

（9）AveragePageDensity（平均页密度）：

　　页的平均密度，以百分比表示。该值会考虑行大小。因此，该值可以更准确地指示页的填充程度。百分比越大越好。‍

‍ 

4. 判断碎片化

　　DBCC SHOWCONTIG 可确定表是否高度碎片化。索引的碎片程度可通过以下方式确定： 

（1） 比较“区切换次数”和“扫描区数”的值 

　　“区切换次数”的值应尽可能接近于“扫描区数”的值。 此比率将作为“扫描密度”值计算。 此值应尽可能的大，可通过减少索引碎片得到改善。

（2）了解“逻辑扫描碎片”和“区扫描碎片”的值 

　　“逻辑扫描碎片”和“区扫描碎片”（对于较小的区）的值是表的碎片级别的最好指标。 这两个值应尽可能接近零，但 0% 到 10% 之间的值都是可接受的。 

三. sys.dm_db_index_physical_stats 判断碎片程度

1. 兼容性

　　SQL Server 2012联机手册中有以下声明：后续版本的 Microsoft SQL Server 将删除DBCC SHOWCONTIG 功能。请不要在新的开发工作中使用该功能，并尽快修改当前还在使用该功能的应用程序。请改用 sys.dm_db_index_physical_stats。

2. 语法

　　sys.dm_db_index_physical_stats 函数需要5个参数，可以都使用默认值NULL，从而为当前SQL Server实例中每个数据库每个分区上每个表每个索引的每个级别返回21列数据。

　　 重要的区别：DBCC SHOWCONTIG 是将共享锁 (S) 放置在包含索引的表上，而 sys.dm_db_index_physical_stats 仅放置一个意图共享锁 (IS)，从而在函数执行期间极大地减少了表的阻塞。

3. 碎片指标

（1）avg_fragmentation_in_percent 

　　此列中所返回的值可确定索引的逻辑碎片（堆的区碎片）。逻辑碎片是在索引的叶级别中次序混乱页面的百分比，而区碎片是在索引的叶级别中次序混乱区的百分比。由于磁头只有左右跳动才能按照顺序读取页面，因此，逻辑碎片和区碎片会因为其需要额外的 I/O 和磁头运动而影响索引的性能。尽量保证逻辑碎片和区碎片均接近于零。

（2）avg_page_space_used_in_percent 

　　此列可以确定索引页的填充度。为了正确配置该数字以使其尽量接近 100%，请在调整索引填充因子的同时观察所出现的页面分割数量。在某一刻，页面分割的数量会开始急剧增加，这表明您设置的索引填充因子数值高于其应有的数值。调整索引的填充因子需要花费一定的时间并需要进行测试，而且必须在事先进行合理规划。（如果未向索引中随意插入，则可以将索引填充因子设置为 100，且不必担心增加的页面分割数。）

四、GUI 查看索引属性

1. 查看索引的属性

2.  结果信息说明

 （1）分区 ID

　　包含该索引的 B 树的分区 ID。

 （2）建立虚影行版本

　　由于某个快照隔离事务未完成而保留的虚影记录的数目。

（3）平均行大小

　　叶级行的平均大小。

（4）前推记录数

　　堆中具有指向另一个数据位置的转向指针的记录数。在更新过程中，如果在原始位置存储新行的空间不足，将会出现此状态。

（5）深度

　　索引中的级别数（包括叶级别）。

（6）索引类型

　　索引的类型。可能的值包括 “聚集索引”、 “非聚集索引”和 “主 XML”。 表也可以存储为堆（不带索引），但此后将无法打开此“索引属性”页。

（7）虚影行数  

　　标记为已删除，但尚未移除的行数。当服务器不忙时，将通过清除线程移除这些行。此值不包括由于某个快照隔离事务未完成而保留的行。

（8）叶级行数

　　叶级行的数目。

（9）页

　　数据页总数。

（10）最大行大小

　　叶级行最大大小。

（11）最小行大小

　　叶级行最小大小。

（12）页填充度

　　指示索引页的平均填充率（以百分比表示）。100% 表示索引页完全填充。50% 表示每个索引页平均填充一半。

（13）碎片总计

　　逻辑碎片百分比。用于指示索引中未按顺序存储的页数。

五、索引的重组与重建

　　在对表进行数据修改（INSERT、UPDATE 和 DELETE 语句）的过程中会出现索引的碎片现象，索引页的填满状态会随时间而改变。对于扫描部分或全部索引的查询，这样的索引碎片会导致读取额外的页。 从而延缓了数据的读取。

　　如果索引的碎片非常多，可选择以下方法来减少碎片：

1. 删除然后重新创建聚集索引 

　　重新创建聚集索引将重新组织数据，从而使数据页填满。 填充度可以使用 CREATE INDEX 中的 FILLFACTOR 选项进行配置。 这种方法的缺点是索引在删除/重新创建周期内为脱机状态，并且该操作是一个整体，不可中断。 如果中断索引创建，则不能重新创建索引。 

DROP INDEX IX_person1_UserID ON person1

CREATE CLUSTERED INDEX IX_person1_UserID ON person1 (UserID)

2. 对索引的叶级页按逻辑顺序重新排序 

　　使用 INDEX…REORGANIZE，对索引的页级页按逻辑顺序重新排序。 由于此操作是联机操作，因此语句运行时索引可用。 此外，中断该操作不会丢失已完成的工作。 这种方法的缺点是在重新组织数据方面没有聚集索引的删除/重建操作有效。 

　　REORGANIZE 操作通过对叶级页以物理方式重新排序，使之与叶节点的从左到右的逻辑顺序相匹配，进而对表和视图中的聚集索引和非聚集索引的叶级进行碎片整理。 重新组织还会压缩索引页。 压缩基于现有的填充因子值。

ALTER INDEX IX_person2_UserID ON person2 REORGANIZE

3. 重新生成索引 

　　使用 REBUILD 和 ALTER INDEX 重新生成索引。此操作将根据指定的或现有的填充因子设置压缩页来删除碎片、回收磁盘空间，然后对连续页中的索引行重新排序。 如果指定 ALL，将删除表中的所有索引，然后在单个事务中重新生成。

　　REBUILD 操作还有一个重要的选项：ONLINE=OFF 或者 ONLINE=ON。

ALTER INDEX IX_person2_UserID ON person2 REBUILD WITH (ONLINE=OFF)

　　online模式下，REBUILD 操作会复制旧索引来新建索引，此时旧的索引依然可以被读取和修改，但是所有在旧索引上的修改都会同步更新到新索引下。中间会有一些冲突解决机制。然后在REBUILD 即将完成的时候，会对table上锁一段时间，在这段时间里会用新索引来替换旧索引，当这个过程完成以后再释放table上面的锁。如果索引列包含 LOB对象的话，在SQL Server 2005等版本中rebuild index online会失败。在SQL server 2012中消除了这个限制。

　　offline模式下，REBUILD 会对table上锁，所有对这个table的读写操作都会被阻塞，在这期间新索引根据旧索引来创建，其实就是一个复制的过程，但是新索引没有碎片，最后使用新索引替换旧索引。当REBUILD 整个过程完成以后，table上面的锁才会被释放。

六、填充因子

1. 填充因子

　　在向索引中添加新行时容易发生分页，不仅在页拆分时会降低性能，还会导致产生过多的索引碎片（内部碎片）。

　　SQL Server允许在创建索引时指定一个填充因子，以便在索引的每个叶级页上留出额外的间隙和保留一定百分比的空间，减少页拆分的可能性。

　　填充因子的值是从 0 到 100 之间的百分比数值，指定在创建索引后对数据页的填充比例。值为 0或100 时表示页将填满，所留出的存储空间量最小。只有当不会对数据进行更改时（例如，在只读表中）才会使用此设置。值越小则数据页上的空闲空间越大，这样可以减少在索引增长过程中对数据页进行拆分的需要，但需要更多的存储空间。当表中数据会频繁发生更改时，这种设置更为适当。

2. 通过脚本修改填充因子并重建索引

ALTER INDEX IX_person2_UserID ON person2 REBUILD WITH (FILLFACTOR = 60)

3. 通过GUI修改填充因子

索引优化（5）索引设计指南

　通过前面的几篇文章，我们初步了解了索引的一些特征，这将有助于设计出最佳的索引。

一、从数据库的角度进行设计

1. 索引的宽度

　　索引宽度，即索引的键占用了多少个字节。影响索引宽度有2个因素：一是引用的列，二是索的数据类型。原则上，索引应保持较窄，就是说，列要尽可能少，列的数据类型要尽可能精简。

　　不能将 ntext、text、image、varchar(max)、nvarchar(max) 和 varbinary(max) 数据类型的列指定为索引键列。不过，varchar(max)、nvarchar(max)、varbinary(max) 和 xml 数据类型的列可以作为非键索引列参与非聚集索引。

　　设计时注意以下几类数据类型：

（1）日期型 vs. 日期时间型

 　　旧版本的SQL Server只有日期时间型datetime（1753-01-01 00:00:00.000到 9999-12-31 23:59:59.999，8个字节）和smalldatetime（范围从1900-01-01 00:00到 2079-06-06 23:59，4个字节）。SQL Server 2008 新增了多种日期类型，其中包括date（范围从0001-01-01 到 9999-12-31，3个字节）和time。如果某个列经常只需要查询日期，建议将实际业务的datetime拆分为date和time类型的2个列，并在date类型的列上建立索引。

（2）整型 vs. 字符型

　　有些编号，例如客户ID，如果业务上没有特别要求，那么使用整型是最佳选择。因为，整型的范围从 -2,147,483,648 到 2,147,483,647 ，占4个字节，而同样范围的字符型却需要10个字节。此外，对于小范围的编号，smallint也是不错的选择，它的范围从 -32,768 到 32,767 ，只占2个字节。

（3）Uniqueidentifier列（GUID）vs. IDENTITY列

　　有些程序员希望每行有一个唯一标识，于是将GUID作为标识。如果在 SQL Server 的表定义中将列类型指定为 uniqueidentifier，则列的值就为 GUID 类型，占16个字节。

CREATE TABLE Table1( MyID uniqueidentifier, MyName varchar(10) )

insert into Table1 values (newid(),'noname')
select * from Table1

　　从程序设计的角度来看，上述设计并无不妥。但如果在这个列上建立索引（尤其是聚集索引），对性能可能有很大的影响。建议改用IDENTITY列。

　　首先，GUID占用16个字节；而IDENTITY列为int类型，仅占用4个字节。对比之下，后者的索引宽度可以缩减12个字节。

　　其次，GUID是随机的，导致索引的分页现象非常严重；而IDENTITY列的值一般是连续增长，因此不会造成索引分页。

CREATE TABLE Table2 ( MyId int IDENTITY, MyName varchar(10) ) 
insert into Table2 (MyName) Values ('noname')
select * from Table2 
dbcc checkident('Table2', NORESEED) 

　　或者，使用IDENTITY函数。

SELECT IDENTITY(int, 1,1) AS ID_Num
INTO NewTable
FROM OldTable

2. 索引维护的开销

（1）权衡读与写的数量

　　使用多个索引可以提高数据的查询（例如 SELECT 语句）的性能，因为查询优化器有更多的索引可供选择，从而可以确定最快的访问方法。

　　但是，一个表如果建有大量索引会影响 INSERT、UPDATE、DELETE 和 MERGE 语句的性能，因为当表中的数据更改时，所有索引都须进行适当的维护。

　　避免对经常更新的表进行过多的索引。

（2）索引尽可能窄

　　避免添加不必要的列。添加太多索引列可能对磁盘空间和索引维护性能产生负面影响。

3. 唯一索引

　　检查列的唯一性。在同一个列组合的唯一索引而不是非唯一索引提供了有关使索引更有用的查询优化器的附加信息。 

4. 聚集索引优化

　　请保持较短的索引键长度。另外，对唯一列或非空列创建聚集索引可以使聚集索引获益。

二、从查询的角度

　　设计索引时，应考虑以下查询准则：

（1）小表的索引

　　对小表进行索引可能不会产生优化效果，因为查询优化器在遍历用于搜索数据的索引时，花费的时间可能比执行简单的表扫描还长。因此，小表的索引可能从来不用，却仍必须在表中的数据更改时进行维护。

（2）索引覆盖

　　为经常用于查询中的谓词和联接条件的所有列创建非聚集索引。

　　涵盖索引可以提高查询性能，因为符合查询要求的全部数据都存在于索引本身中。也就是说，只需要索引页，而不需要表的数据页或聚集索引来检索所需数据，因此，减少了总体磁盘 I/O。例如，对某一表（其中对列 a、列 b 和列 c 创建了组合索引）的列 a 和列 b 的查询，仅仅从该索引本身就可以检索指定数据。

（3）降低索引维护的开销

　　将插入或修改尽可能多的行的查询写入单个语句内，而不要使用多个查询更新相同的行。仅使用一个语句，就可以利用优化的索引维护。

（4）索引的效率

　　评估查询类型以及如何在查询中使用列。例如，在完全匹配查询类型中使用的列就适合用于非聚集索引或聚集索引。

　　在列中检查数据分布。通常情况下，为包含很少唯一值的列创建索引或在这样的列上执行联接将导致长时间运行的查询。这是数据和查询的基本问题，通常不识别这种情况就无法解决这类问题。例如，如果物理电话簿按姓的字母顺序排序，而城市里所有人的姓都是 Smith 或 Jones，则无法快速找到某个人。

（5）索引中列的顺序

　　如果索引包含多个列，则应考虑列的顺序。用于等于 (=)、大于 (>)、小于 (<) 或 BETWEEN 搜索条件的 WHERE 子句或者参与联接的列应该放在最前面。其他列应该基于其非重复级别进行排序，就是说，从最不重复的列到最重复的列。

　　例如，如果将索引定义为 LastName、FirstName，则该索引在搜索条件为 WHERE LastName = 'Smith' 或 WHERE LastName = Smith AND FirstName LIKE 'J%' 时将很有用。不过，查询优化器不会将此索引用于基于 FirstName (WHERE FirstName = 'Jane') 而搜索的查询。

索引优化（6）筛选索引 

一、概念

　　筛选索引是一种经过优化的非聚集索引，尤其适用于涵盖从定义完善的数据子集中选择数据的查询。筛选索引使用筛选谓词对表中的部分行进行索引。

二、优势

　　筛选索引与全表索引相比具有以下优点：

（1）提高了查询性能和计划质量

　　设计良好的筛选索引可以提高查询性能和执行计划质量，因为它比全表非聚集索引小并且具有经过筛选的统计信息。与全表统计信息相比，经过筛选的统计信息更加准确，因为它们只涵盖筛选索引中的行。

（2）减少了索引维护开销

　　仅在数据操作语言 (DML) 语句对索引中的数据产生影响时，才对索引进行维护。与全表非聚集索引相比，筛选索引减少了索引维护开销，因为它更小并且仅在对索引中的数据产生影响时才进行维护。筛选索引的数量可以非常多，特别是在其中包含很少受影响的数据时。同样，如果筛选索引只包含频繁受影响的数据，则索引大小较小时可以减少更新统计信息的开销。

（3）减少了索引存储开销

　　在没必要创建全表索引时，创建筛选索引可以减少非聚集索引的磁盘存储开销。可以使用多个筛选索引替换一个全表非聚集索引而不会明显增加存储需要。

三、设计注意事项

　　为了设计有效的筛选索引，必须了解应用程序使用哪些查询以及这些查询与您的数据子集有何关联。例如，所含值中大部分为 NULL 的列、含异类类别的值的列以及含不同范围的值的列都属于具有定义完善的子集的数据。以下设计注意事项提供了筛选索引优于全表索引的各种情况。

1. 数据子集的筛选索引

　　在列中只有少量相关值需要查询时，可以针对值的子集创建筛选索引。例如，当列中的值大部分为 NULL 并且查询只从非 NULL 值中进行选择时，可以为非 NULL 数据行创建筛选索引。由此得到的索引与对相同键列定义的全表非聚集索引相比，前者更小且维护开销更低。

　　例如，AdventureWorks2008R2 数据库中有一个包含 2679 行的 Production.BillOfMaterials 表。EndDate 列只有 199 行包含非 NULL 值，其余 2480 行均包含 NULL。下面的筛选索引将涵盖这样的查询：返回在此索引中定义的列的查询，以及只选择 EndDate 值不为 NULL 的行的查询。

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsWithEndDate'
    AND object_id = OBJECT_ID (N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials
GO
CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL ;
GO

SELECT ProductAssemblyID, ComponentID, StartDate 
FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL 
    AND ComponentID = 5 
    AND StartDate > '01/01/2008' ;
GO

2. 异类数据的筛选索引

　　表中含有异类数据行时，可以为一种或多种类别的数据创建筛选索引。

　　例如，Production.Product 表中列出的每种产品均分配到一个 ProductSubcategoryID，后者又与 Bikes、Components、Clothing 或 Accessories 产品类别关联。这些类别为异类类别，因为它们在 Production.Product 表中的列值并不是紧密相关的。例如，对于每种产品类别，Color、ReorderPoint、ListPrice、Weight、Class 和 Style 均具有唯一特征。假设会经常查询具有子类别 27-36 的 Accessories。通过对 Accessories 子类别创建筛选索引，可以提高对 Accessories 的查询的性能。

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIProductAccessories'
    AND object_id = OBJECT_ID ('Production.Product'))
DROP INDEX FIProductAccessories
    ON Production.Product;
GO
CREATE NONCLUSTERED INDEX FIProductAccessories
    ON Production.Product (ProductSubcategoryID, ListPrice) 
        Include (Name)
WHERE ProductSubcategoryID >= 27 AND ProductSubcategoryID <= 36;
GO
SELECT Name, ProductSubcategoryID, ListPrice
FROM Production.Product
WHERE ProductSubcategoryID = 33 AND ListPrice > 25.00 ;
GO

　　筛选索引 FIProductAccessories 涵盖上面的查询，因为查询结果包含在该索引中，并且查询计划不包括基表查找。例如，查询谓词表达式 ProductSubcategoryID = 33 是筛选索引谓词 ProductSubcategoryID >= 27 和 ProductSubcategoryID <= 36 的子集，查询谓词中的 ProductSubcategoryID 和 ListPrice 列全都是索引中的键列，并且名称作为包含列存储在索引的叶级别。

本文由职坐标整理并发布，希望对同学们学习SQL Server有所帮助，更多内容请关注职坐标数据库SQL Server数据库频道！