none
聚集索引与非聚集索引 RRS feed

  • 问题

  • 两个表,一个使用聚集索引,一个使用非聚集索引,数据有一百万. 索引字段都是name。字段都是一,二,三几个值的重复值 聚集索引 select * from dbo.c1 where name='七' 执行计划是,聚集索引查找 而非聚集索引 select * from dbo.c2 where name='七' 执行计划,提示 缺少索引 CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] 而使用 select name from dbo.c2 where name='七' 执行计划显示 index seek 如果换成select name from dbo.c1 where name='七' 执行计划显示 clustered index seek index seek 和 clustered index seek 算法是否一样,两者有多大差别,谢谢大家指点
    2011年4月18日 16:08

答案

  • Some differences:

    1. If table doesn't have clustered index, sql has to do rid lookup to get the row. There's extra cost for lookup.

    2. With clustered index, data in the table is physically ordered by clustered index.

    You can run both queries in same batch and compare costs, can also compare disk io with 'set statistics io on'.

    2011年4月18日 19:43
  • 看看 聚集索引和非聚集索引的工作原理,可能你就明白了,前人也总结了一堆堆了。

    http://www.cnblogs.com/tmyh/archive/2010/09/29/sqlindex_01.html

    http://www.phplovers.com/html/shujuku/2009/0710/sql%20server_suoyin.html

    http://blog.csdn.net/olony/archive/2009/01/16/3793976.aspx

    2011年4月20日 7:43

全部回复

  • Some differences:

    1. If table doesn't have clustered index, sql has to do rid lookup to get the row. There's extra cost for lookup.

    2. With clustered index, data in the table is physically ordered by clustered index.

    You can run both queries in same batch and compare costs, can also compare disk io with 'set statistics io on'.

    2011年4月18日 19:43
  • 看看 聚集索引和非聚集索引的工作原理,可能你就明白了,前人也总结了一堆堆了。

    http://www.cnblogs.com/tmyh/archive/2010/09/29/sqlindex_01.html

    http://www.phplovers.com/html/shujuku/2009/0710/sql%20server_suoyin.html

    http://blog.csdn.net/olony/archive/2009/01/16/3793976.aspx

    2011年4月20日 7:43
  • It's not correct, sql will use non-cluetered index to get rid then get related row(s) via rid lookup in this case. Sql will not ask for index to include all columns when you do 'select * ...'.
    2011年4月21日 3:13
  • Hi rmiao,

     

    You are right. I am really sorry for the reckless reply. Thank you for your remind me. :)

     

    Thanks,
    Weilin Qiao


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.
    2011年4月25日 2:28
    版主