如何让IS NOT NULL查询使用Index Seek而非Index Scan？

场景与查询 #

执行以下查询时，执行计划始终显示为INDEX SCAN，尝试过多种索引（含过滤索引）甚至强制索引都无法触发INDEX SEEK，需优化查询性能：

SELECT Compra_ID
FROM [Fart_Compras_dss_tracking]
WHERE [local_create_peer_timestamp] IS NOT NULL

目标表结构 #

CREATE TABLE [DataSync].[Fart_Compras_dss_tracking]
(
    [COMPRA_ID] [int] NOT NULL,
    [Sucursal_ID] [int] NOT NULL,
    [update_scope_local_id] [int] NULL,
    [scope_update_peer_key] [int] NULL,
    [scope_update_peer_timestamp] [bigint] NULL,
    [local_update_peer_key] [int] NOT NULL,
    [local_update_peer_timestamp] [timestamp] NOT NULL,
    [create_scope_local_id] [int] NULL,
    [scope_create_peer_key] [int] NULL,
    [scope_create_peer_timestamp] [bigint] NULL,
    [local_create_peer_key] [int] NOT NULL,
    [local_create_peer_timestamp] [bigint] NOT NULL,
    [sync_row_is_tombstone] [int] NOT NULL,
    [last_change_datetime] [datetime] NULL,

    CONSTRAINT [PK_DataSync.Fart_Compras_dss_tracking] 
        PRIMARY KEY CLUSTERED ([COMPRA_ID] ASC, [Sucursal_ID] ASC)
                WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

已创建的非聚集过滤索引 #

CREATE NONCLUSTERED INDEX [Idx_Text_4] 
ON [DataSync].[Fart_Compras_dss_tracking] ([COMPRA_ID] ASC,
                                           [Sucursal_ID] ASC,
                                           [local_create_peer_timestamp] ASC,
                                           [local_update_peer_timestamp] ASC)
INCLUDE ([update_scope_local_id], [scope_update_peer_key],
         [scope_update_peer_timestamp], [local_update_peer_key],
         [create_scope_local_id], [scope_create_peer_key],
         [scope_create_peer_timestamp], [local_create_peer_key],
         [sync_row_is_tombstone], [last_change_datetime]) 
WHERE ([local_create_peer_timestamp] IS NOT NULL)
WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
GO

问题核心 #

针对IS NOT NULL条件，需创建何种索引才能触发INDEX SEEK并提升查询性能？

解决方案 #

1. 先排查字段属性与过滤条件的矛盾
   从表结构可明确看到local_create_peer_timestamp是[bigint] NOT NULL，即该字段本身不允许为空值。你的查询条件WHERE [local_create_peer_timestamp] IS NOT NULL会匹配表中所有行，SQL Server选择INDEX SCAN是合理决策——返回全量数据时，扫描的成本通常低于查找。

2. 若字段属性定义有误（实际允许为空），调整索引策略
   假设表结构定义错误，该字段实际可空，需创建轻量化覆盖过滤索引：

   • 无需包含无关字段，仅保留查询返回列与过滤条件字段：

     CREATE NONCLUSTERED INDEX [IX_Fart_Compras_dss_tracking_local_create]
     ON [DataSync].[Fart_Compras_dss_tracking] ([local_create_peer_timestamp])
     INCLUDE ([COMPRA_ID])
     WHERE ([local_create_peer_timestamp] IS NOT NULL);
     

   • 这种索引体积更小，过滤条件直接命中索引筛选器，SQL Server更易选择INDEX SEEK操作，仅当过滤后的数据量占比小时，该索引的性能优势会更明显。

3. 强制索引无效的原因
   当查询需返回大部分或全量数据时，SQL Server优化器会判定扫描成本更低，即使强制指定索引，优化器也可能忽略提示；即便执行计划显示为SEEK，实际性能也未必优于扫描。只有当过滤条件能筛选出小比例数据时，INDEX SEEK才会体现性能优势。

内容的提问来源于stack exchange，提问作者Raul