MySql 全文索引

hello早上好
153 阅读5分钟

简介

基本概念

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

虽然使用 like + % 也可以实现模糊匹配，但是对于大量的文本数据检索，是不可想象的。

全文索引在大量的数据面前，能比 like 快 N 倍，速度不是一个数量级

限制与缺点

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

操作全文索引

配置最小搜索长度

SHOW VARIABLES LIKE 'ft%';

image.png

  • ft_boolean_syntax：表示布尔查询时的可以用的符号，改变 IN BOOLEAN MODE 的查询字符，不用重新启动MySQL也不用重建索引。
  • ft_max_word_len :  最长的索引字符串，默认值为84，修改后要重建索引。
  • ft_min_word_len   : 最短的索引字符串，默认值为4，修改后要重建索引。
  • ft_query_expansion_limit:  查询括展时取最相关的几个值用作二次查询。
  • ft_stopword_file    (built-in): 停词文件，这个文件里的词查询时会忽略掉。

全文索引的相关参数都无法进行动态修改，必须通过修改 MySQL 的配置文件来完成。修改最小搜索长度的值为 1，首先打开 MySQL 的配置文件 /etc/my.cnf，在 [mysqld] 的下面追加以下内容：

[mysqld]
innodb_ft_min_token_size = 1
# 最短的索引字符串，默认值为4。
ft_min_word_len = 1

配置完后重启 MySQL 服务器，并修复或重建全文索引方可生效。

repair table test quick;

 创建索引

  • 建表时创建全文索引
CREATE TABLE fulltext_test (
  id int(11) NOT NULL AUTO_INCREMENT,
        content TEXT NOT NULL,
        tag VARCHAR(255),
        PRIMARY KEY (id),
        FULLTEXT KEY content_tag_fulltext(content, tag) WITH PARSER ngram
) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4;
  • 在已存在的表上创建全文索引
CREATE FULLTEXT INDEX content_fulltext ON fulltext_test(content) with parser ngram;
  • 通过 SQL 语句 ALTER TABLE 创建全文索引
ALTER TABLE fulltext_test ADD FULLTEXT INDEX content_fulltext(content) with parser ngram;

 删除索引

  • 使用 DROP INDEX 删除全文索引
DROP INDEX content_fulltext ON fulltext_test;
  • 通过 SQL 语句 ALTER TABLE 删除全文索引
ALTER TABLE fulltext_test DROP INDEX content_fulltext;

检索数据

自然语言的全文检索

默认情况下，或者使用 in natural language mode 修饰符时，match() 函数对文本集合执行自然语言搜索。

SELECT * FROM 表名 WHERE Match(列名1,列名2) Against (检索内容1 检索内容2);

检索内容不需要用逗号隔开！

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

布尔全文检索

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

  • 空(也就是默认状况)，表示可选的，包含该词的顺序较高。
  • + 表示必须包含。
  • - 表示必须排除。
  • > 表示出现该单词时增加相关性，查询的结果靠前。
  • < 表示出现该单词时降低相关性，查询的结果靠后。
  • * 表示通配符，只能接在词后面。
  • ~ 允许出现该单词，但是出现时相关性为负，表示拥有该字会下降相关性，但不像-将之排除，只是排在较后面。
  • ""  双引号表示短语，表示要彻底相符，不可拆字效果，类同于 like '%keyword%'。
  • () 经过括号来使用字条件。
SELECT * FROM fulltext_test d WHERE MATCH ( d.content ) Against ( concat( '"', '葡萄 苹果', '"' ) IN boolean MODE );  -- 匹配词语1 词语2。

SELECT * FROM fulltext_test d WHERE MATCH ( d.content ) Against ( concat(' +苹果 -葡萄 ') IN boolean MODE );  -- 返回结果必须含有词语1，但不能有词语2。

SELECT * FROM fulltext_test d WHERE MATCH ( d.content ) Against ( concat( ' >葡萄 <苹果 ') IN boolean MODE ); -- 含有词语1的话优先级升高，含有词语2的话优先级降低。

全文索引带来的负面影响：

  1. 占有存储空间更大，如果内存一次装不下全部索引，性能会非常差。
  2. 增删改代价更大，修改文本中10个单词，则要操作维护索引 10 次，而不是普通索引的一次。
  3. 如果一个列上有全文索引则一定会用上，即使有性能更好的其他索引也不会用上。
  4. 只是按2个字（可配）顺序截断再索引，不能算是分词。
  5. 如“婴儿的皮肤”，会被截断成“婴儿”、“儿的”、“的皮”、“皮肤”4个词，再索引。
avatar
文章
阅读
粉丝