none
为什么在用非聚集索引作为查询条件查询聚集表时,执行计划中有一个嵌套循环 RRS feed

  • 问题

  • 如图,table1上,createdate是聚集索引列,id上是非聚集索引列

    用非聚集索引作为查询条件时,非聚集索引存储了聚集索引的键值,

    第一步,利用非聚集索引查询聚集索引键值;

    第二步,用聚集索引查找,应该可以查找到所有的列(就类似于第一种查询,用聚集索引查询),本应该查询出所有列的

               (但是output 并没有包含非聚集索引的那个列)

    最后还得循环嵌套才能出结果

    我的问题是为什么键查找没有能够查询出来所有的列,为了查询所有的列,再去做一次嵌套循环?

    谢谢

    2013年7月12日 15:12

答案

  • Clustered index is built on data pages but doesn't include all columns in the table.

    是的,刚刚做了实验,无论聚集索引,还是主键,还是非聚集索引,数据都存在在数据页

    由于LZ是 select *  包括 createdate,聚集索引包含createdate列,所以跟聚集索引匹配会快很多

    如果LZselect 的字段里没有聚集索引包含的列,那么应该会走表扫描或者非聚集索引扫描

    因为无论如何都要到数据页里找数据,但是select里有createdate 那么在聚集索引里将数据查询出来

    然后跟非聚集索引匹配会快很多,所以会有聚集索引查找

    • 已标记为答案 X_PICK 2013年7月22日 7:40
    2013年7月14日 16:53

全部回复

  • 这是书签查找

    因为[Employee_Demo_Heap]的[EmployeeID]上是一个非聚集索引,所以SQL在用非聚集索引
    找到这条记录之后,必须再到数据页面上把其他的行上面的数据找出来,这些数据存放在聚集索引的索引页上

    (所谓的“书签查找” bookmark lookup)

    不知道LZ有没有看过徐海蔚老师的《SQLSERVER企业级平台管理实践》,这本书对书签查找说得非常详细

    一两句话很难说得清楚

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

    或者LZ可以看一下这篇文章:Sql Server查询性能优化之不可小觑的书签查找


    • 已编辑 Steven.桦仔 2013年7月12日 16:03 补充答案内容
    • 已建议为答案 Wison-Ho 2013年7月13日 2:31
    2013年7月12日 16:02
  • Why index seek didn't get all columns? Because that index doesn't cover all columns in the table, that's why need key lookup to get them.
    • 已建议为答案 Wison-Ho 2013年7月13日 2:31
    2013年7月12日 17:00
  • 解决方法:

    Covering Index(复合索引/涵盖索引)
    --一个索引可以建立在一组字段上。这样的好处有:
    --(1)查询的检索条件,本身是一组字段的组合。直接在上面建索引,检索更快
    --(2)如果查询用到的字段索引都能够覆盖,那就不用做书签查找,能节省时间


    在SQL SERVER 2005可以采用以下方式:

    Create index idx on T(C) INCLUDE(A,B...)

    • 已编辑 Steven.桦仔 2013年7月12日 17:38 补充问题答案
    2013年7月12日 17:37
  • 在CSDN上那个应该也是你问的吧。

    Please Mark As Answer if it is helpful.

    2013年7月13日 1:40
  • 在CSDN上那个应该也是你问的吧。

    Please Mark As Answer if it is helpful.

    是的,谢谢关注
    2013年7月13日 1:56
  • 你推荐的文章我看了经常拜读您和“懒惰的肥兔”还有博客园caryson的文章,学到了不少东西,谢谢

    书签查找我是能够理解的

    同样定位到Robert所在行RowID=3”

    非聚集索引定位到ROWID后,理论上可以查询到所有的列,我的问题就在这里,既然可以查询到所有列,为什么要嵌套循环才能出结果呢

    对于示例查询来说先根据索引IX_UserName定位Robert所在行,然后根据RowID=3去数据表里获取Age值,然后完成查询,对于查询4来说需要更多的列(Age,Gender,CreateTime),同样定位到Robert所在行RowID=3,去数据表一次性拿到Age,Gender,CreateTime数据然后返回,这样就形成了书签查找(查询计划中显示为键查找RID查找)

    2013年7月13日 2:01
  • 在CSDN上那个应该也是你问的吧。

    Please Mark As Answer if it is helpful.

    感谢大神关注,还是那句话,有些问题,不弄清楚,心里憋的慌,大神周末都不休息啊?

    本人不才,就这个水平了,最近比较工作郁闷,就职于某公司,已经一年多了,一开始的承诺都不能兑现,感觉被骗了

    求大神指点一二,本人三年多工作时间(都不敢说经验),人在深圳,sqlserver oracle这些都懂一点点,平时开发中也自己或者帮同事解决一些数据库问题

    主要做.net平台企业级应用开发,但是想做数据库相关的工作,求大神指路。



    • 已编辑 X_PICK 2013年7月13日 2:17 1-------1
    2013年7月13日 2:09
  • Loop is not for getting columns but rows.
    2013年7月13日 2:11
  • key lookup has already find all columns,why needs loop?this is my question,

    even the no-clustered index key mapping serival(or more)rows,key lookup has find then leaf page in then B Tree which stored the data 

    非常憋足的英语,呵呵

    2013年7月13日 2:30
  • Again, loop is for getting all matching rows not columns.

    2013年7月13日 2:46
  • LZ的意思是为什麽还需要键查找是吗?

    因为非聚集索引只存放了id列数据,而聚集索引存放了表中的所有列数据,当找到id是123的时候,那么你知道非聚集索引有指针指向聚集索引当

    表是聚集索引表的时候,那么根据id是123 的记录SQLSERVER就会去聚集索引里查找id是123的记录

    即是相当于有两个表,一个表A只有id这一列,另一个表B有所有列id,createdate

    那么查询语句就相当于select * from A AS a INNER JOIN B AS b on a.id=b.id

    所以就需要嵌套循环(join联接)

    不知道这样解释明白不??



    • 已编辑 Steven.桦仔 2013年7月13日 3:42 修改答案内容
    2013年7月13日 2:57
  • 而你知道,聚集索引里每行记录都有键值标识每一行,所以就是键查找

    如果是堆表,那么在非聚集索引里找到记录之后,就要到数据页找匹配的行,而在堆表中,每行记录都有一个rid

    所以就是rid(row id)查找

    解释得不是很专业,望见谅
    • 已编辑 Steven.桦仔 2013年7月13日 3:02 补充问题答案
    2013年7月13日 3:01
  • LZ的意思是为什麽还需要键查找是吗?

    因为非聚集索引只存放了id列数据,而聚集索引存放了表中的所有列数据,当找到id是123的时候,那么你知道非聚集索引有指针指向聚集索引当

    表是聚集索引表的时候,那么根据id是123 的记录SQLSERVER就会去聚集索引里查找id是123的记录

    即是相当于有两个表,一个表A只有id这一列,另一个表B有所有列id,createdate

    那么查询语句就相当于select * from A AS a INNER JOIN B AS b WHERE a.id=b.id

    所以就需要嵌套循环(join联接)

    不知道这样解释明白不??


    http://bbs.csdn.net/topics/390516346?page=1#post-395027020

    我在csdn上问过了,我这里想知道(根据“非聚集索引”查询“聚集表”),

    第一:非聚集索引找到聚集索引,那么找到了聚集索引,根据聚集索引

    就能找到这条数据所有的列,为什么还要嵌套循环

    你看一下同样结构的表,在上面链接的那个帖子中,非常清楚

    (oracle中的索引组织表相当于聚集索引,利用普通B树索引查询),在oracle的计划中,就没有第三部的嵌套循环

    2013年7月13日 3:36
  • oracle我不懂

    非聚集索引使用嵌套循环nest loop join  on a.id=b.id找到/匹配聚集索引,那么找到了聚集索引,根据聚集索引就能找到这条数据所有的列

    2013年7月13日 3:46
  • 其实书签查找我是这样理解的,一本书有很多书签,你想找123页里某一个内容,你需要找到一个书签,书签编号是123,找到那个书签编号为123的书签之后,你就能找到书本第123页的那个书本页面,找到页面之后再找里面某一个内容,然后select出来


    • 已编辑 Steven.桦仔 2013年7月13日 3:55 补充问题答案
    2013年7月13日 3:49
  • Clustered index is built on data pages but doesn't include all columns in the table.
    2013年7月14日 2:20
  • Clustered index is built on data pages but doesn't include all columns in the table.

    是的,刚刚做了实验,无论聚集索引,还是主键,还是非聚集索引,数据都存在在数据页

    由于LZ是 select *  包括 createdate,聚集索引包含createdate列,所以跟聚集索引匹配会快很多

    如果LZselect 的字段里没有聚集索引包含的列,那么应该会走表扫描或者非聚集索引扫描

    因为无论如何都要到数据页里找数据,但是select里有createdate 那么在聚集索引里将数据查询出来

    然后跟非聚集索引匹配会快很多,所以会有聚集索引查找

    • 已标记为答案 X_PICK 2013年7月22日 7:40
    2013年7月14日 16:53
  • Clustered index is built on data pages but doesn't include all columns in the table.

    你这个什么意思?Clustered index的leaf node不就是data pages吗。

    想不想时已是想,不如不想都不想。

    2013年7月15日 2:41
    版主
  • Index pages are index pages and data pages are data pages.
    2013年7月15日 2:50
  • 他就是这个意思啊
    2013年7月15日 2:54
  • Clustered index is built on data pages but doesn't include all columns in the table.


    你这个什么意思?Clustered index的leaf node不就是data pages吗。

    想不想时已是想,不如不想都不想。

    rmiao is trying to say index nodes are index pages, leaf nodes are data pages. they are all part of clustered index. Index page doesnt contain data.

    我觉得楼主你看看两个聚集和非聚集的output是啥,我觉得你的select *导致了两个inner join


    If you think my suggestion is useful, please rate it as helpful.
    If it has helped you to resolve the problem, please Mark it as Answer.
    http://twitter.com/7Kn1ghts

    2013年7月18日 15:53
  • 我重新写了一篇文章,LZ可以看一下

    其实LZ的情况是书签查找的四种情况的其中一种来的

    SQLSERVER聚集索引与非聚集索引的再次研究(下)

    在文章的最后集中讲解了 “书签查找”  并且讲解了书签查找的解决办法

    四种情况分别是:

    索引扫描+rid查找

    索引查找+rid查找

    索引扫描+键查找

    索引查找+键查找

    • 已编辑 Steven.桦仔 2013年7月20日 8:17 补充说明内容
    2013年7月20日 8:13