亚洲乱色熟女一区二区三区丝袜,天堂√中文最新版在线,亚洲精品乱码久久久久久蜜桃图片,香蕉久久久久久av成人,欧美丰满熟妇bbb久久久

我們經(jīng)常會(huì)在 SQL 中使用到子查詢，正常情況下，PostgreSQL 的優(yōu)化器可以選擇最佳的執(zhí)行策略，但是在有些時(shí)候性能表現(xiàn)不一定很理想。

介紹

SQL 允許您在可能出現(xiàn)表或列名稱的幾乎任何地方使用子查詢。您所要做的就是用括號(hào)將查詢括起來(lái)，例如(SELECT ...)，然后您可以在任意表達(dá)式中使用它。這使得 SQL 成為一種強(qiáng)大的語(yǔ)言，但是可能難以閱讀。但我不想討論 SQL 的美或丑。在本教程中，我們來(lái)看看如何編寫出表現(xiàn)良好的子查詢。讓我們先從簡(jiǎn)單開始，稍后再來(lái)了解更令人驚訝和復(fù)雜的話題。

相關(guān)和不相關(guān)的子查詢

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

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

對(duì)“a”中的每一行，子查詢會(huì)不同。這樣的子查詢通常稱為相關(guān)子查詢。不相關(guān)的子查詢是指不引用任何外部?jī)?nèi)容的子查詢。

不相關(guān)的子查詢很簡(jiǎn)單。如果 PostgreSQL 優(yōu)化器沒有“拉起它”（將其集成到主查詢樹中），則執(zhí)行器將在單獨(dú)的步驟中計(jì)算它。您可以在EXPLAIN的輸出中看到InitPlan（初始計(jì)劃）。不相關(guān)的子查詢幾乎從來(lái)都不是性能問題。在本文的其余部分，將會(huì)主要討論相關(guān)的子查詢。

標(biāo)量和表格子查詢

如果在 SQL 語(yǔ)句中的某個(gè)位置編寫一個(gè)子查詢，而該位置本來(lái)需要寫入單個(gè)值，則該子查詢是標(biāo)量子查詢。標(biāo)量子查詢的一個(gè)示例是上一節(jié)中的示例。一個(gè)不同的例子是

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

如果標(biāo)量子查詢不返回任何結(jié)果，則結(jié)果值為 NULL。如果查詢返回多于一行，您會(huì)收到運(yùn)行時(shí)錯(cuò)誤：

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

表格子查詢出現(xiàn)在可以返回多個(gè)值的上下文中：

• ? FROM列表?xiàng)l目：FROM (SELECT ...) AS alias

• ? 公共表表達(dá)式（CTE）：WITH q AS (SELECT ...) SELECT ...

• ? IN或NOT IN表達(dá)式：WHERE a.x IN (SELECT ...)

• ? EXISTS或NOT EXISTS表達(dá)式：WHERE NOT EXISTS (SELECT ...)

標(biāo)量子查詢通常有性能問題

我的經(jīng)驗(yàn)法則是：應(yīng)當(dāng)盡量避免相關(guān)的標(biāo)量子查詢。原因是 PostgreSQL 只能以嵌套循環(huán)方式來(lái)執(zhí)行標(biāo)量子查詢。例如，PostgreSQL 會(huì)對(duì)表 “a” 中的每一行，執(zhí)行一次前面提到的子查詢。如果“a”是一個(gè)小表，這可能很好（請(qǐng)記住，這里的建議只是一個(gè)經(jīng)驗(yàn)法則）。但是，如果表 “a” 很大，即使是快速的子查詢，也會(huì)使查詢執(zhí)行速度慢得令人難受。

重寫 SELECT 列表或 WHERE 子句中的標(biāo)量子查詢

如果相關(guān)的標(biāo)量子查詢對(duì)性能不利，我們?nèi)绾伪苊馑鼈?？沒有單一的、直接的答案，您可能無(wú)法重寫查詢，以避免在所有情況下都出現(xiàn)此類子查詢。但通常的解決方案是，將子查詢轉(zhuǎn)換為連接。對(duì)于我們的第一個(gè)查詢，它將如下所示：

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

查詢?cè)谡Z(yǔ)義上是等效的，不同之處在于如果“a”中的行與“b”中的多行匹配，則不會(huì)收到運(yùn)行時(shí)錯(cuò)誤。我們需要一個(gè)外部連接，來(lái)說明子查詢不返回任何結(jié)果的情況。

對(duì)于我們的第二個(gè)示例，重寫后的查詢將如下所示：

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分組是不夠的，因?yàn)楸?“a” 中的兩個(gè)不同行可能具有相同的col1值。

像上面這樣重寫查詢的優(yōu)點(diǎn)是，PostgreSQL 可以選擇最佳連接策略，并且不限于嵌套循環(huán)。如果表 “a” 只有幾行，這可能沒有區(qū)別，因?yàn)闊o(wú)論如何，嵌套循環(huán)連接可能是最有效的連接策略。但是，在這種情況下，查詢也不會(huì)因重寫而表現(xiàn)變差。如果 “a” 很大，則使用哈?；蚝喜⑦B接的速度會(huì)快得多。

表格子查詢和性能

雖然相關(guān)的標(biāo)量子查詢通常很糟糕，但表格子查詢的情況也沒那么簡(jiǎn)單。讓我們分別考慮不同的情況。

FROM 中的 CTE 和子查詢

這些情況幾乎相同，因?yàn)槟冀K可以將 CTE 重寫為FROM中的子查詢，除非它是遞歸的、MATERIALIZED的或數(shù)據(jù)修改的 CTE。CTE 不會(huì)是相關(guān)的，因此它們永遠(yuǎn)不會(huì)有問題。但是，FROM子句條目可以在橫向連接中關(guān)聯(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í)行這樣的子查詢，這對(duì)于大型表 “a” 可能會(huì)表現(xiàn)不佳。因此，重寫查詢以避免相關(guān)的子查詢，通常是一個(gè)好主意：

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” 有很多行，則重寫后的查詢性能會(huì)更好，但如果 “a” 很小而 “b” 很大可能會(huì)更差，除非在(x, sort)上面有索引。

EXISTS 和 NOT EXISTS 中的子查詢

這是一個(gè)特例。到目前為止，我一直建議避免相關(guān)子查詢。但是使用EXISTS和NOT EXISTS，PostgreSQL 優(yōu)化器能夠?qū)⒆泳浞謩e轉(zhuǎn)換為半連接和反連接。這使得 PostgreSQL 可以使用所有連接策略，而不僅僅是嵌套循環(huán)。

因此，PostgreSQL 可以高效地處理EXISTS和NOT EXISTS的相關(guān)子查詢。

IN 和 NOT IN 的棘手情況

您可能會(huì)期望這兩種情況的行為相似，但事實(shí)并非如此。在一個(gè)查詢中，使用IN的子查詢始終可以使用EXISTS重寫。例如，下面的語(yǔ)句：

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

等價(jià)于

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

PostgreSQL 優(yōu)化器可以做到這一點(diǎn)，并會(huì)像處理EXISTS中的子查詢一樣，高效地處理IN中的子查詢。

然而，NOT IN的情況卻大不相同。您可以像上面一樣，將NOT IN重寫為NOT EXISTS，但這不是 PostgreSQL 可以自動(dòng)完成的轉(zhuǎn)換，因?yàn)橹貙懙恼Z(yǔ)句在語(yǔ)義上不同：如果子查詢返回至少一個(gè) NULL 值，則NOT IN永遠(yuǎn)不會(huì)為 TRUE。而NOT EXISTS子句沒有表現(xiàn)出這種令人驚訝的行為。

現(xiàn)在人們通常不關(guān)心NOT IN的這個(gè)特點(diǎn)（事實(shí)上，很少有人知道它）。無(wú)論如何，大多數(shù)人都更喜歡NOT EXISTS的這種行為。但是您必須自己重寫 SQL 語(yǔ)句，并且不能指望 PostgreSQL 會(huì)自動(dòng)執(zhí)行此操作。因此，我的建議是，您永遠(yuǎn)不要使用帶有子查詢的NOT IN子句，而始終應(yīng)改為使用NOT EXISTS 。

使用相關(guān)子查詢強(qiáng)制嵌套循環(huán)連接

到目前為止，我已經(jīng)告訴您如何重寫 SQL 語(yǔ)句，以避免強(qiáng)制優(yōu)化器使用嵌套循環(huán)。然而，有時(shí)你會(huì)需要完全相反的結(jié)果：你希望優(yōu)化器使用嵌套循環(huán)連接，因?yàn)槟闱『弥肋@是最好的連接策略。然后，您可以有意識(shí)地將常規(guī)連接重寫為橫向交叉連接，以強(qiáng)制嵌套循環(huán)。例如，下面的查詢

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

在語(yǔ)義上等價(jià)于

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

結(jié)論

如果您希望獲得良好的子查詢性能，通常最好遵循以下準(zhǔn)則：

• ? 盡可能地使用不相關(guān)的子查詢，只要它們不會(huì)讓語(yǔ)句難以理解

• ? 在所有地方避免使用相關(guān)子查詢，除非在EXISTS、NOT EXISTS和IN子句中

• ? 總是將NOT IN重寫為NOT EXISTS

也不要把這些規(guī)則當(dāng)作鐵律。有時(shí)，相關(guān)子查詢實(shí)際上可以表現(xiàn)得更好，有時(shí)您可以使用相關(guān)子查詢，來(lái)強(qiáng)制優(yōu)化器使用嵌套循環(huán)，只要您確定這是正確的策略。