PG-NoSQL特性：json和 jsonb 读写性能测试

最新推荐文章于 2024-05-05 21:12:12 发布

福娃筱欢

最新推荐文章于 2024-05-05 21:12:12 发布

阅读量1.5k

点赞数

分类专栏： Postgres 文章标签： nosql json 数据库

本文链接：https://blog.csdn.net/qq961573863/article/details/127479293

版权

Postgres 专栏收录该内容

17 篇文章 1 订阅

订阅专栏

json和 jsonb 读写性能测试

文章目录

json和 jsonb 读写性能测试
前言
一、jsonb 类型创建索引
二、json jsonb 表写性能测试
- 1.写入速度比较
- 2.两表占用空间间大小比较
三、 json jsonb 读性能测试
- 1.选择基于 json jsonb 键值查询的场景
- 2.读入数据

前言

json和 jsonb 读写性能的差异主要表现为 json 写入时比 jsonb 快，但检索时比 jsonb 慢，主要原因为：
json 存储格式为文本而 jsonb 存储格式为二进制，存储格式的不同使得两种 json 数据类型的处理效率不一样。
json 类型存储的内容和输入数据一样，当检索 json 数据时必须重新解析；
jsonb以二进制形式存储已解析好的数据，当检索 jsonb 数据时不需要重新解析。

一、jsonb 类型创建索引

create table tb1_user_jsonb_test(user_info jsonb);
insert into tb1_user_jsonb_test values(
'{
  "id":1,
  "user_id":1440933,
  "user_name":"1_francs",
  "create_time":"2017-08-03 16:22:05.523456+08"
}');

创建 GIN 索引的语法:

create index idx_gin on tb1_user_jsonb_test using gin(user_info);

jsonb 上的 GIN 索引支持"@“”?“”?&“”?|"操作符，例如以下查询将会使用idx_gin索引:

select * from tb1_user_jsonb_test where user_info @>'{"name":"1_frans"}';

基于 jsonb 键值的查询不会走索引 idx_gin:

select * from tb1_user_jsonb_test where user_info->>'user_name'='1_frans';

提升基于 jsonb 类型的键值检索效率，可 jsonb 数据类型对应的键值上创建索引，如下所示：

create index idx_gin_user_infob_user_name on tb1_user_jsonb_test using btree((user_info->>'user_name'));

创建以上索引后，上述根据 user info->>user_name 键值查询的 SQL 会走索引.
创建以下3个表：
user_ini 数据表，并插入 200 万测试数据
tbl_user_json: json 数据类型表， 200 数据；
tbl_user_jsonb: jsonb 数据类型表， 200 万数据；
计划使用 user_ini 表数据生成 json jsonb 数据，tb1_user_json、tb1_user_jsonb 表

create table user_ini(id int4,user_id int8,user_name character varying(64),create_time timestamp(6) with time zone default clock_timestamp());
insert into user_ini(id,user_id,user_name)
select r,round(random()*2000000),r||'_francs' from generate_series(1,2000000) as r;
create table tb1_user_json(id serial,user_info json);
create table tb1_user_jsonb(id serial,user_info jsonb);

二、json jsonb 表写性能测试

1.写入速度比较

\timing    
Timing is on.
postgres=# insert into tb1_user_json(user_info) select row_to_json(user_ini) from user_ini;
INSERT 0 2000000
Time: 10843.115 ms (00:10.843)
postgres=# insert into tb1_user_jsonb(user_info) select row_to_json(user_ini)::jsonb from user_ini;
INSERT 0 2000000
Time: 18482.274 ms (00:18.482)

tb1_user_json 插入 200 万数据花了 10 秒左右
tbl_user_jsonb插入 200 jsonb 数据花了 18 秒左右，正好验证json 数据写入比jsonb快

2.两表占用空间间大小比较

postgres=# \dt+ tb1_user_json;
                                       List of relations
Schema |     Name      | Type  |  Owner   | Persistence | Access method |  Size  | Description
--------+---------------+-------+----------+-------------+---------------+--------+-------------
public | tb1_user_json | table | postgres | permanent   | heap          | 281 MB |
(1 row)


postgres=# \dt+ tb1_user_jsonb;
                                        List of relations
Schema |      Name      | Type  |  Owner   | Persistence | Access method |  Size  | Description
--------+----------------+-------+----------+-------------+---------------+--------+-------------
public | tb1_user_jsonb | table | postgres | permanent   | heap          | 333 MB |
(1 row)

同样的数据量 jsonb 数据类型占用空间比 json 稍大
查询tbl_user_json 的一条测试数据

postgres=# select * from tb1_user_json limit 1;
   id    |                                             user_info                                              
---------+----------------------------------------------------------------------------------------------------
4000001 | {"id":1,"user_id":1774139,"user_name":"1_francs","create_time":"2022-10-22T17:20:24.637715+08:00"}
(1 row)

三、 json jsonb 读性能测试

1.选择基于 json jsonb 键值查询的场景

比如user_info 字段的 user_name 值查询

explain analyze select * from tb1_user_jsonb where user_info->>'user_name'='1_francs';
                                                             QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------
Gather  (cost=1000.00..57053.19 rows=10000 width=143) (actual time=1.343..767.596 rows=1 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on tb1_user_jsonb  (cost=0.00..55053.19 rows=4167 width=143) (actual time=490.262..745.472 rows=0 loops=3)
         Filter: ((user_info ->> 'user_name'::text) = '1_francs'::text)
         Rows Removed by Filter: 666666
Planning Time: 0.092 ms
Execution Time: 767.627 ms
(8 rows)
Time: 768.448 ms

上述 SQL 时间 768毫秒左右基于 user_info 字段的user_name键值创建btree索引，再次执行计划如下所示：

create index idx_jsonb on tb1_user_jsonb using btree((user_info->>'user_name'));                    
postgres=# explain analyze select * from tb1_user_jsonb where user_info->>'user_name'='1_francs';
                                                         QUERY PLAN                                                         
----------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tb1_user_jsonb  (cost=233.93..23868.23 rows=10000 width=143) (actual time=0.056..0.057 rows=1 loops=1)
   Recheck Cond: ((user_info ->> 'user_name'::text) = '1_francs'::text)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on idx_jsonb  (cost=0.00..231.43 rows=10000 width=0) (actual time=0.047..0.047 rows=1 loops=1)
         Index Cond: ((user_info ->> 'user_name'::text) = '1_francs'::text)
Planning Time: 0.167 ms
Execution Time: 0.114 ms
(7 rows)
Time: 1.365 ms

走了索引，并且 SQL 时间下降 1.365ms 。

2.读入数据

为了更好地对tbl_user_json、tbl_user_jsonb 表基于键值查询的效率，根据 user info 字段 id 进行范围扫描以对比性能，创建索引并执行计划：

create index idx_gin_user_info_id on tb1_user_json using btree(((user_info->>'id')::integer));
create index idx_gin_user_infob_id on tb1_user_jsonb using btree(((user_info->>'id')::integer));
postgres=# explain analyze select id,user_info->'id',user_info->'user_name' from tb1_user_json where (user_info->>'id')::int4>1 and (user_info->>'id')::int4<10000;
                                                              QUERY PLAN                                                              
--------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tb1_user_json  (cost=214.93..22654.69 rows=10000 width=68) (actual time=0.491..15.862 rows=9998 loops=1)
   Recheck Cond: ((((user_info ->> 'id'::text))::integer > 1) AND (((user_info ->> 'id'::text))::integer < 10000))
   Heap Blocks: exact=173
   ->  Bitmap Index Scan on idx_gin_user_info_id  (cost=0.00..212.43 rows=10000 width=0) (actual time=0.425..0.425 rows=9998 loops=1)
         Index Cond: ((((user_info ->> 'id'::text))::integer > 1) AND (((user_info ->> 'id'::text))::integer < 10000))
Planning Time: 0.151 ms
Execution Time: 16.104 ms
(7 rows)
Time: 16.875 ms
postgres=# explain analyze select id,user_info->'id',user_info->'user_name' from tb1_user_jsonb where (user_info->>'id')::int4>1 and (user_info->>'id')::int4<10000;
                                                              QUERY PLAN                                                               
---------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tb1_user_jsonb  (cost=214.93..24049.23 rows=10000 width=68) (actual time=0.535..4.833 rows=9998 loops=1)
   Recheck Cond: ((((user_info ->> 'id'::text))::integer > 1) AND (((user_info ->> 'id'::text))::integer < 10000))
   Heap Blocks: exact=212
   ->  Bitmap Index Scan on idx_gin_user_infob_id  (cost=0.00..212.43 rows=10000 width=0) (actual time=0.510..0.510 rows=9998 loops=1)
         Index Cond: ((((user_info ->> 'id'::text))::integer > 1) AND (((user_info ->> 'id'::text))::integer < 10000))
Planning Time: 0.144 ms
Execution Time: 5.112 ms
(7 rows)
Time: 5.753 ms

查询表 tbl_user_json的user_info 字段 id 键值在1到10000范围内的记录走了索引，并且执行时间为16.875毫秒
查询表 tbl user_jsonb的usr_info 字段 id 键值在1到10000范围内的记录走了索引并且执行时间为5.753毫秒,从这个测试看出 jsonb检索比 json效率高。验证了“ json 写入比 jsonb 快，但检索时比 jsonb慢”的观点，值得一提的是如果需要通过key/vue进行检索，执行计划为全表扫描。

SELECT * FROM tbl user jsonb WHERE user_info @> ’ { ” user_name ” : ” 2_francs ” } ’ ;
postgres=# explain analyze select * from tb1_user_jsonb where user_info@>'{"user_name":"2_francs"}';
                                                            QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
Gather  (cost=1000.00..53989.67 rows=200 width=143) (actual time=0.841..338.609 rows=1 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on tb1_user_jsonb  (cost=0.00..52969.67 rows=83 width=143) (actual time=205.647..317.880 rows=0 loops=3)
         Filter: (user_info @> '{"user_name": "2_francs"}'::jsonb)
         Rows Removed by Filter: 666666
Planning Time: 0.084 ms
Execution Time: 338.625 ms
(8 rows)
Time: 339.002 ms

执行时间为 339 毫秒左右。在 tbl_user_jsonb 字段 user_info 上创建 gin 索引，如下所示：

create index idx_tb1_user_jsonb_user_info on tb1_user_jsonb using gin(user_info);
postgres=# explain analyze select * from tb1_user_jsonb where user_info@>'{"user_name":"2_francs"}';
                                                               QUERY PLAN                                                               
----------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tb1_user_jsonb  (cost=45.55..806.92 rows=200 width=143) (actual time=0.209..0.210 rows=1 loops=1)
   Recheck Cond: (user_info @> '{"user_name": "2_francs"}'::jsonb)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on idx_tb1_user_jsonb_user_info  (cost=0.00..45.50 rows=200 width=0) (actual time=0.183..0.183 rows=1 loops=1)
         Index Cond: (user_info @> '{"user_name": "2_francs"}'::jsonb)
Planning Time: 0.633 ms
Execution Time: 0.244 ms
(7 rows)
Time: 1.422 ms

走了索引，并且执行时间下降到了 1.422 毫秒。
测试了 json , jsonb 数据类型读写性能差异，验证了 json 写入时比 jsonb 快，但检索时比 jsonb 慢的观点。

csdn:https://blog.csdn.net/qq961573863/article/details/127467428
墨天轮：https://www.modb.pro/db/530796
公众号：Xiao Yang Mum

在这里插入图片描述