如何优化关联Users和Friends两张大表的慢SQL查询

1. 修复缺失的核心索引（优先级最高） #

• Friends表当前仅创建了friend_id单列索引，但查询中INNER JOIN Friends f ON f.user_id = u.id是以user_id作为关联条件，现有索引完全无法命中，会导致Friends表全表扫描，这是查询耗时极长的核心原因。
• 给Friends表创建联合覆盖索引，直接覆盖查询用到的两个字段，不需要回表查询数据：

CREATE INDEX idx_friends_user_friend ON Friends(user_id, friend_id);

• 补充优化：Users表当前id只是普通二级索引，未设置为主键，查询first、last字段需要回表读取数据，可选择以下任意一种方案优化：

-- 方案1：将id设为Users表主键（更推荐，InnoDB聚簇索引查询性能更好）
ALTER TABLE Users ADD PRIMARY KEY(id);
-- 方案2：创建覆盖索引满足当前查询需求
CREATE INDEX idx_users_id_name ON Users(id, first, last);

2. 优化分组聚合逻辑 #

索引配置正确后MySQL可直接按有序索引顺序分组，避免临时表和文件排序。如果执行计划仍出现Using temporary或Using filesort，可调整SQL写法提前聚合Friends表，减少关联时的数据量：

SELECT u.id, u.first, u.last, f.MyFriends
FROM Users u
INNER JOIN (
  SELECT user_id, group_concat(u2.first, " " , u2.last) MyFriends
  FROM Friends f
  INNER JOIN Users u2 ON u2.id = f.friend_id
  GROUP BY f.user_id
) f ON u.id = f.user_id;

3. 可选补充调整 #

如果单用户好友数量较多，可适当调大group_concat的长度限制，避免结果被截断（不影响性能，仅保证结果正确性）：

SET SESSION group_concat_max_len = 102400; -- 可根据实际业务需求调整长度

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