为什么这个（不相关的）子查询会导致这样的问题？

Question

我有一个大型查询，其中一个简单的子查询优化将其从 8 分钟缩短到 20 秒。我不确定我是否理解为什么优化会产生如此巨大的效果。

本质上，这是问题部分：

SELECT  (bunch of stuff)
FROM
  a LEFT OUTER JOIN b ON a.ID = b.a
  LEFT OUTER JOIN c ON b.ID = c.b
  ...
  ...
      INNER JOIN veryLargeTable 
      ON a.ID = veryLargeTable.a 
         AND veryLargeTable.PetID = 
             (SELECT id from Pets WHERE Pets.Name = 'Something')    /* BAD! */
  ...
  ...

总共有 16 个连接表。如果我将 veryLargeTable 连接的第二个谓词替换为包含 petID 的预填充变量（而不是使用子查询），则整个查询速度将显着提高：

AND veryLargeTable.PetID = @petID   /* Awesome! */

显然，每行都会执行 (SELECT id from Pets WHERE Name = 'Something') 。有两件事我不完全理解：

1. 据我所知，这是一个不相关的子查询。 Pets 表根本不是外部查询的一部分。非相关子查询不是独立评估（并因此优化）的吗？为什么这里的情况不是这样？

2. 执行计划截然不同。在失败的情况下（上面），整个子树处理估计 950k 行。在获胜的情况下（使用变量而不是子查询），估计只有大约 125k 行。这是怎么回事？如果存在该子查询，为什么会涉及这么多行？ Pets.Name 列肯定具有唯一的数据（但据我所知，没有唯一的约束）。

请注意，正如我所期望的那样，将谓词移至 WHERE 子句不会影响任何一种情况下的查询，因为它是 INNER JOIN。

见解赞赏！

Answer 1

根据我的经验，查询越复杂，SQL 优化器创建灵活计划的能力就越差。这里你有 16 个连接，一些或大部分是外部连接，你至少有一个子查询......扔进足够的索引，基数，视图，外部应用，谁知道还有什么，没有人，甚至微软都没有工程师*，可以找出统一、定期生成最佳计划的例程。

你所描述的，我已经经历过很多次了——在混乱的查询中改变一个简单的事情，一切都会快一个数量级（或者，咬牙切齿，慢一些）。我没有办法确定什么时候复杂就太复杂了，这更多的是一种感觉。我的一般经验法则是，如果它看起来太长或太复杂，请尽可能简化 - 例如您预先选择的单个嵌套值，或者打破查询的一部分总是运行快速使用小结果集，首先运行它并将结果存储在临时表中。

（*请注意，这是轻微的讽刺）

Answer 2

作为替代方案，我认为您可以使用以下方法消除子查询：

...
INNER JOIN veryLargeTable vLT
    ON a.ID = vLT.a 
INNER JOIN Pets p
    ON vLT.PetID = p.id
        and p.Name = 'Something'
...

Answer 3

我个人认为，如果 Pets.Name 上没有索引，结果并不奇怪。如果您在 Pets.Name 上创建唯一索引，您可能会看到更好的结果。从服务器的角度来看，如果没有索引，子查询可能会返回多行或 NULL。也许优化器可以做得更好；它经常需要帮助。

Answer 4

原因是正如您所指出的，根据我的经验，即使是最简单的非相关子查询通常也会被 SQL Server 的查询优化器重新计算。

例如，您可以查看以下查询的执行计划，发现不相关的子查询被重新计算。

SELECT ID
FROM #table1
WHERE ID in (SELECT ID from #table1)
UNION ALL
SELECT ID
FROM #table1
WHERE ID in (SELECT ID from #table1)

属性上有或没有聚集索引，在本例中为“ID”。正如有人指出的，您可以重写此查询以使用联接而不是子查询。然而，在许多情况下，如果子查询返回聚合标量，

where ID = (select MAX(ID) from #table1)

那么连接重写可能不会那么容易进行。