php - 存储图形数据的最有效方法

Question

我总共提出了三种不同的、同样可行的方法来保存图形数据。

有问题的图表是“玩家在不同类别中随时间的得分”。类别包括“建筑”、“元素”、“任务完成”、“成就”等。

方法一:

CREATE TABLE `graphdata` (
    `userid` INT UNSIGNED NOT NULL,
    `date` DATE NOT NULL,
    `category` ENUM('buildings','items',...) NOT NULL,
    `score` FLOAT UNSIGNED NOT NULL,
    PRIMARY KEY (`userid`, `date`, `category`),
    INDEX `userid` (`userid`),
    INDEX `date` (`date`)
) ENGINE=InnoDB

此表包含每个用户/日期/类别组合的一行。要显示用户的数据，请选择 userid。旧条目通过以下方式清除:

DELETE FROM `graphdata` WHERE `date` < DATE_ADD(NOW(),INTERVAL -1 WEEK)

方法二:

CREATE TABLE `graphdata` (
    `userid` INT UNSIGNED NOT NULL,
    `buildings-1day` FLOAT UNSIGNED NOT NULL,
    `buildings-2day` FLOAT UNSIGNED NOT NULL,
    ... (and so on for each category up to `-7day`
    PRIMARY KEY (`userid`)
)

由于是主键，按用户 ID 选择速度更快。每天分数都会向下移动，如下所示:

... SET `buildings-3day`=`buildings-2day`, `buildings-2day`=`buildings-1day`...

条目不会被删除(除非用户删除他们的帐户)。可以使用 INSERT...ON DUPLICATE KEY UPDATE 查询添加/更新行。

方法三:

为每个用户使用一个文件，其中包含他们的分数数据的 JSON 编码数组。由于数据是通过 AJAX JSON 调用获取的，这意味着可以静态获取文件(甚至缓存到下一个午夜)，而不会对服务器造成任何压力。服务器每天运行每个文件，shift() 从每个数组中提取最旧的分数，然后 push() 在末尾输入新分数。

---

我个人认为方法 3 是目前​​为止最好的，但是我听说过使用文件而不是数据库的坏消息 - 例如，如果我希望能够根据用户在不同类别中的分数对他们进行排名，这个解决方案将是非常糟糕。

在这两个数据库解决方案中，我在我的一个较早的项目中实现了方法 2，它似乎工作得很好。方法 1 似乎“更好”，因为它更好地利用了关系数据库和所有这些东西，但我有点担心它将包含 (number of users) * (number of categories) * 7 行，结果可能是一个很大的数字。

有什么我遗漏的可以帮助我最终决定使用哪种方法吗？ 1、2、3 还是以上都不是？

Answer 1

如果你打算使用关系数据库，方法一比方法二好很多。它是规范化的，所以很容易维护和搜索。我会将 date 字段更改为 timestamp 并将其命名为 added_on(或者不是保留字的名称，例如“date”)。我会添加一个 auto_increment 主键 score_id，这样 user_id/date/category 就不必是唯一的了。这样一来，如果用户设法在同一秒内两次增加他的建筑分数，两次仍会被记录下来。

第二种方法要求您每天更新所有记录。第一种方法只做插入，不做更新，所以每条记录只写入一次。

... SET buildings-3day=buildings-2day, buildings-2day=buildings-1day...

您真的想每天更新表中的每条记录，直到时间结束？!

Selecting by user id is faster due to being a primary key

由于 user_id 是方法 1 主键中的第一个字段，因此查找速度同样很快。作为常规索引中的第一个字段(这是我上面所建议的)，它仍然会非常快。

关系数据库的想法是每一行代表一个实例/ Action /事件。因此，当用户做了一些影响他的分数的事情时，做一个 INSERT 记录他做了什么。您始终可以从这样的数据创建摘要。但是您无法从摘要中获得此类数据。

其次，您似乎异常关心如何删除旧数据。为什么？您的选择查询将有一个日期范围，可以自动排除旧数据。如果您关心性能，您可以 partition您的表基于行龄或设置一个 cronjob 以定期删除旧记录。

ETA:关于存储在文件中的JSON

在我看来，这似乎结合了方法 2 的缺点(难以搜索，每个文件必须每天更新)和文件访问的其他缺点。文件访问是昂贵的。文件写入更是如此。如果您真的想存储汇总数据，我会仅在请求数据时运行查询，然后将结果按 user_id 存储在汇总表中。该表可以包含一个 JSON 字符串:

CREATE TABLE score_summaries(
user_id INT unsigned NOT NULL PRIMARY KEY,
gen_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
json_data TEXT NOT NULL DEFAULT '{}'
);

例如:

Bob (user_id=7) 第一次登录游戏。他在他的个人资料页面上显示他的每周统计数据。这些查询运行:

SELECT json_data FROM score_summaries 
  WHERE user_id=7 
    AND gen_date > DATE_SUB(CURDATE() INTERVAL 1 DAY); 
//returns nothing so generate summary record

SELECT DATE(added_on), category, SUM(score) 
  FROM scores WHERE user_id=7 AND added_on < CURDATE() AND > DATE_SUB(CURDATE(), INTERVAL 1 WEEK)
  GROUP BY DATE(added_on), category; //never include today's data, encode as json with php

INSERT INTO score_summaries(user_id, json_data)
  VALUES(7, '$json') //from PHP, in this case $json == NULL
  ON DUPLICATE KEY UPDATE json_data=VALUES(json_data)

//use $json for presentation too

今天的分数是根据需要生成的，不会存储在摘要中。如果 Bob 今天再次查看他的分数，历史分数可以来自汇总表，也可以存储在第一次请求后的 session 中。如果 Bob 一周没有访问，则不需要生成摘要。