多字段_任意组合条件查询(0建模)_-_毫秒级实时圈人_实践 postgresqlsqlMySpace360网页游戏
程序员文章站
2022-07-10 22:13:25
...
阅读原文请点击：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/
摘要： 标签 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/