添加p.user in条件后SQL查询耗时剧增，求优化方案

哇，这个性能差异确实够夸张的——从0.0024秒跳到15.35秒，明显是执行计划出了问题。咱们一步步拆解来解决：

先搞懂为什么加了p.user in (2,3,5)反而变慢 #

最可能的原因是数据库优化器选错了执行计划：

• 当没有p.user过滤条件时，优化器可能选择全表扫描+高效的哈希连接，因为要处理全表数据，这种方式反而更高效；
• 加了p.user条件后，优化器可能错误地选择了基于p.user索引的嵌套循环连接，但后续关联多个表（likes、comentarios等）时，会触发大量索引查找和数据回表操作，最终拖慢整个查询；
• 另外，多表左连接后再做COUNT(DISTINCT)会产生大量重复数据，去重计算的开销会随着过滤后的数据量变化剧增——这也是性能暴跌的关键因素之一。

具体优化步骤 #

1. 先看执行计划，定位瓶颈 #

先分别执行带条件和不带条件的EXPLAIN语句，对比两者的执行计划：

EXPLAIN select c.nome, p.foto... -- 你的原查询（带p.user条件）
EXPLAIN select c.nome, p.foto... -- 你的原查询（不带p.user条件）

重点关注这几个字段：

• type：是否出现ALL（全表扫描）或range之外的低效类型；
• key：是否用到了合适的索引；
• rows：优化器预估的扫描行数，和实际行数差异是否过大；
• Extra：是否出现Using filesort、Using temporary这些性能杀手。

2. 针对性创建索引 #

索引是解决这类问题的核心，按优先级创建以下索引：

• posts表的核心过滤+排序索引：

  CREATE INDEX idx_posts_user_delete_id ON posts(user, delete, id);
  

  这个索引覆盖了WHERE里的p.user和p.delete，同时id作为最后一列可以直接支持GROUP BY p.id和ORDER BY p.id DESC，避免额外的排序和临时表。
• 关联表的索引：

  -- likes表：支持帖子关联和用户点赞判断
  CREATE INDEX idx_likes_post ON likes(post);
  CREATE INDEX idx_likes_post_user ON likes(post, user);
  
  -- comentarios表：支持帖子关联和有效过滤
  CREATE INDEX idx_comentarios_foto_delete ON comentarios(foto, delete);
  
  -- posts表：支持分享关联
  CREATE INDEX idx_posts_post_share ON posts(post_share);
  
  -- profile_picture表：支持用户头像关联
  CREATE INDEX idx_profile_picture_user ON profile_picture(user);
  

3. 重构查询，减少不必要的计算开销 #

原查询里的多个COUNT(DISTINCT)是性能黑洞——多表左连接会产生大量重复数据，去重计数的成本极高。我们可以把聚合逻辑拆成子查询，提前计算好统计值再关联：

SELECT 
    c.nome, 
    p.foto, 
    c.user, 
    p.user, 
    p.id, 
    p.data, 
    p.titulo, 
    p.youtube, 
    pp.foto, 
    COALESCE(likes_stats.likes_count, 0) AS likes_count,
    COALESCE(coment_stats.comentarios_count, 0) AS comentarios_count,
    COALESCE(liked_by_me.has_liked, 0) AS count2,
    linked.id AS shared_id, 
    linked.titulo AS shared_titulo, 
    linked.user AS shared_user_id, 
    c2.user AS shared_nick, 
    linked.foto AS shared_foto, 
    pp2.foto AS shared_perfil,
    COALESCE(share_stats.shares_count, 0) AS shares_count
FROM posts p 
JOIN cadastro c ON p.user = c.id 
LEFT JOIN profile_picture pp ON p.user = pp.user 
-- 提前计算每个帖子的点赞数
LEFT JOIN (
    SELECT post, COUNT(DISTINCT user) AS likes_count
    FROM likes
    GROUP BY post
) likes_stats ON likes_stats.post = p.id
-- 提前计算每个帖子的有效评论数
LEFT JOIN (
    SELECT foto, COUNT(DISTINCT id) AS comentarios_count
    FROM comentarios
    WHERE delete = 0
    GROUP BY foto
) coment_stats ON coment_stats.foto = p.id
-- 只判断用户1是否给该帖子点过赞，无需COUNT(DISTINCT)
LEFT JOIN (
    SELECT post, 1 AS has_liked
    FROM likes
    WHERE user = 1
) liked_by_me ON liked_by_me.post = p.id
-- 提前计算每个帖子的分享数
LEFT JOIN (
    SELECT post_share, COUNT(DISTINCT id) AS shares_count
    FROM posts
    WHERE delete = 0
    GROUP BY post_share
) share_stats ON share_stats.post_share = p.id
-- 关联分享的原帖子
LEFT JOIN posts linked ON linked.id = p.post_share 
LEFT JOIN cadastro c2 ON linked.user = c2.id 
LEFT JOIN profile_picture pp2 ON linked.user = pp2.user 
WHERE p.user IN (2,3,5) AND p.delete = '0' 
GROUP BY p.id 
ORDER BY p.id DESC 
LIMIT 15

这样重构后，每个聚合统计都在小范围的子查询里完成，主查询只做简单的关联，避免了大量重复数据的生成和去重计算。

4. 关于分区是否有用？ #

分区适合**数据量极大（比如千万级以上）**的表，且需要按特定维度（比如时间、用户ID范围）拆分数据。如果你的posts表数据量没那么大，分区带来的收益远不如索引和查询重构明显。

如果确实要尝试分区，比如按user做列表分区（把user=2、3、5分到单独的分区），那查询时只会扫描对应的3个分区，理论上能减少扫描范围。但注意：

• 分区键的选择要和你的查询过滤条件匹配；
• 分区会增加维护成本，比如数据迁移、备份等；
• 优先把索引和查询优化做好，再考虑分区作为进阶方案。

最后提醒 #

先跑EXPLAIN确认瓶颈，再逐步优化索引和查询逻辑。如果优化器还是选错执行计划，可以尝试用FORCE INDEX强制指定使用idx_posts_user_delete_id索引，但尽量少用这种方式，最好通过合理的索引让优化器自动选择最优计划。

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