MySQL -百万条目排行榜

Question

我正在做一个项目，现在我正在实现一个排行榜。在我开始工作之前，我需要一些建议来更好地实践我的排行榜结构。

首先，排行榜将显示在两个页面上，一个是在每个球员的主页上，其中将包含前10支球队(所有球员相同的10支球队)，另一个排行榜将在排行榜的页面上，在那里，将有所有的球队与排序功能。

每行排行榜的结构如下：

·排名位置

·团队名称

·团队价值

·球队赢得的比赛总数

·球队击败的比赛总数

·球队打平的比赛总数

·团队制定的目标总和

·球队丢球的总和

·球队最近4场比赛的结果

下面是我的数据库的表

挑战表

CREATE TABLE `challenges` (
  id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  'challenge_date` datetime NOT NULL,
  `status` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `challanges_id_index` (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

挑战和结果

CREATE TABLE `challenges_results` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `challenge_id` int(11) NOT NULL,
  `team_id` int(11) NOT NULL,
  `goals` int(11) NOT NULL,
  `result` char(1) DEFAULT NULL,
  `challenge_date` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

在挑战结果列中，W代表胜利，D代表平局，L代表失败

团队价值观

CREATE TABLE `team_values` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `team_id` int(11) DEFAULT NULL,
  `value` double(15,8) DEFAULT '1500.00000000',
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

团队

CREATE TABLE `teams` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `avatar` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `founded` date NOT NULL,
  `residense_city_id` int(10) unsigned NOT NULL,
  `slug` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `primary_color` char(10) COLLATE utf8_unicode_ci NOT NULL,
  `secondary_color` char(10) COLLATE utf8_unicode_ci NOT NULL,
  `status` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `teams_slug_unique` (`slug`),
  KEY `teams_id_index` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

一个团队可以有多个值(teams_values)，但只显示最近的值。一个团队可能面临许多挑战。一个团队可以从不同的挑战中获得许多结果。

排行榜将按如下方式工作。将使用teams_values表中的最高值对团队进行排序。每次团队遇到挑战时，都会计算并存储该值。

在两个或更多团队具有相同值的情况下，我们需要应用以下三个规则。规则也需要一个接一个地执行，例如，如果我运行第一个规则，仍然有一些球队在价值和得分上是相等的，那么我将应用第二个规则，依此类推。·最佳进攻(进球次数更多)

·最佳防守(失球次数更少)

·在他们之间的比赛中获胜最多的球队

所以我提出了三个解决方案，我仍然不知道哪一个更好，也不知道这三个中是否有更好的。

我认为第一个选项是使用inner join、union等选项从表中收集信息，并将规则应用于相同的SQL查询。所以每次我想要查看排行榜的时候，我都会执行这个SQL。这个解决方案的问题是，如果我们希望排行榜总是更新最新的结果，我不知道效果会有多好。因为映像每天有10k访问者，并且每个人都在执行这个查询。

第二种选择是收集信息，如果有重复的值，我将使用PHP获取重复的团队，应用规则，然后根据规则的结果在数组中滑动团队。从performance site我不知道这个选项有多有效。

第三个解决方案是创建另一个名为leaderboard的表，我将在球队不存在的情况下存储所有这些信息，或者如果存在，我将根据最新挑战的结果更新记录，例如，如果球队得分，则增加目标。然后，我将只使用排行榜表格来过滤数据并打印团队的排名。我认为这个选项更好，因为我只需要处理一个表，并且我只会在团队完成挑战时更新记录。

我们将使用缓存，但现在我们认为排行榜应该始终是最新的，而不是每天更新一次。

哪一个是更好的解决方案，为什么，如果有更好的解决方案，我愿意接受建议。谢谢

Answer 1

由于您在虚拟专用服务器上使用共享帐户运行，因此理论上很可能会遇到争用服务器资源、磁盘使用率、内存使用率和cpu处理能力的情况。

首先，也是最重要的，尝试在MySQL中进行所有的数据库计算，只有在完成所有操作后才将数据返回给PHP。MySQL针对这项工作进行了优化，而PHP在一般的计算问题上做得更好。

减轻服务器负载的一种选择是使用PHP创建一个网页，该网页在每次排行榜更新时都可以在浏览器中查看。这样，每次排行榜需要更新时，您只需要运行一次计算。

如果我正在构建系统，并且系统永远不会达到企业级级别，而是保持小而实用，那么我会在早期编写一个PHP脚本，因为这样可以节省大量的处理能力。

值得注意的是，如果服务器配置良好，你不应该担心每天收到10k个用户请求，除非你的代码写得真的很糟糕。

编辑:作为事后考虑，您可以安装一个像https://memcached.org/这样的程序，它可以将您的SQL数据缓存到内存中。像LiveJournal和Wordpress这样的网站都在使用它，但你需要对它进行配置，使其适用于其余的vps用户，除非它的规格非常高。