如何建模用户视频观看历史？含续播与历史查询需求

Cassandra的设计核心是为每个查询模式单独建模表，你的两个查询场景需求差异较大，单表无法同时满足高效查询和数据唯一性要求，建议拆分两张表分别对应Q1和Q2：

1. 表1：满足Q1（按用户+视频查询PIT） #

保留原设计的核心逻辑，调整主键结构让数据分布更合理，确保每个用户+视频组合仅存一条最新PIT记录：

CREATE TABLE pit_by_user_video (
  user_id UUID,
  video_id UUID,
  pit timestamp,
  PRIMARY KEY(user_id, video_id) -- 分区键user_id，聚类列video_id
);

• 查询逻辑：直接执行SELECT pit FROM pit_by_user_video WHERE user_id = ? AND video_id = ?，高效定位单条记录。
• 更新逻辑：每次用户观看视频更新PIT时，用INSERT语句自动覆盖同主键的旧记录（Cassandra的INSERT自带UPSERT特性），确保数据始终是最新的。

2. 表2：满足Q2（按用户查询最近浏览历史） #

新建专门的历史表，确保同一视频仅保留最新的浏览记录，同时原生支持按PIT倒序查询：

CREATE TABLE user_watch_history (
  user_id UUID,
  video_id UUID,
  last_watched_at timestamp,
  PRIMARY KEY(user_id, last_watched_at DESC, video_id)
);

• 主键设计：user_id作为分区键聚合同一用户的所有历史，last_watched_at DESC作为第一聚类列实现天然倒序，video_id作为第二聚类列避免同一时间戳下的视频冲突。
• 关键操作逻辑（业务层配合）：
  当用户更新某视频的PIT时：
  1. 先从pit_by_user_video表查询该用户+视频的旧PIT（如果存在）。
  2. 若旧PIT存在，执行DELETE FROM user_watch_history WHERE user_id = ? AND last_watched_at = ? AND video_id = ?删除旧记录。
  3. 插入新记录到user_watch_history：INSERT INTO user_watch_history (user_id, video_id, last_watched_at) VALUES (?, ?, ?)。
  4. 最后更新pit_by_user_video表的PIT。
• 查询逻辑：直接执行SELECT video_id, last_watched_at FROM user_watch_history WHERE user_id = ? LIMIT ?，结果天然按观看时间倒序排列，直接取最近的N条记录。

备选优化：轻量级事务（LWT）避免并发冲突 #

如果担心高并发场景下旧记录删除不及时，可以用LWT确保同一视频在历史表中仅存最新记录：

INSERT INTO user_watch_history (user_id, video_id, last_watched_at)
VALUES (?, ?, ?)
IF NOT EXISTS (
  SELECT 1 FROM user_watch_history 
  WHERE user_id = ? AND video_id = ? AND last_watched_at > ?
);

同时可配合定时任务，定期清理同一用户+视频下的冗余旧记录，进一步保证数据整洁。

为什么不建议单表？ #

Cassandra的主键结构直接决定了数据存储顺序和查询能力：

• 若用(user_id, video_id)做主键，无法按PIT排序查询用户的视频列表；
• 若用(user_id, pit, video_id)做主键，每次更新PIT都会生成新记录，导致同一视频有多条历史，且Cassandra不支持高效的聚类列去重查询。

分表设计虽然增加了一点业务层的操作复杂度，但完全匹配两个查询场景的性能需求，是Cassandra建模的标准实践。

内容的提问来源于stack exchange，提问作者MartinS