主页 / dba / 问题 / 280533
Accepted
DevelJoe
Asked: 2020-11-30 07:10:22 +0800 CST

通过用户名查询数据 - 对 - 最佳性能

  • 772

关系型数据库：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 的哪个解决方案可能执行得更好，为什么？还是有更好的方法？

optimization query-performance

1 个回答

  1. Best Answer

    批判

    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.

    其余的细节我就不说了；他们确实变得一团糟。

    • 0

相关问题