高並發低基數多字段任意組合查詢的優化
1.問題
首先解釋一下這個標題裡出現的"低基數多字段任意組合查詢"指什麼東西。這裡是指滿足下面幾個條件的查詢：
1. 檢索條件中涉及多個字段條件的組合
2. 這些字段的組合是不確定的
3. 每個單獨字段的選擇性都不好
這種類型的查詢的使用場景很多，比如電商的商品展示頁面。用戶會輸入各種不同查詢條件組合：品類，供應商，品牌，促銷，價格等等...,最後往往還要對結果進行排序和分頁。
這類問題令人頭疼的地方在於：
1. 記錄數量眾多，如果進行全表掃描性能低下無法滿足高並發訪問的要求。
2. 查詢條件涉及的任何單個字段的選擇性都很低，不能通過單字段索引解決查詢效率問題。
3. 如果建立普通的Btree多字段索引，由於用戶的輸入條件組合太多，可能要建成百上千個索引，這不現實也很難維護。
2.方案
對這類問題我想到的解決方案有2種
2.1bitmap索引
bitmap的特點是存儲key以及所有取值等於這個key的行集的bitmap，對於涉及多個key的組合查詢，只需把這些key對應的bitmap做與或運算即可。由於bitmap的size很小，bit與或運算的效率也很高，所以bitmap非常適合做這類查詢。
bitmap索引也有缺點，更新一條記錄就會鎖住整個表，不適合並發寫比較多的場景。另外一個問題是，常見的關系數據庫中支持bitmap索引的似乎只有Oracle一家，而我們很多時候我們想用開源數據庫。
2.2 倒排索引
倒排索引和bitmap有相似之處，存儲的是key和取值等於這個key的行集，行集可能是list也可能是tree或其它存儲形式。對於多個key的組合查詢，把這些key的結果做集合運算即可。
倒排索引一般用於全文檢索，但很多系統也用它支持結構化數據的搜索，比如Elasticsearch。Elasticsearch支持JSON文檔的快速搜索，支持復合查詢，排序，聚合，分布式部署等很多不錯的特性。但是考慮下面幾個因素，我們更希望在關系數據庫裡找方案。
-不需要使用搜索引擎為模糊匹配提供的高級特性，實際上我們需要是精確匹配或者簡單的模糊匹配。
-數據量還沒有大到需要建一個分布式搜索集群。
-原始數據本來就在關系數據庫裡，不想煩心數據同步的問題。
-已經基於關系數據庫的接口開發了應用，不想推倒重來。
-已經掌握了關系數據庫的運維管理，對於全新的系統不知道還要踩多少坑。
-考慮到Java和C效能差異，關系數據庫內建方案的性能未必輸與專業的搜索引擎。
3.PostgreSQL的解法
如果把解決方案的范圍限定在開源關系數據庫，答案可能只有一個，就是PostgreSQL的gin索引。
PostgreSQL的gin索引就是倒排索引，它不僅被用於全文檢索還可以用在常規的數據類型上，比如int,varchar。
對於多維查詢我們可以這樣建索引：
1. 對所有等值條件涉及的低基數字段，建立唯一一個多字段gin索引
2. 對選擇性比較好的等值查詢或范圍查詢涉及的字段，另外建btree索引
可能有同學會有疑問，同樣是多字段索引，為什麼gin的多字段索引只要建一個就可以了，而btree的多字段索引卻要考慮各種查詢組合建若干個。這是由於gin多字段索引中的每個字段是等價的，不存在前導字段的說法，所以只要建一個唯一的gin多字段索引就可以覆蓋所有的查詢組合；而btree多字段索引則不同，如果查詢條件中不包含suoyi前導字段，是無法利用索引的。
多字段gin索引的內部存儲的每個鍵是(column number,key datum)這樣的形式，所以可以區分不同的字段而不致混淆。存儲的值是匹配key的所有記錄的ctid集合。這個集合在記錄數比較多的情況下采用btree的形式存儲,並且經過了壓縮，所以gin索引占用的存儲空間很小，大約只有等價的btree索引的二十分之一，這也從另一方面提升了性能。
對於多維查詢涉及的多個字段，包含在多字段gin索引中的字段，由gin索引做ctid的集合歸並(取並集或交集)，然後得到的ctid集合和其它索引得到的ctid集合再做BitmapAnd或BitmapOr歸並。gin索引內部的ctid集合歸並效率遠高於索引間的ctid集合歸並，而且gin索引對低基數字段的優化更好，所以充分利用gin索引的特性比為每個字段單獨建一個btree索引再通過BitmapAnd或BitmapOr歸並結果集效率高的多。
4.一個真實的案例
4.1 原始查詢
下面這個SQL是某系統中一個真實SQL的簡化版。
- SELECT CASE WHEN gpppur.GB_BEGINDATE <= '2016-02-29 14:36:00' AND gpppur.GB_ENDDATE > '2016-02-29 14:36:00' THEN 1
- WHEN gpppur.PREVIEW_BEGINDT <= '2016-02-29 14:36:00' AND gpppur.PREVIEW_ENDDT > '2016-02-29 14:36:00' THEN 2
- ELSE 3 END AS flag,
- gpppur.*
- FROM T_MPS_INFO gpppur
- WHERE gpppur.ATTRACT_TP = 0
- AND gpppur.COLUMN_ID = 1
- AND gpppur.FIELD2 = 1
- AND gpppur.STATUS = 1
- ORDER BY flag ASC,gpppur.PC_SORT_NUM ASC,gpppur.GB_BEGINDATE DESC
- LIMIT 0,45
用的是MySQL數據庫，總數據量是60w，其中建有FIELD2+STATUS的多字段索引。
查詢條件涉及的4個字段的值分布情況如下：
- postgres=# select ATTRACT_TP,count(*) from T_MPS_INFO group by ATTRACT_TP;
- attract_tp | count
- ------------+--------
- | 16196
- 6 | 251
- 2 | 50
- 1 | 3692
- 3 | 143
- 10 | 314
- 4 | 214
- 5 | 194333
- 9 | 326485
- 7 | 1029
- 0 | 6458
- (11 rows)
- postgres=# select COLUMN_ID,count(*) from T_MPS_INFO group by COLUMN_ID;
- column_id | count
- ------------+--------
- | 2557
- 285 | 20
- 120 | 194
- 351 | 2
- 337 | 79
- 227 | 26
- 311 | 9
- 347 | 2
- 228 | 21
- 318 | 1
- 314 | 9
- 54 | 10
- 133 | 27
- 2147483647 | 1
- 336 | 1056
- 364 | 1
- 131 | 10
- 243 | 5
- 115 | 393
- 61 | 73
- 226 | 40
- 196 | 16
- 350 | 5
- 373 | 72
- 377 | 2
- 260 | 4
- 184 | 181
- 363 | 1
- 341 | 392
- 64 | 1
- 344 | 199271
- 235 | 17
- 294 | 755
- 352 | 3
- 368 | 1
- 225 | 1
- 199 | 8
- 374 | 2
- 248 | 8
- 84 | 1
- 362 | 1
- 361 | 331979
- 319 | 7
- 244 | 65
- 125 | 2
- 130 | 1
- 272 | 65
- 66 | 2
- 240 | 2
- 775 | 1
- 253 | 49
- 60 | 45
- 121 | 5
- 257 | 3
- 365 | 1
- 0 | 1
- 217 | 5
- 270 | 1
- 122 | 39
- 56 | 49
- 355 | 5
- 161 | 1
- 329 | 1
- 222 | 9
- 261 | 275
- 2 | 3816
- 57 | 19
- 307 | 4
- 310 | 8
- 97 | 37
- 202 | 20
- 203 | 3
- 85 | 1
- 375 | 641
- 58 | 98
- 1 | 6479
- 59 | 114
- 185 | 7
- 338 | 10
- 379 | 17
- (80 rows)
- postgres=# select FIELD2,count(*) from T_MPS_INFO group by FIELD2;
- field2 | count
- --------+--------
- | 2297
- 6 | 469
- 2 | 320
- 1 | 11452
- 3 | 286
- 10 | 394
- 4 | 291
- 5 | 200497
- 9 | 331979
- 0 | 2
- 7 | 1178
- (11 rows)
- postgres=# select STATUS,count(*) from T_MPS_INFO group by STATUS;
- status | count
- --------+--------
- | 2297
- 0 | 15002
- 3 | 5
- 4 | 1
- 1 | 531829
- 2 | 31
- (6 rows)
由於這幾個字段的值分布極其不均的，我們構造下面這個lua腳本產生不同的select語句來模擬負載。
qx.lua:
- pathtest = string.match(test, "(.*/)") or ""
- dofile(pathtest .. "common.lua")
- function thread_init(thread_id)
- set_vars()
- end
- function event(thread_id)
- local ATTRACT_TP,COLUMN_ID,FIELD2,STATUS
- ATTRACT_TP = sb_rand_uniform(0, 10)
- COLUMN_ID = sb_rand_uniform(1, 100)
- FIELD2 = sb_rand_uniform(0, 10)
- STATUS = sb_rand_uniform(0, 4)
- rs = db_query("SELECT CASE WHEN gpppur.GB_BEGINDATE <= '2016-02-29 14:36:00' AND gpppur.GB_ENDDATE > '2016-02-29 14:36:00' THEN 1
- WHEN gpppur.PREVIEW_BEGINDT <= '2016-02-29 14:36:00' AND gpppur.PREVIEW_ENDDT > '2016-02-29 14:36:00' THEN 2
- ELSE 3 END AS flag,
- gpppur.*
- FROM T_MPS_INFO gpppur
- WHERE gpppur.ATTRACT_TP = "..ATTRACT_TP.."
- AND gpppur.COLUMN_ID = "..COLUMN_ID.."
- AND gpppur.FIELD2 = "..FIELD2.."
- AND gpppur.STATUS = "..STATUS.."
- ORDER BY flag ASC,gpppur.PC_SORT_NUM ASC,gpppur.GB_BEGINDATE DESC
- LIMIT 45")
- end
然後用sysbench進行壓測，結果在32並發時測得的qps是64。
- [root@rh6375Gt20150507 ~]# sysbench --db-driver=mysql --test=/opt/sysbench-0.5/sysbench/tests/db/qx.lua --mysql-db=test --mysql-user=mysql --mysql-password=mysql --mysql-host=srdsdevapp69 --num-threads=32 --max-time=5 run
- sysbench 0.5: multi-threaded system evaluation benchmark
- Running the test with following options:
- Number of threads: 32
- Random number generator seed is 0 and will be ignored
- Threads started!
- OLTP test statistics:
- queries performed:
- read: 825
- write: 0
- other: 0
- total: 825
- transactions: 0 (0.00 per sec.)
- read/write requests: 825 (64.20 per sec.)
- other operations: 0 (0.00 per sec.)
- ignored errors: 0 (0.00 per sec.)
- reconnects: 0 (0.00 per sec.)
- General statistics:
- total time: 12.8496s
- total number of events: 825
- total time taken by event execution: 399.6003s
- response time:
- min: 1.01ms
- avg: 484.36ms
- max: 12602.74ms
- approx. 95 percentile: 222.79ms
- Threads fairness:
- events (avg/stddev): 25.7812/24.12
- execution time (avg/stddev): 12.4875/0.23
4.2 優化後的查詢
對於上面那個特定的SQL雖然我們可以通過建一個包含所有等值查詢條件中4個字段(ATTRACT_TP,COLUMN_ID,FIELD2,STATUS)的組合索引進行優化，但是需要說明的是，這條SQL只是各種查詢組合產生的1000多種不同SQL中的一個，每個SQL涉及的查詢字段的組合是不一樣的，我們不可能為每種組合都單獨建一個多字段索引。
所以我們想到了PostgreSQL的gin索引。為了使用PostgreSQL的gin索引，先把MySQL的表定義，索引和數據原封不動的遷移到PostgreSQL。
在添加gin索引前，先做了一個測試。另人驚訝的是，還沒有開始進行優化，PostgreSQL測出的性能已經是MySQL的5倍(335/64=5)了。
- [root@rh6375Gt20150507 ~]# sysbench --db-driver=pgsql --test=/opt/sysbench-0.5/sysbench/tests/db/qx.lua --pgsql-db=postgres --pgsql-user=postgres --pgsql-password=postgres --pgsql-host=srdsdevapp69 --num-threads=32 --max-time=5 run
- sysbench 0.5: multi-threaded system evaluation benchmark
- Running the test with following options:
- Number of threads: 32
- Random number generator seed is 0 and will be ignored
- Threads
- OLTP test statistics:
- queries performed:
- read: 1948
- write: 0
- other: 0
- total: 1948
- transactions: 0 (0.00 per sec.)
- read/write requests: 1948 (335.52 per sec.)
- other operations: 0 (0.00 per sec.)
- ignored errors: 0 (0.00 per sec.)
- reconnects: 0 (0.00 per sec.)
- General statistics:
- total time: 5.8059s
- total number of events: 1948
- total time taken by event execution: 172.0538s
- response time:
- min: 0.90ms
- avg: 88.32ms
- max: 2885.69ms
- approx. 95 percentile: 80.01ms
- Threads fairness:
- events (avg/stddev): 60.8750/27.85
- execution time (avg/stddev): 5.3767/0.29
下一步，添加gin索引。
- postgres=# create extension btree_gin;
- CREATE EXTENSION
- postgres=# create index idx3 on t_mps_info using gin(attract_tp, column_id, field2, status);
- CREATE INDEX
再進行壓測，測出的qps是5412，是MySQL的85倍(5412/64=85)。
- [root@rh6375Gt20150507 ~]# sysbench --db-driver=pgsql --test=/opt/sysbench-0.5/sysbench/tests/db/qx.lua --pgsql-db=postgres --pgsql-user=postgres --pgsql-password=postgres --pgsql-host=srdsdevapp69 --num-threads=32 --max-time=5 run
- sysbench 0.5: multi-threaded system evaluation benchmark
- Running the test with following options:
- Number of threads: 32
- Random number generator seed is 0 and will be ignored
- Threads
- OLTP test statistics:
- queries performed:
- read: 10000
- write: 0
- other: 0
- total: 10000
- transactions: 0 (0.00 per sec.)
- read/write requests: 10000 (5412.80 per sec.)
- other operations: 0 (0.00 per sec.)
- ignored errors: 0 (0.00 per sec.)
- reconnects: 0 (0.00 per sec.)
- General statistics:
- total time: 1.8475s
- total number of events: 10000
- total time taken by event execution: 58.2706s
- response time:
- min: 0.95ms
- avg: 5.83ms
- max: 68.36ms
- approx. 95 percentile: 9.42ms
- Threads fairness:
- events (avg/stddev): 312.5000/47.80
- execution time (avg/stddev): 1.8210/0.02
4.3 補充
作為對比，我們又在MySQL上添加了包含attract_tp, column_id, field2和status這4個字段的多字段索引，測出的qps是4000多，仍然不如PostgreSQL。可見業界廣為流傳的MySQL的簡單查詢性能優於PostgreSQL的說法不可信！(對於復雜查詢PostgreSQL的性能大大優於MySQL應該是大家的共識。我例子中的SQL不能算是復雜查詢吧？)
5. 總結
gin索引(還包括類似的gist,spgist索引)是PostgreSQL的一大特色，基於它可以挖掘出很多好玩的用法。對於本文提到的場景，有興趣的同學可以把它和Oracle的bitmap索引以及基於搜索引擎(Elasticsearch，Solr等)的方案做個對比。另外，本人所知有限，如果有其它更好的方案，希望能讓我知道。
