解析索引中数据列顺序的选择问题

发布时间：2015-09-18 00:00 来源：未知

在多个列上面建立索引的时候，我们常常会遇到这样的一个问题“需要把哪个列放在过去不久的文章里”，因为索引中列顺序的不同，会对索引的使用，以至性能产生很大的影响。我们该文就来分析这个问题。

对于上面的问题，一个常见的回答就是“把选择性最大列放在过去不久的文章里”，这里为了使得后面的讲述顺序进行，我们先来解释一下选择性的含义。选择性是用来描述数据的差异情况的，例如，假如一个表中有1000条数据，其中的某个字段，如ID，假如每一条数据的ID值都不一样，那么ID的选择性就是1；假如其中有300百个ID是一样的，那么就是说，有700个ID不同，那么选择性就是70%。很显然，数据的选择性越高，那么在上面建立索引效果就越好。

下面，我们就来解释一下为什么在多个列上面建立索引的时候需要把选择性高的列放在最前面。

也许有朋友听到上面的建议之后，在建立任何基于多个列的索引的时候，都会把表的聚集索引所在的列作为这个多列索引的第一个字段。例如，假设现在表中有4个字段，ID,Name,Age，BirthDate，其中ID是主键，也是聚集索引，现在我们需要在Name,BirthDate上面建立索引，这个时候，有朋友发现：ID的选择性最高，那么把ID放在新的索引中，势必会更好，于是一个名字为IX_Index的索引就包含了三个列：ID,Name，BirthDate。到后来，可能就发现，假如冒冒然的这样做，使得这个新建的索引没有发挥作用，反而导致性能问题。

对于数据库中的每一个索引，都会有相应的统计数据信息，这个统计数据显示了数据的分布情况，统计信息以一个类似柱形的形式表现了数据的分布。数据库只把索引中的第一个列的数据分布情况放在柱形图中，换句话说，这个统计信息显示的就是索引中的第一个数据列的数据分布情况（这里面涉及到的内容有点深，大家可以关注本站点的“查询优化器内核系列”，里面会讲述到）。

我给大家看个例子吧，假设在SalesOrderDetail表上面有一个索引：X_SalesOrderDetail_ProductID，运行下面的语句：

这个索引包含的列有：ProductID，SalesOrderID和SalesOrderDetailID。我们查看它的数据的柱形分布图，如下：

我们发现，其中的RANGE_HI_KEY列出的就是ProductID的值，通过图中，我们可以知道：ProductID值为826的数据有305条，值为831的数据有198条。ProductID的值在826到831之间的数据有110条。查询优化器就是根据这个来估算数据的条数的。

通过上面可以知道：把索引中的哪个列放在过去不久的文章里至关重要，假如把一个选择性很低的列放在过去不久的文章里，那么就导致索引的统计数据显示的数据分布完全改变，可能导致查询优化器选择比较低效的执行计划。

下面，我们就通过一个例子来进一步的看看这个问题。

首先，建立一个测试的表，如下：

这个表中有10000条数据，并且这个表是一个堆表，即没有聚集索引的表。并且在这个表中有100个不同的SomeString值，有5000个不同的SomeDate值，而ID是唯一的，全部都不同。

那么，上面的值的选择性如下：

字段名	选择性
ID	100%
SomeString	100/10000*100%=1%
SomeDate	5000/10000*100%=50%

在表中，有一个非聚集索引，假设名字为Idx_test，包含了表中的三个值，三个列在索引中的顺序为：ID,SomeDate，SomeString，按照选择性排序，确实不错！ 

…  WHERE ID = @ID AND SomeDate = @dt AND SomeString = @str  …  WHERE ID = @ID AND SomeDate = @dt  …  WHERE ID = @ID 

换句话说，就是这个索引只在查询中的Where/Join的列按照索引中的列的顺序使用的时候才有效。假如查询是这样的，如下：

对于上面的索引，只有在类似下面的查询结构中发挥作用，如下：

…  WHERE SomeDate = @dt或者…  SomeDate = @dt AND SomeString = @str 

那么，这个索引就不会上面的查询中使用了，那么查询在执行的时候就会扫描整表了。

我们通过执行计划来看看是不是这样的。

对于，WHERE ID = @ID的查询，执行计划如下：

很显然，执行了Seek操作，是很快的。

对于WHERE ID = @ID AND SomeDate = @dt的查询，执行计划如下：

还是进行了Seek操作。

那么对于… SomeDate = @dt AND SomeString = @str的查询，如下：

大家可以看到，这个时候已经开始进行全表扫描了。

我们该文讲述了在索引的进行列的相等操作时候，列的顺序问题，我们下一篇就讲述假如是在列上进行不等操作，例如ID>1，那么索引中的列的顺序还是这样进行吗？

原文链接：http://www.cnblogs.com/yanyangtian/archive/2012/05/03/2480052.html