主持人 > 博客 > 管理 > PostgreSQL 反模式：導航註冊表

PostgreSQL 反模式：導航註冊表

今天，SQL 中將不再有複雜的情況和複雜的演算法。 一切都會非常簡單，在 Captain Obvious 的水平上 - 讓我們開始吧 查看事件註冊表 按時間排序。

也就是資料庫中有一個標誌 events，她有一個領域 ts - 正是我們想要有序顯示這些記錄的時間：

CREATE TABLE events(
  id
    serial
      PRIMARY KEY
, ts
    timestamp
, data
    json
);

CREATE INDEX ON events(ts DESC);

很明顯，我們不會有十幾筆記錄，所以我們需要某種形式的 頁面導航.

#0。 “我是我母親的大屠殺犯”

cur.execute("SELECT * FROM events;")
rows = cur.fetchall();
rows.sort(key=lambda row: row.ts, reverse=True);
limit = 26
print(rows[offset:offset+limit]);

這幾乎不是一個笑話——它很罕見，但卻是在野外發現的。 有時，在使用 ORM 後，可能很難切換到「直接」使用 SQL。

但讓我們繼續討論更常見和不太明顯的問題。

#1. 抵銷

SELECT
  ...
FROM
  events
ORDER BY
  ts DESC
LIMIT 26 OFFSET $1; -- 26 - записей на странице, $1 - начало страницы

26這個數字是怎麼來的？ 這是填滿一個螢幕的大致條目數。 更準確地說，顯示了 25 條記錄，再加上 1 條，表明樣本中至少還有其他內容，因此繼續前進是有意義的。

當然，這個值不能「縫」到請求體中，而是透過參數傳遞。 但在這種情況下，PostgreSQL 調度程序將無法依賴記錄相對較少的知識 - 並且很容易選擇無效的計劃。

在應用程式介面中，查看註冊表是透過在視覺「頁面」之間切換來實現的，很長一段時間沒有人注意到任何可疑之處。 直到那一刻，為了方便起見，UI/UX 決定將介面重新設計為「無限滾動」——也就是說，所有登錄項目都繪製在用戶可以上下滾動的單一清單中。

所以，在下一次測試中，你被抓住了 重複記錄 在註冊表中。 為什麼呢，因為表有正常索引 (ts)，您的查詢依賴哪一個？

正是因為你沒有考慮到這一點 ts 不是唯一的密鑰 在此表中。 事實上，並且 它的值並不唯一，就像現實條件下的任何“時間”一樣- 因此，由於對相同鍵值進行排序的框架內的最終順序不同，兩個相鄰查詢中的相同記錄很容易從一個頁面“跳轉”到另一個頁面。

事實上，這裡還隱藏著第二個問題，這個問題更難被注意到—— 某些條目將不會顯示 根本！ 畢竟，「重複」記錄取代了別人的位置。 詳細的解釋和精美的圖片可以找到 在這裡閱讀.

擴大索引

狡猾的開發人員知道索引鍵需要唯一，最簡單的方法是用明顯唯一的欄位擴展它，PK 非常適合：

CREATE UNIQUE INDEX ON events(ts DESC, id DESC);

並且請求發生了變化：

SELECT
  ...
ORDER BY
  ts DESC, id DESC
LIMIT 26 OFFSET $1;

#2. 切換到“遊標”

一段時間後，DBA 來找您，並對您的請求感到“高興” 他們用 OFFSET 規則載入伺服器，一般來說，是時候切換到 從最後顯示的值開始導航。 您的查詢再次改變：

SELECT
  ...
WHERE
  (ts, id) < ($1, $2) -- последние полученные на предыдущем шаге значения
ORDER BY
  ts DESC, id DESC
LIMIT 26;

直到它到來，你才鬆了一口氣…

＃3。 清潔指標

因為有一天你的 DBA 讀到 關於尋找無效索引的文章 並意識到 「不是最新的」時間戳不好。 我又來找你了 - 現在我的想法是該索引仍然應該變回 (ts DESC).

但是如何解決頁面之間「跳躍」記錄的最初問題？...一切都很簡單 - 您需要選擇記錄數量不固定的區塊！

一般來說，誰禁止我們讀的不是“正好26”，而是“不少於26”？ 例如，在下一個區塊中有 具有明顯不同意義的記錄 ts - 那麼就不會出現區塊之間「跳躍」記錄的問題了！

以下是實現這一目標的方法：

SELECT
  ...
WHERE
  ts < $1 AND
  ts >= coalesce((
    SELECT
      ts
    FROM
      events
    WHERE
      ts < $1
    ORDER BY
      ts DESC
    LIMIT 1 OFFSET 25
  ), '-infinity')
ORDER BY
  ts DESC;

這裡發生了什麼事？

  1. 我們「向下」步進 25 筆記錄並得到「邊界」值 ts.
  2. 如果那裡已經沒有任何內容，則將 NULL 值替換為 -infinity.
  3. 我們在接收到的值之間減去整個段的值 ts 以及從介面傳遞的 $1 參數（之前的「最後」渲染值）。
  4. 如果傳回的區塊的記錄少於 26 條，則它是最後一個。

或同一張圖片：
PostgreSQL 反模式：導航註冊表

因為現在我們有 樣本沒有任何特定的“開始”，那麼沒有什麼可以阻止我們向相反的方向「擴展」這個請求，並實現從「參考點」兩個方向（向下和向上）動態載入資料塊。

備註

  1. 是的，在這種情況下，我們訪問索引兩次，但一切都是“純粹通過索引”。 因此，子查詢只會導致 到一個額外的僅索引掃描.
  2. 很明顯，只有當你有價值觀時才能使用這種技術 ts 只有偶然才能穿越，並且 數量並不多。 如果您的典型情況是“00:00:00.000 有一百萬筆記錄”，您不應該這樣做。 我的意思是，你不應該允許這樣的情況發生。 但如果發生這種情況，請使用具有擴展索引的選項。

來源： www.habr.com

添加評論