cassandra中可变列的Order by

Question

create table xx(
 bucket_id int,
 like_count int,
 photo_id int,
 username text,
 PRIMARY KEY(bucket_id,like_count,photo_id)
) WITH CLUSTERING ORDER BY (like_count DESC)

在这里，我可以按照like_count的降序获取所有记录。但是我需要在应用程序的某个时候更新like_count，因为它是主键的一部分，所以我不能这样做。

如果我从主键中删除它，我就不能根据like_count获得排序结果。在卡桑德拉，解决这个问题的正确方法是什么？

Answer 1

恐怕卡桑德拉不适合处理易变的命令。（考虑Redis Sorted Sets）也就是说，您实际上可以使用类似CAS的语义（比较和设置）和轻量级事务来实现这一点，这将使您的更新慢20倍左右。

您还需要一个额外的表，它将作为每个bucket_id/photo_id的当前like_count的查找。

create table yy (
  bucket_id int,
  photo_id int,
  like_count int,
  PRIMARY KEY((bucket_id,photo_id))
)

然后从xx执行一个轻量级事务性删除，然后（如果成功）重新插入xx并更新到YY：一些伪代码：

//CAS loop (supposedly in a function of args: bucket_id, photo_id, username, new_score)
for (;;) {

  //read current score (the assumption here is that the bucket_id/photo_id entry already exists in both xx and yy)
  ResultSet rs1 = select like_count from yy where bucket_id = ? and photo_id = ?
  int old_score = rs1.one().getInt(0)

  //same score don't do anything
  if (new_score == old_score) break;

  //attempt to delete using light-weight transaction (note usage of IF EXISTS)
  ResultSet r2 = delete from xx where bucket_id = ? and photo_id = ? and like_count = old_score IF EXISTS
  if (rs2.one().getBool(0)) {

    //if delete was successful, reinsert with the new score
    insert bucket_id, photo_id, photo_id, username, like_count into xx values (?, ?, ?, new_score)

    //update lookup table
    update yy set like_count = new_score where bucket_id = ? and photo_id = ?

    //we are done!
    break;
  }

  //delete was not successful, someone already updated the score
  //try again in a next CAS iteration
}