postgresl系列之NoSQL特性

本文是《PostgreSQL实战》的读书笔记&＃xff0c;感兴趣的可以参看本书对应章节。

一、PostgreSQL的NoSQL特性

PostgreSQL不只是一个关系型数据库&＃xff0c;同时支持非关系特性&＃xff0c;而且逐步增加对非关系特性的支持。

1.1 为jsonb类型创建索引

jsonb数据类型支持GIN索引。

{"id":1,"user_id":122,"user_name":"1_joker","create_time":"2018-11-25 20:48:10"
}


表tbl_user_jsonb中的user_info字段类型是jsonb 。在user_info创建GIN索引。

create index idx_gin ON tbl_user_jsonb USING gin(user_info);


jsonb上的GIN支持 &＃64;> 、&＃xff1f;、 &＃xff1f;& 、&＃xff1f;| 操作符。例如

-- 使用索引查询
select * from tbl_user_jsonb where user_info &＃64;> &＃39;{"user_name":"1_joker"}&＃39;-- 但是以下基于jsonb键值的查询不会走索引idx_gin:
select * from tbl_user_jsonb where user_info ->>&＃39;user_name&＃39; &＃61; &＃39;1_joker&＃39;;-- 如果要提升基于json类型的键值检索效率&＃xff0c;可以在jsonb类型对应的键值上创建索引&＃xff1a;
create index idx_gin_user_infob_user_name ON tbl_user_jsonb Using btree(user_info->>&＃39;user_name&＃39;)


1.2 json、jsonb读写性能测试

json写入时比jsonb快&＃xff0c;但检索时比jsonb慢。原因&＃xff1a;json存储格式为文本而jsonb为二进制&＃xff0c;存储格式的不同使的两种json数据类型的处理效率不一样。json类型存储的内容和输入数据一样&＃xff0c;当检索json数据时必须重新解析&＃xff1b;而jsonb以二进制存储已解析好的数据&＃xff0c;当检索jsonb数据时不需要重新解析。

通过简答的例子测试json、jsonb的读写性能差异。

--1.创建user_ini表并插入200w数据。
create table user_ini(id int4,user_id int8,user_name character varying(64),create_time timestamp(6) with time zone default clock_timestamp()
);
--1.1插入数据
INSERT INTO user_ini(id,user_id,user_name)
SELECT r,round(random()*2000000),r||&＃39;_frances&＃39;
FROM generate_series(1,2000000) AS r;--2.使用user_ini表生成json、jsonb数据&＃xff0c;创建user_ini_json&＃xff0c;user_ini_jsonb表。
CREATE TABLE tbl_user_json(id serial,user_info json);CREATE TABLE tbl_user_jsonb(id serial,user_info jsonb);


json、 jsonb 表写性能测试

根据user_ini数据通过 row_to_json函数向表user_ini_json插入200万json数据。

INSERT INTO tbl_user_json(user_info) SELECT row_to_json(user_ini) FROM user_ini;


INSERT INTO tbl_user_jsonb(user_info) SELECT row_to_json(user_ini) FROM user_ini;


可以明显看出来&＃xff0c;json的写性能要比jsonb好 。从占用空间来看&＃xff0c;同样数量jsonb数据类型占用空间比json稍大。

json、jsonb表读性能测试

根据user_info字段的user_name键的值查询

注意&＃xff1a;关闭并行查询 SET max_parallel_workers_per_gather &＃61; 0

EXPLAIN ANALYSE SELECT * FROM tbl_user_jsonb WHERE user_info ->>&＃39;user_name&＃39; &＃61; &＃39;1_frances&＃39;;-- 执行计划
Seq Scan on tbl_user_jsonb (cost&＃61;0.00..73500.32 rows&＃61;10000 width&＃61;144) (actual time&＃61;0.800..591.076 rows&＃61;1 loops&＃61;1)Filter: ((user_info ->> &＃39;user_name&＃39;::text) &＃61; &＃39;1_frances&＃39;::text)Rows Removed by Filter: 1999999
Planning time: 0.056 ms
Execution time: 591.101 ms


基于user_info字段的user_name 键值创建btree索引

CREATE INDEX idx_jsonb ON tbl_user_jsonb USING btree((user_info->>&＃39;user_name&＃39;));


再次执行上述查询&＃xff1a;

EXPLAIN ANALYSE SELECT * FROM tbl_user_jsonb WHERE user_info ->>&＃39;user_name&＃39; &＃61; &＃39;1_frances&＃39;;-- 执行计划
Bitmap Heap Scan on tbl_user_jsonb (cost&＃61;233.93..24043.95 rows&＃61;10000 width&＃61;144) (actual time&＃61;0.372..0.373 rows&＃61;1 loops&＃61;1)Recheck Cond: ((user_info ->> &＃39;user_name&＃39;::text) &＃61; &＃39;1_frances&＃39;::text)Heap Blocks: exact&＃61;1-> Bitmap Index Scan on idx_jsonb (cost&＃61;0.00..231.43 rows&＃61;10000 width&＃61;0) (actual time&＃61;0.328..0.328 rows&＃61;1 loops&＃61;1)Index Cond: ((user_info ->> &＃39;user_name&＃39;::text) &＃61; &＃39;1_frances&＃39;::text)
Planning time: 0.629 ms
Execution time: 0.429 ms


通过比较执行计划可以看出走了索引&＃xff0c;并且SQL时间下降。

为了更好地对比 tbl_user_json、tbl_user_jsonb 表基于键值查询的效率&＃xff0c;对user_info字段id键进行范围扫描以对比性能。

CREATE INDEX idx_gin_user_info_id ON tbl_user_json USING btree(((user_info ->>&＃39;id&＃39;)::integer));CREATE INDEX idx_gin_user_infob_id ON tbl_user_jsonb USING btree(((user_info ->>&＃39;id&＃39;)::integer));


--索引创建后&＃xff0c;查询tbl_user_json表
EXPLAIN ANALYSE SELECT id,user_info->&＃39;id&＃39;,user_info->&＃39;user_name&＃39; FROM tbl_user_json
WHERE(user_info->>&＃39;id&＃39;)::int4 > 1 AND (user_info->>&＃39;id&＃39;)::int4 < 10000;-- 执行计划
Bitmap Heap Scan on tbl_user_json (cost&＃61;214.93..22741.47 rows&＃61;10000 width&＃61;68) (actual time&＃61;1.830..41.511 rows&＃61;9998 loops&＃61;1)Recheck Cond: ((((user_info ->> &＃39;id&＃39;::text))::integer > 1) AND (((user_info ->> &＃39;id&＃39;::text))::integer < 10000))Heap Blocks: exact&＃61;173-> Bitmap Index Scan on idx_gin_user_info_id (cost&＃61;0.00..212.43 rows&＃61;10000 width&＃61;0) (actual time&＃61;1.762..1.762 rows&＃61;9998 loops&＃61;1)Index Cond: ((((user_info ->> &＃39;id&＃39;::text))::integer > 1) AND (((user_info ->> &＃39;id&＃39;::text))::integer < 10000))
Planning time: 0.389 ms
Execution time: 42.467 ms

查询表tbl_user_json的user_info字段id键值在1到10000范围内走了索引。

EXPLAIN ANALYSE SELECT id,user_info->&＃39;id&＃39;,user_info->&＃39;user_name&＃39; FROM tbl_user_jsonb
WHERE(user_info->>&＃39;id&＃39;)::int4 > 1 AND (user_info->>&＃39;id&＃39;)::int4 < 10000;-- 执行计划
Bitmap Heap Scan on tbl_user_jsonb (cost&＃61;214.93..24224.95 rows&＃61;10000 width&＃61;68) (actual time&＃61;1.269..12.417 rows&＃61;9998 loops&＃61;1)Recheck Cond: ((((user_info ->> &＃39;id&＃39;::text))::integer > 1) AND (((user_info ->> &＃39;id&＃39;::text))::integer < 10000))Heap Blocks: exact&＃61;213-> Bitmap Index Scan on idx_gin_user_infob_id (cost&＃61;0.00..212.43 rows&＃61;10000 width&＃61;0) (actual time&＃61;1.175..1.175 rows&＃61;9998 loops&＃61;1)Index Cond: ((((user_info ->> &＃39;id&＃39;::text))::integer > 1) AND (((user_info ->> &＃39;id&＃39;::text))::integer < 10000))
Planning time: 0.271 ms
Execution time: 13.074 ms


执行时间明显变短了。

从上面的测试看出&＃xff0c;json写入比jsonb快&＃xff0c;但检索时比jsonb慢。

二、全文索引对json和jsonb数据类型的支持

对于多数应用来说全文检索很少在数据库中实现&＃xff0c;一般使用单独的全文检索引擎&＃xff0c;例如基于SQL的全文检索引擎Sphinx。PostgreSQL支持全文检索&＃xff0c;对于规模不大的应用如果不想搭建专门的搜索引擎&＃xff0c;PostgreSQL的全文检索也可满足需求。

大部分检索需要通过数据库like操作匹配&＃xff0c;这种检索方式的主要缺点&＃xff1a;

• 不能很好地支持索引&＃xff0c;通常需要全表扫描检索数据&＃xff0c;数据量大时检索性能很低。
• 不能提供检索结果排序&＃xff0c;当输出结果数据量非常大时表现更加明显

PostgreSQL全文检索有效地解决这个问题。PostgreSQL全文检索通过以下两种数据类型来实现。

tsvector

tsvector 全文检索数据类型代表一个被优化的可以基于搜索的文档&＃xff0c;要将一串字符串转换成tsvector全文检索数据类型。

-- tsvectorSELECT &＃39;Hello,cat,how are u? cat is smiling!&＃39;::tsvector;-- &＃39;Hello,cat,how&＃39; &＃39;are&＃39; &＃39;cat&＃39; &＃39;is&＃39; &＃39;smiling!&＃39; &＃39;u?&＃39;
--可以看到&＃xff0c;字符串的内容被分隔成好几段&＃xff0c;但通过::tsvector只是做类型转换&＃xff0c;没有进行数据标准处理
--对于英文全文检索可以通过函数 to_tsvecotr进行标准化&＃xff1a;SELECT to_tsvector(&＃39;english&＃39; ,&＃39;Hello cat,&＃39;)
-- &＃39;cat&＃39;:2 &＃39;hello&＃39;:1

tsquery

tsquery 表示一个文本查询&＃xff0c;存储用于搜索的词&＃xff0c;并且支持布尔操作 & 、|、!

--将字符串转换成 tsquery
SELECT &＃39;hello&cat&＃39;::tsquery;
-- &＃39;hello&＃39; & &＃39;cat&＃39;--上述只是转换成tsquery类型&＃xff0c;而并没有做标准化&＃xff0c;使用to_tsquery函数可以执行标准化SELECT to_tsquery(&＃39;hello&cat&＃39;);
-- &＃39;hello&＃39; & &＃39;cat&＃39;


--用于检索字符串是否包括"hello" 和 "cat"字符。
SELECT to_tsvector(&＃39;english&＃39;,&＃39;Hello cat, how are u&＃39;) &＃64;&＃64; to_tsquery(&＃39;hello&cat&＃39;);
-- 结果&＃xff1a;t--2. 检索字符串是否包含字符"hello" 和 "dog"
SELECT to_tsvector(&＃39;english&＃39;,&＃39;Hello cat, how are u&＃39;) &＃64;&＃64; to_tsquery(&＃39;hello&dog&＃39;);
--结果&＃xff1a;f


英文全文检索例子

在PostgreSQL10之前全文检索不支持json和jsonb数据类型。10版本则开始支持这两种类型。