16 全文本搜索

16.1 理解全文本搜索

  • 并非所有引擎都支持全文本搜索 MySQL支持几种基本的数据库引擎。并非所有的引擎都支持本 书所描述的全文本搜索。两个最常用的引擎为MyISAM和InnoDB，前者支持全文搜索，而后者不支持。这就是为什么虽然本书中创建的多数样例使用InnoDB，而有一个样例表（productnotes）却使用MyISAM的原因。如果你的应用中需要全文本搜索功能，应该记住这一点。
  • 前面介绍了LIKE关键字，它利用通配符操作符匹配文本。使用LIKE，能够查找包含特殊值和部分值得行。
  • 用基于文本的搜索作为正则表达式匹配列值得更一步的介绍。使用正则表达式，可以编写查找所需行的非常复杂的匹配模式。
  • 虽然这些搜索机制非常有用，但存在几个重要的限制。
    • 性能——通配符和正则表达式匹配通常要求MySQL尝试匹配表中所有行（而且这些搜索极少使用表索引）。因此，由于被搜索行数不断增加，这些搜索可能非常耗时。
    • 明确控制——使用通配符和正则表达式匹配，很难明确地控制匹配什么和不匹配什么。例如，指定一个词必须匹配，一个词必须不匹配，而一个词仅在第一个词确实匹配的情况下可以匹配或者才可以不匹配。
    • 智能化的结果——虽然基于通配符和正则表达式的搜索提供了非常灵活的搜索，但它们都不能提供一个智能化的选择结果的方法。例如，一个特殊词的搜索将会返回包含该词的所有行，而不区分包含单个匹配的行和包含多个匹配的行（按照可能是更好的匹配来排列它们）。类似，一个特殊词的搜索不会找出不包含该词但包含其他相关词的行。
  • 所有这些限制以及更多的限制都可以用全文本搜索来解决。在使用全文本搜索时，MySQL不需要分别查看每个行，不需要分别分析和处理每个词。MySQL创建指定列中各词的一个索引，搜索可以针对这些词进行。这样，MySQL可以快速有效地决定哪些词哪些词匹配（哪些行包含它们），哪些词不匹配，它们匹配的频率，等等。

16.2 使用全文本搜索

  • 为了进行全文本搜索，必须索引被搜索的列，而且要随着数据的改变不断地重复索引。在对表进行适当设计后，MySQL会自动进行所有的索引和重新索引。
  • 在索引之后，select可与Match()和Against()一起使用以实际执行搜索。

16.2.1 启用全文本搜索支持

  • 一般在创建表时启用全文本搜索。create table语句接受full text子句，它给出被索引列的一个逗号分隔的列表。
  • 下面的create语句演示了FULLTEXT子句的使用：
CREATE TABLE `productnotes` (
  `note_id` int(11) NOT NULL AUTO_INCREMENT,
  `prod_id` char(10) NOT NULL,
  `note_date` datetime NOT NULL,
  `note_text` text,
  PRIMARY KEY (`note_id`),
  FULLTEXT(`note_text`)
) ENGINE=MyISAM;
  • 这些列中有一个名为note_text的列，为了进行全文本搜索，MySQL根据子句full text(note_text)的指示对它进行索引。这里的FULLTEXT索引单个列，如果需要也可以指定多个列。
  • 在定义之后，MySQL自动维护该索引。在增加、更新或删除行时，索引随之自动更新
  • 可以在创建表时指定full text,或者在稍后指定（这种情况下所有已有数据必须立即索引）。
  • 不要在导入数据时使用FULLTEXT 更新索引要花时间，虽然不是很多，但毕竟要花时间。如 果正在导入数据到一个新表，此时不应该启用FULLTEXT索引。应该首先导入所有数据，然后再修改表，定义FULLTEXT。这样有助于更快地导入数据（而且使索引数据的 总时间小于在导入每行时分别进行索引所需的总时间）。

16.2.2 进行全文本搜索

  • 在索引之后，使用两个函数Match()和Against()执行全文本搜索，其中Match()指定被搜索的列，Against()指 定要使用的搜索表达式。
  • 下面举一个例子：
select note_text
from productnotes
where Match(note_text) Against('rabbit');

+---------------------------------------------------------------------------------------------------------------------+
| note_text                                                                                                           |
+---------------------------------------------------------------------------------------------------------------------+
| Customer complaint: rabbit has been able to detect trap, food apparently less effective now.                        |
| Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait. |
+---------------------------------------------------------------------------------------------------------------------+
  • 此select语句检索单个列note_text。由于where子句，一个全文本搜索被执行。Match(note_text)指示MySQL针对指定的列进行搜索，Against('rabbit')指定词rabbit作为搜索文本。由于有两行包含词rabbit，这两个行被返回。
  • 使用完整的Match()说明 传递给Match()的值必须与FULLTEXT()定义中的相同。如果指定多个列，则必须列出它们（而且次序正确）。
  • 搜索不区分大小写 除非使用BINARY方式(本章中没有介绍)，否则全文本搜索不区分大小写。
  • 事实是刚才的搜索可以简单地用LIKE子句完成，如下所示：
select note_text
from productnotes
where note_text LIKE '%rabbit%';

+---------------------------------------------------------------------------------------------------------------------+
| note_text                                                                                                           |
+---------------------------------------------------------------------------------------------------------------------+
| Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait. |
| Customer complaint: rabbit has been able to detect trap, food apparently less effective now.                        |
+---------------------------------------------------------------------------------------------------------------------+
  • 这条select语句同样检索出两行，但次序不同(虽然并不总是出现这种情况)。
  • 上述两条select语句都不包含order by子句。后者（使用LIKE）以不特别有用的顺序返回数据。前者（使用全文本搜索）返回以文本匹配的良好程度排序的数据。两个行都包含词rabbit，但包含词rabbit作为第3个词的行的等级比作为第20个词的行高。这很重要。全文本搜索的一个重要部分就是对结果排序。具有较高等级的行返回（因为这些行很可能是你真正想要的行）。
  • 为演示排序如何工作，请看以下例子：
select note_text,
        Match(note_text) Against('rabbit') AS rank
from productnotes;

+----------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+
| note_text                                                                                                                                                | rank               |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+
| Customer complaint:
Sticks not individually wrapped, too easy to mistakenly detonate all at once.
Recommend individual wrapping.                           |                  0 |
| Can shipped full, refills not available.
Need to order new can if refill needed.                                                                          |                  0 |
| Safe is combination locked, combination not provided with safe.
This is rarely a problem as safes are typically blown up or dropped by customers.         |                  0 |
| Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait.                                      | 1.5905543565750122 |
| Included fuses are short and have been known to detonate too quickly for some customers.
Longer fuses are available (item FU1) and should be recommended. |                  0 |
| Matches not included, recommend purchase of matches or detonator (item DTNTR).                                                                           |                  0 |
| Please note that no returns will be accepted if safe opened using explosives.                                                                            |                  0 |
| Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils. |                  0 |
| Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes.                                      |                  0 |
| Customer complaint: rabbit has been able to detect trap, food apparently less effective now.                                                             | 1.6408053636550903 |
| Shipped unassembled, requires common tools (including oversized hammer).                                                                                 |                  0 |
| Customer complaint:
Circular hole in safe floor can apparently be easily cut with handsaw.                                                                |                  0 |
| Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.   |                  0 |
| Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be added.
Comment forwarded to vendor.                            |                  0 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+
  • 这里，在select而不是where子句中使用Match()和Against()。这使所有行都被返回（因为没有where子句）。Match()和Against()用来建立一个计算列（别名为rank）,此列包含全文本搜索计算出的等级值。等级由MySQL根据行中词的数目、唯一词的数目、整个索引中的词的总数以及包含该词的数目计算出来。正如所见，不包含词rabbit的行等级为0（因此不被前一例子中的where子句选择）。确实包含词rabbit的两个行每行都有一个 等级值，文本中词靠前的行的等级值比词靠后的行的等级值高。
  • 这个例子有助于说明全文本搜索如何排除行（排除那些等级为0的行），如何排序结果（按等级以降序排序）。
  • 排序多个搜索项 如果指定多个搜索项，则包含多数匹配词的那些行将具有比包含较少词的那些行高的等级值。
  • 正如所见，全文本搜索提供了简单LIKE搜索不能提供的功能。而且，由于数据是索引的，全文本搜索还相当快。

16.2.3 使用查询扩展

  • 查询扩展用来设法放宽所返回的全文本搜索结果的范围。考虑下面的情况。你想找出所有提到anvils的注释。只有一个注释包含词anvils，但你还想找出可能与你的搜索有关的所有其他行，即使它们不包含词anvils。 * 这也是查询扩展的一项任务。在使用查询扩展时，MySQL对数据和索引进行两遍扫描来完成搜索：
    • 首先，进行一个基本的全文本搜索，找出与搜索条件匹配的所有行
    • 其次，MySQL检查这些匹配并选择所有有用的词
    • 再其次，MySQL再次进行全文本搜索，这次不仅使用原来的条件，而且还使用所有有用的词。
  • 利用查询扩展，能找出可能相关的结果，即使它们并不精确包含所查找的词。
  • 下面举一个例子，首先进行一个简单的全文本搜索，没有查询扩展：
select note_text
from productnotes
where Match(note_text) Against('anvils');

+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| note_text                                                                                                                                                |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils. |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
  • 下面是相同的搜索，这次使用查询扩展：
select note_text
from productnotes
where Match(note_text) Against('anvils' with query expansion);

+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| note_text                                                                                                                                                |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils. |
| Customer complaint:
Sticks not individually wrapped, too easy to mistakenly detonate all at once.
Recommend individual wrapping.                           |
| Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.   |
| Please note that no returns will be accepted if safe opened using explosives.                                                                            |
| Customer complaint: rabbit has been able to detect trap, food apparently less effective now.                                                             |
| Customer complaint:
Circular hole in safe floor can apparently be easily cut with handsaw.                                                                |
| Matches not included, recommend purchase of matches or detonator (item DTNTR).                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
  • 这次返回了7行。第一行包含词anvils，因此等级最高。第二行与anvils无关，但因为它包含第一行中的两个词（customers和recommend），所以也被检索出来。第3行也包含这两个相同的词，但它们在文本中的位置更靠后且分开得更远，因此也包含这一行，但等级为第3.第3行确实也没有涉及anvils（按它们的产品名）。
  • 正如所见，查询扩展极大地增加了返回的行数，但这样做也增加了你实际上并不想要的行的数目。
  • 行越多越好 表中的行越多，使用查询扩展的结果越好。

16.2.4 布尔文本搜索

  • MySQL支持全文本搜索的另一种形式，称为布尔方式。以布尔方式，可以提供关于如下内容的细节：
    • 要匹配的词
    • 要排斥的词（如果某行包含这个词，则不返回该行，即使它包含其他指定的词也是如此）
    • 排列提示（指定某些词比其他词更重要，更重要的词等级更高）
    • 表达式分组
    • 另外一些内容
  • 即使没有FULLTEXT索引也可以使用 布尔方式不同于迄今为止使用的全文本搜索语法的地方在于，即使没有定义FULLTEXT索引，也可以使用它。但这是一种非常缓慢的操作（其性能将随着数据量的增加而降低）。
  • 为演示IN BOOLEAN MODE的作用，举一个简单的例子：
select note_text
from productnotes
where Match(note_text) Against('heavy' IN BOOLEAN MODE);

+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| note_text                                                                                                                                              |
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes.                                    |
| Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead. |
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
  • 此全文本搜索检索包含词heavy的所有行（有两行）。其中使用了关键字IN BOOLEAN MODE，但实际上没有指定布尔操作符，因此，其结果与没有指定布尔方式的结果相同。
  • IN BOOLEAN MODE的行为差异 虽然这个例子的结果与没有IN BOOLEAN MODE的相同，但其行为有一个重要的差别（即使在这个特殊的例子没有表现出来）。
  • 为了匹配包含heavy但不包含任意以rope开始的词的行，可使用以下查询：
select note_text
from productnotes
where Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE);

+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| note_text                                                                                                                                              |
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead. |
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
  • 这次只返回一行。这一次仍然匹配词heavy，但-rope明确地指示MySQL排除包含rope(任何以rope开始的词，包括ropes)的行，这就是为什么上一个例子的第一行被排除的原因。
  • 我们已经看到了两个全文本搜索布尔操作符-和，-排除一个词，而是截断操作符（可想象为用于词尾的一个通配符）。
  • 列出支持的所有布尔操作符。
布尔操作符 说明
+ 包含，词必须存在
- 排除，词必须不出现
> 包含，而且增加等级值
< 包含，且减少等级值
（） 把词组成子表达式（允许这些子表达式作为一个组被包含、排除、排列等）
~ 取消一个词的排序值
* 词尾的通配符
"" 定义一个短语（与单个词的列表不一样，它匹配整个短语以便包含或排除这个短语）
  • 下面举几个例子，说明某些操作符如何使用:
select note_text
from productnotes
where Match(note_text) Against('+rabbit +bait' IN BOOLEAN MODE);
  • 这个搜索匹配包含词rabbit和bait的行。
select note_text
from productnotes
where Match(note_text) Againt('rabbit bait' IN BOOLEAN MODE);
  • 没有指定操作符，这个搜索匹配包含rabbit和bait中的至少一个词的行。
select note_text
from productnotes
where Match(note_text) Againt('"rabbit bait"' IN BOOLEAN MODE);
  • 这个搜索匹配短语rabbit bait而不是匹配两个词rabbit和bait。
select note_text
from productnotes
where Match(note_text) Againt('rabbit bait' IN BOOLEAN MODE);
  • 这个搜索匹配短语rabbit bait而不是匹配两个词rabbit和bait。
select note_text
from productnotes
where Match(note_text) Againt('>rabbit <carrot' IN BOOLEAN MODE);
  • 匹配rabbit和carrot，增加前者的等级，降低后者的等级。
select note_text
from productnotes
where Match(note_text) Against('+safe +(<combination)' IN BOOLEAN MODE);
  • 这个搜索匹配词safe和combination，降低后者的等级。
  • 排序而不排序 在布尔方式中，不按等级值降序排序返回的行。

16.2.5 全文本搜索的使用说明

  • 关于全文本搜索的某些重要的说明。
    • 在索引全文本数据时，短词被忽略且从索引中删除。短词定义为那些具有3个或3个以下字符的词（如果需要，这个数目可以更改）
    • MySQL带有一个内建的非用词列表，这些词在索引全文本数据时总是被忽略。如果需要，可以覆盖这个列表
    • 许多词出现的频率很高，搜索它们没有用处（返回太多的结果）。因此，MySQL规定了一条50%规则，如果一个词出现在50%以上的行中，则将它作为一个非用词忽略。50%规则不用于IN BOOLEAN MODE。
    • 如果表中行数少于3行，则全文本搜索不返回结果（因为每个词或者不出现，或者至少出现在50%的行中）。
    • 忽略词中的单引号。例如，don't索引为dont
    • 不具有词分隔符（包括日语和汉语）的语言不能恰当地返回全文本搜索结果。
    • 如前所述，仅在MyISAM数据库引擎中支持全文本搜索。
  • 没有邻近操作符 它能搜索相邻的词。MySQL全文本搜索现在还不支持邻近操作符。
posted @ 2018-01-14 17:40  _sanjun  阅读(151)  评论(0)  编辑  收藏  举报