第30期:索引设计(全文索引中文处理) - 技术分享 - 新闻资讯 - 爱可生

第30期:索引设计(全文索引中文处理)

发布时间:2021-07-01 浏览次数:292

关键字:军工大数据数据库分布式数据库高可用

本篇是全文索引终篇,来细聊下 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_size 调小到 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: NULL
PLUGIN_LIBRARY_VERSION: NULL
        PLUGIN_AUTHOR: Oracle Corp
   PLUGIN_DESCRIPTION: Ngram Full-Text Parser
       PLUGIN_LICENSE: GPL
          LOAD_OPTION: ON
1 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: 0

mysql> 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: 0

mysql> 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: 0

mysql> 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)

关键字:军工大数据数据库分布式数据库高可用

相关推荐

产品试用 产品试用
400-820-6580 免费电话