共计 8785 个字符,预计需要花费 22 分钟才能阅读完成。
本篇是全文索引终篇,来细聊下 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: 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)
对于 MySQL 的技术内容,你们还有什么想晓得的吗?连忙留言通知小编吧!