MySQLのレプリケーションとパーティショニングの設計例(2)

前回の記事で、SNSや出会い系などのコミュニティでよく使われる会員制のメールシステムを構築することを考えることを通して、MySQLのレプリケーションについて考えてみた。今回はそのお話の続きをします。

さて、前回はテーブルを「ユーザー情報」「メール情報」の２つのスキーマに分割し、さらにそれぞれにレプリケーションを適応したのでした。

メールのテーブル分割

さて、今回は、さらにそのテーブルを複数に分割してみたいと思います。

テーブルを分割する際に、まずはそのデータが何によって成り立っているかを考えてみます。まずはメールについて考えてみると、mixiなどを含む会員制のメールシステムにおいては、主に以下のような機能が提供されています。

受信箱
送信箱
保存箱
ごみ箱
メール送受信機能
メール削除

これらをよく見てみると、どれも「特定のひとりのユーザー」にひもづいていることがわかります（メール送受信機能については、送信されたメールと受信されたメールで、それぞれ一人のユーザーとひもづきます）。なので、データを分割する際には、同じユーザーのメールは同じテーブルに入れておいたほうが一度に取得できることがわかります。逆を言えば、個々のユーザーごとのメールは別々のテーブルに入れても問題なさそうです。

つまり、ユーザーごとにメールデータを保存するテーブルを決定すればよいことになります。

つまり、test_recv_mail、test_send_mailそれぞれで user_id をキーにテーブルを分割すればよいことになります。

この時に重要なのは、あくまでも設計上、user_idにまたがって処理を行うSQLをできるだけ発生させないことです。とはいえ、この場合のメールについては問題なさそうですが、会員については分割する際に困った問題が発生するので、合わせて対処策を考えたいと思います。

さて、それでは、このテーブルの分割ですが、私の場合は、テーブル名の後ろに番号のついた、定義の同じテーブルを複数作成しています。

そして、user_idごとにどうやって分割するかですが、私の場合は、基本的には単純にテーブル数で割った余りで分割し、さらにテーブル名の管理用のクラスを作成しています。

<?php
class TableName {
  static public $prefix = "test_";
  static function recv_mail($user_id) {
    $i = ($user_id % 8) + 1;
    return self::$prefix."recv_mail__".$i;
  }
  static function send_mail($user_id) {
    $i = ($user_id % 8) + 1;
    return self::$prefix."recv_mail__".$i;
  }
}
?>

前回、作成したメールスキーマのクラスをこれに従って修正すると、以下のようになります。

<?php
class MailDB {
  // ... 省略
  
  function send($recv_user_id,$send_user_id,$title,$body) {
    $dt = date("Y/m/d H:i:s");
    $table_recv_mail = TableName::recv_mail($recv_user_id);
    $table_send_mail = TableName::send_mail($send_user_id);
    // まずは test_recv_mail に INSERT 
    $this->db_w->insert($table_recv_mail , array(
      "user_id" => $recv_user_id , 
      "send_user_id" => $send_user_id , 
      "title" => $title, 
      "body" => $body , 
      "status" => 1 , 
      "ins_dt" => $dt , 
      "upd_dt" => $dt
    ));
    // 次に、test_send_mail に INSERT 
    $this->db_w->insert($table_send_mail , array(
      "user_id" => $send_user_id , 
      "recv_user_id" => $recv_user_id , 
      "title" => $title, 
      "body" => $body , 
      "status" => 1 , 
      "ins_dt" => $dt , 
      "upd_dt" => $dt
    ));
  }
}

?>

recv_mailにはrecv_user_id、send_mailにはsend_user_idを分割のキーとして使用しますので、注意してください。

また、念のための注意点ですが、これによって、同じメール送信でも、test_recv_mailとtest_send_mailのレコードはそれぞれ別々の番号のテーブルに入ります。（たまたま一緒になることはあります）例えば、user_id=2のユーザーがuser_id=1のユーザーに対してメールを送信した場合、test_recv_mail__2とtest_send_mail__3にレコードがINSERTされます。

テーブル分割時の一意なプライマリーキーの生成

さて、これでメールテーブルの分割が完了しました、と言いたいところですが、ひとつ問題があります。

それは、それぞれのテーブルにおいて、プライマリーキーのauto_incrementが個別に設定されているため、それぞれのテーブルでmail_idが別々に採番されるということです。

書き方を気をつければそれでも動作するのですが、なんとなく気持ちが悪いですし、IDが一意になっていたほうが細かいところで気をつける必要もなくなるので、ここは一意にIDが発行されるように修正します。

まずは、以下のようにauto_incrementしていないテーブルに修正します。

次に、recv_mail、send_mailのぞれぞれで一意なテーブルを生成するためのテーブルを生成し、予めレコードをひとつだけ入れておきます。

このレコードに対して、以下のようなSQLを発行することで、IDが1増えます。さらに、SQLのLAST_INSERT_ID()関数を使って、最新のIDを取得します。

実は上記のDBクラスでは、id_gen()というメソッドが用意してあるので、それを使えば上記のSQLを内部的に実行します。（最後にはSELECT LAST_INSERT_ID()とやっていますが、その前にUPDATEするので、必ず更新系DBのクラスに対して行ってください）

これを使って、先ほどのメール送信部分のプログラムを修正すると、以下のようになります。

<?php
class MailDB {
  // ... 省略
  
  function send($recv_user_id,$send_user_id,$title,$body) {
    $dt = date("Y/m/d H:i:s");
    $table_recv_mail = TableName::recv_mail($recv_user_id);
    $table_send_mail = TableName::send_mail($send_user_id);
    // まずは test_recv_mail に INSERT 
    $mail_id = $this->db_w->id_gen("test_recv_mail_id_gen","mail_id");
    $this->db_w->insert($table_recv_mail , array(
      "mail_id" => $mail_id , 
      "user_id" => $recv_user_id , 
      "send_user_id" => $send_user_id , 
      "title" => $title, 
      "body" => $body , 
      "status" => 1 , 
      "ins_dt" => $dt , 
      "upd_dt" => $dt
    ));
    // 次に、test_send_mail に INSERT 
    $mail_id = $this->db_w->id_gen("test_send_mail_id_gen","mail_id");
    $this->db_w->insert($table_send_mail , array(
      "mail_id" => $mail_id , 
      "user_id" => $send_user_id , 
      "recv_user_id" => $recv_user_id , 
      "title" => $title, 
      "body" => $body , 
      "status" => 1 , 
      "ins_dt" => $dt , 
      "upd_dt" => $dt
    ));
  }
}

?>