mysql - 在数据库中按视频时间保存视频观看统计数据

Question

我需要保存一些关于正在观看的视频的统计数据，其中之一是视频的哪些部分被观看最多。我想出的设计是将视频分成 256 个间隔，并为每个间隔保留 float 。我以用户连续观看的间隔数接收数据。问题是如何存储它们。我看到了两种解决方案。

每个视频片段的行

让我们有一个这样的数据库表:

CREATE TABLE `video_heatmap` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `video_id` int(11) NOT NULL,
 `position` tinyint(3) unsigned NOT NULL,
 `views` float NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `idx_lookup` (`video_id`,`position`)
) ENGINE=MyISAM

然后，每当我们必须处理多个 View 时，请确保存在相应的数据库行并将适当的值添加到 views 列。我发现如果首先处理行的存在(SELECT COUNT(*) 给定视频的行，如果缺少行则INSERT IGNORE，速度会快很多), 然后像这样使用一些更新查询:

UPDATE video_heatmap
SET views = views + ?
WHERE video_id = ? AND position >= ? AND position < ?

然而，这似乎有点臃肿。我想到的另一个解决方案是

每个视频行，交易更新

表格看起来(有点)像这样:

CREATE TABLE video (
 id INT NOT NULL AUTO_INCREMENT,
 heatmap BINARY (4 * 256) NOT NULL,
 ...
) ENGINE=InnoDB

然后，每次需要存储 View 时，都会在具有一致快照的事务中完成，顺序如下:

1. 如果数据库中不存在该视频，则会创建它。
2. 检索一行，heatmap，一个以二进制形式存储的 float 组，被转换成更易于处理的形式(在 PHP 中)。
3. 数组中的值适当增加，数组转换回来。
4. 通过 UPDATE 查询更改行。

到目前为止，优点可以总结如下:

第一种方法

• 将数据存储为 float ，而不是一些神奇的二进制数组。
• 不需要事务支持，因此不需要 InnoDB，而且我们目前对所有内容都使用 MyISAM，因此不需要混合存储引擎。 (只适用于我的具体情况)
• 不需要事务WITH CONSISTENT SNAPSHOT。我不知道这些的性能惩罚是什么。
• 我已经实现了它并且它有效。 (只适用于我的具体情况)

第二种方法

• 使用的存储空间少得多(第一种方法是存储视频 ID 256 次，并为视频的每个片段存储位置，更不用说主键了)。
• 应该更好地扩展，因为 InnoDB 的每行锁定与 MyISAM 的表锁定相反。
• 通常会工作得更快，因为发出的请求要少得多。
• 更易于在代码中实现(尽管另一个已经实现)。

那么，我该怎么办？如果不是我们系统的其余部分始终使用 MyISAM，我会采用第二种方法，但目前我倾向于第一种方法。但也许有一些理由支持一种或另一种方法？

Answer 1

第二种方法乍一看很诱人，但它使得诸如“视频 y 的片段 x 有多少 View ”之类的查询无法在 上使用索引video.heatmap.不确定这是否是您现实生活中的问题。此外，每次只需要一个段的数据时，您都必须来回解析整个数组。

但首先，您的第二个解决方案是 hackish(但仍然很有趣)。在您遇到实际性能问题之前，我不建议对您的数据库进行非规范化。

此外，尝试在插入 video 后立即使用 wiews = 0 预先填充 video_headmap 表(触发器可以提供帮助) .

如果空间确实是一个问题，请删除您的代理键 video_headmap.id 并将 (video_id, position) 设为主键(然后去掉多余的 UNIQUE 约束)。但这不应该考虑在内。每个视频 256 x 12 字节(具有 3 个数字列的粗行长度，可以为索引添加一些)每个视频仅额外 3kb!

最后，没有什么能阻止您将当前表切换到 InnoDB 并利用其行级锁定功能。

请注意，我无法理解为什么 views 不能是 UNSIGNED INT。我建议更改此类型。