阅读原文请点击:
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/
分享到:
相关推荐
通过opc协议读取dcs数据并保存到表格中,每天保存一个表格
ARCGIS中土地利用代码赋值,更改部分参数,即可将此代码应用于if语句赋值的各种情景。
用途 内存表格数据查询 游戏触发器条件查询 成就表: 定义成就类型, 事件类型, 玩家等级等静态表格数据 通过本...多字段任意组合查询 支持构建字段搜索索引, 提高不等匹配(!=, <,>...)查询性能, 从O(N*M)降低到O(1)
MySQL高级特性字段约束-索引-视图-外键学习实践PDF文档;
arcgis elasticSearch es 矢量数据导入插件 数据建模 mapping indexsetting 字段映射 索引建模支持geoshape、shape,text索引支持keyword/ngram/edgeNgram/ikSmart,快速导入,兼容multipolygon,带洞,多面,使用时...
2、MySQL字段数据类型和长度.sql 3、usefullSQL.sql 4、编码.txt usefullSQL包含有: 字符串:求长度,定位,截取,拼接 日期操作:求差,加减,UTC时间,时区(timezone)转换等 查看表空间大小
主要介绍了Thinkphp使用mongodb数据库实现多条件查询方法,修改了mongodb驱动文件来实现复合查询、多条件查询,需要的朋友可以参考下
从Kaggle上下载的电信客户流失数据集,共7043条记录,21个字段,可以用作数据分析入门数据集。
Hibernate_query查询数据表中的一个字段.
Access数据库-组合框行来源SQL-提取部分字段列表
易语言多条件组合查询模块源码,多条件组合查询模块,ACCESS组合查询,添加条件,字段组合框加数据,比较符组合框加数据,查询,SQL组合查询
C#ArcObject_DwgAnnoToShp根据相交位置设置字段值
项目中遇到一个需求需要修改SO的抬头增强字段,通常增强字段只能通过BAPI的EXTENSIONIN和EXTENSIONINX参数传递。
java 静态_非静态 字段_方法_代码块 子类父类构造_初始化顺序! 三个class 让你清清楚楚 第一个class java代码如下: package initialOrder; class Parent { // 静态变量 public static String p_StaticField...
千年服务端TGS所有代码字段详解。包含CreateGate.sdb文件各字段含义详解。Iitem.sdb文件各字段含义详解。ItemDrug.sdb文件各字段含义详解 等全部解释。
------------------------/组函数(共5个):将多个条件组合到一起最后只产生一个数据------min() max() avg() sum() count()----------------------------/ 51、select count(*) from emp; --求出表中一共有多少条...
<P><P>原理:在窗体启动时把需要查询的表中的所有字段名读取到一个组合框中,设定两个变量来保存查询用的SQL语句及显示给用户看的SQL语句,每点击一次添加条件,就在SQL语句中加一个条件,最后用该SQL语句做为查询...
sql语句:按照某一个字段进行去重后获取全部字段。
查询模板初始化(对报表对应的功能模板编码进行条件设置) -动态建模平台-开发配置-模板管理-查询模板初始化 (具体查询条件来源、配置、需要用友技术支) 分配报表权限给相关人员、角色、职能等。
Elasticsearch的source字段详解,带图说明;总结性的文档。