上一篇文章有谈到在 Rails 中实现模糊查找的相关事宜，其中一个比较尴尬的事情就是给模糊查找加上 Gin 索引之后，在搜索中文的场景下索引并不生效。这篇文章简单来聊聊在 Ralis 应用中模糊搜索中文的时候，如何让索引生效，以达到优化的目的。原文链接 https://step-by-step.tech/posts/fuzzy-searching-with-chinese-in-rails

话接上回

上一篇文章《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 字符进行模糊查找，索引是可以命中的

然而搜索中文却不行

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

语系不对

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

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

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 sv_SE: This example for Unix systems sets the locale to Swedish (sv) as spoken in Sweden (SE). Other possibilities might include en_US (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

# ...

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

~ 与 ~~ 是不一样的

通过修改数据库的Collate与Ctype，从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)

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

不过英文依旧支持得不错

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

转换成 bytea 再对比

上面的方式其实只适用于 9.3 版本之前的 PostgreSQL，据说在这之前 pg_trgm 还不支持 wchar。所以那个时候哪怕数据库的语系切换成en_US.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'), '%')")

看看运行结果

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

2. 正则表达式

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

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

看看运行结果

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

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

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

总结

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

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

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

参考文献

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