日期：2012-06-27 05:02:00 来源：本站整理
Sql Server查询性能优化之不可小觑的书签查找介绍[MSSQL防范]
本文“Sql Server查询性能优化之不可小觑的书签查找介绍[MSSQL防范]”是由七道奇为您精心收集，来源于网络转载，文章版权归文章作者所有，本站不对其观点以及内容做任何评价，请读者自行判断，以下是其具体内容：
小小程序猿SQL Server认知的生长
1.没毕业或工作没多久,只知道有数据库、SQL这么个东东,浑然分不清SQL和Sql Server Oracle、MySql的关系,普通认为SQL就是SQL Server
2.工作好几年了,也写过不少SQL,却浑然不知道索引为什么物,只知道数据库有索引这么个东西,分不清堆积索引和非堆积索引,只知道查询慢了建个索引查询就快了,到头来索引也建了不少,查询也确切快了,无意问之:汝建之索引为什么范例?答曰:...
3.终于遭到刺激开始发奋图强,买书,gg查资料终于知道本来索引分为堆积索引和非堆积索引,登时泪流满面,呜呼哀哉,吾终知索引为什么物也.
4.再进一步学习之亦知堆积索引为物理索引、非堆积索引为逻辑索引,堆积索引为数据的存储次序,非堆积索引是逻辑索引既对堆积索引的索引
5.再往后学会了查看履行筹划,通过查询筹划终于对查询历程有了大约理解,也知道了堆积索引扫描和表扫描没有效到索引,看到堆积索引、索引查找高兴的欢天喜地,看到RID、键查找暗自窃喜,瞧,键查找必定就是关键字查找了,用着索引呢,效率必定高,于是每次写完sql都要傍观下其履行筹划,表扫描的干货通通不要,俺只要索引查找、键查找.
6.自大满满的过着悠哉的小日子,忽然有一天苍茫了,为嘛俺明显在这个字段上成立了索引,它她妹的老给我显示堆积索引扫描的,莫非查询优化器发烧了,实际履行下,发现实际的履行筹划还是表扫描,这下完好迷惑了,兴许是查询优化器显示的有问题吧.
7.持续深化学习终发现,数据库这潭水太深了,理解的太单方面了,想想从猿到人的进化历程吧,恩恩,目前就是一个灵智初开的程序猿,向着巨大的程序员奋勇行进
恩恩,跑题了,进入我们的主题:数据库的书签查找
熟习书签查找
书签查找这个词大概关于很多开辟人员对比陌生,很多人都碰到过,但是却没惹起充足的器重以至于一向都忽视它的存在了
定义:当查询优化器利用非堆积索引举行查找时,假如所挑选的列或查询条件中的列只部份包含在利用的非堆积索引和堆积索引中时,就需求一个查找（lookup）来检索其他字段来满意恳求.对一个有聚簇索引的表来说是一个键查找（key lookup）,对一个堆表来说是一个RID查找（RID lookup）,这种查找便是——书签查找(bookmark lookup).简单的说就是当你利用的sql查询条件和select返回的列没有完好包含在索引列中时就会发生书签查找.
书签查找的重要性
1.书签查找发生条件:只有在利用非堆积索引举行数据查找时才会产生书签查找,堆积索引查找、堆积索引扫描和表扫描不会发生书签查找.
2.书签查找发生频率:书签查找发生频率非常高,乃至可以说大部份查询城市发生书签查找,我们知道一个表只能成立一个堆积索引,所以我们的查询更多的会利用非堆积索引,非堆积索引不大概覆盖全部的查询列,所以会常常性产生书签查找.
3.书签查找的影响:招致索引失效的主要缘由之一.书签查找按照索引的行定位器从表中读取数据,除了索引页面的逻辑读取外,还需求数据页面的逻辑读取,假如查询的后果返回数据量较大会招致大量的逻辑读大概索引失效,这也是为什么我们查看查询筹划时有时明显在查询列上成立了索引,查询优化器却仍然利用表扫描的缘由.
4.若何消除书签查找:
1.利用堆积索引查找,堆积索引的叶子节点就是数据行本身,因此不存在书签查找
2.堆积索引扫描、表扫描,说白了就是啥索引都不建直接全表扫描,必定不会发生书签查找,不过效率吗...
3.利用非堆积索引的键列包含全部查询或返回的列,这个不靠谱,非堆积索引最大键列数为16,最大索引键大小为900字节,就算你有勇气在16列上全部成立索引,那假如表的列数超越16列了你咋办,还有索引列长度之和不能超越900字节,所以不大概让非堆积索引包含全部列,并且索引触及到得列越多保护索引的开销也就越大.
4.利用include,嗯,这是个好东东,索引做到只能包含16列且不能超越900字节,include不受此限制,最多可以包含1023列怎么也够你用了,并且对长度也没有限制你可以随心所欲的包含nvarchar(max)这也的列,当然了text之流就不要考虑了
5.别的,别的还有神马呢,这个我也不知道了,预计应当、大概、大约木有了吧,若有知道的兄弟可以奉告我声哈
大概上面说的有点抽象,我们开看看具体的例子
普通我们的数据库城市建上堆积索引(普通大家喜好建表时有效没有必定先来个自增ID列当主键,这个主键SQL Server默许就给你成立成堆积索引了),故我们这里都假定表上已经成立了堆积索引,不考虑堆表(就是没有堆积索引的表)
1.首先成立表Users、插入一些示例数据并成立堆积索引PK_UserID 非堆积索引IX_UserName
复制代码 代码以下:
--懒得的肥兔 --成立表Users
Create table Users
(
UserID int identity,
UserName nvarchar(50),
Age int,
Gender bit,
CreateTime datetime
)
--在UserID列成立堆积索引PK_UserID
create unique clustered index PK_UserID on Users(UserID)
--在UserName成立非堆积索引IX_UserName
create index IX_UserName on Users(UserName)
--插入示例数据
insert into Users(UserName,Age,Gender,CreateTime)
select N'Bob',20,1,'2012-5-1'
union all
select N'Jack',23,0,'2012-5-2'
union all
select N'Robert',28,1,'2012-5-3'
union all
select N'Janet',40,0,'2012-5-9'
union all
select N'Michael',22,1,'2012-5-2'
union all
select N'Laura',16,1,'2012-5-1'
union all
select N'Anne',36,1,'2012-5-7'
2.履行以下查询并查看查询筹划,可以看到第一个SQL履行堆积索引扫描,第二个SQL履行堆积索引查找都没有利用到书签查找
复制代码 代码以下:
select * from Users
select * from Users where UserID=4
3.对比以下几个查询SQL,察看其查询筹划,考虑下为什么会发生书签查找
复制代码 代码以下:
--查询1:利用索引IX_UserName,挑选列UserID,UserName,查询条件列为UserName
select UserID,UserName from Users with(index(IX_UserName)) where UserName='Robert'
--查询2:利用索引IX_UserName,挑选列UserID,UserName,Age,查询条件列为UserName
select UserID,UserName,Age from Users with(index(IX_UserName)) where UserName='Robert'
--查询3:利用索引IX_UserName,挑选列UserID,UserName,查询条件列为UserName,Age
select UserID,UserName from Users with(index(IX_UserName)) where UserName='Robert' and Age=28
--查询4:利用索引IX_UserName,挑选列全部列,查询条件列为UserName
select * from Users with(index(IX_UserName)) where UserName='Robert'
解析:
查询1:挑选的列UserID是堆积索引PK_UserID的键列,UserName为索引IX_UserName的键列,查询条件列为UserName,由于索引IX_UserName包含了查询用到得全部列,所以仅需求扫描索引便可返回查询后果,不需求再额外的去数据页获得数据,故不会发生书签查找
查询2:挑选列Age不包含在堆积索引PK_UserID和IX_UserName中,故需求举行额外的书签查找
查询3:查询条件Age列不包含在堆积索引PK_UserID和IX_UserName中,故需求举行额外的书签查找
查询4:包含了全部的列,Age、Gender、CreateTime列均不在堆积索引PK_UserID和IX_UserName中,所以需求书签查找以定位数据
这里注释下:查询顶用到的列无论是一列还是多列不在索引覆盖范围查询开销基本上一样,每条记录均只需求一次书签查找开销,不会说因为查询3只有一个Age列,查询4有Age、Gender、CreateTime 3列不在索引覆盖范围而招致额外的开销
解析:
查询1:挑选的列UserID是堆积索引PK_UserID的键列,UserName为索引IX_UserName的键列,查询条件列为UserName,由于索引IX_UserName包含了查询用到得全部列,所以仅需求扫描索引便可返回查询后果,不需求再额外的去数据页获得数据,故不会发生书签查找
查询2:挑选列Age不包含在堆积索引PK_UserID和IX_UserName中,故需求举行额外的书签查找
查询3:查询条件Age列不包含在堆积索引PK_UserID和IX_UserName中,故需求举行额外的书签查找
查询4:包含了全部的列,Age、Gender、CreateTime列均不在堆积索引PK_UserID和IX_UserName中,所以需求书签查找以定位数据
这里注释下:查询顶用到的列无论是一列还是多列不在索引覆盖范围查询开销基本上一样,每条记录均只需求一次书签查找开销,不会说因为查询3只有一个Age列,查询4有Age、Gender、CreateTime 3列不在索引覆盖范围而招致额外的开销
书签查找是怎么发生的
和很多人一样看到大神们画的二叉树索引构造图就脑袋大,看得云里雾里,所以这里我们以表Users为例来说堆积索引(PK_UserID)和非堆积索引(IX_UserName)的构造可以简单的表示为下图
首先我们来看堆积索引PK_UserID,关于堆积索引来说数据行就是其叶子节点,故当履行堆积索引查找时找到了具体的键值后便可以直接去叶子节点获得全部需求的数据不需求举行额外的逻辑读,比方select * from Users where UserID=2,按照值2在索引PK_UserID中找到UserID为2的值后去叶子节点便可以拿到所需数据,然后返回查询后果然后看非堆积索引IX_UserName,上面我们说过非堆积索引覆盖的列为非堆积索引的键列+包含的列+堆积索引的键列,关于IX_UserName来说就是如图中所示键列UserName保存在索引的二叉树节点中,堆积索引的列包含在其叶子节点中,这也就形成了对列(UserName,UserID)的覆盖,关于查询1(select UserID,UserName from Users with(index(IX_UserName)) where UserName='Robert')来说查询只用到了UserName,UserID列,这样只需求扫描索引IX_UserName便可拿到全部数据然后举行后果返回,而关于查询2、查询3来说由于需求用到Age列,而索引IX_UserName中并没有包含Age列,这时就需求个书签查找(bookmark lookup)按照叶节点中的RowID去定位到具体的数据行获得Age列值,关于示例查询来说先按照索引IX_UserName定位Robert所在行,然后按照RowID=3去数据表里获得Age值,然后完成查询,关于查询4来说需求更多的列(Age,Gender,CreateTime),一样定位到Robert所在行RowID=3,去数据表一次性拿到Age,Gender,CreateTime数据然后返回,这样就形成了书签查找(查询筹划中显示为键查找或RID查找)
书签查找的对查询性能的影响
--这是我们目前利用的索引create index IX_UserName on Users(UserName)
翻开IO统计并履行下面两个查询
复制代码 代码以下:
--set statistics io onselect * from Users where UserName like 'ja%'select * from Users with(index(IX_UserName)) where UserName like 'ja%'
两个查询都返回2条数据,堆积索引扫描仅仅2次逻辑读,利用索引IX_UserName却到达了6次的逻辑读
我们示例的数据量对比小,所以感受不明显,不过我们却也看到了我们在UserName列上市成立了索引 IX_UserName,默许情形下查询优化器并没有利用我们的索引,而是挑选了表扫描,仅仅需求2次逻辑读就拿到了我们需求的数据,在我们利用索引提醒强迫查询优化器利用索引IX_UserName后,一样也是返回2条数据,逻辑读缺到达了惊人的6次,看查询筹划利用IX_UserName后发生了书签查找,而这个开销主如果有书签查找造成的,并且随着我们返回数据量的增添,由书签查找招致的逻辑读将会成直线上升,造成的后果就是查询开销比举行全表扫描还要大的多,终究招致索引失效
利用覆盖索引避免书签查找
覆盖索引是指非堆积索引上的列(键列+包含列) + 堆积索引的键列包含了查询顶用到的全部列,关于索引IX_UserName来说索引覆盖列就是(UserName,UserID).若查询中只用到了索引所覆盖的列,那么只需扫描索引便可完成查询,若用到了索引覆盖范围以外的列就需求书签查找来获得数据,当这种查找发生次较多时就会招致索引失效从而招致表扫描,因为查询优化器是基于开销的优化器,当其发现利用非堆积索引引发的书签查找开销比表扫描开销还大时就会放弃利用索引,转向表扫描.
1.在UserName,Age列上重建索引IX_UserName,这时关于索引IX_UserName来说覆盖列变成(UserName,Age,UserID),再次履行上面的查询SQL可以发现查询筹划已经发生改变
复制代码 代码以下:
drop index IX_UserName on Userscreate index IX_UserName on Users(UserName,Age)
我们可以看到查询2、查询3的书签查找已经消逝,因为索引IX_UserName包含了查询顶用到得全部列(UserID,UserName,Age),查询4因为挑选返回全部列我们的索引没有包含Gender和CreateTime列,故还是会举行书签查找
这时索引IX_UserName构造表示以下
可见关于查询2、查询3仅仅通过索引IX_UserName既可以拿到需求的列UserName,Age,UserID,而关于查询4索引并没有全部覆盖还是需求举行书签查找
复制代码 代码以下:
drop index IX_UserName on Userscreate index IX_UserName on Users(UserName,Age) include(Gender,CreateTime)
可以看到我们改正索引利用include包含了Gender,CreateTime后,索引IX_UserName到达了对数据表Users的全部列的全覆盖,这时刻毫无疑问的查询2、查询3没有呈现书签查找,查询4的书签查找也消逝了.
此时索引IX_UserName 构造以下
索引IX_UserName已经到达了对Users表的全覆盖,关于我们的查询2、查询3、查询4来说,仅通过索引IX_UserName便可完成查询,不需求举行书签查找.
这时我们再来看一下这两个查询的开销及查询筹划,可以看到不需求我们举行索引提醒,查询优化器已经自动挑选了我们的索引,逻辑读也降至了2次
select * from Users where UserName like 'ja%'select * from Users with(index(IX_UserName)) where UserName like 'ja%'关于Include请参考 SQL Server 索引中include的魅力（具有包含性列的索引）
这里阐明下书签查找对查询性能有着较大的影响并且基本上不可避免,这并不意味着书签查找就是大水猛兽,本来我们不是也不知道啥叫书签查找么,查询性能一样也不差,是吧,呵呵.书签查找也阐明了为什么我们不举荐写sql时利用select *,也注释了为什么有时刻我们的索引会失效,同时可以作为优化查询性能考虑的一个方面,在计划表和索引时尽大概躲避书签查找带来的负面影响,比方非堆积索引尽大概挑选高挑选性的列即返回尽大概少的行,需求大批量数据查询时尽大概利用堆积索引等.
本文中为了便于演示仅仅利用了有几条数据的表,并且查询中为了利用索引都用了索引提醒,实际开辟中请不要利用索引提醒,查询优化器大大都情形下会为我们生成最优(最优不代表开销最小,只要开销充足小即认为最优)的履行筹划,索引构造里面用到得RowID也仅仅是为了演示虚拟出来的,我们只要认为它是关于数据行的一个标识位就行了.
此文旨在让我们熟习书签查找并意识到书签查找的意义,从而关于索引失效缘由有清楚的熟习,更好的理解查询筹划.
以上是“Sql Server查询性能优化之不可小觑的书签查找介绍[MSSQL防范]”的内容，如果你对以上该文章内容感兴趣，你可以看看七道奇为您推荐以下文章：
| 本文地址： | 与您的QQ/BBS好友分享! |
- 好的评价 如果您觉得此文章好，就请您
0%(0) 
- 差的评价 如果您觉得此文章差，就请您
0%(0) 
中查找“Sql Server查询性能优化之不可小觑的书签查找介绍”更多相关内容
中查找“Sql Server查询性能优化之不可小觑的书签查找介绍”更多相关内容评论内容只代表网友观点，与本站立场无关！
评论摘要(共 0 条，得分 0 分，平均 0 分)
查看完整评论