MySQL连接/ IN性能优化

Question

我有以下MySQL查询：

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 In，但在并发环境中运行1-2秒(在性能测试期间)。

解释输出：

​

​

posts_to_tribes表的索引：

​

​

有什么方法可以提高这里的性能吗？

Answer 1

您需要一个用于posts_to_tribes：INDEX(tribe_id, post_id)的复合索引。

GROUP BY是为了补偿JOIN爆炸的行数。这里有一个比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

Answer 2

当您真正想要在两个表之间应用半连接时，您已经应用了一个半连接操作(在IN或EXISTS谓词中实现了半连接)。

因为您使用了错误的JOIN类型，所以再次使用GROUP BY删除重复记录。这是很多浪费CPU周期的地方。

以下查询要快得多：

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 
WHERE p.post_id IN (
  SELECT ptt.post_id
  FROM posts_to_tribes ptt
  WHERE ptt.tribe_id IN (1, 2, 3, 4, 5)
)
ORDER BY p.last_edited DESC, p.total_votes DESC LIMIT 25

您仍然应该有(p.post_id)和(ptt.tribe_id, ptt.post_id)的索引。