|
|
51CTO旗下网站
|
|
移动端

PostgreSQL何以支持丰富的NoSQL特性?

PostgreSQL不仅是关系型数据库,同时支持丰富的NoSQL特性,所以本文将介绍PostgreSQL的NoSQL特性:PostgreSQL的JSON和JSONB数据类型简介、JSON与JSONB读写性能测试和PostgreSQL全文检索支持JSON和JSONB。

作者:DBAplus社群来源:今日头条|2018-09-28 14:37

PostgreSQL何以支持丰富的NoSQL特性?

作者介绍

谭峰,网名francs,中国开源软件推进联盟PostgreSQL分会特聘专家,《PostgreSQL实战》作者之一,《PostgreSQL 9 Administration Cookbook》译者之一。现就职于浙江移动负责应用上云架构管控以及私有云建设工作。

张文升,中国开源软件推进联盟PostgreSQL分会核心成员之一,《PostgreSQL实战》作者之一,活跃于PostgreSQL、MySQL、Redis等开源技术社区,致力于推动PostgreSQL在互联网企业的应用及企业PostgreSQL培训与技术支持。

在上一篇文章《PostgreSQL用户应掌握的高级SQL特性》我们介绍了PostgreSQL的典型高级SQL特性。PostgreSQL不仅是关系型数据库,同时支持丰富的NoSQL特性,所以本文将介绍PostgreSQL的NoSQL特性,分以下三部分来介绍:

  • PostgreSQL的JSON和JSONB数据类型简介;

  • JSON与JSONB读写性能测试;

  • PostgreSQL全文检索支持JSON和JSONB(PosgreSQL 10新特性)。

一、JSON和JSONB数据类型

PostgreSQL支持非关系数据类型json (JavaScript Object Notation),本节介绍json类型、json与jsonb差异、json与jsonb操作符和函数以及jsonb键值的追加、删除、更新。

1、JSON类型简介

PotgreSQL早在9.2版本已经提供了json类型,并且随着大版本的演进,PostgreSQL对json的支持趋于完善,例如提供白菜送彩金大全的json函数和操作符方便应用开发,一个简单的json类型例子如下:

  1. mydb=> SELECT '{"a":1,"b":2}'::json; 
  2. json 
  3. --------------- 
  4. {"a":1,"b":2} 

为了更好演示json类型,接下来创建一张表,如下所示:

  1. mydb=> CREATE TABLE test_json1 (id serial primary key,name json);  
  2. CREATE TABLE 

以上示例定义字段name为json类型,插入表数据,如下所示:

  1. mydb=> INSERT INTO test_json1 (name 
  2. VALUES ('{"col1":1,"col2":"francs","col3":"male"}');  
  3. INSERT 0 1  
  4. mydb=> INSERT INTO test_json1 (name 
  5. VALUES ('{"col1":2,"col2":"fp","col3":"female"}');  
  6. INSERT 0 1 

查询表test_json1数据:

  1. mydb=> SELECT * FROM test_json1;  
  2. id | name  
  3. ----+------------------------------------------  
  4. 1 | {"col1":1,"col2":"francs","col3":"male" 
  5. 2 | {"col1":2,"col2":"fp","col3":"female"

2、查询JSON数据

通过->操作符可以查询json数据的键值,如下所示:

  1. mydb=> SELECT name -> 'col2' FROM test_json1 WHERE id=1;  
  2. ?column 
  3. ----------  
  4. "francs"  
  5. (1 row) 

如果想以文本格式返回json字段键值可以使用->>符,如下所示:

  1. mydb=> SELECT name ->> 'col2' FROM test_json1 WHERE id=1;  
  2. francs  
  3. (1 row) 

3、JSONB与JSON差异

PostgreSQL支持两种JSON数据类型:json和jsonb,两种类型在使用上几乎完全相同,主要区别如下:

json存储格式为文本,而jsonb存储格式为二进制 ,由于存储格式的不同使得两种json数据类型的处理效率不一样,json类型以文本存储并且存储的内容和输入数据一样,当检索json数据时必须重新解析,而jsonb以二进制形式存储已解析好的数据,当检索jsonb数据时不需要重新解析,因此json写入比jsonb快,但检索比jsonb慢,后面会通过测试验证两者读写性能差异。

除了上述介绍的区别之外,json与jsonb在使用过程中还存在差异,例如jsonb输出的键的顺序和输入不一样,如下所示:

  1. mydb=> SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::jsonb; 
  2. jsonb 
  3. -------------------------------------------------- 
  4. {"bar""baz""active"false"balance": 7.77} 
  5. (1 row) 

而json的输出键的顺序和输入完全一样,如下所示:

  1. mydb=> SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::json;  
  2. json  
  3. -------------------------------------------------  
  4. {"bar""baz""balance": 7.77, "active":false 
  5. (1 row) 

另外,jsonb类型会去掉输入数据中键值的空格,如下所示:

  1. mydb=> SELECT ' {"id":1, "name":"francs"}'::jsonb;  
  2. jsonb  
  3. -----------------------------  
  4. {"id": 1, "name""francs" 
  5. (1 row) 

上例中id键与name键输入时是有空格的,输出显示空格键被删除,而json的输出和输入一样,不会删掉空格键:

  1. mydb=> SELECT ' {"id":1, "name":"francs"}'::json; 
  2. json 
  3. ------------------------------- 
  4. {"id":1, "name":"francs"
  5. (1 row) 

另外,jsonb会删除重复的键,仅保留最后一个,如下所示:

  1. mydb=> SELECT ' {"id":1,  
  2. "name":"francs" 
  3. "remark":"a good guy!" 
  4. "name":"test"  
  5. }'::jsonb;  
  6. jsonb  
  7. ----------------------------------------------------  
  8. {"id": 1, "name""test""remark""a good guy!" 
  9. (1 row) 

上面name键重复,仅保留最后一个name键的值,而json数据类型会保留重复的键值。

相比json大多数应用场景建议使用jsonb,除非有特殊的需求,比如对json的键顺序有特殊的要求。

4、JSONB与JSON操作符

PostgreSQL支持丰富的JSONB和JSON的操作符,举例如下:

以文本格式返回json类型的字段键值可以使用->>符,如下所示:

字符串是否作为顶层键值,如下所示:

  1. mydb=> SELECT '{"a":1, "b":2}'::jsonb ? 'a' 
  2.  
  3. (1 row) 

删除json数据的键/值,如下所示:

  1. mydb=> SELECT '{"a":1, "b":2}'::jsonb - 'a' 
  2. {"b": 2}  
  3. (1 row) 

5、JSONB与JSON函数

json与jsonb相关的函数非常丰富,举例如下:

扩展最外层的json对象成为一组键/值结果集,如下所示:

  1. mydb=> SELECT * FROM json_each('{"a":"foo", "b":"bar"}');  
  2. key | value  
  3. -----+-------  
  4. a | "foo"  
  5. b | "bar"  
  6. (2 rows

以文本形式返回结果,如下所示:

  1. mydb=> SELECT * FROM json_each_text('{"a":"foo", "b":"bar"}');  
  2. a | foo  
  3. b | bar  
  4. (2 rows

一个非常重要的函数为row_to_json函数,能够将行作为json对象返回,此函数常用来生成json测试数据,比如将一个普通表转换成json类型表:

  1. mydb=> SELECT * FROM test_copy WHERE id=1;  
  2. id | name  
  3. ----+------  
  4. 1 | a  
  5. (1 row)  
  6. mydb=> SELECT row_to_json(test_copy) FROM test_copy WHERE id=1;  
  7. row_to_json  
  8. ---------------------  
  9. {"id":1,"name":"a" 
  10. (1 row) 

返回最外层的json对像中的键的集合,如下所示:

  1. mydb=> SELECT * FROM json_object_keys('{"a":"foo", "b":"bar"}');  
  2. json_object_keys  
  3. ------------------  
  4.  
  5.  
  6. (2 rows

6、jsonb键/值的追加、删除、更新

jsonb键/值追加可通过||操作符,如下增加sex键/值:

  1. mydb=> SELECT '{"name":"francs","age":"31"}'::jsonb ||  
  2. '{"sex":"male"}'::jsonb;  
  3. ?column 
  4. ------------------------------------------------  
  5. {"age""31""sex""male""name""francs" 
  6. (1 row) 

jsonb键/值的删除有两种方法,一种是通过操作符号-删除,另一种通过操作符#-删除指定键/值。

通过操作符号-删除键/值如下:

  1. mydb=> SELECT '{"name": "James", "email": "james@localhost"}'::jsonb  
  2. 'email' 
  3. ?column 
  4. -------------------  
  5. {"name""James" 
  6. (1 row)  
  7. mydb=> SELECT '["red","green","blue"]'::jsonb - 0;  
  8. ["green""blue"

第二种方法是通过操作符#-删除指定键/值,通常用于有嵌套json数据删除的场景,如下删除嵌套contact中的fax键/值:

  1. mydb=> SELECT '{"name": "James", "contact": {"phone": "01234 567890", "fax": "01987 543210"}}'::jsonb #- '{contact,fax}'::text;  
  2. ?column 
  3. ---------------------------------------------------------  
  4. {"name""James""contact": {"phone""01234 567890"}}  
  5. (1 row) 

删除嵌套aliases中的位置为1的键/值,如下所示:

  1. mydb=> SELECT '{"name": "James", "aliases": ["Jamie","The Jamester","J Man"]}'::jsonb #- '{aliases,1}'::text;  
  2. {"name""James""aliases": ["Jamie""J Man"]}  
  3. (1 row) 

键/值的更新也有两种方式,第一种方式为||操作符,||操作符可以连接json键,也可覆盖重复的键值,如下修改age键的值:

  1. mydb=> SELECT '{"name":"francs","age":"31"}'::jsonb ||  
  2. '{"age":"32"}'::jsonb;  
  3. ?column 
  4. ---------------------------------  
  5. {"age""32""name""francs" 
  6. (1 row) 

第二种方式是通过jsonb_set函数,语法如下:

  1. jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean]) 

target指源jsonb数据,path指路径,new_value指更新后的键值,create_missing 值为 true表示如果键不存在则添加,create_missing 值为 false表示如果键不存在则不添加,示例如下:

  1. mydb=> SELECT jsonb_set('{"name":"francs","age":"31"}'::jsonb,'{age}','"32"'::jsonb,false);  
  2. jsonb_set  
  3. mydb=> SELECT jsonb_set('{"name":"francs","age":"31"}'::jsonb,'{sex}','"male"'::jsonb,true); 

7、给JSONB类型创建索引

这一小节介绍给jsonb数据类型创建索引,jsonb数据类型支持GIN索引,为了便于说明,假如一个json字段内容如下,并且以jsonb格式存储。

  1.  
  2. "id": 1,  
  3. "user_id": 1440933,  
  4. "user_name""1_francs" 
  5. "create_time""2017-08-03 16:22:05.528432+08"  

假如存储以上jsonb数据的字段名为user_info,表名为tbl_user_jsonb,在user_info字段上创建GIN索引语法如下:

CREATE INDEX idx_gin ON tbl_user_jsonb USING gin(user_info);

jsonb上的GIN索引支持@>、?、 ?&、?|操作符,例如以下查询将会使用索引:

  1. SELECT * FROM tbl_user_jsonb WHERE user_info @> '{"user_name": "1_frans"}' 

但是以下基于jsonb键值的查询不会走索引idx_gin,如下所示:

  1. SELECT * FROM tbl_user_jsonb WHERE user_info->>'user_name''1_francs'

如果要想提升基于jsonb类型的键值检索效率,可以在jsonb数据类型对应的键值上创建索引,如下所示:

  1. CREATE INDEX idx_gin_user_infob_user_name ON tbl_user_jsonb USING btree ((user_info ->> 'user_name')); 

创建以上索引后,上述根据user_info->>'user_name'键值查询的SQL将会走索引。

二、JSON与JSONB读写性能测试

前面介绍了jsonb数据类型索引创建相关内容,本部分将对json、jsonb读写性能进行简单对比。json与jsonb读写性能存在差异,主要表现为json写入时比jsonb快,但检索时比jsonb慢,主要原因为:

json存储格式为文本,而jsonb存储格式为二进制,存储格式的不同使得两种json数据类型的处理效率不一样,json类型存储的内容和输入数据一样,当检索json数据时必须重新解析,而jsonb以二进制形式存储已解析好的数据,当检索jsonb数据时不需要重新解析。

1、构建JSON、JSONB测试表

下面通过一个简单的例子测试下json、jsonb的读写性能差异,计划创建以下三张表:

  • quser_ini:基础数据表,并插入200万测试数据;

  • qtbl_user_json: json 数据类型表,200万数据;

  • qtbl_user_jsonb:jsonb 数据类型表,200万数据。

首先创建user_ini表并插入200万测试数据,如下:

  1. mydb=> CREATE TABLE user_ini(id int4 ,user_id int8, user_name character varying(64),create_time timestamp(6) with time zone default clock_timestamp); 
  2.  
  3. CREATE TABLE 
  4.  
  5. mydb=> INSERT INTO user_ini(id,user_id,user_name) 
  6.  
  7. SELECT r,round(random*2000000), r || '_francs' FROM generate_series(1,2000000) as r; 
  8.  
  9. INSERT 0 2000000 

计划使用user_ini表数据生成json、jsonb数据,创建user_ini_json、user_ini_jsonb表,如下所示:

  1. mydb=> CREATE TABLE tbl_user_json(id serial, user_info json);  
  2. CREATE TABLE  
  3. mydb=> CREATE TABLE tbl_user_jsonb(id serial, user_info jsonb);  
  4. CREATE TABLE 

2、JSON与JSONB表写性能测试

根据user_ini数据通过row_to_json函数向表user_ini_json插入200万json数据,如下:

  1. mydb=> iming  
  2. Timing is on 
  3. mydb=> INSERT INTO tbl_user_json(user_info) SELECT row_to_json(user_ini)  
  4. FROM user_ini;  
  5. INSERT 0 2000000  
  6. Time: 13825.974 ms (00:13.826) 

从以上结果看出tbl_user_json插入200万数据花了13秒左右;接着根据user_ini表数据生成200万jsonb数据并插入表tbl_user_jsonb,如下:

  1. mydb=> INSERT INTO tbl_user_jsonb(user_info)  
  2. SELECT row_to_json(user_ini)::jsonb FROM user_ini;  
  3. INSERT 0 2000000  
  4. Time: 20756.993 ms (00:20.757) 

从以上看出tbl_user_jsonb表插入200万jsonb数据花了20秒左右,正好验证了json数据写入比jsonb快,比较两表占用空间大小,如下所示:

  1. mydb=> dt+ tbl_user_json  
  2. List of relations  
  3. Schema | Name | Type | Owner | Size | Description  
  4. --------+---------------+-------+--------+--------+-------------  
  5. pguser | tbl_user_json | table | pguser | 281 MB |  
  6. (1 row)  
  7. mydb=> dt+ tbl_user_jsonb  
  8. --------+----------------+-------+--------+--------+-------------  
  9. pguser | tbl_user_jsonb | table | pguser | 333 MB |  
  10. (1 row) 

从占用空间来看,同样的数据量jsonb数据类型占用空间比json稍大。

查询tbl_user_json表的一条测试数据,如下:

  1. mydb=> SELECT * FROM tbl_user_json LIMIT 1;  
  2. id | user_info  
  3. ---------+------------------------------------------------------------------------------------  
  4. 2000001 | {"id":1,"user_id":1182883,"user_name":"1_francs","create_time":"2017-08-03T20:59:27.42741+08:00" 
  5. (1 row) 

3、JSON与JSONB表读性能测试

对于json、jsonb读性能测试我们选择基于json、jsonb键值查询的场景,例如,根据user_info字段的user_name键的值查询,如下所示:

  1. mydb=> EXPLAIN ANALYZE SELECT * FROM tbl_user_jsonb WHERE user_info->>'user_name'='1_francs' 
  2. QUERY PLAN  
  3. -------------------------------------------------------------------------------------  
  4. Seq Scan on tbl_user_jsonb (cost=0.00..72859.90 rows=10042 width=143) (actual time=0.023..524.843 rows=1 loops=1)  
  5. Filter: ((user_info ->> 'user_name'::text) = '1_francs'::text)  
  6. Rows Removed by Filter: 1999999  
  7. Planning time: 0.091 ms  
  8. Execution time: 524.876 ms  
  9. (5 rows

上述SQL执行时间为524毫秒左右,基于user_info字段的user_name键值创建btree索引如下:

  1. mydb=> CREATE INDEX idx_jsonb ON tbl_user_jsonb USING btree ((user_info->>'user_name')); 

再次执行上述查询,如下所示:

  1. Bitmap Heap Scan on tbl_user_jsonb (cost=155.93..14113.93 rows=10000 width=143) (actual time=0.027..0.027 rows=1 loops=1)  
  2. Recheck Cond: ((user_info ->> 'user_name'::text) = '1_francs'::text)  
  3. Heap Blocks: exact=1  
  4. -> Bitmap Index Scan on idx_jsonb (cost=0.00..153.43 rows=10000 width=0) (actual time=0.021..0.021 rows=1 loops=1)  
  5. Index Cond: ((user_info ->> 'user_name'::text) = '1_francs'::text)  
  6. Planning time: 0.091 ms  
  7. Execution time: 0.060 ms  
  8. (7 rows

根据上述执行计划看出走了索引,并且SQL时间下降到0.060ms。为更好地对比tbl_user_json、tbl_user_jsonb表基于键值查询的效率,计划根据user_info字段id键进行范围扫描对比性能,创建索引如下:

  1. mydb=> CREATE INDEX idx_gin_user_info_id ON tbl_user_json USING btree  
  2. (((user_info ->> 'id')::integer));  
  3. CREATE INDEX  
  4. mydb=> CREATE INDEX idx_gin_user_infob_id ON tbl_user_jsonb USING btree 

索引创建后,查询tbl_user_json表如下:

  1. mydb=> EXPLAIN ANALYZE SELECT id,user_info->'id',user_info->'user_name' 
  2. FROM tbl_user_json  
  3. WHERE (user_info->>'id')::int4>1 AND (user_info->>'id')::int4<10000;  
  4. Bitmap Heap Scan on tbl_user_json (cost=166.30..14178.17 rows=10329 width=68) (actual time=1.167..26.534 rows=9998 loops=1)  
  5. Recheck Cond: ((((user_info ->> 'id'::text))::integer > 1) AND (((user_info ->> 'id'::text))::integer < 10000))  
  6. Heap Blocks: exact=338  
  7. -> Bitmap Index Scan on idx_gin_user_info_id (cost=0.00..163.72 rows=10329 width=0) (actual time=1.110..1.110 rows=19996 loops= 1)  
  8. Index Cond: ((((user_info ->> 'id'::text))::integer > 1) AND (((user_info ->> 'id'::text))::integer < 10000)) 
  9. Planning time: 0.094 ms  
  10. Execution time: 27.092 ms  
  11. (7 rows

根据以上看出,查询表tbl_user_json的user_info字段id键值在1到10000范围内的记录走了索引,并且执行时间为27.092毫秒,接着测试tbl_user_jsonb表同样SQL的检索性能,如下所示:

  1. mydb=> EXPLAIN ANALYZE SELECT id,user_info->'id',user_info->'user_name'  
  2. FROM tbl_user_jsonb  
  3. Bitmap Heap Scan on tbl_user_jsonb (cost=158.93..14316.93 rows=10000 width=68) (actual time=1.140..8.116 rows=9998 loops=1)  
  4. Heap Blocks: exact=393  
  5. -> Bitmap Index Scan on idx_gin_user_infob_id (cost=0.00..156.43 rows=10000 width=0) (actual time=1.058..1.058 rows=18992 loops =1)  
  6. Planning time: 0.104 ms  
  7. Execution time: 8.656 ms  
  8. (7 rows

根据以上看出,查询表tbl_user_jsonb的user_info字段id键值在1到10000范围内的记录走了索引并且执行时间为8.656毫秒,从这个测试看出jsonb检索比json效率高。

从以上两个测试看出,正好验证了“json写入比jsonb快,但检索时比jsonb慢”的观点,值得一提的是如果需要通过key/value进行检索,例如以下:

  1. SELECT * FROM tbl_user_jsonb WHERE user_info @> '{"user_name": "2_francs"}'

这时执行计划为全表扫描,如下所示:

  1. mydb=> EXPLAIN ANALYZE SELECT * FROM tbl_user_jsonb WHERE user_info @> '{"user_name": "2_francs"}' 
  2. QUERY PLAN  
  3. ------------------------------------------------------------------------------------  
  4. Seq Scan on tbl_user_jsonb (cost=0.00..67733.00 rows=2000 width=143) (actual time=0.018..582.207 rows=1 loops=1)  
  5. Filter: (user_info @> '{"user_name": "2_francs"}'::jsonb)  
  6. Rows Removed by Filter: 1999999  
  7. Planning time: 0.065 ms  
  8. Execution time: 582.232 ms  
  9. (5 rows

从以上看出执行时间为582毫秒左右,在tbl_user_jsonb字段user_info上创建gin索引,如下所示:

  1. mydb=> CREATE INDEX idx_tbl_user_jsonb_user_Info ON tbl_user_jsonb USING gin(user_Info); 
  2. CREATE INDEX

索引创建后,再次执行以下,如下所示:

  1. Bitmap Heap Scan on tbl_user_jsonb (cost=37.50..3554.34 rows=2000 width=143) (actual time=0.079..0.080 rows=1 loops=1)  
  2. Recheck Cond: (user_info @> '{"user_name": "2_francs"}'::jsonb)  
  3. Heap Blocks: exact=1  
  4. -> Bitmap Index Scan on idx_tbl_user_jsonb_user_info (cost=0.00..37.00 rows=2000 width=0) (actual time=0.069..0.069 rows=1 loops=1)  
  5. Index Cond: (user_info @> '{"user_name": "2_francs"}'::jsonb)  
  6. Planning time: 0.094 ms  
  7. Execution time: 0.114 ms  
  8. (7 rows

从以上看出走了索引,并且执行时间下降到了0.114毫秒。

这部分内容测试了json、jsonb数据类型读写性能差异,验证了json写入时比jsonb快,但检索时比jsonb慢的观点。

三、全文检索支持JSON和JSONB

接下来我们来介绍PostgreSQL 10的一个新特性:全文检索支持json、jsonb数据类型。这部分我们会分两部分来说明,第一部分简单介绍PostgreSQL全文检索,第二部分演示全文检索对json、jsonb数据类型的支持。

1、PostgreSQL全文检索简介

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

如果没有使用专门的搜索引擎,大部检索需要通过数据库like操作匹配,这种检索方式主要缺点在于:

  • 不能很好的支持索引,通常需全表扫描检索数据,数据量大时检索性能很低;

  • 不提供检索结果排序,当输出结果数据量非常大时表现更加明显。

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

Tsvector

tsvector全文检索数据类型代表一个被优化的可以基于搜索的文档,将一串字符串转换成tsvector全文检索数据类型,如下:

  1. mydb=> SELECT 'Hello,cat,how are u? cat is smiling! '::tsvector;  
  2. tsvector  
  3. --------------------------------------------------  
  4. 'Hello,cat,how' 'are' 'cat' 'is' 'smiling!' 'u?'  
  5. (1 row) 

可以看到,字符串的内容被分隔成好几段,但通过::tsvector只是做类型转换,没有进行数据标准化处理,对于英文全文检索可通过函数to_tsvector进行数据标准化,如下所示:

  1. mydb=> SELECT to_tsvector('english','Hello cat,');  
  2. to_tsvector  
  3. -------------------  
  4. 'cat':2 'hello':1  
  5. (1 row) 

Tsquery

tsquery表示一个文本查询,存储用于搜索的词,并且支持布尔操作&、|、!,将字符串转换成tsquery,如下所示:

  1. mydb=> SELECT 'hello&cat'::tsquery;  
  2. tsquery  
  3. -----------------  
  4. 'hello' & 'cat'  
  5. (1 row) 

上述只是转换成tsquery类型,而并没有做标准化,使用to_tsquery函数可以执行标准化,如下所示:

  1. mydb=> SELECT to_tsquery( 'hello&cat' ); 

to_tsquery

一个全文检索示例如下,检索字符串是否包括hello和cat字符,本例中返回真。

  1. mydb=> SELECT to_tsvector('english','Hello cat,how are u') @@to_tsquery( 'hello&cat' ); 

检索字符串是否包含字符hello和dog,本例中返回假。

  1. mydb=> SELECT to_tsvector('english','Hello cat,how are u') @@ to_tsquery( 'hello&dog' );  
  2.  
  3. (1 row) 

有兴趣的读者可以测试tsquery的其他操作符,例如|、!等。

注意:这里使用了带双参数的to_tsvector函数,函数to_tsvector双参数的格式如下:

to_tsvector([ config regconfig , ] document text),本节to_tsvector函数指定了config参数为english,如果不指定config参数,则默认使用default_text_search_config参数的配置。

英文全文检索例子

下面演示一个英文全文检索示例,创建一张测试表并插入200万测试数据,如下所示:

  1. mydb=> CREATE TABLE test_search(id int4,name text);  
  2. CREATE TABLE  
  3. mydb=> INSERT INTO test_search(id,nameSELECT n, n||'_francs'  
  4. FROM generate_series(1,2000000) n;  
  5. INSERT 0 2000000 

执行以下SQL,查询test_search表name字段包含字符1_francs的记录。

  1. mydb=> SELECT * FROM test_search WHERE name LIKE '1_francs' 
  2. id | name  
  3. ----+----------  
  4. 1 | 1_francs  
  5. (1 row) 

执行计划如下:

  1. mydb=> EXPLAIN ANALYZE SELECT * FROM test_search WHERE name LIKE '1_francs' 
  2. QUERY PLAN  
  3. -------------------------------------------------------------------------------------Seq Scan on test_search (cost=0.00..38465.04 rows=204 width=18) (actual time=0.022..261.766 rows=1 loops=1)  
  4. Filter: (name ~~ '1_francs'::text)  
  5. Rows Removed by Filter: 1999999  
  6. Planning time: 0.101 ms  
  7. Execution time: 261.796 ms  
  8. (5 rows

以上执行计划走了全表扫描,执行时间为261毫秒左右,性能很低,接着创建索引,如下所示:

  1. mydb=> CREATE INDEX idx_gin_search ON test_search USING gin (to_tsvector('english',name)); 
  2. mydb=> SELECT * FROM test_search WHERE to_tsvector('english',name) @@ to_tsquery('english','1_francs'); 

再次查看执行计划和执行时间,如下所示:

  1. mydb=> EXPLAIN ANALYZE SELECT * FROM test_search WHERE to_tsvector('english',name) @@ Bitmap Heap Scan on test_search (cost=18.39..128.38 rows=50 width=36) (actual time=0.071..0.071 rows=1 loops=1)  
  2. Recheck Cond: (to_tsvector('english'::regconfig, name) @@ '''1'' & ''franc'''::tsquery)  
  3. Heap Blocks: exact=1  
  4. -> Bitmap Index Scan on idx_gin_search (cost=0.00..18.38 rows=50 width=0) (actual time=0.064..0.064 rows=1 loops=1)  
  5. Index Cond: (to_tsvector('english'::regconfig, name) @@ '''1'' & ''franc'''::tsquery)  
  6. Planning time: 0.122 ms  
  7. Execution time: 0.104 ms  
  8. (7 rows

创建索引后,以上查询走了索引并且执行时间下降到0.104毫秒,性能提升了3个数量级,值得一提的是如果SQL改成以下,则不走索引,如下所示:

  1. mydb=> EXPLAIN ANALYZE SELECT * FROM test_search WHERE to_tsvector(name) @@ to_tsquery('1_francs');  
  2. Seq Scan on test_search (cost=0.00..1037730.00 rows=50 width=18) (actual time=0.036..10297.764 rows=1 loops=1)  
  3. Filter: (to_tsvector(name) @@ to_tsquery('1_francs'::text))  
  4. Rows Removed by Filter: 1999999  
  5. Planning time: 0.098 ms  
  6. Execution time: 10297.787 ms  
  7. (5 rows

由于创建索引时使用的是to_tsvector('english',name)函数索引,带了两个参数,因此where条件中的to_tsvector函数带两个参数才能走索引,而to_tsvector(name)不走索引。

2、JSON、JSONB全文检索实践

在PostgreSQL 10版本之前全文检索不支持json和jsonb数据类型,10版本的一个重要特性是全文检索支持json和jsonb数据类型。

10版本与9.6版本to_tsvector函数的差异

先来看下9.6版本to_tsvector函数,如下:

  1. [postgres@pghost1 ~]$ psql francs francs  
  2. psql (9.6.3)  
  3. Type "help" for help.  
  4. mydb=> df *to_tsvector*  
  5. List of functions  
  6. Schema | Name | Result data type | Argument data types | Type  
  7. ------------+-------------------+------------------+---------------------+--------  
  8. pg_catalog | array_to_tsvector | tsvector | text | normal  
  9. pg_catalog | to_tsvector | tsvector | regconfig, text | normal  
  10. pg_catalog | to_tsvector | tsvector | text | normal  
  11. (3 rows

从以上看出9.6版本to_tsvector函数的输入参数仅支持text、text数据类型,接着看下10版本的to_tsvector函数,如下所示:

  1. [postgres@pghost1 ~]$ psql mydb pguser  
  2. psql (10.0)  
  3. pg_catalog | to_tsvector | tsvector | json | normal  
  4. pg_catalog | to_tsvector | tsvector | jsonb | normal  
  5. pg_catalog | to_tsvector | tsvector | regconfig, json | normal  
  6. pg_catalog | to_tsvector | tsvector | regconfig, jsonb | normal 

从以上看出,10版本的to_tsvector函数支持的数据类型增加了json和jsonb。

创建数据生成函数

为了便于生成测试数据,创建以下两个函数用来随机生成指定长度的字符串,创建random_range(int4, int4)函数如下:

  1. CREATE OR REPLACE FUNCTION random_range(int4, int4)  
  2. RETURNS int4  
  3. LANGUAGE SQL  
  4. AS $$  
  5. SELECT ($1 + FLOOR(($2 - $1 + 1) * random ))::int4;  
  6. $$; 

接着创建random_text_simple(length int4)函数,此函数会调用random_range(int4, int4)函数。

  1. CREATE OR REPLACE FUNCTION random_text_simple(length int4)  
  2. RETURNS text  
  3. LANGUAGE PLPGSQL  
  4. AS $$  
  5. DECLARE  
  6. possible_chars text := '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ' 
  7. output text := '' 
  8. i int4;  
  9. pos int4;  
  10. BEGIN  
  11. FOR i IN 1..length LOOP  
  12. pos := random_range(1, length(possible_chars));  
  13. output := output || substr(possible_chars, pos, 1);  
  14. END LOOP;  
  15. RETURN output 
  16. END
  17. $$; 

random_text_simple(length int4)函数可以随机生成指定长度字符串,如下随机生成含三位字符的字符串:

  1. mydb=> SELECT random_text_simple(3);  
  2. random_text_simple  
  3. --------------------  
  4. LL9  
  5. (1 row) 

随机生成含六位字符的字符串,如下所示:

  1. mydb=> SELECT random_text_simple(6);  
  2. B81BPW  
  3. (1 row) 

后面会用到这个函数生成测试数据。

创建JSON测试表

创建user_ini测试表,并通过random_text_simple(length int4)函数插入100万随机生成六位字符的字符串测试数据,如下所示:

  1. mydb=> CREATE TABLE user_ini(id int4 ,user_id int8,  
  2. user_name character varying(64),  
  3. create_time timestamp(6) with time zone default clock_timestamp);  
  4. SELECT r,round(random*1000000), random_text_simple(6)  
  5. FROM generate_series(1,1000000) as r;  
  6. INSERT 0 1000000 

创建tbl_user_search_json表,并通过row_to_json函数将表user_ini行数据转换成json数据,如下所示:

  1. mydb=> CREATE TABLE tbl_user_search_json(id serial, user_info json);  
  2. CREATE TABLE  
  3. mydb=> INSERT INTO tbl_user_search_json(user_info)  
  4. SELECT row_to_json(user_ini) FROM user_ini;  
  5. INSERT 0 1000000 

生成的数据如下:

  1. mydb=> SELECT * FROM tbl_user_search_json LIMIT 1;  
  2. id | user_info  
  3. ----+-----------------------------------------------------------------------------------------------  
  4. 1 | {"id":1,"user_id":186536,"user_name":"KTU89H","create_time":"2017-08-05T15:59:25.359148+08:00" 
  5. (1 row) 

JSON数据全文检索测试

使用全文检索查询表tbl_user_search_json的user_info字段中包含KTU89H字符的记录,如下所示:

  1. mydb=> SELECT * FROM tbl_user_search_json  
  2. WHERE to_tsvector('english',user_info) @@ to_tsquery('ENGLISH','KTU89H');  
  3. id | user_info  
  4. ----+---------------------------------------------------------------------------------------- 

以上SQL能正常执行说明全文检索支持json数据类型,只是上述SQL走了全表扫描性能低,执行时间为8061毫秒,如下所示:

  1. mydb=> EXPLAIN ANALYZE SELECT * FROM tbl_user_search_json  
  2. -----------------------------------------------------------------------------------  
  3. Seq Scan on tbl_user_search_json (cost=0.00..279513.00 rows=5000 width=104) (actual time=0.046..8061.858 rows=1 loops=1)  
  4. Filter: (to_tsvector('english'::regconfig, user_info) @@ '''ktu89h'''::tsquery)  
  5. Rows Removed by Filter: 999999  
  6. Planning time: 0.091 ms  
  7. Execution time: 8061.880 ms  
  8. (5 rows

创建如下索引:

  1. mydb=> CREATE INDEX idx_gin_search_json ON tbl_user_search_json USING  gin(to_tsvector('english',user_info));  
  2. CREATE INDEX 

索引创建后,再次执行以下SQL,如下所示:

  1. mydb=> EXPLAIN ANALYZE SELECT * FROM tbl_user_search_json WHERE to_tsvector('english',user_info) @@ to_tsquery('ENGLISH','KTU89H');  
  2. Bitmap Heap Scan on tbl_user_search_json (cost=50.75..7876.06 rows=5000 width=104) (actual time=0.024..0.024 rows=1 loops=1)  
  3. Recheck Cond: (to_tsvector('english'::regconfig, user_info) @@ '''ktu89h'''::tsquery)  
  4. Heap Blocks: exact=1  
  5. -> Bitmap Index Scan on idx_gin_search_json (cost=0.00..49.50 rows=5000 width=0) (actual time=0.018..0.018 rows=1 loops=1)  
  6. Index Cond: (to_tsvector('english'::regconfig, user_info) @@ '''ktu89h'''::tsquery)  
  7. Planning time: 0.113 ms  
  8. Execution time: 0.057 ms  
  9. (7 rows

从上述执行计划看出走了索引,并且执行时间降为0.057毫秒,性能非常不错。

这一小节前一部分对PostgreSQL全文检索的实现做了简单介绍,并且给出了一个英文检索的例子,后一部分通过示例介绍了PostgreSQL10的一个新特性,即全文检索支持json、jsonb类型。

四、总结

本文介绍了PostgreSQL的NoSQL特性,首先介绍了json和jsonb数据类型,之后通过示例对比json、jsonb数据类型读写性能差异,最后介绍了PostgreSQL全文检索对json、jsonb类型的支持(PostgreSQL 10新特性);值得一提的是,PostgreSQL对中文全文检索也是支持的,有兴趣的读者可自行测试。

【编辑推荐】

  1. 为什么开发人员必须要了解数据库锁?
  2. 阿里云数据库再添新成员,企业级MariaDB正式开卖!
  3. MySQL数据库性能优化的八种方式,你可能都没用过!
  4. 区块链,一种数据库技术
  5. 读完这篇文章,就基本搞定了Redis数据库
【责任编辑:庞桂玉 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

读 书 +白菜送彩金大全

程序员教程(第2版)

本书按照人事部、信息产业部全国计算机技术与软件专业技术资格(水平)考试程序员考试大纲编写,是对2004版的修订版,内容包括计算机系统、...

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊
博聚网