SQL Azure添加冗余IS NOT NULL子句后索引与行数估算异常问题

正常查询行为 #

执行以下查询时，可正常使用现有索引，执行计划显示预期返回行数约为1：

declare @p__linq__0 nvarchar(4000),@p__linq__1 nvarchar(4000),@p__linq__2 nvarchar(4000)
SELECT 
            [UserId] AS [UserId]
            FROM [dbo].[Users]
            WHERE ([TenantId] = @p__linq__0)
                AND ([IpId] = @p__linq__1)
                AND ([IpUserId] = @p__linq__2)

添加冗余IS NOT NULL后的异常行为 #

当添加冗余的IS NOT NULL子句后：

declare @p__linq__0 nvarchar(4000),@p__linq__1 nvarchar(4000),@p__linq__2 nvarchar(4000)
SELECT 
            [UserId] AS [UserId]
            FROM [dbo].[Users]
            WHERE ([TenantId] = @p__linq__0)
                AND ([IpId] = @p__linq__1)
                AND ([IpUserId] = @p__linq__2)
                AND ([IpUserId] IS NOT NULL)

优化器估算返回行数变为5000，基础案例中执行计划仍正常，但复杂场景会出现全聚集索引扫描问题。由于[IpUserId] = @p__linq__2已隐含非空条件，冗余子句理论上不应产生影响。

表与索引结构 #

CREATE TABLE [dbo].[Users] (
    [UserId] [nvarchar](128) NOT NULL,
    [IpId] [nvarchar](50) NOT NULL,
    [IpUserId] [nvarchar](400) NULL,
    [Identifier] [nvarchar](400) NULL,
    [TenantId] [nvarchar](10) NOT NULL,
    [CreatedAt] [datetime] NULL,
    [Status] [int] NOT NULL,
    CONSTRAINT [PK_dbo.Users] PRIMARY KEY CLUSTERED 
    (
        [UserId] ASC
    )
)
go

CREATE UNIQUE INDEX [IX_IpId_IpUserId] ON [dbo].[Users]
(
    [TenantId],
    [IpId],
    [IpUserId]
)
WHERE ([IpUserId] IS NOT NULL)
go

CREATE INDEX [IX_IpId_IpUserId_Nullable] ON [dbo].[Users]
(
    [TenantId] ASC,
    [IpId] ASC,
    [IpUserId] ASC
)
go

排查结果 #

删除IX_IpId_IpUserId索引后问题消失，推测该问题与该索引的NOT NULL过滤条件直接相关。在复杂场景中：

• 无NOT NULL子句时，执行计划符合预期
• 添加NOT NULL子句后，出现全聚集索引扫描的错误计划

环境信息 #

使用的DBMS版本：Microsoft SQL Azure (RTM) - 12.0.2000.8

原因分析 #

SQL Azure的查询优化器在处理带过滤条件的索引时，对于同时存在IpUserId = @param和IpUserId IS NOT NULL的查询，未能正确识别这两个条件的逻辑等价性（= @param本身已排除NULL值），导致索引匹配逻辑出现偏差：

1. 过滤索引IX_IpId_IpUserId的WHERE条件为IpUserId IS NOT NULL，原本应该能被查询匹配，但显式添加IS NOT NULL后，优化器可能错误地优先选择了不带过滤条件的IX_IpId_IpUserId_Nullable索引
2. 由于IX_IpId_IpUserId_Nullable包含NULL值的统计信息，优化器基于此计算出的估算行数远高于实际值，最终在复杂场景中选择了全聚集索引扫描的低效计划

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