MySql全文索引学习记录

转载：https://blog.csdn.net/bbirdsky/article/details/45368897

1.简要

全文索引可以支持各种字符内容的搜索（包括CHAR、VARCHAR和TEXT类型），也支持自然语言搜索和布尔搜索，虽然在MYSQL中其有很多限制（MYSQL5.6版本之后，InnoDB才可支持全文索引；MyISAM同样支持，但其表级锁的特征对诸多场景并不适用），但与互联网搜索引擎的基本原理还是一样的，所以还是有了解的必要。

2.功能用法

全文搜索的语法：MATCH(col1,col2,…) AGAINST (expr[search_modifier])。

其中MATCH中的内容为已建立FULLTEXT索引并要从中查找数据的列，AGAINST中的expr为要查找的文本内容，search_modifier为可选搜索类型。
search_modifier的可能取值有：IN NATURAL LANGUAGEMODE、IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION、IN BOOLEAN MODE、WITH QUERY EXPANSION。
search_modifier的每个取值代表一种类型的全文搜索，分别为自然语言全文搜索、带查询扩展的自然语言全文搜索、布尔全文搜索、查询扩展全文搜索（默认使用IN NATURAL LANGUAGE MODE）。

注意：在MATCH()函数中指定的列必须和在全文索引中制定的列完全相同，否则就无法使用全文索引。这是因为全文索引不会记录关键字是来自哪一列

2.1自然语言的全文索引基本用法套用规则即可

2.2布尔全文索引

通用修饰符

Example           Meaning

language          包含“language”的行相关贡献值为正

~language        包含“language”的行相关贡献值为负

+language        行记录必须包含“language”

-language         行记录不可以包含“language”

lang*                 包含以“lang”开头的单词的行相关贡献值为正

>language        用于增加包含“language”对其所在行的相关贡献值

<language        用于降低包含“language”对其所在行的相关贡献值

"language"        用于“短语式搜索”，精确匹配包含“language”的行

"tutorial show" @distance  用于指定两个或多个单词相互之间的距离（以单词度量）需在指定的范围内，此处表示“tutorial”与“show”之间间隔在distance范围内

注意：在测试使用时的一个常见的错误就是，只是用很少的数据结合进行全文索引，所以总是无法返回结果。
    原因在于，每个搜索关键词都可能在一半以上的记录里面出现过。

3.查询示例可见转发大佬的4.1小节内容

4.其他注意事项

• 默认情况下全文搜索大小写不敏感
• 对于InnoDB表MATCH()中的列仅能来自于同一个表，因为索引不能快多张表（MyISAM表的的布尔搜索因为可以不使用索引所以可以跨多张表中的列，但速度很慢）。
• MyISAM全文搜索会忽略至少在一半以上数据行中出现的单词（也即所谓的50%阈值），InnoDB无此限制。而在布尔全文搜索中MyISAM的50%阈值不生效。
• 停止字列表也适用于布尔全文搜索。
• 最小和最大词长全文搜索参数也适用于布尔全文搜索。
• MyISAM中的布尔搜索在FULLTEXT索引不存在的时候仍可工作，但速度很慢。而InnoDB表的各类全文搜索必须有FULLTEXT索引，否则会出现找不到与指定列相匹配的FULLTEXT索引的错误
• InnoDB中的全文搜索不支持在单一搜索单词前使用多个操作符如“++MySQL”。MyISAM中全文搜索可以处理这种情况，但是会忽略除了紧邻单词之外的其他操作符。

5.全文索引与LIKE的比较

转发的大佬已经用大量的数据统计出，与LIKE搜索相比，FULLTEXT全文搜索的性能要强很多。但如果存在搜索的关键词太常见的极端情况的话，FULLTEXT会返回太多的记录再判断是否精确匹配短语，相比此时LIKE的顺序读操作反而会快很多。

6.粗略拓展Sphinx全文搜索引擎

Sphinx是一个免费、开源的全文搜索引擎，设计着眼于与数据库完美结合。
    它有类似DBMS的特性，查询速度非常快，支持分布式检索，并且可扩展性好。
    它可以高效利用内存和磁盘I/O，缓解大型操作在这部分的瓶颈。
    它可以被用来加速各种查询，包括全文搜索，也可以用来在其他的应用中执行快速的分组和排序操作。

提供的mysql所没有的功能：

• 快速、高效、可扩展和核心的全文搜索。
• 能在使用低选择性索引或者无索引的列时优化where条件（Sphinx无论是执行索引搜索还是全扫描都快过Mysql，有时Sphinx的全扫描比Mysql的索引读取还要快）
• 优化order by...limit N 查询以及group by 查询（Sphinx通过解决内存与I/O问题）
• 并行地产生结果集（从相同的数据中同时产生几份结果，同样是使用固定量的内存）
• 向上扩展和向外扩展（在单独的机器上增加更多CPU或内核；可以再各机器之间分布）
• 聚合分片数据