资讯专栏INFORMATION COLUMN

PostgreSQL JSONB 使用入门

MageekChiu / 3645人阅读

摘要:类型说明根据中的说明,数据类型是用来存储数据的。它们几乎接受完全相同的值集合作为输入。该结构是非强制的,但是有一个可预测的结构会使集合的查询更容易。如中,表示在和这两个位置出现过,在中这些位置实际上就是元组的行号,包括数据块以及。

json 类型 说明

根据RFC 7159中的说明,JSON 数据类型是用来存储 JSON(JavaScript Object Notation)数据的。这种数据也可以被存储为text,但是 JSON 数据类型的优势在于能强制要求每个被存储的值符合 JSON 规则。也有很多 JSON 相关的函数和操作符可以用于存储在这些数据类型中的数据

PostgreSQL支持两种 JSON 数据类型:json 和 jsonb。它们几乎接受完全相同的值集合作为输入。两者最大的区别是效率。json数据类型存储输入文本的精准拷贝,处理函数必须在每 次执行时必须重新解析该数据。而jsonb数据被存储在一种分解好的二进制格式中,因为需要做附加的转换,它在输入时要稍慢一些。但是 jsonb在处理时要快很多,因为不需要重新解析。

</>复制代码

  1. 重点:jsonb支持索引

由于json类型存储的是输入文本的准确拷贝,存储时会空格和JSON 对象内部的键的顺序。如果一个值中的 JSON 对象包含同一个键超过一次,所有的键/值对都会被保留( 处理函数会把最后的值当作有效值)。

jsonb不保留空格、不保留对象键的顺序并且不保留重复的对象键。如果在输入中指定了重复的键,只有最后一个值会被保留。

</>复制代码

  1. 推荐把JSON 数据存储为jsonb

在把文本 JSON 输入转换成jsonb时,JSON的基本类型(RFC 7159 )会被映射到原生的 PostgreSQL类型。因此,jsonb数据有一些次要额外约束。
比如:jsonb将拒绝除 PostgreSQL numeric数据类型范围之外的数字,而json则不会。

JSON 基本类型和相应的PostgreSQL类型

JSON 基本类型 PostgreSQL类型 注释
string text 不允许u0000,如果数据库编码不是 UTF8,非 ASCII Unicode 转义也是这样
number numeric 不允许NaNinfinity
boolean boolean 只接受小写truefalse拼写
null (无) SQL NULL是一个不同的概念
json 输入输出语法

</>复制代码

  1. -- 简单标量/基本值
  2. -- 基本值可以是数字、带引号的字符串、truefalse或者null
  3. SELECT "5"::json;
  4. -- 有零个或者更多元素的数组(元素不需要为同一类型)
  5. SELECT "[1, 2, "foo", null]"::json;
  6. -- 包含键值对的对象
  7. -- 注意对象键必须总是带引号的字符串
  8. SELECT "{"bar": "baz", "balance": 7.77, "active": false}"::json;
  9. -- 数组和对象可以被任意嵌套
  10. SELECT "{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}"::json;
  11. -- "->" 通过键获得 JSON 对象域 结果为json对象
  12. select "{"nickname": "goodspeed", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::json->"nickname" as nickname;
  13. nickname
  14. -------------
  15. "goodspeed"
  16. -- "->>" 通过键获得 JSON 对象域 结果为text
  17. select "{"nickname": "goodspeed", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::json->>"nickname" as nickname;
  18. nickname
  19. -----------
  20. goodspeed
  21. -- "->" 通过键获得 JSON 对象域 结果为json对象
  22. select "{"nickname": "goodspeed", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::jsonb->"nickname" as nickname;
  23. nickname
  24. -------------
  25. "goodspeed"
  26. -- "->>" 通过键获得 JSON 对象域 结果为text
  27. select "{"nickname": "goodspeed", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::jsonb->>"nickname" as nickname;
  28. nickname
  29. -----------
  30. goodspeed

当一个 JSON 值被输入并且接着不做任何附加处理就输出时, json会输出和输入完全相同的文本,而jsonb 则不会保留语义上没有意义的细节

</>复制代码

  1. SELECT "{"bar": "baz", "balance": 7.77, "active":false}"::json;
  2. json
  3. -------------------------------------------------
  4. {"bar": "baz", "balance": 7.77, "active":false}
  5. -- jsonb 不会保留语义上的细节,key 的顺序也和原始数据不一致
  6. SELECT "{"bar": "baz", "balance": 7.77, "active":false}"::jsonb;
  7. jsonb
  8. --------------------------------------------------
  9. {"bar": "baz", "active": false, "balance": 7.77}
json 查询语法

</>复制代码

  1. 在使用JSON文档时,推荐 将JSON 文档存储为固定的结构。(该结构是非强制的,但是有一个可预测的结构会使集合的查询更容易。 )
    设计JSON文档建议:任何更新都在整行上要求一个行级锁。为了减少锁争夺,JSON 文档应该每个表示 一个原子数据(业务规则上的不可拆分,可独立修改的数据)。

    这些常用的比较操作符只对jsonb 有效,而不适用于json

常用的比较操作符

操作符 描述
< 小于
> 大于
<= 小于等于
>= 大于等于
= 等于
<> or != 不等于
包含和存在
json 数据查询(适用于jsonb)

json和jsonb 操作符

-> 和 ->> 操作符

</>复制代码

  1. 使用 ->> 查出的数据为text
    使用 -> 查出的数据为json 对象

</>复制代码

  1. -- nickname 为 gs 的用户 这里使用 ->> 查出的数据为text,所以匹配项也应该是text
  2. select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::json->>"nickname" = "gs";
  3. select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::jsonb->>"nickname" = "gs";
  4. -- 使用 -> 查询,会抛出错误,这里无论匹配项是text类型的 "gs" 还是 json 类型的 ""gs""::json都会抛出异常,json 类型不支持 等号(=)操作符
  5. select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::json->"nickname" = ""gs"";
  6. ERROR: operator does not exist: json = unknown
  7. -- json 类型不支持 "=" 操作符
  8. select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::json->"nickname" = ""gs""::json;
  9. ERROR: operator does not exist: json = json
  10. -- jsonb 格式是可以查询成功的,这里使用 -> 查出的数据为json 对象,所以匹配项也应该是json 对象
  11. select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::jsonb->"nickname" = ""gs"";
#> 和 #>> 操作符

</>复制代码

  1. 使用 #>> 查出的数据为text
    使用 #> 查出的数据为json 对象

</>复制代码

  1. select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::json#>"{tags,0}" as tag;
  2. tag
  3. ----------
  4. "python"
  5. select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::json#>>"{tags,0}" as tag;
  6. tag
  7. --------
  8. python
  9. select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::jsonb#>"{tags,0}" = ""python"";
  10. ?column?
  11. ----------
  12. t
  13. select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::jsonb#>>"{tags,0}" = "python";
  14. ?column?
  15. ----------
  16. t
  17. select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::json#>>"{tags,0}" = "python";
  18. ?column?
  19. ----------
  20. t
  21. -- 会抛出错误,这里无论匹配项是text类型的 "python" 还是 json 类型的 ""python""::json都会抛出异常,json 类型不支持 等号(=)操作符
  22. select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::json#>"{tags,0}" = ""python"";
  23. ERROR: operator does not exist: json = unknown
jsonb 数据查询(不适用于json)

额外的jsonb操作符

@>操作符

</>复制代码

  1. -- nickname 为 nickname 的用户
  2. select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::jsonb @> "{"nickname": "gs"}"::jsonb;
  3. -- 等同于以下查询
  4. -- 这里使用 -> 查出的数据为json 对象,所以匹配项也应该是json 对象
  5. select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::jsonb->"nickname" = ""gs"";
  6. select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::jsonb->>"nickname" = "gs";
  7. -- 查询有 python 和 golang 标签的数据
  8. select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::jsonb @> "{"tags": ["python", "golang"]}";
  9. ?column?
  10. ----------
  11. t
?操作符、?|操作符和?&操作符

</>复制代码

  1. -- 查询有 avatar 属性的用户
  2. select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::jsonb ? "avatar";
  3. -- 查询有 avatar 属性 并且avatar 数据不为空的数据
  4. select "{"nickname": "gs", "avatar": null, "tags": ["python", "golang", "db"]}"::jsonb->>"avatar" is not null;
  5. -- 查询 有 avatar 或 tags 的数据
  6. select "{"nickname": "gs", "tags": ["python", "golang", "db"]}"::jsonb ?| array["avatar", "tags"];
  7. ?column?
  8. ----------
  9. t
  10. -- 查询 既有 avatar 又有 tags 的用户
  11. select "{"nickname": "gs", "tags": ["python", "golang", "db"]}"::jsonb ?& array["avatar", "tags"];
  12. ?column?
  13. ----------
  14. f
  15. -- 查询 tags 中包含 python 标签的数据
  16. select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::jsonb->"tags" ? "python";
  17. ?column?
  18. ----------
  19. t
json 更新

</>复制代码

  1. -- 更新 account content 字段(覆盖式更新)
  2. update account set content = jsonb_set(content, "{}", "{"nickname": "gs", "tags": ["python", "golang", "db"]}", false);
  3. -- 修改nickanme为nickanme 的用户标签
  4. update account set content = jsonb_set(content, "{tags}", "["test", "心理"]", true) where content @> "{"nickname": "nickname"}"::jsonb;
  5. update account set content = jsonb_set(content, "{tags}", "["test", "心理", "医疗"]", true) where content @> "{"nickname": "nickname"}"::jsonb;
  6. -- 更新account content字段中 weixin_mp 的值(如果没有会创建)
  7. update account set content = jsonb_set(content, "{weixin_mp}", ""weixin_mp5522bd28-ed4d-11e8-949c-7200014964f0"", true) where id="5522bd28-ed4d-11e8-949c-7200014964f0";
  8. -- 更新account 去除content 中weixin 字段(如果没有weixin 字段也不会抛出异常)
  9. update account set content= content - "weixin" where id="5522bd28-ed4d-11e8-949c-7200014964f0";
json 函数 jsonb_pretty

</>复制代码

  1. 作为缩进JSON文本返回from_json。

</>复制代码

  1. select jsonb_pretty("[{"f1":1,"f2":null},2,null,3]");
  2. jsonb_pretty
  3. --------------------
  4. [ +
  5. { +
  6. "f1": 1, +
  7. "f2": null+
  8. }, +
  9. 2, +
  10. null, +
  11. 3 +
  12. ]
  13. (1 row)
jsonb_set

jsonb_set() 函数参数如下:

</>复制代码

  1. jsonb_set(target jsonb, // 需要修改的数据
  2. path text[], // 数据路径
  3. new_value jsonb, // 新数据
  4. create_missing boolean default true)

</>复制代码

  1. 如果create_missing 是true (缺省是true),并且path指定的路径在target 中不存在,那么target将包含path指定部分, new_value替换部分, 或者new_value添加部分。

</>复制代码

  1. -- target 结构
  2. select jsonb_pretty("[{"f1":1,"f2":null},2]");
  3. jsonb_pretty
  4. --------------------
  5. [ +
  6. { +
  7. "f1": 1, +
  8. "f2": null+
  9. }, +
  10. 2 +
  11. ]
  12. -- 更新 target 第0 个元素 key 为 f1 的值,如果f1 不存在 忽略
  13. select jsonb_set("[{"f1":1,"f2":null},2,null,3]", "{0,f1}","[2,3,4]", false);
  14. jsonb_set
  15. ---------------------------------------------
  16. [{"f1": [2, 3, 4], "f2": null}, 2, null, 3]
  17. -- 更新 target 第0 个元素 key 为 f3 的值,如果f3 不存在 创建
  18. select jsonb_set("[{"f1":1,"f2":null},2]", "{0,f3}","[2,3,4]");
  19. jsonb_set
  20. ---------------------------------------------
  21. [{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2]
  22. -- 更新 target 第0 个元素 key 为 f3 的值,如果f3 不存在 忽略
  23. select jsonb_set("[{"f1":1,"f2":null},2]", "{0,f3}","[2,3,4]", false);
  24. jsonb_set
  25. ---------------------------------------------
  26. [{"f1": 1, "f2": null}, 2]

详细的json 函数和操作符可以参考文档:JSON 函数和操作符

jsonb 性能分析

我们使用下面的例子来说明一下json 的查询性能

表结构

</>复制代码

  1. -- account 表 id 使用uuid 类型,需要先添加uuid-ossp模块。
  2. CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
  3. -- create table
  4. 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);
  5. json=> d account
  6. Table "public.account"
  7. Column | Type | Collation | Nullable | Default
  8. --------------+--------------------------+-----------+----------+--------------------
  9. id | uuid | | not null |uuid_generate_v1()
  10. content | jsonb | | |
  11. created_at | timestamp with time zone | | | CURRENT_TIMESTAMP
  12. updated_at | timestamp with time zone | | | CURRENT_TIMESTAMP
  13. Indexes:
  14. "account_pkey" PRIMARY KEY, btree (id)

</>复制代码

  1. 一个好的实践是把 created_at和 updated_at 也放入jsonb 字段,这里只是示例

content 数据结构为:

</>复制代码

  1. content = {
  2. "nickname": {"type": "string"},
  3. "avatar": {"type": "string"},
  4. "weixin": {"type": "string"},
  5. "tags": {"type": "array", "items": {"type": "string"}},
  6. }
准备数据

批量插入数据

</>复制代码

  1. -- 插入100w条有 nickname avatar tags 为["python", "golang", "c"]的数据
  2. 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;
  3. -- 插入100w条有 nickname tags 为["python", "golang"]的数据
  4. 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;
  5. -- 插入100w条有 nickname tags 为["python"]的数据
  6. insert into account select uuid_generate_v1(), ("{"nickname": "nn-" || round(random()*2000000) || "", "tags": ["python"]}")::jsonb from (select * from generate_series(1,1000000)) as tmp;
测试查询

EXPLAIN:显示PostgreSQL计划程序为提供的语句生成的执行计划。

ANALYZE:收集有关数据库中表的内容的统计信息。

</>复制代码

  1. --content 中有avatar key 的数据条数 count(*) 查询不是一个好的测试语句,就算是有索引,也只能起到过滤的作用,如果结果集比较大,查询速度还是会很慢
  2. explain analyze select count(*) from account where content::jsonb ? "avatar";
  3. QUERY PLAN
  4. ----------------------------------------------------------------------------------------
  5. Finalize Aggregate (cost=29280.40..29280.41 rows=1 width=8) (actual time=170.366..170.366 rows=1 loops=1)
  6. -> Gather (cost=29280.19..29280.40 rows=2 width=8) (actual time=170.119..174.451 rows=3 loops=1)
  7. Workers Planned: 2
  8. Workers Launched: 2
  9. -> Partial Aggregate (cost=28280.19..28280.20 rows=1 width=8) (actual time=166.034..166.034 rows=1 loops=3)
  10. -> Parallel Seq Scan on account (cost=0.00..28278.83 rows=542 width=0) (actual time=0.022..161.937 rows=33333 loops=3)
  11. Filter: (content ? "avatar"::text)
  12. Rows Removed by Filter: 400000
  13. Planning Time: 0.048 ms
  14. Execution Time: 174.486 ms
  15. -- content 中没有avatar key 的数据条数
  16. explain analyze select count(*) from account where content::jsonb ? "avatar" = false;
  17. QUERY PLAN
  18. ----------------------------------------------------------------------------------------
  19. Finalize Aggregate (cost=30631.86..30631.87 rows=1 width=8) (actual time=207.770..207.770 rows=1 loops=1)
  20. -> Gather (cost=30631.65..30631.86 rows=2 width=8) (actual time=207.681..212.357 rows=3 loops=1)
  21. Workers Planned: 2
  22. Workers Launched: 2
  23. -> Partial Aggregate (cost=29631.65..29631.66 rows=1 width=8) (actual time=203.565..203.565 rows=1 loops=3)
  24. -> Parallel Seq Scan on account (cost=0.00..28278.83 rows=541125 width=0) (actual time=0.050..163.629 rows=400000 loops=3)
  25. Filter: (NOT (content ? "avatar"::text))
  26. Rows Removed by Filter: 33333
  27. Planning Time: 0.050 ms
  28. Execution Time: 212.393 ms

</>复制代码

  1. --查询content 中nickname 为nn-194318的数据
  2. explain analyze select * from account where content@>"{"nickname": "nn-194318"}";
  3. QUERY PLAN
  4. ----------------------------------------------------------------------------------------
  5. Gather (cost=1000.00..29408.83 rows=1300 width=100) (actual time=0.159..206.990 rows=1 loops=1)
  6. Workers Planned: 2
  7. Workers Launched: 2
  8. -> Parallel Seq Scan on account (cost=0.00..28278.83 rows=542 width=100) (actual time=130.867..198.081 rows=0 loops=3)
  9. Filter: (content @> "{"nickname": "nn-194318"}"::jsonb)
  10. Rows Removed by Filter: 433333
  11. Planning Time: 0.047 ms
  12. Execution Time: 207.007 ms
  13. -- 对应的查询id"b5b3ed06-7d35-11e9-b3ea-00909e9dab1d" 的数据
  14. explain analyze select * from account where id="b5b3ed06-7d35-11e9-b3ea-00909e9dab1d";
  15. QUERY PLAN
  16. ----------------------------------------------------------------------------------------
  17. 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)
  18. Index Cond: (id = "b5b3ed06-7d35-11e9-b3ea-00909e9dab1d"::uuid)
  19. Planning Time: 0.348 ms
  20. Execution Time: 0.931 ms

通过结果可以看到 使用 jsonb 查询和使用主键查询速度差异巨大,通过看查询分析记录可以看到,这两个语句最大的差别在于使用主键的查询用到了索引,而content nickname 的查询没有索引可以使用。
接下来测试一下使用索引时的查询速度。

索引 GIN 索引介绍

JSONB 最常用的是GIN 索引,GIN 索引可以被用来有效地搜索在大量jsonb文档(数据)中出现 的键或者键值对。

</>复制代码

  1. GIN(Generalized Inverted Index, 通用倒排索引) 是一个存储对(key, posting list)集合的索引结构,其中key是一个键值,而posting list 是一组出现过key的位置。如(‘hello", "14:2 23:4")中,表示hello在14:2和23:4这两个位置出现过,在PG中这些位置实际上就是元组的tid(行号,包括数据块ID(32bit),以及item point(16 bit) )。

    在表中的每一个属性,在建立索引时,都可能会被解析为多个键值,所以同一个元组的tid可能会出现在多个keyposting list中。

  2. 通过这种索引结构可以快速的查找到包含指定关键字的元组,因此GIN索引特别适用于多值类型的元素搜索,比如支持全文搜索,数组中元素的搜索,而PG的GIN索引模块最初也是为了支持全文搜索而开发的。

jsonb的默认 GIN 操作符类支持使用顶层键存在运算符??&以及?| 操作符和路径/值存在运算符@>的查询。

</>复制代码

  1. -- 创建默认索引
  2. CREATE INDEX idxgin ON api USING GIN (jdoc);

非默认的 GIN 操作符类jsonb_path_ops只支持索引@>操作符。

</>复制代码

  1. -- 创建指定路径的索引
  2. CREATE INDEX idxginp ON api USING GIN (jdoc jsonb_path_ops);
  3. -- create index ix_account_content_nickname_gin on account using gin (content, (content->"nickname"));
  4. -- create index ix_account_content_tags_gin on account using gin (content, (content->"nickname"));
  5. -- create index ix_account_content_tags_gin on account using gin ((content->"tags"));
多索引支持

</>复制代码

  1. PostgreSQL 拥有开放的索引接口,使得PG支持非常丰富的索引方法,例如btree , hash , gin , gist , sp-gist , brin , bloom , rum , zombodb , bitmap (greenplum extend),用户可以根据不同的数据类型,以及查询的场景,选择不同的索引。
查询优化

创建默认索引

</>复制代码

  1. -- 创建简单索引
  2. create index ix_account_content on account USING GIN (content);

现在下面这样的查询就能使用该索引:

</>复制代码

  1. -- content 中有avatar key 的数据条数
  2. explain analyze select count(*) from account where content::jsonb ? "avatar";
  3. QUERY PLAN
  4. ------------------------------------------------------------------------------------------------------------------------------------------
  5. Aggregate (cost=4180.49..4180.50 rows=1 width=8) (actual time=43.462..43.462 rows=1 loops=1)
  6. -> Bitmap Heap Scan on account (cost=30.07..4177.24 rows=1300 width=0) (actual time=8.362..36.048 rows=100000 loops=1)
  7. Recheck Cond: (content ? "avatar"::text)
  8. Heap Blocks: exact=2032
  9. -> 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)
  10. Index Cond: (content ? "avatar"::text)
  11. Planning Time: 0.078 ms
  12. Execution Time: 43.503 ms

和之前没有添加索引时速度提升了3倍。

</>复制代码

  1. -- 查询content 中nickname 为nn-194318的数据
  2. explain analyze select * from account where content@>"{"nickname": "nn-194318"}";
  3. QUERY PLAN
  4. ----------------------------------------------------------------------------------------
  5. Bitmap Heap Scan on account (cost=46.08..4193.24 rows=1300 width=100) (actual time=0.097..0.097 rows=1 loops=1)
  6. Recheck Cond: (content @> "{"nickname": "nn-194318"}"::jsonb)
  7. Heap Blocks: exact=1
  8. -> 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)
  9. Index Cond: (content @> "{"nickname": "nn-194318"}"::jsonb)
  10. Planning Time: 0.075 ms
  11. Execution Time: 0.132 ms

这个查询效率提升更明显,竟然比使用主键还要高效。

但是下面这种查询并不能使用索引

</>复制代码

  1. -- 查询content 中不存在 avatar key 的数据条数
  2. explain analyze select count(*) from account where content::jsonb ? "avatar" = false;
  3. QUERY PLAN
  4. ----------------------------------------------------------------------------------------
  5. Finalize Aggregate (cost=30631.86..30631.87 rows=1 width=8) (actual time=207.641..207.641 rows=1 loops=1)
  6. -> Gather (cost=30631.65..30631.86 rows=2 width=8) (actual time=207.510..211.062 rows=3 loops=1)
  7. Workers Planned: 2
  8. Workers Launched: 2
  9. -> Partial Aggregate (cost=29631.65..29631.66 rows=1 width=8) (actual time=203.739..203.739 rows=1 loops=3)
  10. -> Parallel Seq Scan on account (cost=0.00..28278.83 rows=541125 width=0) (actual time=0.024..163.444 rows=400000 loops=3)
  11. Filter: (NOT (content ? "avatar"::text))
  12. Rows Removed by Filter: 33333
  13. Planning Time: 0.068 ms
  14. Execution Time: 211.097 ms

该索引也不能被用于下面这样的查询,因为尽管操作符? 是可索引的,但它不能直接被应用于被索引列content:

</>复制代码

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

使用表达式索引

</>复制代码

  1. -- 创建路径索引
  2. create index ix_account_content_tags on account USING GIN ((content->"tags"));

</>复制代码

  1. -- 测试查询性能
  2. explain analyze select count(1) from account where content -> "tags" ? "c";
  3. QUERY PLAN
  4. ----------------------------------------------------------------------------------------
  5. Aggregate (cost=4631.74..4631.75 rows=1 width=8) (actual time=49.274..49.275 rows=1 loops=1)
  6. -> Bitmap Heap Scan on account (cost=478.07..4628.49 rows=1300 width=0) (actual time=8.655..42.074 rows=100000 loops=1)
  7. Recheck Cond: ((content -> "tags"::text) ? "c"::text)
  8. Heap Blocks: exact=2032
  9. -> 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)
  10. Index Cond: ((content -> "tags"::text) ? "c"::text)
  11. Planning Time: 0.216 ms
  12. Execution Time: 49.309 ms

现在,WHERE 子句content -> "tags" ? "c" 将被识别为可索引操作符?在索引表达式content -> "tags" 上的应用。

也可以利用包含查询的方式,例如:

</>复制代码

  1. -- 查寻 "tags" 包含数组元素 "c" 的数据的个数
  2. select count(1) from account where content @> "{"tags": ["c"]}";

content 列上的简单 GIN 索引(默认索引)就能支持索引查询。 但是索引将会存储content列中每一个键 和值的拷贝
表达式索引只存储tags 键下找到的数据。

</>复制代码

  1. 虽然简单索引的方法更加灵活(因为它支持有关任意键的查询),但定向的表达式索引更小并且搜索速度比简单索引更快。
    尽管jsonb_path_ops操作符类只支持用 @>操作符的查询,但它比起默认的操作符类 jsonb_ops有更客观的性能优势。一个 jsonb_path_ops索引通常也比一个相同数据上的 jsonb_ops要小得多,并且搜索的专一性更好,特 别是当查询包含频繁出现在该数据中的键时。因此,其上的搜索操作 通常比使用默认操作符类的搜索表现更好。
总结

PG 有两种 JSON 数据类型:jsonjsonb,jsonb 性能优于json,且jsonb 支持索引。

jsonb 写入时会处理写入数据,写入相对较慢,json会保留原始数据(包括无用的空格)

jsonb 查询优化时一个好的方式是添加GIN 索引

简单索引和路径索引相比更灵活,但是占用空间多

路径索引比简单索引更高效,占用空间更小

参考链接

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

PostgreSQL 文档: JSON 类型

JSON 函数和操作符

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

PostgreSQL 9种索引的原理和应用场景

PostgreSQL GIN索引实现原理

PostgreSQL internals: JSONB type and its indexes

倒排索引

最后,感谢女朋友支持和包容,比❤️

也可以在公号输入以下关键字获取历史文章:公号&小程序 | 设计模式 | 并发&协程

文章版权归作者所有,未经允许请勿转载,若此文章存在违规行为,您可以联系管理员删除。

转载请注明本文地址:https://www.ucloud.cn/yun/18034.html

相关文章

  • PostgreSQL JSONB 使用入门

    摘要:类型说明根据中的说明,数据类型是用来存储数据的。它们几乎接受完全相同的值集合作为输入。该结构是非强制的,但是有一个可预测的结构会使集合的查询更容易。如中,表示在和这两个位置出现过,在中这些位置实际上就是元组的行号,包括数据块以及。 json 类型 说明 根据RFC 7159中的说明,JSON 数据类型是用来存储 JSON(JavaScript Object Notation)数据的。这...

    Karrdy 评论0 收藏0
  • sequelize入门

    摘要:最近在公司接触到了的框架,研究了一下官方文档,做了以下整理其他定义方法字段类型是否允许为字段是否自定义表名是否需要增加字段不需要字段将字段改个名将字段改名同时需要设置为此种模式下,删除数据时不会进行物理删除,而是设置为当前时间 最近在公司接触到了sequelize(Nodejs的ORM框架),研究了一下官方文档,做了以下整理 Models Definition let DeviceIn...

    kidsamong 评论0 收藏0
  • PostgreSQL 9.4 中使用 jsonb

    摘要:但这些不会发生列中,这种查找使用了索引,却并没有像使用表达式索引那样将速度优化的很好。这也使得数据验证和创建表单更加简单。在每一个新版本中,使用和都比过去更加容易,变得更加出色。因此,尝试使用最新的版本,付出总是会很快得到回报的。 转载翻译自http://nandovieira.com/using-postgresql-and-jsonb-with-ruby-on-rails Pos...

    张红新 评论0 收藏0

发表评论

0条评论

MageekChiu

|高级讲师

TA的文章

阅读更多
最新活动
阅读需要支付1元查看
<