索引列，列选择率和等式谓词

上一篇 / 下一篇 2012-08-23 10:27:16 / 个人分类：数据库

　　本篇文章也可以叫做”建立索引时那一列应该放到最前面”。

　　通常对于索引列的选择的通常准则都是把最高选择率（译者注：所谓选择率指的是在where子句中作为选择条件使用次数的比例来说的）的列放在最前面，我接下来并不是要说这个准则不对，因为这个准则本身是正确的。但通常在给出这个准则的同时并没有同时给出为什么要把最高选择率的列作为索引列以及索引列的顺序。

　　综上原因，这很有可能导致对索引列选择的误解。比如，在极端情况下，某个人风闻了上述建议后，把所有非聚集索引的的索引键都设置成聚集索引的键（因为这列有很高的选择率），然后开始纠结为什么数据库的性能开始惨不忍睹。51Testing软件测试网6d"mrK'Os9p5E

　　出现上面那种极端情况是因为SQLServer为每一个索引存储统计信息，但这个统计信息仅仅记录索引索引第一列的统计分布，这意味着索引仅仅知道第一列的数据分布，如果第一列不作为谓词使用，可能这个索引依然会被使用，但这并不是全部。51Testing软件测试网!oYH-O?+Q0I\

%i2s k]s'm0　　除了统计分布图之外，SQL Server还为索引列的所有子集存储密度。对于3列作为组合索引键，SQL Server就会存储第一列的密度，第一列和第二列的组合密度以及整个三列的组合密度。密度这个词表示列中所存的数据所不同的概率，公式为1/唯一值。每个索引上的这个值都可以通过使用DBCC Show_Statistics加上DENSITY_VECTOR选项进行查看。

(q-?"ge1rC0　　这也同时意味着，虽然SQL Server知道了第一列的数据分布，也同时知道索引列中其它键组合包含数据的平均值。

&U[/?o'D O0　　那么，对于索引列的先后顺序该怎么做呢？要把最高选择率的列放在第一个，剩下的列先后顺序就无所谓了。

　　下面通过下表来看这究竟是什么意思。51Testing软件测试网 a8TV0P$G

CREATE TABLE ConsideringIndexOrder (
ID INT IDENTITY,
SomeString VARCHAR (100),
SomeDate DATETIME DEFAULT GETDATE()
);

Zf lug^cL0　　假设上面的表有10000行，没有聚集索引所以是基于堆存储的，然后SomeString列包含100个不同的值，SomeDate列包含5000个不同的值，而ID列是自增，所以唯一。

w$q LI3uRL0　　建立一个非聚集索引包含上述散列，顺序为ID,SomeDate,SomeString.51Testing软件测试网%N r'~u,gh

c ]b5P _^0　　上面建立的索引只能在谓词是如下时被使用：

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

　　换句话说，这三列的子集只有按从左到右的顺序包含在谓词中才能被where和join使用。

,kHT#EI/K&\ B5u!i0　　如果你仅仅在where子句之后使用SomeDate列作为过滤条件，则不能使用索引进行查找。这就像是你想通过电话本按照人的名字而不是姓查找电话号码一样，想找到这个人是不能使用目录的，而只能翻遍整个电话版。51Testing软件测试网1tt1C1PUj

　　此外，把具有最高选择率的列放在最左边，但这一列很少在谓词中使用。而大量的where过滤的是其它列的话，只能进行索引扫描，而扫描的代价非常高。

　　由此得出结论如何选择放在索引第一列的列的标准并不唯一，而是基于数据库中使用最多的查询，如果这几列在where等子句之后使用等于号进行过滤的话，那么毫无疑问，选择具有最高选择率的列放到第一位，这样SQL Server就有更多的几率知道这个索引有用，如果不是这样的话，将在where等子句之后使用最多的列放到第一列，这样这个索引就可以适用于更多的查询。

　　下面基于文章前面创建的表来做一些查询。51Testing软件测试网I4iS V$G1jv

　　场景1：用ID作为谓词使用等于号进行过滤51Testing软件测试网%evt,h~7oTM

　　这是最简单的一个场景，因为这个情况直接匹配索引的第一列，索引仅仅使用查找方式找到数据。

　　场景2：用ID和SomeDate列作为谓词使用等于号进行过滤

　　这个场景同样非常简单，使用和非聚集索引顺序相同的子集作为过滤条件，因此也使用查找方式找到数据。

　　场景3：用ID和SomeString列作为谓词使用等于号进行过滤

　　这个场景就有点意思了。只能使用部分使用ID作为索引查找条件，因为SomeString列并不是索引的第二列。这个索引的第二列是SomeDate但查询没有按照SomeDate进行过滤。因此这个查询首先使用ID进行过滤，然后过滤后的列进行字符串比较来找到匹配的行。虽然这个操作是通过查找实现，但SQL Server仅仅使用查找找到ID，然后再将过滤后的行进行字符串比较。