Sql Server查询性能优化之索引篇【推荐】

Sql Server查询性能优化之索引篇【推荐】

这篇是索引系列中比较完整的，经过整理而来的

一 索引基础知识

索引概述

1、概念

　　可以把索引理解为一种特殊的目录。就好比《新华字典》为了加快查找的速度，提供了几套目录，分别按拼音、偏旁部首、难检字等排序，这样我们就可以方便地找到需要的字。
　　与书中的索引一样，数据库中的索引使您可以快速找到表或索引视图中的特定信息。索引包含从表或视图中一个或多个列生成的键，以及映射到指定数据的存储位置的指针。通过创建设计良好的索引以支持查询，可以显著提高数据库查询和应用程序的性能。索引可以减少为返回查询结果集而必须读取的数据量。索引还可以强制表中的行具有唯一性，从而确保表数据的数据完整性。

2. 分类

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

3. 认识索引的二叉树(索引B树)及级数

　　SQL Server中所有的索引都是平衡二叉树结构，平衡树的意思是所有叶子节点到根节点的距离都相同，SQL Server进行索引查找时总是从索引的根节点开始，并从根跳到下一级的相应页，并继续从一个级别跳到下一个级别，直到达把可以查找键的叶子页。所有叶级节点到底跟的距离都是相同的，这意味着一次查找操作在叶读取上的成本正好是页的级数。索引级数大多为2级到4级，2级索引大约包含几千行，3级索引大约4 000 000行，4级索引能容纳约4 000 000 000 行，这点上聚集索引和非聚集索引上是一样的。一般来说对于小表来说索引通常只有2级，对于大表通常包含3级或4级。索引是按照B树结构组织的。如下图。

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

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

页是SQL Server存储数据的基本单位，大小为8KB。　　请一定要记住，页是SQL Server进行数据读写的最小I/O单位，而不是行。SQL Server中一个页大小为8KB，每8个页形成一个区，每页8KB，其中页头占用96个字节，页尾的行指示器占用2个字节，还有几个保留字节，也就是一个页8192个字节，能用了存储数据的实际约8000个字节，8000个字节一般可以存储很多行数据。即便SQL Server只访问一行数据，它也要把整个页加载到缓存并从缓存读取数据，我们通常所说的开销主要就是I/O开销，这点不少人都没有清醒的认知。

索引级数也就意味着一次索引查找的最小开销，比如我们建立一个User表

1.  
   CREATE TABLE Users
2.  
   (
3.  
   UserID INT IDENTITY,
4.  
   UserName nvarchar(50),
5.  
   　　 Age INT,
6.  
   　　 Gender BIT,
7.  
   CreateTime DateTime
8.  
   )
9.  
    
10.  
    --在UserID列上创建聚集索引 IX_UserID
11.  
    CREATE UNIQUE CLUSTERED INDEX IX_UserID ON dbo.Users(UserID)
12.  
    --插入示例数据
13.  
    insert into Users(UserName,Age,Gender,CreateTime)
14.  
    select N'Bob',20,1,'2012-5-1'
15.  
    union all
16.  
    select N'Jack',23,0,'2012-5-2'
17.  
    union all
18.  
    select N'Robert',28,1,'2012-5-3'
19.  
    union all
20.  
    select N'Janet',40,0,'2012-5-9'
21.  
    union all
22.  
    select N'Michael',22,1,'2012-5-2'
23.  
    union all
24.  
    select N'Laura',16,1,'2012-5-1'
25.  
    union all
26.  
    select N'Anne',36,1,'2012-5-7'

我们执行查询

SELECT * FROM dbo.Users WHERE UserID=1
可以看到输出信息为

(1 行受影响)
表 'Users'。扫描计数 0，逻辑读取 2 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
当表数据量增多至几千条时，执行上述查询逻辑读依然为2，当数据量到达百万级别时逻辑读会变成3，当到达千万、亿级别时，逻辑读就会变成4，有兴趣的童鞋可以亲自去试验下。

认识窄索引

　　很多书籍和文章都写过索引要使用窄索引，窄索引是个什么东东呢，大白话就是把索引建在字节长度比较小的列上，比如int占用4个字节,bigint占用8个字节,char(20)占用20个字节nchar(20)最占用40个字节，那么int 相对于bigint来说就是窄了(占用字节数更少)，bigint比char(20)也要窄,char(20)和nchar(20)相比要窄(nchar(20)每个字符占用2个字节)。

　　明白了啥是窄索引我们来说下为什么要使用窄索引，我们知道数据存储和读取的最小单位是页，一个页8K大小，当使用比较窄的列做索引列时，每个页能存储的数据就更多，以int和bigint为例，一个8K的页大约能存储8*1024/4(int 4个字节)=2048(实际值要比这个数字小)条数据，使用bigint大约能存储8*1024/8(bigint为8个字节)=1024(实际值要比这个数字小)条数据，也就是说索引列的长度也小，每个页能存储的数据也就越多，反过来说就是存储索引所需要的页数也就越少，页数少了进行索引查找时需要检索的页自然也就少了，检索页数少了IO开销也就随之减少，查询效率自然也就高了。

认识书签查找的开销

当使用非聚集索引时，若查询条件没有实现索引覆盖就会形成书签查找，那么一次书签查找的开销是多少呢？答案是不一定，一般为1到4次逻辑读，对于堆表(无聚集索引的表)来说，一次书签查找只需要一次逻辑读，对于非堆表(有聚集索引的表)来说一次书签查找的逻辑读次数为聚集索引的级数，在索引结构我们简单说了下大多数聚集索引的级数为2-4级，也就是说对于非堆表来说一次逻辑读的开销为2-4次逻辑读，下面我们做具体测试

创建非聚集索引 IX_UserName

CREATE INDEX IX_UserName ON dbo.Users(UserName)
执行查询

SELECT UserID,UserName FROM dbo.Users WITH(INDEX(IX_UserName))WHERE UserName =('Bob')
由于我们的查询实现了索引覆盖，没有书签查找，获取一条数据需要2次逻辑读



(1 行受影响)
表 'Users'。扫描计数 1，逻辑读取 2 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

(1 行受影响)
修改查询，返回列中增加CreateTime，这样就无法实现索引覆盖

SELECT UserID,UserName,CreateTime FROM dbo.Users WITH(INDEX(IX_UserName))WHERE UserName =('Bob')
 可以看到这时发生了书签查找，同样返回一条数据，逻辑读达到了4次(索引查找2次，书签查找2次），由于我们的表Users为非堆表，故一次书签查找需要2次(聚集索引IS_UserID的级数为2)逻辑读



(1 行受影响)
表 'Users'。扫描计数 1，逻辑读取 4 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

(1 行受影响)
然后我们继续测试堆表的书签查找开销，删除表Users的聚集索引IX_UserID，使其变为堆表

DROP INDEX IX_UserID ON dbo.Users
再次执行我们的查询，可以看到逻辑读变成了3次（索引查找2次，书签查找1次），想想为什么堆表的书签查找次数少呢？

SELECT UserID,UserName,CreateTime FROM dbo.Users WITH(INDEX(IX_UserName))WHERE UserName =('Bob')



(1 行受影响)
表 'Users'。扫描计数 1，逻辑读取 3 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

(1 行受影响)
使用索引时的随机读

　　嗯，关于随机读我现在也有些迷糊，只是大概的知道发生随即读时即便需要的数据就存在同一页上，也会发生多次读取，而不是一次拿到整页数据后进行筛选。当进行where in查找或发生书签查找时，一定会使用随机读

首先我们看看聚集索引的随即读表现

--创建聚集索引IX_UserID
CREATE UNIQUE CLUSTERED INDEX IX_UserID ON dbo.Users(UserID)
执行如下查询，可以发现在聚集索引上面使用where in时不管有没有找到记录都会进行聚集索引查找，而且查找次数固定为where in里面的条件数*索引级数，不知道为什么明明索引扫描有着更高的效率，查询优化器还是选择聚集索引查找，有知道的朋友还请告知下哈


--这个聚集索引扫描，返回记录7条，逻辑读2次
SELECT * FROM dbo.Users
--这个聚集索引查找，返回记录3条，逻辑读2次
SELECT * FROM dbo.Users WHERE UserID<=2
--这个聚集索引查找，返回记录3条，逻辑读6次
SELECT * FROM dbo.Users WHERE UserID IN(1,2)
--这个聚集索引查找，返回记录0条，逻辑读6次
SELECT * FROM dbo.Users WHERE UserID IN(10,20,22)


(7 行受影响)
表 'Users'。扫描计数 1，逻辑读取 2 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

(1 行受影响)

(3 行受影响)
表 'Users'。扫描计数 1，逻辑读取 2 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

(1 行受影响)

(3 行受影响)
表 'Users'。扫描计数 3，逻辑读取 6 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

(1 行受影响)

(0 行受影响)
表 'Users'。扫描计数 3，逻辑读取 6 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

(1 行受影响)

我们再来看下非聚集索引的随机读，当然我这里为了演示特意使用了索引提示，实际应用中没事千万别加索引提示，当使用非聚集索引时查询优化器发现使用索引后效率更低时会放弃索引转为使用表扫描，我们这个例子中若去掉索引提示的话使用表扫描仅需要2次逻辑读就可以完成查询，这里仅仅是为了演示

--非聚集索引查找，返回记录2,逻辑读2
SELECT UserID,UserName FROM dbo.Users WITH(INDEX(IX_UserName))WHERE UserName  LIKE 'J%'
--非聚集索引查找，返回记录2，逻辑读4
SELECT UserID,UserName FROM dbo.Users WITH(INDEX(IX_UserName))WHERE UserName IN('Bob','Jack')

--非聚集索引查找+书签查找，返回记录2,逻辑读6
SELECT UserID,UserName,CreateTime FROM dbo.Users WITH(INDEX(IX_UserName))WHERE UserName  LIKE 'J%'

--非聚集索引查找+书签查找，返回记录0，逻辑读2(索引查找2*1+书签查找2*0）
SELECT UserID,UserName,CreateTime FROM dbo.Users WITH(INDEX(IX_UserName))WHERE UserName IN('Rabbit')
--非聚集索引查找+书签查找，返回记录1，逻辑读4(索引查找2*1+书签查找2*1）
SELECT UserID,UserName,CreateTime FROM dbo.Users WITH(INDEX(IX_UserName))WHERE UserName IN('Bob')
--非聚集索引查找+书签查找，返回记录2，逻辑读8(索引查找2*2+书签查找2*2）
SELECT UserID,UserName,CreateTime FROM dbo.Users WITH(INDEX(IX_UserName))WHERE UserName IN('Bob','Jack')

(2 行受影响)
表 'Users'。扫描计数 1，逻辑读取 2 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

(1 行受影响)

(2 行受影响)
表 'Users'。扫描计数 2，逻辑读取 4 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

(1 行受影响)

(2 行受影响)
表 'Users'。扫描计数 1，逻辑读取 6 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

(1 行受影响)

(0 行受影响)
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Users'。扫描计数 1，逻辑读取 2 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

(1 行受影响)

(1 行受影响)
表 'Users'。扫描计数 1，逻辑读取 4 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

(1 行受影响)

(2 行受影响)
表 'Users'。扫描计数 2，逻辑读取 8 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

(1 行受影响)

总结：

　　认识书签索引的查找开销、书签查找的开销、随机读的影响对我们具体创建索引和编写SQL有着积极的影响，毕竟对查询的的认识更加清楚了，我们在写索引和SQL时候才有更有针对性，最起码我们又知道了一个尽量不适用where in的理由，为什么要尽量规避书签查找，聚集索引查找不见得就一定高效，顺便留个问题：聚集索引扫描和非聚集索引扫描哪个有着更高的效率，什么情况下会发生非聚集索引扫描？

数据库索引分为聚集索引和非聚集索引，聚集索引就是物理索引，也就是数据的物理的存储顺序，聚集索引的叶子节点就是数据行本身；非聚集索引是逻辑索引，也可以简单的认为是对聚集索引建立的索引，一般来说聚集索引的键就是非聚集索引的叶子节点(在不使用include时)。

堆的物理结构

　　SQL Server 的数据组织结构为HOBT（堆或平衡树），详见《SQL Server 数据文件存储结构》 http://jimshu.blog.51cto.com/3171847/987275。

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

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

堆上的索引

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

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

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

堆上的非聚集索引

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表示不返回所有的信息。

　　结果如下表所示：

index _depth	Index _level	Record _count	Page _count	MinLen	MaxLen	AvgLen	PageDensity
2	0	80000	179	16	16	16	99.37
2	1	179	1	22	22	22	53.05

 　　根据上表的数据，可以看到该索引共有2层。level=0 是叶级，它有179个页面，指向包含80000行数据的数据页；level=1 是根页，它只有1个页面，指向叶级的179个索引页。

　　叶级索引的行长度为16字节，它包括：4 个字节对应于 int 列（UserID列），8 个字节对应于数据行定位符（即堆 RID），1个字节对应于索引行的行标题开销，3个字节用于NULL位图（UserID列可以为空）。详细的计算方法，见《估计非聚集索引的大小》 http://technet.microsoft.com/zh-cn/library/ms190620.aspx

　　根页的行长度为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')

　　结果如下：

index _depth	Index _level	Record _count	Page _count	MinLen	MaxLen	AvgLen	PageDensity
1	0	80000	4000	399	399	399	99.06

6. 总结

堆上的（唯一、非空值）非聚集索引

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')

　　结果如下表所示：

index _depth	Index _level	Record _count	Page _count	MinLen	MaxLen	AvgLen	PageDensity
1	0	80000	4000	399	399	399	99.06
2	0	80000	179	16	16	16	99.37
2	1	179	1	11	11	11	53.05

　　根据上表的数据，可以看到该聚集索引共有2层（仅指index_depth=2的索引）。叶级索引的行长度为16字节不变。但根页的行长度从22字节降为11字节。

删除堆中的数据

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）才能释放堆中的空闲页面。

聚集索引和非聚集索引

关于索引的选择

　　对于索引类型来说没什么好选的，一般来说聚集索引是必须的（有特殊需要的另说），非聚集索引看实际需要灵活建立。因此对于索引来说主要是决定在那些列上建立索引，尤其是对于聚集索引这点非常重要。

聚集索引

一、聚集索引的概念

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

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

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

　　聚集索引作为最重要的索引往往被我们所忽略，而其最大的优势就是大范围数据查询有着较高的效率，因此聚集索引列的选择往往对数据库性能有着灰常大的影响。为了尽量发挥聚集索引在大范围数据查找上的优势，推荐按以下顺序选择聚集索引列。

聚集索引字段选择优先级：时间字段>>会进行大范围查询的列>>具有唯一值的有实际意义的字段>>自增列ID

　　1.时间字段：若表里面有时间列，并且时间是按照数据插入顺序增长时（时间无需唯一即可有重复值，哪怕是大范围重复），建议采用时间列作为聚集索引的第一选择。理由：聚集索引有一个巨大的优势就是进行大范围数据查找，而且这个优势会随着数据量的增加而越来越明显，一般来说我们需要进行大数据量范围查询时都会用时间列围作为筛选条件，由于聚集索引不存在书签查找而且可以进行连续扫描，因此查询速度会非常快。时间列数据最好是顺序插入的这样可以尽量减少磁盘碎片，是数据存储相对集中，便于连续数据读取。

　　2.会进行大范围查询的列：若表里面没有时间字段或者时间字段不适合做聚集索引，可以选择那些在建表时就明确知道会经常进行大范围数据筛选的列，而且最好是选择性较低的列(即有较多重复值的列，性别这种列不算啦)，如有必要可以使用组合索引。理由：聚集索引在数据查询的优势主要在于范围数据查找，把聚集索引弄成唯一的把这个大好优势给白白浪费了。

　　3.具有唯一值的有实际意义的字段：若找不到适合条件1、2的列，那还是乖乖的把聚集索引列建立在唯一列上吧，最好找那种有实际意义的具有唯一性的列，比如订单表可以用订单号作聚集索引，订单明细表使用订单号和产品编号做联合聚集索引。理由：找不到合适的时间字段和较低选择性字段的话，把主键建成聚集索引是我们大多情况下的选择。

　　这里建议把唯一性的聚集索引顺便建成主键，和编码时方法、变量命名一样，推荐列名自解释，即看到列名就知道它就是主键，省得你再去猜，比如订单表你来个自增ID列做主键，再建一个OrderCode列做订单号，用这个表时你得怀疑这个OrderCode是不是唯一滴呢，有木有建立唯一约束呢，同理在订单明细表来个自增列ID也会产生如此疑问，产生疑问还是小事，若是你忘记了在应该唯一的列上建立约束，没准哪天程序控制不好给你个巨大的惊喜。

　　4.自增列ID：前面3中条件都找不到合适的列了还是使用我们的神器自增列ID吧，自增列ID也是我们使用最多的主键(顺便也就成聚集索引了)，而且能较好满足我们大多数需求。自增ID列堪称无所不能，int类型只占用4个字节完全满足窄索引要求，绝对的顺序存储可以有效降低索引碎片，完全符合我们的见表习惯，有用没用来个自增ID列做主键总是没错滴。

　　这里考虑聚集索引的键列主要为查询考虑，有些观点认为应该总是把聚集索引建立唯一列上，这里不敢苟同，诚然有些特殊情况下确实需要这么做，但大说情况下还是建立在选择性较低的列、时间列上比较好，这样才能发挥聚集索引在范围数据查找方面的巨大优势。关于聚集索引在列上重复数据SQL Server需要额外的建立唯一标示用以定位造成查询时的额外开销非常小，小到与其带来范围查找的优势而言完全可以忽略。

　　当然了在选择列时要尽量使用窄索引，也只是尽量而已，主要还是看付出的代价还获得的收益，若有足够的收益啥玩意都可以滴。记住我们滴目标是利用聚集索引提高大范围查询效率。

（非唯一）聚集索引

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')

　　结果如下：

Index _depth	Index _level	Record _count	Page _count	MinLen	MaxLen	AvgLen	PageDensity
3	0	80000	4009	399	407	401.498	99.45
3	1	4009	10	14	22	16.632	92.26
3	2	10	1	14	22	18	2.45

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

　　中间级索引的行长度为14~22个字节，根级索引的行长度也为14~22 个字节。详细的计算方法，见《估计聚集索引的大小》http://technet.microsoft.com/zh-cn/library/ms178085.aspx

4. 总结

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

（唯一）聚集索引

　　聚集索引中的数据即可以唯一，也可以不唯一，取决于定义这个索引时的 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')

　　结果为：

Index _depth	Index _level	Record _count	Page _count	MinLen	MaxLen	AvgLen	PageDensity
3	0	80000	4000	399	399	399	99.06
3	1	4000	7	11	11	11	91.75
3	2	7	1	11	11	11	1.10

　　与前一个实验中的不带UNIQUE 的聚集索引相比，由于不需要创建一个 4 字节的uniqueifier 列，因此唯一聚集索引的数据行仍然是原来的固定长度，数据页的数量也不会增加。而且，由于数据行的唯一性，非叶级索引的宽度也减小到11个字节，其中4个字节用于定义聚集索引的 int 列（UserID列），6个字节用于页指针，1个字节用于行的系统开销。

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. 约束与索引的主要区别

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

 

非聚集索引

　　与聚集索引不同，非聚集索引可以建立多个，这也给我们带来了很大的灵活，毕竟聚集索引就那么一个不可能靠它满足所有需求，更多的我们得依赖非聚集索引。记住非聚集索引不是大白菜，你想键多少就建多少，建立索引是有代价的，任何涉及到索引列的数据修改都会导致索引的修改，索引越多数据的曾、删、改的额外代价也就越大。对于非聚集索引来说，我们的目标是用尽可能少的索引覆盖尽可能多的查询。

        SQL Server缺省情况下建立的索引是非聚簇索引，由于非聚簇索引不重新组织表中的数据，而是对每一行存储索引列值并用一个指针指向数据所在的页面。换句话说非聚簇索引具有在索引结构和数据本身之间的一个额外级。一个表如果没有聚簇索引时,可有250个非聚簇索引。每个非聚簇索引提供访问数据的不同排序顺序。在建立非聚簇索引时，要权衡索引对查询速度的加快与降低修改速度之间的利弊。另外，还要考虑这些问题：

　　1、索引需要使用多少空间。

　　2、合适的列是否稳定。

　　3、索引键是如何选择的，扫描效果是否更佳。

　　4、是否有许多重复值。

　　对更新频繁的表来说，表上的非聚簇索引比聚簇索引和根本没有索引需要更多的额外开销。对移到新页的每一行而言，指向该数据的每个非聚簇索引的页级行也必须更新，有时可能还需要索引页的分理。从一个页面删除数据的进程也会有类似的开销，另外，删除进程还必须把数据移到页面上部，以保证数据的连续性。所以，建立非聚簇索引要非常慎重。非聚簇索引常被用在以下情况: 

　　1、某列常用于集合函数(如Sum,....)。

　　2、某列常用于join,order by,group by。

　　3、查寻出的数据不超过表中数据量的20%。

非聚集索引的列选择顺序(组合索引):经常被使用为查询条件列>>具有较高选择性的列(选择性越高越好，唯一最好)>>经常排序的列

　　1.经常被使用为查询条件列：我们的查询千变万化，建立索引时要首先考虑有哪些列被经常性的用于各种查询，把使用频率较高的列作为组合索引的第一列(先导列)，若一个查询中没有用到组合索引中的先导列，多数情况下这个索引就不会被使用，因此为了尽可能多的复用组合索引把使用较多的查询列作为组合索引的第一列吧。(关于这点对于聚集索引的组合索引同样适用)

　　2.具有较高选择性的列：这点很简单尽量使用高选择性列作为先导列，如果可以通过第一个条件过滤(随便什么判定逻辑=、>、<、like)，只要能大幅减少数据范围，就把它作为先导列。

　　3.条件1、2、3都确定不了时那就用经常被排序的列吧，我们的很多操作都需要先进行排序才可以进行进一步查询，比如group by，like等操作都是要先进行排序操作才可以完成下一步查询。

　　补充一点：可以把经常被返回的列放到索引的include里面去，在不增加索引键大小的情况下尽可能覆盖尽可能多的列，这样当遇到某些查询，没有用到组合索引的先导列，但又感觉不值得为其建立索引时，若此查询用到得字段被组合索引实现了索引覆盖，可以进行非聚集索引扫描完成查询（当非聚集索引实现了索引覆盖时，进行非聚集索引扫描有着比聚集索引扫描更好的效率）。

　　下面我们用一些示例来简单说明下　　

1.  
   CREATE TABLE Orders
2.  
   (
3.  
   ID INT IDENTITY PRIMARY KEY,--自增列ID做主键，这样创建默认就成了聚集索引
4.  
   OrderCode BIGINT NOT NULL,--订单号
5.  
   Price DECIMAL(18,2) NOT NULL,--订单金额
6.  
   UserID INT NOT NULL,--用户ID
7.  
   [Status] INT NOT NULL,--订单状态
8.  
   PostTime DATETIME NOT NULL,--下单时间
9.  
   Moblie CHAR(11) NOT NULL,--用户手机号
10.  
    [Address] NVARCHAR(200) NOT NULL--收获地址
11.  
    )

创建一个订单表，按照我们的习惯有用没用来个自增列ID做主键，随随便便也就建立了聚集索引，现在我们来看一下，对于订单表来我们一般都会一组规则生成订单号，而不是简单的使用自增ID，因此我们创建了OrderCode用作订单号，当然了订单号必须是唯一的，因此需要创建唯一约束，过了些日子有其它人用到订单表或你自己用这个表，难免就会有些疑惑，OrderCode需要唯一，在这个表里到底是不是唯一的呢，于是乎你首先查看OrderCode上面是否建立了唯一约束，然后知道OrderCode就是唯一的，这也没啥，但是来个人都要查一遍，过段时间忘了还得再确认一次，很是麻烦，再看看我们那个主键ID，他神马都没干，就在那里呆着，现在拿掉它，于是表变为

1.  
   CREATE TABLE Orders
2.  
   (
3.  
   OrderCode BIGINT NOT NULL PRIMARY KEY,--订单号
4.  
   Price DECIMAL(18,2) NOT NULL,--订单金额
5.  
   UserID INT NOT NULL,--用户ID
6.  
   [Status] INT NOT NULL,--订单状态
7.  
   PostTime DATETIME NOT NULL,--下单时间
8.  
   Moblie CHAR(11) NOT NULL,--用户手机号
9.  
   [Address] NVARCHAR(200) NOT NULL--收获地址
10.  
    )

现在不管谁、什么时候看到这个表基本上不会怀疑OrderCode是否唯一了，一个不起眼的小改进，带来了很大的便利，所以主键自解释很有必要的

我们看一下以下几个可能经常用到的查询

--查询1：指定用户特定时间内的所有订单
SELECT * FROM dbo.Orders WHERE UserID=1 AND PostTime BETWEEN '2012-6-1' AND '2012-6-30'
--查询2：指定用户的单个订单
SELECT * FROM dbo.Orders WHERE UserID=1 AND OrderCode=22222222222
--查询3：指定用户特定时间内特定状态的订单
SELECT * FROM dbo.Orders WHERE UserID=1 AND Status=1 AND PostTime BETWEEN '2012-6-1' AND '2012-6-30'
--查询4：指定时间内所有的订单
SELECT * FROM dbo.Orders WHERE PostTime BETWEEN '2012-6-1' AND '2012-6-30' AND Status=1

为了最优查询速度有可能会创建以下索引

索引1：
CREATE INDEX IX_UserIDPostTime ON dbo.Orders(UserID,PostTime)
索引2：
CREATE INDEX IX_UserIDOrderCode ON dbo.Orders(UserID,OrderCode)
索引3：
CREATE INDEX IX_UserIDStatusPostTime ON dbo.Orders(UserID, Status,PostTime)
索引4：
CREATE INDEX IX_PostTimeStatus ON dbo.Orders(PostTime,Status)

最悲观的情况下上面4个索引可能同时存在，为每一个查询建立对应的索引固然可行，但代价未免太大，别忘了索引不是大白菜。因此我们应尽可能的用少的索引覆盖多的查询。来看下上面的索引，如果只创建了索引1，那么只有查询1、3能从索引1受益，查询4没用用到索引1的先导列故不会用到索引1，查询2由于聚集索引存在根本不需要额外的非聚集索引。而索引2由于聚集索引的存在更是完全没必要存在，因此首先干掉索引2。再看索引3，索引3可以覆盖查询1和查询3、查询4，但由于索引列顺序问题使其在应对查询4时基本无效，对查询1虽然有效但效果不尽如人意，我们对索引3做下简单调整，把PostTime列和Status列顺序互换，修改后索引3对原查询3基本没影响，而且对查询1的效率提升也达到最大化

修改后的索引3：
CREATE INDEX IX_UserIDPostTimeStatus ON dbo.Orders(UserID,PostTime,Status)
现在索引3可以很好的完成查询1和查询3，因此索引1现在可以删除掉，现在只剩索引3和索引4了，我们可以看到修改后的索引3由于先导列问题依然无法用于查询4，为了使索引3用于查询4我们再次修改索引3，把PostTime放到索引的第一列，其它列保持顺序不变

再次修改后的索引3：
CREATE INDEX IX_PostTimeUserIDStatus ON dbo.Orders(PostTime,UserID,Status)
可以看到现在索引3也可以有效的用于查询4了，但是由于先导列原因若将Status列和UserID列换货，固然可以提高查询4效率但是会影响查询1，我们考虑到Status列一般也就几种状态，多了也就几十种，相对于UserID来说选择性低高，因此还是把选择性较高的UserID列放在前面，最大化查询1和查询3查询效率。再来看最后一个索引4，索引4和对查询1和查询4起效果，由于查询1已有索引3可用，故忽略对查询1的作用，现在只剩下对查询4的起作用，我们看查询4，索引3和索引4都对其产生效果，毫无疑问索引4对查询4效果更大一些，但考虑到Status列的低选择性和多维护一个索引的代价，索引3已能较好的完成查询4，所以删除索引4。

　　这样一来针对Orders表的4个查询，我们经过对原来4个索引的优化调整后只保留了修改后的索引3，索引从4个变成一个，而查询效率方面却没有受较大的影响，达到了用尽可能少的索引完成尽可能多的查询的目的。

　　在上面的演示中我们使用了OrderCode做为聚集索引，通过对非聚集索引的调整较好的完成了查询，在大说数情况下这样就可以ok了，现在我们考虑下若Orders表数据量较大，执行我们的查询4若返回结果达到几万、几十万甚至更多的时候，很可能会导致索引失效从而发生表扫描，这时除非我们队查询4使用的索引实现索引覆盖，不过这基本上不大现实。那如何解决这个问题呢？这时时间列上的聚集索引就开始显示威力了，修改我们的Orders表将聚集索引建立到PostTime列上

--删除原来的聚集索引主键 PK_Orders
ALTER TABLE dbo.Orders DROP CONSTRAINT PK_Orders

--创建非聚集索引主键 PK_Orders 
ALTER TABLE dbo.Orders ADD CONSTRAINT PK_Orders PRIMARY KEY NONCLUSTERED(OrderCode)

--在时间列PostTime上创建聚集索引
CREATE CLUSTERED INDEX IX_PostTime ON dbo.Orders(PostTime)

修改后我们担心的书签查找问题彻底消失了，因为通常导致索引失效的原因都是过多的书签查找，发生大量书签查找时基本上和范围查询脱不开关系，大范围数据查询又肯定会用到时间列，所以推荐时间列做聚集索引。至于说修改了聚集索引后更新，订单查询效率会不会下降，对于这点基本上可以忽略，进行订单查询时一般数据量都会较小，那点书签查找开销完全可以忽略掉，比如我们的查询2。

　　这时我们可能发现我们上面修改的索引3有个尴尬的现状，索引3的先导列PostTime现在成了聚集索引，那么索引3对查询1、查询3、查询4还有木有用，索引3还有木有存在的必要，关于这点一般来说呢非聚集索引最好不要用聚集索引做先导列，很可能建了等于白建这里只是个建议，那么若删除了索引3，查询1、查询3、查询4效率会如何，查询4效率肯定是会提升的、查询1、查询3需要实际测试，当然若可以的话把聚集索引建成联合的，把UserID放进去就不会有此问题了

--在时间列PostTime和UserID列上创建聚集索引
CREATE CLUSTERED INDEX IX_PostTimeUserID ON dbo.Orders(PostTime,UserID)
当然了这种修改还得小心进行，根据实际需求灵活修改，理想的情况下是建立专门的只读数据库复制，在只读库上建立最适合范围查找的聚集索引，在主库上建立最有利于增、删、改的聚集索引，对实时性要求不高的查询全部转移到只读库上执行，相对而言需要大范围数据筛选的查询都不需要多好的实时性，尽可到只读库执行，而主库呢肯定是主要执行实时性要求高的小数据量查询。

总结：

　　聚集索引的优势在于大范围数据查询效率，因此需要将聚集索引建立在时间列、选择性相对较低并且经常会用于范围查询的列(选择性过低的如性别列肯定不行，过低的选择性列索引建了等于白建，比如你在性别列上集索引以为通过性别列起码一下过滤掉一半数据，范围大大减小你就大错特错了，这点选择性通常查询优化器会直接忽略掉，还不如个表扫描来的快），充分发挥聚集索引大范围数据查询优势。

　　非聚集索引要尽量使用选择性较高的列以尽可能减少返回的数据量，利用组合索引提高索引的复用率，不要建过多的无用索引，如果发现某个表建了很多的非聚集索引，不妨把那些索引、查询摘出来分析合并下，减少没用索引的数量，以提高整体性能。

　　索引建立还需根据实际需要进行选择，本文所述观点在能够适用于大多数情况，但建立好的索引不是一朝一夕能够做到的，理论上成立的事实际应用中往往会事与愿违，索引的有效性还要依靠数据库统计信息等综合考虑，故每当建立索引后一定要查看下查询计划，查看下IO开销，看看查询优化器是否按照我们预期的方式使用了索引。

聚集索引上的非聚集索引

 

索引结构

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

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

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

实验

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

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')

　　结果如下表所示： 

index _depth	Index _level	Record _count	Page _count	MinLen	MaxLen	AvgLen	PageDensity
2	0	80000	179	16	16	16	99.36
2	1	179	1	22	22	22	53.05

 　　根据上表的数据，可以发现它与堆上的非聚集索引的数据是一样的。该索引共有2层。level=0 是叶级，它有179个页面，指向底层的聚集索引的根页；level=1 是这个非聚集索引的根页，它只有1个页面，指向叶级的179个索引页。

比较三类索引占用的页数

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

1. 堆

　　堆是最原始的结构，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 页。

覆盖索引(covering indexes)的使用

　　覆盖索引是指那些索引项中包含查寻所需要的全部信息的非聚簇索引，这种索引之所以比较快也正是因为索引页中包含了查寻所必须的数据,不需去访问数据页。如果非聚簇索引中包含结果数据,那么它的查询速度将快于聚簇索引。

　　但是由于覆盖索引的索引项比较多,要占用比较大的空间。而且update操作会引起索引值改变。所以如果潜在的覆盖查询并不常用或不太关键，则覆盖索引的增加反而会降低性能。

索引碎片

一、碎片的产生

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 检查碎片程度

http://technet.microsoft.com/zh-cn/library/ms175008.aspx

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 判断碎片程度

http://technet.microsoft.com/zh-cn/library/ms188917.aspx

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，将删除表中的所有索引，然后在单个事务中重新生成。http://technet.microsoft.com/zh-cn/library/ms188388.aspx

　　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中消除了这个限制，详见 http://stackoverflow.com/questions/6309614/what-is-the-difference-between-offline-and-online-index-rebuild-in-sql-server

　　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修改填充因子

索引设计指南

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

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

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') 而搜索的查询。

筛选索引

一、概念

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

二、优势

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

（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 列全都是索引中的键列，并且名称作为包含列存储在索引的叶级别。

结语：

　　筛选索引提高了查询性能和计划质量，减少了索引维护开销，还可以减少非聚集索引的磁盘存储开销。

索引选择

A:尽量避免表扫描检查你的查询语句的where子句，因为这是优化器重要关注的地方。包含在where里面的每一列（column)都是可能的侯选索引，为能达到最优的性能，考虑在下面给出的例子：对于在where子句中给出了column1这个列。下面的两个条件可以提高索引的优化查询性能！第一：在表中的column1列上有一个单索引第二：在表中有多索引，但是column1是第一个索引的列避免定义多索引而column1是第二个或后面的索引，这样的索引不能优化服务器性能例如：下面的例子用了pubs数据库。
SELECT au_id, au_lname, au_fname FROM authors WHERE au_lname = 'White' 按下面几个列上建立的索引将会是对优化器有用的索引
　　au_lname
　　au_lname, au_fname而在下面几个列上建立的索引将不会对优化器起到好的作用
　　au_address
　　au_fname, au_lname考虑使用窄的索引在一个或两个列上，窄索引比多索引和复合索引更能有效。用窄的索引，在每一页上将会有更多的行和更少的索引级别（相对与多索引和复合索引而言），这将推进系统性能。对于多列索引，SQL Server维持一个在所有列的索引上的密度统计（用于联合）和在第一个索引上的
histogram（柱状图）统计。根据统计结果，如果在复合索引上的第一个索引很少被选择使用，那么优化器对很多查询请求将不会使用索引。有用的索引会提高select语句的性能，包括insert,uodate,delete。但是，由于改变一个表的内容，将会影响索引。每一个insert,update,delete语句将会使性能下降一些。实验表明，不要在一个单表上用大量的索引，不要在共享的列上（指在多表中用了参考约束）使用重叠的索引。在某一列上检查唯一的数据的个数，比较它与表中数据的行数做一个比较。这就是数据的选择性，这比较结果将会帮助你决定是否将某一列作为侯选的索引列，如果需要，建哪一种索引。你可以用下面的查询语句返回某一列的不同值的数目。
　　select count(distinct cloumn_name) from table_name假设column_name是一个10000行的表，则看column_name返回值来决定是否应该使用，及应该使用什么索引。
Unique values Index
5000 Nonclustered index
20 Clustered index
3 No index
　　镞索引和非镞索引的选择
　　<1:>镞索引是行的物理顺序和索引的顺序是一致的。页级，低层等索引的各个级别上都包含实际的数据页。一个表只能是有一个镞索引。由于update,delete语句要求相对多一些的读操作，因此镞索引常常能加速这样的操作。在至少有一个索引的表中，你应该有一个镞索引。在下面的几个情况下，你可以考虑用镞索引：例如：某列包括的不同值的个数是有限的（但是不是极少的）顾客表的州名列有50个左右的不同州名的缩写值，可以使用镞索引。例如：对返回一定范围内值的列可以使用镞索引，比如用between,>,>=,<,<=等等来对列进行操作的列上。
select * from sales where ord_date between '5/1/93' and '6/1/93'例如：对查询时返回大量结果的列可以使用镞索引。

　　SELECT * FROM phonebook WHERE last_name = 'Smith'

　　当有大量的行正在被插入表中时，要避免在本表一个自然增长（例如，identity列）的列上建立镞索引。如果你建立了镞的索引，那么insert的性能就会大大降低。因为每一个插入的行必须到表的最后，表的最后一个数据页。当一个数据正在被插入（这时这个数据页是被锁定的），所有的其他插入行必须等待直到当前的插入已经结束。一个索引的叶级页中包括实际的数据页，并且在硬盘上的数据页的次序是跟镞索引的逻辑次序一样的。 

　　<2:>一个非镞的索引就是行的物理次序与索引的次序是不同的。一个非镞索引的叶级包含了指向行数据页的指针。在一个表中可以有多个非镞索引，你可以在以下几个情况下考虑使用非镞索引。在有很多不同值的列上可以考虑使用非镞索引例如：一个part_id列在一个part表中select * from employee where emp_id = 'pcm9809f'查询语句中用order by子句的列上可以考虑使用镞索引 
　　三、查询语句的设计
　　SQL Server优化器通过分析查询语句，自动对查询进行优化并决定最有效的执行方案。优化器分析查询语句来决定那个子句可以被优化，并针对可以被优化查询的子句来选择有用的索引。最后优化器比较所有可能的执行方案并选择最有效的一个方案出来。在执行一个查询时，用一个where子句来限制必须处理的行数，除非完全需要，否则应该避免在一个表中无限制地读并处理所有的行。例如下面的例子，select qty from sales where stor_id=7131是很有效的比下面这个无限制的查询select qty from sales避免给客户的最后数据选择返回大量的结果集。允许SQL Server运行满足它目的的函数限制结果集的大小是更有效的。这能减少网络I/O并能提高多用户的相关并发时的应用程序性能。因为优化器关注的焦点就是where子句的查询，以利用有用的索引。在表中的每一个索引都可能成为包括在where子句中的侯选索引。为了最好的性能可以遵照下面的用于一个给定列column1的索引。第一：在表中的column1列上有一个单索引第二：在表中有多索引，但是column1是第一个索引的列不要在where子句中使用没有column1列索引的查询语句，并避免在where子句用一个多索引的非第一个索引的索引。这时多索引是没有用的。
For example, given a multicolumn index on the au_lname, au_fname columns of the authors table in 
the pubs database,下面这个query语句利用了au_lname上的索引
SELECT au_id, au_lname, au_fname FROM authors
WHERE au_lname = 'White'
AND au_fname = 'Johnson'
SELECT au_id, au_lname, au_fname FROM authors
WHERE au_lname = 'White'下面这个查询没有利用索引，因为他使用了多索引的非第一个索引的索引
SELECT au_id, au_lname, au_fname FROM authors

WHERE au_fname = 'Johnson'

本文转自 

http://www.cnblogs.com/lzrabbit/archive/2012/06/11/2517963.html ; 

http://www.cnblogs.com/lzrabbit/archive/2012/07/03/2549558.html 

http://jimshu.blog.51cto.com/3171847/1254965

--- end ---