理解全文本搜索

之前学习到了用LIKE关键字，利用通配符来匹配文本，还有通过正则表达式来匹配文本。不过这些匹配都有些缺点：

  • 性能：通配符和正则表达式匹配通常要求MySQL尝试匹配表中所有行。如果搜索行越长匹配就越耗时。
  • 明确控制：通配符和正则表达式很难做到精细的控制，控制在匹配过程中哪些需要匹配哪些需要不匹配，这样会影响性能无法做到优化。
  • 智能化结果：基于通配符和正则表达式的匹配得到的结果你无法控制匹配数量，比方说匹配的关键词是#，在匹配的全文中有多个#,但是我无法控制结果中只返回一个或者n个。
    而这些限制都能通过全文本搜索来解决。

使用全文本搜索

一般在建表时启用全文本搜索。
MySQL最常用的两个引擎为MyISAM和InnoDB
MyISAM：支持全文索引
InnoDB：不支持全文索引

CREATE TABLE books(
  id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  title varchar(200),
  content text,
  FULLTEXT(content) WITH PARSER ngram)
  ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='书籍表';

插入数据

INSERT INTO books(
  id,
  title,
  content) VALUES(
    0,
    'Love of Life',
    'The house of Blencarrow, which, without being one of the great houses of the county, was as comfortable and handsome as a country gentleman not exactly of the highest importance could desire');

执行全文本搜索

SELECT * FROM books WHERE MATCH(content) AGAINST('county');

+----+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | title        | content                                                                                                                                                                                        |
+----+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | Love of Life | The house of Blencarrow, which, without being one of the great houses of the county, was as comfortable and handsome as a country gentleman not exactly of the highest importance could desire |
+----+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

在执行全文本搜索时有几点需要注意：

  • 在执行全文本搜索语句时，MATCH()中需要传入该表所有支持全文搜索的字段。
  • 有些常用关键词不能用作检索，否则将返回空。
  • 目前新版本的MySQL，InnDB引擎已经支持了全文本搜索并且能识别中文。

匹配结果排序

同时全文本搜索会对表中的匹配结果进行排序，它们的优先级按照匹配词的总数以及包含该词的行的数目，还有匹配词的越靠前优先级越高。
可以通过以下命令查询权重：

SELECT body, MATCH(title,body) AGAINST('two') AS q FROM articles;

WechatIMG69.png

使用查询扩展

查询扩展的意思是放宽查询结果的范围。什么意思？就是指查询除了包含关键词的行之外还可以另外找出与搜索有关的其他行。该功能普遍用于联想搜索等，当然现在有了推荐算法会比这个好很多。
在查询扩展过程中，MySQL对数据进行了两遍扫描：

  • 首先进行最基本的扫描来搜索条件匹配的所有行。
  • 随后MySQL会对这些匹配行做过滤选择游泳的行
  • 最后再进行全文本搜索，这次不仅匹配关键词，还匹配与关键词所在行有关的行。

示例：

SELECT * FROM articles WHERE MATCH(title,body) AGAINST('database' WITH QUERY EXPANSION);

+----+-----------------------+------------------------------------------+
| id | title                 | body                                     |
+----+-----------------------+------------------------------------------+
|  5 | MySQL vs. YourSQL     | In the following database comparison ... |
|  1 | MySQL Tutorial        | DBMS stands for DataBase ...             |
|  3 | Optimizing MySQL      | In this tutorial we will show ...        |
|  6 | MySQL Security        | When configured properly, MySQL ...      |
|  2 | How To Use MySQL Well | After you went through a ...             |
|  4 | 1001 MySQL Tricks     | 1. Never run mysqld as root. 2. ...      |
+----+-----------------------+------------------------------------------+

看到这组数据会很奇怪，为什么会把没有包含database的行也匹配到了。这个就是查询扩展的特性，它除了匹配关键词所在的行之外还会从匹配到的行（id=5,1)这两条数据中找到合适的关联词MySQL，再对表进行一次匹配，从而查找到了（id=3,6,2,4)的行。

Boolean全文本搜索

MySQL可以使用IN BOOLEAN MODE修饰符来执行全文本搜索。它的作用是能够通过+和-来表示匹配中哪些单词必须存在，哪些单词不能存在。

SELECT * FROM articles WHERE MATCH(title,body) AGAINST('+MySQL -YourSQL' IN BOOLEAN MODE);

+----+-----------------------+-------------------------------------+
| id | title                 | body                                |
+----+-----------------------+-------------------------------------+
|  6 | MySQL Security        | When configured properly, MySQL ... |
|  1 | MySQL Tutorial        | DBMS stands for DataBase ...        |
|  2 | How To Use MySQL Well | After you went through a ...        |
|  3 | Optimizing MySQL      | In this tutorial we will show ...   |
|  4 | 1001 MySQL Tricks     | 1. Never run mysqld as root. 2. ... |
+----+-----------------------+-------------------------------------+
  • +等同于AND
  • -等同于NOT

Boolean全文本搜索具有以下特征：

  • 这种搜索不会改变相关性顺序，优先级仍旧与默认搜索相同
  • InnoDB与MyISAM在全文本搜索的不同点在于，前者的Boolean查询需要使用全文本索引，而后者则不需要。当然这样的话效率会低很多。
  • 最小和最大字长 full-text 参数适用于使用 built-in FULLTEXT解析器和 MeCab 解析器插件创建的FULLTEXT索引。 innodb_ft_min_token_size和innodb_ft_max_token_size用于InnoDB搜索索引。 ft_min_word_len和ft_max_word_len用于MyISAM搜索索引。

全文本布尔操作符

布尔操作符 说明
+ 包含，词必须存在
- 排除，词必须不出现
> 包含并且增加等级值
< 包含且减少等级值
() 把词组成子表达式
~ 取消一个词的排序值
* 词尾的通配符
“” 定义一个短语(与单个词的列表不一样，它匹配整个短语) 
SELECT * FROM articles WHERE MATCH(title,body) AGAINST('MySQL use' IN BOOLEAN MODE);

+----+-----------------------+------------------------------------------+
| id | title                 | body                                     |
+----+-----------------------+------------------------------------------+
|  2 | How To Use MySQL Well | After you went through a ...             |
|  6 | MySQL Security        | When configured properly, MySQL ...      |
|  1 | MySQL Tutorial        | DBMS stands for DataBase ...             |
|  3 | Optimizing MySQL      | In this tutorial we will show ...        |
|  4 | 1001 MySQL Tricks     | 1. Never run mysqld as root. 2. ...      |
|  5 | MySQL vs. YourSQL     | In the following database comparison ... |
+----+-----------------------+------------------------------------------+

这种没有添加操作符的Boolean搜索，会匹配MySQL与use中至少一个关键词。

SELECT * FROM articles WHERE MATCH(title,body) AGAINST('<DBMS >Well' IN BOOLEAN MODE);

+----+-----------------------+------------------------------+
| id | title                 | body                         |
+----+-----------------------+------------------------------+
|  2 | How To Use MySQL Well | After you went through a ... |
|  1 | MySQL Tutorial        | DBMS stands for DataBase ... |
+----+-----------------------+------------------------------+

匹配DBMS与Well,同时降低前者等级，提高后者等级

全文本搜索使用说明

  • 在索引全文本数据时，短词被忽略且从索引中删除。短词定义为那些具有3个或3个以下字符的词。（指的是英文，如果需要可以更改限制）
  • MySQL带有一个内建的非用词列表，这些词在索引群文本数据时会被忽略。如果有需要可以覆盖这个列表。（中文的不清楚有没有这样的表）
  • 许多词出现的频率高，如果出现频率高于50%，那么MySQL将忽略它。（In Boolean Mode模式除外）
  • 如果表中的行数少于3行，则全文本搜索不返回结果。