多字段_任意组合条件查询(0建模)_-_毫秒级实时圈人_实践

阅读原文请点击：http://click.aliyun.com/m/22881/
摘要： 标签 PostgreSQL , 数组 , GIN索引 , 任意字段组合查询 , 圈人 , ToB分析型业务 , 建模 背景 你也许在一家ToB的数据分析公司，你可能设计了一张表（包括用户标识，及若干已经统计好的的属性值），你也许收集了一些用户的数据，你也许要为客户提供报表，你也许需要为客户提供任意属性值的组合查询，并快速的返回结果给用户。

标签

PostgreSQL , 数组 , GIN索引 , 任意字段组合查询 , 圈人 , ToB分析型业务 , 建模

背景

你也许在一家ToB的数据分析公司，你可能设计了一张表（包括用户标识，及若干已经统计好的的属性值），你也许收集了一些用户的数据，你也许要为客户提供报表，你也许需要为客户提供任意属性值的组合查询，并快速的返回结果给用户。

这些需求应该是非常常见的ToB的数据平台公司的形态，头痛的问题无法建模，因为B端的需求无法捉摸，任意组合查询、要求实时响应。

你的客户数据也许有几十亿上百亿，客户数据也许有几百个属性，用户可能需要的是任意属性组合的结果。

如果要快速响应，你的第一反应是不是对查询条件建立索引呢？

比如

where col1=? and col2=? and col3<>? or col4=?; 
这样的SQL，你准备怎么做到实时响应呢？(col1,col2)建立索引，col4建立索引，这样是吗？

但是用户下次的请求肯又换条件了

where col3=1 or col100=? 
是不是又要建col3, col100的索引呢？

你会发现根本没有办法优化，因为对应查询的索引组合可能是成千上万的。

PostgreSQL 对付任意字段检索的黑科技

我在之前写过一些关于任意字段查询的实践文章，广泛应用于广告营销平台的圈人，ToB的圈人，前端页面的任意组合筛选等场景。

方法1，GIN复合索引

对需要参与查询的字段，建立GIN的复合索引。

pic

CASE如下：

《任意组合字段等效查询, 探探PostgreSQL多列展开式B树 (GIN)》

这个场景针对任意字段匹配的场景，PostgreSQL对于多个查询条件，内部会使用索引+bitmapAnd或bitmapOr来筛选BLOCK，得到中间结果。

+---------------------------------------------+   
|100000000001000000010000000000000111100000000| bitmap 1   
|000001000001000100010000000001000010000000010| bitmap 2   
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&   
|000000000001000000010000000000000010000000000| Combined bitmap   
+-----------+-------+--------------+----------+   
            |       |              |   
            v       v              v   
Used to scan the heap only for matching pages:   
+---------------------------------------------+   
|___________X_______X______________X__________|   
+---------------------------------------------+   
这种方法为什么快呢？

原因是GIN索引实现了内部bitmapAnd or bitmapOr，实际上等效于对每个字段建立单独的B-Tree索引（PostgreSQL对多个B-Tree索引也支持bitmapAnd, bitmapOr的合并）。

bitmapand,or原理如下：

《PostgreSQL bitmapAnd, bitmapOr, bitmap index scan, bitmap heap scan》

GIN的复合索引这种方法可以满足以上需求，但是，当数据量非常庞大或者列非常多时，GIN索引会比较大。

方法1 优化技巧

建议可以拆成多张表（例如随机拆分，或者按强制条件拆分）。降低GIN索引的大小，同时还可以利用PostgreSQL 10的多表并行特性，提升查询性能。

PostgreSQL并行计算特性

PostgreSQL支持单表多核并行，也支持多表的并行查询

单表并行，指一条SQL，在处理单张表的数据时，可以使用多个CPU进行运算。

多表并行，指的是一条SQL涉及到多张表的处理（例如APPEND SCAN）时，可以并行的处理多个表的SCAN。

多表并行是在PG 10版本加入的，PostgreSQL 10 append scan 并行

《PostgreSQL 10.0 preview sharding增强 - 支持Append节点并行》
阅读原文请点击：http://click.aliyun.com/m/22881/