遇到SQL 子查询性能很差？其实可以这样优化

介绍

SQL 允许您在可能出现表或列名称的几乎任何地方使用子查询。您所要做的就是用括号将查询括起来，例如(SELECT ...)，然后您可以在任意表达式中使用它。这使得 SQL 成为一种强大的语言，但是可能难以阅读。但我不想讨论 SQL 的美或丑。在本教程中，我们来看看如何编写出表现良好的子查询。让我们先从简单开始，稍后再来了解更令人惊讶和复杂的话题。

相关和不相关的子查询

在子查询中，您可以使用外部的表列，例如

SELECT a.col1,
       (SELECT b.col2 FROM b WHERE b.x = a.x)
FROM a;

对“a”中的每一行，子查询会不同。这样的子查询通常称为相关子查询。不相关的子查询是指不引用任何外部内容的子查询。

不相关的子查询很简单。如果 PostgreSQL 优化器没有“拉起它”（将其集成到主查询树中），则执行器将在单独的步骤中计算它。您可以在EXPLAIN的输出中看到InitPlan（初始计划）。不相关的子查询几乎从来都不是性能问题。在本文的其余部分，将会主要讨论相关的子查询。

标量和表格子查询

如果在 SQL 语句中的某个位置编写一个子查询，而该位置本来需要写入单个值，则该子查询是标量子查询。标量子查询的一个示例是上一节中的示例。一个不同的例子是

SELECT a.col1
FROM a
WHERE 1 = (SELECT count(*)
           FROM b
           WHERE b.x = a.x);

如果标量子查询不返回任何结果，则结果值为 NULL。如果查询返回多于一行，您会收到运行时错误：

ERROR:  more than one row returned by a subquery used as an expression

表格子查询出现在可以返回多个值的上下文中：

? FROM列表条目：FROM (SELECT ...) AS alias

? 公共表表达式（CTE）：WITH q AS (SELECT ...) SELECT ...

? IN或NOT IN表达式：WHERE a.x IN (SELECT ...)

? EXISTS或NOT EXISTS表达式：WHERE NOT EXISTS (SELECT ...)

标量子查询通常有性能问题

我的经验法则是：应当尽量避免相关的标量子查询。原因是 PostgreSQL 只能以嵌套循环方式来执行标量子查询。例如，PostgreSQL 会对表 “a” 中的每一行，执行一次前面提到的子查询。如果“a”是一个小表，这可能很好（请记住，这里的建议只是一个经验法则）。但是，如果表 “a” 很大，即使是快速的子查询，也会使查询执行速度慢得令人难受。

重写 SELECT 列表或 WHERE 子句中的标量子查询

如果相关的标量子查询对性能不利，我们如何避免它们？没有单一的、直接的答案，您可能无法重写查询，以避免在所有情况下都出现此类子查询。但通常的解决方案是，将子查询转换为连接。对于我们的第一个查询，它将如下所示：

SELECT a.col1,
       b.col2
FROM a
   LEFT JOIN b ON b.x = a.x;

查询在语义上是等效的，不同之处在于如果“a”中的行与“b”中的多行匹配，则不会收到运行时错误。我们需要一个外部连接，来说明子查询不返回任何结果的情况。

对于我们的第二个示例，重写后的查询将如下所示：

SELECT a.col1
FROM a
   JOIN b ON b.x = a.x
GROUP BY a.pkey, a.col1
HAVING count(*) = 1;

这里，a.pkey是“a”的主键。根据a.col1分组是不够的，因为表 “a” 中的两个不同行可能具有相同的col1值。

像上面这样重写查询的优点是，PostgreSQL 可以选择最佳连接策略，并且不限于嵌套循环。如果表 “a” 只有几行，这可能没有区别，因为无论如何，嵌套循环连接可能是最有效的连接策略。但是，在这种情况下，查询也不会因重写而表现变差。如果 “a” 很大，则使用哈希或合并连接的速度会快得多。

表格子查询和性能

虽然相关的标量子查询通常很糟糕，但表格子查询的情况也没那么简单。让我们分别考虑不同的情况。

FROM 中的 CTE 和子查询

这些情况几乎相同，因为您始终可以将 CTE 重写为FROM中的子查询，除非它是递归的、MATERIALIZED的或数据修改的 CTE。CTE 不会是相关的，因此它们永远不会有问题。但是，FROM子句条目可以在横向连接中关联：

SELECT a.col1,sub.col2
FROM a
CROSS JOIN LATERAL
(SELECT b.col2
FROM b
WHERE b.x = a.x
ORDER BY b.sort
       LIMIT 1) AS sub;

同样，PostgreSQL 将在嵌套循环中执行这样的子查询，这对于大型表 “a” 可能会表现不佳。因此，重写查询以避免相关的子查询，通常是一个好主意：

SELECT DISTINCT ON (a.pkey)
       a.col1, b.col2
FROM a
   JOIN b ON b.x = a.x
ORDER BY a.pkey, b.sort;

如果 “a” 有很多行，则重写后的查询性能会更好，但如果 “a” 很小而 “b” 很大可能会更差，除非在(x, sort)上面有索引。

EXISTS 和 NOT EXISTS 中的子查询

这是一个特例。到目前为止，我一直建议避免相关子查询。但是使用EXISTS和NOT EXISTS，PostgreSQL 优化器能够将子句分别转换为半连接和反连接。这使得 PostgreSQL 可以使用所有连接策略，而不仅仅是嵌套循环。

因此，PostgreSQL 可以高效地处理EXISTS和NOT EXISTS的相关子查询。

IN 和 NOT IN 的棘手情况

您可能会期望这两种情况的行为相似，但事实并非如此。在一个查询中，使用IN的子查询始终可以使用EXISTS重写。例如，下面的语句：

SELECT a.col1
FROM a
WHERE a.foo IN (SELECT b.col2
                 FROM b
                 WHERE a.x = b.x);

等价于

SELECT a.col1
FROM a
WHERE EXISTS (SELECT NULL
              FROM b
              WHERE a.x = b.x
                AND a.foo = b.col2);

PostgreSQL 优化器可以做到这一点，并会像处理EXISTS中的子查询一样，高效地处理IN中的子查询。

然而，NOT IN的情况却大不相同。您可以像上面一样，将NOT IN重写为NOT EXISTS，但这不是 PostgreSQL 可以自动完成的转换，因为重写的语句在语义上不同：如果子查询返回至少一个 NULL 值，则NOT IN永远不会为 TRUE。而NOT EXISTS子句没有表现出这种令人惊讶的行为。

现在人们通常不关心NOT IN的这个特点（事实上，很少有人知道它）。无论如何，大多数人都更喜欢NOT EXISTS的这种行为。但是您必须自己重写 SQL 语句，并且不能指望 PostgreSQL 会自动执行此操作。因此，我的建议是，您永远不要使用带有子查询的NOT IN子句，而始终应改为使用NOT EXISTS 。

使用相关子查询强制嵌套循环连接

到目前为止，我已经告诉您如何重写 SQL 语句，以避免强制优化器使用嵌套循环。然而，有时你会需要完全相反的结果：你希望优化器使用嵌套循环连接，因为你恰好知道这是最好的连接策略。然后，您可以有意识地将常规连接重写为横向交叉连接，以强制嵌套循环。例如，下面的查询

SELECT a.col1, b.col2
FROM a
   JOIN b ON a.x = b.x;

在语义上等价于

SELECT a.col1, sub.col2
FROM a
   CROSS JOIN LATERAL
      (SELECT b.col2
       FROM b
       WHERE a.x = b.x) AS sub;

结论

如果您希望获得良好的子查询性能，通常最好遵循以下准则：

? 尽可能地使用不相关的子查询，只要它们不会让语句难以理解

? 在所有地方避免使用相关子查询，除非在EXISTS、NOT EXISTS和IN子句中

? 总是将NOT IN重写为NOT EXISTS

也不要把这些规则当作铁律。有时，相关子查询实际上可以表现得更好，有时您可以使用相关子查询，来强制优化器使用嵌套循环，只要您确定这是正确的策略。