通過使用者名查詢數據 - 對 - 最佳性能

關係型數據庫：MariaDB 10.3

假設您有一個表，它根據給定的使用者名對儲存特定資訊（ 、 等）info_1；info_2像這樣：

+----------+----------+----------+----------+----------+------+--..
|  user_1  |  user_2  |  info_1  |  info_2  |  info_3  |  ..  |  ..
+----------+----------+----------+----------+----------+------+--..

其中 和 的值等於特定user_1且user_2不同的使用者名 ( VARCHAR(50))，並在創建時定義為表的多列索引 (user_1,user_2)。我的應用程序在以下功能中搜尋的查詢：

A）給定的使用者對：

WHERE user_1 = name_1 AND user_2 = name_2 OR user_1 = name_2 AND user_2 = name_1

B）給定使用者/返回特定使用者是成員之一的所有記錄：

WHERE user_1 = name_1 OR user_1 = '%' AND user_2 = name_1

問題：我必須確保搜尋總是被索引。但是，使用OR子句時，每次都會執行全表掃描。

解決方案一）

由於這個答案，導致這些案例的索引搜尋的可能查詢是：

一種）

SELECT * FROM my_table WHERE user_1 = name_1 AND user_2 = name_2
UNION ALL
SELECT * FROM my_table WHERE user_1 = name_2 AND user_2 = name_1;

二）

SELECT * FROM my_table WHERE user_1 = name_1
UNION ALL
SELECT * FROM my_table WHERE user_1 = '%' AND user_2 = name_1;

然而，總是使用兩個SELECT語句和一個WHERE子句的缺點總是徒勞的（因為每個使用者對總是有一個記錄）。這就是為什麼我嘗試通過使用全文索引來進一步優化查詢，結果如下表：

解決方案二）

+---------+----------+----------+----------+------+--..
|  users  |  info_1  |  info_2  |  info_3  |  ..  |  ..
+---------+----------+----------+----------+------+--..

whereusers已被定義為FULLTEXT VARCHAR(150)，其值始終對應於 format name_1-name_2，而我使用連字元是因為在MATCH...AGAINST語法中使用逗號來執行全文索引查詢。

有了這個結構，我現在得到了 A) 和 B) 的以下新查詢可能性：

一種）

SELECT * FROM my_table WHERE MATCH(users) AGAINST ('"name_1-name_2","name_2-name_1"');

二）

SELECT * FROM my_table WHERE MATCH(users) AGAINST ('name_1');

在我看來，這極大地促進了查詢結構，並且既不使用兩個語句，也不使用將徒勞SELECT的附加子句。WHERE儘管如此，由於我對查詢性能優化以及全文索引搜尋與正常索引搜尋的比較知之甚少，我想知道 I 和 II 的哪個解決方案可能執行得更好，為什麼？還是有更好的方法？

危急

1A 不能使用索引，因為OR

1B -user_1 = '%'阻止使用INDEX(user_1, ...)，因此解決方案 1B 已失效。

1B 可以通過有第二個索引來挽救： INDEX(user_2). 然後簡單的說

SELECT * FROM my_table WHERE user_1 = name_1
UNION ALL
SELECT * FROM my_table WHERE user_2 = name_1;

2– FULLTEXT 有限制，例如“單詞”長度。所以要小心。

2A ( WHERE MATCH(users) AGAINST ('"name_1-name_2","name_2-name_1"')– 簡單地說 +可能就足夠了，WHERE MATCH(users) AGAINST (’+name_1 +name_2’ IN BOOLEAN MODE); The` 表示兩者都是必需的，但順序不限。

2B – 好的（有上述注意事項）

FULLTEXT，當它適用時，可能會非常高效。以下是我使用過的兩個技巧：

WHERE MATCH(text) AGAINST("R +James" IN BOOLEAN MODE)

簡短的“單詞”（R）將被忽略；它將有效地搜尋詹姆斯。注意“+”的選擇性使用。

WHERE MATCH(text) AGAINST("R +Anders" IN BOOLEAN MODE)
 AND user LIKE "%R. Anders%"

這假設名稱可能位於大列的中間，我需要檢查初始名稱。FT 測試將是第一個（並且很快），然後是 LIKE（慢，但針對幾行）。

這仍然有問題，因為“E. Anders and R. Anderson”會被錯誤地抓住。

（等等，等等。但我離題了你簡單的 2 字案例。）

底線：

1B（額外索引）是最佳的。（但我懷疑你淡化了這個問題。）

FULLTEXT將是我的下一個選擇。

讓我添加一個選項3：

在插入之前和查詢時對使用者進行排序。那是

INSERT ... (user1, user2)
  VALUES
  (LEAST(?, ?), GREATEST(?, ?))

那麼你只需要（對於案例A INDEX(user1, user2)）

WHERE user1 = LEAST(?, ?)
 AND user1 = GREATEST(?, ?)

唉，這可能會搞砸案例 B。（是否所有行都有 2 個使用者名，但有些查詢只有 1 個使用者？）

和選項 4

有另一個將使用者名映射到my_tableby 的表id。這個新表中的每一行通常有 2 行my_table。可以選擇從my_table.

其餘的細節我就不說了；他們確實變得一團糟。

引用自：https://dba.stackexchange.com/questions/280533