「いいね!」的なもののschema

facebookの「いいね!」やはてなstarみたいなものを自分でつくるとすると、どういうschemaにするかなーということで、ちょっと考えてみた。(mysql 5.1以上)

create table user (
    id int unsigned not null auto_increment,
    primary key id(id)
) ENGINE=InnoDB;

create table object (
    id int unsigned not null auto_increment,
    `key` varchar(255) not null,
    key_hash int unsigned not null,
    primary key id(id),
    index key_hash(key_hash)
) ENGINE=InnoDB;

create table favorite (
    object_id int unsigned not null,
    user_id   int unsigned not null,
    cnt       int unsigned not null default 1,
    timestamp timestamp,
    primary key (object_id, user_id),
    INDEX user_id(user_id)
) ENGINE=InnoDB PARTITIONS BY HASH(object_id)
PARTITIONS 100;

create table favorite_counter (
    object_id int unsigned not null,
    cnt       int unsigned not null default 1,
    primary key (object_id),
    INDEX   ranking(cnt)
) ENGINE=InnoDB;

この中で一番やっかいなのは、favoriteテーブルで、件数がどんどん増えてくる。
partitionでさばけなくなったらshardingするように変更する、という感じだろうか。
基本は、object(URLとか)単位でpartitioningしているが、これはユーザーごとのfavorite一覧よりは、objectごとのfavorite一覧の方が圧倒的に参照回数が多そうな気がするため。できれば時系列ごとに切れるとデータ量がある程度一定になるのでうれしいが、こういうサービスだと時系列でfavを消しちゃうのはどうなのかなとおもったりする。ひょっとするとhashパーティションよりも、object_idで、range partitionの方がよいかもしれない。というのも、object_idが新しいほど、参照や追加される数は多く、object_idが古いものは何かの弾みなどで再浮上したりするようなごく一部の人気記事に限られそうなので。ただし、別途object_idの範囲を監視しておいて、必要になったら次のpartitionを作る仕組みが必要なのと、パーティショニングが240個の制限があるし、rangeにせよどのぐらいの期間にするべきなのか、というのがちょっと難しそう。

object_id, user_idで複合主キーにしておくことで、clustered indexによりobjectごとに、favしているユーザー一覧を表示するときのSELECT文が効率的になるのではないかと思う。
(SELECT * FROM favorite WHERE object_id = ? )

とはいえ、人気のobjectとかだと結構な件数になったりする可能性があるので、別途counterも更新するようにしておき、件数だけはすばやくだせるようにしておく。cntへINDEXを貼っておくことでrankingをしやすくしておく。

objectもユーザーがどんどん作れるので、うまくいけば、けっこうなレコード数ができることになる。長い文字列に対してINDEXを貼ると、データサイズよりもINDEXのサイズが大きくなり、innodb buffer poolを圧迫するので、hash関数を通して、数値型に対してindexを貼ってやる
(ちょっとSELECTおよびINSERTがめんどうになるが
keyは予約語なので、別の名前の方が何かとめんどうでなくてよいかもしれない。

こういうやつだと、KVSの方が楽なのかなーと思いつつ、あるobjectをfavoriteしているユーザーの一覧の配列が巨大になったりすると、その対策を考えることになるやもしれない。