mysql - MySQL查询性能?

mysql - MySQL查询性能?,第1张

我有MySQL数据库和5个名为tribes(groups)postsposts_to_groupspost_commentsposts_votes的表。

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

我正在搜索此用户关注的3个最受欢迎的帖子(通过posts_to_tribes关联 - MANY_2_MANY关系表)从此时起过去24小时,并按(comments_count votes_count)的总和排序)DESC

这是我目前的查询:

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

有没有办法重写此查询以提高性能?

更新:

EXPLAIN输出:

mysql - MySQL查询性能?,enter image description here,第2张

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

EXPLAIN输出:

mysql - MySQL查询性能?,enter image description here,第3张

最佳答案:

2 个答案:

答案 0 :(得分: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

如果性能仍然不可接受,您可能需要考虑直接更新total_comments,total_votes或使用触发器或预定作业。

答案 1 :(得分:0)

您使用了2 correlated subqueries。来自相关子查询的每一个将针对来自外部查询的每一行执行一次。因此,如果你可以避免它们,那么查询可能会更快。

  

[..]它们效率低,可能很慢。将查询重写为连接可能会提高性能。

您必须使用加入来避免它们。这可能会对您有所帮助:MySQL - can I avoid these correlated / dependant subqueries?

本文经用户投稿或网站收集转载,如有侵权请联系本站。

发表评论

0条回复