作者：沸洋洋\来源：juejin.cn/post/6989871497040887845前言我们都知道InnoDB在模糊查询数据时使用“%xx”，会导致索引失效，但有时候这是需求，也有像这样的其他需求还有很多，比如搜索引擎需要根据用户数据搜索关键词全文，电商网站需要根据用户查询条件在商品的详细介绍中进行搜索。这些都是B+树索引Work做得不好的。通过数值比较、范围过滤等，可以完成我们需要的大部分查询。但是，如果要通过关键字匹配进行查询过滤，就需要根据相似度进行查询，而不是原来的精确数值比较。全文索引就是为这种场景设计的。全文索引（Full-TextSearch）是一种在数据库中存储的整本书或文章中查找任何信息的技术。可以根据需要获取全文中的相关章、节、段、句、词等信息，还可以进行各种统计和分析。在MySQL早期，InnoDB并不支持全文搜索技术。从MySQL5.6开始，InnoDB开始支持全文搜索。倒排索引全文搜索通常使用倒排索引来实现，倒排索引也是一种类似B+Tree的索引结构。将单词与单词本身在一个或多个文档中的位置的映射存储在副表中，通常使用关联数组实现，有两种表现形式：倒排文件索引：{word,theidword所在文档的of}fullinvertedindex:{word,(word所在文档的id,以及在具体文档中的位置)}上图是倒排文件索引的关联数组。可以看到文档1和4中都存在“code”这个词，所以存储起来很容易进行全文查询，直接根据Documents可以得到包含查询关键字的文档；而全量倒排索引存储的是pair，即(DocumentId,Position)，所以其存储的倒排索引如下图所示，如“关键词代码”存在于文档1的第6个词和文档4的第8个词相比之下，全倒排索引占用的空间更多，但可以更好地定位数据并扩展一些其他的搜索功能。全文搜索创建全文索引1.创建表时创建全文索引语法如下：CREATETABLEtable_name(idINTUNSIGNEDAUTO_INCREMENTNOTNULLPRIMARYKEY,authorVARCHAR(200),titleVARCHAR(200),内容TEXT(500),FULLTEXTfull_index_name(col_name))ENGINE=InnoDB;输入查询语句：SELECTtable_id,name,spacefromINFORMATION_SCHEMA.INNODB_TABLESWHEREnameLIKE'test/%';以上六张索引表组成一个倒排索引，称为辅助索引表。当传入的文档被标记化时，具有位置信息和相关DOC_ID的单个单词，单词根据单词第一个字符的字符集排序权重在六个索引表中完全排序和分区。2.在已创建的表上创建全文索引的语法如下：CREATEFULLTEXTINDEXfull_index_nameONtable_name(col_name);使用全文索引MySQL数据库支持全文搜索查询，全文索引只能在InnoDB或MyISAM表上使用，并且只能用于创建char、varchar、text类型的列。它的语法如下：MATCH(col1,col2,...)AGAINST(expr[search_modifier])search_modifier:{INNATURALLANGUAGEMODE|在带有查询扩展的自然语言模式中|在布尔模式下|WITHQUERYEXPANSION}全文搜索使用MATCH()AGAINST()语法，其中MATCH()采用以逗号分隔的列表命名要搜索的列。AGAINST()接受一个要搜索的字符串，以及一个可选的修饰符来表示要执行的搜索类型。全文检索分为三种类型：自然语言检索、布尔检索和查询扩展检索。下面将介绍各种查询方式。NaturalLanguage自然语言搜索将搜索字符串解释为自然人类语言中的短语。MATCH()默认使用NaturalLanguage模式，即查询指定关键字的文档。接下来结合demo更好的理解NaturalLanguageSELECTcount(*)AScountFROM`fts_articles`WHEREMATCH(title,body)AGAINST('MySQL');上面的语句，querytitle,body列包含关键字'MySQL'的行数。上面的语句也可以这样写：SELECTcount(IF(MATCH(title,body)against('MySQL'),1,NULL))AScountFROM`fts_articles`;虽然上面两条语句得到的结果相同，但是从内部操作来看，第二条SQL语句的执行速度更快，因为第一条SQL语句（基于where索引查询方式）也需要进行相关排序统计，而第二种方法不需要它。相关性也可以通过SQL查询：SELECT*,MATCH(title,body)against('MySQL')ASRelevanceFROMfts_articles;相关性的计算基于以下四个条件：词是否出现在文档中词在文档中出现词在索引列中出现的次数以及有多少文档包含该词对于InnoDB的全文搜索存储引擎，还需要考虑以下因素：queryword在stopword列，对于string忽略queryword的字符长度在区间[innodb_ft_min_token_size,innodb_ft_max_token_size]如果word在stopword中，则word不会被查询。例如查询'for'这个词，结果如下：SELECT*,MATCH(title,body)against('for')ASRelevanceFROMfts_articles;可以看出，虽然'for'出现在文档2和4中，但是它的相关性是0，因为它是一个停用词。参数innodb_ft_min_token_size和innodb_ft_max_token_size控制InnoDB引擎查询字符的长度。当长度大于innodb_ft_max_token_size时，将忽略对该词的搜索。在InnoDB引擎中，参数innodb_ft_min_token_size默认值为3，innodb_ft_max_token_size默认值为84Boolean布尔搜索使用特殊查询语言的规则解释搜索字符串，其中包含要搜索的词，可以还包含所需的操作，例如，匹配行中必须存在或不存在一个词，或者它的权重应该比平时高或低。例如，以下语句要求提供包含字符串“Pease”但不包含“hot”的文档，其中+和-分别表示该词必须存在或不得存在。select*fromfts_testwhereMATCH(content)AGAINST('+Pease-hot'INBOOLEANMODE);Boolean支持的全文检索类型包括：+：表示该词必须存在-：表示该词不存在（无运算符）表示该词可选，如果出现则相关性更高@distance表示query中多个单词之间的距离是否在距离之内，距离单位是bytes。这种全文搜索查询也称为ForProximitySearch，例如MATCH(context)AGAINST('"Peasehot"@30'INBOOLEANMODE)语句表示字符串Pease和hot之间的距离必须在30字节>：表示该词出现时相关性增加<：表示该词出现时相关性降低~：表示该词允许出现，但出现时相关性为负*：表示该词以单词开头，例如lik*，可以是lik,like,likes":表示在短语下面是一些演示，看看如何使用布尔模式。demo1:+-SELECT*FROM`fts_articles`WHEREMATCH(title,body)AGAINST('+MySQL-YourSQL'INBOOLEANMODE);上述语句查询包含'MySQL'但不包含'YourSQL'的信息demo2:nooperatorSELECT*FROM`fts_articles`WHEREMATCH(title,body)AGAINST('MySQLIBM'INBOOLEANMODE);上面的语句中，查询到的'MySQLIBM'没有'+'和'-'的符号，表示word是可选的如果出现，其相关性会更高demo3:@SELECT*FROM`fts_articles`WHEREMATCH(title,body)AGAINST('"DB2IBM"@3'INBOOLEANMODE);上面的语句表示“DB2”，两个单词“IBM”之间的距离在3个字节以内'在布尔模式下）；对于上述语句，查询同时包含'MySQL'、'database'和'DBMS'的行信息，但不包含'DBMS'的行的相关性高于包含'DBMS'的行的相关性。demo5:~SELECT*FROM`fts_articles`WHEREMATCH(title,body)AGAINST('MySQL~database'INBOOLEANMODE);上面的语句查询包含'MySQL'的行，但是如果该行同时包含'database'，则Reducecorrelation。demo6:*SELECT*FROM`fts_articles`WHEREMATCH(title,body)AGAINST('My*'INBOOLEANMODE);以上语句查询关键字中包含'My'的行信息。demo7:"SELECT*FROM`fts_articles`WHEREMATCH(title,body)AGAINST('"MySQLSecurity"'INBOOLEANMODE);上面的语句，查询包含精确词组'MySQLSecurity'的行信息。QueryExpansion查询扩展search是自然语言搜索的变体，通常在查询的关键字太短，用户需要隐含知识（impliedknowledge）时执行。例如，对于词库的查询，用户可能想查询的不是only包含databaseDocuments的词也可能引用包含MySQL、Oracle、RDBMS的词，这时候可以使用QueryExpansion模式开启全文检索的隐含知识，通过添加WITHQUERYEXPANSION/INNATURALLANGUAGEMODEWITHQUERYEXPANSION到查询语句，可以启用盲查询扩展（也称为自动相关性反馈），查询分为两个阶段。第一阶段：根据搜索词进行全文索引查询。第二阶段：基于第一阶段生成的分词的全文搜索查询。那么让我们看一个例子，看看QueryExpansion是如何使用的。--创建索引createFULLTEXTINDEXtitle_body_indexonfts_articles(title,body);--使用自然语言模式查询SELECT*FROM`fts_articles`WHEREMATCH(title,body)AGAINST('database');在使用QueryExpansion之前，查询结果如下：--当使用QueryExpansion方式查询时SELECT*FROM`fts_articles`WHEREMATCH(title,body)AGAINST('database'WITHQUERYexpansion);使用QueryExpansion后查询结果如下：由于QueryExpansion的全文检索可能会带来很多Non-correlationquery，所以用户在使用时可能需要非常谨慎。删除全文索引1、直接删除全文索引语法如下：DROPINDEXfull_idx_nameONdb_name.table_name;2、使用altertable删除全文索引语法如下：ALTERTABLEdb_name.table_nameDROPINDEXfull_idx_name;近期文章推荐：1.1,000+Java面试题及答案（2022最新版）2.厉害了！Java协程来了。..3.SpringBoot2.x教程，太全面了！4.不要用爆破爆满画面，试试装饰者模式，这才是优雅的方式！！5.《Java开发手册（嵩山版）》最新发布，赶快下载吧！感觉不错，别忘了点赞+转发！
