MySQL查询性能？

Question

我有 MySQL 数据库和 5 个表，分别为 tribes(groups)、posts、posts_to_groups、post_comments 和posts_votes。

群组和帖子之间的关系是 MANY_2_MANY，因此每个帖子可以属于多个群组，并且每个群组可以包含 0-* 个帖子。这就是表 posts_to_groups 的作用。

我正在搜索从此时起过去 24 小时内发布到该用户关注的群组中的 3 个最受欢迎的帖子(通过 posts_to_tribes - MANY_2_MANY 关系表关联)，并按 (comments_count + votes_count) 的总和排序) 描述

这是我当前的查询:

SELECT DISTINCT
    p.post_id,
    p.description,
    p.link,
    p.user_id,
    p.total_comments,
    p.total_votes,
    (SELECT 
            COUNT(*)
        FROM
            comments
        WHERE
            last_edited > DATE_SUB(NOW(), INTERVAL 24 HOUR)
                AND post_id = p.post_id) AS comments_count,
    (SELECT 
            COUNT(*)
        FROM
            posts_votes
        WHERE
            date_voted > DATE_SUB(NOW(), INTERVAL 24 HOUR)
                AND post_id = p.post_id) AS votes_count
FROM
    posts p
        JOIN
    posts_to_tribes pt ON pt.post_id = p.post_id
WHERE
    pt.tribe_id IN (3 , 38, 107)
ORDER BY (comments_count + votes_count) DESC , p.last_edited DESC
LIMIT 3;

此查询非常慢，现在需要~500ms。

有什么方法可以重写此查询以提高性能吗？

更新:

解释输出:

Tim3880建议的查询:

SELECT 
    p.post_id,
    p.description,
    p.link,
    p.user_id,
    p.total_comments,
    p.total_votes,
    t.comments_count,
    t.votes_count
FROM posts p
JOIN (
    SELECT 
        p.post_id,
        (SELECT 
                COUNT(*)
            FROM
                comments
            WHERE
                last_edited > DATE_SUB(NOW(), INTERVAL 24 HOUR)
                    AND post_id = p.post_id) AS comments_count,
        (SELECT 
                COUNT(*)
            FROM
                posts_votes
            WHERE
                date_voted > DATE_SUB(NOW(), INTERVAL 24 HOUR)
                    AND post_id = p.post_id) AS votes_count
    FROM
        posts p
            JOIN
        posts_to_tribes pt ON pt.post_id = p.post_id
    WHERE
        pt.tribe_id IN (3 , 38, 107)
    ORDER BY (comments_count + votes_count) DESC , p.last_edited DESC
    LIMIT 3
) t
ON p.post_id = t.post_id
ORDER BY (t.comments_count + t.votes_count) DESC , p.last_edited DESC

现在需要~280ms。

解释输出:

Answer 1

如果您的 post_id 是主键(或唯一)，请尝试先获取 3 post_id:

SELECT 
    p.post_id,
    p.description,
    p.link,
    p.user_id,
    p.total_comments,
    p.total_votes,
    t.comments_count,
    t.votes_count
FROM posts p 
JOIN (
    SELECT 
        p.post_id,
        (SELECT 
                COUNT(*)
            FROM
                comments
            WHERE
                last_edited > DATE_SUB(NOW(), INTERVAL 24 HOUR)
                    AND post_id = p.post_id) AS comments_count,
        (SELECT 
                COUNT(*)
            FROM
                posts_votes
            WHERE
                date_voted > DATE_SUB(NOW(), INTERVAL 24 HOUR)
                    AND post_id = p.post_id) AS votes_count
    FROM
        posts p
            JOIN
        posts_to_tribes pt ON pt.post_id = p.post_id
    WHERE
        pt.tribe_id IN (3 , 38, 107)
        AND p.last_edited >  DATE_SUB(NOW(), INTERVAL 24 HOUR)
    ORDER BY (comments_count + votes_count) DESC , p.last_edited DESC
    LIMIT 3
) t
ON p.post_id = t.post_id
ORDER BY (t.comments_count + t.votes_count) DESC , p.last_edited DESC

编辑:这是加入版本:

SELECT 
    p.post_id,
    p.description,
    p.link,
    p.user_id,
    p.total_comments,
    p.total_votes,
    t.comments_count,
    t.votes_count
FROM posts p 
JOIN (
    SELECT 
        p.post_id,Comments_Count, Votes_Count
    FROM
        posts p
        JOIN
        posts_to_tribes pt ON pt.post_id = p.post_id
        LEFT JOIN (SELECT 
                post_id, COUNT(*) Comments_Count
            FROM
                comments
            WHERE
                last_edited > DATE_SUB(NOW(), INTERVAL 24 HOUR)
            GROUP BY post_id) cc
        ON p.post_id = cc.post_id
        LEFT JOIN 
        ( 
            SELECT 
                post_id, COUNT(*) Votes_Count
            FROM
                posts_votes
            WHERE
                date_voted > DATE_SUB(NOW(), INTERVAL 24 HOUR)
            GROUP BY post_id
        ) vc
        ON p.post_id = vc.post_id
        WHERE pt.tribe_id IN (3 , 38, 107)
    ORDER BY (comments_count + votes_count) DESC , p.last_edited DESC
    LIMIT 3
) t
ON p.post_id = t.post_id
ORDER BY (t.comments_count + t.votes_count) DESC , p.last_edited DESC

如果性能仍然 Not Acceptable ，您可能需要考虑直接更新total_comments、total_votes或使用触发器或计划作业。