上一篇文章有谈到在Rails中实现模糊查找的相关事宜,其中一个比较尴尬的事情就是给模糊查找加上Gin索引之后,在搜索中文的场景下索引并不生效。这篇文章简单来聊聊在Ralis应用中模糊搜索中文的时候,如何让索引生效,以达到优化的目的。

话接上回

上一篇文章《Rails性能优化-简易模糊查找》谈到,当给posts表的title列,加上gin索引之后

class AddIndexForPostTitle < ActiveRecord::Migration[6.0]
  def change
    add_index :posts, :title, using: 'gin', opclass: :gin_trgm_ops
  end
end
> Post.count
   (40.3ms)  SELECT COUNT(*) FROM "posts"
=> 999985

在众多数据中用ASCII字符进行模糊查找,索引是可以命中的

英文索引生效.png

然而搜索中文却不行

333.png

接下来简单总结一些解决方案。

语系不对

模糊查找优化需要用到pg_trgm这个插件,而官方文档对他的描述大概如下

Screen Shot 2022-03-23 at 8.59.44 AM.png

所以要检测这个插件是否生效很简单

stone_development=# select show_trgm('abc');
        show_trgm
-------------------------
 {"  a"," ab",abc,"bc "}
(1 row)

然而如果是中文的话

stone_development=# select show_trgm('这是中文');
 show_trgm
-----------
 {}
(1 row)

似乎中文支持得不是很好。这个时候可以查看一下当前数据库的语系

stone_development=# \l stone_development
                             List of databases
       Name        | Owner | Encoding | Collate | Ctype | Access privileges
-------------------+-------+----------+---------+-------+-------------------
 stone_development | lan   | UTF8     | C       | C     |
(1 row)

可见,编码是UTF8,而Collate以及Ctype都是C,在这种情况下中文支持并不友好。而如果我重新创建一个数据库,并修改这两个参数,

stone_development=# CREATE DATABASE stone_development_with_utf8 WITH ENCODING 'UTF8' LC_COLLATE='en_US.UTF-8' LC_CTYPE='en_US.UTF-8' TEMPLATE=template0;
CREATE DATABASE

stone_development=# \l stone_development_with_utf8
                                       List of databases
            Name             | Owner | Encoding |   Collate   |    Ctype    | Access privileges
-----------------------------+-------+----------+-------------+-------------+-------------------
 stone_development_with_utf8 | lan   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(1 row)

stone_development=# \c stone_development_with_utf8;

现在Collate以及Ctype都是en_US.UTF8,这个值的意义大概如下

About svSE: This example for Unix systems sets the locale to Swedish (sv) as spoken in Sweden (SE). Other possibilities might include enUS (U.S. English) and fr_CA (French Canadian).

这个时候再来一下

stone_development_with_utf8=# CREATE EXTENSION pg_trgm;
CREATE EXTENSION

stone_development_with_utf8=# select show_trgm('这是中文');
                   show_trgm
------------------------------------------------
 {0xaf9d36,0xb30af1,0xc1adf2,0x1cf409,0x7616a5}

可见生效。容我把老的数据导入到新的数据库里面再看看搜索效果

> pg_dump stone_development > stone_development_backup.sql

> psql -d stone_development_with_utf8 < stone_development_backup.sql

database.yml文件要修改一下,换成新的数据库

# ...

development:
  <<: *default
  database: stone_development_with_utf8

# ...

在这种情况下对中文做模糊查找,似乎索引就可以生效了。

中文索引生效.png

~ 与 ~~ 是不一样的

通过修改数据库的CollateCtype,从C调整成en_US.UTF-8使我们在搜索中文的时候可以命中索引,达到优化的效果。然而在有些场景下表现也不是很完美。在PostgreSQL中,模糊查找有很多种表达方式LIKE, ILIKE, ~~, ~~*等等,还有个能够达到类似效果的符号~。而~~~是不一样的,且容易混淆。

  • ~~就是LIKE的另一种写法,主要用来做模糊查找。
  • ~~*就是ILIKE的另一种写法,主要用来做模糊查找,与LIKE类似,忽略大小写。
  • ~~是正则匹配,搜索条件能够更加多元化。
stone_development_with_utf8=# select COUNT(*) from posts where title ~ '黑客与画家'; -- 通过正则表达式查找
 count
-------
     1
(1 row)

stone_development_with_utf8=# select COUNT(*) from posts where title ~~ '%黑客与画家%'; -- LIKE模糊查找
 count
-------
     1
(1 row)

两者虽然结果一样,但是底层逻辑是不同的,搜索条件的写法也不太一样,我们前面添加的索引,在用正则表达式的方式进行中文模糊搜索的时候表现并不好。

正则失效.png

不过英文依旧支持得不错

正则表达式英文.png

看来数据库优化水挺深的,不同的搜索条件可能还要用不同的方式去优化。

转换成bytea再对比

上面的方式其实只适用于9.3版本之前的PostgreSQL,据说在这之前pgtrgm还不支持wchar。所以那个时候哪怕数据库的语系切换成`enUS.utf8`搭配pg_trgm插件也无法直接对中文做模糊查找,这个时候可以采用另一种方案。也就是PostgreSQL里面提供的Binary Data Types

这也是个挺有趣的东西,我们都知道,在计算机时代一切事物都是以0-1的形式存储,当然PostgreSQL也不能免俗。在同样的编码规则下,同样的字符串,在计算机里面会以相同的二进制形式存储,而我们平时用得比较多的编码方式是UTF8。如果pg_trgm本身没办法很好地识别中文,我们可以把需要模糊比较的字符串都转换成各自的二进制形式,接下来再比较这些二进制串有没有相似之处。简单演示一下

stone_development_with_utf8=# select '阿里云'::bytea;
        bytea
----------------------
 \xe998bfe9878ce4ba91
(1 row)

这里用的是十六进制的表现形式,毕竟纯二进制串太过于反人类了,一般都会转换成更好理解的十六进制串。当然也可以用八进制串。

stone_development_with_utf8=# SET bytea_output TO 'escape';
SET
stone_development_with_utf8=# select '阿里云'::bytea;
                bytea
--------------------------------------
 \351\230\277\351\207\214\344\272\221
(1 row)

扯远了,接下来把这些编码串还原成TEXT类型

stone_development_with_utf8=# select convert_from('\351\230\277\351\207\214\344\272\221', 'utf8');
 convert_from
--------------
 阿里云
(1 row)

stone_development_with_utf8=# select convert_from('\xe998bfe9878ce4ba91', 'utf8');
 convert_from
--------------
 阿里云
(1 row)

可见他们只是展示形式不同,底层是一样的。接下来把这个技巧跟pg_trgm结合一下。首先就要撤销原来的索引,并重新生成。

bin/rails db:rollback

重新创建索引

class AddIndexForPostTitle < ActiveRecord::Migration[6.1]
  def up
    execute <<-SQL
    CREATE INDEX post_title_gin_index ON posts USING GIN (text(title::bytea) gin_trgm_ops);
    SQL
  end

  def down
    execute <<-SQL
    DROP INDEX post_title_gin_index;
    SQL
  end
end
bin/rails db:migrate

接下来就可以搜索了

1. LIKE模糊查找

如果要以二进制的形式进行查找,那么原来的模糊查找语句需要调整成这个样子

Post.where("text(title::bytea) ~~ CONCAT('%', ltrim(text('黑客与画家'::bytea), '\\x'), '%')")

看看运行结果

模糊查找二进制索引生效.png

查询语句写起来要麻烦一些,不过搜索中文的时候索引也已经生效了。不知道这种方式能不能适用于基于正则表达式的中文搜索?

2. 正则表达式

如果要以正则表达式的形式进行模糊查找,那么模糊查找语句需要调整成这样

Post.where("text(title::bytea) ~ ltrim(text('黑客与画家'::bytea), '\\x')")

看看运行结果

正则表达式查找二进制索引生效.png

可喜可贺,哪怕是以正则的手段去搜索中文,索引也能够生效。把需要对比的中文字符串转换成二进制形式再进行对比,这种方式虽然稍微麻烦一些,但是表现还算稳定。不过呢,如果想要编写稍微复杂点的正则查询,那可能需要耗费一些功夫了。比如,正则模式是/黑客.+画家/的时候,则需要把查询语句写成这个样子

Post.where("text(title::bytea) ~ CONCAT(ltrim(text('黑客'::bytea), '\\x'), '.+', ltrim(text('画家'::bytea), '\\x'))")

虽说索引也能够生效,然而这种写法还是挺烦人的

复杂一点的正则.png

总结

Rails + PostgreSQL再搭配索引可以优化模糊查找的速度,然而这种做法在搜索中文的时候却有可能达不到优化的效果。这篇文章主要总结了一下要如何解决这类问题。简单提供了两种做法

  1. Ctype以及Collate换成en_US.UTF-8。如果原来的数据库这两个值是C,则需要创建新的数据库并迁移数据。这种做法比较简单,然而在用正则的方式编写查询来搜索中文字符串的时候,索引依旧无法生效。而且这种做法我在MacOS上怎么都无法生效(暂时不知道原因)。
  2. 把需要查找的中文转换成二进制的形式并进行查找,索引也需要重新建立。无论是常见的LIKEILIKE关键字还是正则匹配符号~,都能很好的兼容,然而如果采用这种做法,代码会比较难以维护。

具体选择哪种,还是要看自己的实际场景。

参考文献

  1. pg_trgm: https://www.postgresql.org/docs/current/pgtrgm.html
  2. Binary Data Types: https://www.postgresql.org/docs/current/datatype-binary.html
  3. 中文模糊查询性能优化 by PostgreSQL trgm: https://billtian.github.io/digoal.blog/2016/05/06/02.html