为何HASH JOIN中列A未作为Seek Predicate？与INNER JOIN差异解析

测试环境与数据准备 #

在SQL Server 2016、2022环境下，创建数据表并插入测试数据：

CREATE TABLE MAIN (
    A       VARCHAR(30)     ,
    B       VARCHAR(20)     ,
    C       INT             ,
    D       INT             ,
    E       DATE            
)

CREATE TABLE SUB (
    ProjectCode         INT             ,
    A               VARCHAR(30)     ,
    C       INT
    CONSTRAINT [PK__SUB] PRIMARY KEY CLUSTERED
    (
        ProjectCode ASC
    )
) ON [PRIMARY]


CREATE TABLE #TEXT_GENERATOR_1      (A      VARCHAR(30))
CREATE TABLE #TEXT_GENERATOR_2      (B      VARCHAR(20))
CREATE TABLE #INT_GENERATOR_1       (C      INT)
CREATE TABLE #INT_GENERATOR_2       (D      INT)
CREATE TABLE #DATE_GENERATOR_1      (ID     INT IDENTITY(1,1), E    DATE)

INSERT INTO #TEXT_GENERATOR_1 VALUES ('Apple'),('Pear'),('Grapes'),('Mango'),('Yuja '),('Watermelon'),('Orange'),('Lime'),('Peach'),('Blueberry'),('Kiwi'),('Tomato')
INSERT INTO #TEXT_GENERATOR_2 VALUES ('Bear '),('Camel'),('Cow'),('Deer'),('Elephant '),('Goat')
INSERT INTO #INT_GENERATOR_1 VALUES (1),(2),(3),(4)
INSERT INTO #INT_GENERATOR_2 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20)

DECLARE     @START_NUM  INT = 1
        ,   @END_NUM    INT

DECLARE @S_DATE smalldatetime,@E_DATE smalldatetime
SET @S_DATE = CONVERT(smalldatetime, '2020-01-01')
SET @E_DATE = DATEADD(DAY, -1,DATEADD(YEAR, 4,DATENAME(YEAR,@S_DATE) + DATENAME(MONTH,@S_DATE)+'01'))

INSERT INTO #DATE_GENERATOR_1
SELECT CONVERT(CHAR(10), DATEADD(d, NUMBER, @S_DATE),120) AS DT
FROM MASTER..SPT_VALUES WITH(NOLOCK)
WHERE TYPE = 'P' AND CONVERT(CHAR(10), DATEADD(D, NUMBER, @S_DATE), 120) <= @E_DATE

SET @END_NUM = @@ROWCOUNT

WHILE @START_NUM < @END_NUM
BEGIN

    INSERT INTO MAIN
    SELECT      A
    ,           B
    ,           C
    ,           D
    ,           E
    FROM        #TEXT_GENERATOR_1   A
    CROSS JOIN  #TEXT_GENERATOR_2   B
    CROSS JOIN  #INT_GENERATOR_1    C
    CROSS JOIN  #INT_GENERATOR_2    D
    CROSS JOIN  #DATE_GENERATOR_1   E
    WHERE       E.ID = @START_NUM

    SET @START_NUM += 1

END

INSERT INTO SUB
SELECT      ROW_NUMBER() OVER (ORDER BY (SELECT 1))
,           A
,           C
FROM        #TEXT_GENERATOR_1
CROSS JOIN  #INT_GENERATOR_1

CREATE NONCLUSTERED INDEX NIX__MAIN__A__B__E__C ON MAIN (A, B, E, C)
CREATE NONCLUSTERED INDEX NIX__MAIN__B__A__E__C ON MAIN (B, A, E, C)

两个对比查询 #

普通INNER JOIN查询 #

DECLARE @E DATE = '2023-03-10'
DECLARE @CC_E DATE = DATEADD(D, -1, @E)

DECLARE @TAB TABLE (PROJECT_CD INT, C INT)
DECLARE @CODE TABLE (PROJECT_CD INT, A VARCHAR(30), C INT)

INSERT INTO @CODE
SELECT  ProjectCode
,       A
,       C
FROM    SUB
WHERE   ProjectCode > 4

;WITH TBL_1 AS (
    SELECT  A
    ,       B
    ,       C
    ,       D
    ,       E
    FROM    MAIN WITH(NOLOCK)
    WHERE   E BETWEEN DATEADD(DAY, -13, @CC_E) AND @CC_E
    AND     A IN (SELECT A FROM @CODE)
    AND     B IN ('Bear','Camel')
)

INSERT INTO     @TAB
SELECT          PROJECTCODE
,               A.C
FROM            TBL_1   A
INNER JOIN      SUB     B
ON              A.A = B.A
GROUP BY        PROJECTCODE, A.C
OPTION(MAXDOP 1)

指定INNER HASH JOIN查询 #

DECLARE @E DATE = '2023-03-10'
DECLARE @CC_E DATE = DATEADD(D, -1, @E)

DECLARE @TAB TABLE (PROJECT_CD INT, C INT)
DECLARE @CODE TABLE (PROJECT_CD INT, A VARCHAR(30), C INT)

INSERT INTO @CODE
SELECT  ProjectCode
,       A
,       C
FROM    SUB
WHERE   ProjectCode > 4

;WITH TBL_1 AS (
    SELECT  A
    ,       B
    ,       C
    ,       D
    ,       E
    FROM    MAIN WITH(NOLOCK)
    WHERE   E BETWEEN DATEADD(DAY, -13, @CC_E) AND @CC_E
    AND     A IN (SELECT A FROM @CODE)
    AND     B IN ('Bear','Camel')
)

INSERT INTO         @TAB
SELECT              PROJECTCODE
,                   A.C
FROM                TBL_1   A
INNER HASH JOIN     SUB     B
ON                  A.A = B.A
GROUP BY            PROJECTCODE, A.C
OPTION(MAXDOP 1)

执行计划差异原因 #

这两种连接方式的核心差异源于SQL Server对不同连接算法的执行逻辑设计：

• 普通INNER JOIN（优化器选择嵌套循环连接）
  未指定连接类型时，优化器根据数据量选择了嵌套循环连接。此时SUB表作为驱动表（仅返回ProjectCode>4的少量行），每一行的A值会被直接下推到MAIN表的索引Seek条件中——结合原查询的B IN ('Bear','Camel')和日期范围，用A=当前SUB行的A值、B、E作为精准的Seek Predicates，直接定位MAIN表的匹配行，减少无效数据读取。

• 强制INNER HASH JOIN
  哈希连接的执行分为两个固定步骤：

  1. 先构建小表（SUB）的哈希表，将所有符合条件的A值存入哈希结构；
  2. 再扫描大表（MAIN）中符合基础过滤条件（B范围、日期范围、A IN (SELECT A FROM @CODE)）的数据集，最后将每一行的A值与哈希表做匹配。
     这种逻辑下，优化器无法把哈希连接的A.A=B.A条件下推到MAIN表的Seek阶段——因为哈希匹配是在MAIN表数据读取完成后才进行的，Seek阶段只能用到提前确定的静态过滤条件，无法利用哈希表中的动态A值做精准Seek。

另外要注意：A IN (SELECT A FROM @CODE)已经对MAIN表的A值做了范围过滤，所以哈希连接时即使没有把A=SUB.A加入Seek，也不会读取超出范围的数据，只是Seek的条件粒度更粗而已。

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