为什么子查询将行估算值减少到1？

考虑以下人为但简单的查询：我希望此查询的最终行估计等于X_HEAP表中的行数。我在子查询中所做的任何事情都不会影响行估计，因为它无法过滤出任何行。但是，在SQL Server 2016上，由于子查询，我看到行估计减少为1：

为什么会发生这种情况？我该怎么办？

使用正确的语法很容易重现此问题。这是一组可以做到的表定义：

SELECT 
  ID
, CASE
    WHEN ID <> 0 
    THEN (SELECT TOP 1 ID FROM X_OTHER_TABLE) 
    ELSE (SELECT TOP 1 ID FROM X_OTHER_TABLE_2) 
  END AS ID2
FROM X_HEAP;

db fiddle链接。

#1 楼

该基数估计（CE）在以下情况下出现：

联接是具有传递谓词的外部联接
传递联接谓词的选择性估计为正好1.

注意：用于确定选择性的特定计算器并不重要。

详细信息

CE计算外部联接的选择性与以下各项之和：

具有相同谓词的内部联接选择性
具有相同谓词的内部联接选择性

外部联接和内部联接之间的唯一区别是，外部联接还会返回与联接谓词不匹配的行。反连接正是提供了这种差异。内部连接和反连接的基数估计比直接外部连接的基数估计更容易。

连接选择性估计过程非常简单：

首先，图2的选择性SPT评估通过谓词。

可以使用适合情况的任何一种计算器来完成此操作。
谓词是完整的，包括所有否定的IsFalseOrNull组件。 br />

反联接表示将“通过”联接的行。内部联接表示不会“通过”的行。请注意，“通过”是指流过联接而根本不运行内侧的行。要强调的是：所有行都将由联接返回，区别在于在出现之前在联接内侧运行的行与未出现联接的行之间。

显然，应始终将1 - SPT添加到SPT给出的总选择性为1，这意味着所有行都按预期返回。 />当1 - SPT = 1时，内部连接和反向连接的选择性都估计为零，从而导致一行的基数估计（对于整个连接而言）。据我所知，这是无意的，应将其报告为错误。

相关问题

与人们可能会认为，由于单独的CE限制。当SPT表达式使用SPT子句时（通常），就会出现这种情况。例如，问题中的以下修改后的查询未遇到意外的基数估计：

-- This is fine
SELECT 
    CASE
        WHEN XH.ID = 1
        THEN (SELECT TOP (1) XOT.ID FROM dbo.X_OTHER_TABLE AS XOT) 
    END
FROM dbo.X_HEAP AS XH;

引入琐碎的SPT确实使问题浮出水面：

-- This is not fine
SELECT 
    CASE
        WHEN EXISTS (SELECT 1 WHERE XH.ID = 1)
        THEN (SELECT TOP (1) XOT.ID FROM dbo.X_OTHER_TABLE AS XOT) 
    END
FROM dbo.X_HEAP AS XH;

使用CASE在执行计划中引入了半连接（突出显示）：精细。问题在于，CE将关联的探针列视为简单投影，固定选择性为1：

 EXISTS

无论EXISTS子句的内容如何，这都自动满足此CE问题表现出来的条件之一。

有关重要的背景信息，请参阅子查询。在Craig Freedman的EXISTS表达式中。

#2 楼

这绝对似乎是意外行为。的确，基数估计不需要在计划的每个步骤中保持一致，但这是一个相对简单的查询计划，最终基数估计与查询的操作不一致。这样低的基数估计可能会导致在更复杂的计划中对其他表的下游连接类型和访问方法的选择不佳。问题未出现：

SELECT 
  ID
, CASE
    WHEN ID <> 0 
    THEN (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE) 
    ELSE (SELECT -1) 
  END AS ID2
FROM dbo.X_HEAP;

SELECT 
  ID
, CASE
    WHEN ID < 500 
    THEN (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE) 
    WHEN ID >= 500 
    THEN (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE_2) 
  END AS ID2
FROM dbo.X_HEAP;

我们还可以针对出现问题的更多查询：

SELECT 
  ID
, CASE
    WHEN ID < 500 
    THEN (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE) 
    WHEN ID >= 500 
    THEN (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE_2) 
    ELSE (SELECT TOP 1 ID FROM X_OTHER_TABLE) 
  END AS ID2
FROM dbo.X_HEAP;

SELECT 
  ID
, CASE
    WHEN ID = 0 
    THEN (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE) 
    ELSE (SELECT -1) 
  END AS ID2
FROM dbo.X_HEAP;

SELECT 
  ID
, CASE
    WHEN ID = 0 
    THEN (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE) 
    ELSE (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE_2) 
  END AS ID2
FROM dbo.X_HEAP;

似乎有一种模式：如果CASE中存在一个不希望执行的表达式，并且结果表达式是针对表的子查询，则该表达式之后的行估计值将降至1。

如果我针对具有聚集索引的表编写查询，则规则会有所变化。我们可以使用相同的数据：

CREATE TABLE dbo.X_CI (ID INT NOT NULL, PRIMARY KEY (ID))

INSERT INTO dbo.X_CI WITH (TABLOCK)
SELECT * FROM dbo.X_HEAP;

UPDATE STATISTICS X_CI WITH FULLSCAN;

此查询的最终估算值为1000行：

SELECT 
  ID
, CASE
    WHEN ID = 0 
    THEN (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE_2) 
    ELSE (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE) 
  END
FROM dbo.X_CI;

但是此查询的最终估算为1行：

SELECT 
  ID
, CASE
    WHEN ID <> 0 
    THEN (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE) 
    ELSE (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE_2) 
  END
FROM dbo.X_CI;

要进一步深入研究，我们可以使用未记录的跟踪标志2363获取有关查询优化器如何执行选择性的信息。计算。我发现将跟踪标记与未记录的跟踪标记8606配对会很有帮助。TF 2363似乎为简化树和项目归一化后的树提供了选择性计算。启用两个跟踪标志可以清楚地表明哪些计算适用于哪棵树。

让我们对问题中发布的原始查询进行尝试：

SELECT 
  ID
, CASE
    WHEN ID <> 0 
    THEN (SELECT TOP 1 ID FROM X_OTHER_TABLE) 
    ELSE (SELECT TOP 1 ID FROM X_OTHER_TABLE_2) 
  END AS ID2
FROM X_HEAP
OPTION (QUERYTRACEON 3604, QUERYTRACEON 2363, QUERYTRACEON 8606);

/>这是输出部分的一部分，我认为它与一些注释相关：

Plan for computation:

  CSelCalcColumnInInterval -- this is the type of calculator used

      Column: QCOL: [SE_DB].[dbo].[X_HEAP].ID -- this is the column used for the calculation

Pass-through selectivity: 0 -- all rows are expected to have a true value for the case expression

Stats collection generated: 

  CStCollOuterJoin(ID=8, CARD=1000 x_jtLeftOuter) -- the row estimate after the join will still be 1000

      CStCollBaseTable(ID=1, CARD=1000 TBL: X_HEAP)

      CStCollBaseTable(ID=2, CARD=1 TBL: X_OTHER_TABLE)

...

Plan for computation:

  CSelCalcColumnInInterval

      Column: QCOL: [SE_DB].[dbo].[X_HEAP].ID

Pass-through selectivity: 1 -- no rows are expected to have a true value for the case expression

Stats collection generated: 

  CStCollOuterJoin(ID=9, CARD=1 x_jtLeftOuter) -- the row estimate after the join will still be 1

      CStCollOuterJoin(ID=8, CARD=1000 x_jtLeftOuter) -- here is the row estimate after the previous join

          CStCollBaseTable(ID=1, CARD=1000 TBL: X_HEAP)

          CStCollBaseTable(ID=2, CARD=1 TBL: X_OTHER_TABLE)

      CStCollBaseTable(ID=3, CARD=1 TBL: X_OTHER_TABLE_2)

现在让我们尝试使用类似的查询，其中没有问题。我要使用这个：

SELECT 
  ID
, CASE
    WHEN ID <> 0 
    THEN (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE) 
    ELSE (SELECT -1) 
  END AS ID2
FROM dbo.X_HEAP
OPTION (QUERYTRACEON 3604, QUERYTRACEON 2363, QUERYTRACEON 8606);

在最末端调试输出：

Plan for computation:

  CSelCalcColumnInInterval

      Column: QCOL: [SE_DB].[dbo].[X_HEAP].ID

Pass-through selectivity: 1

Stats collection generated: 

  CStCollOuterJoin(ID=9, CARD=1000 x_jtLeftOuter)

      CStCollOuterJoin(ID=8, CARD=1000 x_jtLeftOuter)

          CStCollBaseTable(ID=1, CARD=1000 TBL: dbo.X_HEAP)

          CStCollBaseTable(ID=2, CARD=1 TBL: dbo.X_OTHER_TABLE)

      CStCollConstTable(ID=4, CARD=1) -- this is different than before because we select a constant instead of from a table

让我们尝试另一个存在错误行估计的查询：

SELECT 
  ID
, CASE
    WHEN ID < 500 
    THEN (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE) 
    WHEN ID >= 500 
    THEN (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE_2) 
    ELSE (SELECT TOP 1 ID FROM X_OTHER_TABLE) 
  END AS ID2
FROM dbo.X_HEAP
OPTION (QUERYTRACEON 3604, QUERYTRACEON 2363, QUERYTRACEON 8606);

最后，在传递选择性= 1之后，基数估计值再次下降到1行。在选择性0.501和0.499之后，基数估计值得以保留。
>
让我们再次切换到另一个没有问题的类似查询。我将使用以下代码：

Plan for computation:

 CSelCalcColumnInInterval

      Column: QCOL: [SE_DB].[dbo].[X_HEAP].ID

Pass-through selectivity: 0.501

...

Plan for computation:

  CSelCalcColumnInInterval

      Column: QCOL: [SE_DB].[dbo].[X_HEAP].ID

Pass-through selectivity: 0.499

...

Plan for computation:

  CSelCalcColumnInInterval

      Column: QCOL: [SE_DB].[dbo].[X_HEAP].ID

Pass-through selectivity: 1

Stats collection generated: 

  CStCollOuterJoin(ID=12, CARD=1 x_jtLeftOuter) -- this is associated with the ELSE expression

      CStCollOuterJoin(ID=11, CARD=1000 x_jtLeftOuter)

          CStCollOuterJoin(ID=10, CARD=1000 x_jtLeftOuter)

              CStCollBaseTable(ID=1, CARD=1000 TBL: dbo.X_HEAP)

              CStCollBaseTable(ID=2, CARD=1 TBL: dbo.X_OTHER_TABLE)

          CStCollBaseTable(ID=3, CARD=1 TBL: dbo.X_OTHER_TABLE_2)

      CStCollBaseTable(ID=4, CARD=1 TBL: X_OTHER_TABLE)

在调试输出中，永远不会有通过选择性为1的步骤。基数估计值保持为1000行。

SELECT 
  ID
, CASE
    WHEN ID < 500 
    THEN (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE) 
    WHEN ID >= 500 
    THEN (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE_2) 
  END AS ID2
FROM dbo.X_HEAP
OPTION (QUERYTRACEON 3604, QUERYTRACEON 2363, QUERYTRACEON 8606);

如果查询涉及具有聚集索引的表，该查询怎么办？请考虑以下有关行估计问题的查询：

Plan for computation:

  CSelCalcColumnInInterval

      Column: QCOL: [SE_DB].[dbo].[X_HEAP].ID

Pass-through selectivity: 0.499

Stats collection generated: 

  CStCollOuterJoin(ID=9, CARD=1000 x_jtLeftOuter)

      CStCollOuterJoin(ID=8, CARD=1000 x_jtLeftOuter)

          CStCollBaseTable(ID=1, CARD=1000 TBL: dbo.X_HEAP)

          CStCollBaseTable(ID=2, CARD=1 TBL: dbo.X_OTHER_TABLE)

      CStCollBaseTable(ID=3, CARD=1 TBL: dbo.X_OTHER_TABLE_2)

End selectivity computation

调试输出的结尾类似于我们已经看到的内容：

SELECT 
  ID
, CASE
    WHEN ID <> 0 
    THEN (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE) 
    ELSE (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE_2) 
  END
FROM dbo.X_CI
OPTION (QUERYTRACEON 3604, QUERYTRACEON 2363, QUERYTRACEON 8606);

但是，针对没有问题的CI的查询具有不同的输出。使用以下查询：

Plan for computation:

  CSelCalcColumnInInterval

      Column: QCOL: [SE_DB].[dbo].[X_CI].ID

Pass-through selectivity: 1

Stats collection generated: 

  CStCollOuterJoin(ID=9, CARD=1 x_jtLeftOuter)

      CStCollOuterJoin(ID=8, CARD=1000 x_jtLeftOuter)

          CStCollBaseTable(ID=1, CARD=1000 TBL: dbo.X_CI)

          CStCollBaseTable(ID=2, CARD=1 TBL: dbo.X_OTHER_TABLE)

      CStCollBaseTable(ID=3, CARD=1 TBL: dbo.X_OTHER_TABLE_2)

使用不同计算器的结果。 CSelCalcColumnInInterval不再出现：

SELECT 
  ID
, CASE
    WHEN ID = 0 
    THEN (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE_2) 
    ELSE (SELECT TOP 1 ID FROM dbo.X_OTHER_TABLE) 
  END
FROM dbo.X_CI
OPTION (QUERYTRACEON 3604, QUERYTRACEON 2363, QUERYTRACEON 8606);

总之，在以下情况下，子查询后，我们似乎得到了错误的行估计：

使用了CSelCalcColumnInInterval选择性计算器。我不知道什么时候使用它，但是当基表是堆时，它似乎更经常出现。
传递选择性=1。换句话说，CASE表达式之一有望对于所有行，评估为false。对于所有行，第一个CASE表达式的求值都为true都没关系。
CStCollBaseTable有一个外部联接。换句话说，CASE结果表达式是针对表的子查询。常量值将不起作用。

也许在这些条件下，查询优化器无意中将传递选择性应用于外部表的行估计，而不是应用于嵌套循环内部的工作。这样可以将行估计减少到1。

我能够找到两个解决方法。使用APPLY而不是子查询时，我无法重现该问题。跟踪标志2363的输出与APPLY非常不同。这是重写问题中原始查询的一种方法：

Plan for computation:

  CSelCalcFixedFilter (0.559)

Pass-through selectivity: 0.559

Stats collection generated: 

  CStCollOuterJoin(ID=8, CARD=1000 x_jtLeftOuter)

      CStCollBaseTable(ID=1, CARD=1000 TBL: dbo.X_CI)

      CStCollBaseTable(ID=2, CARD=1 TBL: dbo.X_OTHER_TABLE_2)

...

Plan for computation:

  CSelCalcUniqueKeyFilter

Pass-through selectivity: 0.001

Stats collection generated: 

  CStCollOuterJoin(ID=9, CARD=1000 x_jtLeftOuter)

      CStCollOuterJoin(ID=8, CARD=1000 x_jtLeftOuter)

          CStCollBaseTable(ID=1, CARD=1000 TBL: dbo.X_CI)

          CStCollBaseTable(ID=2, CARD=1 TBL: dbo.X_OTHER_TABLE_2)

      CStCollBaseTable(ID=3, CARD=1 TBL: dbo.X_OTHER_TABLE)

传统的CE似乎也可以避免此问题。 br />

SELECT 
  h.ID
, a.ID2
FROM X_HEAP h
OUTER APPLY
(
SELECT CASE
    WHEN ID <> 0 
    THEN (SELECT TOP 1 ID FROM X_OTHER_TABLE) 
    ELSE (SELECT TOP 1 ID FROM X_OTHER_TABLE_2) 
  END
) a(ID2);

为此问题提交了一个连接项（其中Paul White在其回答中提供了一些细节）。

编程黑洞网

为什么子查询将行估算值减少到1？

#1 楼

#2 楼