MyException - 我的异常网
当前位置:我的异常网» 操作系统 » PostgreSQL 实时高效率搜索 - 全文检索、模糊查询、

PostgreSQL 实时高效率搜索 - 全文检索、模糊查询、正则查询、相似查询、ADHOC查询

www.MyException.Cn  网友分享于:2013-12-18  浏览:0次
PostgreSQL 实时高效搜索 - 全文检索、模糊查询、正则查询、相似查询、ADHOC查询

点击有惊喜

阿里云幸运券分享给你,用券购买或者升级阿里云相应产品会有特惠惊喜哦!把想要买的产品的幸运券都领走吧!快下手,马上就要抢光了。


标签

 

PostgreSQL , 搜索引擎 , GIN , ranking , high light , 全文检索 , 模糊查询 , 正则查询 , 相似查询 , ADHOC查询


背景

字符串搜索是非常常见的业务需求,它包括:

1、前缀+模糊查询。(可以使用b-tree索引)

select * from tbl where col like 'ab%';  
  
或  
  
select * from tbl where col ~ '^ab';  

2、后缀+模糊查询。(可以使用reverse(col)表达式b-tree索引)

select * from tbl where col like '%ab';  
  
或  
  
select * from tbl where col ~ 'ab$';  
  
写法  
  
select * from tbl where reverse(col) like 'ba%';  
  
或  
  
select * from tbl where reverse(col) ~ '^ba';  

3、前后模糊查询。(可以使用pg_trgm和gin索引)

https://www.postgresql.org/docs/10/static/pgtrgm.html

select * from tbl where col like '%ab%';  
  
或  
  
select * from tbl where col ~ 'ab';  

4、全文检索。(可以使用全文检索类型以及gin或rum索引)

select * from tbl where tsvector_col @@ 'postgres & china | digoal:A' order by ts_rank(tsvector_col, 'postgres & china | digoal:A') limit xx;  
  
详细语法后面介绍  

5、正则查询。(可以使用pg_trgm和gin索引)

select * from tbl where col ~ '^a[0-9]{1,5}\ +digoal$';  

6、相似查询。(可以使用pg_trgm和gin索引)

select * from tbl order by similarity(col, 'postgre') desc limit 10;  

7、ADHOC查询,任意字段组合查询。(通过bloom index, multi-index bitmap scan, gin-index bitmap scan 等索引都可以实现)

select * from tbl where a=? and b=? or c=? and d=? or e between ? and ? and f in (?);  

通常来说,数据库并不具备3以后的加速能力,但是PostgreSQL的功能非常强大,它可以非常完美的支持这类查询的加速。(是指查询和写入不冲突的,并且索引BUILD是实时的。)

用户完全不需要将数据同步到搜索引擎,再来查询,而且搜索引擎也只能做到全文检索,并不你做到正则、相似、前后模糊这几个需求。

使用PostgreSQL可以大幅度的简化用户的架构,开发成本,同时保证数据查询的绝对实时性。

一、全文检索

全文检索中几个核心的功能:

词典、分词语法、搜索语法、排序算法、效率、命中词高亮等。

PostgreSQL都已经实现,并支持扩展。例如扩展词典、扩展排序算法等。

pic

支持4种文档结构(标题、作者、摘要、内容),可以在生成tsvector时指定。在一个tsvector中允许多个文档结构。

文档结构在ranking算法中,被用于计算权值,例如在标题中命中的词权值可以设更大一些。

pic

支持掩码,主要用于调和很长的文本,调和ranking的输出。

pic

通过设置不同文档结构权值,调和ranking的输出。

pic

词典

默认PG没有中文分词,但是好在我们可以基于text search框架扩展,例如开源的zhparser, jieba等中文分词插件。

https://github.com/jaiminpan/pg_jieba

https://github.com/jaiminpan/pg_scws

甚至可以通过pljava, plpython等来实现对中文的分词,这个实际上是对应编程体系内的分词能力,通过PostgreSQL的过程语言引入,是不是很炫酷。

《使用阿里云PostgreSQL zhparser中文分词时不可不知的几个参数》

《如何加快PostgreSQL结巴分词加载速度》

《PostgreSQL Greenplum 结巴分词(by plpython)》

分词介绍

1、parser,功能是将字符串转换为token(可以自定义parser)。

default parser的token类别如下:

pic

例子

SELECT alias, description, token FROM ts_debug('http://example.com/stuff/index.html');  
  alias   |  description  |            token               
----------+---------------+------------------------------  
 protocol | Protocol head | http://  
 url      | URL           | example.com/stuff/index.html  
 host     | Host          | example.com  
 url_path | URL path      | /stuff/index.html  

创建text parser的语法

https://www.postgresql.org/docs/10/static/sql-createtsparser.html

2、配合text search configuration 和dictionary,将token转换为lexemes

例如创建了一个同义词字典

postgres        pgsql  
postgresql      pgsql  
postgre pgsql  
gogle   googl  
indices index*  

然后用这个字典来将token转换为lexemes,转换后得到的是lexeme. (tsvector中存储的也是lexeme,并不是原始token)

mydb=# CREATE TEXT SEARCH DICTIONARY syn (template=synonym, synonyms='synonym_sample');  
mydb=# SELECT ts_lexize('syn','indices');  
 ts_lexize  
-----------  
 {index}  
(1 row)  
  
mydb=# CREATE TEXT SEARCH CONFIGURATION tst (copy=simple);  
mydb=# ALTER TEXT SEARCH CONFIGURATION tst ALTER MAPPING FOR asciiword WITH syn;  
mydb=# SELECT to_tsvector('tst','indices');  
 to_tsvector  
-------------  
 'index':1  
(1 row)  
  
mydb=# SELECT to_tsquery('tst','indices');  
 to_tsquery  
------------  
 'index':*  
(1 row)  
  
mydb=# SELECT 'indexes are very useful'::tsvector;  
            tsvector               
---------------------------------  
 'are' 'indexes' 'useful' 'very'  
(1 row)  
  
mydb=# SELECT 'indexes are very useful'::tsvector @@ to_tsquery('tst','indices');  
 ?column?  
----------  
 t  
(1 row)  

创建text dictionary的语法

https://www.postgresql.org/docs/10/static/sql-createtsdictionary.html

3、将lexemes存储为tsvector

text search configuration 决定了要存哪些东西。

convert过程中,parser得到的token依次与configuration配置的dictionary匹配,并存储从dictionary中对应的lexeme。

ALTER TEXT SEARCH CONFIGURATION tsconfig名  
    ADD MAPPING FOR token类型1 WITH 字典1, 字典2, 字典3;  
  
如果使用这个tsconfig来转换文本为tsvector,那么对于 token类型1,首先与字典1匹配,如果匹配上了,会存储字典1中对应的lexeme,如果没有对应上,则继续搜索字典2......  

创建text search configuration的语法

https://www.postgresql.org/docs/10/static/sql-createtsconfig.html

创建text search template的语法

https://www.postgresql.org/docs/10/static/sql-createtstemplate.html

4、控制参数

通常parser有一些控制参数,例如是否输出单字、双字等。例如zhparser这个parser的参数如下:

《使用阿里云PostgreSQL zhparser中文分词时不可不知的几个参数》

5、文档结构

标题、作者、摘要、内容

使用ABCD来表示。

 

点击有惊喜

文章评论

代码女神横空出世
代码女神横空出世
 程序员的样子
程序员的样子
聊聊HTTPS和SSL/TLS协议
聊聊HTTPS和SSL/TLS协议
初级 vs 高级开发者 哪个性价比更高?
初级 vs 高级开发者 哪个性价比更高?
老程序员的下场
老程序员的下场
那些性感的让人尖叫的程序员
那些性感的让人尖叫的程序员
Java 与 .NET 的平台发展之争
Java 与 .NET 的平台发展之争
2013年美国开发者薪资调查报告
2013年美国开发者薪资调查报告
5款最佳正则表达式编辑调试器
5款最佳正则表达式编辑调试器
旅行,写作,编程
旅行,写作,编程
科技史上最臭名昭著的13大罪犯
科技史上最臭名昭著的13大罪犯
2013年中国软件开发者薪资调查报告
2013年中国软件开发者薪资调查报告
程序员的一天:一寸光阴一寸金
程序员的一天:一寸光阴一寸金
如何区分一个程序员是“老手“还是“新手“?
如何区分一个程序员是“老手“还是“新手“?
编程语言是女人
编程语言是女人
看13位CEO、创始人和高管如何提高工作效率
看13位CEO、创始人和高管如何提高工作效率
当下全球最炙手可热的八位少年创业者
当下全球最炙手可热的八位少年创业者
为啥Android手机总会越用越慢?
为啥Android手机总会越用越慢?
什么才是优秀的用户界面设计
什么才是优秀的用户界面设计
不懂技术不要对懂技术的人说这很容易实现
不懂技术不要对懂技术的人说这很容易实现
程序员和编码员之间的区别
程序员和编码员之间的区别
10个调试和排错的小建议
10个调试和排错的小建议
那些争议最大的编程观点
那些争议最大的编程观点
10个帮程序员减压放松的网站
10个帮程序员减压放松的网站
我跳槽是因为他们的显示器更大
我跳槽是因为他们的显示器更大
如何成为一名黑客
如何成为一名黑客
十大编程算法助程序员走上高手之路
十大编程算法助程序员走上高手之路
漫画:程序员的工作
漫画:程序员的工作
Web开发人员为什么越来越懒了?
Web开发人员为什么越来越懒了?
总结2014中国互联网十大段子
总结2014中国互联网十大段子
Google伦敦新总部 犹如星级庄园
Google伦敦新总部 犹如星级庄园
老美怎么看待阿里赴美上市
老美怎么看待阿里赴美上市
“肮脏的”IT工作排行榜
“肮脏的”IT工作排行榜
程序猿的崛起——Growth Hacker
程序猿的崛起——Growth Hacker
程序员应该关注的一些事儿
程序员应该关注的一些事儿
我是如何打败拖延症的
我是如何打败拖延症的
程序员周末都喜欢做什么?
程序员周末都喜欢做什么?
Web开发者需具备的8个好习惯
Web开发者需具备的8个好习惯
做程序猿的老婆应该注意的一些事情
做程序猿的老婆应该注意的一些事情
写给自己也写给你 自己到底该何去何从
写给自己也写给你 自己到底该何去何从
要嫁就嫁程序猿—钱多话少死的早
要嫁就嫁程序猿—钱多话少死的早
一个程序员的时间管理
一个程序员的时间管理
我的丈夫是个程序员
我的丈夫是个程序员
60个开发者不容错过的免费资源库
60个开发者不容错过的免费资源库
程序员都该阅读的书
程序员都该阅读的书
中美印日四国程序员比较
中美印日四国程序员比较
程序员必看的十大电影
程序员必看的十大电影
每天工作4小时的程序员
每天工作4小时的程序员
亲爱的项目经理,我恨你
亲爱的项目经理,我恨你
软件开发程序错误异常ExceptionCopyright © 2009-2015 MyException 版权所有