网友通过本文主要向大家介绍了字段组合,sql 字段组合,数据透视表字段组合,crm 字段自定义,报表字段自定义显示等相关知识,希望对您有所帮助,也希望大家支持linkedu.com www.linkedu.com
高并发低基数多字段任意组合查询的优化
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
查询条件涉及的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: