具有限制的 MySQL 全文搜索返回空结果

Question

我有一个这样的表(我删除了所有那些不相关的字段):

CREATE TABLE `products` (
  `id` bigint(20) NOT NULL,
  `keywords` varchar(2000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `KEYWORDS_FTIDX` (`keywords`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

并且有两行:

1. id: 131072, keywords: product1
2. id: 131073, keywords: product2

如果我运行查询 SELECT * FROM products WHERE MATCH(keywords) AGAINST('product1' IN BOOLEAN MODE)，它将按我的预期返回第一行。

但如果我将限制添加到查询 SELECT * FROM products WHERE MATCH(keywords) AGAINST('product1' IN BOOLEAN MODE) LIMIT 10，那么我什么也得不到。

我尝试为 LIMIT SELECT * FROM products WHERE MATCH(keywords) AGAINST('product1' IN BOOLEAN MODE) LIMIT 100 使用更大的数字，它再次起作用。

谁能告诉我如何使它正确吗？

Answer 1

我很久以前读过一些关于全文索引实现的内容。它是关于一些索引引擎只是将任何删除的记录标记为已删除而不是将它们从索引中删除，并且那些删除的项目可以被索引搜索操作命中并在返回给用户之前从最终结果中删除。

所以如果 mysql 以这种方式工作，那么我将 LIMIT 与 MATCH 一起使用，mysql 可能只搜索索引的最开头以获得更好的性能。但是所有这些行都已删除，所以这就是我得到空结果的原因。

然后我做了那些，结果证明了这一点:

1. 使用我在问题部分发布的完全相同的查询创建一个测试表。
2. 插入两行:id:1，关键字:product1，id:2，关键字:product2。
3. 运行搜索查询:SELECT * FROM products WHERE MATCH(keywords) AGAINST('product1' IN BOOLEAN MODE) LIMIT 1，然后我得到了正确的结果。
4. 删除所有这两条记录并重新插入。
5. 运行第 3 步中的查询，然后我什么也没得到。
6. 但如果我运行:SELECT * FROM products WHERE MATCH(keywords) AGAINST('product1' IN BOOLEAN MODE) LIMIT 2，它将再次返回正确的结果。

所以现在，一个快速的解决方法是将匹配条件与另一个匹配条件结合起来。在这种情况下，mysql 将获取比匹配阶段所需的更多内容。此查询有效:SELECT * FROM products WHERE MATCH(keywords) AGAINST('product1' IN BOOLEAN MODE) AND id >= 0 LIMIT 1

我还对查询进行了解释，它也使用了 KEYWORDS_FTIDX 索引。

MySQL Doc( https://dev.mysql.com/doc/refman/5.6/en/innodb-fulltext-index.html ) 中的那些行也可能与问题有关:

删除具有全文索引列的记录可能会导致辅助索引表中的大量小删除，从而使对这些表的并发访问成为争用点。为避免此问题，每当从索引表中删除记录时，已删除文档的文档 ID (DOC_ID) 都会记录在特殊的 FTS__DELETED 表中，并且索引记录保留在全文索引中。在返回查询结果之前，FTS__DELETED 表中的信息用于过滤掉已删除的 Document ID。这种设计的好处是删除速度快且成本低。缺点是删除记录后索引的大小不会立即减小。要删除已删除记录的全文索引条目，您必须在索引表上运行 OPTIMIZE TABLE 并设置 innodb_optimize_fulltext_only=ON 以重建全文索引。有关详细信息，请参阅优化 InnoDB 全文索引。