PostgreSQL 十亿级模糊查询最佳实践

postgresql , 模糊查询 , 正则查询 , pg_trgm , bytea , gin , 函数索引

前模糊(有前缀的模糊)，后模糊(有后缀的模糊)，前后模糊(无前后缀的模糊)，正则匹配都属于文本搜索领域常见的需求。

postgresql拥有很强的文本搜索能力，除了支持全文检索，还支持模糊查询、正则查询。内置的pg_trgm插件是一般数据库没有的，可能很多人没有听说过。同时还内置了表达式索引、gin索引的功能。

不同的模糊查询需求，有不同的优化方法。

对于前模糊和后模糊，postgresql则与其他数据库一样，可以使用btree来加速。后模糊可以使用反转函数的函数索引来加速。

对于前后模糊和正则匹配，一种方法是使用pg_trgm插件，利用gin索引加速模糊和正则查询（输入3个或3个以上字符的模糊查询效果很好）。另一种方法是自定义gin表达式索引的方法，适合于定制的模糊查询。

1. 前模糊(有前缀的模糊)优化方法

使用b-tree可以支持前模糊的查询。

1.1 当使用类型默认的index ops class时，仅适合于collate="c"的查询（当数据库默认的lc_collate<>c时，索引和查询都需要明确指定collate "c"）。

索引、查询条件的collate必须一致才能使用索引。

例子

1.2 当数据库默认的lc_collate<>c时，还有一种方法让b-tree索引支持模糊查询。使用对应类型的pattern ops，使用pattern ops将使用字符的查询方式而非binary的搜索方式。

文档中有如下解释

<a href="https://www.postgresql.org/docs/9.6/static/indexes-opclass.html">https://www.postgresql.org/docs/9.6/static/indexes-opclass.html</a>

使用类型对应的pattern ops，索引搜索不仅支持like的写法，还支持规则表达式的写法，如下：

2. 后模糊(有后缀的模糊)的优化方法

使用反转函数(reverse)索引，可以支持后模糊的查询。

2.1 当使用类型默认的index ops class时，仅适合于collate="c"的查询（当数据库默认的lc_collate&lt;&gt;c时，索引和查询都需要明确指定collate "c"）。

2.2 当数据库默认的lc_collate&lt;&gt;c时，还有一种方法让b-tree索引支持模糊查询。使用对应类型的pattern ops，使用pattern ops将使用字符的查询方式而非binary的搜索方式。

使用类型对应的pattern ops，索引搜索不仅支持like的写法，还支持规则表达式的写法。

3. 前、后模糊的合体优化方法

使用pg_trgm索引，可以支持前、后模糊的查询。

注意：

(有前缀的模糊)至少输入1个字符，(有后缀的模糊)至少输入2个字符，才有好的索引过滤效果。

如果要高效支持多字节字符(例如中文)，数据库lc_ctype不能为"c"，只有token分割正确效果才是ok的。(因为lc_ctype决定了多字节字符中什么是字:lc_ctype: character classification (what is a letter? its upper-case equivalent?))。

生成随机中文字符串的函数

生成随机数据

模糊查询

使用pg_trgm插件，支持前后模糊的查询。

如果要让pg_trgm高效支持多字节字符(例如中文)，数据库lc_ctype不能为"c"，只有token分割正确效果才是ok的。(因为lc_ctype决定了多字节字符中什么是字:lc_ctype: character classification (what is a letter? its upper-case equivalent?))。

建议输入3个或3个以上字符，否则效果不佳（后面会分析原因）。

postgresql 正则匹配的语法为 <code>字符串 ~ 'pattern'</code> 或 <code>字符串 ~* 'pattern'</code>

<a href="https://www.postgresql.org/docs/9.6/static/functions-matching.html">https://www.postgresql.org/docs/9.6/static/functions-matching.html</a>

正则匹配索引原理参考contrib/pg_trgm/trgm_regexp.c

首先，pg_trgm将字符串的前端添加2个空格，末尾添加1个空格。

然后，每连续的3个字符为一个token，拆开。

最后，对token建立gin倒排索引。

查看字符串的token，可以使用如下方法。

使用pg_trgm时，如果要获得最好的效果，最好满足这些条件。

1. 有前缀的模糊查询，例如a%，至少需要提供1个字符。( 搜索的是token=' a' )

2. 有后缀的模糊查询，例如%ab，至少需要提供2个字符。( 搜索的是token='ab ' )

3. 前后模糊查询，例如%abcd%，至少需要提供3个字符。( 这个使用数组搜索，搜索的是token(s) 包含 {" a"," ab",abc,bcd,"cd "} )

原因是什么呢？

因为pg_trgm生成的token是三个字符，只有在以上三个条件下，才能匹配到对应的token。

当需要前后模糊搜索1个或者2个字符时，pg_trgm无法满足需求，但是我们可以使用表达式gin索引。

使用表达式，将字符串拆成1个单字，两个连续的字符的数组，对数组建立gin索引即可。

模糊查询和正则匹配都是找出完全符合条件的记录，还有一种需求是相似查询。

例如postgresql字符串，输入 p0stgresgl 也能根据相似度匹配到。

这里同样用到了pg_trgm插件，如果要支持中文，同样有这样的要求：

如果需要让pg_trgm支持中文相似查询，数据库lc_ctype不能为"c"，只有token分割正确效果才是ok的。(因为lc_ctype决定了多字节字符中什么是字:lc_ctype: character classification (what is a letter? its upper-case equivalent?))。

1. 如果只有前模糊查询需求(字符串 like 'xx%')，使用collate "c"的b-tree索引；当collate不为"c"时，可以使用类型对应的pattern ops(例如text_pattern_ops)建立b-tree索引。

2. 如果只有后模糊的查询需求(字符串 like '%xx' 等价于 reverse(字符串) like 'xx%')，使用collate "c"的reverse()表达式的b-tree索引；当collate不为"c"时，可以使用类型对应的pattern ops(例如text_pattern_ops)建立b-tree索引。

3. 如果有前后模糊查询需求，并且包含中文，请使用lc_ctype &lt;&gt; "c"的数据库，同时使用pg_trgm插件的gin索引。(只有token分割正确效果才是ok的。(因为lc_ctype决定了多字节字符中什么是字:lc_ctype: character classification (what is a letter? its upper-case equivalent?))。)

4. 如果有前后模糊查询需求，并且不包含中文，请使用pg_trgm插件的gin索引。

5. 如果有正则表达式查询需求，请使用pg_trgm插件的gin索引。

6. 如果有输入条件少于3个字符的模糊查询需求，可以使用gin表达式索引，通过数组包含的方式进行搜索，性能一样非常好。

1亿条记录，每条记录15个随机中文。测试前后模糊查询性能。

1. 生成测试数据

2. 创建索引

表和索引大小

3. 模糊查询性能测试

3.1 前模糊

响应时间：9毫秒

返回4701行

3.2 后模糊

响应时间：0.25毫秒

返回2行

3.3 前后模糊

响应时间：0.2毫秒

返回1行