Zusammengesetzter Volltextindex in MySQL

Question

Ich möchte, dass das System die Suche nach Benutzernachrichten nach bestimmten Benutzern ermöglicht. Angenommen, wir haben die folgende Tabelle

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

Wenn ich alle Nachrichten von Benutzer 1 durchsuchen möchte, die das Wort „foo“ enthalten, welchen Index soll ich hier verwenden?

1. Einfache, nicht eindeutige Index-Benutzer-ID

   
   

   Es filtert nur bestimmte Benutzernachrichten und scannt dann vollständig nach bestimmten Wörtern.

2. VolltextIndex der Nachrichten

   
   

   Dadurch werden alle Nachrichten für alle Benutzer gefunden und dann nach ID gefiltert, was bei einem großen Benutzervolumen ineffizient erscheint.

3. ZusammengesetzterIndex aus

   Benutzer-ID
   und

   Nachricht

   Daher wird der Volltextindexbaum für jeden Benutzer individuell erstellt und kann somit individuell durchsucht werden. Während der Abfrage filtert das System Nachrichten nach ID und führt dann eine Textsuche in den verbleibenden Zeilen im Index durch.

Soweit ich weiß. Der letzte Punkt ist unmöglich. Daher gehe ich davon aus, dass ich die erste Option verwenden sollte. Wird sie bei einigen tausend Benutzern eine bessere Leistung erbringen?

Würde eine vollständige Iteration nicht zu viele Ressourcen kosten, wenn es jeweils etwa 100 Nachrichten gäbe?

Vielleicht könnte ich den Benutzernamen in die Nachricht einfügen und den BOOLEAN-Volltextsuchmodus verwenden, aber ich denke, das wäre langsamer als die Verwendung des Index 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”。