mysql ngram全文检索引擎

最新推荐文章于 2024-05-31 09:59:18 发布

重生之我是一名程序员

最新推荐文章于 2024-05-31 09:59:18 发布

阅读量2.3k

点赞数 1

文章标签： mysql

原文链接：https://editor.csdn.net/md?articleId=106757993

版权

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 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 ;

查询

用MATCH() … AGAINST 方式来进行搜索
- match()
  - 表示搜索的是那个列
- against
  - 表示要搜索的是那个字符串

SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('文言文' IN NATURAL LANGUAGE MODE);
 
// 不指定模式，默认使用自然语言模式
SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('文言文');

查看生成索引词

show variables like ‘%token_size%’;
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE ORDER BY doc_id, position;

问题

查询数据不完整?如数据中明明存在文言文, 但是查询 文言的时候查询不到

解决方案

使用 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
- 步骤
  - 1. 开启 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('文言文');

引用文章

https://dev.mysql.com/doc/refman/5.7/en/fulltext-fine-tuning.html
https://dev.mysql.com/doc/refman/5.7/en/fulltext-search-ngram.html
https://www.jianshu.com/p/27b95ed94c73
https://stackoverflow.com/questions/35480332/use-full-text-search-to-search-incomplete-words-in-mysql

重生之我是一名程序员

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
mysql ngram全文检索引擎

mysql ngram全文检索引擎ngram是什么？全文检索引擎，全文解析器能够对文本进行分词特点Mysql 5.6之前版本，只有myisam支持全文索引，5.6之后，Innodb和myisam均支持全文索引ngram全文解析器，用来支持中文、日文、韩文分词只有char、varchar、text类型字段能创建全文索引英文分词用空格，逗号；中文分词用 ngram_token_size 设定全文检索模式自然语言模式(NATURAL LANGUAGE MODE)自然语言模式是My
复制链接

扫一扫