介紹PostgreSQL中的jsonb數(shù)據(jù)類型

2020-03-12 23:53:42

字體：大中小

供稿：網(wǎng)友

這篇文章主要介紹了介紹PostgreSQL中的jsonb數(shù)據(jù)類型,jsonb是PostgreSQL9.4中開(kāi)始內(nèi)置的類型,能夠支持GIN索引,需要的朋友可以參考下

PostgreSQL 9.4 正在加載一項(xiàng)新功能叫jsonb，是一種新型資料，可以儲(chǔ)存支援GIN索引的JSON 資料。換言之，此功能，在即將來(lái)臨的更新中最重要的是，如果連這都不重要的話，那就把Postgres 置于文件為本數(shù)據(jù)庫(kù)系統(tǒng)的推薦位置吧。

自從9.2開(kāi)始，一個(gè)整合JSON 資料類型已經(jīng)存在，帶有一整套功能(例如資料產(chǎn)生和資料解構(gòu)功能)，還有9.3新增的操作者。當(dāng)使用JSON 資料類型，資料的被存儲(chǔ)成一完全一樣的副本，功能還在此之上運(yùn)作，還另外需要后臺(tái)運(yùn)作的重新分析。

這心得JSONB 資料類型以已降解的2元格式存儲(chǔ)，所以，插入此資料會(huì)比JSON高效，因?yàn)楹笈_(tái)不再需要重新分析，因此讓它更快速運(yùn)行，而且還兼顧GIN 索引。就是因?yàn)樽詈筮@個(gè)原因，我們實(shí)際上建議讀者使用jsonb來(lái)代替json制作程式(當(dāng)然你還可以因應(yīng)需要而使用json)。請(qǐng)記住jsonb使用相同的操作者和功能，讀者們可以看我之前的帖子去令你得到些什么啟發(fā)(或者干脆看Postgres的文件)。

現(xiàn)在讓我們看一下JSONB是如何工作的，同時(shí)和JSON比較一下。采用的測(cè)試數(shù)據(jù)是860萬(wàn)的geobase類型數(shù)據(jù)，大概1.1G大小，包括了城市名，國(guó)家代碼(可以在這參見(jiàn)完整列表)等很多字段。首先通過(guò)底層復(fù)制(raw copy)來(lái)把這些數(shù)據(jù)存儲(chǔ)到數(shù)據(jù)庫(kù)的一個(gè)新表里面，之后把這張表通過(guò)一組填充因子是100的表轉(zhuǎn)換成JSON/JSONB，之后來(lái)看它們各占多少空間。

=# COPY geodata FROM '$HOME/Downloads/allCountries.txt';

COPY 8647839

=# CREATE TABLE geodata_jsonb (data jsonb) with (fillfactor=100);

CREATE TABLE

=# CREATE TABLE geodata_json (data json) with (fillfactor=100);

CREATE TABLE

=# /timing

Timing is on.

=# INSERT INTO geodata_json SELECT row_to_json(geodata) FROM geodata;

INSERT 0 8647839

Time: 287158.457 ms

=# INSERT INTO geodata_jsonb SELECT row_to_json(geodata)::jsonb FROM geodata;

INSERT 0 8647839

Time: 425825.967 ms

生成JSONB數(shù)據(jù)花費(fèi)稍微長(zhǎng)一點(diǎn)時(shí)間，大小有沒(méi)有區(qū)別呢?

=# 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

(1 row)

在JSON數(shù)據(jù)上面做索引從9.3版本開(kāi)始，比如用操作符(注意因?yàn)樗祷匚谋?，所?#39;->>'被采用;并且根據(jù)查詢不同，索引采用不同的關(guān)鍵字)

=# CREATE INDEX geodata_index ON

geodata_json ((data->>'country_code'), (data->>'asciiname'));

CREATE INDEX

=# SELECT pg_size_pretty(pg_relation_size('geodata_index'::regclass))

AS json_index;

json_index

------------

310 MB

(1 row)

=# SELECT (data->>'population')::int as population,

data->'latitude' as latitude,

data->'longitude' as longitude

FROM geodata_json WHERE data->>'country_code' = 'JP' AND

data->>'asciiname' = 'Tokyo' AND

(data->>'population')::int != 0;

population | latitude | longitude

------------+----------+-----------

8336599 | 35.6895 | 139.69171

(1 row)

=# -- 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

(6 rows)

在這個(gè)例子里，計(jì)劃(planner)可以使用bitmap索引掃描，同時(shí)使用了之前產(chǎn)生的索引。

現(xiàn)在，JSONB的一個(gè)新特點(diǎn)就是檢查包含帶有操作符@>的數(shù)據(jù)容量，這種數(shù)據(jù)是可以用GIN來(lái)索引的，這種操作符數(shù)據(jù)也包括了?，?|和?&(為了檢查給定的關(guān)鍵字是否存在)。 GIN索引對(duì)兩類操作符起作用：

缺省操作符類，之前列出的四個(gè);

jsonb_hash_ops，僅支持@>，但是當(dāng)搜索數(shù)據(jù)時(shí)性能表現(xiàn)不錯(cuò)，而且所占磁盤空間較小;

下面是它如何工作：

=# CREATE INDEX geodata_gin ON geodata_jsonb

USING GIN (data jsonb_hash_ops);

CREATE INDEX

=# SELECT (data->>'population')::int as population,

data->'latitude' as latitude,

data->'longitude' as longitude

FROM geodata_jsonb WHERE data @> '{"country_code": "JP", "asciiname": "Tokyo"}' AND

(data->>'population')::int != 0;

population | latitude | longitude

------------+----------+-----------

8336599 | 35.6895 | 139.69171

(1 row)

=# SELECT pg_size_pretty(pg_relation_size('geodata_gin'::regclass)) AS jsonb_gin;

jsonb_gin

-----------

1519 MB

(1 row)

=# -- 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