使用频繁的Mysql排行榜

Question

我有一堆移动应用程序将数据发送到 mysql 服务器。每个应用程序都有自己的表，并且可以在短短几周内轻松达到 100,000 多条记录。目前我每天大约有 1000 万次对数据库的调用。我的系统当前设置的方式是每 5 分钟一个 cron 将对每个表运行一堆查询并将 html 缓存到文件中。然后，当用户调用排行榜时，我运行 1 个查询来获取他们当前的排名，然后显示缓存的 html 以及从查询中提取的排名。

虽然这有效，但我希望改变这一点有两个主要原因。我想在客户端而不是我的服务器上提供排行榜。我想发送 json 然后在他们的设备上解析它。另外，我想为每个应用程序添加每日、每周、每月和所有时间的排行榜。目前我存储数据的方式不允许这样做。

我的问题是

1. 将 json 发送给用户的最佳方式是什么？像我在文件中处理 html 一样缓存 json 然后让用户调用该文件是否明智？唯一的提示是我需要为每个应用程序创建 4 个 json 文件(每日、每周、每月、所有时间)。

2. 如果想要添加每日、每周、每月和所有时间的分数，数据库结构会是什么样子？我已经将每日分数添加到我的一些应用程序中，但我停止这样做了，因为这似乎是一种糟糕的做法。我基本上保留了所有用户的一个大列表以及他们开始新的一天的内容，每次他们更新分数时，我都会将其与他们存储的分数进行比较并得出差异。对于每天来说，该表很快就变得非常大，如果我有每周和每月，那么它很容易就会有数百万条记录。

我的每个应用程序的表结构确实非常简单

CREATE TABLE `app_test1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `deviceID` varchar(50) NOT NULL,
  `username` varchar(25) NOT NULL,
  `score` int(11) unsigned NOT NULL
  PRIMARY KEY (`id`),
  UNIQUE KEY `deviceID_2` (`deviceID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

通常我只会进行更改并根据我认为合适的方式调整它们，但是通过如此多的调用，我发现很多次简单的小更改很容易使服务器崩溃，所以现在我喜欢在我之前获得想法甚至尝试任何事情。

Answer 1

1) 不管最好的方法是缓存 JSON 文件，它确实具有与 HTML 中缓存的数据保持一致的好处。生成 JSON 可能会重用您在 HTML 中使用的相同代码 (d)，因此在某种程度上，您的工作量会减少。

2) 这部分很简单，至少乍一看是这样:为您为用户注册的每个分数添加时间戳。 确保添加包含时间戳的索引(或更多):您将根据时间执行大量数据操作。

另一种可能性是为每日、每周、每月和历史分数创建新的表格。缺点是，这会增加服务器端代码的复杂性，而且坦率地说，这有点令人厌恶……但是，您只需要在特定的表上查询相关的高分即可。因此，如果您保留 50 个最高分数，那么这些表中只会有 50 x 您的应用数量 行。当用户注册新分数时，您检查它是否高于 50 个每日分数中的最低分数:在这种情况下，将其插入表中并删除最低分数。如果它击败了每日得分，请对照每周得分进行检查，如果它击败了每周得分，请对照每月得分进行检查，依此类推。当您插入新的高分时，重新创建缓存的高分文件(HTML 或 JSON)。运行 cron 来在一天结束时清理每日表等。对这些表的读取和写入将比全表搜索更快。

只是给你一些想法，希望我的想法有意义。

编辑:我忘记了另一种可能性，即在不同的服务器之间分割数据。例如，在服务器 1 上拥有游戏 A、B 和 C 的数据，在服务器 2 上拥有游戏 D、E、F 和 G 的数据。这将使您能够更均匀地处理负载。这需要一些调整(您不希望一个服务器接收 80% 的流量，而另一个服务器只获得剩余的 20%)，但这是一个很容易实现的解决方案，因为所有游戏似乎都使用相同的架构；缺点是维护两台服务器显然会更昂贵。