主页 / dba / 问题 / 117193
Accepted
Randy Minder
Asked: 2015-10-07 11:05:45 +0800 CST

对为什么使用特定索引感到困惑

  • 772

我有一个表（有 170M 行），如下所示：

CREATE TABLE [dbo].[Panel]
(
    [SubId] [varchar](15) NOT NULL,
    [LineageId] [int] NULL,
    [Buck] [varchar](20) NULL,
    [Lot] [varchar](20) NULL,
    [GlassType] [varchar](20) NULL,
    [ETA] [varchar](200) NULL,
 CONSTRAINT [PK_Panel] PRIMARY KEY CLUSTERED 
(
    [SubId] ASC
)

99% 的针对此表的查询在 Where 子句或连接中引用 SubId。我们的一位 DBA 告诉我，他可以通过创建以下索引使所有这些查询和连接性能更好：

CREATE UNIQUE NONCLUSTERED INDEX [IX-Panel-SubID-I-LineageID] ON [dbo].[Panel]
(
    [SubId] ASC
)
INCLUDE ([LineageId])

当他告诉我这件事时，我以为他疯了。但是我刚刚检查了索引的使用情况，因为这个索引被创建并发现了以下内容：

PK_Panel (232,394 seeks / 2,133 scans)
IX-Panel-SubID-I-LineageID (25,528 seeks / 3644 scans)

看到这里我有点震惊。在什么情况下会使用这个新索引？为什么 SQL Server 会选择它？

或者也许更好的问题是，为什么 SQL Server 选择新索引而不是聚集索引来进行查找？大约 25K 次，它认为在新索引上搜索是更好的选择。

如果这有帮助，LineageId 本质上指示面板的创建位置，并且它可以包含约 35 个不同的值。

sql-server performance

2 个回答

  1. Best Answer

    在集群键涉及的列上创建索引可能看起来有点奇怪。有人问为什么要创建另一个索引，当一个索引已经存在时？

    聚簇索引就是表。也就是说，该表的聚集索引类似于以下索引：

    CREATE INDEX IX_Panel
ON dbo.Panel(SubId ASC)
INCLUDE (
    LineageId
    , Buck
    , Lot
    , GlassType
    , ETA
);

    很明显，这与聚集键上的非聚集索引不同，在您的情况下，它仅“包含”该LineageId列：

    CREATE UNIQUE NONCLUSTERED INDEX [IX-Panel-SubID-I-LineageID] ON [dbo].[Panel]
(
    [SubId] ASC
)
INCLUDE ([LineageId]);

    作为测试，我创建了您的表的模型，然后向其中插入超过 700,000 行：

    INSERT INTO dbo.Panel(LineageId, Buck, Lot, GlassType, ETA)
SELECT (ROW_NUMBER() OVER (ORDER BY o1.object_id, o2.object_id) % 35)
    , SUBSTRING(o2.name, 1, 15)
    , 'lot'
    , 'GlassType'
    , o3.name + o2.name
FROM sys.objects o1
    , sys.objects o2
    , sys.objects o3;

    然后我运行以下查询以查看有关这两个索引的统计信息：

    SELECT o.name
    , ps.index_id
    , ps.index_type_desc
    , ps.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('dbo.Panel'),-1,0, NULL) ps
INNER JOIN sys.objects o ON ps.object_id = o.object_id;

    上面查询的结果是：

    在此处输入图像描述

    很明显，非聚集索引比聚集索引小。在这种情况下，大约是尺寸的 1/5。作为较小的索引意味着查询优化器将在索引以某种方式满足查询要求时选择使用它。

    例如，

    SELECT SubId
FROM dbo.Panel;

    通过扫描非聚集索引只需要从磁盘读取 1,304 页，而不是必须读取 7,105 页来扫描聚集索引。

    在此处输入图像描述

    另一方面，对于引用不在included非聚集索引中的列的查询，SQL Server 可能会使用聚集索引，因为它满足查询的要求。

    以下面的查询为例，您可能直觉地认为查询优化器可能会选择非聚集索引，因为它可以使用它来满足WHERE子句，但是必须查找Buck列的简单行为意味着它更快为 中的值寻找聚簇索引，然后返回子句WHERE中的 3 列：SELECT

    SELECT SubId
    , LineageId
    , Buck
FROM dbo.Panel
WHERE LineageId = 6
    AND SubId >= 27
    AND SubId <= 42;

    在此处输入图像描述

    这有点简化，我强烈建议查看Brent Ozar 的索引页面

    • 6

  2. 选择较窄的索引通常更好，特别是如果它涵盖，这意味着那些使用搜索的查询可能只需要 SubID 和 LineageID。尽管即使索引没有覆盖，但如果行数足够小以至于总体成本仍然较低，它仍可能会选择较窄的索引并为其他列执行查找。

    除非查询实际上需要表中的所有或大部分列，否则聚集索引将是一个糟糕的选择，因为它分布在更多的页面上，这意味着需要更多的 I/O 来获取您需要的列的子集。如果这些都可以从更窄的索引中获取，这将是一种更便宜的数据访问方式。

    成本在很大程度上取决于为满足查询而需要读取的页数。聚集索引是整个表，因此它永远不会比任何非聚集索引更瘦。这意味着，通常，对于相同数量的行，聚簇索引比非聚簇索引需要更多的 I/O。当非聚集索引不满足查询时，比例会提示，这意味着 SQL Server 需要在转到聚集索引和转到非聚集索引并执行查找之间做出选择。后者大致根据估计的行数和列宽来判断，因此确实有很多“视情况而定”会发挥作用。

    可悲的是，索引使用统计 DMV 没有区分单例搜索和伪装范围扫描的搜索（无论是 2 行还是 200 万行），所以仅仅看到一些搜索与扫描并不能真正告诉我们整个故事。我们没有足够的关于您实际查询的信息来向您确切说明做出此选择的原因，但我希望我至少在较高层次上指出了可能的原因。

    • 5

相关问题