查询优化器在从表中查询数据时,需要选择一个合适的访问模式,在决定使用哪一种索引,使用扫描还是查找,使用书签查询时,查询优化器要考虑许多因素,这些因素包括:
- 索引执行时,查找或扫描所需的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列是字符型,长度为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)
执行上述查询后,结果如下:
索引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列上的谓词选择的行仅有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)))
源文出处:http://www.haixiait.com/article.asp?id=162
相关推荐
SQLserver索引失效举例.txt
个人整理的索引使用的样例 包含 索引并行创建 分区 监测 和索引统计信息
4、索引高度:索引高度是指由于数据行的插入操作而产生的索引层数,当表中添加大量数据时,oracle将生成索引的新层次以适应加入的数据行,因此,oracle索引可能有4层,但是这只会出现在索引数中产生大量插入操作的...
DB2索引设计及其优化,并对常见的数据库索引问题进行分析,解答。包括索引失效,针对不同情况该如何建立索引,建立怎样的索引等等····
代码复制粘贴 改下里面的参数 就可以用 而且参数 也有说明 简单易懂
该表的记录排列是无序的。 若表中的记录作如右图排列,则表中记录按学号字段升序排列。这样一个排列次序就是一个索引,索引表达式为学号
C语言实现的词索引表程序:IndexBook。C语言实现按照索引插入并查找元素。严蔚敏建立词索引表:严蔚敏数据结构C语言实现,串操作应用举例中的词索引表例子,由于作者没给出完整源码,自己写了一个比较完整的。
java 字符串索引越界异常(StringIndexOutBounds) java 字符串索引越界异常(StringIndexOutBounds)
LaTeX论文写作教程-建立索引及文献引用 LaTeX是一种基于TeX的排版系统,用于创建高质量的科技文档,如学术论文、书籍、报告等。与传统的字处理软件(如Word)相比,LaTeX具有以下优势: 专业排版:LaTeX提供了丰富...
花哨的索引单纯的花哨索引组合索引花哨索引与普通索引花哨索引与切片花哨索引与掩码结合使用花哨索引举例_随机取点花哨索引举例_修改值 Numpy数组中数据的抽取 前面讲解了Numpy中数组的创建,操作,运算等内容 有些...
day05_11_常见问题_数组索引越界异常
下面,我们举例来说明一下聚集索引和非聚集索引的区别: 其实,我们的汉语字典的正文本身就是一个聚集索引。比如,我们要查“安”字,就会很自然地翻开字典的前几页,因为“安”的拼音是“an”,而按照拼音排序汉字...
严蔚敏数据结构C语言实现,串操作应用举例中的词索引表例子,由于作者没给出完整源码,自己写了一个比较完整的
主要通过实例来介绍MySQL中的前缀索引,包括前缀在实际使用中需要考虑到的长度问题等,需要的朋友可以参考下
SQLServer中没有函数索引,在某些场景下查询的时候要根据字段的某一部分做查询或者经过某种计算之后做查询...SQLServer在建表的时候允许使用计算列,可以借助这个计算列来实现函数索引的功能,这里举例说明一下 Create
众所周知,海量数据处理是搜索引擎最耀眼的核心技术之一,本书准确和系统地阐明了海量数据处理中压缩、索引和查询的理论、技术和实现,由此奠定了其搜索引擎圣经的美名,至今对学术界和行业界都产生着巨大的影响。...
下面,我们举例来说明一下聚集索引和非聚集索引的区别: 其实,我们的汉语字典的正文本身就是一个聚集索引。比如,我们要查“安”字,就会很自然地翻开字典的前几页,因为“安”的拼音是“an”,而按照拼音排序汉字...
聚集索引聚集索引:指索引项的排序方式和表中数据记录排序方式一致的索引(这里不懂先放着,一会举例),每张表只能有一个聚集索引,聚集索引的叶子节点存储了整个行数据。
系统瓶颈分析举例软件测试经验举例1交易的响应时间如果很长,远远超过系统性能需求,表示耗费CPU的数据库操作,例如排序,执行aggregatefunctions(例如sum、min、max、count)等较多,可考虑是否有索引以及索引建立的...
绝大部分的内容笔者是认可的,不过部分举例中笔者认为用词太绝对了,并没有说明其中的原由,很多人不知道为什么。所以笔者绝对再整理一遍MySQL中索引失效的常见场景,并分析其中的原由供大家参考。 当然请记住,...