sql 索引设置问题,相同字段在不同的索引中重复设置多次.

Question

各位老师好,

我在生产系统中看到有个表的索引设置好奇怪.相同字段在不同的索引中设置了好多次.这样设置是否合理?

该表大概有3,500,000条记录,数据大小为1.6G左右,但索引却有2.7G.

尝试将其重复的字段的索引([wkdate], [groupdate],[ctno_jobgroup_wkdate])删除后.在SQL Profile监控查询时看到Disk IO Read明显上升. 没有删除索引前大概为45,000， 删除后到了90,000到100,00。重新生产那些索引后.Read又回到了45,000水平.

/****** Object:  Index [wkdate]    Script Date: 07/22/2010 11:15:45 ******/
CREATE NONCLUSTERED INDEX [wkdate] ON [dbo].[t_scan_tag]
(
 [wk_date] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

/****** Object:  Index [groupdate]    Script Date: 07/22/2010 11:19:31 ******/
CREATE NONCLUSTERED INDEX [groupdate] ON [dbo].[t_scan_tag]
(
 [job_group] ASC,
 [wk_date] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

 

/****** Object:  Index [ctno_jobgroup_wkdate]    Script Date: 07/22/2010 11:30:55 ******/
CREATE NONCLUSTERED INDEX [ctno_jobgroup_wkdate] ON [dbo].[t_scan_tag]
(
 [ct_no] ASC,
 [job_group] ASC,
 [wk_date] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

 

/****** Object:  Index [PK_t_scan_tag_1]    Script Date: 07/22/2010 18:40:04 ******/
ALTER TABLE [dbo].[t_scan_tag] ADD  CONSTRAINT [PK_t_scan_tag_1] PRIMARY KEY CLUSTERED
(
 [ct_tag_id] ASC,
 [ct_no] ASC,
 [job_group] ASC,
 [wk_date] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Answer 1

Depends on queries run against the table, they are good if table has more reading processes than writing.

Answer 2

一个字段在多个索引内被使用，这个是看查询需要。很多查询的条件不是一个。

对这个字段单独做索引并不意味着任何查询都可以使用到这个索引。因此就需要对常用的条件分别做索引。这样你就会看到一个字段在多个索引中出现。、

至于你删除这个字段的索引导致的IO增加，可能是因为没有索引使用，sql在使用表扫描代替索引扫描，这个是IO开销增大的一个原因。你可以抓部分sql用查询计划看看，比较删除索引前后的查询计划就知道了。

 

---

family as water

Answer 3

简单地说, 索引依靠顺序检索和减少I/O来提高性能.

对于顺序来说, 一个索引只能有一种顺序, 所以以 a, b 两列来说, 如果创建 a+b的复合索引, 那么只引用b(条件和返回的结果)仅在 b 上的时候, 使用这个复合索引的效果不好, 因为顺序不是在 b 上面, 这种情况下, 如果有一个 b 列的索引会更好.

对于io方面来说, 还是以a,b列, 如果复合索引是 a+b, 但我只想找 a (条件和要返回的结果), 虽然从顺序上来说, a+b的索引是有效的, 但包含了多余的 b 列, 显然没有单独建一个 a 列的索引的 IO 开销低

所以, 一个列出现在多个索引中是允许的, 至于是否必要, 那要看实际的查询引用和列的可选性等因素.

Answer 4

我使用的是同一个查询语句. select job_group, count(ct_tag_id) from t_scan_tag where convert(char,wk_date,120) >= '2010-07-23 07:00:00' and convert(char,wk_date,120) <= '2010-07-23 23:59:59' group by job_group order by job_group 但我觉得非常奇怪的是所建的索引是: [wk_date] --> [job_group],[wk_date] --> [ct_no],[job_group],[wk_date] --> [ct_tag_id],[ct_no],[job_group],[wk_date] 这样重复很多次岂不是很不合理?谢谢..

Answer 5

Does sql use all those indices for that query? Double check it in execution plan. By the way, who created indices?