mySQL效率问题 - 如何找到正常化的正确平衡......？

Question

我对与关系型数据库一起工作相当陌生，但已经阅读了几本书并了解了优秀设计的基础知识。

我正面临设计决策，我不确定如何继续。以下是我正在构建的一个非常简化的版本：用户可以对照片1-5进行评分，并且我需要显示图片上的平均投票数，同时记录个人投票。例如，12人投1中，7人投2中，等等，等等

我正常化怪胎最初设计表结构是这样的：

Table pictures 
id* | picture | userID | 

Table ratings 
id* | pictureID | userID | rating 

与所有设置的外键约束和一切因为他们应该是。每次有人评价一张照片时，我都会在评分中插入一条新的记录，并用它来完成。

要查找的平均评分的图片大小，我只是运行是这样的：

SELECT AVG(rating) FROM ratings WHERE pictureID = '5' GROUP by pictureID 

有它设置这种方式让我跑我看中的统计数据。我可以很容易地找到谁给某张照片评了一张3，而不是。

现在我想如果有一大堆评级（这是非常可能的，我真的在设计），发现平均会变得非常昂贵和痛苦。

使用非标准化版本似乎更有效。例如：

Table picture 
id | picture | userID | ratingOne | ratingTwo | ratingThree | ratingFour | ratingFive 

要计算平均值，我只需要选择一行。看起来效率更高，但更加丑陋。

有人能指出我该做什么的正确方向吗？我最初的研究表明我必须“找到适当的平衡”，但我该如何去寻找这种平衡？任何文章或额外的阅读信息也将不胜感激。

谢谢。

Answer 1

你规范化的方法有很大的意义，非规范化的方法没有。

---

根据我的经验（电信绩效管理，每1/4小时的数据点的数十万），我们将做到以下几点：

Table: pictures 
id* | picture | userID | avg_rating | rating_count 

Table: ratings 
id* | pictureID | userID | rating 

对于电信图片评级将被重新计算每天一次，你应该做定期的（例如每小时）或每次插入时（重新计算评级图片，而不是整个表格）。这取决于您获得的评分数量。

---

在电信我们也保持在什么是你的“照片”表和一个1/4H时间戳的收视率表的等级，日期，但我不认为你需要的详细程度。

---

的 '非规范化' 是移动calculateable事实（计数（等级）和AVG（评级））到照片表。这可以节省CPU周期，但会增加存储空间。

Answer 2

什么将这些ratingOne评为五个领域包含？收到的票数是多少？那么你不会知道谁投了票。如果你确实需要非规范化，我只需在图片表中添加一个“平均评级”字段，并在投票投票时（并保持评级表的原样）更新。

更一般地说，不要陷入过早优化。尝试编写一个测试脚本，它可以创建100.000张图片和100万个评分（或任何想要支持的数字），并查看您的AVG查询需要多长时间。机会仍然会很快。确保你的“收视率”表有一个pictureID索引，因此数据库不需要遍历百万行。

Answer 3

在RDBMS的世界里，非规范化的意思是“我要以提高查询效率的提高维护成本，同时仍然保留了模型的正确性”

在你的情况下，效率会略微确实有所上升（因为所有评级总是从相同的数据页面中检索）。

但模型的正确性呢？

有了这个设计，你首先不知道是谁作出了选票（这个信息不再存储），其次，不能评价超过五次。

由于您的初始模型没有任何这些限制，我相信这种非规范化不是您真正想要的。

Answer 4

享受两个世界的好方法是使用Mysql触发器。 http://dev.mysql.com/doc/refman/5.0/en/triggers.html

现在添加一个触发器，当用户对图片进行评级时，它将更新图片表中的avg_rating。 （使用与您所述相同的选项）

现在，当您选择时，您只能在一张桌子上选择。它总是更新。如果你想获得谁可以评价哪张照片的确切信息，你可以从评价表中选择。

Answer 5

这是我应该怎样解决这个问题http://pastie.org/879604

drop table if exists picture; 
create table picture 
( 
picture_id int unsigned not null auto_increment primary key, 
user_id int unsigned not null, -- owner of the picture, the user who uploaded it 
tot_votes int unsigned not null default 0, -- total number of votes 
tot_rating int unsigned not null default 0, -- accumulative ratings 
avg_rating decimal(5,2) not null default 0, -- tot_rating/tot_votes 
key picture_user_idx(user_id) 
)engine=innodb; 

insert into picture (user_id) values 
(1),(2),(3),(4),(5),(6),(7),(1),(1),(2),(3),(6),(7),(7),(5); 


drop table if exists picture_vote; 
create table picture_vote 
( 
picture_id int unsigned not null, 
user_id int unsigned not null,-- voter 
rating tinyint unsigned not null default 0, -- rating 0 to 5 
primary key (picture_id, user_id) 
)engine=innodb; 

delimiter # 

create trigger picture_vote_before_ins_trig before insert on picture_vote 
for each row 
begin 
declare total_rating int unsigned default 0; 
declare total_votes int unsigned default 0; 

select tot_rating + new.rating, tot_votes + 1 into total_rating, total_votes 
    from picture where picture_id = new.picture_id; 

-- counts/stats 
update picture set 
    tot_votes = total_votes, tot_rating = total_rating, 
    avg_rating = total_rating/total_votes 
where picture_id = new.picture_id; 

end# 
delimiter ; 

希望这有助于:)