Fork me on GitHub

【搜索引擎】 PostgreSQL 10 实时全文检索和分词、相似搜索、模糊匹配实现类似Google搜索自动提示

需求分析

要通过PostgreSQL实现类似Google搜索自动提示的功能,例如要实现一个查询海量数据中的商品名字,每次输入就提示用户各种相关搜索选项,例如淘宝、京东等电商查询

思路

这个功能可以用 PostgreSQL的实时全文检索和分词、相似搜索、前模糊匹配等特性实现。具体策略是,定义一个搜索提示的最大数量。首先通过前模糊匹配查询获取数据,如果未满最大数量,则进行全文检索分词查询补偿,如果全文检索补偿查询的数据未满最大提示数量,最后就加入相识查询的结果。当然这里是一个简单的思路,复杂的还得根据实际需求实现。

构造数据

新建一张商品表,插入一千万条数据,name就是商品名字。

create table goods(id int, name varchar);

insert into goods select generate_series(1,10000000),md5(random()::varchar); 

一、前模糊匹配及优化

实现SQL,每次输入就作为前缀模糊查询:

select * from goods where name like '123%' ;

这个简单的前模糊匹配SQL,可以使用B-Tree来加速优化模糊查询。

未建立索引时查询"123%"的商品名字,执行计划显示耗时大约为575ms:

explain (analyze,verbose,timing,costs,buffers) select * from goods where name like '123%' ;
========================================
Gather  (cost=1000.00..136516.59 rows=1000 width=37) (actual time=1.390..572.857 rows=2364 loops=1)
  Output: id, name
  Workers Planned: 2
  Workers Launched: 2
  Buffers: shared hit=83334
  ->  Parallel Seq Scan on public.goods  (cost=0.00..135416.59 rows=417 width=37) (actual time=0.750..528.116 rows=788 loops=3)
        Output: id, name
        Filter: ((goods.name)::text ~~ '123%'::text)
        Rows Removed by Filter: 3332545
        Buffers: shared hit=83334
        Worker 0: actual time=1.032..511.776 rows=676 loops=1
          Buffers: shared hit=24201
        Worker 1: actual time=0.145..511.737 rows=755 loops=1
          Buffers: shared hit=26101
Planning time: 0.065 ms
Execution time: 573.157 ms

优化1,建立索引(lc_collate方式)

通过lc_collate方式建立索引、也就是B-Tree索引。

  • lc_collate (string) 是?#21103;?#21578;文本数据排序使用的区域
  • lc_collate (string) 是?#21103;?#21578;文本数据排序使用的区域

建立索引脚本如下

create index idx_c on goods(name collate "C");

执行计划显示耗时为10ms以内:

explain (analyze,verbose,timing,costs,buffers) select * from goods where name like '123%' collate "C";

优化2,建立索引(操作符类varchar_pattern_ops方式)

建立索引脚本如下

create index idx_varchar on goods(name varchar_pattern_ops);

执行计划显示耗时为5ms以内:

explain (analyze,verbose,timing,costs,buffers) select * from goods where name like '123%' collate "C";

======================================
Bitmap Heap Scan on public.goods  (cost=86.60..7681.10 rows=1000 width=37) (actual time=0.740..4.628 rows=2364 loops=1)
  Output: id, name
  Filter: ((goods.name)::text ~~ '123%'::text)
  Heap Blocks: exact=2330
  Buffers: shared hit=2351
  ->  Bitmap Index Scan on idx_varchar  (cost=0.00..86.35 rows=2179 width=0) (actual time=0.487..0.487 rows=2364 loops=1)
        Index Cond: (((goods.name)::text ~>=~ '123'::text) AND ((goods.name)::text ~<~ '124'::text))
        Buffers: shared hit=21
Planning time: 0.139 ms
Execution time: 4.891 ms

二、全文检索和分词(通过gin索引优化加速)

注意:全文检索和下面的相识搜索都需要pg_trgm插件。所以先要执行:

create extension pg_trgm; 

具体SQL如下,每次输入空格用&符号代替,最后接:*表示模糊检索。to_tsvector ,to_tsquery参阅postgresql全文检索文档。

SELECT name FROM goods WHERE to_tsvector('English',name) @@  to_tsquery('English','aaa&bbb&cc:*')

通过执行计划查看速度?#33322;?#36817;8秒

。。。。。。。
Planning time: 0.129 ms
Execution time: 7986.176 ms

通过gin索引来优化加速,这里to_tsvector('English',name)就是一个表达式索引。

CREATE INDEX name_idx ON goods USING GIN(to_tsvector('English',name));

优化后后的执行计划,速度为13毫秒左右:

explain (analyze,verbose,timing,costs,buffers) SELECT name FROM goods WHERE to_tsvector('English',name) @@  to_tsquery('English','aaa&bbb&cc:*')
=================================================
Bitmap Heap Scan on public.goods  (cost=88.04..109.24 rows=5 width=33) (actual time=17.343..17.353 rows=4 loops=1)
  Output: name
  Recheck Cond: (to_tsvector('english'::regconfig, (goods.name)::text) @@ '''aaa'' & ''bbb'' & ''cc'':*'::tsquery)
  Heap Blocks: exact=1
  Buffers: shared hit=473
  ->  Bitmap Index Scan on name_idx  (cost=0.00..88.04 rows=5 width=0) (actual time=17.334..17.334 rows=4 loops=1)
        Index Cond: (to_tsvector('english'::regconfig, (goods.name)::text) @@ '''aaa'' & ''bbb'' & ''cc'':*'::tsquery)
        Buffers: shared hit=472
Planning time: 0.222 ms
Execution time: 13.381 ms

三、相似搜索

具体实现SQL,通过查询结果可以看到越相似,相似度越小,可以看到,在搜索aaa bbb的时候搜索出了aaa b6b,这就是相似搜索。

SELECT name ,name <-> 'aaa bbb' FROM goods WHERE name <-> 'aaa bbb' < 0.7 LIMIT 10
aaa bbb             0
aaa bbb ccc         0.333333
aaa ccc bbb         0.333333
aaa bbb ccc ddd     0.5
aaa b6b ccc         0.666667
aaa bbb ccsdsd      0.466667
aaa                 0.5

PostgreSQL扩展知识

在第一种模糊查询中,可以使用关键字ILIKE替换LIKE, ILIKE表示字符串匹配时与大小?#27425;?#20851;。这是一个PostgreSQL扩展、并不是标准SQL语法。

参考

postgresql索引官方文档

posted @ 2019-07-11 22:20 monkjavaer 阅读(...) 评论(...) 编辑 收藏
耐克篮球多少钱
12生肖时时彩方法 安徽25选5开奖公告 吉林新快3 山东11选5前三值选走势图 排三现场直播开奖结果百度 三四连码奖上数打一肖 一肖中特免费公开资料马会 双色球中大奖吗 河南快三电脑走势图 排三组六最大遗漏 重庆时时开奖直播软件 电子游戏机 极速时时彩的技巧 新浪彩票频道首页 快乐十分投注技巧