MySQL聊天应用SQL查询优化求助：不改变逻辑优化关联查询

问题背景 #

我正在开发一款聊天应用，编写了一条SQL用于获取用户的活跃聊天记录，该查询会获取单聊和群聊，并按最后消息发送时间倒序排序。使用MySQL，希望在不改变逻辑的前提下优化这条查询，避免生产环境数据量大时出现性能问题、锁表情况。

表结构 #

group_attributes表 #

当chat_group_messages表添加消息记录时，该表的UpdatedAt字段会更新为当前时间戳。

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

目前尚未添加外键，还在熟悉Sequelize关联机制。

查询逻辑 #

单聊记录查询 #

从group_attributes表选择用户组，关联chat_groups和users表，过滤条件：

• 该组存在至少一条消息（关联chat_group_messages）；
• isGroup为false；
• 关联的user_id不等于登录用户ID；
• 登录用户属于该组。

注：条件1是为了过滤前端创建的临时组，后续会移除该条件。

群聊记录查询 #

从group_attributes表选择，关联chat_groups表（要求user_id等于登录用户ID），过滤条件：IsGroup为true。

最终将两个查询结果取并集，按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 ;

EXPLAIN执行结果 #

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

优化方案 #

1. 添加必要索引 #

从EXPLAIN结果看，大量全表扫描（type: ALL）是性能瓶颈，需添加以下索引：

• chat_groups表：
  • 复合索引idx_chatgroup_user (Chat_Group_id, user_id)：用于快速定位组内用户，同时满足单聊查询中判断登录用户是否在组内的条件。
  • 复合索引idx_user_chatgroup (user_id, Chat_Group_id)：用于快速找到登录用户所属的所有组，优化群聊查询和单聊的前置过滤。
• chat_group_messages表：
  • 索引idx_chatgroup_id (Chat_Group_id)：用于快速判断组内是否存在消息，优化EXISTS子查询。
• group_attributes表：
  • 复合索引idx_isgroup_updatedat (IsGroup, updatedAt)：用于按组类型和更新时间快速筛选，减少排序时的数据量。

创建索引的SQL：

-- 给chat_groups添加索引
CREATE INDEX idx_chatgroup_user ON chat_groups(Chat_Group_id, user_id);
CREATE INDEX idx_user_chatgroup ON chat_groups(user_id, Chat_Group_id);

-- 给chat_group_messages添加索引
CREATE INDEX idx_chatgroup_id ON chat_group_messages(Chat_Group_id);

-- 给group_attributes添加索引
CREATE INDEX idx_isgroup_updatedat ON group_attributes(IsGroup, updatedAt);

2. 重写子查询，避免嵌套关联 #

原单聊查询中，判断登录用户是否在组内的子查询可以用JOIN替代IN，减少临时表的创建：
将原单聊查询中的：

and ${logged_user.id} in (
  select  cg2.user_id  from chat_groups cg2 
  where cg.Chat_Group_id = cg2.Chat_Group_id
)

替换为：

INNER JOIN chat_groups cg2 ON cg.Chat_Group_id = cg2.Chat_Group_id AND cg2.user_id = ${logged_user.id}

3. 优化UNION查询 #

原查询用UNION会自动去重，但单聊和群聊的IsGroup值不同（false/true），不会有重复数据，改用UNION ALL可以避免去重的额外开销，提升性能。

4. 提前过滤数据，减少中间结果集 #

用CTE提前获取登录用户所属的所有组ID，再基于这些ID查询单聊和群聊，避免全表扫描：

优化后的完整SQL #

WITH user_groups AS (
    SELECT Chat_Group_id FROM chat_groups WHERE user_id = ${logged_user.id}
)
SELECT * FROM (
    -- 单聊记录
    SELECT 
        ca.chat_group_id, 
        ca.isgroup, 
        u.user_id, 
        u.username, 
        ca.name AS group_name, 
        ca.description AS group_description, 
        ca.updatedat
    FROM user_groups ug
    INNER JOIN group_attributes ca ON ug.Chat_Group_id = ca.chat_group_id
    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
    INNER JOIN chat_group_messages cgm ON ca.chat_group_id = cgm.chat_group_id
    WHERE 
        ca.isgroup = false 
        AND u.user_id != ${logged_user.id}
    GROUP BY ca.chat_group_id -- 确保每个组只返回一条记录，替代EXISTS的作用
    -- 群聊记录
    UNION ALL
    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 user_groups ug
    INNER JOIN group_attributes ca ON ug.Chat_Group_id = ca.chat_group_id
    WHERE ca.isgroup = true
) AS temp
ORDER BY updatedat DESC;

注：使用GROUP BY替代EXISTS是因为我们只需要确认组内有消息，通过关联chat_group_messages后分组，既满足条件又能避免重复记录；如果后续移除“存在消息”的条件，直接去掉INNER JOIN chat_group_messages和GROUP BY即可。

5. 其他建议 #

• 后续熟悉Sequelize后，添加外键约束，既保证数据一致性，也能让优化器更好地执行查询。
• 考虑分页查询：如果用户聊天组数量较多，添加LIMIT和OFFSET减少单次返回的数据量，避免排序和传输的性能开销。

内容的提问来源于stack exchange，提问作者Shaswat