第30期：索引设计（全文索引中文处理）-白红宇

第30期：索引设计（全文索引中文处理）

阅读量：2141 次

发布时间：2019-04-30

本文共 12099 字，大约阅读时间需要 40 分钟。

本篇是全文索引终篇，来细聊下 MySQL 全文索引对中文如何处理。在了解 MySQL 全文索引如何处理中文之前，先来看看什么是分词。

MySQL 全文索引默认是基于单字节流处理的，也就是按照单词与停止词（默认空格或者标点符号）来划分各个关键词，并且把关键词的文档ID和位置保存到辅助表用于后期检索。这种对英文，数字类的单字节字符处理很好，比如“I am a boy!”，每个单词很明确的用空格分割，后期查询只需要按照以空格为分隔符的单词检索就行，这些我前面三篇文章已经详细讲过。但是这种分割方法对多字节字符比如中文不是很友好，对中文来说每个字就是单独的字，无规律的字可以组成词，但是各个词之间不需要按照空格来分割。举个例子：“为中国人自豪” ，这句话包含了三个词“为”，“中国人”，“自豪”。如果按照默认的全文索引处理，搜索其中任何子句，结果肯定是出不来。这也间接导致大家说 MySQL 的全文检索结果不准确，不靠谱，其实并非如此，主要是MySQL 全文索引对分词以及停止符界定有差异。例如下面，表 ft_ch ，有三条记录，怎么查都没有没有结果。

mysql> create table ft_ch(id int unsigned auto_increment primary key, s1 varchar(200),fulltext ft_s1(s1));Query OK, 0 rows affected (0.39 sec)mysql> select * from ft_ch where match(s1) against ('我是');Empty set (0.00 sec)mysql> select * from ft_ch where match(s1) against ('中国');Empty set (0.00 sec)mysql> select * from ft_ch where match(s1) against ('我是中');Empty set (0.01 sec)

但这张表其实有记录：

mysql> select * from ft_ch;+----+--------------------------------------+| id | s1                                   |+----+--------------------------------------+|  1 | 我是中国人你是哪里人？               ||  2 | 我是中国人，你是哪里人？             ||  3 | 我是中国人 你是哪里人？              |+----+--------------------------------------+3 rows in set (0.00 sec)

问题出在哪里呢？回顾下之前介绍的全文索引，可能就想到了，分词长度不够或者是停止词不对，分词长度见下面参数，停止词默认是空格或者标点符号。

mysql> show variables like '%innodb_ft_%token%';+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| innodb_ft_max_token_size | 84    || innodb_ft_min_token_size | 3     |+--------------------------+-------+2 rows in set (0.00 sec)

这两个参数定义了最小和最大的分词长度，在此范围内，包含边界的关键词都会被检索出来。之前的查询没有结果的原因就是 SQL 提供的关键词没有触发停止词的边界。那修改 SQL 里的关键词，再来查询一次，把检索关键词变为 “我是中国人”, 这个关键词刚好后面有一个停止词（空格或者逗号）。

mysql> select * from ft_ch where match(s1) against ('我是中国人');+----+--------------------------------------+| id | s1                                   |+----+--------------------------------------+|  2 | 我是中国人，你是哪里人？             ||  3 | 我是中国人 你是哪里人？              |+----+--------------------------------------+2 rows in set (0.00 sec)

结果是有了，但是不完整，ID 为 1 的记录没有被查出来。什么原因呢？分词的最小边界是不是太大了？的确如此，不过调小分词的大小，比如把参数 innodb_ft_min_token_siz e调小到 2 ，那必须得把数据重新录入，类似下面这样：

insert into ft_ch(s1) values (“我是中国人，你是哪里人？”);

这数据看起来很怪，按照设置的分词大小，并以空格分割这句话，明显这样数据就乱了，或者说，之后查出来的数据得重新组合处理。显然这样不可行。那 MySQL 有无办法按照国人的思维录入数据，并且还能正常查询出来结果吗？答案是肯定的。

MySQL 从 5.7 就原生提供了处理中文的插件 ngram 来解决这个问题。下面我来介绍下中文处理插件 Ngram .

查看 Ngram 插件是否正常加载，结果显示为 ON 代表加载成功。

mysql> select * from information_schema.plugins where plugin_name = 'ngram'\G*************************** 1. row ***************************           PLUGIN_NAME: ngram        PLUGIN_VERSION: 0.1         PLUGIN_STATUS: ACTIVE           PLUGIN_TYPE: FTPARSER   PLUGIN_TYPE_VERSION: 1.1        PLUGIN_LIBRARY: NULLPLUGIN_LIBRARY_VERSION: NULL         PLUGIN_AUTHOR: Oracle Corp    PLUGIN_DESCRIPTION: Ngram Full-Text Parser        PLUGIN_LICENSE: GPL           LOAD_OPTION: ON1 row in set (0.00 sec)

Ngram 插件只有一个单独的系统参数，那就是配置分词长度，默认为 2 ，也就是 2 个字为一个分词。

mysql> show variables like '%ngram%';+------------------+-------+| Variable_name    | Value |+------------------+-------+| ngram_token_size | 2     |+------------------+-------+1 row in set (0.00 sec)

那针对表 ft_ch ，把全文索引由默认改为 Ngram ，只需加上 with parser ngram 子句即可。

mysql> alter table ft_ch drop key ft_s1, add fulltext ft_s1_n( s1) with parser ngram;Query OK, 0 rows affected (0.35 sec)Records: 0  Duplicates: 0  Warnings: 0

为了验证 ngram 插件，我再插入一条没有标点符号的记录

mysql> insert into ft_ch(s1) values('我是中国人你是哪里人');Query OK, 1 row affected (0.01 sec)

接下来再次执行之前的查询，现在有结果了。

mysql>  select * from ft_ch where match(s1) against ('中国');+----+--------------------------------------+| id | s1                                   |+----+--------------------------------------+|  1 | 我是中国人你是哪里人？               ||  2 | 我是中国人，你是哪里人？             ||  3 | 我是中国人 你是哪里人？              ||  4 | 我是中国人你是哪里人                 |+----+--------------------------------------+4 rows in set (0.00 sec)

接下来再看看这些记录到底是怎么分词的，跟默认全文检索分词有什么不一样？针对表 ft_ch ，克隆一张表 ft_en .

mysql> create table ft_en like ft_ch;Query OK, 0 rows affected (0.40 sec)mysql> alter table ft_en drop key ft_s1_n, add fulltext ft_s1 (s1);Query OK, 0 rows affected (0.34 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> insert into ft_en select * from ft_ch;Query OK, 4 rows affected (0.02 sec)Records: 4  Duplicates: 0  Warnings: 0

开启监测表ft_en.

mysql> set global innodb_ft_aux_table = 'ytt/ft_en';Query OK, 0 rows affected (0.00 sec)

查看全文检索缓存表，可以看到分词记录是按照停止词来划分的，其实对中文来说，这样的索引很不完整。

mysql> select word,doc_id,position,doc_count from information_schema.innodb_ft_index_cache;+--------------------------------+--------+----------+-----------+| word                           | doc_id | position | doc_count |+--------------------------------+--------+----------+-----------+| 你是哪里人                     |      3 |       18 |         2 || 你是哪里人                     |      4 |       16 |         2 || 我是中国人                     |      3 |        0 |         2 || 我是中国人                     |      4 |        0 |         2 || 我是中国人你是哪里人           |      2 |        0 |         2 || 我是中国人你是哪里人           |      5 |        0 |         2 |+--------------------------------+--------+----------+-----------+6 rows in set (0.00 sec)

切换为 ngram 索引表

mysql> set global innodb_ft_aux_table = 'ytt/ft_ch';Query OK, 0 rows affected (0.00 sec)

查看全文索引缓存表，可以看到分词数据严格按照设定的个数来划分，没有任何冗余数据，也没有按照停止词来分，这点更适合对中文的处理。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;+--------+--------------+-------------+-----------+--------+----------+| WORD   | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |+--------+--------------+-------------+-----------+--------+----------+| 中国   |            6 |           6 |         1 |      6 |        6 || 人你   |            6 |           6 |         1 |      6 |       12 || 你是   |            6 |           6 |         1 |      6 |       15 || 哪里   |            6 |           6 |         1 |      6 |       21 || 国人   |            6 |           6 |         1 |      6 |        9 || 我是   |            6 |           6 |         1 |      6 |        0 || 是中   |            6 |           6 |         1 |      6 |        3 || 是哪   |            6 |           6 |         1 |      6 |       18 || 里人   |            6 |           6 |         1 |      6 |       24 |+--------+--------------+-------------+-----------+--------+----------+9 rows in set (0.00 sec)

以上结果还可以发现，标点符号并没有在分词里显示，Ngram 默认把这部分优化掉了（这也就是默认分词为 2 的原因）。

除了分词数据保存方式不同，其他和默认的全文索引没有任何异同。

例如看看内部索引表存储是否类似，查询出来结果和默认的也一样。

mysql> select table_id from information_schema.innodb_tables where name = 'ytt/ft_ch' into @tid;               Query OK, 1 row affected (0.00 sec)mysql> select table_id,name from information_schema.innodb_tables where name like concat('ytt/fts_',lpad(hex(@tid),16,'0'),'%');+----------+---------------------------------------------------+| table_id | name                                              |+----------+---------------------------------------------------+|     1431 | ytt/fts_0000000000000596_being_deleted            ||     1432 | ytt/fts_0000000000000596_being_deleted_cache      ||     1433 | ytt/fts_0000000000000596_config                   ||     1434 | ytt/fts_0000000000000596_deleted                  ||     1435 | ytt/fts_0000000000000596_deleted_cache            ||     1442 | ytt/fts_0000000000000596_00000000000002be_index_1 ||     1443 | ytt/fts_0000000000000596_00000000000002be_index_2 ||     1444 | ytt/fts_0000000000000596_00000000000002be_index_3 ||     1445 | ytt/fts_0000000000000596_00000000000002be_index_4 ||     1446 | ytt/fts_0000000000000596_00000000000002be_index_5 ||     1447 | ytt/fts_0000000000000596_00000000000002be_index_6 |+----------+---------------------------------------------------+11 rows in set (0.00 sec)

那把全文所以你变为 ngram 后，只是检索了是否有结果，至于结果是不是正确的，依然没有做校验。为了能更好的说明结果的准确性，我重新插入两行记录：

mysql> truncate ft_ch;Query OK, 0 rows affected (0.52 sec)mysql> insert into ft_ch(s1) values('我是中国人，你呢？');Query OK, 1 row affected (0.02 sec)mysql> insert into ft_ch(s1) values('我是外国人，你呢？');Query OK, 1 row affected (0.01 sec)

那接下来看看 ngram 插件对搜索结果的影响。match against 默认是自然语言模式，搜索关键词“中国人”，两行记录都被匹配了出来，但是明显 ID 为 2 的记录不符合检索关键词，为什么 MySQL 把不相干的记录也打印出来？原因在于，自然语言模式会把搜索关键词按照分词大小做一个并集，也就是说关键词"中国人"被切分为“中国”,“国人”两个关键词，MySQL 用 OR 的方式来输出结果，这样就把包含“中国”或者“国人”的记录全部打印出来，所以结果有两条！但是这并不是我们预期的结果。

mysql> select * from ft_ch where match(s1) against('中国人' in natural language mode);+----+-----------------------------+| id | s1                          |+----+-----------------------------+|  1 | 我是中国人，你呢？          ||  2 | 我是外国人，你呢？          |+----+-----------------------------+2 rows in set (0.00 sec)

为了让结果准确无误，就必须用布尔模式，在布尔模式下，只选关键词对应的结果，那下面结果就是对的。

mysql> select * from ft_ch where match(s1) against('中国人' in  boolean mode);+----+-----------------------------+| id | s1                          |+----+-----------------------------+|  1 | 我是中国人，你呢？          |+----+-----------------------------+1 row in set (0.00 sec)

那这时如果想任何结果都能匹配呢？比如，单个词的匹配？这时布尔模式也得不到想要的结果。那其实并不是结果不正确，而是分词太大。这条 SQL 的搜索关键词只有一个字，分词大小默认为 2 ，结果肯定不对。

mysql> select * from ft_ch where match(s1) against('国' in boolean mode);Empty set (0.01 sec)

此时可以修改分词为 1 ，在配置文件里修改参数 ngram_token_size=1 ；重启 MySQL 服务。

监测表 ft_ch

mysql> set global innodb_ft_aux_table='ytt/ft_ch';Query OK, 0 rows affected (0.01 sec)

修改分词大小，必须重建索引。可以看到分词数据把标点符号也包含进去了，这也就是 MySQL 的 ngram 插件分词默认为 2 的原因。

mysql> alter table ft_ch drop key ft_s1_n;Query OK, 0 rows affected (0.06 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> alter table ft_ch add fulltext ft_s1_n(s1) with parser ngram;Query OK, 0 rows affected (0.25 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> select * from information_schema.innodb_ft_index_table;+------+--------------+-------------+-----------+--------+----------+| WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |+------+--------------+-------------+-----------+--------+----------+| ，   |            2 |           3 |         2 |      2 |       15 || ，   |            2 |           3 |         2 |      3 |       15 || ？   |            2 |           3 |         2 |      2 |       24 || ？   |            2 |           3 |         2 |      3 |       24 || 中   |            2 |           2 |         1 |      2 |        6 || 人   |            2 |           3 |         2 |      2 |       12 || 人   |            2 |           3 |         2 |      3 |       12 || 你   |            2 |           3 |         2 |      2 |       18 || 你   |            2 |           3 |         2 |      3 |       18 || 呢   |            2 |           3 |         2 |      2 |       21 || 呢   |            2 |           3 |         2 |      3 |       21 || 国   |            2 |           3 |         2 |      2 |        9 || 国   |            2 |           3 |         2 |      3 |        9 || 外   |            3 |           3 |         1 |      3 |        6 || 我   |            2 |           3 |         2 |      2 |        0 || 我   |            2 |           3 |         2 |      3 |        0 || 是   |            2 |           3 |         2 |      2 |        3 || 是   |            2 |           3 |         2 |      3 |        3 |+------+--------------+-------------+-----------+--------+----------+18 rows in set (0.00 sec)

接下来之前的 SQL 重新执行，结果肯定有了。

mysql> select * from ft_ch where match(s1) against('国' in boolean mode);+----+-----------------------------+| id | s1                          |+----+-----------------------------+|  1 | 我是中国人，你呢？          ||  2 | 我是外国人，你呢？          |+----+-----------------------------+2 rows in set (0.00 sec)mysql> select * from ft_ch where match(s1) against('中国人' in boolean mode);+----+-----------------------------+| id | s1                          |+----+-----------------------------+|  1 | 我是中国人，你呢？          |+----+-----------------------------+1 row in set (0.00 sec)mysql> select * from ft_ch where match(s1) against('人，' in boolean mode);+----+-----------------------------+| id | s1                          |+----+-----------------------------+|  1 | 我是中国人，你呢？          ||  2 | 我是外国人，你呢？          |+----+-----------------------------+2 rows in set (0.00 sec)

关于 MySQL 的技术内容，你们还有什么想知道的吗？赶紧留言告诉小编吧！