拥有包含聚集索引中主键的非聚集索引是不是很糟糕？ [英] Is it bad to have a non-clustered index that contains the primary key from the clustered index?

问题描述

如果在主键（int）上有一个包含聚簇索引的表，那么将一个（更多）非聚簇索引包含在非关键字列中的列之一是不是多余而且不好 - 聚集索引？

If you have a table with a clustered index on the Primary Key (int), is it redundant and bad to have one (ore more) non-clustered indexes that include that primary key column as one of the columns in the non-clustered index?

推荐答案

实际上可能有正当理由创建非聚集索引相同与群集的。原因是聚集索引带有行数据的包袱，这可能会导致行密度非常差。 IE浏览器。由于群集密钥中没有宽字段，每页可以有2-3行，但聚簇索引键只有20个字节。在上具有非聚集索引与聚簇索引相同的密钥和顺序将为每页提供2-3 数百密钥的密度。非聚集索引可以更有效地回答OLAP / BI工作负载的许多典型聚合查询，这仅仅是因为它将I / O减少了数百倍。

Actually there could be valid reasons to create a non-clustered index identical with the clustered one. The reason is that clustered indexes carry the baggage of the row data and this can make very poor row density. Ie. you can have 2-3 rows per page due to wide fields that are not in the clustered key, but the clustered index key is only, say, 20 bytes. Having a non-clustered index on exactly the same key(s) and order as the clustered index would give a density of 2-3 hundreds of keys per page. A lot of aggregate queries typical for an OLAP/BI workload can be answered more efficiently by the non-clustered index, simply because it reduces the I/O by hundreds of times.

对于包含群集密钥部分的非聚集索引，甚至是相同的密钥但顺序不同，那么所有的投注都会关闭，因为它们显然可以使用对于大量的查询。

As for non-clustered indexes that contain parts of the clustered key, or even the same keys but in different order, then all bets are off as they obviously could be used for a multitude of queries.

所以问题的答案是：它取决于。

要获得更精确的答案，您必须分享您的表格的确切模式以及所涉及的确切查询。

For a more precise answer you'll have to share the exact schema of your table(s) and the exact queries involved.

这篇关于拥有包含聚集索引中主键的非聚集索引是不是很糟糕？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！