狠狠色丁香婷婷综合尤物/久久精品综合一区二区三区/中国有色金属学报/国产日韩欧美在线观看 - 国产一区二区三区四区五区tv

我們經常會在 SQL 中使用到子查詢，正常情況下，PostgreSQL 的優化器可以選擇最佳的執行策略，但是在有些時候性能表現不一定很理想。

介紹

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

也不要把這些規則當作鐵律。有時，相關子查詢實際上可以表現得更好，有時您可以使用相關子查詢，來強制優化器使用嵌套循環，只要您確定這是正確的策略。