下载安卓APP箭头
箭头给我发消息

客服QQ:3315713922

SQL Server里书签查找是不是真的好?

作者:课课家教育     来源: http://www.kokojia.com点击数:1071发布时间: 2018-12-16 14:02:32

标签: 数据库软件工程师oracle数据库ocp认证课程ocp认证

  书签查找这个词可能对于很多开发人员比较陌生,很多人都遇到过,但是却没引起足够的重视以至于一直都忽略它的存在了。我们先来看一下书签查找的定义和重要性:

  书签查找定义

  当查询优化器使用非聚集索引进行查找时,如果序列所选择的列或查询条件中的列只部分包含在使用的非聚集索引和聚集索引中时,就需要一个查找(lookup)触发器来检索其他字段来满足请求。对一个有聚簇索引的表来说是一个键查找(keylookup),对一个堆表来说是一个RID查找(RIDlookup),这种查找即是——书签查找(bookmarklookup)。简单的说就是当你使用的sql查询条件和select返回的列没有完全包含在索引列中时就会发生书签查找。

  书签查找的重要性

  1.书签查找发生条件:只有在使用非聚集索引进行数据查找时才会产生书签查找,聚集索引查找、聚集索引扫描和表扫描不会发生书签查找。

  2.书签查找发生频率:书签查找发生频率非常高,甚至可以说大部分存储过程查询都会发生书签查找,我们知道一个表只能建立一个聚集索引,所以我们的查询更多的会使用非聚集索引,非聚集索引不可能覆盖所有的查询列,所以会经常性产生书签查找。

  3.书签查找的影响:导致索引失效的主要原因之一。书签查找根据索引的行定位器从表中读取数据,除了索引页面的逻辑读取外,还需要数据页面的逻辑读取,如果查询的结果返回数据量较大会导致大量的逻辑读或者索引失效,这也是为什么我们查看查询计划时有时明明在查询列上建立了索引,查询优化器却依然使用表扫描的原因。

  4.如何消除书签查找:

  1.使用聚集索引查找,聚集索引的叶子节点就是数据行本身,因此不存在书签查找

  2.聚集索引扫描、表扫描,说白了就是游标啥索引都不建直接全表扫描,肯定不会发生书签查找,不过效率吗。

  3.使用非聚集索引的键列包含所有查询或返回的列,这个不靠谱,非聚集struts索引最大键列数为16,最大索引键大小为900字节,就算你有勇气在16列上全部建立索引,那如果表的列数超过16列了你咋办,还有索引列长度之和不能超过900字节,所以不可能让非聚集索引包含所有列,而且索引涉及到得列越多维护索引的开销也就越大。

  4.使用include,嗯,这是个好东东,索引做到只能包含16列且不能超过900字节,include不受此限制,最多可以包含1023列怎么也够你用了,而且对spring长度也没有限制你可以随心所欲的包含nvarchar(max)这也的列,当然了text之流就不要考虑了

  下面小编想从性能角度进一步谈下书签查找,还有它们如何拉低你整个SQLServer性能。

  书签查找——反复循环

  如果你的非聚集索引不是个覆盖非聚集索引,SQLServer的查询优化器会引入书签查找。对于从非聚集索引你返回的每一行,SQLServer需要在聚集索引里或堆表里进行额外的查找操作。

  例如当你的的聚集索引包含3层,为了返回必要的信息,对于每一行,你需要3页额外的读取。因此,查询优化器再执行计划里选择书签查找操作,仅在有意义的时候发生——基于你查询的选择度。下图展示了有书签查找操作的执行计划。

  通常人们不会太关注书签查找,因为它们只执行几次。如果你的查询选择度太低,查询Hadoop优化器会用聚集索引扫描或表扫描运算符直接扫描整个表。但只在SQLServer重用缓存的执行计划,这个计划是有多次不同运行值,包含书签查找的(基于最初提供的输入值),因此这个情况很容易发生,书签查找反复执行。

  为了演示这个性能问题,接下来的查询我指定查询优化器使用特定的非聚集索引。查询本身返回80000行,因为对于每个查询执行,SQLServer需要进行书签查找80000次——反复执行。

 为了演示这个性能问题,接下来的查询我指定查询优化器使用特定的非聚集索引。查询本身返回80000行,因为对于每个查询执行,SQLServer需要进行书签查找80000次——反复执行。

  下图展示了查询执行后的实际执行计划。

下图展示了查询执行后的实际执行计划。

  执行计划看起来非常恐怖(查询优化器甚至启用了并行计划!),因为书签查找运算符这里执行了80000次,查询本身产生了超过165000个逻辑读!(逻辑读个数可以从STATISTICIO里获取)。

 执行计划看起来非常恐怖(查询优化器甚至启用了并行计划!),因为书签查找运算符这里执行了80000次,查询本身产生了超过165000个逻辑读!(逻辑读个数可以从STATISTICIO里获取)。

  接下来向你展示下,当你有很多并行用户执行这个糟糕查询时,SQLServer会发生什么。我会使用ostress.exe(RML工具的一部分)来模拟100个并行用户的查询。

 接下来向你展示下,当你有很多并行用户执行这个糟糕查询时,SQLServer会发生什么。我会使用ostress.exe(RML工具的一部分)来模拟100个并行用户的查询。

  在我的测试系统上花费了近15秒来完成100个并行查询。在此期间,CPU占用很高,因为SQLServer需要嵌套循环运算符来进行书签查找操作。嵌套循环操作当然很占CPU资源。

  现在让我们修改索引设计,为这个查询创建覆盖非聚集索引。有了非聚集索引,查询优化器不需要再执行计划里进行书签查找。一个非聚集索引查找就可以返回同样的结果:

  现在让我们修改索引设计,为这个查询创建覆盖非聚集索引。有了非聚集索引,查询优化器不需要再执行计划里进行书签查找。一个非聚集索引查找就可以返回同样的结果:

  这次当我们再次用ostress.exe执行同个查询,我们看到Spark每个查询在5秒内完成。和我们刚才看到的15秒有很大的区别。这就是覆盖非聚集索引的威力:在我们查询里气门请求的数据都可以在非聚集索引里直接找到,因此书签查找就可以避免。

  小编结语:

  在这个文章里我向你展示了不好的书签查找会伤及性能。因此,对于重要的查询快速完成查询非常重要——而使用并行的书签查找的执行计划并不是好的选择。这里覆盖非聚集索引可以帮到你。下次设计索引时可以考虑下这个方法。

  更多内容尽在课课家教育!

赞(16)
踩(0)
分享到:
华为认证网络工程师 HCIE直播课视频教程