首页>Database>2020-12-02 11:30source

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

RDBMS：MariaDB 10.3

假设您有一个存储特定信息的表（ info_1 ， info_2 ，等等）根据给定的用户名对；像这样：

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

其中 user_1的值和 user_2 每个都等于一个特定且不同的用户名（ VARCHAR(50) ），并且在创建时被定义为表的多列索引（用户_1，用户_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时子句，但是每次都会执行全表扫描。

SOLUTION I)

感谢此答案，可能的查询导致针对这些情况的索引搜索是：

A）

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;

B）

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 子句永远是徒劳的（因为每个用户对总是有一条记录）.这就是为什么我尝试通过使用全文索引，生成了下表：

SOLUTION II)

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

其中 users 被定义为 FULLTEXT VARCHAR(150) ，其值始终对应于 name_1-name_2格式，而我使用连字符是因为在 MATCH...AGAINST中使用逗号执行全文索引查询的语法。

有了这种结构，我现在获得以下针对A）和B）的新查询可能性：

A）

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

B）

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

在我看来，这极大地简化了查询结构，并且不使用两个 SELECT 声明，也无需其他 WHERE 条款将是徒劳的.不过，由于我对查询性能优化以及全文索引搜索与常规索引搜索的比较了解不多，我想知道I和II的哪个解决方案可能会更好，为什么？还是有更好的方法？

1月前
1 #
Critique

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）将被忽略；它将有效地搜索James.请注意选择性使用" +"。
```
WHERE MATCH(text) AGAINST("R +Anders" IN BOOLEAN MODE)
  AND user LIKE "%R. Anders%"
```
这假设名称可能在大列的中间，并且我需要检查名字的首字母. FT测试将首先（快速），然后是LIKE（缓慢，但只有很少的几行）。

那仍然有问题，因为" E. Anders和R. Anderson"会被错误地抓住。

（等等），但我偏离了您简单的2词用例。）

Bottom line:

1B（额外索引）是最佳的. （但是我怀疑您淡化了这个问题。）

FULLTEXT 将是我的下一个选择。

Let me add an option 3:

在插入之前和查询时对用户进行排序.那是
```
INSERT ... (user1, user2)
   VALUES
   (LEAST(?, ?), GREATEST(?, ?))
```
那么您只需要（对于案例A） INDEX(user1, user2) 与
一起
```
WHERE user1 = LEAST(?, ?)
  AND user1 = GREATEST(?, ?)
```
A，这可能会弄乱情况B。（所有行是否有2个用户名，但是some查询只有1个用户？）

And Option 4

具有另一个将用户名映射到 my_table的表由 id .通常在此新表中有2行，对于 my_table中的每一行 . （可选）从 my_table扔掉用户列

我将不再赘述其他细节；他们确实很混乱。

Codebug

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

queryperformance

optimization

mariadb

performancetuning

相关问题