mysql聚合索引失效
索引是可以高效的获取数据的数据结构, 对查询有很大作用. 索引对于数据库, 就像偏旁部首, 或者26个英文字母对于字典一样, 能很快的提高查询速度.但是索引也有它的缺点:首先就是占用磁盘空间其次是虽然...
2024.11.15前段时间,因为项目需求,需要根据关键词搜索聊天记录,这不就是一个搜索引擎的功能吗? 于是我第一时间想到的就是 ElasticSearch 分布式搜索引擎,但是由于一些原因,公司的服务器资源比较紧张,没有额外的机器去部署一套 ElasticSearch 服务,而且上线时间也比较紧张,数据量也不大,然后就想到了 Mysql 的全文索引。
简介其实 Mysql 很早就支持全文索引了,只不过一直只支持英文的检索,从5.7.6 版本开始,Mysql 就内置了 ngram 全文解析器,用来支持中文、日文、韩文分词。
Mysql 全文索引采用的是倒排索引的原理,在倒排索引中关键词是主键,每个关键词都对应着一系列文件,这些文件中都出现了这个关键词。这样当用户搜索某个关键词时,排序程序在倒排索引中定位到这个关键词,就可以马上找出所有包含这个关键词的文件。
本文测试,基于 Mysql 8.0 版本,数据库引擎采用的是 InnoDB
ngram 全文解析器ngram 就是一段文字里面连续的 n 个字的序列。ngram 全文解析器能够对文本进行分词,每个单词是连续的 n 个字的序列。例如,用 ngram 全文解析器对“你好靓仔”进行分词:
n=1: ‘你‘, ‘好‘, ‘靓‘, ‘仔‘ n=2: ‘你好‘, ‘好靓‘, ‘靓仔‘ n=3: ‘你好靓‘, ‘好靓仔‘ n=4: ‘你好靓仔‘MySQL 中使用全局变量 ngram_token_size来配置 ngram 中 n 的大小,它的取值范围是1到10,默认值是 2。通常 ngram_token_size 设置为要查询的单词的最小字数。如果需要搜索单字,就要把 ngram_token_size 设置为 1。在默认值是 2 的情况下,搜索单字是得不到任何结果的。因为中文单词最少是两个汉字,推荐使用默认值 2。
可以通过以下命令查看 Mysql 默认的 ngram_token_size 大小:
show variables like ‘ngram_token_size‘有两种方式可以设置全局变量 ngram_token_size 的值:
1、启动 mysqld 命令时指定:
mysqld --ngram_token_size=22、修改 Mysql 配置文件 my.ini,末尾增加一行参数:
ngram_token_size=2创建全文索引1、建表时创建全文索引CREATE TABLE `article` (`id` bigint NOT NULL,`url` varchar(1024) COLLATE utf8mb4_general_ci NOT NULL DEFAULT ‘‘,`title` varchar(256) COLLATE utf8mb4_general_ci NOT NULL DEFAULT ‘‘,`source` varchar(32) COLLATE utf8mb4_general_ci DEFAULT ‘‘,`keywords` varchar(32) COLLATE utf8mb4_general_ci DEFAULT NULL,`publish_time` timestamp NULL DEFAULT NULL,PRIMARY KEY (`id`),FULLTEXT KEY `title_index` (`title`) WITH PARSER `ngram`) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;2、通过 alter table 方式ALTER TABLE article ADD FULLTEXT INDEX title_index(title) WITH PARSER ngram;3、通过 create index 方式CREATE FULLTEXT INDEX title_index ON article (title) WITH PARSER ngram;检索方式1、自然语言检索(NATURAL LANGUAGE MODE)自然语言模式是 MySQL 默认的全文检索模式。自然语言模式不能使用操作符,不能指定关键词必须出现或者必须不能出现等复杂查询。
示例
select * from article where MATCH(title) AGAINST (‘北京旅游‘ IN NATURAL LANGUAGE MODE);// 不指定模式,默认使用自然语言模式select * from article where MATCH(title) AGAINST (‘北京旅游‘);可以看出,该模式下根据“北京旅游”搜索,可以搜索出包含“北京”的或者包含“旅游”的内容,因为它是根据自然语言分成了两个关键词。
上面示例中返回的结果会自动按照匹配度排序,匹配度高的在前面,匹配度是一个非负浮点数。
示例
// 查看匹配度select * , MATCH(title) AGAINST (‘北京旅游‘) as score from article where MATCH(title) AGAINST (‘北京旅游‘ IN NATURAL LANGUAGE MODE);2、布尔检索(BOOLEAN MODE)布尔检索模式可以使用操作符,可以支持指定关键词必须出现或者必须不能出现或者关键词的权重高还是低等复杂查询。
示例
// 无操作符// 包含“约会”或“攻略”select * from article where MATCH(title) AGAINST (‘约会 攻略‘ IN BOOLEAN MODE);// 使用操作符// 必须包含“约会”,可包含“攻略”select * from article where MATCH(title) AGAINST (‘+约会 攻略‘ IN BOOLEAN MODE);更多操作符示例:
‘约会 攻略‘ 无操作符,表示或,要么包含“约会”,要么包含“攻略”‘+约会 +攻略‘必须同时包含两个词‘+约会 攻略‘必须包含“约会”,但是如果也包含“攻略”的话,匹配度更高。‘+约会 -攻略‘必须包含“约会”,同时不能包含“攻略”。‘+约会 ~攻略‘必须包含“约会”,但是如果也包含“攻略”的话,匹配度要比不包含“攻略”的记录低。‘+约会 +(>攻略索引是可以高效的获取数据的数据结构, 对查询有很大作用. 索引对于数据库, 就像偏旁部首, 或者26个英文字母对于字典一样, 能很快的提高查询速度.但是索引也有它的缺点:首先就是占用磁盘空间其次是虽然...
2024.11.15死锁问题 共享间隙锁引起的死锁 如何产生共享间隙锁 何时产生的隐式锁转换问题现象在一个事务内只会锁一行的数据,没有锁多行数据才会出现的顺序问题,但是会偶尔报个Deadlock事务内sql执行顺序...
2024.11.14在使用mysql的时候我们经常需要用到排序方法,也就是order by,如果是数字类型的字段,我们一般用order by 或者order by desc就能解决, 但是,如果我们要对字符类型的数据进行...
2024.11.15概述今天主要讲下mysql数据库引擎的一些概念和mysql数据库本质,一句话总结:文件夹-文件:一个数据库其实就是一个的文件夹,数据库里面的表就是文件夹里的一个或者多个文件(根据数据库引擎不同而不同,...
2024.11.15四种mysql存储引擎前言数据库存储引擎是数据库底层软件组织,数据库管理系统(DBMS)使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能,使用不同...
2024.11.13