主持人 > 博客 > 管理 > PostgreSQL 反模式：按名稱迭代優化搜索或“來回優化”的故事

PostgreSQL 反模式：按名稱迭代優化搜索或“來回優化”的故事

全國銷售處數千名經理記錄 我們的客戶關係管理系統 每天有數以萬計的聯絡人 — 與潛在或現有客戶溝通的事實。 為此，您必須先找到客戶，最好是盡快找到客戶。 這種情況最常發生在名字上。

因此，再次分析負載最重的資料庫之一（我們自己的資料庫）上的「繁重」查詢也就不足為奇了 VLSI企業帳戶，我發現“在頂部” 請求按名稱“快速”搜索 用於組織卡。

此外，進一步的調查發現了一個有趣的例子 先優化，後效能下降 請求經過多個團隊的連續改進，每個團隊的行為都是出於最好的意圖。

0：用戶想要什麼？

PostgreSQL 反模式：按名稱迭代優化搜索或“來回優化”的故事[KDPV 故]

當用戶談論按名稱進行「快速」搜尋時，通常意味著什麼？ 它幾乎從來都不是對像這樣的子字串的“誠實”搜索 ... LIKE '%роза%' - 因為這樣結果不只包括 'Розалия' и 'Магазин Роза'但 'Гроза' идаже 'Дом Деда Мороза'.

用戶在日常生活中假設您將為他提供 按單字開頭搜尋 在標題中並使其更相關 以。。開始 進入。 你會做到的 幾乎立即 - 用於行間輸入。

1：限制任務

更重要的是，一個人不會特別進入 'роз магаз'，因此您必須按前綴搜尋每個單字。 不，對於用戶來說，響應最後一個單字的快速提示比故意「低估」前面的單字要容易得多 - 看看搜尋引擎是如何處理這個問題的。

一般情況下， 正確地 制定出問題的需求就已經解決了一半以上了。 有時仔細的用例分析 可以顯著影響結果.

抽象開發人員做什麼？

1.0：外部搜尋引擎

哦，搜索很難，我根本不想做任何事情——讓我們把它交給 devops！ 讓他們在資料庫外部部署一個搜尋引擎：Sphinx、ElasticSearch…

一個可行的選擇，儘管在同步和變化速度方面是勞動密集的。 但在我們的例子中並非如此，因為搜尋僅在每個客戶的帳戶資料框架內進行。 並且數據具有相當高的可變性 - 如果經理現在已經輸入卡 'Магазин Роза'，然後 5-10 秒後，他可能已經記得他忘記在那裡標明他的電子郵件，並想要找到它並更正它。

因此 - 讓我們 “直接在資料庫中搜尋”。 幸運的是，PostgreSQL 允許我們做到這一點，而且不僅僅是一種選擇 - 我們將看看它們。

1.1：「誠實」子串

我們堅持使用「子字串」這個字。 但是對於通過子字串（甚至通過正則表達式！）進行索引搜索，有一個很好的方法 模組 pg_trgm！ 這樣才需要正確排序。

我們試著拿下圖來簡化模型：

CREATE TABLE firms(
  id
    serial
      PRIMARY KEY
, name
    text
);

我們在那裡上傳了 7.8 萬個真實組織的記錄並為其建立索引：

CREATE EXTENSION pg_trgm;
CREATE INDEX ON firms USING gin(lower(name) gin_trgm_ops);

讓我們找出行間搜尋的前 10 筆記錄：

SELECT
  *
FROM
  firms
WHERE
  lower(name) ~ ('(^|s)' || 'роза')
ORDER BY
  lower(name) ~ ('^' || 'роза') DESC -- сначала "начинающиеся на"
, lower(name) -- остальное по алфавиту
LIMIT 10;

PostgreSQL 反模式：按名稱迭代優化搜索或“來回優化”的故事
[看看 explain.tensor.ru]

嗯，那是... 26 毫秒，31MB 讀取資料和超過 1.7K 筆過濾記錄 - 10 個搜尋記錄。 管理費用太高了，難道沒有更有效的方法嗎？

1.2：透過文字搜尋？ 這是FTS！

確實，PostgreSQL 提供了非常強大的 全文搜尋引擎 （全文搜尋），包括前綴搜尋的能力。 一個很好的選擇，您甚至不需要安裝擴充功能！ 咱們試試：

CREATE INDEX ON firms USING gin(to_tsvector('simple'::regconfig, lower(name)));

SELECT
  *
FROM
  firms
WHERE
  to_tsvector('simple'::regconfig, lower(name)) @@ to_tsquery('simple', 'роза:*')
ORDER BY
  lower(name) ~ ('^' || 'роза') DESC
, lower(name)
LIMIT 10;

PostgreSQL 反模式：按名稱迭代優化搜索或“來回優化”的故事
[看看 explain.tensor.ru]

這裡查詢執行的平行化對我們有一點幫助，將時間減少了一半 11毫秒。 我們總共需要閱讀的內容減少了 1.5 倍 20MB。 但在這裡，越少越好，因為我們讀取的捲越大，快取未命中的機會就越高，並且從磁碟讀取的每一個額外的資料頁都是請求的潛在「煞車」。

1.3：還喜歡嗎？

前面的要求對大家都好，但是只有每天拉十萬次才會來 2TB 讀取資料。 在最好的情況下，從內存中，但如果你不幸運，那麼從磁碟中。 因此，讓我們嘗試將其變小。

讓我們記住用戶想看到什麼 第一個“以…開頭”。 所以這是最純粹的形式 前綴搜尋 在...的幫助下 text_pattern_ops！ 只有當我們「沒有足夠的」最多 10 條要尋找的記錄時，我們才必須使用 FTS 搜尋來完成讀取它們：

CREATE INDEX ON firms(lower(name) text_pattern_ops);

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('роза' || '%')
LIMIT 10;

PostgreSQL 反模式：按名稱迭代優化搜索或“來回優化”的故事
[看看 explain.tensor.ru]

出色的表現 - 總計 0.05ms，略多於 100KB 讀！ 只是我們忘了 按名稱分類這樣用戶就不會迷失在結果中：

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('роза' || '%')
ORDER BY
  lower(name)
LIMIT 10;

PostgreSQL 反模式：按名稱迭代優化搜索或“來回優化”的故事
[看看 explain.tensor.ru]

哦，有些東西不再那麼漂亮了——似乎有一個索引，但排序卻飛過去了……當然，它已經比以前的選項有效很多倍了，但是…

1.4：“用文件完成”

但是有一個索引可以讓你按範圍搜尋並仍然正常使用排序 - 常規B樹!

CREATE INDEX ON firms(lower(name));

只有請求才需要「手動收集」：

SELECT
  *
FROM
  firms
WHERE
  lower(name) >= 'роза' AND
  lower(name) <= ('роза' || chr(65535)) -- для UTF8, для однобайтовых - chr(255)
ORDER BY
   lower(name)
LIMIT 10;

PostgreSQL 反模式：按名稱迭代優化搜索或“來回優化”的故事
[看看 explain.tensor.ru]

非常好 - 排序工作正常，並且資源消耗仍然“微觀”， 比「純」FTS 有效數千倍！ 剩下的就是將其組合成一個請求：

(
  SELECT
    *
  FROM
    firms
  WHERE
    lower(name) >= 'роза' AND
    lower(name) <= ('роза' || chr(65535)) -- для UTF8, для однобайтовых кодировок - chr(255)
  ORDER BY
     lower(name)
  LIMIT 10
)
UNION ALL
(
  SELECT
    *
  FROM
    firms
  WHERE
    to_tsvector('simple'::regconfig, lower(name)) @@ to_tsquery('simple', 'роза:*') AND
    lower(name) NOT LIKE ('роза' || '%') -- "начинающиеся на" мы уже нашли выше
  ORDER BY
    lower(name) ~ ('^' || 'роза') DESC -- используем ту же сортировку, чтобы НЕ пойти по btree-индексу
  , lower(name)
  LIMIT 10
)
LIMIT 10;

注意執行的是第二個子查詢 僅當第一個回傳值低於預期時 最後 LIMIT 行數。 我說的是這種查詢優化的方法 之前已經寫過.

所以，是的，我們現在桌上有 btree 和 gin，但從統計數據來看，事實證明 不到 10% 的請求到達第二個區塊的執行。 也就是說，透過事先了解任務的此類典型限制，我們能夠將伺服器資源的總消耗量減少近千倍！

1.5*：我們可以不用文件

以上 LIKE 我們被阻止使用不正確的排序。 但可以透過指定 USING 運算子來「設定在正確的路徑上」：

預設假設 ASC。 此外，您可以在子句中指定特定排序運算子的名稱 USING。 排序運算子必須是某些 B 樹運算子系列的小於或大於的成員。 ASC 通常等價 USING < и DESC 通常等價 USING >.

在我們的例子中，「少」是 ~<~:

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('роза' || '%')
ORDER BY
  lower(name) USING ~<~
LIMIT 10;

PostgreSQL 反模式：按名稱迭代優化搜索或“來回優化”的故事
[看看 explain.tensor.ru]

2：請求如何變壞

現在，我們將請求“醞釀”六個月或一年，我們驚訝地發現它再次“位於頂部”，並帶有每日總“泵送”內存的指標（緩衝區共享命中）在 5.5TB ——也就是說，甚至比原來還要多。

不，當然，我們的業務成長了，我們的工作量增加了，但幅度不一樣！ 這意味著這裡有些可疑——讓我們找出答案。

2.1：分頁的誕生

在某個時候，另一個開發團隊希望能夠從快速下標搜尋「跳轉」到註冊表，並獲得相同但擴展的結果。 沒有頁面導航的註冊表是什麼？ 讓我們搞砸吧！

( ... LIMIT <N> + 10)
UNION ALL
( ... LIMIT <N> + 10)
LIMIT 10 OFFSET <N>;

現在可以透過「逐頁」載入來顯示搜尋結果的註冊表，而不會給開發人員帶來任何壓力。

當然，事實上， 對於每個後續資料頁，讀取的資料越來越多 （所有來自上一次的，我們將丟棄，加上必要的“尾巴”） - 也就是說，這是一個明顯的反模式。 但是，在下一次迭代時從介面中儲存的密鑰開始搜尋會更正確，但大約是另一次。

2.2：我想要一些異國情調的東西

在某些時候，開發人員想要 用數據使結果樣本多樣化 來自另一個表，先前的整個請求已發送到 CTE：

WITH q AS (
  ...
  LIMIT <N> + 10
)
SELECT
  *
, (SELECT ...) sub_query -- какой-то запрос к связанной таблице
FROM
  q
LIMIT 10 OFFSET <N>;

即便如此，這也不錯，因為子查詢僅針對 10 個返回的記錄進行評估，如果不是的話...

2.3：DISTINCT是無意義且無情的

從第二個子查詢演變過程中的某個地方 迷路了 NOT LIKE 條件。 很明顯，在這之後 UNION ALL 開始返回 有些條目兩次 - 首先在行的開頭找到，然後再次 - 在該行的第一個單字的開頭找到。 在限制範圍內，第二個子查詢的所有記錄都可以與第一個子查詢的記錄相符。

開發人員不尋找原因會做什麼？...毫無疑問！

  • 雙倍大小 原始樣品
  • 應用不同僅取得每行的單一實例

WITH q AS (
  ( ... LIMIT <2 * N> + 10)
  UNION ALL
  ( ... LIMIT <2 * N> + 10)
  LIMIT <2 * N> + 10
)
SELECT DISTINCT
  *
, (SELECT ...) sub_query
FROM
  q
LIMIT 10 OFFSET <N>;

也就是說，很明顯，最終的結果是完全相同的，但是「飛」到第二個 CTE 子查詢的機會變得更高，即使沒有這個， 顯然更具可讀性.

但這還不是最悲傷的事。 由於開發商要求選擇 DISTINCT 不是針對特定領域，而是同時針對所有領域 記錄，然後 sub_query 欄位（子查詢的結果）會自動包含在那裡。 現在，執行 DISTINCT，資料庫必須已經執行 不是 10 個子查詢，而是全部 <2 * N> + 10!

2.4：合作高於一切！

因此，開發人員繼續生活 - 他們沒有打擾，因為用戶顯然沒有足夠的耐心將註冊表「調整」到顯著的 N 值，並且接收每個後續「頁面」的速度長期減慢。

直到其他部門的開發人員找到他們，想要用這麼方便的方法 用於迭代搜尋 - 也就是說，我們從某個樣本中取出一塊，通過附加條件對其進行過濾，繪製結果，然後繪製下一塊（在我們的例子中是通過增加N 來實現的），依此類推，直到填滿屏幕。

一般來說，在捕獲的標本中 N達到了接近17K的值，僅在一天之內，「沿鏈」執行了至少 4K 個此類請求。 最後一個被大膽地掃描了 每次迭代 1GB 內存...

在總

PostgreSQL 反模式：按名稱迭代優化搜索或“來回優化”的故事

來源： www.habr.com

添加評論