我正在基于Apache Cassandra设计一款聊天应用的数据库Schema，但遇到了一个棘手的问题。以下是我目前的Schema：

CREATE TABLE users( 
    user_id bigint, 
    nickname text, 
    email text, 
    chat_rooms set<timeuuid>, 
    PRIMARY KEY(user_id) 
); 

CREATE TABLE rooms( 
    room_id timeuuid, 
    creation_date timestamp, 
    creator_id bigint, 
    participants set<bigint>, 
    PRIMARY KEY(room_id) 
);

CREATE TABLE messages( 
    message_id timeuuid, 
    room_id timeuuid, 
    author_id bigint, 
    time_bucket int, 
    content text, 
    PRIMARY KEY((room_id, time_bucket), message_id) 
) WITH CLUSTERING ORDER BY (message_id DESC);

我希望能根据用户ID获取房间列表，并按最后回复时间排序，类似Facebook Messenger和Telegram的效果。我曾考虑给rooms表新增last_reply_time字段并将其作为聚簇键，但Cassandra不允许更新聚簇键值。我已查阅KillrChat示例和Discord关于Cassandra实现的文章，但未找到相关问题的解决方案，希望得到帮助。

Cassandra的核心设计原则是基于查询来建模，既然你的核心需求是「按用户ID获取房间列表并按最后回复时间降序排列」，那我们需要专门设计一张表来满足这个查询，而不是试图修改原有的rooms表（毕竟聚簇键确实无法更新）。

1. 创建专用查询表 #

我们需要一张以user_id为分区键，last_reply_time和room_id为聚簇键的表，这样就能直接按最后回复时间排序返回用户的房间列表。同时可以冗余一些常用字段（比如房间名称、最后一条消息预览）来避免二次查询，提升性能：

CREATE TABLE user_rooms_by_last_reply (
    user_id bigint,
    last_reply_time timestamp,
    room_id timeuuid,
    room_name text, -- 可选：房间名称，直接展示在列表中
    last_message_preview text, -- 可选：最后一条消息的预览内容
    creator_id bigint, -- 可选：冗余房间创建者ID
    PRIMARY KEY ((user_id), last_reply_time, room_id)
) WITH CLUSTERING ORDER BY (last_reply_time DESC, room_id ASC);

2. 维护这张表的关键操作 #

这张表需要和房间创建、消息发送、用户加入/离开房间的操作联动，确保数据一致性：

（1）房间创建时初始化记录

当创建一个新房间时，需要给所有初始参与者在这张表中插入一条记录，初始的last_reply_time就是房间的creation_date：

BEGIN BATCH
    -- 给参与者123插入记录
    INSERT INTO user_rooms_by_last_reply (user_id, last_reply_time, room_id, room_name, creator_id)
    VALUES (123, '2024-05-20 10:00:00', 550e8400-e29b-41d4-a716-446655440000, 'Tech Chat', 123);
    -- 给参与者456插入记录
    INSERT INTO user_rooms_by_last_reply (user_id, last_reply_time, room_id, room_name, creator_id)
    VALUES (456, '2024-05-20 10:00:00', 550e8400-e29b-41d4-a716-446655440000, 'Tech Chat', 123);
APPLY BATCH;

（2）发送新消息时更新最后回复时间

当有新消息发送到房间时，需要更新该房间所有参与者在这张表中的last_reply_time。这里推荐先删除旧记录，再插入新记录（比直接更新更简单，因为更新需要指定旧的聚簇键值）：

-- 假设新消息的时间戳是2024-05-20 10:30:00，房间ID是550e8400-e29b-41d4-a716-446655440000
BEGIN BATCH
    -- 更新用户123的记录：先删旧的，再插新的
    DELETE FROM user_rooms_by_last_reply
    WHERE user_id = 123 AND last_reply_time = '2024-05-20 10:00:00' AND room_id = 550e8400-e29b-41d4-a716-446655440000;
    INSERT INTO user_rooms_by_last_reply (user_id, last_reply_time, room_id, room_name, last_message_preview, creator_id)
    VALUES (123, '2024-05-20 10:30:00', 550e8400-e29b-41d4-a716-446655440000, 'Tech Chat', 'Hey, did you see the new Cassandra release?', 123);

    -- 对房间内其他用户执行同样的删除+插入操作
APPLY BATCH;

（3）用户加入/离开房间时的处理

• 用户加入房间：插入一条对应的记录，last_reply_time设为当前房间的最新回复时间（可以从rooms表或者messages表中获取）。
• 用户离开房间：从这张表中删除该用户对应的房间记录。

3. 查询用户的排序房间列表 #

有了这张表，查询就变得非常简单，直接按user_id查询，结果会自动按last_reply_time降序返回：

SELECT room_id, room_name, last_reply_time, last_message_preview 
FROM user_rooms_by_last_reply 
WHERE user_id = 123;

额外建议 #

• 优化时间处理：建议在messages表中显式添加message_time timestamp字段，而不是从message_id（timeuuid）中提取时间，这样更直观，也能避免时区处理的麻烦。
• 避免大批量操作：如果房间内有大量用户（比如几百人），不要一次性用BATCH处理所有用户的更新，可以分批次异步处理，避免Cassandra节点压力过大。
• 废弃冗余集合：原users表中的chat_rooms集合可以考虑删除，因为新的查询表已经能完全满足用户获取房间列表的需求，集合在Cassandra中无法排序，维护成本也更高。

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