MySQL JOIN/IN 性能优化

问题描述：

我有以下 MySQL 查询:

I have a following MySQL query:

SELECT 
    p.post_id,
    p.date_created,
    p.description, 
    p.last_edited, 
    p.link, 
    p.link_description, 
    p.link_image_url, 
    p.link_title, 
    p.total_comments, 
    p.total_votes, 
    p.type_id, 
    p.user_id 
FROM posts p JOIN posts_to_tribes ptt ON p.post_id=ptt.post_id 
WHERE ptt.tribe_id IN (1, 2, 3, 4, 5) 
GROUP BY p.post_id 
ORDER BY p.last_edited DESC, p.total_votes DESC LIMIT 25

在非并发环境中，此查询运行约 172 毫秒，但在并发环境中运行 1-2 秒(在性能测试期间).

In a non-concurrent environment this query runs ~172ms but in concurrent environment runs 1-2 sec(during the performance testing).

解释输出:

posts_to_tribes 表上的索引:

有什么办法可以提高这里的性能吗?

Is any way in order to improve performance here ?

答

posts_to_tribes 需要一个复合索引:INDEX(tribe_id, post_id).

You need a composite index for posts_to_tribes: INDEX(tribe_id, post_id).

GROUP BY 是为了补偿 JOIN 爆炸行数.这是一个比 IN ( SELECT ... ) 更好的解决方法:

The GROUP BY was to compensate for the JOIN exploding the number of rows. Here is a better workaround, than IN ( SELECT ... ):

SELECT  p.post_id, p.date_created, p.description, p.last_edited,
        p.link, p.link_description, p.link_image_url, p.link_title,
        p.total_comments, p.total_votes, p.type_id, p.user_id
    FROM  posts p
    JOIN  
      ( SELECT  DISTINCT  post_id
            FROM  posts_to_tribes
            WHERE  tribe_id IN (1, 2, 3, 4, 5)
      ) AS ptt USING (post_id)
    ORDER BY  p.last_edited DESC,
              p.total_votes DESC
    LIMIT  25

MySQL JOIN/IN 性能优化

相关推荐