SQL Server 2008中的聚集索引
我有一个带有复合主键的表。它创建聚集索引。如果我在 WHERE 子句中使用该复合主键中的几列，该索引仍然有效吗？或者我是否必须根据 WHERE 中使用的列创建新索引？任何帮助将不胜感激。
I have a table with a composite primary key. It creates clustered index. If I use a few columns from that composite primary key in a WHERE clause, will that index be still effective? Or do I have to make new index based on the columns used in WHERE? Any help would be appreciated.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
任何索引，无论是否聚集，仅对定义中最左侧的列都是 WHERE 子句的一部分的查询有用。
如果您在
(Col1,Col2,Col3)上有索引，则此索引对于使用全部 3 列或Col2和Col1 的 WHERE 子句非常有用，或者只是Col1。但是，一旦搜索中不包含Col1，索引就毫无用处。Any index, whether clustered or not, is only useful to a query provided it's Left-most columns in the definition are all part of the WHERE clause.
If you have an index on
(Col1,Col2,Col3), then this index can be useful for WHERE clauses that use all 3 columns, orCol2andCol1, or justCol1. But as soon asCol1isn't included in the search, the index is useless.如果可能的话，我会尽量避免使用复合键——尤其是主键。另外：如果您有组合键，则仅当您使用最左边的 n 列时才有效，例如，如果您的组合键中的第三个位置有一列，并且您的搜索只有一个
WHERE在第三列上，索引将无法使用。理想情况下，集群键是一个小、稳定、唯一且不断增加的列 - INT 或 BIGINT 作为默认选项。不要让你的聚类键超载！不要让它太宽，并且无论如何，尽量避免不同大小的列（例如
VARCHAR- 它们会带来额外的开销）还有另一个需要考虑的问题：表上的集群键将是也添加到表中每个非聚集索引的每个条目中 - 因此您确实希望确保它尽可能小。通常，具有 2+ 十亿行的 INT 对于绝大多数表来说应该足够了 - 与作为集群键的 GUID 相比，您可以在磁盘和服务器内存中节省数百兆字节的存储空间。
还有一些值得深思的东西 - Kimberly Tripp 写的很棒的东西 - 读它，再读它，消化它！这确实是 SQL Server 索引的福音。
If ever possible, I try to avoid composite keys - especially the primary key. Also: if you have a composite key, it's only effective if you use the left-most n columns, e.g. if you have a column at the third position in your composite key and your search only has a
WHEREon that third column, the index won't be able to be used.The clustering key would ideally be a small, stable, unique and ever-increasing column - INT or BIGINT as your default option. Don't overload your clustering key! Don't make it too wide, and by all means, try to avoid columns of varying size (like
VARCHAR- they carry additional overhead)There's another issue to consider: the clustering key on a table will be added to each and every entry on each and every non-clustered index on your table as well - thus you really want to make sure it's as small as possible. Typically, an INT with 2+ billion rows should be sufficient for the vast majority of tables - and compared to a GUID as the clustering key, you can save yourself hundreds of megabytes of storage on disk and in server memory.
Some more food for thought - excellent stuff by Kimberly Tripp - read it, read it again, digest it! It's the SQL Server indexing gospel, really.