MySQL中的复合全文索引

Question

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

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

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

1. 简单，非唯一索引user_id

   
   

   它将仅过滤特定的用户消息，然后全面扫描特定单词。

2. 消息的全文索引

   
   

   这会找到所有用户的所有消息，然后按 ID 进行过滤，在用户量很大的情况下似乎效率很低。

3. user_id 和消息的复合索引

   
   

   因此，全文索引树是为每个用户单独创建的，因此可以单独搜索。在查询过程中，系统按ID过滤消息，然后对索引中的剩余行执行文本搜索。

据我所知。最后一项是不可能的。那么我假设我应该使用第一个选项，如果有几千个用户，它会表现得更好吗？

如果每个消息都有大约 100 条消息，完整迭代不会花费太多资源吗？

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

Answer 1

您应该在 message 上添加全文索引，在 user_id 上添加常规索引，并使用以下查询：

SELECT *
FROM messages
WHERE MATCH(message) AGAINST(@search_query)
AND user_id = @user_id;

您说得对，您不能执行选项 3。但是，与其尝试在 1 和 2 之间进行选择，不如让 MySQL 为您完成这项工作。 MySQL只会使用这两个索引之一，并会进行线性扫描来完成第二次过滤，但它会估计每个索引的有效性并选择最佳的一个。

注意：仅当您可以承受两个索引的开销（较慢的插入/更新/删除）时才执行此操作。另外，如果您知道每个用户只会有几条消息，那么使用简单的索引并在应用程序层中执行正则表达式或类似的操作可能是有意义的。

Answer 2

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

实际发生的情况是这样的：

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

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

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

出现这种情况的原因是全文索引不会向优化器提供任何有意义的统计信息。它只是说“是的，我认为查询可能只需要我检查 1 行”...当然，这极大地满足了优化器的需要，因此全文索引以最低的成本赢得了投标，除非索引具有整数价值也相对较低或更低。

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

还有另一个选项，最适合全文查询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”。