索引举例介绍

Description:

　查询优化器在从表中查询数据时，需要选择一个合适的访问模式，在决定使用哪一种索引，使用扫描还是查找，使用书签查询时，查询优化器要考虑许多因素，这些因素包括：

• 索引执行时，查找或扫描所需的I/O数
• 评估查询中的索引键是否是最佳
• 谓词的选择性（也就是说，相对于表中总记录数满足谓词的百分比）
• 索引是否覆盖所有列？

下面通过一个例子来介绍：

create table T (a int, b int, c int, d int, x char(200))

create unique clustered index Ta on T(a)

create index Tb on T(b)

create index Tcd on T(c, d)

create index Tdc on T(d, c)

插入一些数据：

set nocount on

declare @i int

set @i = 0

while @i < 100000

begin

insert T values (@i, @i, @i, @i, @i)

set @i = @i + 1

end

无Where条件

Select a,b FROM T，

该 查询不包含Where条件语句，而使用扫描，可是这里有两种索引可用：聚集索引(Ta)和非聚集索引(Tb)，这两个索引均覆盖a和b两列，另外，聚集索 引也覆盖c和x列．由于x列是字符型，长度为２００个字符，聚集索引的每一行总宽度超过了２００个字节，对于每一个８ＫＢ的页面，存储的行数也不超过４０ 行．而索引需要２５００个页来存储所有１０万行数据，与之相反的是，非聚集索引中每一行的总宽仅有８个字节，加一些头部信息，每一页可以存储上百行数据， 索引则需要不到２５０页来存储所有的１０万行数据．通过扫描非聚集索引，当执行查询时则需要较少的I/O操作．因而使用的最佳计划是：

|--Index Scan(OBJECT:([T].[Tb]))

我们也可以使用sys.dm_db_index_physical_stats视图来比较聚集索引与非聚集索引两者所使用的页数

select index_id, page_count

from sys.dm_db_index_physical_stats

(DB_ID('northwind'), OBJECT_ID('T'), NULL, NULL, NULL)

执行上述查询后，结果如下：

索引ID号	页数
1	2858
2	174
3	223
4	223

从输出结果可以看出，非聚集索引存储行所使用的页数明显小于聚集索引使用的页数．

当然我们也可以使用stats I/O和索引hints来比较聚集索引与非聚集索引的I/O数．

set statistics io on

select a, b from T with (index(Ta))

表'T'。扫描计数1，逻辑读取2872 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

select a, b from T with (index(Tb))

表'T'。扫描计数1，逻辑读取176 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

从stats I/O数可以看出，非聚集索引在获取数据时，读取较少的数据页．

索引的选择性

select a from T

where c > 150 and c < 160 and d > 100 and d < 200

此查询有两个不同的谓词用于索引查找，可以使用位于c列上的非聚集索引Tcd，也可以使用位于d列上的非聚集索引Tdc．

查询优化器通过查看两个谓词的选择性来确定使用哪一个索引，在c列上的谓词选择的行仅有９行，而在d列上则有９９行，显然使用索引Tcd来评估位于d列上的residual谓词比使用Tdc索引的I/O开销要小得多．

以下是该查询的计划：

|--Index Seek(OBJECT:([T].[Tcd]), SEEK:([T].[c] > (150) AND [T].[c] < (160)),

Where:([T].[d]>(100) AND [T].[d]<(200)) orDERED FORWARD)

索引查找与索引扫描示例

select a from T where a between 1001 and 9000

select a from T where a between 101 and 90000

其执I/O信息如下：

表'T'。扫描计数1，逻辑读取234 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

表'T'。扫描计数1，逻辑读取176 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

如您所预料的，对于第一个查询来说，查询优化器在a列上选择使用聚集索引来获取数据，以下是其的查询计划：

|--Clustered Index Seek(OBJECT:([T].[Ta]),

SEEK:([T].[a] >= CONVERT_IMPLICIT(int,[@1],0) AND [T].[a] <= CONVERT_IMPLICIT(int,[@2],0)) orDERED FORWARD)

注意：该计划中的两个参数是由自动参数化功能所生成的，当执行该计划时，@1参数为1001，@2参数为9000.

对于第二个查询来说，查询优化器却选择了非聚集索引来扫描数据，以下其查询计划：

|--Index Scan(OBJECT:([T].[Tb]), Where:([T].[a]>=(101) AND [T].[a]<=(90000)))

为 什么是这样呢？注意第一个查询选择的记录数有８千行（相对于１０万行数据而言），对于聚集索引来说，选择度为表的８％，约２３０个数据页，而第二查询选择 的记录数有８９０００行，选择度为表的约９０％，若使用聚集索引来读取89000行数据时，则需要读２５００个数据页．通过比较，非聚集索引仅需要读取 １７４个页面，查询优化器选择此计划，大大减少了I/O操作．

带书签查询的查询与扫描示例

select x from T where b between 101 and 200

select x from T where b between 1001 and 2000

对 于上述的两个查询而言，可以通过聚集索引直接扫描然后在列b上应用谓词，或者使用非聚集索引Tb在列b上执行索引查找，然后在聚集索引上执行书签查询来读 取满足x列值的行．（注意：书签查询采用的I/O开销比较大的方式是随机读．）对于查找的选择度高的书签查询，则是值得的．

以下是第一个包含书签查询的查询计划（仅需要读取１００行）：

|--Nested Loops(Inner Join, OUTER REFERENCES:([T].[a], [Expr1005]) ...)

|--Index Seek(OBJECT:([T].[Tb]), SEEK:([T].[b] >= (101) AND [T].[b] <= (200)) ...)

|--Clustered Index Seek(OBJECT:([T].[Ta]), SEEK:([T].[a]=[T].[a]) LOOKUP ...)

　　而第二个查询则读取１０００行，对于表而言，仅有１％．查询优化器由此推出，执行１０００次的随机读要比执行２８００次的顺序读的开销要大得多，第二个查询的计划如下：

|--Clustered Index Scan(OBJECT:([T].[Ta]), Where:([T].[b]>=(1001) AND [T].[b]<=(2000)))

源文出处:http://www.haixiait.com/article.asp?id=162