用于评级系统的高效MySQL表结构 [英] Efficient MySQL table structure for rating system

问题描述

此帖子是对以下已回答问题的跟踪:用于存储用户ID列表的最佳方法.

This post is a follow-up of this answered question: Best method for storing a list of user IDs.

我接受了cletus和Mehrdad Afshari关于使用标准化数据库方法的史诗般的建议.下表是否正确设置以进行适当的优化?我是MySQL效率的新手，因此我想确保它是有效的.

I took cletus and Mehrdad Afshari's epic advice of using a normalized database approach. Are the following tables properly set up for proper optimization? I'm kind of new to MySQL efficiency, so I want to make sure this is effective.

此外，在查找游戏的平均评分和总票数时，我应该分别使用以下两个查询吗?

Also, when it comes to finding the average rating for a game and the total number of votes should I use the following two queries, respectively?

SELECT avg(vote) FROM votes WHERE uid = $uid AND gid = $gid;    
SELECT count(uid) FROM votes WHERE uid = $uid AND gid = $gid;

CREATE TABLE IF NOT EXISTS `games` (
  `id` int(8) NOT NULL auto_increment,
  `title` varchar(50) NOT NULL,
  PRIMARY KEY  (`id`)
) AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(8) NOT NULL auto_increment,
  `username` varchar(20) NOT NULL,
  PRIMARY KEY  (`id`)
) AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `votes` (
  `uid` int(8) NOT NULL,
  `gid` int(8) NOT NULL,
  `vote` int(1) NOT NULL,
  KEY `uid` (`uid`,`gid`)
) ;

推荐答案

游戏平均票数:SELECT avg(vote) FROM votes WHERE gid = $gid;

游戏票数:SELECT count(uid) FROM votes WHERE gid = $gid;

由于您的用户或游戏ID不会小于0，因此可以将它们设置为无符号整数(int(8) unsigned NOT NULL).

As you will not have any user or game ids smaller then 0 you could make them unsigned integers (int(8) unsigned NOT NULL).

如果要强制用户只能对游戏进行一次投票，请在votes表中的uid和gid上创建一个主键，而不是普通索引.

If you want to enforce that a user can only make a single vote for a game, then create a primary key over uid and gid in the votes table instead of just a normal index.

CREATE TABLE IF NOT EXISTS `votes` (
  `uid` int(8) unsigned NOT NULL,
  `gid` int(8) unsigned NOT NULL,
  `vote` int(1) NOT NULL,
  PRIMARY KEY (`gid`, `uid`)
) ;

主键字段的顺序(首先是gid，然后是uid)很重要，因此索引首先按gid进行排序.这使得索引对于具有给定gid的选择特别有用.如果要选择给定用户进行的所有投票，则只需添加uid即可添加另一个索引.

The order of the primary key's fields (first gid, then uid) is important so the index is sorted by gid first. That makes the index especially useful for selects with a given gid. If you want to select all the votes a given user has made then add another index with just uid.

我建议将InnoDB用于存储引擎，因为尤其是在高负载设置中，表锁会降低性能.为了提高读取性能，您可以使用APC，Memcached或其他工具实现缓存系统.

I would recommend InnoDB for storage engine because especially in high load settings the table locks will kill your performance. For read performance you can implement a caching system using APC, Memcached or others.

这篇关于用于评级系统的高效MySQL表结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！