mysql - MySQL 中的复合 FULLTEXT 索引

Question

我想制作允许按特定用户搜索用户消息的系统。假设有下表

create table messages(
  user_id int,
  message nvarchar(500));

那么，如果我想搜索来自用户 1 的所有包含单词“foo”的消息，我应该在这里使用什么样的索引。

1. 简单，非唯一索引user_id
   它将仅过滤特定的用户消息，然后全面扫描特定的词。
2. FULLTEXT 索引消息
   这会找到来自所有用户的所有消息，然后按 ID 过滤，在用户数量很大的情况下似乎效率很低。
user_id 和 message
上的
3. compound 索引所以全文索引树是为每个用户单独创建的，所以他们可以单独搜索。在查询期间，系统按ID 过滤消息，然后对索引中剩余的行执行文本搜索。

A.F.A.I.K.最后一个是不可能的。那么我假设我应该使用第一个选项，它会在几千个用户的情况下表现更好吗？

如果每个人都有大约 100 条消息，那么完整的迭代不会消耗太多资源？

也许我可以在消息中包含用户名并使用 BOOLEAN 全文搜索模式，但我认为这会比使用索引 user_id 慢。

Answer 1

@Alden Quimby 的回答就目前而言是正确的，但还有更多的故事，因为 MySQL 只会尝试选择最佳索引，而它做出决定的能力是有限的因为全文索引与优化器交互的方式。

实际发生的是这样的:

如果指定的 user_id 存在于表中的 0 或 1 个匹配行中，优化器将意识到这一点并选择 user_id 作为该查询的索引。快速执行。

否则，优化器将选择全文索引，过滤与全文索引匹配的每一行，以消除不包含与 WHERE 子句匹配的 user_id 的行。没那么快。

所以这并不是真正的“最佳”路径。它更像是全文，有一个很好的优化来避免在我们知道我们对表几乎没有任何兴趣的情况下进行全文搜索。

失败的原因是全文索引不会将任何有意义的统计信息返回给优化器。它只是说“是的，我认为查询可能只需要我检查 1 行”......当然，这让优化器非常满意，所以全文索引以最低成本赢得投标，除非索引具有整数值(value)也相对较低或更低。

不过，这并不意味着我不会先尝试这种方式。

还有另一种选择，它最适合全文查询 IN BOOLEAN MODE，那就是创建另一个列，您可以用类似 CONCAT('user_id_',user_id) 或类似的东西填充它，然后声明一个 2 列的全文索引。

filter_string VARCHAR(48) # populated with CONCAT('user_id_',user_id);
....
FULLTEXT KEY (message,filter_string)

然后在查询中指定所有内容。

SELECT ...
 WHERE user_id = 500 AND
 MATCH (message,filter_string) AGAINST ('+kittens +puppies +user_id_500' IN BOOLEAN MODE);

现在，全文索引将负责仅匹配那些在两列的组合全文索引中出现 kittens、puppies 和“user_id_500”的行，但您仍然希望在那里也有整数过滤器尽管消息中随机出现“user_id_500”，请确保最终结果受到约束。