MySQL使用全文索引实现大字段的模糊查询

技术

  1. 场景说明

    有一个需求，用户输入关键字对博客系统内所有含有该字段的文章(标题&简介&内容)进行展示，我自然而然地想到了索引。这里我是在CentOS7下MySql5.7版本的数据库.

  2. 创建表

    因为是一个全局搜索的的需求，用户输入关键字对博客系统内所有含有该字段的文章(标题&简介&内容)进行展示,(上表只是文章内容表)，由于要对文章内容进行搜素，而文章内容content在数据库中是以text存储的，所以为了查询效率，这里就需要使用索引，由于是查大文本，这里选择使用全文索引(fulltext).

1
2
3
4
5
6
7
8
9
10
11
12
13
DROP TABLE IF EXISTS `tbl_article_content`;
CREATE TABLE `tbl_article_content`  (
  `id` bigint(40) NOT NULL AUTO_INCREMENT,
  `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `article_id` bigint(40) NOT NULL COMMENT '对应文章ID',
  `create_by` datetime(0) NOT NULL COMMENT '创建时间',
  `modifield_by` datetime(0) NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `artid`(`article_id`) USING BTREE,
  FULLTEXT INDEX `content_word`(`content`) WITH PARSER `ngram`
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;
  1. 创建索引（使用ngram解析器）
1
create fulltext index content_word on tbl_article_content(content) WITH PARSER ngram;

ngram 是一个支持中文索引的分词引擎， 在这里我们对tbl_article_content表的content字段建一个叫content_word的全文索引.

  1. mysql配置（my.cnf）

    mysql默认是没有开启与配置ngram的，所以在建立索引后对mysql进行配置 打开mysql配置文件

1
vim /etc/my.cnf

在[mysqld]下加入

1
2
ft_min_word_len=1    #全文索引的最小搜索长度。默认是4
ngram_token_size=1   #分词的大小设置，这里设置越小，索引越大

在终端重启mysql

1
systemctl restart mysqld

如果是在已有的表上对某一个字段增加索引，在此需要修复一下之前创的索引.

1
repair table tbl_article_content
  1. 如何在项目中使用刚才创建的索引进行查询呢？
1
select article_id FROM tbl_article_content WHERE MATCH(content) AGAINST('没有了');

  1. 总结问题

    从MySQL 5.7开始，MySQL内置了ngram全文检索插件，用来支持中文分词，并且对MyISAM和InnoDB引擎有效，使用的时候注意自己版本号和配置.

  2. 相关命令

1
2
show create table tbl_article_content; //查询当前表信息
SHOW VARIABLES LIKE '%ngram%';  //查询ngram引擎配置信息