主页 / dba / 问题 / 176279
Accepted
Thomas Franz
Asked: 2017-06-15 07:15:41 +0800 CST

HASH JOIN 的限制

  • 772

我有一个非常复杂的查询（谁没有:-)）。在这个查询中是一个像这样的表的连接（当然原始表有更多的列）：

CREATE TABLE dbo.tbl_detail (id bigint PRIMARY key, 
                   descr varchar(300), 
                   txt varchar(MAX),
                   descr_p varchar(300)
                  );

查询中有两个连接到此表：

  • LEFT JOIN dbo.tbl_detail td1 ON td1.id = main.detail_id
  • LEFT JOIN dbo.tbl_detail td2 ON td2.id = main.second_id
  • 查询返回约 20 万行。
  • td1 输出所有列
  • td2 输出除 [txt] ( varchar(max))之外的所有列

问题：

  • td2 使用 HASH JOIN
  • 但 td1 使用 NESTED LOOKUP（超过 200k 行；没有统计问题，因为估计是正确的）
  • 当我从 td1 输出中删除 [txt] 列时，它也使用 HASH JOIN
  • 当我将列更改为 VARCHAR(5000)（或更小）时，它使用 HASH JOIN
  • 当我将列更改为 VARCHAR(8000) 时，它再次使用 NESTED LOOKUP
  • OPTION (HASH JOIN)或者LEFT HASH JOIN会工作，但会产生一个极慢的查询计划

问题： 为什么不总是使用HASH JOIN？是否有长度限制（每列或所有输出列的总和）？

PS：Microsoft SQL Server 2014 (SP2) 开发者版

sql-server sql-server-2014

1 个回答

  1. Best Answer

    重要的是要记住，查询优化器不会单独选择每个连接，也不会独立于查询中的其他所有连接。联接具有不同的属性，这意味着不同的联接类型可能更好或更差，具体取决于计划中的其他联接或操作。我可以生成显示与您在此处看到的行为类似的行为的测试数据，但再现取决于服务器的可用内存。

    将 10k 行分别放入两个表中：

    CREATE TABLE dbo.tbl_detail (
    id bigint PRIMARY key, 
    descr varchar(300), 
    descr_p varchar(300),
    txt_max varchar(MAX),
    txt_5k varchar(5000)
);

INSERT INTO dbo.tbl_detail WITH (TABLOCK)
SELECT TOP (10000)
  ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, REPLICATE('DESCR', 60)
, REPLICATE('descr_p', 40)
, NULL
, NULL
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

CREATE TABLE dbo.OUTER_TABLE (
    id bigint primary key,
    id2 bigint
);

INSERT INTO dbo.OUTER_TABLE WITH (TABLOCK)
SELECT TOP (10000)
  ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

    这是要测试的查询：

    SELECT o.*, d.id, d.descr, d.descr_p
FROM OUTER_TABLE o
LEFT OUTER JOIN tbl_detail d ON o.ID2 = d.ID
ORDER BY o.ID;

    如果我添加d.txt_5k到SELECT列表中，我自然会得到一个散列连接：

    自然散列

    但是，如果我添加d.txt_max到SELECT列表中，我自然会得到一个循环连接：

    自然循环

    这里的关键是循环连接保留了外表的顺序。这意味着嵌套循环连接可以避免对某些查询进行显式排序。使用散列连接的查询可能需要进行排序。排序的成本取决于（除其他外）数据的估计大小。查询优化器根据估计的行数和数据类型来估计数据的大小。对于该VARCHAR(5000)列，我得到的估计大小为 27 MB，对于该VARCHAR(MAX)列，我得到的估计大小为 42 MB。在我的机器上，预计 42 MB 排序会溢出到磁盘，因此哈希连接计划比列的循环计划昂贵得多VARCHAR(MAX)。

    通过使用提示强制执行不同的计划，我们可以更清楚地看到这一点，以便我们可以比较它们。这是VARCHAR(5000)查询的比较：

    5k比较

    以及查询的比较VARCHAR(MAX)：

    最大比较

    当然，这可能不是您看到特定查询行为的原因。我只是想提供一个示例，说明为什么更改数据类型会更改连接类型。我将假设循环连接引起了一个问题，并且您确实需要它来进行散列连接。该OPTION (HASH JOIN)提示可能不是一个好的解决方法，因为它会强制每个连接成为查询中的散列连接。该LEFT HASH JOIN提示可能不是一个好的解决方法，因为它暗示了一个FORCE ORDER提示，这意味着查询优化器将无法更改连接顺序。或许您可以将查询中有问题的部分移至临时表，并将所需的任何提示应用于较小的查询。没有更多信息真的很难说太多。

    您也可以尝试FORCESCAN提示以鼓励散列连接，尽管我不愿意推荐它。一定要仔细测试：

    SELECT o.*, d.id, d.descr, d.descr_p, d.txt_max
FROM OUTER_TABLE o
LEFT OUTER JOIN tbl_detail d WITH (FORCESCAN) ON o.ID2 = d.ID 
ORDER BY o.ID
OPTION (MAXDOP 1);
    • 3

相关问题