mysql - 为什么这个查询对于 70k+ 行真的很慢？

Question

首先，这是我的表结构:

CREATE TABLE IF NOT EXISTS `site_forum_comments` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`forum_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`data` int(11) NOT NULL,
`comment` longtext NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

在导入我的备份之前，它有大约 10-15 行，我根据评论数量制作了一个排名系统，这个查询运行完美:

SELECT u.id, u.username, COUNT(f.id) AS rank 
FROM site_users AS u 
LEFT JOIN site_forum_comments AS f ON (f.user_id = u.id)
GROUP BY u.id 
ORDER BY rank DESC 
LIMIT :l

但是现在，插入了超过 70k 行，脚本甚至不会加载，只会导致服务器崩溃。

我可能做错了什么？这个问题是具体查询还是表结构？

提前致谢，干杯!

Answer 1

这是您的查询:

SELECT u.id, u.username, COUNT(f.id) AS rank 
FROM site_users u LEFT JOIN
     site_forum_comments f
     ON f.user_id = u.id
GROUP BY u.id 
ORDER BY rank DESC 
LIMIT :l

因为您要选择排名最高的用户，所以您可以使用内部联接而不是外部联接。无论如何，这个版本没有太多的优化机会。但是，您需要在 site_forum_comments(user_id, id) 上建立索引。

使用相同的索引和相关的子查询可能会获得更好的性能:

SELECT u.id, u.username, 
       (SELECT COUNT(*)
        FROM site_forum_comments f
        WHERE f.user_id = u.id
       ) as rank
FROM site_users u 
ORDER BY rank DESC 
LIMIT :l;