mysql 如何在不更改其逻辑的情况下优化此SQL查询？

我正在开发一个聊天应用程序。我已经写了一个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_messages表：

+---------------+---------------+------+-----+---------+----------------+
| 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    |                |
+---------------+---------------+------+-----+---------+----------------+

我还没有在上面的模式中添加forign键，因为我还没有完全理解关联在序列化模块中是如何工作的。

查询1：用于查找与登录用户关联的用户对用户聊天。它从group_attributes表中选择用户组，并将其与chat_group_id列上的chat_groups表和user_id列上的users表连接，然后使用以下内容过滤它们：

1.检查用户是否存在于组中，以及该组的至少1条消息是否存在于表chat_group_messages中

isGroup标志为假
user_id不等于请求用户（登录用户）
1.已登录用户是否与另一个用户在一个组中
第1点是必要的，因为我的前端工作的方式，它创建任意用户之间的组时，执行某些行动（我将改变这一行为在未来删除此过滤器）
点2检查是否为组
点3和点4用于在检查组中是否存在已登录用户的记录时过滤掉这些记录

查询2：用于查找与登录用户关联的群聊。

这从group_attributes表中选择，并在chat_groups表的chat_group_id列上执行内部连接，并且如果user_id等于登录的用户id，则该查询将根据IsGroup标志是否为true（即关联是包含2个或更多用户的组）将其过滤掉。
最后，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表应该较小，而其他2个表应该相对较大。
这个查询在我的开发数据库中运行得很快。2但是我认为在生产中它会花很长时间，而且可能会锁定表一段时间，从而减慢整个应用程序的速度。
我不知道如何在不破坏这个逻辑的情况下优化这个查询。
解释查询显示：

+----+-------------------+------------+------------+--------+---------------+---------+---------+--------------------------+------+----------+--------------------------------------------+
| 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                            |
+----+-------------------+------------+------------+--------+---------------+---------+---------+--------------------------+------+----------+--------------------------------------------+

如果将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;

mysql 如何在不更改其逻辑的情况下优化此SQL查询？

2条答案

相关问题

热门标签

最新问答