mysql模糊查询text字段时 查询效率极慢(优化)

项目需求:在文章中模糊查询相关文章,因需求紧急没有使用Elasticsearch搜索引擎

优化前

优化后

首先两个表的数据量是相同的,第二张表是添加了全文索引,比没添加时快了将近10秒左右

要求: MySQL 版本要大于 5.6 或 5.7.6     MySQL 5.6版本中，InnoDB加入了全文索引，但是不支持中文全文索引,在5.7.6版本，MySQL内置了ngram全文解析器，用来支持亚洲语种的分词.

使用全文索引:

一.创建全文索引: (二选一)

   1.如果是现成的表需要加全文索引: 这里给表中 articleContent, articleTittle 两个字段添加全文索引

ALTER TABLE t_help_center_article  ADD FULLTEXT INDEX articleTittle_articleContent_index (articleTittle,articleContent) WITH PARSER ngram;

  2.创建表时添加全文索引:

create table t_help_center_article
(
    helpCenterArticleId  bigint        not null auto_increment comment '帮助中心文章id',
    helpCenterCategoryId bigint        not null comment '帮助中心分类id',
    articleTittle        varchar(2000) not null comment '文章标题',
    articleContent       text      not null comment '文章内容',
    viewCount            int           not null default 0 comment '浏览量',
    sortNo               int           not null comment '排序',
    createTime           datetime      not null comment '创建时间',
    editTime             datetime comment '编辑时间',
    deleteTime           datetime comment '删除时间',
    primary key (helpCenterArticleId),
    FULLTEXT (articleContent, articleTittle) WITH PARSER ngram
);
alter table t_help_center_article comment '帮助中心文章';

3.运行后就可以看到 

删除索引:

DROP INDEX articleContent ON db_name.t_help_center_article;

 二.使用全文索引:(全文检索分为三种类型：自然语言搜索、布尔搜索、查询扩展搜索。)

1.语法 : MATCH() AGAINST()进行使用 , MATCH()采用逗号分隔，里面填写需要检索的字段AGAINST()接收需要检索的字符串。

2.简单使用

SELECT articleTittle,articleContent FROM t_help_center_article WHERE MATCH (articleTittle,articleContent) AGAINST ('chinese');

3.自然语言搜索(可以看到加 "IN NATURAL LANGUAGE MODE" 或不加效果是一样的,因为mysql 默认采用自然语言搜索 )

SELECT articleTittle,articleContent FROM t_help_center_article WHERE MATCH (articleTittle,articleContent) AGAINST ('chinese' IN NATURAL LANGUAGE MODE);

 4.布尔搜索:可以理解为判断搜索 (想要深入了解可以去这篇文章中很细致的讲解了每个的效果 深入了解)

• +：表示该 word 必须存在
• -：表示该 word 必须不存在
• (no operator)表示该 word 是可选的，但是如果出现，其相关性会更高
• @distance表示查询的多个单词之间的距离是否在 distance 之内，distance 的单位是字节，这种全文检索的查询也称为 Proximity Search，如 MATCH(context) AGAINST('"Pease hot"@30' IN BOOLEAN MODE)语句表示字符串 Pease 和 hot 之间的距离需在30字节内
• >：表示出现该单词时增加相关性
• <：表示出现该单词时降低相关性
• ~：表示允许出现该单词，但出现时相关性为负
• * ：表示以该单词开头的单词，如 lik*,表示可以是 lik，like，likes
• " ：表示短语

1.语法:

SELECT articleTittle,articleContent FROM t_help_center_article WHERE MATCH (articleTittle,articleContent) AGAINST('+chinese'  IN BOOLEAN MODE);

 5.查询扩展搜索 原作者文章地址http://events.jianshu.io/p/f9775c624771

 三.分词器设置

找到mysql中my.ini文件,在文件中

[mysqld] 下方添加
ft_min_word_len=2  (最小搜索长度)
ngram_token_size=2  (分词长度)

ft_min_word_len: 可以理解为,查询条件最小长度不能小于2

ngram_token_size: 分词长度,例如这句话 "吃饭坐小孩那桌"   分词器就会解析为: "吃饭","饭坐","坐小","小孩"... 分别去匹配字段中包含这些字符的数据;

四.停用词

1.查看停用词 sql

select * from information_schema.INNODB_FT_DEFAULT_STOPWORD;

a
about
an
are
as
at
be
by
com
de
en
for
from
how
i
in
is
it
la
of
on
or
that
the
this
to
was
what
when
where
who
will
with
und
the
www

2.举例:

 因为在分词器中我们设置了最小长度为2 所以从两个字符开始

 'aall' 根据上面的停用词知道 'a' 为停用词

当分词器拆分的时候:(因为在分词中设置了ft_min_word_len=2 所以搜索条件长度必须为2不足则   跳过)

1.拿取前两个字符 'aa' 判断到a为停用词 跳过

2.继续分 'al' 判断遇到a为停用词 跳过

3.继续分 'll' 不是停用词 长度不小于2 进入查询条件

注意:全文索引占有存储空间很大，如果内存一次装不下全部索引，性能会非常差! 建议还是使用Elasticsearch 专用搜索引擎