主页 / dba / 问题 / 47127
Accepted
beeks
Asked: 2013-07-27 20:14:36 +0800 CST

Anti-Semi Join 错误的解决方法

  • 772

我构建了以下 SQL Server 查询，但它遇到了SQL Server 2005 中的反半连接缺陷，这会导致基数估计不准确（1 - 呃！）并且永远运行。由于它是一个长期生产的 SQL Server，我不能轻易建议升级版本，因此我不能在这个特定查询上强制使用 traceflag 4199 提示。

我很难重构WHERE AND NOT IN (SELECT). 有人可以帮忙吗？我已经确保尝试使用基于集群密钥对的最佳连接。

SELECT TOP 5000 d.doc2_id
    ,d.direction_cd
    ,a.address_type_cd
    ,d.external_identification
    ,s.hash_value
    ,d.publishdate
    ,d.sender_address_id AS [D2 Sender_Address_id]
    ,a.address_id AS [A Address_ID]
    ,d.message_size
    ,d.subject
    ,emi.employee_id
FROM assentor.emcsdbuser.doc2 d(NOLOCK)
INNER JOIN assentor.emcsdbuser.employee_msg_index emi(NOLOCK)
    ON d.processdate = emi.processdate
    AND d.doc2_id = emi.doc2_id
INNER LOOP JOIN assentor.emcsdbuser.doc2_address a(NOLOCK)
    ON emi.doc2_id = a.doc2_id
    AND emi.address_type_cd = a.address_type_cd
    AND emi.address_id = a.address_id
INNER JOIN sis.dbo.sis s(NOLOCK) ON d.external_identification = s.external_identification
WHERE d.publishdate > '2008-01-01'
    **AND d.doc2_id NOT IN (
        SELECT doc2_id
        FROM assentor.emcsdbuser.doc2_address d2a(NOLOCK)
        WHERE d.doc2_id = d2a.doc2_id
            AND d2a.address_type_cd = 'FRM'
        )**
OPTION (FAST 10)

请注意，该Employee_MSG_Index表是 500m 行，doc2是 1.5b 行，SIS是 ~500m 行。

任何帮助，将不胜感激！

sql-server sql-server-2005

2 个回答

  1. Best Answer

    由于它是一个长期生产的 SQL Server，我不能轻易建议升级版本

    反半连接基数估计错误可在2005 至 2012 年（含）的所有 SQL Server 版本上重现。所有这些都需要跟踪标志 4199 才能启用修复，因此如果不激活 4199，升级将无法解决您的问题（当然，从 2005 年升级还有许多其他充分的理由）。

    ...因此，我无法在此特定查询上强制使用 traceflag 4199 提示。

    如果只是一个特定的查询受到影响，您可以使用OPTION (QUERYTRACEON 4199)为该查询启用跟踪标志。此查询提示已记录并支持与 4199 一起使用，并且从 SQL Server 2005 Service Pack 2 开始适用。

    此提示有效地围绕查询运行，因此需要DBCC TRACEON (4199)系统管理员权限。如果这是一个问题，请使用计划指南添加提示。DBCC TRACEOFF (4199)

    您还应该查看使用 4199 enabled instance-wide测试您的整个系统。计划回归是可能的，但总体而言，您可能会发现此标志启用的各种优化器修复非常值得。所有未来影响计划的查询处理器修复都需要激活此标志。

    说了这么多...

    正如ypercube 的回答中提到的，该错误需要两个或更多连接列才能显示（在许多细节中）。您的子句中的冗余NOT IN导致优化器看到两个列比较（尽管逻辑上只有一个），从而暴露了错误。

    删除这种冗余将“解决”这个特定查询的问题，尽管其他确实有多个连接谓词的查询仍然容易受到攻击。

    例子

    为了说明，这是一个基于问题中链接的 CSS 博客文章的示例（但带有完整的脚本！）：

    CREATE TABLE dbo.tst_TAB1
(
    c1 integer NOT NULL, 
    c2 integer NOT NULL, 
    c3 integer NOT NULL
);

CREATE TABLE dbo.tst_TAB2
(
    c1 integer NOT NULL, 
    c2 integer NOT NULL, 
    c3 integer NOT NULL
);

CREATE INDEX i ON dbo.tst_TAB1 (c1, c2);
CREATE INDEX i ON dbo.tst_TAB2 (c1, c2);

    样本数据：

    INSERT dbo.tst_TAB1
    (c1, c2, c3)
SELECT 
    number, number, number
FROM master.dbo.spt_values
WHERE 
    [type] = N'P' 
    AND number BETWEEN 1 AND 2047;

INSERT dbo.tst_TAB2 (c1, c2, c3)
VALUES (1, 1, 1);

    NOT IN使用冗余谓词测试查询：

    SELECT
    T1.c1
FROM tst_TAB1 AS t1 
WHERE
    t1.c1 NOT IN 
    (
        SELECT 
            t2.c1 
        FROM tst_TAB2 AS t2
        -- This is redundant!
        WHERE
            t2.c1 = t1.c1
    );

    估计的执行计划显示在反半连接后估计有 1 行：

    估计 1 行

    旁注：事实上，这是另一个（罕见）错误的示例。编写WHERE子句 ast1.c1 = t2.c1而不是t2.c1 = t1.c1允许优化器看到这两个连接谓词实际上是相同的，并且错误不会出现。

    相同的查询OPTION (QUERYTRACEON 4199)：

    SELECT
    T1.c1
FROM tst_TAB1 AS t1 
WHERE
    t1.c1 NOT IN 
    (
        SELECT 
            t2.c1 
        FROM tst_TAB2 AS t2
        WHERE
            t2.c1 = t1.c1
    )
OPTION (QUERYTRACEON 4199);

    估计的执行计划现在显示估计为2046 行，这完全正确：

    使用 TF 4199 进行计划

    我们还可以删除多余的谓词：

    SELECT
    T1.c1
FROM tst_TAB1 AS t1 
WHERE
    t1.c1 NOT IN 
    (
        SELECT 
            t2.c1 
        FROM tst_TAB2 AS t2
    );

    执行计划碰巧使用了额外的不相关优化（Stream Aggregate），但重要的一点是加入后估计是正确的，无需启用 4199：

    没有多余的谓词

    多个反半连接列

    NOT IN可以使用语法在多个列上表达反半联接。这些情况将需要 4199。例如，下一个查询连接c1和c2：

    SELECT
    T1.c1
FROM tst_TAB1 AS t1 
WHERE
    t1.c1 NOT IN 
    (
        SELECT 
            t2.c1 
        FROM tst_TAB2 AS t2
        WHERE
            t2.c2 = t1.c2
    );

    执行计划显示错误的 1 行估计：

    多个连接列

    使用 4199，问题得到解决：

    SELECT
    T1.c1
FROM tst_TAB1 AS t1 
WHERE
    t1.c1 NOT IN 
    (
        SELECT 
            t2.c1 
        FROM tst_TAB2 AS t2
        WHERE
            t2.c2 = t1.c2
    )
OPTION (QUERYTRACEON 4199);

    具有 4199 的多个连接列

    其他语法

    最好避免以这种方式使用NOT IN，尤其是出于联机丛书中提到的原因：

    提单警告

    这个问题已经写过很多次了NOT IN。有许多替代语法可用，其中是我个人的偏好。请注意，更改语法不会避免基数估计错误：NULLsNOT EXISTS

    SELECT
    T1.c1
FROM dbo.tst_TAB1 AS t1 
WHERE 
    NOT EXISTS 
    ( 
        SELECT 1 
        FROM dbo.tst_TAB2 AS t2 
        WHERE 
            t2.c1 = t1.c1 
            AND t2.c2 = t1.c2
    );

    两列反半连接产生 1 行估计，需要 4199 来修复它。执行计划与之前看到的完全一样，所以我不再赘述。NOT EXISTS语法确实避免NULLs了.NOT IN

    其他意见

    我同意 ypercube 的其他意见。

    • 在查询中的每个表上添加NOLOCK提示是一种不好的代码气味。如果查询确实可以容忍READ UNCOMMITTED事务语义，请显式设置隔离级别。

    • TOP没有ORDER BY是糟糕代码的另一个标志。TOP需要一个ORDER BY子句来定义什么TOP意思。永远不要依赖观察到的行为，使用明确的顶级ORDER BY来获得保证。

    • INNER LOOP JOIN和一般的连接提示，暗示一个FORCE ORDER查询提示。这严重限制了优化器的自由度，通常会被误解和误用。切勿使用您不完全理解的提示。

    • 14

  2. 您提供的链接表明该错误仅影响多列的联接：

    请注意，仅当连接中涉及多个连接列时，您才会遇到此问题，如上例所示。

    而且我不明白您为什么NOT IN以这种方式编写（d.doc2_id = d2a.doc2_id在子查询中添加条件。）它是多余的（除非连接的列可以为空-是吗？），因此您可以编写NOT IN为：

    AND d.doc2_id NOT IN (
    SELECT d2a.doc2_id
    FROM assentor.emcsdbuser.doc2_address d2a
    WHERE d2a.address_type_cd = 'FRM'
    )

    或与NOT EXISTS：

    AND NOT EXISTS (
    SELECT 1
    FROM assentor.emcsdbuser.doc2_address d2a
    WHERE d.doc2_id = d2a.doc2_id
        AND d2a.address_type_cd = 'FRM'
    )

    尝试两者并检查基数估计问题是否已解决。

    其他注意事项：

    • 你有索引address_type_cd吗？
    • 为什么要多次使用NOLOCK？
    • TOPwithoutORDER BY每次执行可能会给您不同的结果。
    • 6

相关问题