none
请教大侠一个聚集索引和非聚集索引的问题,谢谢! RRS feed

  • 问题

  • 一个有几百万行的数据表,根据时间+Userid列建了聚集索引,如果再根据时间列建立非聚集索引,请问这个非聚集索引是不是没有用处,白浪费空间。请给位指教,多谢!
    2012年12月6日 2:35

答案

  • 比如说,只查时间的数据,时间的那个索引显然就比你那个聚焦索引有效

    • 已标记为答案 Tim-2009 2012年12月6日 9:07
    2012年12月6日 3:16
  • No difference unless your query is 'select time from table where time ...'.
    • 已标记为答案 Tim-2009 2012年12月6日 9:07
    2012年12月6日 4:17
  • 要看你的查询语句,where后面的是不是只指定时间 像邹建大侠所说,还有where后面的顺序也很重要

    索引的组织方式只能按照最左列,也就是第一列来排序,假如A B C这个索引(建立索引的顺序:A列 B列 C列),当你选择where a =1 and c=2时,可以通过
    B树定位到a=1的所有行,然后在这些行中range-scan找到 c=2的值。


    给我写信: QQ我:点击这里给我发消息


    2012年12月6日 6:29

全部回复

  • 基本上是没用的,不过还要看你具体的sql,

    不好下定论。

    2012年12月6日 2:53
  • 比如说,只查时间的数据,时间的那个索引显然就比你那个聚焦索引有效

    • 已标记为答案 Tim-2009 2012年12月6日 9:07
    2012年12月6日 3:16
  • 比如说,只查时间的数据,时间的那个索引显然就比你那个聚焦索引有效


    请问你说的只查时间的数据是指where条件或Join中只用到了时间吗?谢谢!
    2012年12月6日 3:55
  • No difference unless your query is 'select time from table where time ...'.
    • 已标记为答案 Tim-2009 2012年12月6日 9:07
    2012年12月6日 4:17
  • 要看你的查询语句,where后面的是不是只指定时间 像邹建大侠所说,还有where后面的顺序也很重要

    索引的组织方式只能按照最左列,也就是第一列来排序,假如A B C这个索引(建立索引的顺序:A列 B列 C列),当你选择where a =1 and c=2时,可以通过
    B树定位到a=1的所有行,然后在这些行中range-scan找到 c=2的值。


    给我写信: QQ我:点击这里给我发消息


    2012年12月6日 6:29
  • 比如说,只查时间的数据,时间的那个索引显然就比你那个聚焦索引有效


    这个不一定,非聚集索不一定就比聚集索引“窄”,因为非聚集索引也包含了聚集索引。


    Please click the Mark as Answer button if a post solves your problem!

    2012年12月6日 9:12
  • 具体看情况,在你的聚集索引键包含全部键的时候,那你的时间列就是完全没有用的,无论单独查时间还是全部。从INDEX大小跟逻辑读可以证明。
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [dbo].[Table_7](
     [C] [nchar](100) NULL,
     [T] [datetime] NULL
    ) ON [PRIMARY]

    GO
    INSERT INTO [dbo].[Table_7] VALUES('',getdate())
    GO 2000

    GO

    SET ANSI_PADDING ON

    GO

    /****** Object:  Index [ClusteredIndex]    Script Date: 12/6/2012 5:28:30 PM ******/
    CREATE CLUSTERED INDEX [ClusteredIndex] ON [dbo].[Table_7]
    (
    [T] ASC,
    [C] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO

    /****** Object:  Index [NonClusteredIndex]    Script Date: 12/6/2012 5:28:30 PM ******/
    CREATE NONCLUSTERED INDEX [NonClusteredIndex] ON [dbo].[Table_7]
    (
     [T] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO

    SELECT
        i.name              AS IndexName,
        SUM(page_count * 8) AS IndexSizeKB
    FROM sys.dm_db_index_physical_stats(
        db_id(), object_id('dbo.Table_7'), NULL, NULL, 'DETAILED') AS s
    JOIN sys.indexes AS i
    ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
    GROUP BY i.name
    ORDER BY i.name

    go

    set statistics io on
    select [T] from [dbo].[Table_7]
    select  * from [dbo].[Table_7]

    select [T] from [dbo].[Table_7] Where [T]=xxx

    select  * from [dbo].[Table_7]  With (index= [ClusteredIndex]  )  Where [T]=  xxxxx
    set statistics io off


    2012年12月6日 10:07
  • 具体看情况,在你的聚集索引键包含全部键的时候,那你的时间列就是完全没有用的,无论单独查时间还是全部。从INDEX大小跟逻辑读可以证明。
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [dbo].[Table_7](
     [C] [nchar](100) NULL,
     [T] [datetime] NULL
    ) ON [PRIMARY]

    GO
    INSERT INTO [dbo].[Table_7] VALUES('',getdate())
    GO 2000

    GO

    SET ANSI_PADDING ON

    GO

    /****** Object:  Index [ClusteredIndex]    Script Date: 12/6/2012 5:28:30 PM ******/
    CREATE CLUSTERED INDEX [ClusteredIndex] ON [dbo].[Table_7]
    (
    [T] ASC,
    [C] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO

    /****** Object:  Index [NonClusteredIndex]    Script Date: 12/6/2012 5:28:30 PM ******/
    CREATE NONCLUSTERED INDEX [NonClusteredIndex] ON [dbo].[Table_7]
    (
     [T] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO

    SELECT
        i.name              AS IndexName,
        SUM(page_count * 8) AS IndexSizeKB
    FROM sys.dm_db_index_physical_stats(
        db_id(), object_id('dbo.Table_7'), NULL, NULL, 'DETAILED') AS s
    JOIN sys.indexes AS i
    ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
    GROUP BY i.name
    ORDER BY i.name

    go

    set statistics io on
    select [T] from [dbo].[Table_7]
    select  * from [dbo].[Table_7]

    select [T] from [dbo].[Table_7] Where [T]=xxx

    select  * from [dbo].[Table_7]  With (index= [ClusteredIndex]  )  Where [T]=  xxxxx
    set statistics io off



    请问Jackie Shen,你说的“在你的聚集索引键包含全部键的时候,那你的时间列就是完全没有用的”,全部键是指非聚集索引的全部字段?谢谢!

    2012年12月6日 11:57
  • @Tim-2009

    就是我上面举的例子呀,表的全部列,比如说一个表只有2列,那么以2列为键来建聚集索引,那不就是包含所有的列了嘛



    Please click the Mark as Answer button if a post solves your problem!


    2012年12月6日 12:50
  • @Tim-2009

    就是我上面举的例子呀,表的全部列,比如说一个表只有2列,那么以2列为键来建聚集索引,那不就是包含所有的列了嘛



    Please click the Mark as Answer button if a post solves your problem!


    一般来说很少用全部列建聚集索引,除非表的列很少。

    如果我的表列很多的情况下,建了时间+userid的聚集索引还有没有必要建立以时间的非聚集索引?

    2012年12月7日 6:56
  • @Tim-2009

    就是我上面举的例子呀,表的全部列,比如说一个表只有2列,那么以2列为键来建聚集索引,那不就是包含所有的列了嘛



    Please click the Mark as Answer button if a post solves your problem!


    一般来说很少用全部列建聚集索引,除非表的列很少。

    如果我的表列很多的情况下,建了时间+userid的聚集索引还有没有必要建立以时间的非聚集索引?


    看其他的人回复,他们已经说了
    Please click the Mark as Answer button if a post solves your problem!


    2012年12月7日 8:27