SQL查询非常慢。

Question

在过去的24小时里，我有一个更新视图和喜欢的帖子的查询，但是它非常慢。在大约3000篇文章中，它需要超过15秒才能完成，这是一个很大的问题，因为在此期间无法执行其他查询。

$update_query = mysqli_query($database, "UPDATE post SET 
                likes_last_day = (SELECT COUNT(post_like_id) FROM post_like WHERE post.post_id = post_like.post_id AND post_like.date > ('$current_date' - INTERVAL 1 DAY)), 
                views_last_day = (SELECT COUNT(post_view_id) FROM post_view WHERE post.post_id = post_view.post_id AND post_view.date > ('$current_date' - INTERVAL 1 DAY))");

是否有方法优化此查询以更快地执行？

Answer 1

这是您的查询：

UPDATE post p
    SET likes_last_day = (SELECT COUNT(*) FROM post_like l WHERE p.post_id = l.post_id AND l.date > ('$current_date' - INTERVAL 1 DAY)), 
        views_last_day = (SELECT COUNT(*) FROM post_view v WHERE p.post_id = v.post_id AND v.date > ('$current_date' - INTERVAL 1 DAY));

您正在使用子查询更新3000行。那要花点时间。要加快查询速度，可以使用索引：post_like(post_id, date)和post_view(post_id, date)。

还可以重写查询以在加入结果之前进行聚合。但是，我怀疑使用正确的索引，相关的子查询可能会更快。

Answer 2

如果在添加索引后性能仍然是一个问题，您可能会考虑在存储过程中执行这个问题，因此db将重用执行计划，而不是为每个特殊查询构建它。

Answer 3

为了尽量减少在post表上持有锁的时间，我们可以将结果预先聚合到一个临时表中，然后从该表执行更新。

例如：

DROP TEMPORARY TABLE IF EXISTS `__post_last_day_counts__`
;
CREATE TEMPORARY TABLE `__post_last_day_counts__`
( post_id        BIGINT NOT NULL COMMENT 'pk'
, likes_last_day BIGINT NOT NULL
, views_last_day BIGINT NOT NULL
, PRIMARY KEY (post_id)
) ENGINE=InnoDB
;
INSERT INTO `__post_last_day_counts__`
( post_id
, likes_last_day
, views_last_day
)
SELECT p.post_id
     , IFNULL(lc.likes_last_day,0) AS likes_last_day
     , IFNULL(vc.views_last_day,0) AS views_last_day
  FROM post p
  LEFT
  JOIN ( SELECT pl.post_id
              , COUNT(pl.post_id) AS likes_last_day
           FROM post_like pl
          WHERE pl.date > ('$current_date' - INTERVAL 1 DAY)
       ) lc
    ON lc.post_id = p.post_id
  LEFT
  JOIN ( SELECT pv.post_id
              , COUNT(pv.post_id) AS views_last_day
           FROM post_view pv
          WHERE pv.date > ('$current_date' - INTERVAL 1 DAY)
       ) vc
    ON vc.post_id = p.post_id
;

使用临时表中的计数，我们可以更新post表.

UPDATE post t
  JOIN `__post_last_day_counts__` s
    ON s.post_id = t.id
   SET t.likes_last_day = s.likes_last_day
     , t.views_last_day = s.views_last_day
;

还有清理..。

DROP TEMPORARY TABLE IF EXISTS `__post_last_day_counts__`
;

总的来说，这可能要慢一些，但它可能会最小化UPDATE语句持有锁的时间。

注意:临时表中post_id列的数据类型应该与post表中的post_id数据类型相匹配。我只是猜了一下。

我们假设post_id是post表的主键(或唯一键)。

确保有适当的索引可用：

... on post_view (post_id, date) 
... on post_like (post_id, date)

使用“解释”查看执行计划、正在执行的操作和正在使用的索引。(我们希望在额外的列中看到“使用组索引”。)