SQL Server 聚集索引 - 索引顺序问题 [英] SQL Server Clustered Index - Order of Index Question

问题描述

我有一张这样的桌子:

keyA keyB data

keyA 和 keyB 一起是唯一的，是我的表的主键并构成聚集索引.

keyA and keyB together are unique, are the primary key of my table and make up a clustered index.

keyB 有 5 个可能的值，但 keyA 的可能值有无限多个.keyB 通常递增.

There are 5 possible values of keyB but an unlimited number of possible values of keyA,. keyB generally increments.

例如，以下数据可以按 2 种方式排序，具体取决于哪个键列先排序:

For example, the following data can be ordered in 2 ways depending on which key column is ordered first:

keyA keyB data
A    1    X
B    1    X
A    3    X
B    3    X
A    5    X
B    5    X
A    7    X
B    7    X

或

keyA keyB data
A    1    X
A    3    X
A    5    X
A    7    X
B    1    X
B    3    X
B    5    X
B    7    X

我是否需要告诉聚集索引哪个键列的可能值较少，以允许它首先按该值对数据进行排序?还是先排序的性能无关紧要?

Do I need to tell the clustered index which of the key columns has fewer possible values to allow it to order the data by that value first? Or does it not matter in terms of performance which is ordered first?

推荐答案

您应该首先对具有最具选择性的列的复合聚集索引进行排序.这意味着与总行数相比具有最不同值的列.

You should order your composite clustered index with the most selective column first. This means the column with the most distinct values compared to total row count.

B*TREE 索引提高了从表中选择一小部分行的查询的性能."http://www.akadia.com/services/ora_index_selectivity.html?

"B*TREE Indexes improve the performance of queries that select a small percentage of rows from a table." http://www.akadia.com/services/ora_index_selectivity.html?

这篇文章是针对 Oracle 的，但仍然具有相关性.

This article is for Oracle, but still relevant.

此外，如果您有一个持续运行并返回很少字段的查询，您可以考虑创建一个包含所有字段的复合索引 - 它不必访问基表，而是从索引中提取数据.

Also, if you have a query that runs constantly and returns few fields, you may consider creating a composite index that contains all the fields - it will not have to access the base table, but will instead pull data from the index.

ligget78 关于确保提及复合索引中的第一列的评论很重要.

ligget78's comment on making sure to mention the first column in a composite index is important to remember.

这篇关于SQL Server 聚集索引 - 索引顺序问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！