PolarDB 开源版通过 pg_trgm GIN 索引实现高效率 `like '%xxx%'` 模糊查询

本文涉及的产品
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
简介: PolarDB 的云原生存算分离架构, 具备低廉的数据存储、高效扩展弹性、高速多机并行计算能力、高速数据搜索和处理; PolarDB与计算算法结合, 将实现双剑合璧, 推动业务数据的 价值产出, 将数据变成生产力. 本文将介绍PolarDB 开源版通过 pg_trgm GIN 索引实现高效率 `like '%xxx%'` 模糊查询

背景

PolarDB 的云原生存算分离架构, 具备低廉的数据存储、高效扩展弹性、高速多机并行计算能力、高速数据搜索和处理; PolarDB与计算算法结合, 将实现双剑合璧, 推动业务数据的
价值产出, 将数据变成生产力.

本文将介绍PolarDB 开源版通过 pg_trgm GIN 索引实现高效率 like '%xxx%' 模糊查询

测试环境为macos+docker, polardb部署请参考:

原理

pg_trgm将字符串前面加2个空格, 后面加1个空格, 按每连续的3个字符为一组进行切分, 生成一堆tokens, 例如hello被切分为{" h"," he",ell,hel,llo,"lo "}1

postgres=# select show_trgm('hello');  
            show_trgm              
---------------------------------  
 {"  h"," he",ell,hel,llo,"lo "}  
(1 row)  

对tokens创建gin索引, 在进行模糊搜索(甚至支持正则搜索)时, 将针对目标字符串条件也进行同样的token化处理(只是前后不需要再加空格, 除非输入了前缀或者后缀限定), 可以使用gin索引快速匹配到目标行.

更多原理可参考:

在PolarDB中使用pg_trgm GIN 索引实现高效率 like '%xxx%' 模糊查询

1、建表, 生成200万条测试文本

create table tbl (id int, info text);  
  
insert into tbl select id, md5(random()::text) from generate_series(1,1000000) id;  
insert into tbl select id, md5(random()::text) from generate_series(1,1000000) id;  

2、在没有索引的情况下, 进行模糊查询, 需要全表扫描, 耗时巨大.

explain (analyze,verbose,timing,costs,buffers) select * from tbl where info like '%abcd%';  
  
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tbl where info like '%abcd%';  
                                                  QUERY PLAN                                                     
---------------------------------------------------------------------------------------------------------------  
 Seq Scan on public.tbl  (cost=0.00..41665.50 rows=200 width=37) (actual time=2.505..522.958 rows=851 loops=1)  
   Output: id, info  
   Filter: (tbl.info ~~ '%abcd%'::text)  
   Rows Removed by Filter: 1999149  
   Buffers: shared hit=16645 read=22 dirtied=8334  
 Planning Time: 1.643 ms  
 Execution Time: 523.138 ms  
(7 rows)  

3、创建pg_trgm插件, 以及gin索引.

postgres=# create extension pg_trgm ;  
CREATE EXTENSION  
  
create index on tbl using gin (info gin_trgm_ops);  

4、使用pg_trgm GIN 索引实现高效率 like '%xxx%' 模糊查询

explain (analyze,verbose,timing,costs,buffers) select * from tbl where info like '%abcd%';  
  
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tbl where info like '%abcd%';  
                                                        QUERY PLAN                                                          
--------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on public.tbl  (cost=29.55..762.82 rows=200 width=37) (actual time=2.445..3.962 rows=851 loops=1)  
   Output: id, info  
   Recheck Cond: (tbl.info ~~ '%abcd%'::text)  
   Rows Removed by Index Recheck: 96  
   Heap Blocks: exact=926  
   Buffers: shared hit=946  
   ->  Bitmap Index Scan on tbl_info_idx  (cost=0.00..29.50 rows=200 width=0) (actual time=2.287..2.288 rows=947 loops=1)  
         Index Cond: (tbl.info ~~ '%abcd%'::text)  
         Buffers: shared hit=20  
 Planning Time: 0.239 ms  
 Execution Time: 4.112 ms  
(11 rows)  

性能提升100多倍.

参考

《重新发现PostgreSQL之美 - 16 like '%西出函谷关%' 模糊查询》

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
2天前
|
SQL 算法 关系型数据库
PolarDB-X的XPlan索引选择
对于数据库来说,正确的选择索引是基本的要求,选错索引轻则导致查询缓慢,重则导致数据库整体不可用。PolarDB-X存在多种不同的索引,局部索引、全局索引、列存索引、归档表索引。本文主要介绍一种CN上的局部索引算法:XPlan索引选择。
PolarDB-X的XPlan索引选择
|
13天前
|
关系型数据库 分布式数据库 PolarDB
电子书阅读分享《PolarDB开发者大会:拥抱开源 | 成就开源PolarDB开发者大会:拥抱开源 | 成就开源》
电子书阅读分享《PolarDB开发者大会:拥抱开源 | 成就开源PolarDB开发者大会:拥抱开源 | 成就开源》
12 3
|
14天前
|
关系型数据库 分布式数据库 PolarDB
电子书阅读分享《PolarDB开发者大会:拥抱开源 | 成就开源》
电子书阅读分享《PolarDB开发者大会:拥抱开源 | 成就开源》
|
15天前
|
关系型数据库 分布式数据库 PolarDB
电子书阅读分享《PolarDB开发者大会:拥抱开源 | 成就开源》
电子书阅读分享《PolarDB开发者大会:拥抱开源 | 成就开源》
17 4
|
17天前
|
关系型数据库 分布式数据库 PolarDB
电子书阅读分享《PolarDB开发者大会:拥抱开源 | 成就开源》
电子书阅读分享《PolarDB开发者大会:拥抱开源 | 成就开源》
11 1
|
24天前
|
关系型数据库 MySQL 数据处理
MySQL vs. PostgreSQL:选择适合你的开源数据库
在当今信息时代,开源数据库成为许多企业和开发者的首选。本文将比较两个主流的开源数据库——MySQL和PostgreSQL,分析它们的特点、优势和适用场景,以帮助读者做出明智的选择。
|
14天前
|
SQL 存储 关系型数据库
MySQL技能完整学习列表——1、数据库基础概念——1、关系型数据库(Relational Database)
MySQL技能完整学习列表——1、数据库基础概念——1、关系型数据库(Relational Database)
17 0
|
14天前
|
关系型数据库 分布式数据库 数据库
PolarDB for PostgreSQL报错问题之psql连接数据库报错如何解决
PolarDB for PostgreSQL是基于PostgreSQL开发的一款云原生关系型数据库服务,它提供了高性能、高可用性和弹性扩展的特性;本合集将围绕PolarDB(pg)的部署、管理和优化提供指导,以及常见问题的排查和解决办法。
|
20天前
|
SQL 算法 JavaScript
【数据库SQL server】关系型数据库的基本知识
【数据库SQL server】关系型数据库的基本知识
82 0
|
20天前
|
Cloud Native 关系型数据库 分布式数据库
2024最新一期中国数据库流行榜公布:阿里云PolarDB登顶
PolarDB登顶国产数据库流行榜,持续引领云原生数据库创新
2024最新一期中国数据库流行榜公布:阿里云PolarDB登顶

热门文章

最新文章

相关产品

  • 云原生数据库 PolarDB