如何在不改变逻辑的情况下优化这个SQL查询?

Question

我正在开发一个聊天应用程序。我已经编写了一个SQL来获取与用户关联的活动聊天记录。它过去是根据最后一条消息发送的时间按降序排序来获取用户对用户的聊天和组聊天。我试图优化这个查询。我正在使用MySQL

group_attributes表模式:

+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| Chat_Group_id | int          | NO   | PRI | NULL    | auto_increment |
| IsGroup       | tinyint(1)   | NO   |     | NULL    |                |
| name          | varchar(20)  | YES  |     | NULL    |                |
| description   | varchar(500) | YES  |     | NULL    |                |
| createdAt     | datetime     | NO   |     | NULL    |                |
| updatedAt     | datetime     | NO   |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+

当chat_group_messages表中添加一条消息记录时，UpdatedAt列将更新为当前时间戳。

chat_groups表模式:

+---------------+------------+------+-----+---------+----------------+
| Field         | Type       | Null | Key | Default | Extra          |
+---------------+------------+------+-----+---------+----------------+
| id            | int        | NO   | PRI | NULL    | auto_increment |
| Chat_Group_id | int        | NO   |     | NULL    |                |
| user_id       | int        | NO   |     | NULL    |                |
| createdAt     | datetime   | NO   |     | NULL    |                |
| updatedAt     | datetime   | NO   |     | NULL    |                |
| admin         | tinyint(1) | NO   |     | 0       |                |
+---------------+------------+------+-----+---------+----------------+

chat_group_messagestable:

+---------------+---------------+------+-----+---------+----------------+
| Field         | Type          | Null | Key | Default | Extra          |
+---------------+---------------+------+-----+---------+----------------+
| id            | int           | NO   | PRI | NULL    | auto_increment |
| Chat_Group_id | int           | NO   |     | NULL    |                |
| user_id       | int           | NO   |     | NULL    |                |
| message       | varchar(5000) | NO   |     | NULL    |                |
| createdAt     | datetime      | NO   |     | NULL    |                |
| updatedAt     | datetime      | NO   |     | NULL    |                |
+---------------+---------------+------+-----+---------+----------------+

我还没有在上述模式中添加外键，因为我还没有完全理解关联如何在sequalize模块中工作。

**查询1 **这用于查找与登录用户相关联的用户间聊天。从group_attributes表中选择用户组，并将其与chat_groups表中的chat_group_id列和users表中的user_id列连接，然后使用:

1. 检查用户是否存在于组中，以及chat_group_messages
表中是否至少存在该组的一条消息。
2. isGroup标志为false
3. user_id不等于请求用户(已登录用户)
4. 登录用户是否与其他用户在一个组中

第1点是需要的，因为我的前端的工作方式是在执行某些操作时在用户之间创建任意组(我将在将来更改此行为以删除此过滤器)

第2点检查是否为Group

第3点和第4点需要在检查是否存在于组中时过滤掉登录用户的记录

查询2：这是用来查找与登录用户相关联的群聊。

从group_attributes表中选择并在chat_group_id列上对chat_groups表执行内连接，并且user_id等于登录的用户id。该查询根据IsGroup标志是否为真来过滤它，即该关联是包含2个或更多用户的组。

在最后，两个查询的联合被采取，它是按列UpdatedAt降序排序，以获得最近更新的列，即消息最后发送的组。

select *  from (
select 
ca.chat_group_id , ca.isgroup , 
u.user_id ,  u.username , 
name group_name , description as  group_description , 
ca.updatedat
from group_attributes ca
inner join chat_groups cg on ca.chat_group_id = cg.chat_group_id
inner join users u on cg.user_id = u.user_id  
where 
exists ( select user_id from chat_group_messages cgm where  cgm.chat_group_id = ca.chat_group_id )
and isgroup =  false and u.user_id != ${logged_user.id} 
and ${logged_user.id} in (
select  cg2.user_id  from chat_groups cg2 
where cg.Chat_Group_id = cg2.Chat_Group_id
)

union 

select 
ca.chat_group_id , ca.isgroup , 
null as user_id , null as username , 
ca.name as group_name , ca.description as group_description , 
ca.updatedat 
from group_attributes ca
inner join chat_groups cg on cg.chat_group_id = ca.chat_group_id and cg.user_id = ${logged_user.id}
where isgroup = true 
) as temp 
order by updatedat desc ;

group_attributes表应该很小，而其他两个表应该比较大。

这个查询在我的开发数据库中运行得很快。但我认为在生产环境中，这将需要很长时间，并且可能会锁定表一段时间，从而减慢整个应用程序的速度。

我不知道如何在不破坏这个逻辑的情况下优化这个查询。

解释查询显示如下:

+----+-------------------+------------+------------+--------+---------------+---------+---------+--------------------------+------+----------+--------------------------------------------+
| id | select_type       | table      | partitions | type   | possible_keys | key     | key_len | ref                      | rows | filtered | Extra                                      |
+----+-------------------+------------+------------+--------+---------------+---------+---------+--------------------------+------+----------+--------------------------------------------+
|  1 | PRIMARY           | <derived2> | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                     |    8 |      100 | Using filesort                             |
|  2 | DERIVED           | cg2        | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                     |   66 |       10 | Using where; Start temporary               |
|  2 | DERIVED           | ca         | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | webapp.cg2.Chat_Group_id |    1 |       10 | Using where                                |
|  2 | DERIVED           | cgm        | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                     |   16 |       10 | Using where; Using join buffer (hash join) |
|  2 | DERIVED           | cg         | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                     |   66 |       10 | Using where; Using join buffer (hash join) |
|  2 | DERIVED           | u          | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | webapp.cg.user_id        |    1 |      100 | End temporary                              |
|  5 | UNCACHEABLE UNION | cg         | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                     |   66 |       10 | Using where                                |
|  5 | UNCACHEABLE UNION | ca         | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | webapp.cg.Chat_Group_id  |    1 |       10 | Using where                                |
|  6 | UNION RESULT      | <union2,5> | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                     | NULL |     NULL | Using temporary                            |
+----+-------------------+------------+------------+--------+---------------+---------+---------+--------------------------+------+----------+--------------------------------------------+

Answer 1

如果将group_attributes重命名为chat_groups，将当前chat_groups重命名为chat_group_users，您的表将更有意义。

删除chat_groups上不必要的AI PK，代之以PK (Chat_Group_id, user_id)和二级索引(user_id, Chat_Group_id)

同时，避免混淆大小写和下划线的组合。选择一个惯例并坚持下去。所有带下划线的小写是最可移植的，但无论你选择什么，都要保持一致。

除了更改chat_groups的结构外，还需要在chat_group_messages (Chat_Group_id)上创建索引。

这应该给出相同的结果:

SELECT
ga.Chat_Group_id, ga.IsGroup,
ga.name as group_name, ga.description as group_description, ga.updatedAt,
u.user_id ,  u.username
FROM group_attributes ga
LEFT JOIN chat_groups cg
ON ga.IsGroup = 0
AND ga.Chat_Group_id = cg.Chat_Group_id
AND cg.user_id <> ${logged_user.id}
LEFT JOIN users u
ON cg.user_id = u.user_id
WHERE EXISTS (
SELECT 1 FROM chat_groups
WHERE Chat_Group_id = ga.Chat_Group_id AND user_id = ${logged_user.id}
)
AND EXISTS (
SELECT 1 FROM chat_group_messages
WHERE Chat_Group_id = ga.Chat_Group_id
)
ORDER BY ga.updatedAt DESC;

Answer 2

请限定每个列名;很难跟踪来自哪个表的内容。

根据user1191247的重新表述，我建议添加以下复合索引:

ga:  INDEX(IsGroup,  Chat_Group_id, description, updatedAt)
ga:  INDEX(Chat_Group_id)
u:   INDEX(user_id,  username)
cg:  INDEX(Chat_Group_id,  user_id)
chat_group_messages:  INDEX(Chat_Group_id)