在上添加复合索引 post ，按以下顺序：

post:  INDEX(user, delete, id)
profile_picture:  (user, foto)
likes:  (post, user)
commentarios:  (foto, delete, id)

如果我理解了“post”和cadastro（注册表），那么每个post都会有一个cadastro条目？因此，不需要在派生表中包含地籍。
另外，我假设每个人最多有一个foto(否则 GROUP BY 有一个解决方案，如果可以有多个，但是您只想显示一个(使用 MAX .)
我在中使用子查询 SELECT 避免爆炸内爆的条款 JOIN...GROUP BY .
我不清楚 l2.user = ? ，但我把它放在一边了。

SELECT  c.nome, p.foto, c.user, p.user, p.id, p.data, p.titulo,
        p.youtube,
        ( SELECT MAX(foto) FROM profile_picture
                           WHERE p.user = user ) AS foto,
        ( SELECT count(DISTINCT user) FROM likes
                           WHERE post = p.id ) as likes_count,
        ( SELECT count(distinct id) FROM comentarios
                           WHERE foto = p.id
                             AND delete  = 0 ) as comentarios_count,
        ( SELECT count(DISTINCT user) FROM likes
                           WHERE post = p.id
                             AND user = ? ) as count2
    FROM  
    (
        SELECT  p.id pid
            FROM  posts p
            WHERE  p.user=?
              and  p.delete='0'
            ORDER BY  p.id
            LIMIT  ? 
    ) selector
    JOIN  posts p  ON selector.pid = p.id
    JOIN  cadastro c  ON p.user = c.id
    ORDER BY  p.id

加速这个查询的一个好方法是重构它以进行延迟连接。我们的目标是 SELECT ... ORDER BY ... LIMIT... 以尽可能少的列数对结果集执行操作。为什么这很重要？订购大的结果集比订购小的结果集要贵，尤其是在 LIMIT 丢弃排序的大部分结果。
所以，从这个子查询开始：

SELECT p.id, c.id
                 FROM posts p
                 JOIN cadastro c ON p.user=c.id 
                WHERE p.user=? and p.delete='0'
                ORDER BY p.id
                LIMIT ?

这里有相关的posts.id和cadastro.id值供查询。你可以用一个复合覆盖索引来加速这个过程 posts(user, delete) ：查询计划器可以通过扫描该复合索引的一部分来完全满足此子查询。
然后将其加入到主查询的一个版本中。

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 (
                   SELECT p.id pid, c.id cid
                     FROM posts p
                     JOIN cadastro c ON p.user=c.id 
                    WHERE p.user=? and p.delete='0'
                    ORDER BY p.id, c.id
                    LIMIT ?
         ) selector
    JOIN posts p ON selector.pid = p.id
    JOIN cadastro c ON selector.cid =  p.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 = ?
   where p.user=? and p.delete='0'
   group by p.id
   order by p.id limit ?

你需要重做 ORDER BY ... LIMIT ? 操作，因为左联接可能会增加最终结果集的大小，您需要限制它。
如果没有关于表的更多信息，很难判断哪些索引将加快查询的其余部分。所有这些计数（不同的…）操作都不可避免地有些昂贵。您可能会从以下内容中受益：https://use-the-index-luke.com/
专业提示你正在使用，可能误用，一个臭名昭著的扩展 GROUP BY 在mysql中。你的 GROUP BY 应该这么说，还是价值观 c.nome 以及 c.user 可能以不可预知的方式被选择。

GROUP BY p.id, c.id

pro-tip单列索引通常对查询或子查询帮助不大：mysql在一个查询中每个表只能使用一个索引。因此，用正确的顺序覆盖索引会有很大帮助。不要只是扔进一堆索引来加速查询。