ユーザID単位で1つだけもつデータ

これは分かりやすいですね。例えばuser_idをプライマリーキーにして、ニックネームや経験値などのデータを保存する類です。

CREATE TABLE `user_data` (
  `user_id` int(10) unsigned NOT NULL,
  `nickname` varchar(40) NOT NULL,
...
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB

ユーザID1つで複数もつデータ

これは例えばユーザの持っているアイテムのデータなどです。user_idとitem_idの複合キーがプライマリーキーになる感じです。

CREATE TABLE `user_item` (
  `user_id` int(10) unsigned NOT NULL,
  `item_id` int(10) unsigned NOT NULL,
  `num` int(10) unsigned NOT NULL,
...
  PRIMARY KEY (`user_id`,`item_id`)
) ENGINE=InnoDB

ソーシャルグラフ的データ

これがソーシャルゲームならではのデータです。例えばあるユーザの友達のデータとか、あるユーザと別のユーザのやり取りを記録するデータとか。user_id-Aとuser_id-Bの複合キーがプライマリーキーになる様な感じです。

CREATE TABLE `user_relations` (
  `user_id` int(10) unsigned NOT NULL,
  `tgt_user_id` int(10) unsigned NOT NULL,
  `visit_num` int(10) unsigned NOT NULL,
...
  PRIMARY KEY (`user_id`,`tgt_user_id`)
) ENGINE=InnoDB

ログ系のinsert中心のデータ

これは例えば何かのアクションの履歴データなどです。自分や他人からのアクションの履歴を表示したいときなどに活用すると思います。これはシーケンシャルな数字がプライマリーキーになり、それを他のテーブルに格納しておいたり、セカンダリインデックスで検索する形になると思います。

CREATE TABLE `action_log` (
  `id` bigint(20) unsigned NOT NULL,
  `date` timestamp NULL DEFAULT 0,
  `value` int(10) unsigned NOT NULL,
...
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

パラメータなどのマスターデータ

これは敵の出現確率とかアイテムの名前とか、主に運営側のみが更新するデータです。こういうデータは別にRDBに保持する必要はなくて、ファイルにまとめてアプリサーバにデプロイする運用もあると思いますが、バックエンドツールを使ってエンジニア以外の人でもパラメータが変更したいとかがある場合にはRDBに保持している方が楽ちんだと思います。

 CREATE TABLE `item_conf` (
  `item_id` int(10) unsigned NOT NULL,
  `item_type` tinyint(3) unsigned NOT NULL,
  `name` varchar(255) DEFAULT NULL,
...
  PRIMARY KEY (`item_id`)
) ENGINE=InnoDB

意外と少ない！

もちろんこれら以外にいろんなデータはあると思いますが、多くのソーシャルゲームで共通しているのはここに当てはまるものが多いと思いますし、この辺のデータの扱い方が分かってくれば、これら以外のデータについても適切な扱い方のコツが分かってくると思います。

これらのテーブルについて、ソーシャルゲーム＋InnoDBという組み合わせにおいてどういったアプローチを取るのがよいかという話をしたいのですが、全部やると多いので今回はまずログ系のテーブルについてフィーチャーしてみたいと思います。

「最近」のデータしか使わない工夫

ログ系のテーブルでは基本は新しくinsertされた行への参照がほとんどになる様に設計すべきです。何よりもこれが大事。ログ系のテーブルは放っておけばどんどんサイズが大きくなります。アクセスされるデータの量が昔のものまで満遍なく存在していると、buffer poolというメモリキャッシュに載せるべきデータが時間と共にどんどん増えていき、すぐにメモリから溢れてIOが発生します。IOは怖いです。まんじゅう怖いとは違います。マジで怖いです。

これにはサービス的な割り切りが必要になることもあります。「2週間より前のデータは見れません」という仕様にしてしまえば、サービス的に2週間より前のデータは不要になるはずですが、「サービス開始時点から全て見れます」という仕様にしてしまうと、結構大変です。

下手にこんな仕様にしてしまうと、Twitterさんがやっている(と言っている)様に、いずれ時系列でサーバ自体を分ける様な運用が必要になってしまうでしょう。

とは言え、こういう背景を知らない人が普通の感覚で言ったら「全部見れて当たり前」という風になってしまうもの。ここはエンジニアが費用対効果を良く検討して、どういう仕様が適切なのかを考えてあげる必要があると思います。

次なる敵は「データサイズ」

さて、最近のデータだけあればよい、という設計ができたとしましょう。しかし、一度insertしてしまったデータは消さない限り基本的にはずっとDBには残り続けてしまいます。極端な例で言えばアクセスログの様に全PVで1レコードをこのテーブルに入れてしまうと、ソーシャルゲームの様にPVの多いサービスではあっという間に数億件とか数十億件とか、そういうサイズになってしまいます。

buffer pool的にはアクセスのないデータはメモリから追い出されるのであまり性能的な劣化はないかも知れません。しかしこのままでは物理的な「容量」がいずれ問題になってしまいます。DISKも無限ではありません。

そこでよくある方法としては、古いデータをdeleteで消す(パージする)というやり方です。先程も書いた様にこの手のテーブルで必要なデータは「最近」のものであることが多いので、思い切って消してしまうという戦略は当然アリです。ただし注意が必要で、deleteはクソ重いです。大切なので2回言います。deleteはクッソ重いです。ログ系のテーブルの場合、わざわざbuffer poolに乗ってないデータを読みだして消す必要があるため、ゲロ遅いです。

パーティションを使おう！

仕方ないので、MySQL 5.0の頃はパージするスクリプトを作って夜中のアクセスが少ない時間に毎日回してせっせと消すといった作業が必要でした。

しかし、MySQL 5.1からは念願の「パーティション機能」が追加されていますのでこれを使わない手はないでしょう。

パーティションを知らない人のために説明しますと、簡単に言えば1つのテーブルを「ある規則」に従って別のテーブルの如く格納させる仕組み、と言えると思います。「ある規則」は色々設定できますが、データパージという意味でよく使うのは「RANGEパーティション」といって、あるカラムの値の幅で切るというやり方です。言葉の説明だけだと分かりにくいですが、こんな感じ。

CREATE TABLE `action_log` (
  `id` bigint(20) unsigned NOT NULL,
  `date` timestamp NULL DEFAULT 0,
  `value` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`,`date`)
) ENGINE=InnoDB
PARTITION BY RANGE (UNIX_TIMESTAMP(date))
(PARTITION p20101004 VALUES LESS THAN (UNIX_TIMESTAMP('20101004')),
 PARTITION p20101011 VALUES LESS THAN (UNIX_TIMESTAMP('20101011')),
 PARTITION p20101018 VALUES LESS THAN (UNIX_TIMESTAMP('20101018')),
 PARTITION p20101025 VALUES LESS THAN (UNIX_TIMESTAMP('20101025')),
 PARTITION p20101101 VALUES LESS THAN (UNIX_TIMESTAMP('20101101')),
 PARTITION p20101108 VALUES LESS THAN (UNIX_TIMESTAMP('20101108')),
 PARTITION p20101115 VALUES LESS THAN (UNIX_TIMESTAMP('20101115')),
 PARTITION over VALUES LESS THAN MAXVALUE)

idはログのユニークな番号でこれについてはまた後述します。pYYYYMMDDというパーティションはYYYYMMDDまでのレコードが格納されるように設定されています。なおパーティションの細かい文法とかはMySQLのドキュメントを参考にして下さい。

一つ注意する必要があるのは、現在の仕様ではパーティションに切りたいカラム(↑ならdate)は全てのunique indexについてその一部である必要があります。そのため、action_logはidカラムだけで完全にユニークにできるのですが、敢えてPKにdateが入っています。

さて、こうしておくと例えば20101004のデータはもう不要なのでパージしたいと思ったときに、5.0時代は

delete from hoge_log where date < XXXXXXXX;

とか書いてたものを(本当はこんなの実行したら死にますが)

alter table hoge_log drop partition p20101004;

とするだけで、あたかもdrop tableするかの如く、高速にパージすることができます。データが巨大なInnoDBを触ったことがないと実感がわかないかも知れませんが、これは本当にありがたい機能です。もはやちまちまdeleteするスクリプトを書く必要もなく、スピード調整に失敗してレプリ遅延に泣かされることもなく(しかも大抵夜中＞＜！)、テーブルサイズをある程度のサイズで管理することができるようになるわけです。DBAにはヨダレものですね！

実はパーティションには他にも恩恵があります。それがプルーニング(刈り込み)と呼ばれる機能です。さっきのテーブルをselectする際に、dateの条件を与えてあげると、場合によっては見る必要のないパーティションが出てくると思いますが、それをオプティマイザが判断してくれて必要なパーティションのみ見るようになってくれます。1週間単位の集計を行いたいとか、サービスからの参照は2週間前までのみでよいとかを明示的にwhere句で与えてあげることで、より効果的にテーブルを使うことができます。

プルーニングが効いているかどうかは「explain partitions」を実行することで分かります。

mysql> explain partitions select * from hoge_log where id > XXXXX\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: hoge_log
   partitions: p20101004,p20101011,p20101018,p20101025,p20101101,p20101108,p20101115,over
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: *********
        Extra: Using where

mysql> explain partitions select * from hoge_log where date > XXXXXXX\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: hoge_log
   partitions: p20101108,p20101115,over
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: **************
        Extra: Using where

下のクエリではwhere句にdateを入れていて、刈り込みが効いていることが「partitions」のカラムを見ると分かりますね！

なんか通販みたいですが、パーティションにはさらにさらに特典があります。それは削除だけでなくINSERTについても性能が向上するということです。前述の様にログ系のテーブルはINSERT中心になるのでこれもおいしい恩恵ですね。RANGEパーティションによるINSERT性能の改善効果については、弊社松信の発表資料(英語ですが)も参考にしてください。http://www.mysqlconf.com/mysql2009/public/schedule/detail/6661(ここからたどれるPDFのp.44-46)

idはどうするか？

さて、ログ系のテーブルのもう一つのトピックとしては「id」をどうやって決めるか、というのがあります。素朴にやるなら、idカラムにauto_increment属性をつけてしまえば、MySQLが勝手にユニークなidをつけてくれるのでお手軽ですね。

ところが、規模が大きくなり秒間クエリ数が増大してくると困ったことになってきます。一つはMySQL 5.0以下のInnoDBを使っている場合には、弊社松信が書いているこちらのエントリにもあるように、並列性が低いという問題があります。こちらの解決の1つの方法はMySQL5.1以上のInnoDBを使うことで解決されます。

もう一つの大きな問題としては、このテーブルを2つ以上のデータベースに分割(Sharding)したくなった時です。この場合、複数データベースにまたがってユニークなidを確保するにはauto_incrementだけではちょっと面倒です。

そこでモバゲータウンではidだけを払い出すテーブルを1つ作って1レコードだけinsertして、updateにより採番し、実際のテーブルには採番したidを指定してinsertするという手段を用いています。

CREATE TABLE `seq_log` (
  `id` bigint(20) unsigned NOT NULL
) ENGINE=MyISAM
INSERT INTO seq_log VALUES (0);

my $sth = $dbh->prepare_cached("update seq_log set id=LAST_INSERT_ID(id+1)");
$sth->execute();
return($dbh->{'mysql_insertid'});

Perlでユニークなidを引っ張るならこんな感じ

この方式であれば、Shardingした系統がいくつになっても常にユニークなidが採番できるため、アプリの作りもデータの持ち方もシンプルにすることができます。なお、MyISAMのテーブルロックがあるため同時並列性はないですが、現状のモバゲータウンのトラフィックであってもこの方式で特に問題は発生していません。ある程度の規模が見込まれて、Shardingすることが予想されるのであれば、こういった方式がおすすめです。

ログ系テーブルのまとめ

というわけでまとめ

• なるべく最新のデータへのアクセスだけで済む様に設計する
  • 全データを永遠にDBに残すとかの仕様にしない
• MySQL 5.1以上オヌヌメ
• RANGEパーティション使って、古いのを簡単に消せる様にする
  • 刈り込みも使える時は有効活用
• idの払い出しは専用の採番テーブルを使うか、5.1以上のauto_increment
  • Shardingする場合には、採番テーブルが便利

実は、今回紹介した以外の方法でログ系のテーブルを一定程度のサイズに押さえこむテクニックもあるのですが、それはこの記事が人気が出たら書くことにしましょう＾＾