ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 索引示例介绍

索引示例介绍

原创 Linux操作系统 作者:bigholy 时间:2008-11-15 14:43:43 0 删除 编辑

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

l   索引执行时,查找或扫描所需的I/O

l   评估查询中的索引键是否是最佳

l   谓词的选择性(也就是说,相对于表中总记录数满足谓词的百分比)

l   索引是否覆盖所有列?

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

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),这两个索引均覆盖ab两列,另外,聚集索引也覆盖cx列.由于x列是字符型,长度为200个字符,聚集索引的每一行总宽度超过了200个字节,对于每一个8KB的页面,存储的行数也不超过40行.而索引需要2500个页来存储所有10万行数据,与之相反的是,非聚集索引中每一行的总宽仅有8个字节,加一些头部信息,每一页可以存储上百行数据,索引则需要不到250页来存储所有的10万行数据.通过扫描非聚集索引,当执行查询时则需要较少的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)

执行上述查询后,结果如下:
index_id    page_count
----------- --------------------
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列上的谓词选择的行仅有9行,而在d列上则有99行,显然使用索引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)))

为什么是这样呢?注意第一个查询选择的记录数有8千行(相对于10万行数据而言),对于聚集索引来说,选择度为表的8%,约230个数据页,而第二查询选择的记录数有89000行,选择度为表的约90%,若使用聚集索引来读取89000行数据时,则需要读2500个数据页.通过比较,非聚集索引仅需要读取174个页面,查询优化器选择此计划,大大减少了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开销比较大的方式是随机读.)对于查找的选择度高的书签查询,则是值得的.

         以下是第一个包含书签查询的查询计划(仅需要读取100行):

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

  而第二个查询则读取1000行,对于表而言,仅有1%.查询优化器由此推出,执行1000次的随机读要比执行2800次的顺序读的开销要大得多,第二个查询的计划如下:

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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/355374/viewspace-495811/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 书签查询
请登录后发表评论 登录
全部评论

注册时间:2008-11-08

  • 博文量
    43
  • 访问量
    84630