SQL Server非聚集索引设计

Question

此问题涉及在Server 2005中设计非聚集索引.

我有一张几百万行的大桌子。行只被读取或插入。大多数操作都是读操作。我一直在查看访问表的各种SELECT查询，目的是提高读取访问速度。磁盘空间并不是真正的问题。(每一行都有一个唯一的ID，我正在使用它作为聚集索引中的单个字段。)

我的问题是，如果非聚集索引索引比查询使用的列多，那么这是否会导致查询执行速度比与查询完全匹配的索引更慢？

随着不同查询数量的增加，在其WHERE子句中使用的列的排列数也会增加。我不确定有几个列的索引(每个查询一个)和更多列的索引更少之间的权衡。

例如，假设我有两个SELECT查询。第一种在WHERE子句中使用A、B、C和D列，第二种使用A、B、E和F。这里的最佳做法是定义两个索引，一个是A/B/C/D，另一个是A/B/E/F；还是A/B/C/D/E/F的单一索引？

Answer 1

首先，索引中列的顺序很重要。因此，构建/调优查询将使您能够很好地利用您构建的索引。

是否单独使用两个索引或一个索引取决于争用中列的依赖关系和运行的查询类型。在您的示例中，如果E和F列与C和D列相关或依赖，那么有一个索引覆盖所有列是有意义的。

Answer 2

我的问题是，如果非聚集索引索引比查询使用的列多，那么这是否会导致查询执行速度比与查询完全匹配的索引更慢？

不，对于使用索引中的前1，2，n列的查询，拥有更多的列并不会减慢查询时间。尽管如此，如果内存有限，将索引加载到内存中可能会将其他东西从内存中挤出并减慢查询速度，但是如果您有足够的内存，这就没有问题了。

随着不同查询数量的增加，其WHERE子句中使用的列的排列数也随之增加。我不确定有几个列的索引(每个查询一个)和更多列的索引更少之间的权衡。

您应该首先将最常见的查询唯一字段添加到索引中。有许多列的索引较少，可能无法满足您的需要。

例如，如果有具有下列列的索引：

• ColumnA
• ColumnB
• ColumnC
• ColumnD
• ColumnE
• ColumnF

按照这个顺序，对ColumnA、ColumnB、ColumnC、ColumnD的查询过滤.将使用索引，但如果您只是在查询ColumnE或ColumnF，则不会使用索引。

如果一个表上有6个索引，那么采用不同的方法，每个索引只有一个列。

ColumnA

• Index2 - ColumnB

• Index3 - ColumnC

• Index4 - ColumnD

• Index5 - ColumnE

• Index6 - ColumnF

在本例中，这6个索引中只有一个将用于任何查询。

另外，如果索引包含的值不是很有选择性，那么它可能对您没有帮助。例如，如果您有一个名为性别的列，该列可能包含以下值(男性、女性和未知数)，那么它可能不会帮助您在索引中包含该列。当查询运行时，Server可能会确定它们的列没有足够的选择性，只是假设完整的表扫描会更快。

有许多方法可以找到查询所使用的索引，但我使用的一种方法是查看从未使用过的索引。在数据库中运行以下查询，并确定您认为正在使用的索引是否确实被使用。

SELECT iv.table_name, 
        i.name                           AS index_name, 
        iv.seeks + iv.scans + iv.lookups AS total_accesses, 
        iv.seeks, 
        iv.scans, 
        iv.lookups, 
        t.indextype, 
        t.indexsizemb 
FROM   (SELECT i.object_id, 
                Object_name(i.object_id) AS table_name, 
                i.index_id, 
                SUM(i.user_seeks)        AS seeks, 
                SUM(i.user_scans)        AS scans, 
                SUM(i.user_lookups)      AS lookups 
        FROM   sys.tables t 
                INNER JOIN sys.dm_db_index_usage_stats i 
                    ON t.object_id = i.object_id 
        GROUP  BY i.object_id, 
                    i.index_id) AS iv 
        INNER JOIN sys.indexes i 
            ON iv.object_id = i.object_id 
            AND iv.index_id = i.index_id 
        INNER JOIN (SELECT sys_schemas.name AS schemaname, 
                            sys_objects.name AS tablename, 
                            sys_indexes.name AS indexname , 
                            sys_indexes.type_desc AS indextype , 
    CAST(partition_stats.used_page_count * 8 / 1024.00 AS DECIMAL(10, 3)) AS indexsizemb 
FROM   sys.dm_db_partition_stats partition_stats 
INNER JOIN sys.indexes sys_indexes 
    ON partition_stats.[object_id] = sys_indexes.[object_id] 
        AND partition_stats.index_id = sys_indexes.index_id 
        AND sys_indexes.type_desc <> 'HEAP' 
INNER JOIN sys.objects sys_objects 
    ON sys_objects.[object_id] = partition_stats.[object_id] 
INNER JOIN sys.schemas sys_schemas 
    ON sys_objects.[schema_id] = sys_schemas.[schema_id] 
        AND sys_schemas.name <> 'SYS') AS t 
ON t.indexname = i.name 
AND t.tablename = iv.table_name 
--WHERE t.IndexSizeMB > 200 
WHERE  iv.seeks + iv.scans + iv.lookups = 0 
ORDER  BY total_accesses ASC; 

我通常跟踪从未使用过的索引，或者在Server重新启动几个月后没有使用的索引，并确定是否应该删除这些索引。有时，过多的索引会减慢Server计算出运行查询的最佳路径，而删除未使用的索引可以加快该进程。

我希望这有助于理解您的索引。

Answer 3

现有的答案已经很好了。这里有一个新的想法:在一定的工作负载和内存可用性下找到一组最优的索引是一个困难的问题，需要对一个很大的搜索空间进行彻底的搜索。

数据库引擎优化顾问(DTA)实现了这一点！我建议您记录一个有代表性的工作负载(包括写！)让DTA给你建议。它也将考虑磁盘空间。