PostgreSQL-模糊查询

PostgreSQL-模糊查询!/bin/bash1模糊查询时,大多数情况下首先想到的是like‘%关键字%’或基于gin索引的正则表达式,gin至少需要三个字符才会使用索引,对于汉语来说十分不方便;2在海量数据的情况下,基于like和gin索引的正则表达式均不理想时,一般采用分词后进行查询.3分词存在两个弊端3.1词库的维护是比较繁重的工作.当词库中没有的关键词会导致查询结果不正确.3.2历史数据的维护工作不好处理

大家好,又见面了,我是你们的朋友全栈君。

函数已改进,请使用新版本函数,参看PostgreSQL 黑科技-递规二分法切分汉字

1 模糊查询时,大多数情况下首先想到的是like ‘%关键字%’或基于gin索引的正则表达式,gin至少需要三个字符才会使用索引,对于汉语来说十分不方便;

2 在海量数据的情况下,基于like和gin索引的正则表达式均不理想时,一般采用分词后进行查询.

3 分词存在两个弊端

3.1 词库的维护是比较繁重的工作.当词库中没有的关键词会导致查询结果不正确.

3.2 历史数据的维护工作不好处理.新增关键词时,历史数据并不包含些新的关键词,使用新关键词查询时无法查询到历史数据.

4 不使用like/不使用正则/不使用分词并保证查询快捷准确的另一种方法

此方法的缺点是比较浪费空间,不过在当前相比较下来以空间换取时间的方法是值得的.

4.1 首先清除文本中的标点符号

drop function if exists clear_punctuation(text);
create or replace function clear_punctuation(text) 
  returns text
as $$
    select regexp_replace($1,
	'[\ |\~|\`|\!|\@|\#|\$|\%|\^|\&|\*|\(|\)|\-|\_|\+|\=|\||\\|\[|\]|\{|\}|\;|\:|\"|\''|\,|\<|\.|\>|\/|\?|\:|\。|\;|\,|\:|\“|\”|\(|\)|\、|\?|\《|\》]'
	,'','g');
$$ language sql strict immutable;

4.2 每两个字符做为一个词处理,将字符串转换为tsvector

drop function if exists str_to_tsvector(text);
create or replace function str_to_tsvector(text) 
returns tsvector
as $$      
	declare
		v_count integer;
		v_txt text;
		v_txts text[];
		v_result tsvector;
	begin
		v_txt := clear_punctuation($1);
		--数组大小为字符数量-1
		v_count := length(v_txt)-1;
		if( v_count < 1 ) then
			raise exception '输入参数("%")去除标点符号后至少需要2个字符',$1;
		end if;
		for i in 1..v_count loop      
			v_txts := array_append(v_txts, substring(v_txt,i,2));      
		end loop;
		--tsvector类型要求去除重复并排序
		with cte1 as(
			select f from unnest(v_txts) as f group by f
		),cte2 as(
			select f from cte1 order by f
		)select array_to_tsvector(array_agg(f)) into v_result from cte2;
		return v_result;
	end;      
$$ language plpgsql strict immutable;

4.3 创建测试表

drop table if exists test_cond;
drop table if exists test;
create table test(
	objectid bigserial not null,					--唯一编号
	name text not null,								--名称
	newtime timestamptz default(now()) not null,	--首次添加时间
	lastime timestamptz,							--最后一次修改时间	
	constraint pk_test_objectid primary key (objectid)
);
--rum需要大量的计算和写入,保存速度非常慢,因此创建表时设置unlogged标记
--unlogged标记不是绝对安全的,因此和主表分开
--虽然浪费了一定的磁盘空间,但可以最大程度保证写入速度
--test_cond表损坏后,通过主表可以很容易的重建,不过机率非常小
--test_cond中的数据通过触发器更新,不需要操作这个表
create unlogged table test_cond(
	objectid bigserial not null,					--唯一编号
	keys tsvector not null,							--关键字
	constraint pk_test_cond_objectid primary key (objectid),
	constraint fk_test_cond_objectid foreign key(objectid) references test(objectid) on delete cascade
);
create index idx_test_cond_keys on test_cond using rum(keys rum_tsvector_ops);

4.4 创建关联触发器

--test_cond表设置了级联删除,所以不需要delete触发
drop trigger if exists tri_test_change on test;
drop function if exists tri_test_trigger();
create or replace function tri_test_trigger() 
returns trigger as $$ 
        begin 
			if (TG_OP = 'INSERT') then					
				insert into test_cond(objectid,keys) values(NEW.objectid,str_to_tsvector(NEW.name));
				return NEW;
			elsif (TG_OP = 'UPDATE') then
				update test_cond set keys=str_to_tsvector(NEW.name) where objectid=NEW.objectid;
				return NEW;			
			end if;
			 RETURN NULL; 
        end; 
$$ language 'plpgsql' SECURITY DEFINER;
--test_cond依赖test表,因此只能在test成功后再触好,只能设置为after
create trigger tri_test_change after INSERT or UPDATE on test 
	for each ROW EXECUTE PROCEDURE tri_test_trigger();

新版本函数,请参看PostgreSQL 黑科技-递规二分法切分汉字

--test_cond表设置了级联删除,所以不需要delete触发
drop trigger if exists tri_test_change on test;
drop function if exists tri_test_trigger();
create or replace function tri_test_trigger() 
returns trigger as $$ 
        begin 
			if (TG_OP = 'INSERT') then					
				insert into test_cond(objectid,keys) values(NEW.objectid,dichotomy_split_tsv(NEW.name));
				return NEW;
			elsif (TG_OP = 'UPDATE') then
				update test_cond set keys=dichotomy_split_tsv(NEW.name) where objectid=NEW.objectid;
				return NEW;			
			end if;
			 RETURN NULL; 
        end; 
$$ language 'plpgsql' SECURITY DEFINER;
--test_cond依赖test表,因此只能在test成功后再触好,只能设置为after
create trigger tri_test_change after INSERT or UPDATE on test 
	for each ROW EXECUTE PROCEDURE tri_test_trigger();

4.5 创建随机生成汉字

drop function if exists gen_random_zh(int,int);
create or replace function gen_random_zh(int,int)
	returns text
as $$
	select string_agg(chr((random()*(20901-19968)+19968 )::integer) , '')  from generate_series(1,(random()*($2-$1)+$1)::integer);
$$ language sql;

4.6 生成测试数据

每调一次ins_test插入100万数据,可以同时调用ins_test插入更多数据,以便验证模糊查询性能

drop function if exists ins_test();
create or replace function ins_test() 
	returns void 
as $$
	declare 
		v_index integer;
	begin
		v_index := 0;
		for i in 1..1000 loop	
			v_index := v_index + 1;
			insert into test(name)
				select gen_random_zh(8,32) as name from generate_series(1,1000);
			raise notice  '%', v_index;
		end loop;
	end;
$$ language plpgsql;
--每调一次ins_test插入100万数据
select ins_test();

4.7 验证触发器的update功能

update test set name='哈哈,我来验证了' where objectid=10000;

4.7 查询数据量

select count(*) from test;
select count(*) from test_cond;

5 模糊查询测试

5.1 创建查询转换函数

drop function if exists str_to_tsquery(text,boolean);
create or replace function str_to_tsquery(text,boolean default true) 
returns tsquery
as $$      
	declare
		v_count integer;
		v_txt text;
		v_txts text[];
		v_result tsquery;
	begin
		v_txt := clear_punctuation($1);
		--数组大小为字符数量-1
		v_count := length(v_txt)-1;
		if( v_count < 1 ) then
			raise exception '输入参数("%")去除标点符号后至少需要2个字符',$1;
		end if;
		for i in 1..v_count loop      
			v_txts := array_append(v_txts, substring(v_txt,i,2));      
		end loop;
		--tsquery类型要求去除重复并排序
		with cte1 as(
			select f from unnest(v_txts) as f group by f
		),cte2 as(
			select f from cte1 order by f
		)select string_agg(f, (case when $2 then '&' else '|' end ) )::tsquery into v_result from cte2;
		return v_result;
	end;      
$$ language plpgsql strict immutable;

5.2 模糊数据

关键字字数越多,查询越准确,并且查询速度

--因优先级问题,此语句可能会不走rum索引
explain (analyze,verbose,costs,buffers,timing) 
select f.* from test as f left join test_cond as s
on f.objectid=s.objectid
where name @@ (str_to_tsquery('価仴'))

建议采用下面的方式,保证查询使用rum索引

explain (analyze,verbose,costs,buffers,timing) 
select f.* from test as f left join test_cond as s
on f.objectid=s.objectid
where s.keys @@ (select str_to_tsquery('哈哈'));

explain (analyze,verbose,costs,buffers,timing) 
select f.* from test as f left join test_cond as s
on f.objectid=s.objectid
where s.keys @@ (select str_to_tsquery('哈我'));

explain (analyze,verbose,costs,buffers,timing) 
select f.* from test as f left join test_cond as s
on f.objectid=s.objectid
where s.keys @@ (select str_to_tsquery('我来'));

explain (analyze,verbose,costs,buffers,timing) 
select f.* from test as f left join test_cond as s
on f.objectid=s.objectid
where s.keys @@ (select str_to_tsquery('来验'));

explain (analyze,verbose,costs,buffers,timing) 
select f.* from test as f left join test_cond as s
on f.objectid=s.objectid
where s.keys @@ (select str_to_tsquery('验证'));

explain (analyze,verbose,costs,buffers,timing) 
select f.* from test as f left join test_cond as s
on f.objectid=s.objectid
where s.keys @@ (select str_to_tsquery('证了'));

explain (analyze,verbose,costs,buffers,timing) 
select f.* from test as f left join test_cond as s
on f.objectid=s.objectid
where s.keys @@ (select str_to_tsquery('哈哈,我来验证了'));

explain (analyze,verbose,costs,buffers,timing) 
select f.* from test as f left join test_cond as s
on f.objectid=s.objectid
where s.keys @@ (select str_to_tsquery('侒亩'));

新版本函数,请参看PostgreSQL 黑科技-递规二分法切分汉字

explain (analyze,verbose,costs,buffers,timing) 
select f.* from test as f left join test_cond as s
on f.objectid=s.objectid
where s.keys @@ (select dichotomy_split_tsq('哈哈'));

explain (analyze,verbose,costs,buffers,timing) 
select f.* from test as f left join test_cond as s
on f.objectid=s.objectid
where s.keys @@ (select dichotomy_split_tsq('哈我'));

explain (analyze,verbose,costs,buffers,timing) 
select f.* from test as f left join test_cond as s
on f.objectid=s.objectid
where s.keys @@ (select dichotomy_split_tsq('我来'));

explain (analyze,verbose,costs,buffers,timing) 
select f.* from test as f left join test_cond as s
on f.objectid=s.objectid
where s.keys @@ (select dichotomy_split_tsq('来验'));

explain (analyze,verbose,costs,buffers,timing) 
select f.* from test as f left join test_cond as s
on f.objectid=s.objectid
where s.keys @@ (select dichotomy_split_tsq('验证'));

explain (analyze,verbose,costs,buffers,timing) 
select f.* from test as f left join test_cond as s
on f.objectid=s.objectid
where s.keys @@ (select dichotomy_split_tsq('证了'));

explain (analyze,verbose,costs,buffers,timing) 
select f.* from test as f left join test_cond as s
on f.objectid=s.objectid
where s.keys @@ (select dichotomy_split_tsq('哈哈,我来验证了'));

explain (analyze,verbose,costs,buffers,timing) 
select f.* from test as f left join test_cond as s
on f.objectid=s.objectid
where s.keys @@ (select dichotomy_split_tsq('侒亩'));

5.3 验证级联删除功能

删除后再执行上面的sql查询均查不到数据

delete from test where objectid=10000;
``
版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/140035.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)


相关推荐

  • tcpdump教程_TCPDUMP

    tcpdump教程_TCPDUMPRST为重置报文段,它会导致TCP连接的快速拆迁,且不需要ack进行确认。1.针对不存在的端口的连请求客户端:#include#include#include#include#include#include#include#include#include#include#include#include#include#include

  • msf 漏洞扫描_漏洞扫描方案

    msf 漏洞扫描_漏洞扫描方案目录1msfconsole介绍1msfconsole介绍msfconsole简称msf是一款常用的安全测试工具,包含了常见的漏洞利用模块和生成各种木马,其提供了一个一体化的集中控制台,通过msfconsole,你可以访问和使用所有的metaslopit插件,payload,利用模块,post模块等等。msfconsole还有第三方程序的接口,比如nmap、sqlmap等,可以直接在msfconsole里面使用。kali可直接在命令使用:msfconsoleMsfconsole的系统

  • vue filters过滤器的统一封装「建议收藏」

    vue filters过滤器的统一封装「建议收藏」目录统一封装规则统一封装规则1、src文件夹下创建filters文件夹,然后再filters文件夹下创建index.js即:src/filters/index.js2、在main.js中引入src/filters/index.js文件,并在下方遍历调用filters中的方法,使用VUE.filters切记:将遍历代码写在newVUE()上方3、在src/utils文件夹下的文件中创建各种工具类方法4、在src/filters/index.js中引入utils中的方法进行调用src

  • redis避免缓存击穿_redis防止缓存击穿

    redis避免缓存击穿_redis防止缓存击穿Redis解决缓存穿透和缓存击穿的工具类

  • Python代码实现Excel转JSON

    Python代码实现Excel转JSON题记项目需求需要用到Excel转JSON,第一时间想到的就是尘封了将近一年的python,一直在JavaJava,python早忘光了,想立刻开始动手却又不敢,最后确认,用python来完成操作Excel有得天独厚的优势,只能硬着头皮上了。短短的代码,做了将近四个小时,中间复习了一下字典和列表,同时也因为其中遇到了一些奇奇怪怪的问题,凌晨一点多躺下,一身轻松。主要技术python3.8.6+字典/列表的运用+对Excel操作的库pandas其中python对Excel操作的库其实有很多,像我

  • C++ seekg()函数「建议收藏」

    C++ seekg()函数「建议收藏」seekg()是对输入流的操作seekp()是对输出流的操作seekg()Movesthereadpositioninastream.//移动在流中读的位置————————————————————————basic_istream<Elem,Tr>&seek…

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号