SQL Server 查询性能优化之不可小觑的书签查找介绍

发布时间：2020-05-24 01:23:51 所属栏目：MsSql 来源：互联网

导读：SQL Server 查询性能优化之不可小觑的书签查找介绍

感兴趣的小伙伴，下面一起跟随脚本之家 jb51.cc的小编两巴掌来看看吧！

小小程序猿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,'2012-5-2' 
union all 
select N'Robert',28,'2012-5-3' 
union all 
select N'Janet',40,'2012-5-9' 
union all 
select N'Michael',22,'2012-5-2' 
union all 
select N'Laura',16,'2012-5-1' 
union all 
select N'Anne',36,'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,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,CreateTime)，同样定位到Robert所在行RowID=3，去数据表一次性拿到Age,CreateTime数据然后返回，这样就形成了书签查找(查询计划中显示为键查找或RID查找)

书签查找的对查询性能的影响
--这是我们现在使用的索引create index IX_UserName on Users(UserName)

打开IO统计并执行下面两个查询

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!