我們經(jīng)常會在 SQL 中使用到子查詢，正常情況下，PostgreSQL 的優(yōu)化器可以選擇最佳的執(zhí)行策略，但是在有些時候性能表現(xiàn)不一定很理想。
介紹
SQL 允許您在可能出現(xiàn)表或列名稱的幾乎任何地方使用子查詢。您所要做的就是用括號將查詢括起來，例如(SELECT ...)，然后您可以在任意表達式中使用它。這使得 SQL 成為一種強大的語言，但是可能難以閱讀。但我不想討論 SQL 的美或丑。在本教程中，我們來看看如何編寫出表現(xiàn)良好的子查詢。讓我們先從簡單開始，稍后再來了解更令人驚訝和復雜的話題。
相關和不相關的子查詢
在子查詢中，您可以使用外部的表列，例如
SELECT a.col1,
(SELECT b.col2 FROM b WHERE b.x = a.x)
FROM a;
對“a”中的每一行，子查詢會不同。這樣的子查詢通常稱為相關子查詢。不相關的子查詢是指不引用任何外部內(nèi)容的子查詢。
不相關的子查詢很簡單。如果 PostgreSQL 優(yōu)化器沒有“拉起它”（將其集成到主查詢樹中），則執(zhí)行器將在單獨的步驟中計算它。您可以在EXPLAIN的輸出中看到InitPlan（初始計劃）。不相關的子查詢幾乎從來都不是性能問題。在本文的其余部分，將會主要討論相關的子查詢。
標量和表格子查詢
如果在 SQL 語句中的某個位置編寫一個子查詢，而該位置本來需要寫入單個值，則該子查詢是標量子查詢。標量子查詢的一個示例是上一節(jié)中的示例。一個不同的例子是
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
表格子查詢出現(xiàn)在可以返回多個值的上下文中：
? 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 ...)
標量子查詢通常有性能問題
我的經(jīng)驗法則是：應當盡量避免相關的標量子查詢。原因是 PostgreSQL 只能以嵌套循環(huán)方式來執(zhí)行標量子查詢。例如，PostgreSQL 會對表 “a” 中的每一行，執(zhí)行一次前面提到的子查詢。如果“a”是一個小表，這可能很好（請記住，這里的建議只是一個經(jīng)驗法則）。但是，如果表 “a” 很大，即使是快速的子查詢，也會使查詢執(zhí)行速度慢得令人難受。
重寫 SELECT 列表或 WHERE 子句中的標量子查詢
如果相關的標量子查詢對性能不利，我們?nèi)绾伪苊馑鼈?？沒有單一的、直接的答案，您可能無法重寫查詢，以避免在所有情況下都出現(xiàn)此類子查詢。但通常的解決方案是，將子查詢轉換為連接。對于我們的第一個查詢，它將如下所示：
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”的主鍵。根據(jù)a.col1分組是不夠的，因為表 “a” 中的兩個不同行可能具有相同的col1值。
像上面這樣重寫查詢的優(yōu)點是，PostgreSQL 可以選擇最佳連接策略，并且不限于嵌套循環(huán)。如果表 “a” 只有幾行，這可能沒有區(qū)別，因為無論如何，嵌套循環(huán)連接可能是最有效的連接策略。但是，在這種情況下，查詢也不會因重寫而表現(xiàn)變差。如果 “a” 很大，則使用哈?；蚝喜⑦B接的速度會快得多。
表格子查詢和性能
雖然相關的標量子查詢通常很糟糕，但表格子查詢的情況也沒那么簡單。讓我們分別考慮不同的情況。
FROM 中的 CTE 和子查詢
這些情況幾乎相同，因為您始終可以將 CTE 重寫為FROM中的子查詢，除非它是遞歸的、MATERIALIZED的或數(shù)據(jù)修改的 CTE。CTE 不會是相關的，因此它們永遠不會有問題。但是，FROM子句條目可以在橫向連接中關聯(lián)：
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 將在嵌套循環(huán)中執(zhí)行這樣的子查詢，這對于大型表 “a” 可能會表現(xiàn)不佳。因此，重寫查詢以避免相關的子查詢，通常是一個好主意：
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 優(yōu)化器能夠?qū)⒆泳浞謩e轉換為半連接和反連接。這使得 PostgreSQL 可以使用所有連接策略，而不僅僅是嵌套循環(huán)。
因此，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 優(yōu)化器可以做到這一點，并會像處理EXISTS中的子查詢一樣，高效地處理IN中的子查詢。
然而，NOT IN的情況卻大不相同。您可以像上面一樣，將NOT IN重寫為NOT EXISTS，但這不是 PostgreSQL 可以自動完成的轉換，因為重寫的語句在語義上不同：如果子查詢返回至少一個 NULL 值，則NOT IN永遠不會為 TRUE。而NOT EXISTS子句沒有表現(xiàn)出這種令人驚訝的行為。
現(xiàn)在人們通常不關心NOT IN的這個特點（事實上，很少有人知道它）。無論如何，大多數(shù)人都更喜歡NOT EXISTS的這種行為。但是您必須自己重寫 SQL 語句，并且不能指望 PostgreSQL 會自動執(zhí)行此操作。因此，我的建議是，您永遠不要使用帶有子查詢的NOT IN子句，而始終應改為使用NOT EXISTS 。
使用相關子查詢強制嵌套循環(huán)連接
到目前為止，我已經(jīng)告訴您如何重寫 SQL 語句，以避免強制優(yōu)化器使用嵌套循環(huán)。然而，有時你會需要完全相反的結果：你希望優(yōu)化器使用嵌套循環(huán)連接，因為你恰好知道這是最好的連接策略。然后，您可以有意識地將常規(guī)連接重寫為橫向交叉連接，以強制嵌套循環(huán)。例如，下面的查詢
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;
結論
如果您希望獲得良好的子查詢性能，通常最好遵循以下準則：
也不要把這些規(guī)則當作鐵律。有時，相關子查詢實際上可以表現(xiàn)得更好，有時您可以使用相關子查詢，來強制優(yōu)化器使用嵌套循環(huán)，只要您確定這是正確的策略。
該文章在 2024/8/8 5:20:13 編輯過
400 186 1886
