主持人 > 博客 > 管理 > PostgreSQL 反模式：“必須只有一個！”

PostgreSQL 反模式：“必須只有一個！”

在 SQL 中，您描述想要獲得“什麼”，而不是“如何”完成它。 因此，以“聽說如其所寫”的方式開發 SQL 查詢的問題佔據了重要地位，同時 SQL計算條件的特點.

今天，使用極其簡單的示例，讓我們看看這在使用環境中會產生什麼結果 GROUP/DISTINCT и LIMIT 跟他們。

如果你在請求中寫了 “首先連接這些符號，然後扔掉所有重複的， 應該只剩下一個了 每個鍵的實例” - 這正是它的工作方式，即使根本不需要連接。

有時你很幸運，它“正常工作”，有時它會對性能產生令人不快的影響，有時它會產生從開發人員的角度來看絕對意想不到的效果。

PostgreSQL 反模式：“必須只有一個！”
嗯，也許不是那麼壯觀，但是......

“甜蜜情侶”：JOIN + DISTINCT

SELECT DISTINCT
  X.*
FROM
  X
JOIN
  Y
    ON Y.fk = X.pk
WHERE
  Y.bool_condition;

他們想要什麼就很清楚了 選擇 Y 中存在與滿足條件相關的記錄的記錄 X。 通過寫了一個請求 JOIN - 多次收到 pk 的某些值（Y 中到底有多少條合適的記錄）。 如何去除？ 當然 DISTINCT!

當每個 X 記錄都有數百條相關的 Y 記錄，然後重複項被英勇地刪除時，這是特別“令人愉快”的……

PostgreSQL 反模式：“必須只有一個！”

怎麼修？ 首先，認識到任務可以修改為 “選擇記錄 X，其中 Y 中至少有一個與滿足條件相關聯” - 畢竟，我們不需要 Y 記錄本身的任何內容。

嵌套存在

SELECT
  *
FROM
  X
WHERE
  EXISTS(
    SELECT
      NULL
    FROM
      Y
    WHERE
      fk = X.pk AND
      bool_condition
    LIMIT 1
  );

某些版本的 PostgreSQL 知道在 EXISTS 中找到遇到的第一條記錄就足夠了，而舊版本則不然。 因此，我更願意始終表明 LIMIT 1 內 EXISTS.

橫向連接

SELECT
  X.*
FROM
  X
, LATERAL (
    SELECT
      Y.*
    FROM
      Y
    WHERE
      fk = X.pk AND
      bool_condition
    LIMIT 1
  ) Y
WHERE
  Y IS DISTINCT FROM NULL;

如有必要，同一選項允許立即同時從找到的關聯 Y 記錄中返回一些數據。 文章中討論了類似的選項 “PostgreSQL 反模式：稀有記錄將到達 JOIN 的中間”.

“為什麼要支付更多”：DISTINCT [ON] + LIMIT 1

此類查詢轉換的另一個好處是，如果只需要一條或幾條記錄，則能夠輕鬆限制對記錄的搜索，如下例所示：

SELECT DISTINCT ON(X.pk)
  *
FROM
  X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 1;

現在我們閱讀請求並嘗試了解 DBMS 應該做什麼：

  • 連接標誌
  • X.pk 獨有
  • 從剩餘條目中選擇一項

那麼你得到了什麼？ “只有一個條目” 從獨特的 - 如果你把這個非獨特的，結果會以某種方式改變嗎？..“如果沒有區別，為什麼要花更多錢呢？”

SELECT
  *
FROM
  (
    SELECT
      *
    FROM
      X
    -- сюда можно подсунуть подходящих условий
    LIMIT 1 -- +1 Limit
  ) X
JOIN
  Y
    ON Y.fk = X.pk
LIMIT 1;

和完全相同的主題 GROUP BY + LIMIT 1.

“我只需要問”：隱式 GROUP + LIMIT

類似的事情發生在不同的地方 非空檢查 隨著請求的進展，標籤或 CTE：

...
CASE
  WHEN (
    SELECT
      count(*)
    FROM
      X
    LIMIT 1
  ) = 0 THEN ...

聚合函數（count/min/max/sum/...）在整個集合上成功執行，即使沒有明確指定 GROUP BY。 只有這裡有 LIMIT 他們不是很友好。

開發者可以思考一下 “現在，如果那裡有記錄，那麼我只需要 LIMIT”。 但不要這樣做！ 因為對於基礎來說是：

  • 數數他們想要什麼 根據所有記錄
  • 按照他們的要求給出盡可能多的行

根據目標條件，可以進行以下替換之一：

  • (count + LIMIT 1) = 0 上 NOT EXISTS(LIMIT 1)
  • (count + LIMIT 1) > 0 上 EXISTS(LIMIT 1)
  • count >= N 上 (SELECT count(*) FROM (... LIMIT N))

“掛多少克”：DISTINCT + LIMIT

SELECT DISTINCT
  pk
FROM
  X
LIMIT $1

天真的開發人員可能真誠地相信請求將停止執行。 一旦我們找到第一個遇到的不同值中的 $1.

在未來的某個時候，由於新的節點，這可能並且將會起作用 索引跳躍掃描，目前正在製定實施方案，但尚未完成。

現在先 所有記錄將被檢索，都是唯一的，並且只會返回所請求的數量。 如果我們想要類似的東西，那就特別悲傷了 $ 1 = 4，表裡有幾十萬條記錄……

為了不白白悲傷，我們會使用遞歸查詢 PostgreSQL Wiki 中的“DISTINCT for the Poor”:

PostgreSQL 反模式：“必須只有一個！”

來源： www.habr.com

添加評論