主页 / dba / 问题 / 286452
Accepted
Jez
Asked: 2021-03-05 11:48:05 +0800 CST

为什么 SQL Server 不在此 SELECT ... WHERE 中使用我的索引？

  • 772

我创建了一个带有非聚集 PK 的表（这是设计使然），并在我使用WHERE子句 ( [target_user_id]) 过滤的列上创建了一个额外的非聚集索引：

CREATE TABLE [dbo].[MP_Notification_Audit] (
    [id]                    BIGINT             IDENTITY (1, 1) NOT NULL,
    [type]                  INT                NOT NULL,
    [source_user_id]        BIGINT             NOT NULL,
    [target_user_id]        BIGINT             NOT NULL,
    [discussion_id]         BIGINT             NULL,
    [discussion_comment_id] BIGINT             NULL,
    [discussion_media_id]   BIGINT             NULL,
    [patient_id]            BIGINT             NULL,
    [task_id]               BIGINT             NULL,
    [date_created]          DATETIMEOFFSET (7) CONSTRAINT [DF_MP_Notification_Audit_date_created] DEFAULT (sysdatetimeoffset()) NOT NULL,
    [clicked]               BIT                NULL,
    [date_clicked]          DATETIMEOFFSET (7) NULL,
    [title]                 NVARCHAR (MAX)     NULL,
    [body]                  NVARCHAR (MAX)     NULL,
    CONSTRAINT [PK_MP_Notification_Audit1] PRIMARY KEY NONCLUSTERED ([id] ASC)
);

[...]

CREATE NONCLUSTERED INDEX [IX_MP_Notification_Audit_TargetUser] ON [dbo].[MP_Notification_Audit]
(
    [target_user_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

该表大约有 11,700 行数据，因此应该足以触发对WHERE子句的索引的使用。如果我SELECT只是要过滤的列，则仅使用索引并读取 133 个匹配行 - 仅索引扫描：

SELECT [target_user_id]
  FROM [TestDb].[dbo].[MP_Notification_Audit]
  WHERE [target_user_id] = 100017

执行计划1

但是，一旦我向 中添加额外的列SELECT，索引就会被忽略，并使用谓词进行表扫描以获得结果，读取超过 11,700 行：

SELECT [target_user_id], [patient_id]
  FROM [TestDb].[dbo].[MP_Notification_Audit]
  WHERE [target_user_id] = 100017

执行计划2

为什么它在第二个查询中忽略了我的索引？我原以为使用索引降低到 133 个 RID，然后查询所需的额外行数据，比使用谓词遍历表的每一行更有效？我知道我可以使用子句中INCLUDE需要的额外字段将列添加到索引中，SELECT以使其再次使用索引，但我对为什么在这种情况下它仍然不使用索引感兴趣。

sql-server query-performance

2 个回答

  1. Best Answer

    鉴于您的表的大小（约 11k 行），我认为可以安全地假设 SQL Server 估计在非聚集索引上执行查找然后可能进行多个 RID 查找的成本比执行表更昂贵扫描。

    在您粘贴的第二个查询计划中，有一些证据支持这一理论。正如您在帖子中提到的那样，我通常希望查询优化器建议为您的查询添加覆盖索引。然而，它没有。这对我来说表明 SQL 认为这样做与全表扫描相比几乎没有改进。

    话虽如此，我确信如果您向表中添加更多行，SQL Server 可能会改变主意并要求您添加覆盖索引或开始执行 seek + RID 查找，如您所期望的。如果您启用了查询存储，您可以随时留意此表上导致问题的查询 - 如果它没有给您造成问题，我现在不会担心。

    • 3

  2. 一些额外信息：这是您使用表扫描与索引查找+RID 查找时的统计信息

    表扫描

    Table 'MP_Notification_Audit'. Scan count 1, logical reads 154, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

    索引查找 + RID 查找

    (118 rows affected)
Table 'MP_Notification_Audit'. Scan count 1, logical reads 120, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
   CPU time = 15 ms,  elapsed time = 220 ms.

    可以看出，逻辑读取的差异约为 23%，但绝对数字很低，I/O 系统甚至不应该注意到它，只有 272kB。

    但是 CPU 的差异是显而易见的，RID 查找和嵌套循环花费了 220 毫秒。正如在回答中所说，它很简单。额外的 IO 成本被正确估计为低于 CPU 的额外成本。

    • 2

相关问题