作者
digoal
日期
2020-09-12
标签
PostgreSQL , 模糊查询 , 相似查询
背景
pg_bigm 采用2-grams粒度切词, 从功能上讲, 比pg_trgm优势明显, 支持高性能1或2个字的模糊和相似搜索.
同时增加了非严谨查询的开关, 在某些特定场合或者用户为性能可以妥协一定精准度时, 好处多多.
同时对wchar友好, 任何lc_ctype的数据库都能支持wchar切词. pg_trgm则需要修改头文件或者使用lc_cypte<>C的数据库来支持wchar的切词以及wchar的模糊查询 , 相似查询.
http://pgbigm.osdn.jp/pg_bigm_en-1-2.html
pg_bigm与pg_trgm对比
功能与特性 | pg_trgm | pg_bigm
---|---|---
切词粒度 | 前加2后加1个空格, 每3个连续字 | 前加2后加1个空格, 每2个连续字
索引接口 | gin, gist | gin
支持的语法 | LIKE (~~), ILIKE (~~*), ~, ~* | LIKE only
wchar支持情况 | lc_ctype <> C 或者 编译时注释 contrib/pg_trgm/trgm.h KEEPONLYALNUM macro定义, 否则不支持切分wchar的token | 任何时候都支持wchar
1或2个字的模糊查询 | 慢(因为切词粒度为3个字, 所以无法匹配), 必须全表扫描, 或者full index scan+RECHECK | 快
相似查询 | 支持 | 支持
like封装函数 | 不支持 | 支持 (likequery 函数自动将%_\进行转意， 不需要人为转意)
最大索引字段长度 | 228MB | 102MB
区分大小写 | 不区分 | 区分, like时匹配的记录区分大小写。
高性能开关(关闭recheck, 不严谨查询) | 不支持 | 支持 pg_bigm.enable_recheck=off
高性能开关(粗查, 不严谨查询) | 不支持 | 支持 pg_bigm.gin_key_limit 只查少量token (2-grams)
pg_bigm 比 pg_trgm 解决了几个切实问题
1、2和1个字 like的性能问题
```
like '%a%'
like '%ab%'
```
2、牺牲严谨结果的高性能开关
pg_bigm.enable_recheck
pg_bigm.gin_key_limit
3、wchar
db1=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+------------+-----------------------
db1 | postgres | UTF8 | C | C |
postgres | postgres | UTF8 | C | en_US.utf8 |
template0 | postgres | UTF8 | C | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
对于lc_ctype=c的数据库, pg_trgm无法正常生成wchar字符串的token.
```
db1=# select show_trgm('中国');
show_trgm
{}
(1 row)
```
而pg_bigm不受影响
```
db1=# select show_bigm('中国');
show_bigm
{中国,"国 "," 中"}
(1 row)
```
通过注释KEEPONLYALNUM宏, 可以让pg_trgm正常切分lc_ctype=C的wchar.
contrib/pg_trgm/trgm.h
```
//#define KEEPONLYALNUM
ifdef KEEPONLYALNUM
define ISWORDCHR(c) (t_isalpha(c) || t_isdigit(c))
define ISPRINTABLECHAR(a) ( isascii( (unsigned char)(a) ) && (isalnum( (unsigned char)(a) ) || (unsigned char)(a)==' ') )
else
define ISWORDCHR(c) (!t_isspace(c))
define ISPRINTABLECHAR(a) ( isascii( (unsigned char)(a) ) && isprint( (unsigned char)(a) ) )
endif
USE_PGXS=1 make uninstall
USE_PGXS=1 make clean
USE_PGXS=1 make distclean
USE_PGXS=1 make
USE_PGXS=1 make install
```
重新编译pg_trgm, 重启后, wchar字符串的token正常生成.
```
db1=# select show_trgm('中国');
show_trgm
{0xca1dc3,0x1b0419,0x780fe8}
(1 row)
```
如果你的数据库原来是lc_ctype=C, wchar例如中文无法实现模糊查询、相似查询, 那么可以使用以上方法重新编译pg_trgm, 并重新创建需要模糊查询、相似查询的对应字段gin索引, 就可以实现高效中文模糊查询、相似查询了.
不管是pg_bigm还是pg_trgm, 你都需要保命符
不管怎么说, 模糊查询和相似查询总比KV那种简单查询要复杂(例如需要recheck等), 在超高并发时可能容易把CPU打满, 现在很多应用都会考虑业务降级, 但是万一没有考虑到数据库这块就悲剧了, 数据库的锅谁来扛?
即使能说清楚是哪个SQL, 哪个模块造成的, DBA也需要承担一定责任.
所以保命要紧, 那就是防雪崩. 超高并发时, 不至于把CPU扛死.
防雪崩设置:
1、最有效的: 根据业务需要, 在全局, USER, DB级, 会话级或者事务级设置: 语句超时.
statement_timeout=xx;
PS : 语句级超时暂时不支持, 除非开启事务, 并在事务中设置. 实际上就是事务级别.
即使采用HINT, 也无法达到这个目的.
```
-- 无效.
-- 在模糊查询相关的sql里面加hint, 打死也不超过N毫秒.
/+ Set(statement_timeout ‘100ms’) / select xx from xx where xx like '%xxxx%';
-- 实际上并不能限制这个sql执行时间在100ms以内.
```
原因: 计时器是分开启动的, 通过HINT来修改这个参数时, 没有逻辑去启动计时器. 当然这个问题可以通过修改代码来支持, 只是目前不支持.
src/backend/tcop/postgres.c
```
/
* Start statement timeout timer, if enabled.
*
* If there's already a timeout running, don't restart the timer. That
* enables compromises between accuracy of timeouts and cost of starting a
* timeout.
/
static void
enable_statement_timeout(void)
{
/ must be within an xact /
Assert(xact_started);
if (StatementTimeout > 0)
{
if (!get_timeout_active(STATEMENT_TIMEOUT))
enable_timeout_after(STATEMENT_TIMEOUT, StatementTimeout);
}
else
{
if (get_timeout_active(STATEMENT_TIMEOUT))
disable_timeout(STATEMENT_TIMEOUT, false);
}
}
/
* Disable statement timeout, if active.
/
static void
disable_statement_timeout(void)
{
if (get_timeout_active(STATEMENT_TIMEOUT))
disable_timeout(STATEMENT_TIMEOUT, false);
}
```
2、对于pg_bigm可以设置关闭recheck, 同时通过降低搜索的token的个数来减少搜索的gin树中的分叉数, 但是结果不严谨, 而且降低token分叉后可能导致满足条件的结果更多(因为bitmap and的组数少了).
```
例如 like '%abcdef%'
db1=# select show_bigm('abcdef');
show_bigm
{" a",ab,bc,cd,de,ef,"f "}
(1 row)
正常应该搜索以上7组分叉, 通过pg_bigm.gin_key_limit 可以设置为低于7组.
```
小结
pg_bigm 和 hint在阿里云MyBase PG中都有, 确实贴心, 提高性能的同时还给PG DBA准备了保命符.
更重要的是, 阿里云不仅仅为PG内核提供代码保障, 同时还为所有阿里云集成的插件提供代码保障. 解决用户一切后顾之忧.
参考
https://github.com/ossc-db/pg_hint_plan
http://pgbigm.osdn.jp/pg_bigm_en-1-2.html
《PostgreSQL 模糊查询最佳实践 - (含单字、双字、多字模糊查询方法)》
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
