mysql全文索引

mysql 全文索引

参考

博客园全文索引

mysql8文档,全文搜索功能

需求

项目中需要使用分词搜索, 数据量不大,并且使用场景比较简单, 研究了 es meilisearch 之后决定使用mysql自带的fulltext索引进行全文搜索, 其实在写这篇文章时,需求使用 like 也能满足。

简介

基本概念

全文索引是为了解决需要基于相似度的查询，而不是精确数值比较。

虽然使用 like + % 也可以实现模糊匹配，但是对于大量的文本数据检索，是不可想象的。全文索引在大量的数据面前，能比 like 快 N 倍，速度不是一个数量级。

版本支持

MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引
MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引
MySQL 5.7.6 中，提供了支持中文、日文和韩文（CJK）的内置全文 ngram 解析器，以及用于日文的可安装 MeCab 全文解析器插件
全文索引只能用于InnoDB或MyISAM表，只能为CHAR、VARCHAR、TEXT列创建
对于大型数据集，将数据加载到没有全文索引的表中然后创建索引要比将数据加载到具有现有全文索引的表中快得多
RDS MySQL 5.6 虽然也支持中文全文检索，但存在BUG

适用场景

1）表中该字段中的数据量庞大
2）经常被检索，经常出现在where子句中的字段
3）经常被DML操作的字段不建议添加索引

优点:

1）大大提高检索数据的性能效率
2）在表连接的连接条件，可以加速表与表直接的相连
3）在分组和排序字句进行数据检索,可以减少查询时间中分组和排序时所消耗的时间(数据库的记录会重新排序)

限制与缺点

创建与维护索引会消耗时间，并随着数据量的增加而增加
导致磁盘资源的大量占用。全文索引本身就是一个利用磁盘空间换取性能的方法。全文索引大的原因是，按照某种语言来进行分词
全文索引创建速度慢，而且对有全文索引的各种数据修改操作也慢
使用全文索引并不是对应用透明的。如果要想利用全文索引，必须修改查询语句。原有的查询语句是不可能利用全文索引的，需要改成全文索引规定的语法
不区分大小写
分区表不支持全文搜索
由多列组合而成的全文检索的索引必须使用相同的字符集与排序规则
全文索引可能存在精度问题，即全文索引找到的数据，可能和like到的不一致
MATCH()函数中的列必须与FULLTEXT索引中定义的列完全一致，除非是在MyISAM表中使用IN BOOLEAN MODE模式的全文搜索（可在没有建立索引的列执行搜索，但速度很慢）
单列分别建立全文索引时，多列模糊查询时不生效
不同表的全文索引不能放在一起查询，可以两个语句中加上OR

操作全文索引

查询索引用到的配置属性

show VARIABLES like '%ft%'

全文索引的相关参数都无法进行动态修改，必须通过修改 MySQL 的配置文件来完成。修改最小搜索长度的值为 1，首先打开 MySQL 的配置文件 /etc/my.cnf，在 [mysqld] 的下面追加以下内容：
-- 配置内容可以参看 官方文档配置项说明
[mysqld]
innodb_ft_min_token_size = 1
# 最短的索引字符串，默认值为4
ft_min_word_len = 1

修改完成后需要重启mysql服务, 并且已经创建的索引需要重建

全局停用词

停用词

mysql 自带停用词表 INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD

-- 配置自己的停用词表 
--  全文停用词, 可以指定单词不建立索引,  一般 用于 the  this 等无意义词,
CREATE TABLE my_stopwords(value VARCHAR(30)) ENGINE = INNODB;
INSERT INTO my_stopwords(value) VALUES ('Ishmael');
SET GLOBAL innodb_ft_server_stopword_table = 'test/my_stopwords';
-- 修改停用词之后需要更新索引  删除后重建

索引增删

ALTER TABLE doc
ADD FULLTEXT INDEX `docIndex`(`name`, `desc`, `content`) WITH PARSER `ngram`;

-- 删除缩影
DROP INDEX docIndex ON doc;


ALTER TABLE  `doc` 
DROP INDEX `docIndex`;

索引查询使用

函数 : MATCH()against()

自然语言的全文检索

默认方式

自然语言搜索引擎将计算每一个文档对象和查询的相关度。这里，相关度是基于匹配的关键词的个数，以及关键词在文档中出现的次数。在整个索引中出现次数越少的词语，匹配时的相关度就越高。相反，非常常见的单词将不会被搜索，如果一个词语的在超过 50% 的记录中都出现了，那么自然语言的搜索将不会搜索这类词语。

-- 查询时创建   查询时  需要将添加索引的字段 按照顺序全部写在 match 后面  否则索引不生效
SELECT * FROM `lc_law_doc` where  MATCH(`law_name`, `law_desc`, `ver_desc`, `content`)against('"我的文档"' );
"" 表示该单词不再分割

扩展用法

格式
select 字段 from 表名 where match(字段) against(‘搜索字符串’ with query expansion);

机制：
首先，进行一个基本的全文本搜索，找出与搜索条件匹配的所有行
其次，Mysql检查这些匹配行并选择所有有用的词
再次，Mysql再次进行全文本搜索，这次不仅使用原来的条件，而且还使用所有有用的词

布尔搜索

在布尔搜索中，我们可以在查询中自定义某个被搜索的词语的相关性，当编写一个布尔搜索查询时，可以通过一些前缀修饰符来定制搜索。

空(也就是默认状况)，表示可选的，包含该词的顺序较高
+ 表示必须包含
- 表示必须排除
“>” 表示出现该单词时增加相关性，查询的结果靠前
“<” 表示出现该单词时降低相关性，查询的结果靠后
* 表示通配符，只能接在词后面
~ 允许出现该单词，但是出现时相关性为负，表示拥有该字会下降相关性，但不像「-」将之排除，只是排在较后面
“” 双引号表示短语，表示要彻底相符，不可拆字效果，类同于 like ‘%keyword%’
() 经过括号来使用字条件:

SELECT * FROM `doc` where  MATCH(`name`, `desc`, `content`)against('+法律法规 +test' IN BOOLEAN MODE)

mysql全文索引