单查询耗时极低，嵌入为子查询后总查询耗时剧增的原因排查

这是个非常典型的数据库执行计划优化问题，咱们一步步拆解背后的逻辑：

核心原因：执行计划的“劣化” #

你单独跑子查询（0.0005秒）和去掉条件的主查询（0.0018秒）都很快，但组合起来耗时暴增，核心问题是数据库的执行计划完全变了，根本不是简单的“先跑子查询，再跑主查询过滤”：

1. OR条件破坏了索引有效性 #

原查询里的(p.user IN (子查询) OR p.user=1)是个致命点：

• 没有这个条件时，数据库可以用posts表的索引（比如id或user的索引）快速定位少量目标数据，再关联其他表，整体数据量极小；
• 但加上OR后，很多数据库（比如MySQL）没办法高效利用posts.user的索引，被迫对posts表做全表扫描——这意味着要处理几万甚至几十万行数据，而不是原来的几十行。

2. 子查询的执行方式从“一次性”变“多次性” #

单独跑子查询时，数据库会一次性算出所有允许的user ID（小结果集）；但放到IN (子查询)里，加上OR条件后，数据库可能会把它变成相关子查询：也就是对posts表的每一行都去执行一次子查询，判断是否符合条件。假设posts有10万行，那子查询就要跑10万次，0.0005秒×10万=50秒，这还没算其他关联和计数的成本。

3. COUNT(DISTINCT)的耗时放大 #

COUNT(DISTINCT)本身就是比较重的操作，需要去重统计。当主查询因为全表扫描拿到大量数据后，后续和likes、comentarios表的关联会产生海量中间结果，再对这些结果做去重计数，耗时会呈非线性增长——这就是总耗时远大于两者之和的关键推手。

解决办法：优化执行计划 #

方案1：用JOIN替换OR+IN的组合 #

把允许的用户ID提前用UNION ALL整合，再用JOIN和posts关联，这样数据库能高效利用posts.user的索引：

SELECT 
    c.nome, p.foto, c.user, p.user, p.id, p.data, p.titulo, p.youtube, pp.foto, 
    COUNT(DISTINCT likes.user) AS likes_count, 
    COUNT(DISTINCT comentarios.id) AS comentarios_count, 
    COUNT(DISTINCT l2.user) AS count2 
FROM posts p 
JOIN cadastro c ON p.user = c.id 
-- 先获取所有允许的用户ID（包括自己）
JOIN (
    SELECT following AS user FROM following WHERE user = 1 AND block = 0 AND feed = 0
    UNION ALL
    SELECT 1 AS user
) allowed_users ON p.user = allowed_users.user
LEFT JOIN profile_picture pp ON p.user = pp.user 
LEFT JOIN likes ON likes.post = p.id 
LEFT JOIN comentarios ON comentarios.foto = p.id AND comentarios.delete = 0 
LEFT JOIN likes l2 ON l2.post = p.id AND l2.user = 1 
WHERE p.delete = '0' 
GROUP BY p.id 
ORDER BY p.id DESC 
LIMIT 15

方案2：给关键字段加复合索引 #

给这些字段建复合索引，进一步提升查询效率：

• posts(user, delete, id)：用来快速过滤指定用户的未删除帖子，同时支持GROUP BY和ORDER BY
• following(user, block, feed, following)：让子查询能瞬间返回结果
• likes(post, user)：加速点赞数的去重统计
• comentarios(foto, delete, id)：加速评论数的过滤和统计

方案3：用EXPLAIN排查执行计划 #

执行EXPLAIN命令看原查询的执行计划，重点看type列：

EXPLAIN select c.nome, p.foto, c.user, p.user, p.id, p.data, p.titulo, p.youtube, pp.foto, count(DISTINCT likes.user) as likes_count, count(distinct comentarios.id) as comentarios_count, count(DISTINCT l2.user) as count2 from posts p join cadastro c on p.user=c.id left join profile_picture pp on p.user = pp.user left join likes on likes.post = p.id left join comentarios on comentarios.foto = p.id and comentarios.delete = 0 left join likes l2 on l2.post = p.id and l2.user = 1 where (p.user in (select following from following where user =1 and block=0 and feed=0) or p.user=1) and p.delete='0' group by p.id order by p.id desc limit 15;

如果看到type是ALL，说明是全表扫描，这就是耗时的根源；如果是ref或range，说明用了索引，效率更高。

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