MySQL在百万行表上的分组最大性能问题

Question

我正试图找到一种简单的方法来提高非常活跃的论坛的性能，因为那里有大量的帖子，mysql不能再在内存中进行表排序，而且似乎不能充分利用索引。

这个简单的查询为用户找到每个主题中的最新帖子，以确定他们是否有自以来的回复（稍后通过比较主题时间）

SELECT p.*, MAX(post_time) as post_time FROM forum_posts AS p   
WHERE p.poster_id = '1' AND p.post_status = '0' 
GROUP BY p.topic_id  
ORDER BY post_time DESC 
LIMIT 50

简单的、平的桌子看起来有点像

post_id | poster_id | topic_id | post_status | post_time | post_text

然而，当有一百万条帖子，而用户自己也有数以万计的帖子时，它的性能就会崩溃。MySQL要么无法再对内存中的表进行排序，要么扫描的行太多。在现实世界中使用它可能需要长达3秒的时间，这是不可接受的，因为在这段时间里，它会使CPU峰值，并使其他人的速度减慢。

当然，我可以进行任何索引组合，但mysql似乎最喜欢使用的组合

poster_id + post_time 

因此，它只需从一百万条帖子中选择一个用户的5万条帖子，然后开始按topic_id进行分组和排序。奇怪的是，在索引组合中添加topic_id似乎对性能没有帮助，尽管这可能是索引字段的顺序？

我试着写一个等效的JOIN，这样我就可以使用多个索引，但我遇到了问题，因为每一边都必须通过postrongtatus和poster进行过滤。

我认为，如果mysql能够在post_time之前通过其索引对数据进行first排序，然后开始按降序为用户挑选不同的topic_id，至少在最初的几页中会更快。我想这需要一个子查询，不确定50k结果的子查询会更好，仍然需要一个临时表

当然，一个基本的解决方案是增加核心设计，这样就有了另一个表，它只存储每个主题中每个用户的最大post_time，但除非找不到其他解决方案，否则这一变化太大了。

谢谢你的建议！

---

添加真实世界示例和解释：

慢速日志

# Query_time: 2.751334  Lock_time: 0.000056 Rows_sent: 40  Rows_examined: 48286
SELECT   p.*, MAX(post_time) as post_time FROM forum_posts AS p   WHERE p.poster_id = '2' AND p.post_status = '0' GROUP BY p.topic_id  ORDER BY post_time DESC LIMIT 7000, 40;

解释

select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
SIMPLE          p   ref poster_time poster_time 4   const   27072   Using where; Using temporary; Using filesort

Answer 1

首先，修复您的查询以提供确定的结果：

SELECT p.topic_id, 
       MAX(post_time) as post_time 
FROM forum_posts AS p   
WHERE p.poster_id = '1' AND p.post_status = '0' 
GROUP BY p.topic_id  
ORDER BY post_time DESC 
  LIMIT 50 ;

然后在(post_status, poster_id, topic_id, post_time)上添加索引后进行尝试。