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

问题描述

我有这样的表：

  keyA keyB data 
  pre> 
 
 
 keyA和keyB一起是唯一的，是我的表的主键，构成一个聚集索引。
 
 
 
 keyB有5个可能的值，但是keyA的可能值的数目不受限制。 
 
 
 
例如，以下数据可以按照两种方式排序，具体取决于首先排序的键列：
  keyA keyB data 
 A 1 X 
 B 1 X 
 A 3 X 
 B 3 X 
 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 
  
我需要告诉聚集索引哪些键列具有较少的可能值，以允许它先按该值排序数据？ 
解决方案
您应该为您的复合聚集索引选择最具选择性的列首先。这意味着与总行数相比具有最不同值的列。
 
 
 
B * TREE索引可提高从表中选择少量行的查询的性能。  http://www.akadia.com/services/ora_index_selectivity.html ？
 
 
 
本文适用于Oracle，但仍然相关。 
 
 
 
此外，如果您有一个持续运行并返回少量字段的查询，您可以考虑创建一个包含所有字段的复合索引，它不必访问
 
 
 
 ligget78的注释确保提及复合索引中的第一列很重要。
 
I have a table like so:
keyA keyB data
keyA and keyB together are unique, are the primary key of my table and make up a clustered index.

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

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
or
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 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?

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's comment on making sure to mention the first column in a composite index is important to remember.

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