之前在搞宜搭後設資料底層索引最佳化的時候，針對一些查詢時快時慢，以及一些索引選擇的問題，研究過，也基於看過的一些案例以及自身歸納思考，下面整理分享下；
一般我們為了加快查詢速度，會設計索引，當然有索引情況下，大多是會命中去走索引查詢；但是呢:
- 儲存最佳化器去執行，就算加了索引，在一定時候有可能沒用到索引，速度會更慢點，這是為什麼不用？
- 有些時候同一個使用者不同時間去請求，產生相同SQL語句去查詢也可能出現不同的快慢效能，這又是為什麼？
- 就算命中了索引，速度可能更慢，這最後又是為什麼？
案例一
-- 建立測試表
CREATE TABLE `t` (
`id` int primary key auto_increment,
`a` int default null,
`b` int default null,
KEY `a` (`a`),
KEY `b` (`b`)
) ENGINE=InnoDB;
-- 插入10w行測試資料
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=100000)do
insert into t values(i, i, i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();mysql全表掃描
explain select from t where a between 10000 and 20000;
透過explain的執行結果我們可以看出，上面的SQL語句並沒有走我們的索引a，而是直接使用了全表掃描。
-- 強制走索引a explain select from t force index(a) where a between 10000 and 20000;
透過explain的執行結果我們可以看出，上面的SQL語句我們透過force index(a)以後，確實使用了索引。
-- 開啟慢日誌 set global slow_query_log = true; set long_query_time = 0; -- 分別執行不走索引和走索引的SQL select from t where a between 10000 and 20000; select from t force index(a) where a between 10000 and 20000;
可以看出走索引的查詢比不走索引的查詢快了將近10ms。
但是儲存最佳化器預設沒走索引的查詢，雖然加了索引
案例二
針對某個平臺有張訊息傳送交流的表，規模達到數千萬行級，PG儲存；訊息表上的主查詢通常極快，但是也遇到了一些間歇的慢查詢超時。慢查詢不但影響了訊息功能的使用者體驗，而且加大了整個系統的負荷，拖慢了其他功能的使用者體驗。
這個查詢長這樣：
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在context上有兩個索引
- context列上的GIN索引
- jsonb_extract_path_text(context, ‘topic’)表示式上的BTREE表示式索引
看下上面語句偶爾慢的時候QUERY PLAN：
UERY 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超時)看下上面語句快的時候QUERY PLAN：
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圖中執行計劃可以看出，(context @> '{"involved_parties": [{"id": 1, "type": 1}]}'::jsonb) 沒走索引更快，走了索引還更慢； 而且同一個SQL查詢，有些時候走索引，有些時候又不走；
案例分析
這裡針對案例二進行分析:
瞭解下索引區別
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索引對於這個條件的選擇度很差。實際上，這個查詢中的其他條件已能提供很好的選擇度，所以永遠不需要為這個條件使用索引。
針對快慢查詢分析
慢查詢路徑 快查詢路徑
如圖可見，這個慢查詢計劃比快查詢計劃更復雜。它多了一個”BitmapAnd”和一個掃描index 3的”Bitmap Index Scan”節點（index 3是context列上的GIN索引）。若index 3低效率，總體效能就會降低。
當成本估計準確時，查詢計劃器工作得很好。但是JSONB上的GIN索引的成本估計不是很準確的。由觀測可見，它認為這個索引的選擇度為0.001（這是一個硬編碼的固定值），也就是說它假設任何相關的查詢都會選擇表中所有行的0.1%，但在我們這個場景它實際會選擇90%的行，所以這個假設不成立。錯誤的假設使查詢計劃器低估了慢查詢計劃的成本。雖然JSONB型別的列也有一些統計資訊，但好像沒有起到作用。
結論
所以說有些時命中索引不一定就快，而且索引最佳化器也不一定是準確的，可能會執行更慢；
一些有用的原則
原則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。
- 這個問題發生於每天的同一時間嗎？不，它能發生於任何時間。
每一次發生是獨立事件嗎？不，會在某個小的時間窗聚集發生多個事件。那時一定是發生了什麼事才導致這個問題。
一些最佳化的措施
針對宜搭本身，一些可能有參考價值的最佳化措施，當然這裡不涉及快取，主要講儲存層面的
宜搭作為釘釘上低程式碼開發平臺，上面承載著上百萬應用，例項資料總共達到幾十億規模；不同的應用會生長出不同的場景業務，不同的場景業務也會衍生出很多業務元件，比如單行文字元件，成本元件等； 這些元件對應儲存會有很多不同的索引去加速查詢；
調整SQL語句，使得之前特定元件沒走索引，充分走索引；比如下面gin語句的查詢
針對一些特定元件的查詢，最佳化了查詢語句，使得充分利用索引，在資料量大時候查詢更快；
正如上面案例說的，有了索引不一定快，沒索引可能更快，所以需要根據查詢場景控制並判斷；
最佳化前
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------
Limit (cost=4085.57..4086.38 rows=36 width=51) (actual time=100.118..100.121 rows=1 loops=1)
-> Gather Motion 128:1 (slice1; segments: 128) (cost=4085.57..4086.38 rows=36 width=51) (actual time=100.111..100.111 rows=1 loops=1)
Merge Key: gmt_create, pid
-> Limit (cost=4085.57..4085.66 rows=1 width=51) (actual time=52.273..52.276 rows=1 loops=1)
-> Sort (cost=4085.57..4085.66 rows=1 width=51) (actual time=52.271..52.273 rows=1 loops=1)
Sort Key: gmt_create, pid
Sort Method: top-N heapsort Memory: 4224kB
-> Index Scan Backward using idx_app_type_table_name_gmt_create on yida_entity_instance a (cost=0.20..4084.66 rows=1 width=5
1) (actual time=0.361..52.258 rows=1 loops=1)
Index Cond: (((app_type)::text = 'APP_GXUUGZJ1ZPPBIJKLE9BH'::text) AND ((model_uuid)::text = 'FORM-EX866CB1E6TV7F6SZME2Y
QA5IKWO1052BFOWKK'::text))
Filter: (is_deleted = 'n'::bpchar) AND (json_data -> 'employeeField_kw0b5hyf_code'::text) = '["050323"]'::jsonb)
Planning time: 0.254 ms
(slice0) Executor memory: 180K bytes.
(slice1) Executor memory: 188K bytes avg x 128 workers, 188K bytes max (seg0). Work_mem: 33K bytes max.
Memory used: 2047000kB
Optimizer: Postgres query optimizer
Execution time: 101.799 ms
(16 rows)最佳化後
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------
---------
Limit (cost=5.55..6.36 rows=36 width=51) (actual time=28.342..28.344 rows=1 loops=1)
-> Gather Motion 128:1 (slice1; segments: 128) (cost=5.55..6.36 rows=36 width=51) (actual time=28.339..28.339 rows=1 loops=1)
Merge Key: gmt_create, pid
-> Limit (cost=5.55..5.64 rows=1 width=51) (actual time=5.173..5.175 rows=1 loops=1)
-> Sort (cost=5.55..5.64 rows=1 width=51) (actual time=5.171..5.173 rows=1 loops=1)
Sort Key: gmt_create, pid
Sort Method: top-N heapsort Memory: 4224kB
-> Bitmap Heap Scan on yida_entity_instance a (cost=4.51..4.64 rows=1 width=51) (actual time=5.156..5.159 rows=1 loops=1)
Recheck Cond: (json_data @> '{"employeeField_kw0b5hyf_code": ["050323"]}'::jsonb)
Filter: ((is_deleted = 'n'::bpchar) AND ((app_type)::text = 'APP_GXUUGZJ1ZPPBIJKLE9BH'::text) AND ((model_uuid)::text =
'FORM-EX866CB1E6TV7F6SZME2YQA5IKWO1052BFOWKK'::text))
-> Bitmap Index Scan on idx_json_data_path (cost=0.00..4.50 rows=1 width=0) (actual time=5.129..5.129 rows=4 loops=1)
Index Cond: (json_data @> '{"employeeField_kw0b5hyf_code": ["050323"]}'::jsonb)
Planning time: 0.345 ms
(slice0) Executor memory: 157K bytes.
(slice1) Executor memory: 495K bytes avg x 128 workers, 576K bytes max (seg31). Work_mem: 33K bytes max.
Memory used: 2047000kB
Optimizer: Postgres query optimizer
Execution time: 30.130 ms
(18 rows)這裡主要調整了查詢SQL
把a.json_data->'employeeField_kw0b5hyf_code'='["050323"]' 換成json_data @> '{"employeeField_kw0b5hyf_code": ["050323"]}'::jsonb查詢，充分利用gin jsonb_path_ops的索引（相比gin jsob_ops索引更高效）；
當然上面在資料量稍微大一點效果更明顯，因為資料量太少會預設走表掃描，不走索引更快；
說明 在JSONB上建立GIN索引的方式有兩種：使用預設的jsonb_ops運算子建立和使用jsonb_path_ops運算子建立。兩者的區別在jsonb_ops的GIN索引中，JSONB資料中的每個key和value都是作為一個單獨的索引項的，而jsonb_path_ops則只為每個value建立一個索引項。
大家還記得最上面案例二嗎？ 上面調整後的SQL語句，如果是在案例二場景當中，調整後會更慢，所以需要具體場景具體分析；
針對儲存最佳化器的查詢效率情況，自行選擇最佳的掃描計劃方式
正如上面案例說的，最佳化器也不一定是準確的，所以有些時候需要我們程式碼最佳化器自行選擇最佳的掃描計劃方式
比如說：我們查詢場景中會有limit 1，limit 2，limit 5等這種查詢語句，用limit來確保它在找到n個滿足條件的行時就停下，而不用掃描整個表；
對於最佳化器來說，針對limit很小的數值，認為表掃描可能會更快，我們有些應用資料量很大，這種場景如果走表掃描，不走索引，效率會更慢（取決於掃描行數來命中）；這種情況我們查詢的時候，會針對性force index請求去強制走索引掃描計劃，提升速度；
後續針對每個SQL的代價效率統計，也可以自動選擇對應掃描計劃，也算是對最佳化器針對業務場景不同下不同代價執行的一個補充；這個步驟可以理解叫“探查執行”，在宜搭專屬大客戶場景下，後續會基於這個進行“探查執行”，以達到大資料量下查詢效率的最優解；
SQL Parse業務最佳化器，前置處理最佳化SQL查詢
這裡SQL Parse業務最佳化器，主要是針對我們業務上不合理待最佳化的SQL，算是前置攔截最佳化，與儲存的還是有點區別；目的是最佳化器的補充，讓儲存更加專注於基於代價和成本的最佳化（CBO，cost based optimization）上，讓最佳化器能更多的集中在理解計算進行執行計劃最佳化這件事情上。
宜搭本身有很多業務功能，這些業務功能對接底層後設資料引擎，來操作獲取資料；業務上具體選擇AST資料操作引數來呼叫後設資料引擎統一API；
後設資料底層獲取到對應的AST引數，解析後組裝SQL，這裡針對性生成的不合理SQL進行最佳化
- 沒有應用標識，底層會兜底上下文去取，如果沒有的話，會丟擲不合理的異常，拒絕不合理的SQL去查詢，減少查詢範圍到具體應用層面；
- limit沒傳的，組裝SQL會預設給個值；
- 針對select * 查詢語法，預設解析成select欄位
- 以及一些函式或者表示式的變換，比如日期函式大於等於值，命中不了索引，SQL最佳化；
- 數值介於查詢，明顯命中不了資料的，直接判斷攔截；
- 一些不合理多條件查詢，合併
- ...
最近團隊有一些hc，對後設資料或者低程式碼平臺感興趣的來試試；java研發，資料研發，技術專家等都可，歡迎來撩
簡歷發 edagarli.lz@alibaba-inc.com 或者微信私我hangzhoushoot
參考
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
https://zhuanlan.zhihu.com/p/523900025
https://www.cnblogs.com/flying-tiger/p/6702796.html
https://cloud.tencent.com/developer/article/1943819