五個容易錯過的 PostgreSQL 查詢效能瓶頸

banq發表於2022-01-21
SQL

PostgreSQL 查詢計劃器充滿了驚喜，因此編寫高效能查詢的常識性方法有時會產生誤導。在這篇博文中，我將描述藉助 EXPLAIN ANALYZE 和 Postgres 後設資料分析最佳化看似顯而易見的查詢的示例。

1. 透過函式呼叫搜尋
透過使用 PostgreSQL 函式呼叫修改的值進行搜尋是很常見的。讓我們看一下透過小寫值搜尋列的查詢計劃：

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM users WHERE lower(email) = 'email@example.com' ;

->  Parallel Seq Scan on users
   Filter: (lower((email)::text) = 'email@example.com'::text)
   Rows Removed by Filter: 333667
   Buffers: shared hit=1248 read=41725
Execution Time: 180.813 ms


該報告表明查詢計劃器執行了低效的Seq Scan操作Filter來執行查詢。由於在BUFFERS查詢中新增了選項，我們可以看到資料庫必須使用慢速磁碟讀取操作來獲取超過 40k 的資料頁，並且其中只有大約 1k 被快取在記憶體中。
按函式搜尋的查詢不能使用標準索引。因此，您需要新增自定義索引以使其高效。
但是，在每個查詢的基礎上新增自定義索引並不是一種非常可擴充套件的方法。您可能會發現自己有多個冗餘索引，這會顯著減慢寫入操作。
如果大小寫字母無關緊要，您可以執行遷移以小寫所有值並使標準索引起作用。但是，如果您仍想在資料庫中儲存大寫字元，您可以考慮使用CITEXT 副檔名。它建立了一個不區分大小寫的列，可以在不建立自定義索引的情況下進行高效搜尋。

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM users WHERE email = 'Email@exaMple.Com' ;

 Index Scan using index_users_on_email on users
   Index Cond: (email = 'Email@exaMple.Com'::citext)
   Buffers: shared hit=3
 Execution Time: 0.128 ms


原始查詢的180 毫秒執行時間可能看起來並不多。但我們剛剛設法將其加速了幾個數量級，降至 1毫秒以下！
無論資料大小如何，新解決方案都將保持高效能，並且查詢僅從記憶體快取中獲取三個緩衝區塊。此外，透過利用擴充套件，我們可以避免新增額外的索引。
 

2. 按模式搜尋
LIKE和ILIKE查詢經常被使用，但並不總是很明顯需要額外的設定來有效地執行它們。讓我們看看示例查詢在標準 B 樹索引下的表現：

EXPLAIN ANALYZE SELECT * FROM users WHERE email LIKE '%@example.com';

->  Parallel Seq Scan on users
     Filter: ((email)::text ~~ '%@example.com'::text)
 Execution Time: 111.263 ms

和以前一樣，查詢計劃器無法利用索引，不得不求助於低效Seq Scan的Filter.
為了加快這個查詢的速度，我們必須新增一個自定義擴充套件和索引型別。執行以下命令：

CREATE EXTENSION IF NOT EXISTS btree_gin;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX index_users_on_email_gin ON users USING gin (email gin_trgm_ops);

讓我們重新執行我們的查詢：

EXPLAIN ANALYZE SELECT * FROM users WHERE email LIKE '%@example.com';

Bitmap Heap Scan on users
   Recheck Cond: ((email)::text ~~ '%@example.com'::text)
   ->  Bitmap Index Scan on index_users_on_email_gin
         Index Cond: ((email)::text ~~ '%@example.com'::text)
 Execution Time: 0.206 ms

現在它在1ms以下執行。請記住，gin索引的更新速度比標準的要慢。因此，您應該避免將它們新增到經常更新的表中。
  

3. Ordering by NULLS LAST
除非列配置為NOT NULL，否則在使用它進行排序時必須小心。
預設ASC順序將始終在結果末尾返回NULL值。
但是，如果您想按降序對潛在可能是NULL的字串進行排序但將所有NULLs保留在末尾怎麼辦？最初的方法可能是利用NULLS LAST自定義排序順序。讓我們仔細看看EXPLAIN ANALYZE此類查詢會產生的輸出：

EXPLAIN ANALYZE
SELECT * FROM users
ORDER BY email DESC LIMIT 10;

->  Index Scan Backward using index_users_on_email on users
  Execution Time: 0.641 ms



我們可以看到一個Index Scan Backward條目，因此我們的查詢正確地使用了索引，並且幾乎立即執行。但是，此查詢的結果將始終從NULL值開始。因此，如果我們想將它們移動到響應的末尾，我們可以像這樣重寫它：

EXPLAIN ANALYZE
SELECT * FROM users
ORDER BY email DESC NULLS LAST LIMIT 10;

->  Sort  (cost=41482.85..42525.55 rows=417083 width=41) (actual time=5572.524..5572.589 rows=8 loops=3)
   Sort Key: email DESC NULLS LAST
   Sort Method: top-N heapsort  Memory: 26kB
   ->  Parallel Seq Scan on users  (cost=0.00..32469.83 rows=417083 width=41) (actual time=0.037..2793.011 rows=333667 loops=3)
 Execution Time: 5578.725 ms


正如您所看到的，查詢現在執行了超過5 SECONDS。
儘管email列被索引，但標準索引不能用於帶有NULLS LAST選項的排序。相反，資料庫必須在記憶體中對整個表進行排序，或者退回到更慢的磁碟排序。它不僅會降低效能，而且還會顯著增加整體記憶體使用量。
您可以透過新增自定義索引來修復它，但是，就像在按函式搜尋的情況下一樣，在每個查詢的基礎上新增自定義索引是一種不好的做法。
獲得所需結果的一種簡單方法是編寫兩個查詢。第一個將獲取已排序的非空值。如果結果不滿足LIMIT，則另一個查詢會獲取剩餘的帶有NULL值的行。

SELECT *
FROM users ORDER BY email DESC
WHERE email IS NOT NULL LIMIT 10;

SELECT *
FROM users
WHERE email IS NULL LIMIT 10;


4. Bloated null_indexes
新增正確的索引可以顯著提高查詢執行時間。但是，過度使用索引會大大增加資料庫的大小並增加維護記憶體的使用。此外，必須在每次寫入操作時更新索引。所以限制它們的數量和範圍通常是一個好方法。
您的資料庫可能有一些所謂的（我認為）“NULL 索引”。這些是包含高比率NULL值的索引。
根據業務邏輯，NULL可能會使用一個值進行搜尋，因此這些索引是正確的。但是，通常，您不會編寫查詢來搜尋包含特定NULL值的行。如果是這種情況，重新建立索引以排除NULLs 將減少磁碟使用量並限制必須更新的頻率。
您可以執行以下命令來刪除和重建索引以僅包含NOT NULL行：

DROP INDEX CONCURRENTLY users_reset_token_ix;

CREATE INDEX CONCURRENTLY users_reset_token_ix ON users(reset_token)
WHERE reset_token IS NOT NULL;



值得注意的是，這個索引仍然可以被顯式搜尋所有NOT NULL值的查詢使用。
您可以檢視PG Extrasnull_indexes方法（或執行其原始 SQL 原始碼）以檢視您的資料庫是否有許多可以削減的索引以及預期的磁碟空間節省。

您可以在這篇博文中閱讀更多關於使用 PG Extras 最佳化 PostgreSQL 效能的資訊。
 

5.更新事務範圍
通常推薦的做法是將資料庫提交的數量保持在最低限度。這意味著將多個更新查詢包裝到單個事務中應該可以提高寫入效能。
對於許多常見場景，這是一個最佳策略。但是，使用單個事務進行大量資料更新可能會導致所謂的鎖問題。那麼讓我們看看在單個事務中更新超過 100k 行有什麼影響：
UPDATE messages SET status = 'archived';
當事務仍處於掛起狀態時，您可以使用PG Extraslocks方法（或執行其原始 SQL 原始碼）調查它生成的鎖。
您可能沒有足夠大的資料集來locks在更新事務仍在執行時手動執行 SQL。在這種情況下，您可以像這樣在單個事務中偽造緩慢的執行時間：

BEGIN;
UPDATE messages SET status = 'archived';
SELECT pg_sleep(15);
COMMIT;


現在，執行locksSQL 應該會返回類似的輸出：

      relname             |       mode       |          query_snippet
-------------------------------------------------------------------------------
 messages                 | RowExclusiveLock | UPDATE "messages" SET "status" = $1
 index_messages_on_status | RowExclusiveLock | UPDATE "messages" SET "status" = $1
 index_messages_on_text   | RowExclusiveLock | UPDATE "messages" SET "status" = $1
 index_messages_on_time   | RowExclusiveLock | UPDATE "messages" SET "status" = $1
 ...

可以看到更新操作獲取了錶行上的RowExclusiveLock鎖，並鎖定了對應的索引。
這意味著在漫長的單事務更新過程中嘗試更新相同行的任何其他程式都必須等待它完成。因此，後臺工作程式執行的大規模更新可能會使 Web 伺服器程式超時並導致面向使用者的應用程式中斷。
為避免此問題，您可以使用類似的 SQL 將批處理新增到更新操作：

UPDATE messages SET status = 'archived'
  WHERE id IN
  (SELECT ID FROM messages ORDER BY ID LIMIT 10000 OFFSET 0);

UPDATE messages SET status = 'archived'
  WHERE id IN
  (SELECT ID FROM messages ORDER BY ID LIMIT 10000 OFFSET 10000);

UPDATE messages SET status = 'archived'
  WHERE id IN
  (SELECT ID FROM messages ORDER BY ID LIMIT 10000 OFFSET 20000);

...



上面的示例一次更新 10k 的行。整個操作可能需要比在單個事務中執行更長的時間。但是，每個更新步驟都會快速提交資料庫更改，因此其他程式不會卡住。
如果您懷疑您的應用程式的效能因鎖定事務而下降，您可以結合使用locksPG blockingExtras 方法來監控長期表鎖。
 

相關文章