用于评级系统的高效 MySQL 表结构

发布于 2024-07-15 11:02:33 字数 903 浏览 7 评论 0原文

我采纳了 cletus 和 Mehrdad Afshari 关于使用规范化数据库方法的史诗般的建议。下表是否正确设置以进行适当的优化？我对 MySQL 效率有点陌生，所以我想确保这是有效的。

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

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`)
) ;

原文

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

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`)
) ;

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

稀香 2024-07-22 11:02:33

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

游戏的票数：SELECT count(uid) FROM votes WHERE gid = $gid;< /code>

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

如果您想强制用户只能对游戏进行单次投票，请在 votesuid 和 gid 的主键> 表而不是普通的索引。

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 的索引。

我推荐 InnoDB 作为存储引擎，因为特别是在高负载设置下，表锁会降低你的性能。对于读取性能，您可以使用 APC、Memcached 或其他实现缓存系统。

average votes for a game: SELECT avg(vote) FROM votes WHERE gid = $gid;

number of votes for a game: SELECT count(uid) FROM votes WHERE gid = $gid;

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

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`)
) ;

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.

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.

回复收藏 0 原文