xml地图|网站地图|网站标签 [设为首页] [加入收藏]

热门视频

当前位置:美高梅游戏网站 > 热门视频 > 介绍PostgreSQL中的jsonb数据类型_数据库其它_脚本之

介绍PostgreSQL中的jsonb数据类型_数据库其它_脚本之

来源:http://www.gd-chuangmei.com 作者:美高梅游戏网站 时间:2019-11-26 15:05

PostgreSQL 9.4 正在加载一项新功能叫jsonb,是一种新型资料,可以储存支援GIN索引的JSON 资料。换言之,此功能,在即将来临的更新中最重要的是,如果连这都不重要的话,那就把Postgres 置于文件为本数据库系统的推荐位置吧。

PostgreSQL 9.3 用了一种新的联合类型! Lateral联合的推出比较低调,但它实现了之前需要使用编写程序才能获得的强大的新查询. 在本文中, 我将会介绍一个在 PostgreSQL 9.2 不可能被实现的渠道转换分析.什么是 LATERAL 联合?

自从9.2开始,一个整合JSON 资料类型已经存在,带有一整套功能,还有9.3新增的操作者。当使用JSON 资料类型,资料的被存储成一完全一样的副本,功能还在此之上运作,还另外需要后台运作的重新分析。

对此的最佳描述在文档中 可选 FROM 语句清单 的底部:

这心得JSONB 资料类型以已降解的2元格式存储,所以,插入此资料会比JSON高效,因为后台不再需要重新分析,因此让它更快速运行,而且还兼顾GIN 索引。就是因为最后这个原因,我们实际上建议读者使用jsonb来代替json制作程式。请记住jsonb使用相同的操作者和功能,读者们可以看我之前的帖子去令你得到些什么启发。

LATERAL 关键词可以在前缀一个 SELECT FROM 子项. 这能让 SELECT 子项在FROM项出现之前就引用到FROM项中的列. (没有 LATERAL 的话, 每一个 SELECT 子项彼此都是独立的,因此不能够对其它的 FROM 项进行交叉引用.)…当一个 FROM 项包含 LATERAL 交叉引用的时候,查询的计算过程如下: 对于FROM像提供给交叉引用列的每一行,或者多个FROM像提供给引用列的行的集合, LATERAL 项都会使用行或者行的集合的列值来进行计算. 计算出来的结果集像往常一样被加入到联合查询之中. 这一过程会在列的来源表的行或者行的集合上重复进行.

现在让我们看一下JSONB是如何工作的,同时和JSON比较一下。采用的测试数据是860万的geobase类型数据,大概1.1G大小,包括了城市名,国家代码等很多字段。首先通过底层复制来把这些数据存储到数据库的一个新表里面,之后把这张表通过一组填充因子是100的表转换成JSON/JSONB,之后来看它们各占多少空间。

这种计算有一点密集。你可以比较松散的将 LATERAL 联合理解作一个 SQL 的foreach 选择, 在这个循环中 PostgreSQL 将循环一个结果集中的每一行,并将那一行作为参数来执行一次子查询的计算.

=# COPY geodata FROM '$HOME/Downloads/allCountries.txt';COPY 8647839=# CREATE TABLE geodata_jsonb  with ;CREATE TABLE=# CREATE TABLE geodata_json  with ;CREATE TABLE=# timingTiming is on.=# INSERT INTO geodata_json SELECT row_to_json FROM geodata;INSERT 0 8647839Time: 287158.457 ms=# INSERT INTO geodata_jsonb SELECT row_to_json::jsonb FROM geodata;INSERT 0 8647839Time: 425825.967 ms

我们可以用这个来干些什么?

生成JSONB数据花费稍微长一点时间,大小有没有区别呢?

看看下面这个用来记录点击事件的表结构:

=# SELECT pg_size_pretty(pg_relation_size('geodata_json'::regclass)) AS json, pg_size_pretty(pg_relation_size('geodata_jsonb'::regclass)) AS jsonb; json | jsonb ---------+--------- 3274 MB | 3816 MB
CREATE TABLE event ( user_id BIGINT, event_id BIGINT, time BIGINT NOT NULL, data JSON NOT NULL, PRIMARY KEY 

在JSON数据上面做索引从9.3版本开始,比如用操作符(注意 因为它返回文本,所以'->>'被采用;并且根据查询不同,索引采用不同的关键字)

每一个事件都关联了一个用户,拥有一个ID,一个时间戳,还有一个带有事件属性的JSON blob. 在堆中,这些属性可能包含一次点击的DOM层级, 窗口的标题,会话引用等等信息.

=# CREATE INDEX geodata_index ON geodata_json ((data->>'country_code'), ;CREATE INDEX=# SELECT pg_size_pretty(pg_relation_size('geodata_index'::regclass)) AS json_index; json_index ------------ 310 MB=# SELECT ::int as population, data->'latitude' as latitude, data->'longitude' as longitude FROM geodata_json WHERE data->>'country_code' = 'JP' AND data->>'asciiname' = 'Tokyo' AND ::int != 0; population | latitude | longitude ------------+----------+----------- 8336599 | 35.6895 | 139.69171=# -- Explain of previous query QUERY PLAN ------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on geodata_json (cost=6.78..865.24 rows=215 width=32) Recheck Cond: (((data ->> 'country_code'::text) = 'JP'::text) AND ((data ->> 'asciiname'::text) = 'Tokyo'::text)) Filter: (((data ->> 'population'::text))::integer <> 0) -> Bitmap Index Scan on geodata_index (cost=0.00..6.72 rows=216 width=0) Index Cond: (((data ->> 'country_code'::text) = 'JP'::text) AND ((data ->> 'asciiname'::text) = 'Tokyo'::text)) Planning time: 0.172 ms

加入我们要优化我们的登录页面以增加注册. 第一步就是要计算看看我们的哪个渠道转换上正在丢失用户.

在这个例子里,计划可以使用bitmap索引扫描,同时使用了之前产生的索引。

示例:一个注册流程的个步骤之间的渠道转换率.

现在,JSONB的一个新特点就是检查包含带有操作符@>的数据容量,这种数据是可以用GIN来索引的,这种操作符数据也包括了?,?|和?&。 GIN索引对两类操作符起作用:

假设我们已经在前端配备的装置,来沿着这一流程来记录事件日志,所有的数据都会保存到上述的事件数据表中.[1] 最开始的问题是,我们要计算有多少人查看了我们的主页,而他们之中有百分之多少在那次查看了主页之后的两个星期之内输入了验证信息. 如果我们使用 PostgreSQL 较老的版本, 我们可能需要使用PL/pgSQL这一PostgreSQL内置的过程语言 来编写一些定制的函数. 而在 9.3 中, 我们就可以使用一个 lateral 联合,只用一个搞笑的查询就能计算出结果,不需要任何扩展或者 PL/pgSQL.

缺省操作符类,之前列出的四个;

SELECTuser_id,view_homepage,view_homepage_time,enter_credit_card,enter_credit_card_timeFROM (-- Get the first time each user viewed the homepage.SELECTuser_id,1 AS view_homepage,min AS view_homepage_timeFROM eventWHEREdata->>'type' = 'view_homepage'GROUP BY user_id) e1 LEFT JOIN LATERAL (-- For each row, get the first time the user_id did the enter_credit_card-- event, if one exists within two weeks of view_homepage_time.SELECT1 AS enter_credit_card,time AS enter_credit_card_timeFROM eventWHEREuser_id = e1.user_id ANDdata->>'type' = 'enter_credit_card' ANDtime BETWEEN view_homepage_time AND (view_homepage_time + 1000*60*60*24*14)ORDER BY timeLIMIT 1) e2 ON true

jsonb_hash_ops,仅支持@>,但是当搜索数据时性能表现不错,而且所占磁盘空间较小;

没有人会喜欢30多行的SQL查询,所以让我们将这些SQL分成片段来分析。第一块是一段普通的 SQL:

=# CREATE INDEX geodata_gin ON geodata_jsonb USING GIN ;CREATE INDEX=# SELECT ::int as population, data->'latitude' as latitude, data->'longitude' as longitude FROM geodata_jsonb WHERE data @> '{"country_code": "JP", "asciiname": "Tokyo"}' AND ::int != 0; population | latitude | longitude ------------+----------+----------- 8336599 | 35.6895 | 139.69171 =# SELECT pg_size_pretty(pg_relation_size('geodata_gin'::regclass)) AS jsonb_gin; jsonb_gin----------- 1519 MB=# -- EXPLAIN of previous query QUERY PLAN ------------------------------------------------------------------------------------- Bitmap Heap Scan on geodata_jsonb (cost=131.01..31317.76 rows=8605 width=418) Recheck Cond: (data @> '{"asciiname": "Tokyo", "country_code": "JP"}'::jsonb) Filter: (((data ->> 'population'::text))::integer <> 0) -> Bitmap Index Scan on geodata_gin (cost=0.00..128.86 rows=8648 width=0) Index Cond: (data @> '{"asciiname": "Tokyo", "country_code": "JP"}'::jsonb) Planning time: 0.134 ms
SELECT user_id, 1 AS view_homepage, min AS view_homepage_timeFROM eventWHERE data->>'type' = 'view_homepage'GROUP BY user_id

本文由美高梅游戏网站发布于热门视频,转载请注明出处:介绍PostgreSQL中的jsonb数据类型_数据库其它_脚本之

关键词: