Tips on Optimizing SQL Server Composite Indexes(SQL server 复合索引优化提示)

BY BRAD MCGEHEE（作者：BRAD MCGEHEE）

参照网址：http://www.sql-server-performance.com/2007/composite-indexes/

A composite index is an index that is made up of more than one column. In some cases, a composite index is also acovering index. Generally speaking, composite indexes (with the exception of covering indexes) should be avoided. This is because composite indexes tend to be wide, which means that the index will be larger, requiring more disk I/O to read it, hurting performance.

The main reason composite indexes (not covering composite indexes) are used is to make an index more selective. A better way to ensure selectivity is good database design, not creating composite indexes. [2000, 2005, 2008] Updated 1-29-2009

复合索引是由列数大于一列的列组成的索引。在某些情况下，复合索引也是一个covering index.大体上讲，应该避免使用复合索引（除了covering index 之外）。这是因为复合索引是易于变得很宽泛的（即，人们在使用复合索引的总是趋向于定义很多列，而不问实际需要而言），这种宽泛的定义意味着索引将会是很大，需要更多的磁盘 I/O进行读取，从而损失性能。

使用复合索引（非covering index）的主要原因是使查询更具选择性的。然而保证选择性更好的方式是好的数据库设计，而不是创建复合索引。

*****

If you have no choice but to use a composite index, keep the “width” of it as narrow as possible. This reduces the size of the index and reduces the number of disk I/O reads required to read the index, boosting performance. [2000, 2005, 2008] Updated 1-29-2009

如果你除了用复合索引而没有其他的选择，就要保持复合索引的宽度尽可能的窄。这可以减小索引的大小和减少由于读取索引而读取磁盘I/O的次数，

从而提高性能。

*****

A composite index is generally only useful to a query if the WHERE clause of the query matches the column(s) that are leftmost in the index. So if you create a composite index, such as “City, State”, then a query such as “WHERE City = ‘Springfield’” will use the index, but the query “WHERE STATE = ‘MO’” will not use the index. [2000, 2005, 2008] Updated 1-29-2009

复合索引通常只有在Where子句和定义复合索引时，最左侧的列匹配时，这个复合索引才有用。所以，如果你创建一个复合索引，例如“City,State”,然后有一个像"WHERE City = ‘Springfield’"这样的查询，这个复合索引才会被使用，不然，如果是“WHERE STATE = ‘MO’”这样的查询，这个复合索引将不会被查询优化器(query optimizer)使用。

*****

Even if the WHERE clause in a query does not specify the first column of an available index (which normally disqualifies the index from being used), if the index is a composite index and contains all of the columns referenced in the query, the query optimizer can still use the index, because the index is a covering index. [2000, 2005, 2008] Updated 1-29-2009

尽管WHERE子句没有定义一个可用索引（which normally disqualifies the index from being used）的第一列，如果这个索引是一个复合索引，并且这个复合索引包含在查询中引用的所有的列，那么查询优化器仍然会使用这个索引，因为这个索引是一个covering index。

*****

When you create an index with a composite key, the order of the columns of the key is important. Try to order the columns in the key to enhance selectivity, with the most selective columns to the left most of the key. If you don’t do this, and put a non-selective column as the first part of the key, you risk having the Query Optimizer not use the index at all. [2000, 2005, 2008] Updated 1-29-2009

当你使用 复合键定义了一个复合索引后，（在定义时）这些键对应列的定义顺序（对于这个复合索引）是很重要的。尽量定义这些键值列的顺序，使查询更具选择性，即、将查询经常用到的列，放在复合索引列定义的最左侧（leftmost），如果不这样定义的话，就会有查询优化器根本不使用这个索引的风险。

*****

Sometimes, it is a good idea to split a composite index into multiple single-column indexes. This is because only the first column in a composite index has statistics stored for it. And if this first column is not very selective, it may not be used by the Query Optimizer. But if you were to break up the composite index into multiple indexes, then statistics will be kept for each column, and the Query Optimizer will have better information to make better decisions on how to use indexes. SQL Server has the ability to join two or more individual indexes and intersect them, just as if you were using a composite index, giving you the best benefits of single and composite indexes.

This is not to say that multiple single indexes are always better than a single composite index. Only through testing will you know for sure which strategy will offer the best performance in your particular circumstance. [2000, 2005, 2008] Updated 1-29-2009

通常，将复合索引列拆分成几个单一索引列是一个好主意。这是因为，在复合索引中，只有索引的第一列可以被存储统计。并且如果复合索引第一列定义的不是很有选择性的话，它可能对于查询优化器来说是根本没有用的，即查询优化器根本不会使用这个索引。但是，如果你将一个复合索引拆分为多个单一的索引的话，那么每一个列都会被统计到，从而可以给查询优化器做更好的使用什么索引这个决定提供更有用的信息。 SQL Server有对索引联合(join)和取交集(intersect)的能力，从而使你在使用复合索引的时候，给你单一索引和复合索引二者最大的好处。

*****

As you may know, an index is automatically created for column(s) in your table that you specify as a PRIMARY KEY or as UNIQUE. If two or more columns are involved, and a composite index is created, you should choose how the columns will be ordered in the composite index, instead of accepting the default choices offered by SQL Server.

This is because you always want to use the most selective columns at the left of the key to ensure that the composite index is selective enough to be used by the Query Optimizer. If you don’t do this, then the default order of the columns in the composite index may not be very selective, and the index may not be used by Query Optimizer. [2000, 2005, 2008] Updated 1-29-2009

就像你知道的一样，当你在一个表中定义一个主键（PRIMARY KEY） 着这唯一列(UNIQUE)时，一个索引会被自动创建。如果主键或者唯一列包含有多列的话，一个复合索引将会被创建，你应该自己决定这个复合索引中列的排列顺序（依据更具选择性的原则，selective），而不是简单接收sql server提供给你的默认选择。

之所以要这样，是因为你要确保最具选择性的列排在复合索引的最左边，从而使整个复合索引尽可能多的被查询优化器使用。当然，如果你不这样做，那么sql server提供给你的默认的复合索引可能不是很具有选择性的，从而使得这个索引不能被查询优化器使用。