关于表索引，希望给个建议！

[sunnystar365]

有一张表，数据量很大，而且每天都有大批量数据录入！所以如何建立索引，希望大家给个建议！
Table中用到最多的where字段的查询，A，B，C是Varchar(50)字段，D为DateTime类型
查询所用：Select * From Table Where A='条件1' and D>'时间1' and D<'时间2'
更新所用：Update Table Set E='数据1'，F='数据2' Where B='条件1' and C='条件2'

请大家提下建议，哪些字段该用聚合索引，那些该用非聚合索引，或者是否该用组合索引！

还有一个问题，当表中300万数据，我对某些字段建立了非聚合索引，查询很快，但当表中又增加100万，200万数据时，查询又慢，我把索引删除，然后重新建立，查询速度有快，所以想请问，非聚合索引是不是需要经常维护重建？那聚合索引是否也需要经常维护重建呢？

[专注or全面]

Select * From Table Where A='条件1' and D>'时间1' and D<'时间2' 有区间查询，D列建立聚集索引吧 其他列上建立非聚集索引，建议在A上建立一个非聚集索引，B,C列上建立符合索引 至于索引的重建与否，看碎片吧，碎片达到一定程度，超过30%是rebuild，大于5%且不超过30%是REORGANIZE 参考微软给出的建议吧 http://msdn.microsoft.com/zh-cn/library/ms189858.aspx

[發糞塗牆]

还有一个问题，当表中300万数据，我对某些字段建立了非聚合索引，查询很快，但当表中又增加100万，200万数据时，查询又慢，我把索引删除，然后重新建立，查询速度有快，所以想请问，非聚合索引是不是需要经常维护重建？那聚合索引是否也需要经常维护重建呢？

先回答你这个问题，常见情况：1、统计信息缺乏维护，或者自动更新统计信息的选项被关闭了。2、索引设计不合理，插入100万数据之后碎片增长非常快，碎片过多导致性能慢，重建之后碎片减少，速度提升。

如果你的查询是用了*号，或者可以换成列名但是列名其实也非常多，那么可能会引起键值查找。不过键值查找不一定就是性能问题，还要看你查询的相对数量，

对于查询：
Where A='条件1' and D>'时间1' and D<'时间2' 这个你可以用这个公式算一下：
select 1.0/distinct a from tb
select 1.0/distinct d from tb
上面的结果中，那个越小，就把那个作为索引的第一列，这里建议建一个复合索引，至于是A在第一列还是D在第一列就要算一下。另外由于A是字符型，最好统计一下最大、最小及平均长度，如果幅度不高，假设A的实际数据长度是15~20，那么建议换成char(20)，如果幅度很大，从1~50都有可能，那么保留varchar
D中，在保证数据能正确反映业务需求的情况下，使用存储空间越小的日期类型越好。比如smalldatetime等。这样你的索引和数据存储空间都小，逻辑IO也相对较少。


对于更新：
B,C做复合索引即可，列的次序和查询部分相同。由于E/F是需要更新的列，不建议加索引。

至于聚集索引，如果你的表有代理键，比如自增ID、GUID等，那可以作为主键（主键默认就是聚集索引），如果没有的话，找一个或者几个能唯一标识每一行的列作为主键。

在给你更多不知道有用无用的建议之前，先要你提供更多信息，比如SQL Server版本、具体数据量。不要靠一个多字，这个没有标准，我接触过2亿的单表，也听过别人把过万的表称为大表。另外在不涉及商业机密的前提下，把你知道的信息都给出来，比如服务器配置、使用的程序语言，系统拿来干嘛的等等。

关机看书，明天早上起来再看，回复记得引用一下

[發糞塗牆]

引用 1 楼 x_wy46 的回复:

Select * From Table Where A='条件1' and D>'时间1' and D<'时间2' 有区间查询，D列建立聚集索引吧 其他列上建立非聚集索引，建议在A上建立一个非聚集索引，B,C列上建立符合索引 至于索引的重建与否，看碎片吧，碎片达到一定程度，超过30%是rebuild，大于5%且不超过30%是REORGANIZE 参考微软给出的建议吧 http://msdn.microsoft.com/zh-cn/library/ms189858.aspx

大部分微软建议都可取，这个就不可以，这个所谓的标准被大量MVP强烈要求取消。作为入门还可以，作为标准，不行，一个才10来个页的索引，即使100%的碎片，也不碍事，反而你重建还需要更新一大批系统信息。

[發糞塗牆]

有几个世界级的MVP给出的建议是500~1000个页或以上的索引才需要进行碎片处理

[专注or全面]

引用 3 楼 DBA_Huangzj 的回复:

[quote=引用 1 楼 x_wy46 的回复:] Select * From Table Where A='条件1' and D>'时间1' and D<'时间2' 有区间查询，D列建立聚集索引吧 其他列上建立非聚集索引，建议在A上建立一个非聚集索引，B,C列上建立符合索引 至于索引的重建与否，看碎片吧，碎片达到一定程度，超过30%是rebuild，大于5%且不超过30%是REORGANIZE 参考微软给出的建议吧 http://msdn.microsoft.com/zh-cn/library/ms189858.aspx

大部分微软建议都可取，这个就不可以，这个所谓的标准被大量MVP强烈要求取消。作为入门还可以，作为标准，不行，一个才10来个页的索引，即使100%的碎片，也不碍事，反而你重建还需要更新一大批系统信息。[/quote] 也是， 不过话说回来，重建或者重组索引是因为发现了它上面的性能问题，才去做这个的， 假如是小表，占用空间不大，不太可能在它上面发生性能问题

[發糞塗牆]

但是作为常规管理，会有很多dba直接重建整个库的全部索引，而不是有选择性。

引用 5 楼 x_wy46 的回复:

[quote=引用 3 楼 DBA_Huangzj 的回复:] [quote=引用 1 楼 x_wy46 的回复:] Select * From Table Where A='条件1' and D>'时间1' and D<'时间2' 有区间查询，D列建立聚集索引吧 其他列上建立非聚集索引，建议在A上建立一个非聚集索引，B,C列上建立符合索引 至于索引的重建与否，看碎片吧，碎片达到一定程度，超过30%是rebuild，大于5%且不超过30%是REORGANIZE 参考微软给出的建议吧 http://msdn.microsoft.com/zh-cn/library/ms189858.aspx

大部分微软建议都可取，这个就不可以，这个所谓的标准被大量MVP强烈要求取消。作为入门还可以，作为标准，不行，一个才10来个页的索引，即使100%的碎片，也不碍事，反而你重建还需要更新一大批系统信息。[/quote] 也是， 不过话说回来，重建或者重组索引是因为发现了它上面的性能问题，才去做这个的， 假如是小表，占用空间不大，不太可能在它上面发生性能问题[/quote]

[sunnystar365]

引用 1 楼 x_wy46 的回复:

Select * From Table Where A='条件1' and D>'时间1' and D<'时间2' 有区间查询，D列建立聚集索引吧 其他列上建立非聚集索引，建议在A上建立一个非聚集索引，B,C列上建立符合索引 至于索引的重建与否，看碎片吧，碎片达到一定程度，超过30%是rebuild，大于5%且不超过30%是REORGANIZE 参考微软给出的建议吧 http://msdn.microsoft.com/zh-cn/library/ms189858.aspx

如果只在D列上使用聚集索引，那A列需要经常分类汇总，所以是不是应该使用聚集索引的组合？顺序为A,D？ 什么语句查看索引碎片使用情况，再请教一下rebuild和reorganize的使用方法，谢谢！

[sunnystar365]

引用 2 楼 DBA_Huangzj 的回复:

还有一个问题，当表中300万数据，我对某些字段建立了非聚合索引，查询很快，但当表中又增加100万，200万数据时，查询又慢，我把索引删除，然后重新建立，查询速度有快，所以想请问，非聚合索引是不是需要经常维护重建？那聚合索引是否也需要经常维护重建呢？ 先回答你这个问题，常见情况：1、统计信息缺乏维护，或者自动更新统计信息的选项被关闭了。2、索引设计不合理，插入100万数据之后碎片增长非常快，碎片过多导致性能慢，重建之后碎片减少，速度提升。 如果你的查询是用了*号，或者可以换成列名但是列名其实也非常多，那么可能会引起键值查找。不过键值查找不一定就是性能问题，还要看你查询的相对数量， 对于查询： Where A='条件1' and D>'时间1' and D<'时间2' 这个你可以用这个公式算一下： select 1.0/distinct a from tb select 1.0/distinct d from tb 上面的结果中，那个越小，就把那个作为索引的第一列，这里建议建一个复合索引，至于是A在第一列还是D在第一列就要算一下。另外由于A是字符型，最好统计一下最大、最小及平均长度，如果幅度不高，假设A的实际数据长度是15~20，那么建议换成char(20)，如果幅度很大，从1~50都有可能，那么保留varchar D中，在保证数据能正确反映业务需求的情况下，使用存储空间越小的日期类型越好。比如smalldatetime等。这样你的索引和数据存储空间都小，逻辑IO也相对较少。 对于更新： B,C做复合索引即可，列的次序和查询部分相同。由于E/F是需要更新的列，不建议加索引。 至于聚集索引，如果你的表有代理键，比如自增ID、GUID等，那可以作为主键（主键默认就是聚集索引），如果没有的话，找一个或者几个能唯一标识每一行的列作为主键。 在给你更多不知道有用无用的建议之前，先要你提供更多信息，比如SQL Server版本、具体数据量。不要靠一个多字，这个没有标准，我接触过2亿的单表，也听过别人把过万的表称为大表。另外在不涉及商业机密的前提下，把你知道的信息都给出来，比如服务器配置、使用的程序语言，系统拿来干嘛的等等。 关机看书，明天早上起来再看，回复记得引用一下

谢谢你的回答，疑虑又少了一部分！ 那再请教一下创建索引的语句问题 1.聚集索引如何创建，聚集组合索引如何创建 2.非聚集索引如何创建 3.索引的使用情况如何查看，又如何整理呢？

[發糞塗牆]

create clustered/nonclustered index 索引名 on 表名 (列名,......) 具体语法联机丛书大把。怎么查看索引使用情况要看sqlserver的版本。

[sunnystar365]

引用 9 楼 DBA_Huangzj 的回复:

create clustered/nonclustered index 索引名 on 表名 (列名,......) 具体语法联机丛书大把。怎么查看索引使用情况要看sqlserver的版本。

版本是 Sql2008 SP1

[专注or全面]

select * from sys.dm_db_index_physical_stats(DB_ID('数据库名字'),object_id('表名字'),null,null,'detailed') 碎片参考这个列avg_fragmentation_in_percent 好多个参数，你自己先慢慢查查sys.dm_db_index_physical_stats联机丛书吧 重建索引 alter index index_name on tableNamerebuild 重新组织索引 alter index index_name on tableNamerebuild reorganize 置于怎么建立索引，这个东西吧，没有标准答案，主要看实际情况来的，我觉得版主分析的很好了，他叫你的是内功

[發糞塗牆]

这是查碎片的

SELECT  '[' + DB_NAME() + '].[' + OBJECT_SCHEMA_NAME(ddips.[object_id],
                                                     DB_ID()) + '].['
        + OBJECT_NAME(ddips.[object_id], DB_ID()) + ']' AS [statement] ,
        i.[name] AS [index_name] ,
        ddips.[index_type_desc] ,
        ddips.[partition_number] ,
        ddips.[alloc_unit_type_desc] ,
        ddips.[index_depth] ,
        ddips.[index_level] ,
        CAST(ddips.[avg_fragmentation_in_percent] AS SMALLINT) AS [avg_frag_%] ,
        CAST(ddips.[avg_fragment_size_in_pages] AS SMALLINT) AS [avg_frag_size_in_pages] ,
        ddips.[fragment_count] ,
        ddips.[page_count]
FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'limited') ddips
        INNER JOIN sys.[indexes] i ON ddips.[object_id] = i.[object_id]
                                      AND ddips.[index_id] = i.[index_id]
WHERE   ddips.[avg_fragmentation_in_percent] > 15
        AND ddips.[page_count] > 500
ORDER BY ddips.[avg_fragmentation_in_percent] ,
        OBJECT_NAME(ddips.[object_id], DB_ID()) ,
        i.[name]