PostgreSQL LIKE的那些事__笔者前段时间在写一个个人自用的项目时候，遇到一个需求，要从一个6百多万数量的表中，使用like ‘%keyword%’这种语法来找到需要的结果,虽然默认情况下，也不是不能接受（SSD存储+并行查询让这个事情实际的执行效率比想象中高得多），但毕竟对资源的瞬间消耗很大，让家里的小NAS有点不堪重负，因此对PostgreSQL对like的处理上，进行了诸多调查，来让这个事情的处理，可以看起来更漂亮些。_

PG中文社区 /

mdi-home

首页

社区新闻

中文文档

加入ACE

mdi-home

首页

mdi-chat-processing

社区新闻

mdi-book-open-variant

中文文档

mdi-account-multiple-check

加入ACE

mdi-file-multiple-outline

相关资料

mdi-blank

mdi-exit-to-app

退出账号

PostgreSQL LIKE的那些事

原作者：刘伟　　创作时间：2020-03-11 21:27:48+08　　

wangliyun 发布于2020-03-12 08:00:48　　

评论: 1　　

顶: 625　

踩: 610　

### **作者简介**
#### 刘伟，云和恩墨软件开发部研究院研究员；前微博DBA，主要研究方向为开源数据库，分布式数据库，擅长自动化运维以及数据库内核研究。

####笔者前段时间在写一个个人自用的项目时候，遇到一个需求，要从一个6百多万数量的表中，使用like ‘%keyword%’这种语法来找到需要的结果,虽然默认情况下，也不是不能接受（SSD存储+并行查询让这个事情实际的执行效率比想象中高得多），但毕竟对资源的瞬间消耗很大，让家里的小NAS有点不堪重负，因此对PostgreSQL对like的处理上，进行了诸多调查，来让这个事情的处理，可以看起来更漂亮些。

### **“太长不看”版**
#### 如果能确保like后面’%xxx%‘至少是三个字符，那么可以直接考虑trgm的gin索引。
#### 不纠结于非得like语法的，可以考虑具备良好分词器的全文索引。
##### 另外增补一个在下文中没有找到地方写的Tip：trgm的gin索引支持正则表达式方式的查询。

### **模式匹配的相关语法**
####  PostgreSQL中，like这个语法的周边，是很丰富的。
#### 注：以下说明主要来源为官方文档 Pattern Matching章节。
#### 首先当然是like本身。
#### like这个语法，是用通配符，来匹配字符串（或者从别的类型转换来的字符串）的，仅支持两个通配符，‘_’代表任意单个字符，‘%’代表任意0到多个字符，官方文档有个很好的例子：
```
'abc' LIKE 'abc'    true
'abc' LIKE 'a%'     true
'abc' LIKE '_b_'    true
'abc' LIKE 'c'      false
```
#### 对于需要匹配字符中’_‘,’%‘这俩字符的需求，可以使用LIKE ‘’ ESCAPE ‘’语法，在ESCAPE中指定转义字符，比如!为转义字符，就可以在LIKE中用来转义，举例如下：
```
select tablename from pg_tables where tablename like 'tbl!_%'ESCAPE '!';
```
#### 这里还有一个衍生需求，就是like匹配的时候，有时候需要忽略大小写，在PostgreSQL中，有个特殊的ILIKE关键字，专门用来做忽略大小写的LIKE，这个也是我项目中自用的方式。
#### 另外，PostgreSQL中有两个简写：‘’ 这个符号对应LIKE关键字，‘!’对应NOT LIKE，‘~~’这个符号对应ILIKE，‘!~~’对应NOT ILIKE，比方前面的SQL，还可以这么写（个人观点来说，我反对各种语法糖,这样会导致后期交接维护困难，这也是我早些年不使用Ruby语言的主要缘由之一）：
```
'abc' ~~ '_b_'    true
'abc' ~~ '_B_'    false
'abc' ~~* '_B_'   true
```
#### 除此之外，如果仅仅是想要做前缀匹配（比方like ’abc%’这种逻辑），PostgreSQL还有另外两个办法可以解决,^@语法，以及starts_with函数：
```
select 'abc' ^@ 'ab' true
select 'abc' ^@ 'b' false
select starts_with('abc','a') true
select starts_with('abc','b') false
```
#### 在LIKE之外，字符匹配当然不止这一个需求，还需要一个重要需求：正则表达式。
#### 对于正则表达式的语法我这里就不做过多介绍，单只讨论正则表达式在PostgreSQL中的使用。
#### 语法上，PostgreSQL采用SIMILAR TO 关键字，也就是这种用法：
```
'abc' SIMILAR TO 'abc'      true
'abc' SIMILAR TO 'a'        false
'abc' SIMILAR TO '%(b|d)%'  true
'abc' SIMILAR TO '(b|c)%'   false
```
#### 另外一个正则表达式的用途，从字符串提取需要的字符，则是通过函数 substring(string, pattern, escape-character)/substring(string from pattern for escape-character) 实现,第三个参数，是指定转义字符，方便匹配正则表达式的符号。
```
substring('foobar' , '%#"o_b#"%' , '#')   oob
substring('foobar' , '#"o_b#"%' , '#')    NULL
substring('foobar' from '%#"o_b#"%' for '#')   oob
substring('foobar' from '#"o_b#"%' for '#')    NULL
```
#### 前面我们提到LIKE有ILIKE进行忽略大小写的匹配，正则表达式这边，则是这种方式：

| 操作符 | 作用	| 举例
| :------:|  :------:| :------: | 
| ~ |  匹配正则表达式 | ‘thomas’ ~ ‘.thomas.’ | 
| ~* | 匹配正则表达式，忽略大小写 |  ‘thomas’ ~* ‘.thomas.’ | 
| !~  |  匹配正则表达式的NOT逻辑  | ‘thomas’ !~ ‘.thomas.’ | 
| !~* | 匹配正则表达式,忽略大小写的NOT逻辑 |  ‘thomas’ !~* ‘.thomas.’ |

#### 举例如下：
```
'abc' ~ 'abc'    true
'abc' ~ '^a'     true
'abc' ~ '(b|d)'  true
'abc' ~ '^(b|c)' false
```
#### 注：PostgreSQL中，函数使用的是POSIX正则表达式匹配，而SQL中实现的是SQL正则表达式，这两个有些细微的区别，下文讨论的，仅适用于POSIX正则表达式
如果有使用正则表达式比较多的人，应该知道正则表达式中的‘()’表达式，可以用来截取匹配到的字符中仅仅自己需要的字符内容。PostgreSQL也是通过substring做到的：
```
substring('foobar' from 'o.b')     oob
substring('foobar' from 'o(.)b')   o
```
#### 除了substring之外，PostgreSQL还有很多专用于正则表达式匹配的函数，这里仅做简单介绍，避免文章拖延过长：

#### 以上，就是语法层面的一些讨论了，接下来，让我们看看实际使用的时候，具体的优化如何处理。

| 函数 | 作用 | 举例 | 举例输出
| :------:|  :------:| :------: | :------: | 
| regexp_replace | 使用指定的字符替换匹配到的表达式  | regexp_replace(‘foobarbaz’, ‘b(…)’, ‘X\1Y’, ‘g’) | fooXarYXazY |
| regexp_match | 以数组形态输出匹配到的表达式 | regexp_match(‘foobarbequebaz’, ‘(bar)(beque)’)  | {bar,beque}
| regexp_matches | 以多行输出返回多个匹配到的表达式 | regexp_matches(‘foobarbequebazilbarfbonk’, ‘(b[b]+)(b[b]+)’,‘g’)  | {bar,beque},{bazil,barf} |
| regexp_split_to_table | 使用正则表达式切割行，每个匹配一行 | regexp_split_to_table(‘the quick brown’, ‘\s+’)  | the,quick,brown三行 |
| regexp_split_to_array | 使用正则表达式切割行为数组 | regexp_split_to_array(‘the quick brown’, ‘\s+’) | {the,quick,brown} |
#### 以上，就是语法层面的一些讨论了，接下来，让我们看看实际使用的时候，具体的优化如何处理。

### **模式匹配的相关优化措施**
#### 如果是小数据量的情况，优化也许没那么重要，但如果是生产环境中使用，一个性能糟糕的选择，可能就是隐患了，这一部分，就是针对前面提到的匹配方式的一些具体的优化措施。

### **并行查询**
#### 自从PostgreSQL实现并行查询之后，针对不频繁的大扫描/计算量的数据处理上，已经是不需要特别多余的关注了。并行查询并非后文重点讨论，这里仅介绍下其原理，以及相关的一些调整参数：
#### 并行查询来说，到现在的PG 12为止，包括一般查询，子查询，表连接等诸多地方都有已经实现，详细可以参考德哥的文章PostgreSQL 并行计算解说 汇总.
#### 其原理是，当解析到SQL可以并行化处理的时候，设置一个任务分发器（Gather），拆解单个执行任务为多个可以并行的任务，发送到并行查询的执行器（Worker），当Worker执行完成后，结果再回到Gather进行下一步处理。
#### 相关的参数主要有：

| 参数名称 | 参数说明 | 默认值
| :------:|  :------:| :------: |
| max_parallel_workers | 全局最多可以启动的worker上限 | 8 |
| max_parallel_workers_per_gather | 每个执行节点最大的可用worker数量 | 2 |
| min_parallel_index_scan_size | 索引查询时候，单个worker最小的工作量 | 64 |
| min_parallel_table_scan_size | 表数据查询的时候，单个worker最小的工作量 | 1024 |
#### 实际生产环境中，建议max_parallel_workers，max_parallel_workers_per_gather根据实际情况进行适当设置以提升性能。

mynas=> select count(1) from item_test where name like '(C96%';
 count
-------
536
(1 row)

Time: 745.689 ms
```
#### 如果加上普通索引：
```
mynas=> create index idx_itemtest on item_test(name varchar_pattern_ops);
CREATE INDEX
Time: 25311.067 ms (00:25.311)
mynas=> explain select count(1) from item_test where name like '(C96%';
                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Aggregate  (cost=85608.37..85608.38 rows=1 width=8)
   ->  Bitmap Heap Scan on item_test  (cost=978.63..85526.89 rows=32592 width=0)
         Filter: ((name)::text ~~ '(C96%'::text)
         ->  Bitmap Index Scan on idx_itemtest  (cost=0.00..970.48 rows=32592 width=0)
               Index Cond: (((name)::text ~>=~ '(C96'::text) AND ((name)::text ~<~ '(C97'::text))
(5 rows)
mynas=> select count(1) from item_test where name like '(C96%';
 count
-------
536
(1 row)

Time: 4.852 ms
```
#### 从单纯前缀查询看，可以看到B树索引效果显著，但对于后缀查询（‘%C96’），以及模糊查询（‘%C96%’），就没有用处了.
```
mynas=> select count(1) from item_test where name like '%C96';
 count
-------
1
(1 row)

Time: 989.560 ms
mynas=> select count(1) from item_test where name like '%C96%';
 count
-------
922
(1 row)

Time: 922.812 ms
```
#### 实际上，对于后缀查询，有个变通的方式，就是后缀变前缀，比方我要查询’%C96’后缀，可以创建name字段的反向（从字符串最后一个字符倒着创建）的B树索引，以支持”后缀“查询。
```
mynas=#  create index idx_itemtest on item_test(reverse(name) varchar_pattern_ops);
CREATE INDEX
mynas=> select count(1) from item_test where name like '%C96';
 count
-------
1
(1 row)

Time: 1051.475 ms (00:01.051)

mynas=> select count(1) from item_test where reverse(name) like '69C%';
 count
-------
1
(1 row)

Time: 3.034 ms
```
#### 顺带一提，对于ilike的情况如下：
```
mynas=> explain select count(1) from item_test where name ilike '(C96%';

QUERY PLAN
--------------------------------------------------------------------------------------------
 Aggregate  (cost=85522.33..85522.34 rows=1 width=8)
   ->  Bitmap Heap Scan on item_test  (cost=970.80..85519.07 rows=1304 width=0)
         Filter: ((name)::text ~~* '(C96%'::text)
         ->  Bitmap Index Scan on idx_itemtest  (cost=0.00..970.48 rows=32592 width=0)
               Index Cond: (((name)::text ~>=~ '('::text) AND ((name)::text ~<~ ')'::text))
mynas=> select count(1) from item_test where name ilike '(C96%';
 count
-------
536
(1 row)

Time: 63.164 ms
```
#### # 对于没有索引的情况
```
mynas=> drop index idx_itemtest;
DROP INDEX
mynas=> select count(1) from item_test where name ilike '(C96%';
 count
-------
   536
(1 row)

Time: 3946.207 ms (00:03.946)
```
#### **like与pg_trgm组件**
#### 如前文所述，普通的B树索引，仅能用于like前缀查询，而实际的业务场景中，‘%xxx%’这种模糊查询才是使用最广泛的，在PostgreSQL中，恰好有针对这种情况的优化，就是trgm组件，结合gin/gist索引，达到实现模糊搜索的目的。
#### 注1：pg_trgm仅支持like/ilike条件，如果有‘=’等其他需求，需要另外创建B树索引。
#### 注2：下面描述主要来源为官方文档pg_trgm节。
#### 先来说下pg_trgm。
#### pg_trgm的主要设计目的，是支持字符串的相似度检查，以及针对字符串的相似查询。其原理，是把字符串处理为三个字符长度的组（认为每个字符串自带两个前缀空格，和一个后缀空格）：比如’foobar’会被切割（select show_trgm(‘foobar’)）为{" f“,” fo“,”ar ",bar,foo,oba,oob}
#### 而gin/gist索引，就是在这样的三元组之上创建，可以明显认识到，这将会是个庞大的索引。
#### 下一个问题是，当创建索引的时候，是选择gin（倒排索引）还是选择gist（范围索引）？
#### 抛开原理不谈，让我们先测试下。
```
mynas=> create extension pg_trgm;
mynas=> create index idx_itemtest on item_test using gist(name gist_trgm_ops);
CREATE INDEX
mynas=> explain select count(1) from item_test where name like '%C96%';
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Aggregate  (cost=218395.90..218395.91 rows=1 width=8)
   ->  Bitmap Heap Scan on item_test  (cost=15665.13..217744.06 rows=260737 width=0)
         Recheck Cond: ((name)::text ~~ '%C96%'::text)
         ->  Bitmap Index Scan on idx_itemtest  (cost=0.00..15599.94 rows=260737 width=0)
               Index Cond: ((name)::text ~~ '%C96%'::text)
(5 rows)

mynas=> select count(1) from item_test where name like '%C96%';
 count
-------
922
(1 row)

Time: 557.345 ms
mynas=> drop index idx_itemtest;
DROP INDEX
mynas=> create index idx_itemtest on item_test using gin(name gin_trgm_ops);
CREATE INDEX

mynas=> explain select count(1) from item_test where name like '%C96%';
                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Aggregate  (cost=205151.49..205151.50 rows=1 width=8)
   ->  Bitmap Heap Scan on item_test  (cost=2420.71..204499.64 rows=260737 width=0)
         Recheck Cond: ((name)::text ~~ '%C96%'::text)
         ->  Bitmap Index Scan on idx_itemtest  (cost=0.00..2355.53 rows=260737 width=0)
               Index Cond: ((name)::text ~~ '%C96%'::text)
(5 rows)
mynas=> select count(1) from item_test where name like '%C96%';
 count
-------
922
(1 row)

Time: 6.019 ms
```
#### 数据上看，gin索引的sql响应时间，明显强于gist，那是不是说gin就绝对可取了呢？
#### 但实际上，gin有个”小“致命的问题：因为trgm切割出来的都是3长度的组，对于1，2这种长度的like内字符串，是没办法进行匹配的!
```
mynas=> explain select count(1) from item_test where name like '%C9%';
                                QUERY PLAN
--------------------------------------------------------------------------
 Aggregate  (cost=273622.43..273622.45 rows=1 width=8)
   ->  Seq Scan on item_test  (cost=0.00..270363.22 rows=1303684 width=0)
         Filter: ((name)::text ~~ '%C9%'::text)
(3 rows)
mynas=> select count(1) from item_test where name like '%C9%';
 count
-------
10039
(1 row)

Time: 1033.843 ms (00:01.034)
```
#### 实际上这个限制的说法更专业点是：
#### 1.有前缀的模糊查询，例如a%，至少需要提供1个字符。( 搜索的是token=’ a’ )
#### 2.有后缀的模糊查询，例如%ab，至少需要提供2个字符。( 搜索的是token=‘ab’ )
#### 3.前后模糊查询，例如%abcd%，至少需要提供3个字符。( 这个使用数组搜索，搜索的是token(s) 包含 {" a“,” ab“,abc,bcd,”cd "} )
#### 解决方案也很简单（至少看起来）：自己手动切字符串而非依赖trgm德哥的博客/PostgreSQL模糊搜索的最佳实践，建立基于”单字符“的gin索引。
```
mynas=> create or replace function split001(text) returns text[] as $$
mynas$> declare
mynas$>   res text[];
mynas$> begin
mynas$>   select regexp_split_to_array($1,'') into res;
mynas$>   for i in 1..length($1)-1 loop
mynas$>     res := array_append(res, substring($1,i,2));
mynas$>   end loop;
mynas$>   return res;
mynas$> end;
mynas$> $$ language plpgsql strict immutable;
CREATE FUNCTION

mynas=> create index idx_itemtest on item_test using gin(split001(name) );

mynas=> explain select count(1) from item_test where split001(name) @> array['C9'];
                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Aggregate  (cost=93090.33..93090.34 rows=1 width=8)
   ->  Bitmap Heap Scan on item_test  (cost=312.59..93008.85 rows=32592 width=0)
         Recheck Cond: (split001((name)::text) @> '{C9}'::text[])
         ->  Bitmap Index Scan on idx_itemtest  (cost=0.00..304.44 rows=32592 width=0)
               Index Cond: (split001((name)::text) @> '{C9}'::text[])
(5 rows)

mynas=> select count(1) from item_test where split001(name) @> array['C9'];
 count
-------
10039
(1 row)

Time: 15.190 ms
```
#### 效果显著。
#### 但总的来说，这个索引的维护工作量非常大，创建，以及后续的新数据插入时间代价都很大，除非确实认为单双字符检索很重要，一般不建议这种玩法。
#### **like与全文索引**
#### trgm这种办法，是另外建立索引，而如果不纠结于非得用LIKE这个关键字，而是使用PostgreSQL自带的全文索引功能，问题解决的办法就有更多的玩法了。
#### 但既然使用了全文索引，那么表结构就得变一下了，首先，是新增一个tsvector列，在其上建立一个gin索引。
```
mynas=> alter table item_test add column tk tsvector;
mynas=> update item_test set tk=to_tsvector(name) ;
mynas=> create index idx_idx_itemtest on item_test using gin (tk);
CREATE INDEX
Time: 19366.479 ms (00:19.366)
mynas=> explain select count(1) from item_test where tk @@ to_tsquery('%C96%');

QUERY PLAN
-----------------------------------------------------------------------------------------
 Aggregate  (cost=4545.10..4545.11 rows=1 width=8)
   ->  Bitmap Heap Scan on item_test  (cost=32.76..4542.35 rows=1099 width=0)
         Recheck Cond: (tk @@ to_tsquery('%C96%'::text))
         ->  Bitmap Index Scan on idx_idx_itemtest  (cost=0.00..32.49 rows=1099 width=0)
               Index Cond: (tk @@ to_tsquery('%C96%'::text))
(5 rows)

mynas=> select count(1) from item_test where tk @@ to_tsquery('C96');
 count
-------
786
(1 row)

Time: 4.172 ms
mynas=> select count(1) from item_test where name like '%C96%';
 count
-------
922
(1 row)

Time: 1610.243 ms (00:01.610)
```
#### 如果不考虑精确性（实际上有922而查询出来只有786），全文搜索效率是高于前面提到的trgm处理的gin索引，而这个“精确性”的主要缘由，和分词有关，默认的to_tsvector分词，是根据分隔符切分的，比如(C96)[xxx]yyy这种格式，可以分词出来，而04_C96_004.jpg这种名字，就不会被识别到。
#### 由于我这边的个人需求，单独处理分词器并不划算（以我的需求来说需要CJK三合一的分词器），因此后文中针对全文索引进一步的优化，我这边项目中并没有实际应用。
从技术上来说，这里的全文索引，看起来和前文提到的trgm/gin形式的索引，区别只是在是否单独处理拆分出来的词，但是，既然单独拆分出来了，就可以有更独立的方式处理了。
#### PostgreSQL从9.6开始，新出来一个在gin基础上，进一步优化了全文搜索功能的插件，RUM插件，postgrespro/rum，感兴趣的可以参考RUM索引接口(潘多拉魔盒)。

![CENTER_PostgreSQL_Community](/images/news/2016/pg_bot_banner.jpg)

评论:1　　

顶: 625　

踩: 610　

请在登录后发表评论，否则无法保存。

1# __ xiaowu 回答于 2024-04-24 10:43:47+08

读假如给我三天光明有感:https://www.nanss.com/xuexi/19680.html 科学实验报告:https://www.nanss.com/xuexi/18805.html 财务管理实习心得:https://www.nanss.com/gongzuo/19676.html 幼师培训心得:https://www.nanss.com/gongzuo/19667.html 韩国人口:https://www.nanss.com/shenghuo/19642.html 掩耳盗铃的故事:https://www.nanss.com/xuexi/19822.html 政治面貌怎么填:https://www.nanss.com/wenti/19845.html 居住证是什么:https://www.nanss.com/shenghuo/19868.html 教代会闭幕词:https://www.nanss.com/gongzuo/18637.html 小龙虾季节几月到几月:https://www.nanss.com/yinshi/18520.html 改进方案:https://www.nanss.com/gongzuo/18717.html 研究性学习报告:https://www.nanss.com/xuexi/18711.html 黄山四绝分别是什么:https://www.nanss.com/shenghuo/18369.html 橘子上火还是降火:https://www.nanss.com/yinshi/19726.html 歌颂祖国的文章:https://www.nanss.com/yuedu/18283.html 家长给老师的感谢信:https://www.nanss.com/shenghuo/18782.html 公厕管理制度:https://www.nanss.com/gongzuo/18719.html XL是多少尺寸:https://www.nanss.com/shenghuo/19877.html 个人简历模板范文:https://www.nanss.com/gongzuo/18670.html 乾隆之后的皇帝是谁:https://www.nanss.com/shenghuo/20162.html 三国演义读书心得:https://www.nanss.com/xuexi/19685.html 孕妇喝什么奶粉比较好:https://www.nanss.com/yuer/19548.html 志愿军观后感:https://www.nanss.com/xuexi/19717.html 马寅初名言:https://www.nanss.com/yulu/19241.html 长大后我就成了你作文:https://www.nanss.com/xuexi/19364.html 孔融让梨读后感:https://www.nanss.com/xuexi/19684.html 提高执行力:https://www.nanss.com/gongzuo/18856.html 租房广告:https://www.nanss.com/shenghuo/18286.html 下一步工作:https://www.nanss.com/gongzuo/18860.html 星座分析:https://www.nanss.com/xingzuo/18833.html