MySQL全文索引

MySQL全文索引

简介

全文索引(也称全文检索)是目前搜索引擎使用的一种关键技术。它能够利用「分词技术「等多种算法智能分析出文本文字中关键字词的频率及重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。

通过数值比较、范围过滤等就可以完成绝大多数我们需要的查询，但是，如果希望通过关键字的匹配来进行查询过滤，那么就需要基于相似度的查询，而不是原来的精确数值比较。全文索引就是为这种场景设计的。

全文索引可以支持各种字符串内容搜索（包括 char、varchar、text）也支持自然语言和布尔搜索，在 MySQL 中全文索引有很多限制，实现也很复杂，但因为是 MySQL 内置功能，而且满足很多基本的搜索需求，所以它的应用非常广泛

MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引，5.6之后，存储引擎均支持全文索引

全文索引的操作

创建全文索引

CREATE TABLE `test`  (
  `id` int NOT NULL,
  `content` varchar(22) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  FULLTEXT INDEX `idx_fulltext`(`content`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;

或者已存在的表上创建全文索引

alter table test add fulltext idx_fulltext(content)

删除全文索引

drop index idx_fulltext on test

使用全文索引

全 文 索 引 的 使 用 与 其 他 索 引 不 同 。 在 查 询 语 句 中 需 要 使 用 match(column) against(‘content’) 来检索数据。

mysql> select * from test where match(content) AGAINST('回到正题');
+----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | content                                                                                                                                                                                                                                                  |
+----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | 回到正题，我们在根据某个搜索条件查找一些记录时为什么要遍历所有的数据页呢？因为各个页中的记录并没有规律，我们并不知道我们的搜索条件匹配哪些页中的记录，所以 不得不 依次遍历所有的数据页。所以如果我们想快速的定位到需要查找的记录在哪些数据页中该咋办？还 |
+----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)

查看执行计划

mysql> explain select * from test where match(content) AGAINST('回到正题');
+----+-------------+-------+------------+----------+---------------+--------------+---------+-------+------+----------+-------------------------------+
| id | select_type | table | partitions | type     | possible_keys | key          | key_len | ref   | rows | filtered | Extra                         |
+----+-------------+-------+------------+----------+---------------+--------------+---------+-------+------+----------+-------------------------------+
|  1 | SIMPLE      | test  | NULL       | fulltext | idx_fulltext  | idx_fulltext | 0       | const |    1 |   100.00 | Using where; Ft_hints: sorted |
+----+-------------+-------+------------+----------+---------------+--------------+---------+-------+------+----------+-------------------------------+
1 row in set (0.05 sec)

注意：

1. .如果您的表格中没有足够的数据(例如1行),则全文搜索将无效，所以出现在100％的行中,因此没有索引
2. MySQL InnoDB 最短索引字符串默认为4，也就是只有达到4个字符才会生效，可以进行修改

mysql> SHOW VARIABLES  LIKE 'ft%';
+--------------------------+----------------+
| Variable_name            | Value          |
+--------------------------+----------------+
| ft_boolean_syntax        | + -><()~*:""&| |
| ft_max_word_len          | 84             |
| ft_min_word_len          | 4              |
| ft_query_expansion_limit | 20             |
| ft_stopword_file         | (built-in)     |
+--------------------------+----------------+
5 rows in set (19.05 sec)

自然语言全文索引

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

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

函数match() 将返回关键词匹配的相关度，是一个浮点数字。你可以根据相关度进行匹配，或者将此直接展现给用户。在一个查询中使用两次 match() 函数不会有额外的消耗，MySQL 会自动识别并只进行一次搜索。不过你将 match() 函数放到 order by 子句中， MySQL 将会使用文件排序。

布尔全文索引

在布尔搜索中，我们可以在查询中自定义某个被搜索的词语的相关性，布尔搜索通过停用词列表过滤掉那些 “噪音” 词，除此之外，布尔搜索还要求搜索关键词长度必须大于 ft_min_word_len , 同时小于 ft_max_word_len。搜索返回结果未经排序

布尔全文索引通用修饰符

• +必须包含该词
• -必须不包含该词
• >提高该词的相关性，查询的结果靠前
• <降低该词的相关性，查询的结果靠后
• (*)星号 通配符，只能接在词后面

select * from test where match(content) AGAINST('我*' in boolean mode);

全文索引的配置和优化

全文索引的日常维护通常能够大大提升性能，如果希望全文索引能够高效的工作，还需要保证索引缓存足够大，从而保证所有的全文索引都能够缓存在内存中，可以为全文索引舌质淡度的键缓存

忽略一些太短的单词也可以提升全文索引的效率，索引单词的最小长度可以通过参数 ft_min_word_len 配置。修改该参数可以过滤更多的单词，让查询速度更快，但是也会降低精准度。

当想一个有全文索引的表导入大量数据的时候，最好先通过 disable keys 来禁用全文索引，然后在导入结束后使用 enable keys 来建立全文索引。因为全文索引的更新是一个消耗很大的操作，所有会节省大量的时间