位置: 编程技术 - 正文
和许多人一样看到大神们画的二叉树索引结构图就脑袋大,看得云里雾里,所以这里我们以表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统计并执行下面两个查询 两个查询都返回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可以发现查询计划已经发生变化我们可以看到查询2、查询3的书签查找已经消失,因为索引IX_UserName包含了查询中用到得所有列(UserID,UserName,Age),查询4因为选择返回所有列我们的索引没有包含Gender和CreateTime列,故还是会进行书签查找
这时索引IX_UserName结构表示如下
可见对于查询2、查询3仅仅通过索引IX_UserName既可以拿到需要的列UserName,Age,UserID,而对于查询4索引并没有全部覆盖还是需要进行书签查找
2.继续修改我们的索引IX_UserName,使用include包含非键列(键列就是索引上的列,非键列就是索引之外的列,对于include来说就是存放于非聚集索引叶子节点上的列,聚集索引的列也放在非聚集索引的叶子节点上)可以看到我们修改索引使用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查询性能优化之不可小觑的书签查找介绍(sql server如何查询),希望有所帮助,仅作参考,欢迎阅读内容。
文章相关热门搜索词:sql server数据查询语句,sqlsever 查询语句,sql server查询表数据,sqlserver查询性能分析工具,sql server查询表数据,sqlserver查询数据,sqlserver查询性能分析工具,sqlserver查询语句大全讲解,内容如对您有帮助,希望把文章链接给更多的朋友!
Sql Server 查询性能优化之走出索引的误区分析 据了解绝大多数开发人员对于索引的理解都是一知半解,局限于大多数日常工作没有机会、也什么没有必要去关心、了解索引,实在哪天某个查询太慢
SqlServer参数化查询之where in和like实现详解 身为一名小小的程序猿,在日常开发中不可以避免的要和wherein和like打交道,在大多数情况下我们传的参数不多简单做下单引号、敏感字符转义之后就
SqlServer参数化查询之where in和like实现之xml和DataTable传参介绍 方案5使用xml参数对sqlserverxml类型参数不熟悉的童鞋需要先了解下XQuery概念,这里简单提下XQuery是用来从XML文档查找和提取元素及属性的语言,简单说就
标签: sql server如何查询
本文链接地址:https://www.jiuchutong.com/biancheng/348536.html 转载请保留说明!友情链接: 武汉网站建设