MySql5.6全文索引及 5.7 中文索引插件ngram

MySql 5.6.4 之后开始支持 innodb 全文索引，之前版本只能使用MyISAM

直接开始：

创建一个表和索引

USE test;
 CREATE TABLE articles (
      id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
      title VARCHAR(200),
      body TEXT,
      FULLTEXT KEY  `idx_ft_tb` (title,body) 
    ) ENGINE=InnoDB CHARACTER SET utf8mb4;

表已经存在

ALTER TABLE articles ADD FULLTEXT INDEX ft_index (title,body) ;
或
CREATE FULLTEXT INDEX idx_ft_tbON articles (title,body)
-- 删除索引
DROP INDEX idx_ft_tb ON test.articles ;
或
ALTER TABLE test.articles DROP INDEX idx_ft_tb ;

插入数据

 SET NAMES utf8mb4;

INSERT INTO articles (title,body) VALUES
    ('数据库管理','在本教程中我将向你展示如何管理数据库'),
    ('数据库应用开发','学习开发数据库应用程序'),
    ('项目 管理','在本教程中我将向你展示如何管理数据库'),
    ('计算机 应用开发 管理开发','学习开发数据库应用程序'),
    ('财务管理','在本教程中我将向你展示如何管理数据库'),
    ('python开发','学习开发数据库应用程序'),
    ('项目中 管理开发','在本教程中我将向你展示如何管理数据库'),
    ('财务管理','在 本 教程 中 我 将 向你 展示 如何 管理 数据库'),
    ('database manage','in this job i will tell you how to manage'),
    ('项目中 管理开发2','在本教程中我将向你展示如何管理数据库2'),
    ('项目管 管控','not u'),
    ('项目管控','not me'),
    ('项目 目管控','not us'),

查看生成的索引

SET GLOBAL innodb_ft_aux_table="test/articles";
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE ORDER BY doc_id, position;
注：如果修改了索引，可能会没有结果需要执行
OPTIMIZE TABLE articles

发现结果如图

数据库管理   2   2   1   2   0
在本教程中我将向你展示如何管理数据库  2   8   4   2   16
数据库应用开发 3   3   1   3   0
学习开发数据库应用程序 3   7   3   3   22
在本教程中我将向你展示如何管理数据库  2   8   4   4   14
计算机 5   5   1   5   0
应用开发    5   5   1   5   10
学习开发数据库应用程序 3   7   3   5   23
财务管理    6   9   2   6   0
在本教程中我将向你展示如何管理数据库  2   8   4   6   13
python开发    7   7   1   7   0
学习开发数据库应用程序 3   7   3   7   13
项目2 8   8   1   8   0
在本教程中我将向你展示如何管理数据库  2   8   4   8   15
财务管理    6   9   2   9   0
数据库 9   9   1   9   68
database    11  11  1   11  0
manage  11  11  1   11  9
job 11  11  1   11  24
tell    11  11  1   11  35
you 11  11  1   11  40
manage  11  11  1   11  42

三种查询方式

默认会使用自然语言方式

MATCH (col1,col2,...) AGAINST (expr [search_modifier])
 
    search_modifier:
      {
           IN NATURAL LANGUAGE MODE
         | IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
         | IN BOOLEAN MODE
         | WITH QUERY EXPANSION
      }

①NATURAL LANGUAGE MODE

查询带有指定word的文档。因“NATURAL LANGUAGE MODE”为默认全文检索模式，所以查询语句中可以省略该关键字
MATCH()返回一个相关系数（relevance value），查询结果会按relevance value值降序排列，即相关性最高的结果置于首位。relevance value根据以下条件计算

word是否在记录中出现
word在记录中出现的次数
word在索引字段中的数量
多少行记录包含该word

②BOOLEAN MODE

AGAINST()中关键字的前后字符会有特殊含义。BOOLEAN MODE全文检索支持以下操作符

+word必须存在
-word必须不存在
(no operator)该word可选，如果出现relevance value更高
distance 仅用于InnoDB表。查询多个单词之间的距离是否在distance（字节）内
> < 分别表示出现该word时增加和降低relevance value
~ 出现该word时relevance value变负值，用于制造噪音词（“noise” word）
* 表示以该字符串开头的word，写在word前不生效，word*才有效
'' '' 中的内容视作一个短语（整体）

③（NATURAL LANGUAGE MODE）WITH QUERY EXPANSION

全文检索扩展查询。当查询的关键词太短，需要隐含信息（implied knowledge）时比较有用。例如，当查询“database”时可能意味着“MySQL”、“Oracle”、“DB2”以及“RDBMS”等词语都应该匹配和返回。在查询语句中添加WITH QUERY EXPANSION或者IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION启用“blind query expansion”（也称“automatic relevance feedback”）。扩展查询分两步执行

根据原始的查询关键字表达式进行全文检索
根据第一步查询的返回结果分词后再进行一次全文检索
例

mysql> SELECT * from test.articles where MATCH(title,body) AGAINST ('项目中' IN NATURAL LANGUAGE MODE);

+----+-----------------+--------------------------------------+
| id | title           | body                                 |
+----+-----------------+--------------------------------------+
|  7 | 项目中 管理开发 | 在本教程中我将向你展示如何管理数据库 |
+----+-----------------+--------------------------------------+
1 row in set
返回的 “项目中 管理开发” 在使用扩展查询 会将自然查询分词后作为下一次的查询条件
mysql> SELECT * from test.articles where MATCH(title,body) AGAINST ('项目中'  WITH QUERY EXPANSION);

+----+--------------------------+--------------------------------------+
| id | title                    | body                                 |
+----+--------------------------+--------------------------------------+
|  7 | 项目中 管理开发          | 在本教程中我将向你展示如何管理数据库 |
|  4 | 计算机 应用开发 管理开发 | 学习开发数据库应用程序               |
|  1 | 数据库管理               | 在本教程中我将向你展示如何管理数据库 |
|  3 | 项目 管理                | 在本教程中我将向你展示如何管理数据库 |
|  5 | 财务管理                 | 在本教程中我将向你展示如何管理数据库 |
+----+--------------------------+--------------------------------------+
5 rows in set

blind query expansion通常会返回不相关的记录而极大地增加查询结果的“噪音”（noise），因此仅当查询语句表达式简短的情况下使用

试试索引好不好用

SELECT * from articles where MATCH(title,body) AGAINST ('项目');
无结果
SELECT * from articles where MATCH(title,body) AGAINST ('项目2');
有结果

从结果看出两个问题

Q1.索引文档并没有根据汉语分词，而是根据空格分隔，比如“项目2 管理” 被分成项目2 和管理，而“数据库管理”没有被分词。

Q2.分词中虽然以空格分开也没有出现，但空格分开后3个以下的词如“项目” 和 “管理”并没有生成索引词。

A1:

5.6版本只支持英文分词，英文都是以空格分开的（database manage），中文词都是没有空格的，所以出现了一整句中文作为一个词。
内嵌的文本解释器决定单词的开头和结尾，根据delimiter符号进行判断，比如逗号、空格、点号。如果不是根据delimiter分割的，比如中文，解释器就无法判断出单词的开头和结尾了
在5.7.6中可以使用插件ngram解释器来实现对中文、日文、韩文的支持，或者使用MeCab解释器来支持日文
也可以自己编写插件解释器。示例代码位于plugin/fulltext目录。

A2:

3个以下的词没有生成索引词是因为，默认最小是innodb_ft_min_token_size=3
也就是说，根据分隔符分开之后还得满足长度3以上才可以
innodb_ft_max_token_size=84 即当词的长度超过84时，只取84长度
对于MyISAM引擎为：ft_min_word_len 和 ft_max_word_len
这个参数可以使用命令 show variables like '%ft%'; 查看

ft_boolean_syntax   + -><()~*:""&|
ft_max_word_len 84
ft_min_word_len 4
ft_query_expansion_limit    20
ft_stopword_file    (built-in)
innodb_ft_aux_table test/articles
innodb_ft_cache_size    8000000
innodb_ft_enable_diag_print OFF
innodb_ft_enable_stopword   ON
innodb_ft_max_token_size    84
innodb_ft_min_token_size    3
innodb_ft_num_word_optimize 2000
innodb_ft_result_cache_limit    2000000000
innodb_ft_server_stopword_table 
innodb_ft_sort_pll_degree   2
innodb_ft_total_cache_size  640000000
innodb_ft_user_stopword_table

可以修改配置文件 C:\ProgramData\MySQL\MySQL Server 5.7\my.ini
innodb_ft_min_token_size=2 “项目管理”就可以生成 2个索引了
以上参数都会影响索引

5.7.6 支持了中文索引，只需使用插件ngram

先删除之前的索引

DROP INDEX idx_ft_tb ON test.articles ;

ALTER TABLE articles ADD FULLTEXT INDEX idx_ft_tb (title,body) 
WITH PARSER ngram;

使用命令查看生成的索引词

show variables like '%token_size%';
innodb_ft_max_token_size    84
innodb_ft_min_token_size    3
ngram_token_size    2

发现ngram_token_size 为 2 即汉语的词截取长度为2

数据  2   9   8   2   0
据库  2   9   8   2   3
库管  2   2   1   2   6
...
...
n开  7   7   1   7   5
开发  3   7   3   7   6
ou  10  10  1   10  41
ge  10  10  1   10  42
ho  7   10  2   10  44
ow  10  10  1   10  45

修改ngram_token_size的值是以下效果

ngram_token_size=1 : '信', '息', '系', '统' 
ngram_token_size=2 : '信息', '息系', '系统';
ngram_token_size=3 : '信息系', '息系统';
ngram_token_size=4 : '信息系统';

查询方式不变
①ngram Parser Term Search

在NATURAL LANGUAGE MODE检索模式下，查询关键字表达式被转换为若干个ngram词语的联合。例如，字符串“管理开发”（假设ngram_token_size=2）会被转换为“管理理开开发”。给出多行记录，这多条记录都匹配检索词语“管理理开开发”，只要包含其中之一即可，匹配其一即可返回

mysql> SELECT * from test.articles where MATCH(title,body) AGAINST ('管理开发'
);
+----+--------------------------+------------------------------------------------+
| id | title                    | body                                           |
+----+--------------------------+------------------------------------------------+
|  4 | 计算机 应用开发 管理开发 | 学习开发数据库应用程序                         |
|  7 | 项目中 管理开发          | 在本教程中我将向你展示如何管理数据库           |
| 10 | 项目中 管理开发2         | 在本教程中我将向你展示如何管理数据库2          |
|  2 | 数据库应用开发           | 学习开发数据库应用程序                         |
|  6 | python开发               | 学习开发数据库应用程序                         |
|  1 | 数据库管理               | 在本教程中我将向你展示如何管理数据库           |
|  3 | 项目 管理                | 在本教程中我将向你展示如何管理数据库           |
|  5 | 财务管理                 | 在本教程中我将向你展示如何管理数据库           |
|  8 | 财务管理                 | 在 本 教程 中 我 将 向你 展示 如何 管理 数据库 |
+----+--------------------------+------------------------------------------------+
9 rows in set

在BOOLEAN MODE检索模式下，查询关键字表达式被转换为一个ngram短语检索。例如，字符串“项目中管理”（假设ngram_token_size=2）会被转换为“项目目中中管管理”。给出多行记录，这多条记录都匹配检索词语“项目目中中管管理”。但是只有包含 “项目中管理” 的记录行匹配检索短语 '"项目目中中管管理"'，完全匹配才可返回。

mysql> SELECT * from test.articles where MATCH(title,body) AGAINST ('管理开发' IN BOOLEAN MODE);

+----+--------------------------+---------------------------------------+
| id | title                    | body                                  |
+----+--------------------------+---------------------------------------+
|  4 | 计算机 应用开发 管理开发 | 学习开发数据库应用程序                |
|  7 | 项目中 管理开发          | 在本教程中我将向你展示如何管理数据库  |
| 10 | 项目中 管理开发2         | 在本教程中我将向你展示如何管理数据库2 |
+----+--------------------------+---------------------------------------+
3 rows in set

②ngram Parser Wildcard Search
由于ngram FULLTEXT index仅包含ngram分词，而不包含词语的词首信息，所以通配符检索可能返回预想之外的结果。
如果通配符检索的前缀词语小于“ngram_token_size”，查询会返回包含以该前缀词语开头的ngram分词的所有索引行。例如，假设ngram_token_size=2，对于“管*”的检索会返回所有以“管”开头的记录。

mysql> SELECT * from test.articles where MATCH(title,body) AGAINST ('管*
' in boolean mode);
+----+--------------------------+------------------------------------------------+
| id | title                    | body                                           |
+----+--------------------------+------------------------------------------------+
|  1 | 数据库管理               | 在本教程中我将向你展示如何管理数据库           |
|  3 | 项目 管理                | 在本教程中我将向你展示如何管理数据库           |
|  5 | 财务管理                 | 在本教程中我将向你展示如何管理数据库           |
|  8 | 财务管理                 | 在 本 教程 中 我 将 向你 展示 如何 管理 数据库 |
|  7 | 项目中 管理开发          | 在本教程中我将向你展示如何管理数据库           |
| 10 | 项目中 管理开发2         | 在本教程中我将向你展示如何管理数据库2          |
|  4 | 计算机 应用开发 管理开发 | 学习开发数据库应用程序                         |
+----+--------------------------+------------------------------------------------+
7 rows in set

如果通配符检索的前缀词语大于“ngram_token_size”，该前缀词语会被转换为一个ngram短语，与此同时，通配符符号会被忽略。例如，假设ngram_token_size=2，“管理开发 *”通配符检索会被转换为“管理开发”（“ngram Parser Term Search”的BOOLEAN MODE）。

mysql> SELECT * from test.articles where MATCH(title,body) AGAINST ('管理开发
*' in boolean mode);
+----+--------------------------+---------------------------------------+
| id | title                    | body                                  |
+----+--------------------------+---------------------------------------+
|  4 | 计算机 应用开发 管理开发 | 学习开发数据库应用程序                |
|  7 | 项目中 管理开发          | 在本教程中我将向你展示如何管理数据库  |
| 10 | 项目中 管理开发2         | 在本教程中我将向你展示如何管理数据库2 |
+----+--------------------------+---------------------------------------+
3 rows in set

③ngram Parser Phrase Search

短语检索会被转换为ngram短语检索。例如，检索短语“abc”会被转换为“ab bc”，包含“abc”和“ab bc”的记录都会被返回。检索短语“abc def”会被转换为“ab bc de ef”，包含“abcdef”的记录不会被返回。

mysql> SELECT * from test.articles where MATCH(title,body) AGAINST ('"项目管控"' in boolean mode);

+----+-------------+--------+
| id | title       | body   |
+----+-------------+--------+
| 11 | 项目管 管控 | not u  |
| 12 | 项目管控    | not me |
| 13 | 项目 目管控 | not us |
+----+-------------+--------+
3 rows in set

query expansion扩展查询同样适用于ngram parser

mysql> SELECT * from test.articles where MATCH(title,body) AGAINST ('项目管控
'  WITH QUERY EXPANSION);
+----+--------------------------+------------------------------------------------+
| id | title                    | body                                           |
+----+--------------------------+------------------------------------------------+
| 10 | 项目中 管理开发2         | 在本教程中我将向你展示如何管理数据库2          |
|  7 | 项目中 管理开发          | 在本教程中我将向你展示如何管理数据库           |
|  1 | 数据库管理               | 在本教程中我将向你展示如何管理数据库           |
|  3 | 项目 管理                | 在本教程中我将向你展示如何管理数据库           |
|  5 | 财务管理                 | 在本教程中我将向你展示如何管理数据库           |
| 12 | 项目管控                 | not me                                         |
| 13 | 项目 目管控              | not us                                         |
| 11 | 项目管 管控              | not u                                          |
|  4 | 计算机 应用开发 管理开发 | 学习开发数据库应用程序                         |
|  8 | 财务管理                 | 在 本 教程 中 我 将 向你 展示 如何 管理 数据库 |
|  2 | 数据库应用开发           | 学习开发数据库应用程序                         |
|  6 | python开发               | 学习开发数据库应用程序                         |
+----+--------------------------+------------------------------------------------+
12 rows in set

最后编辑于：2019.04.17 13:45:22

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 205,132评论 6赞 478
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 87,802评论 2赞 381
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 151,566评论 0赞 338
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,858评论 1赞 277
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,867评论 5赞 368
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,695评论 1赞 282
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 38,064评论 3赞 399
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,705评论 0赞 258
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 42,915评论 1赞 300
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,677评论 2赞 323
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,796评论 1赞 333
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,432评论 4赞 322
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 39,041评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,992评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,223评论 1赞 260
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 45,185评论 2赞 352
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,535评论 2赞 343

MySql5.6全文索引 及 5.7 中文索引插件ngram