评级系统查询

Question

我在 MySQL 中构建了一个电影评级系统，但问题是，当我的查询汇总所有评级并进行除法时，它可能需要汇总数百万条记录。

我想到的一种解决方案是基本上将评级缓存在 memcached 中，并且只对不经常使用的项目进行评级操作。然而，即使如此，对于评级不高的电影，如果有人去检查评级，如果必须计算大量行，查询可能会花费很长时间。

我想到的另一个解决方案是建立一个不断更新表的临时表，但是如果电影评级很高并且有人试图访问它怎么办，我相信 INNODB 会进行行锁定，所以这会导致死锁或从长远来看？

Answer 1

由于评级相当静态并且您的数据集很大，因此当用户对电影进行评级时，您可以缓存（非规范化）电影记录中的数据。您需要跟踪投票数和投票总和，以便平均值是准确的。您的电影评级会在您需要时动态计算。这是架构：

create table movie as (
movie_id int not null primary key,
-- your current columns
vote_count int,
vote_sum int
);

然后使用视图来帮助

create view movie_view as
select
  *,
  vote_sum/vote_count as vote_average
from movie;

假设您有一个如下所示的表：

create table user_movie_vote (
user_id int references user,
movie_id int references movie,
vote int
);

您可以使用触发器来使投票总数保持最新：

delimiter ~
create trigger movie_vote_trg after insert on user_movie_vote
for each row
begin
  update movie set
  vote_count = vote_count + 1,
  vote_sum = vote_sum + new.vote
  where movie_id = new.movie_id;
end~
delimiter ;

如果可以更新投票，您将需要：

delimiter ~
create trigger movie_vote_trg after update on user_movie_vote
for each row
begin
  update movie set
  vote_sum = vote_sum + new.vote - old.vote
  where movie_id = new.movie_id;
end~
delimiter ;

如果投票可以删除，你需要：

delimiter ~
create trigger movie_vote_trg after delete on user_movie_vote
for each row
begin
  update movie set
  vote_sum = vote_sum - old.vote,
  vote_count = vote_count - 1
  where movie_id = new.movie_id;
end~
delimiter ;