MySQL-全文索引

MySQL-全文索引

丁起男 166 2022-01-13

MySQL-全文索引

MySQL数据库支持全文索引查询,全文索引只能在innodeb(高版本)或myisam的表上使用,并且只能用于char、varchar、text类型的列

倒排索引

全文检索通常石油倒排索引(inverted index)来实现,倒排索引同b+tree一样,也是一种索引结构。它在辅助表中存储了单词单词在一个或多个文档中所在位置的映射,这通常利用关联数组来实现,拥有两种表现形式:

  • inverted file index:{单词,所在文档的id}
  • full inverted index:{单词,(所在文档的id,文档中的位置)}

创建

创建表时添加

create table table_name(
	......
	fulltext full_index_name(col_name)
)

在已创建表上添加

create fulltext index full_index_name on table_name(col_name)

删除

直接删除:

drop index full_index_name on table_name

使用alter:

alter table table_name drop index full_index_name

使用

语法:

match(col1,col2,...) against(expr[search_modifier])
search_modifier:
{
	in natural language mode
	| in natural language mode with query expansion
	| in boolean mode
	| with query expansion
}

全文索引使用match() against()语法进行

  • match():指定要搜索的列,如果指定多个列,需要设置组合的全文索引
  • against():接收一个要搜索的字符串,以及一个要执行的搜索类型的可选修饰符

全文检索分为三种类型:自然语言搜索、布尔搜索、查询扩展搜索

自然语言搜索

自然语言搜索将搜索字符串解释为自然人类语言中的短语,match()默认采用自然语言搜索模式,其表示查询带有指定关键字的文档

使用:

select * from table_name where match(col_name) against('text')

还可以查看相关性:

select *,match(col_name) against('text') from table_name

相关性计算依据

  • 是否在文档中出现
  • 在文档中出现的次数
  • 索引列中的数量
  • 多少个文档包含

如果是innodb还需要考虑

  • 查询的单词是否在停用词中
  • 查询的单词长度是否在innodb_ft_min_token_size(默认3)和innodb_ft_max_token_size(默认84)区间内

布尔搜索

布尔搜索使用特殊查询语言的规则来解释搜索字符串,该字符串包含要搜索的词,它还可以包含指定要求的运算符

使用:

select * from table_name where match(col_name) against('+value1 -value2' in boolean mode)

表示查询带有value1但是没有value2的数据

支持的类型

类型说明
+单词必须存在
-单词必须不存在
没有指定单词是可选的,但如果出现,相关性会更高
@distance查询的多个单词之间的距离是否在distance之内,distance的单位是字节
>出现该单词时增加相关性
<出现该单词时降低相关性
~允许出现该单词,但出现时相关性为负
*以该单词开头的单词
“ ”表示短语,需要确切的包含该短语

查询扩展搜索

查询扩展搜索是对自然语言搜索的修改,这种查询通常在查询的关键词太短,用户需要implied knowledge(隐含知识)时进行,例如,对于单词database的查询,用户可能希望查询的不仅仅是包含dtabase的文档,可能还需要mysql、oracle的单词,这时可以使用查询扩展搜索模式来开启全文检索的implied knowledge通过在查询语句中添加with query expansionin natural language mode with query expansion可来开启blind query expansion,该查询分为两个阶段

  1. 根据搜索的单词进行全文索引查询
  2. 根据第一阶段产生的分词再艰辛一次全文索引查询