mysql ngram全文检索引擎
ngram是什么 ？
全文检索引擎，全文解析器能够对文本进行分词特点
Mysql 5.6之前版本，只有myisam支持全文索引，5.6之后，Innodb和myisam均支持全文索引ngram全文解析器，用来支持中文、日文、韩文分词只有char、varchar、text类型字段能创建全文索引英文分词用空格，逗号；中文分词用 ngram_token_size 设定全文检索模式
自然语言模式(NATURAL LANGUAGE MODE)
自然语言模式是MySQL 默认的全文检索模式。自然语言模式不能使用操作符，不能指定关键词必须出现或者必须不能出现等复杂查询
通过MATCH AGAINST 传递某个特定的字符串来进行检，默认方式
BOOLEAN模式(BOOLEAN MODE)
BOOLEAN模式可以使用操作符，可以支持指定关键词必须出现或者必须不能出现或者关键词的权重高还是低等复杂查询
为检索的字符串增加操作符，如“+”表示必须包含，"-“不包含，”*" 表示通配符，字符串较小或出现在停词中，也不会被过滤掉+word必须存在-word必须不存在(no operator)该word可选，如果出现relevance value更高distance 仅用于InnoDB表。查询多个单词之间的距离是否在distance（字节）内> < 分别表示出现该word时增加和降低relevance value~ 出现该word时relevance value变负值，用于制造噪音词（“noise” word） 表示以该字符串开头的word，写在word前不生效，word*才有效 ‘’ ‘’ 中的内容视作一个短语（整体）
query expansion search（查询扩展搜索）
搜索字符串用于执行自然语言搜索，然后，搜索返回的最相关行的单词被添加到搜索字符串，并且再次进行搜索，查询将返回来自第二个搜索的行使用
步骤
1.创建表，给字段加上全文索引 fulltex
2.手动生成全文索引
SET GLOBAL innodb_ft_aux_table=“test/articles”;
schema/table_name
3.查询
添加全文索引
未创建表
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT KEYidx_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 ;
查询
用MATCH() … AGAINST 方式来进行搜索
match()
表示搜索的是那个列
against
表示要搜索的是那个字符串
SELECT * FROM articlesWHERE MATCH (title,body)AGAINST ('文言文' IN NATURAL LANGUAGE MODE);// 不指定模式，默认使用自然语言模式SELECT * FROM articlesWHERE MATCH (title,body)AGAINST ('文言文');
查看生成索引词
show variables like ‘%token_size%’;SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE ORDER BY doc_id, position;相关参数
innodb_ft_min_token_size
默认3，表示最小3个字符作为一个关键词，增大该值可减少全文索引的大小innodb_ft_max_token_size
默认84，表示最大84个字符作为一个关键词，限制该值可减少全文索引的大小ngram_token_size
默认2，表示2个字符作为内置分词解析器的一个关键词，如对“abcd”建立全文索引，关键词为’ab’，‘bc’，‘cd’
当使用ngram分词解析器时，innodb_ft_min_token_size和innodb_ft_max_token_size 无效
如何设置
第一种
mysqld --ngram_token_size=1
第二种
在配置文件中[mysqld]ngram_token_size=1
这三个参数均不可动态修改，修改了这些参数，需重启MySQL服务，并重新建立全文索引！！
问题
查询数据不完整?如数据中明明存在
文言文, 但是查询文言的时候查询不到
解决方案
使用boolean mode通配符模式。如 有单词 calculate , 查询匹配为select * from articles where match(title,body) AGAINST('cal*' in boolean mode);即可，如果用普通的自然语言模式+通配符则查询不到调整ngram_token_size的大小至合适的长度查询模式
ngram Parser Term Search
模式查询
在NATURAL LANGUAGE MODE检索模式下，查询关键字表达式被转换为若干个ngram词语的联合 字符串“管理开发”（假设ngram_token_size=2）会被转换为“管理 理开 开发”。给出多行记录，这多条记录都匹配检索词语“管理 理开 开发”，只要包含其中之一即可，匹配其一即可返回 在BOOLEAN MODE检索模式下，查询关键字表达式被转换为一个ngram短语检索 例如，字符串“项目中管理”（假设ngram_token_size=2）会被转换为“项目 目中 中管 管理”。给出多行记录，这多条记录都匹配检索词语“项目 目中 中管 管理”。但是只有包含 “项目中管理” 的记录行匹配检索短语 ‘“项目 目中 中管 管理”’，完全匹配才可返回
ngram Parser Wildcard Search
通配符查询 如果查询的通配符长度小于ngram token size 如果查询的通配符长度小于ngram token size, 查询返回所有索引的行，这些行包含以前缀术语开头的ngram token 如果查询的通配符长度大于ngram token size 该前缀词语会被转换为一个ngram短语，与此同时，通配符符号会被忽略。例如，假设ngram_token_size=2，“管理开发 *”通配符检索会被转换为“管理 开发”（“ngram Parser Term Search”的BOOLEAN MODE）
ngram Parser Phrase Search
短语查询 例如，检索短语“abc”会被转换为“ab bc”，包含“abc”和“ab bc”的记录都会被返回。检索短语“abc def”会被转换为“ab bc de ef”，包含“abcdef”的记录不会被返回
全文索引调优
Configuring Minimum and Maximum Word Lengt-
Configuring the Natural Language Search Threshol-
Modifying Boolean Full-Text Search Operator-
Character Set Modification-
Rebuilding InnoDB Full-Text Indexe-
Optimizing InnoDB Full-Text Indexes
步骤
开启 innodb_optimize_fulltext_only mysql> set GLOBAL innodb_optimize_fulltext_only=ON;
2.执行 optimize table articles
mysql> OPTIMIZE TABLE opening_lines;
Rebuilding MyISAM Full-Text Indexes
当修改了如，innodb_ft_min_token_size; innodb_ft_max_token_size; innodb_ft_server_stopword_table; innodb_ft_user_stopword_table; innodb_ft_enable_stopword; ngram_token_size. Modifying innodb_ft_min_token_size,
innodb_ft_max_token_size, or ngram_token_size 需要重新生成全文索引
操作
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;SET NAMES utf8mb4;INSERT INTO articles (title,body) VALUES('calculate','incomplete quere');## 当修改了ngram_token_szie等参数的时候，需要重新建立索引DROP INDEX idx_ft_tb ON test.articles ;ALTER TABLE articles ADD FULLTEXT INDEX idx_ft_tb (title,body) WITH PARSER ngram;# 生成索引SET GLOBAL innodb_ft_aux_table="test/articles";OPTIMIZE TABLE articles;# 这个命令适合MyISAM存储引擎REPAIR TABLE articles QUICK;show variables like '%token%'mysql> show variables like '%token%';+--------------------------+-------+| Variable_name | Value |+--------------------------+-------+| innodb_ft_max_token_size | 84 || innodb_ft_min_token_size | 3|| ngram_token_size | 1|+--------------------------+-------+3 rows in set查看数据分词结果SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE ORDER BY doc_id, position;select * from articles where match(title,body) AGAINST('文言文*' in boolean mode);select * from articles where match(title,body) AGAINST('文言文');
