PostgreSQL怎么支持丰富的NoSQL特性

2025-03-08 技术教程

PostgreSQL怎么支持丰富的NoSQL特性，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。

PostgreSQL不仅是关系型数据库，同时支持丰富的NoSQL特性，所以小编将介绍PostgreSQL的NoSQL特性。

一、JSON和JSONB数据类型

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

1、JSON类型简介

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

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

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

mydb=>CREATETABLEtest_json1(idserialprimarykey,namejson);CREATETABLE

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

mydb=>INSERTINTOtest_json1(name)VALUES('{"col1":1,"col2":"francs","col3":"male"}');INSERT01mydb=>INSERTINTOtest_json1(name)VALUES('{"col1":2,"col2":"fp","col3":"female"}');INSERT01

查询表test_json1数据：

mydb=>SELECT*FROMtest_json1;id|name----+------------------------------------------1|{"col1":1,"col2":"francs","col3":"male"}2|{"col1":2,"col2":"fp","col3":"female"}

2、查询JSON数据

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

mydb=>SELECTname->'col2'FROMtest_json1WHEREid=1;?column?----------"francs"(1row)

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

mydb=>SELECTname->>'col2'FROMtest_json1WHEREid=1;francs(1row)

3、JSONB与JSON差异

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

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

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

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

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

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

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

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

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

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

另外，jsonb会删除重复的键，仅保留***一个，如下所示：

mydb=>SELECT'{"id":1,"name":"francs","remark":"agoodguy!","name":"test"}'::jsonb;jsonb----------------------------------------------------{"id":1,"name":"test","remark":"agoodguy!"}(1row)

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

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

4、JSONB与JSON操作符

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

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

字符串是否作为顶层键值，如下所示：

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

删除json数据的键/值，如下所示：

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

5、JSONB与JSON函数

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

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

mydb=>SELECT*FROMjson_each('{"a":"foo","b":"bar"}');key|value-----+-------a|"foo"b|"bar"(2rows)

以文本形式返回结果，如下所示：

mydb=>SELECT*FROMjson_each_text('{"a":"foo","b":"bar"}');a|foob|bar(2rows)

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

mydb=>SELECT*FROMtest_copyWHEREid=1;id|name----+------1|a(1row)mydb=>SELECTrow_to_json(test_copy)FROMtest_copyWHEREid=1;row_to_json---------------------{"id":1,"name":"a"}(1row)

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

mydb=>SELECT*FROMjson_object_keys('{"a":"foo","b":"bar"}');json_object_keys------------------ab(2rows)

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

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

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

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

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

mydb=>SELECT'{"name":"James","email":"james@localhost"}'::jsonb-'email';?column?-------------------{"name":"James"}(1row)mydb=>SELECT'["red","green","blue"]'::jsonb-0;["green","blue"]

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

mydb=>SELECT'{"name":"James","contact":{"phone":"01234567890","fax":"01987543210"}}'::jsonb#-'{contact,fax}'::text;?column?---------------------------------------------------------{"name":"James","contact":{"phone":"01234567890"}}(1row)

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

mydb=>SELECT'{"name":"James","aliases":["Jamie","TheJamester","JMan"]}'::jsonb#-'{aliases,1}'::text;{"name":"James","aliases":["Jamie","JMan"]}(1row)

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

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

第二种方式是通过jsonb_set函数，语法如下：

jsonb_set(targetjsonb,pathtext[],new_valuejsonb[,create_missingboolean])

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

mydb=>SELECTjsonb_set('{"name":"francs","age":"31"}'::jsonb,'{age}','"32"'::jsonb,false);jsonb_setmydb=>SELECTjsonb_set('{"name":"francs","age":"31"}'::jsonb,'{sex}','"male"'::jsonb,true);

7、给JSONB类型创建索引

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

{"id":1,"user_id":1440933,"user_name":"1_francs","create_time":"2017-08-0316: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索引支持@>、?、 ?&、?|操作符，例如以下查询将会使用索引：

SELECT*FROMtbl_user_jsonbWHEREuser_info@>'{"user_name":"1_frans"}'

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

SELECT*FROMtbl_user_jsonbWHEREuser_info->>'user_name'='1_francs';

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

CREATEINDEXidx_gin_user_infob_user_nameONtbl_user_jsonbUSINGbtree((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万测试数据，如下：

mydb=>CREATETABLEuser_ini(idint4,user_idint8,user_namecharactervarying(64),create_timetimestamp(6)withtimezonedefaultclock_timestamp);CREATETABLEmydb=>INSERTINTOuser_ini(id,user_id,user_name)SELECTr,round(random*2000000),r||'_francs'FROMgenerate_series(1,2000000)asr;INSERT02000000

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

mydb=>CREATETABLEtbl_user_json(idserial,user_infojson);CREATETABLEmydb=>CREATETABLEtbl_user_jsonb(idserial,user_infojsonb);CREATETABLE

2、JSON与JSONB表写性能测试

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

mydb=>imingTimingison.mydb=>INSERTINTOtbl_user_json(user_info)SELECTrow_to_json(user_ini)FROMuser_ini;INSERT02000000Time:13825.974ms(00:13.826)

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

mydb=>INSERTINTOtbl_user_jsonb(user_info)SELECTrow_to_json(user_ini)::jsonbFROMuser_ini;INSERT02000000Time:20756.993ms(00:20.757)

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

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

查询tbl_user_json表的一条测试数据，如下：

mydb=>SELECT*FROMtbl_user_jsonLIMIT1;id|user_info---------+------------------------------------------------------------------------------------2000001|{"id":1,"user_id":1182883,"user_name":"1_francs","create_time":"2017-08-03T20:59:27.42741+08:00"}(1row)

3、JSON与JSONB表读性能测试

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

mydb=>EXPLAINANALYZESELECT*FROMtbl_user_jsonbWHEREuser_info->>'user_name'='1_francs';QUERYPLAN-------------------------------------------------------------------------------------SeqScanontbl_user_jsonb(cost=0.00..72859.90rows=10042width=143)(actualtime=0.023..524.843rows=1loops=1)Filter:((user_info->>'user_name'::text)='1_francs'::text)RowsRemovedbyFilter:1999999Planningtime:0.091msExecutiontime:524.876ms(5rows)

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

mydb=>CREATEINDEXidx_jsonbONtbl_user_jsonbUSINGbtree((user_info->>'user_name'));

再次执行上述查询，如下所示：

BitmapHeapScanontbl_user_jsonb(cost=155.93..14113.93rows=10000width=143)(actualtime=0.027..0.027rows=1loops=1)RecheckCond:((user_info->>'user_name'::text)='1_francs'::text)HeapBlocks:exact=1->BitmapIndexScanonidx_jsonb(cost=0.00..153.43rows=10000width=0)(actualtime=0.021..0.021rows=1loops=1)IndexCond:((user_info->>'user_name'::text)='1_francs'::text)Planningtime:0.091msExecutiontime:0.060ms(7rows)

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

mydb=>CREATEINDEXidx_gin_user_info_idONtbl_user_jsonUSINGbtree(((user_info->>'id')::integer));CREATEINDEXmydb=>CREATEINDEXidx_gin_user_infob_idONtbl_user_jsonbUSINGbtree

索引创建后，查询tbl_user_json表如下：

mydb=>EXPLAINANALYZESELECTid,user_info->'id',user_info->'user_name'FROMtbl_user_jsonWHERE(user_info->>'id')::int4>1AND(user_info->>'id')::int4<10000;BitmapHeapScanontbl_user_json(cost=166.30..14178.17rows=10329width=68)(actualtime=1.167..26.534rows=9998loops=1)RecheckCond:((((user_info->>'id'::text))::integer>1)AND(((user_info->>'id'::text))::integer<10000))HeapBlocks:exact=338->BitmapIndexScanonidx_gin_user_info_id(cost=0.00..163.72rows=10329width=0)(actualtime=1.110..1.110rows=19996loops=1)IndexCond:((((user_info->>'id'::text))::integer>1)AND(((user_info->>'id'::text))::integer<10000))Planningtime:0.094msExecutiontime:27.092ms(7rows)

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

mydb=>EXPLAINANALYZESELECTid,user_info->'id',user_info->'user_name'FROMtbl_user_jsonbBitmapHeapScanontbl_user_jsonb(cost=158.93..14316.93rows=10000width=68)(actualtime=1.140..8.116rows=9998loops=1)HeapBlocks:exact=393->BitmapIndexScanonidx_gin_user_infob_id(cost=0.00..156.43rows=10000width=0)(actualtime=1.058..1.058rows=18992loops=1)Planningtime:0.104msExecutiontime:8.656ms(7rows)

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

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

SELECT*FROMtbl_user_jsonbWHEREuser_info@>'{"user_name":"2_francs"}';

这时执行计划为全表扫描，如下所示：

mydb=>EXPLAINANALYZESELECT*FROMtbl_user_jsonbWHEREuser_info@>'{"user_name":"2_francs"}';QUERYPLAN------------------------------------------------------------------------------------SeqScanontbl_user_jsonb(cost=0.00..67733.00rows=2000width=143)(actualtime=0.018..582.207rows=1loops=1)Filter:(user_info@>'{"user_name":"2_francs"}'::jsonb)RowsRemovedbyFilter:1999999Planningtime:0.065msExecutiontime:582.232ms(5rows)

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

mydb=>CREATEINDEXidx_tbl_user_jsonb_user_InfoONtbl_user_jsonbUSINGgin(user_Info);CREATEINDEX

索引创建后，再次执行以下，如下所示：

BitmapHeapScanontbl_user_jsonb(cost=37.50..3554.34rows=2000width=143)(actualtime=0.079..0.080rows=1loops=1)RecheckCond:(user_info@>'{"user_name":"2_francs"}'::jsonb)HeapBlocks:exact=1->BitmapIndexScanonidx_tbl_user_jsonb_user_info(cost=0.00..37.00rows=2000width=0)(actualtime=0.069..0.069rows=1loops=1)IndexCond:(user_info@>'{"user_name":"2_francs"}'::jsonb)Planningtime:0.094msExecutiontime:0.114ms(7rows)

从以上看出走了索引，并且执行时间下降到了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全文检索数据类型，如下：

mydb=>SELECT'Hello,cat,howareu?catissmiling!'::tsvector;tsvector--------------------------------------------------'Hello,cat,how''are''cat''is''smiling!''u?'(1row)

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

mydb=>SELECTto_tsvector('english','Hellocat,');to_tsvector-------------------'cat':2'hello':1(1row)

Tsquery

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

mydb=>SELECT'hello&cat'::tsquery;tsquery-----------------'hello'&'cat'(1row)

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

mydb=>SELECTto_tsquery('hello&cat');

to_tsquery

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

mydb=>SELECTto_tsvector('english','Hellocat,howareu')@@to_tsquery('hello&cat');

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

mydb=>SELECTto_tsvector('english','Hellocat,howareu')@@to_tsquery('hello&dog');f(1row)

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

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

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

英文全文检索例子

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

mydb=>CREATETABLEtest_search(idint4,nametext);CREATETABLEmydb=>INSERTINTOtest_search(id,name)SELECTn,n||'_francs'FROMgenerate_series(1,2000000)n;INSERT02000000

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

mydb=>SELECT*FROMtest_searchWHEREnameLIKE'1_francs';id|name----+----------1|1_francs(1row)

执行计划如下：

mydb=>EXPLAINANALYZESELECT*FROMtest_searchWHEREnameLIKE'1_francs';QUERYPLAN-------------------------------------------------------------------------------------SeqScanontest_search(cost=0.00..38465.04rows=204width=18)(actualtime=0.022..261.766rows=1loops=1)Filter:(name~~'1_francs'::text)RowsRemovedbyFilter:1999999Planningtime:0.101msExecutiontime:261.796ms(5rows)

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

mydb=>CREATEINDEXidx_gin_searchONtest_searchUSINGgin(to_tsvector('english',name));mydb=>SELECT*FROMtest_searchWHEREto_tsvector('english',name)@@to_tsquery('english','1_francs');

再次查看执行计划和执行时间，如下所示：

mydb=>EXPLAINANALYZESELECT*FROMtest_searchWHEREto_tsvector('english',name)@@BitmapHeapScanontest_search(cost=18.39..128.38rows=50width=36)(actualtime=0.071..0.071rows=1loops=1)RecheckCond:(to_tsvector('english'::regconfig,name)@@'''1''&''franc'''::tsquery)HeapBlocks:exact=1->BitmapIndexScanonidx_gin_search(cost=0.00..18.38rows=50width=0)(actualtime=0.064..0.064rows=1loops=1)IndexCond:(to_tsvector('english'::regconfig,name)@@'''1''&''franc'''::tsquery)Planningtime:0.122msExecutiontime:0.104ms(7rows)

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

mydb=>EXPLAINANALYZESELECT*FROMtest_searchWHEREto_tsvector(name)@@to_tsquery('1_francs');SeqScanontest_search(cost=0.00..1037730.00rows=50width=18)(actualtime=0.036..10297.764rows=1loops=1)Filter:(to_tsvector(name)@@to_tsquery('1_francs'::text))RowsRemovedbyFilter:1999999Planningtime:0.098msExecutiontime:10297.787ms(5rows)

由于创建索引时使用的是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函数，如下：

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

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

创建数据生成函数

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

CREATEORREPLACEFUNCTIONrandom_range(int4,int4)RETURNSint4LANGUAGESQLAS$$SELECT($1+FLOOR(($2-$1+1)*random))::int4;$$;

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

CREATEORREPLACEFUNCTIONrandom_text_simple(lengthint4)RETURNStextLANGUAGEPLPGSQLAS$$DECLAREpossible_charstext:='0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';outputtext:='';iint4;posint4;BEGINFORiIN1..lengthLOOPpos:=random_range(1,length(possible_chars));output:=output||substr(possible_chars,pos,1);ENDLOOP;RETURNoutput;END;$$;

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

mydb=>SELECTrandom_text_simple(3);random_text_simple--------------------LL9(1row)

随机生成含六位字符的字符串，如下所示：

mydb=>SELECTrandom_text_simple(6);B81BPW(1row)

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

创建JSON测试表

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

mydb=>CREATETABLEuser_ini(idint4,user_idint8,user_namecharactervarying(64),create_timetimestamp(6)withtimezonedefaultclock_timestamp);SELECTr,round(random*1000000),random_text_simple(6)FROMgenerate_series(1,1000000)asr;INSERT01000000

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

mydb=>CREATETABLEtbl_user_search_json(idserial,user_infojson);CREATETABLEmydb=>INSERTINTOtbl_user_search_json(user_info)SELECTrow_to_json(user_ini)FROMuser_ini;INSERT01000000

生成的数据如下：

mydb=>SELECT*FROMtbl_user_search_jsonLIMIT1;id|user_info----+-----------------------------------------------------------------------------------------------1|{"id":1,"user_id":186536,"user_name":"KTU89H","create_time":"2017-08-05T15:59:25.359148+08:00"}(1row)

JSON数据全文检索测试

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

mydb=>SELECT*FROMtbl_user_search_jsonWHEREto_tsvector('english',user_info)@@to_tsquery('ENGLISH','KTU89H');id|user_info----+----------------------------------------------------------------------------------------

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

mydb=>EXPLAINANALYZESELECT*FROMtbl_user_search_json-----------------------------------------------------------------------------------SeqScanontbl_user_search_json(cost=0.00..279513.00rows=5000width=104)(actualtime=0.046..8061.858rows=1loops=1)Filter:(to_tsvector('english'::regconfig,user_info)@@'''ktu89h'''::tsquery)RowsRemovedbyFilter:999999Planningtime:0.091msExecutiontime:8061.880ms(5rows)

创建如下索引：

mydb=>CREATEINDEXidx_gin_search_jsonONtbl_user_search_jsonUSINGgin(to_tsvector('english',user_info));CREATEINDEX

索引创建后，再次执行以下SQL，如下所示：

mydb=>EXPLAINANALYZESELECT*FROMtbl_user_search_jsonWHEREto_tsvector('english',user_info)@@to_tsquery('ENGLISH','KTU89H');BitmapHeapScanontbl_user_search_json(cost=50.75..7876.06rows=5000width=104)(actualtime=0.024..0.024rows=1loops=1)RecheckCond:(to_tsvector('english'::regconfig,user_info)@@'''ktu89h'''::tsquery)HeapBlocks:exact=1->BitmapIndexScanonidx_gin_search_json(cost=0.00..49.50rows=5000width=0)(actualtime=0.018..0.018rows=1loops=1)IndexCond:(to_tsvector('english'::regconfig,user_info)@@'''ktu89h'''::tsquery)Planningtime:0.113msExecutiontime:0.057ms(7rows)

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

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

看完上述内容是否对您有帮助呢？如果还想对相关知识有进一步的了解或阅读更多相关文章，请关注亿速云行业资讯频道，感谢您对亿速云的支持。