MySQL全文索引

午后的茶

MySQL 全文索引 2024年01月17日 09:01 419 1、 背景简介实际开发过程中，我们经常会遇到全文检索的述求，一般都会采用搭建ES服务器来实现。但因为数据量较少，并且不属于高并发高吞吐场景，相比较而言接入 ES，不仅会使得系统设计更加复杂，还会产生资源浪费，所以需要采用更加简单且廉价的方案来实现。一般互联网公司都会用到 MySQL 服务，从 MySQL5.7 开始，MySQL 内置了 ngram 全文检索插件，用来支持中文分词，并且对 MyISAM 和InnoDB 引擎有效。因此可以通过 MySQL 服务接入 full-text 索引来实现简单地全文检索需求。2、 MySQL 全文索引简介MySQL 的全文索引主要用于全文字段的检索场景，支持 char、varchar、text 几种字段加全文索引，仅支持 InnoDB 与 MyISAM 引擎。MySQL 内置了 ngram 解析器来支持中文、日文、韩文等语言的文本。MySQL 全文索引支持三种模式：● 布尔模式(IN BOOLEAN MODE)；● 自然语言模式(NATURAL LANGUAGE MODE)；● 查询拓展(QUERY EXPANSION)；3、 ngram 解析器简介ngram 一种基于统计语言模型的算法，简单来说，就是通过一个大小为 n 的滑动窗口，将一段文本分成多个由 n 个连续单元组成的term。其中 n 为分词大小默认为 2，可通过 ngram_token_size 设置分词大小。示例：使用 ngram 对于“全文索引”进行分词。 ngram_token_size =1,分词为 ‘全’，‘文’，‘索’，‘引’；ngram_token_size =2,分词为 ‘全文’，‘文索’，‘索引’；ngram_token_size =3,分词为 ‘全文索’，‘文索引’；ngram_token_size =4,分词为 ‘全文索引’；3.1、 如何查看配置 ngram_token_size #查看默认分词大小 ngram_token_size = 2 show variables like '%token%';查询结果：innodb_ft_min_token_size：默认 3，表示最小 3 个字符作为一个关键词，增大该值可减少全文索引的大小 innodb_ft_max_token_size：默认 84，表示最大 84 个字符作为一个关键词，限制该值可减少全文索引的大小 ngram_token_size：默认 2，表示2个字符作为内置分词解析器的一个关键词，如对“abcd”建立全文索引，关键词为‘ab’，‘bc’，‘cd’ 当使用 ngram 分词解析器时，innodb_ft_min_token_size 和 innodb_ft_max_token_size 无效3.2、 修改配置 ngram_token_size第一种：mysqld --ngram_token_size = 1；第二种：在配置文件中 [mysqld]ngram_token_size = 1；不可动态修改，修改后需重启 MySQL 服务，并重新建立全文索引。4、创建全文索引1、创建表的同时创建全文索引CREATE TABLE `announcement`  (  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',  `content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '内容',  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '',  RIMARY KEY (`id`) USING BTREE,  FULLTEXT INDEX `idx_full_text`(`content`) WITH ARSER `ngram`) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;2、通过 alter table 的方式来添加ALTER TABLE announcement ADD FULLTEXT INDEX idx_full_text(content) WITH ARSER ngram;3、直接通过 create index 的方式CREATE FULLTEXT INDEX idx_full_text ON announcement(content) WITH ARSER `ngram`;5、全文索引测试构建测试数据：INSERT INTO announcement (id, content, title) VALUES (1, '杭州市最近有大雪，出门多穿衣服', '杭州天气');INSERT INTO announcement (id, content, title) VALUES (2, '杭州市最近温度很低，不适合举办杭州马拉松', '杭州马拉松');INSERT INTO announcement (id, content, title) VALUES (3, '杭州市最近有大雪，西湖断桥会很美', '杭州西湖雪景');INSERT INTO announcement (id, content, title) VALUES (4, '大学的雪景也很美，周末可以去杭州逛逛', '大学雪景');INSERT INTO announcement (id, content, title) VALUES (5, '城北万象城开业，打折力度很大', '城北万象城开业火爆');5.1、布尔模式( IN BOOLEAN MODE )布尔模式的全文检索支持下面几种常用操作符： +(必须出现) -(必须不出现) 无操作符(出现了，相关性会更高) (增加或者减少相关性) ~ (负相关性) *(通配符) “” (短语)通过简单示例分别介绍布尔模式下几种操作符的具体用法：1、操作符+ (必须出现)select * from announcement where MATCH (content) against ('+杭州' in Boolean MODE);'+杭州'表示必须出现“杭州”这个分词，数据才能被检索到，并且包含杭州分词越多的代表着相关性更高。从结果可以看出，“杭州”这个分词出现次数最多的排在最前面。2、 操作符-(必须不出现)select * from announcement where MATCH (content) against ('+杭州 -大学' in Boolean MODE);'+杭州 -大学'表示被检索到的数据必须包含“杭州”这个分词，-大学表示被检索到的数据必须不能包含“大学”这个分词。3、 无操作符(出现了，相关性会更高)select * from announcement where MATCH (content) against ('杭州 大雪' in Boolean MODE);无操作符'杭州 大雪'表示出现“杭州”或者“大雪”的数据会有更高的相关性4、(增加或者减少相关性)select * from announcement where MATCH (content) against ('+杭州 >大学' in Boolean MODE);'+杭州 >大学'表示被检索到的数据必须包含“杭州”这个分词，当出现“大学”时对应数据的相关性会升高，如图可知带有大学的数据排序靠前。同理“