如何让SQL Server在T-SQL中强制使用INDEX SEEK而非SCAN获取下一条记录

问题背景 #

数据库包含上千张表，需模拟旧C-ISAM系统逐行读取逻辑——通过T-SQL查询获取当前记录按键顺序的下一条记录。针对单键、多键表已有对应查询模式，多数表能正常触发INDEX SEEK，但大表GLJNL始终执行INDEX SCAN；尝试过WITH (FORCESEEK)、OPTIMIZE FOR等提示均无效。虽找到UNION ALL改进方案，但查询计划仍低效，且代码为自动生成，无法针对单表单独调整。当前有100+用户同时使用，查询效率至关重要。

附表结构示例 #

-- CUSHST表（正常触发INDEX SEEK）
CREATE TABLE CUSHST (
    CustID INT PRIMARY KEY,
    CustName VARCHAR(50),
    -- 其他业务字段
);
CREATE NONCLUSTERED INDEX IX_CUSHST_CustID ON CUSHST(CustID);

-- GLJNL表（始终执行INDEX SCAN）
CREATE TABLE GLJNL (
    CompanyCode VARCHAR(10),
    JournalDate DATE,
    JournalNo INT,
    LineNo INT,
    -- 其他大量业务字段
);
CREATE NONCLUSTERED INDEX IX_GLJNL_Composite ON GLJNL(CompanyCode, JournalDate, JournalNo, LineNo);

可行解决方案 #

1. 严格匹配索引键前缀顺序 #

SQL Server的INDEX SEEK要求查询过滤条件遵循索引键的前缀匹配规则，必须按索引定义的顺序使用键字段，不能跳过前缀：

• 错误逻辑：跳过CompanyCode直接过滤JournalDate > @CurrentDate
• 正确逻辑：先固定CompanyCode，再依次匹配JournalDate、JournalNo、LineNo的范围条件

2. 消除隐式类型转换 #

自动生成的代码常出现隐式类型转换，导致优化器放弃索引。确保变量类型与表字段完全一致：

-- 显式声明与字段类型匹配的变量
DECLARE @CompCode VARCHAR(10) = @CurrentCompany;
DECLARE @JDate DATE = @CurrentJournalDate;
DECLARE @JNo INT = @CurrentJournalNo;
DECLARE @LNo INT = @CurrentLineNo;

SELECT TOP 1 *
FROM GLJNL WITH (FORCESEEK)
WHERE CompanyCode = @CompCode
  AND (JournalDate > @JDate
       OR (JournalDate = @JDate AND JournalNo > @JNo)
       OR (JournalDate = @JDate AND JournalNo = @JNo AND LineNo > @LNo))
ORDER BY CompanyCode, JournalDate, JournalNo, LineNo;

3. 拆分复杂OR条件为独立SEEK分支 #

复杂OR组合会让优化器难以选择SEEK，拆分后每个分支单独触发SEEK，再合并结果：

WITH NextRecords AS (
    SELECT *
    FROM (
        SELECT TOP 1 *
        FROM GLJNL WITH (FORCESEEK)
        WHERE CompanyCode = @CompCode AND JournalDate > @JDate
        ORDER BY CompanyCode, JournalDate, JournalNo, LineNo
    ) AS d1
    UNION ALL
    SELECT *
    FROM (
        SELECT TOP 1 *
        FROM GLJNL WITH (FORCESEEK)
        WHERE CompanyCode = @CompCode AND JournalDate = @JDate AND JournalNo > @JNo
        ORDER BY CompanyCode, JournalDate, JournalNo, LineNo
    ) AS d2
    UNION ALL
    SELECT *
    FROM (
        SELECT TOP 1 *
        FROM GLJNL WITH (FORCESEEK)
        WHERE CompanyCode = @CompCode AND JournalDate = @JDate AND JournalNo = @JNo AND LineNo > @LNo
        ORDER BY CompanyCode, JournalDate, JournalNo, LineNo
    ) AS d3
)
SELECT TOP 1 *
FROM NextRecords
ORDER BY CompanyCode, JournalDate, JournalNo, LineNo;

每个子查询都能触发INDEX SEEK，最后合并取第一条结果，避免全表扫描。

4. 维护索引与统计信息 #

大表索引碎片或过时统计信息会误导优化器：

• 重建索引：ALTER INDEX IX_GLJNL_Composite ON GLJNL REBUILD;
• 更新统计信息：UPDATE STATISTICS GLJNL WITH FULLSCAN;

5. 创建覆盖索引 #

如果查询不需要返回所有字段，创建包含必要字段的覆盖索引，减少书签查找开销，让优化器更倾向于SEEK：

CREATE NONCLUSTERED INDEX IX_GLJNL_Composite_Covering ON GLJNL(CompanyCode, JournalDate, JournalNo, LineNo)
INCLUDE (Amount, AccountCode, -- 列出查询需要的其他字段
         ...);

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