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

Question

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

4

我想到了三种不同但同样可行的方法来保存图表数据。

所涉及的图表是“各类别得分随时间变化”的玩家得分图表。类别包括“建筑物”、“物品”、“任务完成”、“成就”等。

方法1：

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查询添加/更新行。

方法3：

为每个用户使用一个文件，其中包含其分数数据的JSON编码数组。由于数据正在通过AJAX JSON调用获取，因此该文件可以静态获取（甚至可以缓存到以下午夜），而不会对服务器造成任何压力。每天，服务器都会运行每个文件，将每个数组中最旧的分数shift()出来，并将新的分数push()到末尾。

个人认为方法3是迄今为止最好的方法，但我听说过使用文件而不是数据库的坏处-例如，如果我想能够按不同类别的得分排名用户，则这种解决方案将非常糟糕。

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

是否有什么我错过的东西可以帮助我做出最终决定使用哪种方法？1、2、3或以上都不是？

- Niet the Dark Absol

“(用户数量) * (类别数量) * 7”的答案是什么？ - Ben

目前，由于项目正在开发中，答案是 8*5*7 = 280。随着预期负载的增加，答案将更接近于 100,000 * 8 * 7 = 5,600,000... - Niet the Dark Absol

2个回答

1

对我来说，方法1似乎是一个明显的赢家。如果您担心单个表（graphData）的大小太大，可以通过创建

CREATE TABLE `graphdata` (
    `graphDataId` INT UNSIGNED NOT NULL,
    `categoryId` INT NOT NULL,
    `score` FLOAT UNSIGNED NOT NULL,
    PRIMARY KEY (`GraphDataId'),
) ENGINE=InnoDB

因为你显然需要将graphDataId与userId连接起来，所以需要创建2个表。

create table 'graphDataUser'(
         `graphDataId` INT UNSIGNED NOT NULL,
        `userId` INT NOT NULL,
)ENGINE=InnoDB

和 graphDataId 日期连接

create table 'graphDataDate'(
         `graphDataId` INT UNSIGNED NOT NULL,
        'graphDataDate' DATE NOT NULL
)ENGINE=InnoDB

我认为你不需要担心某个表格包含的行数，因为大多数数据库管理员在处理行数方面做得很好。你的工作只是以易于检索的方式格式化数据，无论数据被检索用于什么任务。长期来看，遵循这个建议应该会有所回报。

- Vjerci

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- dnagirl · Accepted Answer

如果你要使用关系型数据库，方法1比方法2好得多。它是标准化的，因此易于维护和搜索。我会将date字段更改为timestamp并将其称为added_on（或者像“date”这样的保留字）。我会添加一个自增主键score_id，这样user_id/date/category就不必是唯一的了。这样，如果用户在同一秒内成功增加了他的建筑分数两次，两个分数都将被记录下来。

第二种方法要求您每天更新所有记录。第一种方法只进行插入，没有更新，因此每个记录只写入一次。

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

你真的想每天更新表中的每条记录直到永远吗？！

按用户ID选择更快，因为它是一个主键

由于user_id是您的Method 1主键中的第一个字段，因此查找将同样快速。作为常规索引的第一个字段（这就是我上面建议的内容），它仍然非常快。

关系型数据库的理念是每行代表单个实例/操作/事件。因此，当用户执行某些操作以影响其分数时，请进行记录该操作的插入操作。您始终可以从此类数据创建摘要。但是您无法从摘要中获取此类数据。

其次，您似乎过于关注如何摆脱旧数据。为什么？您的选择查询将在其中具有日期范围，自动排除旧数据。如果您担心性能，可以基于行龄对表进行分区或设置定期删除旧记录的cronjob。

预计到达时间：关于存储在文件中的JSON

在我看来，这种方法结合了第二种方法的缺点（难以搜索，每个文件每天都必须更新）和文件访问的额外缺点。文件访问是昂贵的，文件写入则更加昂贵。如果您真的想存储摘要数据，我建议仅在请求数据时运行查询，并按用户 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（用户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再次查看他的分数，历史分数可以来自摘要表或在第一次请求后存储在会话中。如果Bob一个星期内没有访问，则不需要生成摘要。