英文版刊載於Flexport Engineering Blog
簡介
使用更多的索引並不一定能提升資料庫效能。“少即是多”的原則也適用於資料庫領域。本文將介紹我們通過少用索引使一個PostgreSQL查詢提速幾千倍，從分鐘級提升到毫秒級的經驗。
問題
Flexport平臺讓Flexport的客戶、運營人員和其他使用者能傳送訊息進行交流。這個功能的資料儲存於AWS Aurora PostgreSQL上的一個訊息表，此表的規模為數千萬行級。
訊息表上的主查詢通常極快，但是近期它遇到了一些間歇的慢查詢超時。慢查詢不但影響了訊息功能的使用者體驗，而且加大了整個系統的負荷，拖慢了其他功能的使用者體驗。
SELECT messages.* FROM messages
WHERE messages.deleted_at IS NULL AND messages.namespace = ?
AND (
jsonb_extract_path_text(context, 'topic') IN (?, ?)
OR jsonb_extract_path_text(context, 'topic') LIKE ?
)
AND ( context @> '{"involved_parties":[{"id":1,"type":1}]}'::jsonb )
ORDER BY messages.created_at ASC它用了一些PostgreSQL特有的語法。那麼這個查詢是在幹什麼呢？
那個@>符號是PostgreSQL的“包含”操作符。請參考官方文件，簡單來說它就是一種支援多層級的子集運算，判斷左側是否“包含”右側（例如，某個大物件是否包含一個小物件）。這個操作符可以被用於一些結構化型別，例如陣列(array)、範圍(range)或JSONB（PostgreSQL中的JSON二進位制表示格式）。
訊息表有一個整數型的id列，一個JSONB型的context列，以及其他列。在context列中的JSONB物件包含了一條訊息的描述性屬性，例如，topic屬性描述了此訊息的主題，involved_parties屬性描述了有權檢視此訊息的法人實體的列表。
在context列上有兩個索引：
- context列上的GIN索引
- jsonb_extract_path_text(context, ‘topic’)表示式上的BTREE表示式索引
GIN是PostgreSQL提供的一款用於複雜值的索引引擎，一般用於陣列、JSON或文字等的資料結構。GIN的設計用途是索引那些可對內部結構做細分的資料，這樣就可以查詢資料內部的子資料了。BTREE是PostgreSQL的預設索引引擎，能對簡單值做相等性比較或範圍查詢。表示式索引是PostgreSQL提供的一種強力的索引型別，能對一個表示式（而不是一個列）做索引。JSONB型別一般只能用GIN這樣的索引引擎，因為BTREE只支援標量型別（可以理解為“沒有內部結構的簡單值型別”）。因此，context列上的jsonb_extract_path_text(context, ‘topic’)表示式可以用BTREE索引，因為它返回字串型別。不同於BTREE索引統一而一致的表示格式，GIN索引的內容可以因所用資料型別和操作符型別的不同而極為不同。而且考慮到查詢引數的選擇度有較高的多樣性，GIN索引更適用於一些特定的查詢，不像BTREE索引廣泛適用於相等性比較和範圍查詢。
初調查
一個查詢通常會先做索引掃描以初篩，再對篩選後的範圍做表掃描（一個特例是，當索引掃描足以覆蓋所需的所有資料列時，則無需表掃描）。為了最大化效能，索引要有較好的選擇度來縮小範圍，以減少甚至避免之後的表掃描。條件context @> ‘{“involved_parties”:[{“id”:1,”type”:1}]}’::jsonb能使用context列上的GIN索引，但是這並不是一個好選擇，因為{“id”:1,”type”:1}這個值是存在於大多數行中的一個特殊值（這數字就很特殊，像管理員的號碼）。因此，GIN索引對於這個條件的選擇度很差。實際上，這個查詢中的其他條件已能提供很好的選擇度，所以永遠不需要為這個條件使用索引。現在我們需要知道這個查詢究竟有沒有在這個條件上使用GIN索引。
我們可以在SQL控制檯中執行形如“EXPLAIN ANALYZE {the query statement}”的語句來得到查詢計劃。這種語句會實際執行查詢並返回所選的查詢計劃（其上標註有實際時間耗費）。
然而，我們只得到了一個快查詢計劃，它沒有使用這個GIN索引。調查遇到了麻煩。
深入技術細節
首先，通過視覺化表示來讀懂這個快查詢計劃。
快查詢計劃
QUERY PLAN
------------------------------------------------------------------------------
Sort (cost=667.75..667.76 rows=3 width=911) (actual time=0.093..0.094 rows=7 loops=1)
Sort Key: created_at
Sort Method: quicksort Memory: 35kB
-> Bitmap Heap Scan on messages (cost=14.93..667.73 rows=3 width=911) (actual time=0.054..0.077 rows=7 loops=1)
Recheck Cond: ((jsonb_extract_path_text(context, VARIADIC '{topic}'::text[]) = ANY ('{?,?}'::text[])) OR (jsonb_extract_path_text(context, VARIADIC '{topic}'::text[]) ~~ '?%'::text))
Filter: ((deleted_at IS NULL) AND (context @> '{"involved_parties": [{"id": 1, "type": 1}]}'::jsonb) AND ((namespace)::text = '?'::text) AND ((jsonb_extract_path_text(context, VARIADIC '{topic}'::text[]) = ANY ('{?,?}'::text[])) OR (jsonb_extract_path_text(context, VARIADIC '{topic}'::text[]) ~~ '?%'::text)))
Heap Blocks: exact=7
-> BitmapOr (cost=14.93..14.93 rows=163 width=0) (actual time=0.037..0.037 rows=0 loops=1)
-> Bitmap Index Scan on index_messages_on_topic_key_string (cost=0.00..10.36 rows=163 width=0) (actual time=0.029..0.029 rows=4 loops=1)
Index Cond: (jsonb_extract_path_text(context, VARIADIC '{topic}'::text[]) = ANY ('{?,?}'::text[]))
-> Bitmap Index Scan on index_messages_on_topic_key_string (cost=0.00..4.57 rows=1 width=0) (actual time=0.007..0.007 rows=7 loops=1)
Index Cond: ((jsonb_extract_path_text(context, VARIADIC '{topic}'::text[]) ~>=~ '?'::text) AND (jsonb_extract_path_text(context, VARIADIC '{topic}'::text[]) ~<~ '?'::text))
Planning time: 0.313 ms
Execution time: 0.138 ms快查詢計劃的視覺化表示
如下圖，查詢計劃就像一個呼叫棧。查詢被自頂向下地展開成一棵樹，然後這棵樹被自底向上地求值。例如，兩個葉節點（Bitmap Index Scan）先求值，其結果被父節點（BitmapOr）合併，再返回給祖節點（Bitmap Heap Scan），等等。請注意，葉節點中的index 1和index 2實為同一個BTREE表示式索引，只不過被用於兩個不同條件的掃描。
一個PostgreSQL查詢可以同時掃描多個索引再合併其結果，最慢的那個索引決定了索引掃描的總體效能。這通常會比單索引掃描更高效，因為多個索引通常能更大程度地篩選資料集，從而能少讀磁碟（對篩選後的資料集作進一步掃描時，若資料不在記憶體快取中，就要從磁碟讀取）。有一步叫做Bitmap Heap Scan，能合併索引掃描的結果，所以能在查詢計劃中看到“BItmap Heap Scan”字樣。
進一步調查
基於現有資訊，主要關注這些要點：
- 不大可能是資源爭用所致，因為爭用應平等地影響所有的SQL語句，不只是SELECT語句，但只觀測到慢的SELECT？
- 可能是同一查詢即使用相同引數也偶爾會選擇不同的計劃。
- 也可能是GIN相關的問題。Gitlab曾遇到GIN相關的問題。
一個猜想是“資料和統計資訊的更新使得查詢計劃器偶爾選擇了一個使用GIN索引的慢查詢計劃”。
無論有沒有猜想，對於這種情況，最好的行動都是提高可觀察性。缺少一些資料嗎？好的，收集它！我們用一些程式碼來記錄查詢執行時間，並只在查詢真的慢時抓取慢查詢計劃。然後就證明了原因的確是不穩定的查詢計劃：查詢計劃器通常選擇快查詢計劃，但偶爾會選擇慢查詢計劃。
慢查詢計劃
QUERY PLAN
------------------------------------------------------------------------------
Sort (cost=540.08..540.09 rows=3 width=915)
Sort Key: created_at
-> Bitmap Heap Scan on messages (cost=536.03..540.06 rows=3 width=915)
Recheck Cond: (((jsonb_extract_path_text(context, VARIADIC '{topic}'::text[]) = ANY ('{?,?}'::text[])) OR (jsonb_extract_path_text(context, VARIADIC '{topic}'::text[]) ~~ '?%'::text)) AND (context @> '{"involved_parties": [{"id": 1, "type": 1}]}'::jsonb))
Filter: ((deleted_at IS NULL) AND ((namespace)::text = '?'::text) AND ((jsonb_extract_path_text(context, VARIADIC '{topic}'::text[]) = ANY ('{?,?}'::text[])) OR (jsonb_extract_path_text(context, VARIADIC '{topic}'::text[]) ~~ '?%'::text)))
-> BitmapAnd (cost=536.03..536.03 rows=1 width=0)
-> BitmapOr (cost=20.13..20.13 rows=249 width=0)
-> Bitmap Index Scan on index_messages_on_topic_key_string (cost=0.00..15.55 rows=249 width=0)
Index Cond: (jsonb_extract_path_text(context, VARIADIC '{topic}'::text[]) = ANY ('{?,?}'::text[]))
-> Bitmap Index Scan on index_messages_on_topic_key_string (cost=0.00..4.57 rows=1 width=0)
Index Cond: ((jsonb_extract_path_text(context, VARIADIC '{topic}'::text[]) ~>=~ '?'::text) AND (jsonb_extract_path_text(context, VARIADIC '{topic}'::text[]) ~<~ '?'::text))
-> Bitmap Index Scan on index_messages_on_context (cost=0.00..515.65 rows=29820 width=0)
Index Cond: (context @> '{"involved_parties": [{"id": 1, "type": 1}]}'::jsonb)(這個查詢計劃來自EXPLAIN，由於EXPLAIN ANALYZE超時)
慢查詢計劃的視覺化表示
如下圖可見，這個慢查詢計劃比之前的快查詢計劃更復雜。它多了一個”BitmapAnd”和一個掃描index 3的”Bitmap Index Scan”節點（index 3是context列上的GIN索引）。若index 3低效率，總體效能就會降低。
當成本估計準確時，查詢計劃器工作得很好。但是JSONB上的GIN索引的成本估計是不準確的。由觀測可見，它認為這個索引的選擇度為0.001（這是一個硬編碼的固定值），也就是說它假設任何相關的查詢都會選擇表中所有行的0.1%，但在我們這個場景它實際會選擇90%的行，所以這個假設不成立。錯誤的假設使查詢計劃器低估了慢查詢計劃的成本。雖然JSONB型別的列也有一些統計資訊，但好像沒有起到作用。
解決方案
我們不能刪除這個GIN索引，由於其他查詢依賴它。但是，有一個簡單的解決方案能避免這個查詢用到GIN索引。一般地，若一個條件的操作符左側不是簡單的列名而是表示式，那麼就不會用索引，除非有一個對應的表示式索引。例如，若對id列建有索引，條件”id = ?”就會使用索引，而條件”id+0 = ?”就不會使用索引。在我們的場景裡，@>操作符的左側是context 列，因此可以將其改為路徑訪問表示式”context->’involved_parties’”（引數值也相應地更新）。
原始的查詢條件是
context @> ‘{“involved_parties”:[{“id”:1,“type”:1}]}’::jsonb
修改後的查詢條件是
context->’involved_parties’ @> ‘[{“id”:1,”type”:1}]’::jsonb
實驗
我們可以用一個簡單的例子來測試這個有問題的條件。這個實驗能穩定重現兩種條件的不同效果，讓我們有信心把這個優化推上線。
設計兩個簡單的查詢語句：
優化前的語句1
SELECT * FROM messages WHERE context @> ‘{“involved_parties”:[{“id”:1,”type”:1}]}’::jsonb LIMIT 100優化後的語句2
SELECT * FROM messages WHERE context->”involved_parties” @> ‘[{“id”:1,”type”:1}]’::jsonb LIMIT 100
因為語句2只做表掃描而不做索引掃描，所以用LIMIT 100來確保它在找到100個滿足條件的行時就停下，而不用掃描整個表。而在語句1之中，GIN索引掃描總是無序的bitmap index scan，必須掃描整個索引而無法利用LIMIT 100。注意像10這樣的LIMIT值會低於某個閾值從而只使用表掃描，只有高於這個閾值才會啟用索引掃描，所以要用像100這樣的較大LIMIT值（這個閾值取決於成本估計，據我們測試可能是20）。
得到的查詢計劃為：
QUERY PLAN ------------------------------------------------------------------------------ Limit (cost=2027.11..2399.74 rows=100 width=915) (actual time=6489.987..6490.102 rows=100 loops=1) -> Bitmap Heap Scan on messages (cost=2027.11..113145.85 rows=29820 width=915) (actual time=6489.986..6490.093 rows=100 loops=1) Recheck Cond: (context @> '{"involved_parties": [{"id": 1, "type": 1}]}'::jsonb) Heap Blocks: lossy=10 -> Bitmap Index Scan on index_messages_on_context (cost=0.00..2019.65 rows=29820 width=0) (actual time=6477.838..6477.839 rows="millions(confidential number)" loops=1) Index Cond: (context @> '{"involved_parties": [{"id": 1, "type": 1}]}'::jsonb) Planning time: 0.076 ms Execution time: 6490.920 msQUERY PLAN ------------------------------------------------------------------------------ Limit (cost=0.00..13700.25 rows=100 width=915) (actual time=0.013..0.114 rows=100 loops=1) -> Seq Scan on messages (cost=0.00..4085414.08 rows=29820 width=915) (actual time=0.013..0.106 rows=100 loops=1) Filter: ((context -> 'involved_parties'::text) @> '[{"id": 1, "type": 1}]'::jsonb) Planning time: 0.058 ms Execution time: 0.135 ms
對這種情況，優化後的查詢快了5萬倍。
生產環境的真實改善
以下圖表展示了在生產環境上線後的顯著改善。
每天的API請求數差不多 (每6小時時間窗有5~10k個請求)
12月19日（Dec 19）以來，錯誤顯著減少
12月19日（Dec 19）以來，延遲顯著減少
一些有用的原則
我們總結了資料庫效能的一些原則（尤其適用於PostgreSQL）。
原則1: 少即是多
管理好索引
更多的索引並不意味著更好的效能。事實上，每增加一個索引都會降低寫操作的效能。如果查詢計劃器選擇了不高效的索引，那麼查詢仍然會很慢。
不要堆積索引（例如每一列都建索引就是不可取的）。試著儘可能刪除一些索引吧。而且每改動一個索引都要監控其對效能的影響。
優選簡單的資料庫設計
RDBMS（關係型資料庫系統）中的資料一般都宜用正規化化設計。JSON或JSONB則是NoSQL風格的反正規化化設計。
正規化化和反正規化化哪個更好呢？從業務的角度，要具體情況具體分析。從RDBMS的角度，正規化化總是更簡單更好，而反正規化化則可以在某些情況作為補充。
建議1：考慮從DDD（領域驅動設計）的角度來設計資料模型。
- 實體總是可以建模為表，值物件總是可以嵌入儲存在實體中（而有時為了效能，大型值物件也可以建模為表）。
- 某個關聯的目標實體若為聚合根，就一定不能嵌入儲存在別處（而要自成一表）。但如果關聯的目標實體不是聚合根，並且關聯的源實體是自包含的聚合根，那麼目標實體就可以被嵌入儲存。
建議2: 現代RDBMS中的可空列(nullable column)很高效，不用過於擔心效能，如果多個可空列是對於可選屬性(optional attribute)最簡明的建模方式，就不要猶豫了，更別把JSONB當作對可空列的“優化”方式。
原則2: 統計資訊要準確
PostgreSQL維護每一張表的統計資訊，包括而不限於元組數(tuple number)，頁數(page number)，最常見的值(most common values)，柱狀圖界限(histogram bounds)和不同值的個數(number of distinct values, 可能相當於集的基數set cardinality)。有一些統計資訊是取樣得到的而不夠準確。查詢計劃器會對查詢生成多個可能的計劃，根據統計資訊和規則來估計成本，再選擇最高效的那個計劃。查詢計劃的質量取決於統計資料的準確性。準確的資料帶來優秀的執行（這也是資料科學和資料驅動業務的一個好原則）。
正如所提到的，JSONB上的GIN的成本估計是不準確的。而標量型別上的BTREE的成本估計則準確得多。因此JSONB不適合某些情況。為了追求效率，作為變通方法，可以對JSONB的某個標量型別屬性建一個BTREE表示式索引。來自ScaleGrid的這片文章很好地介紹了怎樣高效使用JSONB和GIN。
建議：PostgreSQL有一些特性，如表示式索引和部分索引都是強大而有成本效益的。只要基於資料分析認為有效益，都值得選用之。
原則3: 提高可觀察性
無論我們是否對問題的潛在根因有推測，提高可觀察性都是最好的做法。查詢日誌能證明導致慢請求的是慢查詢，而不是應用程式程式碼或連線等待。自動EXPLAIN能捕獲慢查詢所用的真實的查詢計劃。
像Datadog這樣的APM（應用程式效能管理）也是一個重要的工具。它能提供很多洞察：
- 這個問題是由資源不足所致嗎？不，資源不足應平等影響任何SQL CRUD語句，但我們只觀察到慢的SELECT。
- 這個問題發生於每天的同一時間嗎？不，它能發生於任何時間。
- 每一次發生是獨立事件嗎？不，會在某個小的時間窗聚集發生多個事件。那時一定是發生了什麼事才導致這個問題。
致謝
感謝參與評閱的Vinod Kumar, Dylan Irlbeck, David Goussev, BJ Terry, Kyle Kinsey和其他Flexport同事。
參考
Understanding Postgres GIN Indexes: The Good and the Bad
Postgres Planner not using GIN index Occasionally
Gitlab once faced a GIN related issue
Understanding Postgres query planner behaviour on GIN index
Statistics used by the query planner
When To Avoid JSONB In A PostgreSQL Schema
Using JSONB in PostgreSQL: How to Effectively Store & Index JSON Data in PostgreSQL