sql-server – 为什么子查询将行估计值减少到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;

我希望此查询的最终行估计值等于X_HEAP表中的行数.无论我在子查询中做什么都不应该对行估计无关紧要,因为它不能过滤掉任何行.但是,在SQL Server 2016上,由于子查询,我看到行估计值减少为1：

为什么会这样？我该怎么办？

使用正确的语法重现此问题非常容易.以下是一组表定义：

CREATE TABLE dbo.X_HEAP (ID INT NOT NULL)
CREATE TABLE dbo.X_OTHER_TABLE (ID INT NOT NULL);
CREATE TABLE dbo.X_OTHER_TABLE_2 (ID INT NOT NULL);

INSERT INTO dbo.X_HEAP WITH (TABLOCK)
SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values;

CREATE STATISTICS X_HEAP__ID ON X_HEAP (ID) WITH FULLSCAN;

db fiddle link.

解决方法

>联接是一个带有传递谓词的外连接
>传递谓词的选择性估计恰好为1.

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

细节

CE计算外连接的选择性为：

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

外连接和内连接之间的唯一区别是外连接也返回在连接谓词上不匹配的行.反连接提供了这种差异.内连接和反连接的基数估计比直接外连接更容易.

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

>首先,评估传递谓词的选择性SPT.

>这是使用适合于具体情况的计算器完成的.
>谓词是整个事物,包括任何否定IsFalSEOrNull组件.

>内连接选择性：= 1 – SPT
>反连接选择性：= SPT

反连接表示将“通过”连接的行.内部联接表示不会“通过”的行.请注意,’pass through’表示流经连接的行,而根本不运行内侧.要强调：连接将返回所有行,区别在于在出现之前运行连接内侧的行与不连接之前的行之间.

显然,向SPT添加1-SPT应始终给出总选择性为1,这意味着连接将返回所有行,如预期的那样.

实际上,上述计算的工作方式与SPT的所有值(除1之外)完全相同.

当SPT = 1时,内部连接和反连接选择性都被估计为零,从而导致一行的基数估计(对于整个连接).据我所知,这是无意的,应该报告为bug.

一个相关的问题

由于CE限制,这个错误比人们想象的更容易出现.当CASE表达式使用EXISTS子句时(这是常见的),就会出现这种情况.例如,来自问题的以下修改查询未遇到意外的基数估计：

-- 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;

引入一个简单的EXISTS确实会导致问题浮出水面：

-- 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;

使用EXISTS为执行计划引入了半连接(突出显示)：

半连接的估计很好.问题是CE将关联的探测列视为简单投影,固定选择性为1：

Semijoin with probe column treated as a Project.

Selectivity of probe column = 1

无论EXISTS条款的内容如何,??这都会自动满足此CE问题所需的条件之一.

有关重要背景信息,请参阅Craig Freedman的Subqueries in CASE Expressions.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!