MySql 用 SUM() 缓慢连接结果

Question

有人知道执行此查询的更有效方法吗？

SELECT SQL_CALC_FOUND_ROWS p.*, IFNULL(SUM(v.visits),0) AS visits,
FROM posts AS p 

LEFT JOIN visits_day v ON v.post_id=p.post_id 

GROUP BY post_id 
ORDER BY post_id DESC LIMIT 20 OFFSET 0

visits_day 表每天、每个用户、每个帖子都有一条记录。随着表的增长，这个查询变得非常慢。

我无法添加包含总访问次数的列，因为我需要按每天或每周的访问次数等列出帖子。

有谁知道更好的解决方案吗？

谢谢

CREATE TABLE `visits_day` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `post_id` int(11) NOT NULL,
 `user_id` int(11) NOT NULL,
 `day` date NOT NULL,
 `visits` int(11) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=52302 DEFAULT CHARSET=utf8

CREATE TABLE `posts` (
 `post_id` int(11) NOT NULL AUTO_INCREMENT,
 `link` varchar(300) NOT NULL,
 `date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `title` varchar(500) NOT NULL,
 `img` varchar(300) NOT NULL,
 PRIMARY KEY (`post_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1027 DEFAULT CHARSET=utf8

Answer 1

使用SQL_CALC_FOUND_ROWS，查询必须评估所有内容，而不是传递所有行。摆脱它应该是有益的。

要实际只触及 20 行，我们需要使用单个索引来遍历 WHERE、GROUP BY 和 ORDER BY。否则，我们可能必须接触所有行，对它们进行排序，然后传递 20。明显的索引是 (post_id)；我怀疑它已经被索引为PRIMARY KEY(post_id)？ (如果您在提问时提供SHOW CREATE TABLE，将会有所帮助。)

执行连接并获得所需结果零的另一种方法如下。请注意，它消除了 GROUP BY 的需要。

SELECT p.*,
       IFNULL( ( SELECT SUM(v.visits)
                   FROM visits_day
                   WHERE post_id = p.post_id
               ),
            0) AS visits
    FROM posts AS p 
    ORDER BY post_id DESC
    LIMIT 20 OFFSET 0

如果您确实需要计数，请考虑SELECT COUNT(*) FROM posts。

ON v.post_id=p.post_id 在您的查询中，WHERE post_id = p.post_id 请求 INDEX(post_id) visits_day。这将大大加快这两个变体的速度。