关于综合指数的问题 [英] Question regarding composite indexes

问题描述

我工作的生产数据库非常庞大。大约少于10个表占据了数据库大小的90％。



一张桌子的大小约为25 gig，这张桌子上的索引是132 gig！大致有44,415,774行，121列和48个索引。所有索引都是非唯一且非聚集的。



似乎有几个重叠的索引。示例：

指数1：DueDate

指数2：WCNum

指数3：复合即DueDate + WCNum。



据我所知，复合DueDate + WCNum是否仍然涵盖仅由DueDate或WCNum过滤的查询？是真的需要所有3个索引还是复合覆盖所有场景？



谢谢！

The production database where I work is very large. There are roughly less than 10 tables that are taking up 90% of the database size.

One table is roughly 25 gig in size and the indexes on this one table are 132 gig! Roughly 44,415,774 rows, 121 columns and 48 indexes. All the indexes are non-unique and non-clustered.

There are what appears to be several overlapping indexes. Example:
Index 1: DueDate
Index 2: WCNum
Index 3: composite that is DueDate + WCNum.

For my own knowledge, would the composite DueDate + WCNum still cover queries filtered by DueDate or WCNum only? Are all 3 indexes truly needed or would the composite cover all there scenarios?

Thanks!

推荐答案

由于索引3，索引1将是多余的，但是，您仍然需要索引2.

Index 1 would be redundant because of index 3, however, you would still need Index 2.

这篇关于关于综合指数的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！