优化耗时3.4641秒的关注推荐SQL查询方案咨询

嘿，我来帮你拆解这条关注推荐SQL的性能问题，再给你具体的优化思路！

先分析原查询的性能瓶颈 #

你的SQL逻辑没问题，但几个细节拖慢了速度：

1. 重复子查询浪费资源：两次执行select following from following where user = 1，还重复查询拉黑列表，相当于做了多次重复计算；
2. LEFT JOIN带来冗余数据：后面的c.id not in (...)已经过滤掉了cadastro表无匹配的行，LEFT JOIN完全没必要；
3. ORDER BY RAND()效率极低：大数据集下，这个函数需要给每一行生成随机值再排序，是典型的性能杀手；
4. 缺失关键索引：如果关联字段、查询条件字段没有索引，所有表都会走全表扫描，这大概率是耗时3秒+的核心原因。

具体优化方案 #

1. 用CTE复用重复子查询结果

把目标用户的关注列表、拉黑列表提前计算好，避免重复查询：

WITH user_follows AS (
    -- 目标用户已关注的人，只查一次
    SELECT following FROM following WHERE user = 1
),
user_blocked_ids AS (
    -- 目标用户拉黑的人 + 拉黑目标用户的人，合并成一个列表
    SELECT `block` AS blocked_id FROM block WHERE user = 1
    UNION ALL
    SELECT `user` AS blocked_id FROM block WHERE `block` = 1
)
SELECT 
    c.user, c.nome, p.foto, f.user AS follower_user, f.following AS recommended_user
FROM following f
-- 换成INNER JOIN，过滤掉无匹配的无效数据
INNER JOIN cadastro c ON c.id = f.following
LEFT JOIN profile_picture p ON c.id = p.user
WHERE 
    f.following <> 1  -- 排除自己
    AND f.following NOT IN (SELECT following FROM user_follows)  -- 排除已关注的人
    AND c.id NOT IN (SELECT blocked_id FROM user_blocked_ids)  -- 排除拉黑关系的人
-- 用DISTINCT代替GROUP BY（如果只是去重的话），更高效
DISTINCT
-- 优化随机排序逻辑
ORDER BY RAND() LIMIT 4;

2. 替换ORDER BY RAND()的高效方案

如果following表数据量大，ORDER BY RAND()会非常慢，可以换成以下两种方式：

• 随机ID范围选取（适合following的following是自增ID的场景）：

  -- 先获取推荐用户ID的范围
  SET @min_id = (SELECT MIN(following) FROM following WHERE following <> 1 AND following NOT IN (SELECT following FROM user_follows));
  SET @max_id = (SELECT MAX(following) FROM following WHERE following <> 1 AND following NOT IN (SELECT following FROM user_follows));
  -- 生成随机ID并查询，可能需要处理ID不存在的情况，但整体比ORDER BY RAND()快很多
  SELECT c.user, c.nome, p.foto
  FROM cadastro c
  LEFT JOIN profile_picture p ON c.id = p.user
  WHERE c.id = FLOOR(@min_id + RAND() * (@max_id - @min_id))
    AND c.id NOT IN (SELECT blocked_id FROM user_blocked_ids)
  LIMIT 4;
  

• MySQL 8.0+ 用TABLESAMPLE近似采样：如果不需要精确返回4条，适合快速获取随机样本：

  SELECT ... FROM following f TABLESAMPLE SYSTEM(10) ... -- 采样10%的数据，再筛选
  

3. 必须添加的索引

这是最关键的一步，没有索引的话所有优化都是空谈：

• following表：

  -- 快速获取目标用户的关注列表
  CREATE INDEX idx_following_user ON following(user);
  -- 快速关联cadastro表
  CREATE INDEX idx_following_following ON following(following);
  

• block表：

  -- 快速获取目标用户拉黑的人
  CREATE INDEX idx_block_user ON block(user);
  -- 快速获取拉黑目标用户的人
  CREATE INDEX idx_block_block ON block(block);
  

• profile_picture表：

  -- 快速关联用户头像
  CREATE INDEX idx_profile_user ON profile_picture(user);
  

4. 用EXPLAIN验证优化效果

执行EXPLAIN + 你的SQL，查看执行计划：

• 避免出现type: ALL（全表扫描）；
• 确保key列显示你创建的索引；
• 检查rows列的预估扫描行数是否大幅减少。

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