PostgreSQL JSONB 使用入門

MageekChiu 發(fā)布于2019-06-25 19:19 / 3489人閱讀

摘要：類型說(shuō)明根據(jù)中的說(shuō)明，數(shù)據(jù)類型是用來(lái)存儲(chǔ)數(shù)據(jù)的。它們幾乎接受完全相同的值集合作為輸入。該結(jié)構(gòu)是非強(qiáng)制的，但是有一個(gè)可預(yù)測(cè)的結(jié)構(gòu)會(huì)使集合的查詢更容易。如中，表示在和這兩個(gè)位置出現(xiàn)過，在中這些位置實(shí)際上就是元組的行號(hào)，包括數(shù)據(jù)塊以及。

json 類型 說(shuō)明

根據(jù)RFC 7159中的說(shuō)明，JSON 數(shù)據(jù)類型是用來(lái)存儲(chǔ) JSON（JavaScript Object Notation）數(shù)據(jù)的。這種數(shù)據(jù)也可以被存儲(chǔ)為text，但是 JSON 數(shù)據(jù)類型的優(yōu)勢(shì)在于能強(qiáng)制要求每個(gè)被存儲(chǔ)的值符合 JSON 規(guī)則。也有很多 JSON 相關(guān)的函數(shù)和操作符可以用于存儲(chǔ)在這些數(shù)據(jù)類型中的數(shù)據(jù)

PostgreSQL支持兩種 JSON 數(shù)據(jù)類型：json 和 jsonb。它們幾乎接受完全相同的值集合作為輸入。兩者最大的區(qū)別是效率。json數(shù)據(jù)類型存儲(chǔ)輸入文本的精準(zhǔn)拷貝，處理函數(shù)必須在每次執(zhí)行時(shí)必須重新解析該數(shù)據(jù)。而jsonb數(shù)據(jù)被存儲(chǔ)在一種分解好的二進(jìn)制格式中，因?yàn)樾枰龈郊拥霓D(zhuǎn)換，它在輸入時(shí)要稍慢一些。但是 jsonb在處理時(shí)要快很多，因?yàn)椴恍枰匦陆馕觥?/p>

重點(diǎn)：jsonb支持索引

由于json類型存儲(chǔ)的是輸入文本的準(zhǔn)確拷貝，存儲(chǔ)時(shí)會(huì)空格和JSON 對(duì)象內(nèi)部的鍵的順序。如果一個(gè)值中的 JSON 對(duì)象包含同一個(gè)鍵超過一次，所有的鍵/值對(duì)都會(huì)被保留（ 處理函數(shù)會(huì)把最后的值當(dāng)作有效值）。

jsonb不保留空格、不保留對(duì)象鍵的順序并且不保留重復(fù)的對(duì)象鍵。如果在輸入中指定了重復(fù)的鍵，只有最后一個(gè)值會(huì)被保留。

推薦把JSON 數(shù)據(jù)存儲(chǔ)為jsonb

在把文本 JSON 輸入轉(zhuǎn)換成jsonb時(shí)，JSON的基本類型（RFC 7159 ）會(huì)被映射到原生的 PostgreSQL類型。因此，jsonb數(shù)據(jù)有一些次要額外約束。
比如：jsonb將拒絕除 PostgreSQL numeric數(shù)據(jù)類型范圍之外的數(shù)字，而json則不會(huì)。

JSON 基本類型和相應(yīng)的PostgreSQL類型

JSON 基本類型	PostgreSQL類型	注釋
string	text	不允許u0000，如果數(shù)據(jù)庫(kù)編碼不是 UTF8，非 ASCII Unicode 轉(zhuǎn)義也是這樣
number	numeric	不允許NaN 和 infinity值
boolean	boolean	只接受小寫true和false拼寫
null	(無(wú))	SQL NULL是一個(gè)不同的概念

json 輸入輸出語(yǔ)法

-- 簡(jiǎn)單標(biāo)量/基本值
-- 基本值可以是數(shù)字、帶引號(hào)的字符串、true、false或者null
SELECT "5"::json;

-- 有零個(gè)或者更多元素的數(shù)組（元素不需要為同一類型）
SELECT "[1, 2, "foo", null]"::json;

-- 包含鍵值對(duì)的對(duì)象
-- 注意對(duì)象鍵必須總是帶引號(hào)的字符串
SELECT "{"bar": "baz", "balance": 7.77, "active": false}"::json;

-- 數(shù)組和對(duì)象可以被任意嵌套
SELECT "{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}"::json;

-- "->" 通過鍵獲得 JSON 對(duì)象域 結(jié)果為json對(duì)象
select "{"nickname": "goodspeed", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::json->"nickname" as nickname;
 nickname
-------------
 "goodspeed"

-- "->>" 通過鍵獲得 JSON 對(duì)象域 結(jié)果為text 
select "{"nickname": "goodspeed", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::json->>"nickname" as nickname;
 nickname
-----------
 goodspeed
 
-- "->" 通過鍵獲得 JSON 對(duì)象域 結(jié)果為json對(duì)象
select "{"nickname": "goodspeed", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::jsonb->"nickname" as nickname;
 nickname
-------------
 "goodspeed"

-- "->>" 通過鍵獲得 JSON 對(duì)象域 結(jié)果為text 
select "{"nickname": "goodspeed", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::jsonb->>"nickname" as nickname;
 nickname
-----------
 goodspeed

當(dāng)一個(gè) JSON 值被輸入并且接著不做任何附加處理就輸出時(shí)， json會(huì)輸出和輸入完全相同的文本，而jsonb 則不會(huì)保留語(yǔ)義上沒有意義的細(xì)節(jié)

SELECT "{"bar": "baz", "balance": 7.77, "active":false}"::json;
                      json                       
-------------------------------------------------
 {"bar": "baz", "balance": 7.77, "active":false}

-- jsonb 不會(huì)保留語(yǔ)義上的細(xì)節(jié)，key 的順序也和原始數(shù)據(jù)不一致
SELECT "{"bar": "baz", "balance": 7.77, "active":false}"::jsonb;
                      jsonb                       
--------------------------------------------------
 {"bar": "baz", "active": false, "balance": 7.77}

json 查詢語(yǔ)法

在使用JSON文檔時(shí)，推薦 將JSON 文檔存儲(chǔ)為固定的結(jié)構(gòu)。（該結(jié)構(gòu)是非強(qiáng)制的，但是有一個(gè)可預(yù)測(cè)的結(jié)構(gòu)會(huì)使集合的查詢更容易。 ）
設(shè)計(jì)JSON文檔建議：任何更新都在整行上要求一個(gè)行級(jí)鎖。為了減少鎖爭(zhēng)奪，JSON 文檔應(yīng)該每個(gè)表示 一個(gè)原子數(shù)據(jù)（業(yè)務(wù)規(guī)則上的不可拆分，可獨(dú)立修改的數(shù)據(jù)）。 這些常用的比較操作符只對(duì)jsonb 有效，而不適用于json

常用的比較操作符

操作符	描述
<	小于
>	大于
<=	小于等于
>=	大于等于
=	等于
<> or !=	不等于

包含和存在

json 數(shù)據(jù)查詢（適用于jsonb）

json和jsonb 操作符

-> 和 ->> 操作符

使用 ->> 查出的數(shù)據(jù)為text
使用 -> 查出的數(shù)據(jù)為json 對(duì)象

-- nickname 為 gs 的用戶 這里使用 ->> 查出的數(shù)據(jù)為text，所以匹配項(xiàng)也應(yīng)該是text
select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::json->>"nickname" = "gs";
select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::jsonb->>"nickname" = "gs";

-- 使用 -> 查詢，會(huì)拋出錯(cuò)誤，這里無(wú)論匹配項(xiàng)是text類型的 "gs"  還是 json 類型的 ""gs""::json都會(huì)拋出異常，json 類型不支持 等號(hào)（=）操作符
select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::json->"nickname" = ""gs"";
ERROR:  operator does not exist: json = unknown
-- json 類型不支持 "=" 操作符
select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::json->"nickname" = ""gs""::json;
ERROR:  operator does not exist: json = json

-- jsonb 格式是可以查詢成功的，這里使用 -> 查出的數(shù)據(jù)為json 對(duì)象，所以匹配項(xiàng)也應(yīng)該是json 對(duì)象
select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::jsonb->"nickname" = ""gs"";

#> 和 #>> 操作符

使用 #>> 查出的數(shù)據(jù)為text
使用 #> 查出的數(shù)據(jù)為json 對(duì)象

select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::json#>"{tags,0}" as tag;
   tag
----------
 "python"

select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::json#>>"{tags,0}" as tag;
  tag
--------
 python
 
select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::jsonb#>"{tags,0}" = ""python"";
 ?column?
----------
 t
select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::jsonb#>>"{tags,0}" = "python";
 ?column?
----------
 t

select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::json#>>"{tags,0}" = "python";
 ?column?
----------
 t
-- 會(huì)拋出錯(cuò)誤，這里無(wú)論匹配項(xiàng)是text類型的 "python"  還是 json 類型的 ""python""::json都會(huì)拋出異常，json 類型不支持 等號(hào)（=）操作符
select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::json#>"{tags,0}" = ""python"";
ERROR:  operator does not exist: json = unknown

jsonb 數(shù)據(jù)查詢（不適用于json）

額外的jsonb操作符

@>操作符

-- nickname 為 nickname 的用戶
select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::jsonb @> "{"nickname": "gs"}"::jsonb;

-- 等同于以下查詢
-- 這里使用 -> 查出的數(shù)據(jù)為json 對(duì)象，所以匹配項(xiàng)也應(yīng)該是json 對(duì)象
select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::jsonb->"nickname" = ""gs"";
select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::jsonb->>"nickname" = "gs";

-- 查詢有 python 和 golang 標(biāo)簽的數(shù)據(jù)
select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::jsonb @> "{"tags": ["python", "golang"]}";
 ?column?
----------
 t

?操作符、?|操作符和?&操作符

-- 查詢有 avatar 屬性的用戶
select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::jsonb ? "avatar";
-- 查詢有 avatar 屬性 并且avatar 數(shù)據(jù)不為空的數(shù)據(jù)
select "{"nickname": "gs", "avatar": null, "tags": ["python", "golang", "db"]}"::jsonb->>"avatar" is not null;

-- 查詢 有 avatar 或 tags 的數(shù)據(jù)
select "{"nickname": "gs", "tags": ["python", "golang", "db"]}"::jsonb ?| array["avatar", "tags"];
 ?column?
----------
 t

-- 查詢 既有 avatar 又有 tags 的用戶
select "{"nickname": "gs", "tags": ["python", "golang", "db"]}"::jsonb ?& array["avatar", "tags"];
 ?column?
----------
 f
 
 -- 查詢 tags 中包含 python 標(biāo)簽的數(shù)據(jù)
 select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::jsonb->"tags" ? "python";
 ?column?
----------
 t

json 更新

-- 更新 account content 字段（覆蓋式更新）
update account set content = jsonb_set(content, "{}", "{"nickname": "gs", "tags": ["python", "golang", "db"]}", false);

-- 修改nickanme為nickanme 的用戶標(biāo)簽
update account set content = jsonb_set(content, "{tags}", "["test", "心理"]", true) where content @> "{"nickname": "nickname"}"::jsonb;

update account set content = jsonb_set(content, "{tags}", "["test", "心理", "醫(yī)療"]", true) where content @> "{"nickname": "nickname"}"::jsonb;

-- 更新account content字段中 weixin_mp 的值（如果沒有會(huì)創(chuàng)建）

update account set content = jsonb_set(content, "{weixin_mp}", ""weixin_mp5522bd28-ed4d-11e8-949c-7200014964f0"", true) where id="5522bd28-ed4d-11e8-949c-7200014964f0";

-- 更新account 去除content 中weixin 字段（如果沒有weixin 字段也不會(huì)拋出異常）
update account set content= content - "weixin" where id="5522bd28-ed4d-11e8-949c-7200014964f0";

json 函數(shù) jsonb_pretty

作為縮進(jìn)JSON文本返回from_json。

select jsonb_pretty("[{"f1":1,"f2":null},2,null,3]");
    jsonb_pretty
--------------------
 [                 +
     {             +
         "f1": 1,  +
         "f2": null+
     },            +
     2,            +
     null,         +
     3             +
 ]
(1 row)

jsonb_set

jsonb_set() 函數(shù)參數(shù)如下：

jsonb_set(target         jsonb,  // 需要修改的數(shù)據(jù)
          path           text[], // 數(shù)據(jù)路徑
          new_value      jsonb,  // 新數(shù)據(jù)
          create_missing boolean default true)

如果create_missing 是true （缺省是true），并且path指定的路徑在target 中不存在，那么target將包含path指定部分， new_value替換部分， 或者new_value添加部分。

-- target 結(jié)構(gòu)
select jsonb_pretty("[{"f1":1,"f2":null},2]");
    jsonb_pretty
--------------------
 [                 +
     {             +
         "f1": 1,  +
         "f2": null+
     },            +
     2             +
 ]

-- 更新 target 第0 個(gè)元素 key 為 f1 的值，如果f1 不存在 忽略
select jsonb_set("[{"f1":1,"f2":null},2,null,3]", "{0,f1}","[2,3,4]", false);
                  jsonb_set
---------------------------------------------
 [{"f1": [2, 3, 4], "f2": null}, 2, null, 3]

-- 更新 target 第0 個(gè)元素 key 為 f3 的值，如果f3 不存在 創(chuàng)建 
select jsonb_set("[{"f1":1,"f2":null},2]", "{0,f3}","[2,3,4]");
                  jsonb_set
---------------------------------------------
 [{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2]
 
 -- 更新 target 第0 個(gè)元素 key 為 f3 的值，如果f3 不存在 忽略
select jsonb_set("[{"f1":1,"f2":null},2]", "{0,f3}","[2,3,4]", false);
                  jsonb_set
---------------------------------------------
 [{"f1": 1, "f2": null}, 2]

詳細(xì)的json 函數(shù)和操作符可以參考文檔：JSON 函數(shù)和操作符

jsonb 性能分析

我們使用下面的例子來(lái)說(shuō)明一下json 的查詢性能

表結(jié)構(gòu)

-- account 表 id 使用uuid 類型，需要先添加uuid-ossp模塊。
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- create table
create table account (id UUID NOT NULL PRIMARY KEY default uuid_generate_v1(), content jsonb, created_at timestamptz DEFAULT CURRENT_TIMESTAMP, updated_at timestamptz DEFAULT CURRENT_TIMESTAMP);
json=> d account
                               Table "public.account"
    Column    |           Type           | Collation | Nullable |      Default
--------------+--------------------------+-----------+----------+--------------------
 id           | uuid                     |           | not null |uuid_generate_v1()
 content      | jsonb                    |           |          |
 created_at   | timestamp with time zone |           |          | CURRENT_TIMESTAMP
 updated_at   | timestamp with time zone |           |          | CURRENT_TIMESTAMP
Indexes:
    "account_pkey" PRIMARY KEY, btree (id)

一個(gè)好的實(shí)踐是把 created_at和 updated_at 也放入jsonb 字段，這里只是示例

content 數(shù)據(jù)結(jié)構(gòu)為：

content = {
    "nickname": {"type": "string"},
    "avatar": {"type": "string"},
    "weixin": {"type": "string"},
    "tags": {"type": "array", "items": {"type": "string"}},
}

準(zhǔn)備數(shù)據(jù)

批量插入數(shù)據(jù)

-- 插入100w條有 nickname avatar tags 為["python", "golang", "c"]的數(shù)據(jù)
insert into account select uuid_generate_v1(), ("{"nickname": "nn-" || round(random()*20000000) || "", "avatar": "avatar_url", "tags": ["python", "golang", "c"]}")::jsonb from (select * from generate_series(1,100000)) as tmp;

-- 插入100w條有 nickname tags 為["python", "golang"]的數(shù)據(jù)
insert into account select uuid_generate_v1(), ("{"nickname": "nn-" || round(random()*2000000) || "", "tags": ["python", "golang"]}")::jsonb from (select * from generate_series(1,1000000)) as tmp;

-- 插入100w條有 nickname tags 為["python"]的數(shù)據(jù)
insert into account select uuid_generate_v1(), ("{"nickname": "nn-" || round(random()*2000000) || "", "tags": ["python"]}")::jsonb from (select * from generate_series(1,1000000)) as tmp;

測(cè)試查詢

EXPLAIN：顯示PostgreSQL計(jì)劃程序?yàn)樘峁┑恼Z(yǔ)句生成的執(zhí)行計(jì)劃。

ANALYZE：收集有關(guān)數(shù)據(jù)庫(kù)中表的內(nèi)容的統(tǒng)計(jì)信息。

--content 中有avatar key 的數(shù)據(jù)條數(shù) count(*) 查詢不是一個(gè)好的測(cè)試語(yǔ)句，就算是有索引，也只能起到過濾的作用，如果結(jié)果集比較大，查詢速度還是會(huì)很慢
explain analyze select count(*) from account where content::jsonb ? "avatar";
                   QUERY PLAN
----------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=29280.40..29280.41 rows=1 width=8) (actual time=170.366..170.366 rows=1 loops=1)
   ->  Gather  (cost=29280.19..29280.40 rows=2 width=8) (actual time=170.119..174.451 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=28280.19..28280.20 rows=1 width=8) (actual time=166.034..166.034 rows=1 loops=3)
               ->  Parallel Seq Scan on account  (cost=0.00..28278.83 rows=542 width=0) (actual time=0.022..161.937 rows=33333 loops=3)
                     Filter: (content ? "avatar"::text)
                     Rows Removed by Filter: 400000
 Planning Time: 0.048 ms
 Execution Time: 174.486 ms


-- content 中沒有avatar key 的數(shù)據(jù)條數(shù)
explain analyze select count(*) from account where content::jsonb ? "avatar" = false;
                QUERY PLAN
----------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=30631.86..30631.87 rows=1 width=8) (actual time=207.770..207.770 rows=1 loops=1)
   ->  Gather  (cost=30631.65..30631.86 rows=2 width=8) (actual time=207.681..212.357 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=29631.65..29631.66 rows=1 width=8) (actual time=203.565..203.565 rows=1 loops=3)
               ->  Parallel Seq Scan on account  (cost=0.00..28278.83 rows=541125 width=0) (actual time=0.050..163.629 rows=400000 loops=3)
                     Filter: (NOT (content ? "avatar"::text))
                     Rows Removed by Filter: 33333
 Planning Time: 0.050 ms
 Execution Time: 212.393 ms

--查詢content 中nickname 為nn-194318的數(shù)據(jù)
explain analyze select * from account where content@>"{"nickname": "nn-194318"}";
                                                         QUERY PLAN
----------------------------------------------------------------------------------------
 Gather  (cost=1000.00..29408.83 rows=1300 width=100) (actual time=0.159..206.990 rows=1 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on account  (cost=0.00..28278.83 rows=542 width=100) (actual time=130.867..198.081 rows=0 loops=3)
         Filter: (content @> "{"nickname": "nn-194318"}"::jsonb)
         Rows Removed by Filter: 433333
 Planning Time: 0.047 ms
 Execution Time: 207.007 ms
 
-- 對(duì)應(yīng)的查詢id 為 "b5b3ed06-7d35-11e9-b3ea-00909e9dab1d" 的數(shù)據(jù)
explain analyze select * from account where id="b5b3ed06-7d35-11e9-b3ea-00909e9dab1d";
                                                       QUERY PLAN
----------------------------------------------------------------------------------------
 Index Scan using account_pkey on account  (cost=0.43..8.45 rows=1 width=100) (actual time=0.912..0.914 rows=1 loops=1)
   Index Cond: (id = "b5b3ed06-7d35-11e9-b3ea-00909e9dab1d"::uuid)
 Planning Time: 0.348 ms
 Execution Time: 0.931 ms

通過結(jié)果可以看到使用 jsonb 查詢和使用主鍵查詢速度差異巨大，通過看查詢分析記錄可以看到，這兩個(gè)語(yǔ)句最大的差別在于使用主鍵的查詢用到了索引，而content nickname 的查詢沒有索引可以使用。
接下來(lái)測(cè)試一下使用索引時(shí)的查詢速度。

索引 GIN 索引介紹

JSONB 最常用的是GIN 索引，GIN 索引可以被用來(lái)有效地搜索在大量jsonb文檔（數(shù)據(jù)）中出現(xiàn) 的鍵或者鍵值對(duì)。

GIN(Generalized Inverted Index, 通用倒排索引) 是一個(gè)存儲(chǔ)對(duì)(key, posting list)集合的索引結(jié)構(gòu)，其中key是一個(gè)鍵值，而posting list 是一組出現(xiàn)過key的位置。如(‘hello", "14:2 23:4")中，表示hello在14:2和23:4這兩個(gè)位置出現(xiàn)過，在PG中這些位置實(shí)際上就是元組的tid(行號(hào)，包括數(shù)據(jù)塊ID（32bit）,以及item point(16 bit) )。在表中的每一個(gè)屬性，在建立索引時(shí)，都可能會(huì)被解析為多個(gè)鍵值，所以同一個(gè)元組的tid可能會(huì)出現(xiàn)在多個(gè)key的posting list中。
通過這種索引結(jié)構(gòu)可以快速的查找到包含指定關(guān)鍵字的元組，因此GIN索引特別適用于多值類型的元素搜索，比如支持全文搜索，數(shù)組中元素的搜索，而PG的GIN索引模塊最初也是為了支持全文搜索而開發(fā)的。

jsonb的默認(rèn) GIN 操作符類支持使用頂層鍵存在運(yùn)算符?、?&以及?| 操作符和路徑/值存在運(yùn)算符@>的查詢。

-- 創(chuàng)建默認(rèn)索引
CREATE INDEX idxgin ON api USING GIN (jdoc);

非默認(rèn)的 GIN 操作符類jsonb_path_ops只支持索引@>操作符。

-- 創(chuàng)建指定路徑的索引
CREATE INDEX idxginp ON api USING GIN (jdoc jsonb_path_ops);
-- create index  ix_account_content_nickname_gin on account using gin (content, (content->"nickname"));
-- create index  ix_account_content_tags_gin on account using gin (content, (content->"nickname"));
-- create index  ix_account_content_tags_gin on account using gin ((content->"tags"));

多索引支持

PostgreSQL 擁有開放的索引接口，使得PG支持非常豐富的索引方法，例如btree , hash , gin , gist , sp-gist , brin , bloom , rum , zombodb , bitmap (greenplum extend)，用戶可以根據(jù)不同的數(shù)據(jù)類型，以及查詢的場(chǎng)景，選擇不同的索引。

查詢優(yōu)化

創(chuàng)建默認(rèn)索引

-- 創(chuàng)建簡(jiǎn)單索引
create index ix_account_content on account USING GIN (content);

現(xiàn)在下面這樣的查詢就能使用該索引：

-- content 中有avatar key 的數(shù)據(jù)條數(shù)
explain analyze select count(*) from account where content::jsonb ? "avatar";
                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=4180.49..4180.50 rows=1 width=8) (actual time=43.462..43.462 rows=1 loops=1)
   ->  Bitmap Heap Scan on account  (cost=30.07..4177.24 rows=1300 width=0) (actual time=8.362..36.048 rows=100000 loops=1)
         Recheck Cond: (content ? "avatar"::text)
         Heap Blocks: exact=2032
         ->  Bitmap Index Scan on ix_account_content  (cost=0.00..29.75 rows=1300 width=0) (actual time=8.125..8.125 rows=100000 loops=1)
               Index Cond: (content ? "avatar"::text)
 Planning Time: 0.078 ms
 Execution Time: 43.503 ms

和之前沒有添加索引時(shí)速度提升了3倍。

-- 查詢content 中nickname 為nn-194318的數(shù)據(jù)
explain analyze select * from account where content@>"{"nickname": "nn-194318"}";
                                                          QUERY PLAN
----------------------------------------------------------------------------------------
 Bitmap Heap Scan on account  (cost=46.08..4193.24 rows=1300 width=100) (actual time=0.097..0.097 rows=1 loops=1)
   Recheck Cond: (content @> "{"nickname": "nn-194318"}"::jsonb)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on ix_account_content  (cost=0.00..45.75 rows=1300 width=0) (actual time=0.091..0.091 rows=1 loops=1)
         Index Cond: (content @> "{"nickname": "nn-194318"}"::jsonb)
 Planning Time: 0.075 ms
 Execution Time: 0.132 ms

這個(gè)查詢效率提升更明顯，竟然比使用主鍵還要高效。

但是下面這種查詢并不能使用索引：

-- 查詢content 中不存在 avatar key 的數(shù)據(jù)條數(shù)
explain analyze select count(*) from account where content::jsonb ? "avatar" = false;
                                                                 QUERY PLAN
----------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=30631.86..30631.87 rows=1 width=8) (actual time=207.641..207.641 rows=1 loops=1)
   ->  Gather  (cost=30631.65..30631.86 rows=2 width=8) (actual time=207.510..211.062 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=29631.65..29631.66 rows=1 width=8) (actual time=203.739..203.739 rows=1 loops=3)
               ->  Parallel Seq Scan on account  (cost=0.00..28278.83 rows=541125 width=0) (actual time=0.024..163.444 rows=400000 loops=3)
                     Filter: (NOT (content ? "avatar"::text))
                     Rows Removed by Filter: 33333
 Planning Time: 0.068 ms
 Execution Time: 211.097 ms

該索引也不能被用于下面這樣的查詢，因?yàn)楸M管操作符? 是可索引的，但它不能直接被應(yīng)用于被索引列content：

explain analyze select count(1) from account where content -> "tags" ? "c";
                                                               QUERY PLAN
----------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=30634.57..30634.58 rows=1 width=8) (actual time=184.864..184.864 rows=1 loops=1)
   ->  Gather  (cost=30634.35..30634.56 rows=2 width=8) (actual time=184.754..189.652 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=29634.35..29634.36 rows=1 width=8) (actual time=180.755..180.755 rows=1 loops=3)
               ->  Parallel Seq Scan on account  (cost=0.00..29633.00 rows=542 width=0) (actual time=0.022..177.051 rows=33333 loops=3)
                     Filter: ((content -> "tags"::text) ? "c"::text)
                     Rows Removed by Filter: 400000
 Planning Time: 0.074 ms
 Execution Time: 189.716 ms

使用表達(dá)式索引

-- 創(chuàng)建路徑索引
create index ix_account_content_tags on account USING GIN ((content->"tags"));

-- 測(cè)試查詢性能
explain analyze select count(1) from account where content -> "tags" ? "c";
                                                                   QUERY PLAN
----------------------------------------------------------------------------------------
 Aggregate  (cost=4631.74..4631.75 rows=1 width=8) (actual time=49.274..49.275 rows=1 loops=1)
   ->  Bitmap Heap Scan on account  (cost=478.07..4628.49 rows=1300 width=0) (actual time=8.655..42.074 rows=100000 loops=1)
         Recheck Cond: ((content -> "tags"::text) ? "c"::text)
         Heap Blocks: exact=2032
         ->  Bitmap Index Scan on ix_account_content_tags  (cost=0.00..477.75 rows=1300 width=0) (actual time=8.417..8.417 rows=100000 loops=1)
               Index Cond: ((content -> "tags"::text) ? "c"::text)
 Planning Time: 0.216 ms
 Execution Time: 49.309 ms

現(xiàn)在，WHERE 子句content -> "tags" ? "c" 將被識(shí)別為可索引操作符?在索引表達(dá)式content -> "tags" 上的應(yīng)用。

也可以利用包含查詢的方式，例如：

-- 查尋 "tags" 包含數(shù)組元素 "c" 的數(shù)據(jù)的個(gè)數(shù)
select count(1) from account where content @> "{"tags": ["c"]}";

content 列上的簡(jiǎn)單 GIN 索引（默認(rèn)索引）就能支持索引查詢。但是索引將會(huì)存儲(chǔ)content列中每一個(gè)鍵和值的拷貝，
表達(dá)式索引只存儲(chǔ)tags 鍵下找到的數(shù)據(jù)。

雖然簡(jiǎn)單索引的方法更加靈活（因?yàn)樗С钟嘘P(guān)任意鍵的查詢），但定向的表達(dá)式索引更小并且搜索速度比簡(jiǎn)單索引更快。
盡管jsonb_path_ops操作符類只支持用 @>操作符的查詢，但它比起默認(rèn)的操作符類 jsonb_ops有更客觀的性能優(yōu)勢(shì)。一個(gè) jsonb_path_ops索引通常也比一個(gè)相同數(shù)據(jù)上的 jsonb_ops要小得多，并且搜索的專一性更好，特 別是當(dāng)查詢包含頻繁出現(xiàn)在該數(shù)據(jù)中的鍵時(shí)。因此，其上的搜索操作 通常比使用默認(rèn)操作符類的搜索表現(xiàn)更好。

總結(jié)

PG 有兩種 JSON 數(shù)據(jù)類型：json 和 jsonb，jsonb 性能優(yōu)于json，且jsonb 支持索引。

jsonb 寫入時(shí)會(huì)處理寫入數(shù)據(jù)，寫入相對(duì)較慢，json會(huì)保留原始數(shù)據(jù)（包括無(wú)用的空格）

jsonb 查詢優(yōu)化時(shí)一個(gè)好的方式是添加GIN 索引

簡(jiǎn)單索引和路徑索引相比更靈活，但是占用空間多

路徑索引比簡(jiǎn)單索引更高效，占用空間更小

參考鏈接

RFC 7159 The JavaScript Object Notation (JSON) Data Interchange Format

PostgreSQL 文檔： JSON 類型

JSON 函數(shù)和操作符

How do I modify fields inside the new PostgreSQL JSON datatype?

PostgreSQL 9種索引的原理和應(yīng)用場(chǎng)景

PostgreSQL GIN索引實(shí)現(xiàn)原理

PostgreSQL internals: JSONB type and its indexes

倒排索引

最后，感謝女朋友支持和包容，比??

也可以在公號(hào)輸入以下關(guān)鍵字獲取歷史文章：公號(hào)&小程序 | 設(shè)計(jì)模式 | 并發(fā)&協(xié)程

云服務(wù)器 GPU云服務(wù)器 JSONB hbase入門與使用 PostgreSQL postgresql.conf postgresql

文章版權(quán)歸作者所有，未經(jīng)允許請(qǐng)勿轉(zhuǎn)載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。

轉(zhuǎn)載請(qǐng)注明本文地址：http://specialneedsforspecialkids.com/yun/18034.html

發(fā)表評(píng)論

登陸后可評(píng)論

0條評(píng)論

MageekChiu

男|高級(jí)講師

我要關(guān)注我要私信

TA的文章

tensorflow卷積神經(jīng)網(wǎng)絡(luò)

閱讀 1999·2023-04-25 16:53
SSM+JSP+MYSQL+H-UI 實(shí)現(xiàn)的校園點(diǎn)餐系統(tǒng)

閱讀 1441·2021-10-13 09:39
每秒新增8個(gè)用戶，HarmonyOS 2.0用戶達(dá)到9000萬(wàn)

閱讀 605·2021-09-08 09:35
rem : web app適配的秘密武器

閱讀 1639·2019-08-30 13:03
2019年前端面試題-02

閱讀 2120·2019-08-30 11:06
摘自<css世界>(張?chǎng)涡裰?經(jīng)典案例---------持續(xù)更新中

閱讀 1830·2019-08-30 10:59
頁(yè)面性能優(yōu)化辦法有哪些？

閱讀 3188·2019-08-29 17:00
重學(xué)前端學(xué)習(xí)筆記（二十七）--JavaScript的詞法

閱讀 2287·2019-08-23 17:55

国产xxxx99真实实拍_久久不雅视频_高清韩国a级特黄毛片_嗯老师别我我受不了了小说

資訊專欄INFORMATION COLUMN

上云采購(gòu)季！| 2核2G4M爆款云服務(wù)器低至59元/年，更有多臺(tái)、長(zhǎng)期優(yōu)惠，快來(lái)選購(gòu)！