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”。