SQL Server 2017未使用索引问题排查与优化咨询

场景说明 #

现有一张存储了1.41亿条数据的表[UniqueIdentifier]，已创建非主键索引idx_IssuerNotificationTime，表结构与索引定义如下：

CREATE TABLE [UniqueIdentifier]
(
    [Id] [varchar](100) NOT NULL,
    [ShortUI] [varchar](50) NULL,
    [StateId] [tinyint] NOT NULL,
    [TypeId] [tinyint] NOT NULL,
    [GenerationRequestedOn] [datetimeoffset](7) NULL,
    [AnticipatedUsageOn] [datetimeoffset](7) NULL,
    [IssuerNotificationTime] [datetimeoffset](7) NULL,
    [ParentId] [varchar](100) NULL,
    [ProductItemId] [int] NULL,
    [RecalledByCode] [varchar](100) NULL,
    [FacilityId] [varchar](300) NULL,
    [PendingArrival] [bit] NULL,

    CONSTRAINT [PK_UniqueIdentifier] 
        PRIMARY KEY NONCLUSTERED ([Id] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [idx_IssuerNotificationTime] 
ON [UniqueIdentifier]([IssuerNotificationTime] 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) ON [PRIMARY]

执行以下查询时，预期返回97.2万条数据，但实际执行了全表扫描，未使用上述索引；添加ORDER BY IssuerNotificationTime后仍未触发索引使用，仅增加排序步骤。索引属性显示页面填充度95%，碎片率95%。

SELECT
    * 
FROM
    [UniqueIdentifier] u 
WHERE 
    u.IssuerNotificationTime BETWEEN DATEADD(DAY, -7, DATEADD(YEAR, -5, GETDATE())) 
                                 AND DATEADD(YEAR, -5, GETDATE()) ;

未使用索引的原因 #

1. 非覆盖索引的回表成本过高
   当前索引仅包含IssuerNotificationTime列，查询使用SELECT *需要通过索引找到匹配行后，再到非聚集主键索引中回表获取其他列数据。当匹配行数达到近百万级时，优化器会判断回表的IO总成本高于全表扫描，因此选择全表扫描。

2. 索引碎片率过高
   索引碎片率高达95%，会导致索引页的连续性极差，读取索引时需要频繁随机IO，大幅降低索引的查询效率。优化器评估后认为使用该索引的性能不如全表扫描，因此放弃使用。

3. 统计信息可能过时
   如果表的统计信息未及时更新，优化器无法准确评估查询返回的行数和成本，可能误判索引的性价比，选择全表扫描。

优化方案 #

1. 创建覆盖索引 #

将查询所需的所有列包含到索引中，避免回表操作，让优化器优先选择索引。由于查询使用SELECT *，可以创建包含所有非索引键列的覆盖索引：

CREATE NONCLUSTERED INDEX [idx_IssuerNotificationTime_Covering] 
ON [UniqueIdentifier]([IssuerNotificationTime] ASC)
INCLUDE (
    [Id], [ShortUI], [StateId], [TypeId], 
    [GenerationRequestedOn], [AnticipatedUsageOn], 
    [ParentId], [ProductItemId], [RecalledByCode], 
    [FacilityId], [PendingArrival]
)
WITH (DROP_EXISTING = OFF, ONLINE = ON) ON [PRIMARY]

注：ONLINE = ON仅适用于SQL Server企业版/开发版，可避免重建索引时锁表；若使用其他版本，可移除该参数。

2. 重建索引消除碎片 #

高碎片率会严重影响索引性能，重建索引可以彻底消除碎片，恢复索引页的连续性：

ALTER INDEX [idx_IssuerNotificationTime] ON [UniqueIdentifier] REBUILD WITH (ONLINE = ON);

若无法在线重建，可使用离线重建（会锁表，建议在业务低峰期执行）：

ALTER INDEX [idx_IssuerNotificationTime] ON [UniqueIdentifier] REBUILD;

3. 更新统计信息 #

确保优化器拥有准确的数据分布统计，以便正确评估查询成本：

UPDATE STATISTICS [UniqueIdentifier] WITH FULLSCAN;

FULLSCAN会扫描全表生成统计信息，比默认抽样更准确，适合数据量较大的表。

4. 强制使用索引（临时方案） #

若以上方法均未生效，可通过查询提示强制优化器使用指定索引，但不推荐作为长期方案（优化器的成本评估通常更合理）：

SELECT
    * 
FROM
    [UniqueIdentifier] u WITH (INDEX([idx_IssuerNotificationTime]))
WHERE 
    u.IssuerNotificationTime BETWEEN DATEADD(DAY, -7, DATEADD(YEAR, -5, GETDATE())) 
                                 AND DATEADD(YEAR, -5, GETDATE()) ;

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