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

さて、前回の記事、前々回の記事では、受信メール、送信メールのテーブルを全部で８個に分割しました。

今回は同じようにuser_idをキーにユーザーテーブルを分割します。

そして、テーブル名クラスにもuserを追加します。

<?php
class TableName {
  static public $prefix = "test_";
  static function user($user_id) {
    $i = ($user_id % 8) + 1;
    return self::$prefix."user__".$i;
  }
  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;
  }
}
?>

さらにユーザー情報の取得のメソッドを下記のように修正します。ついでに今回、会員登録するためのregist()メソッドを追加してみました。（本当はメールアドレスに重複チェックなどが必要ですが、今回は省略しています）

<?php
class UserDB {
  // .. 省略
 
  function get($user_id) {
    // 1レコードのみ取得
    $table_user = TableName::user($user_id);
    return $this->db_r->selectFirst("
      SELECT 
        user_id , email , passwd, nickname , 
        status , ins_dt , upd_dt 
      FROM 
        $table_user
      WHERE 
        user_id = :user_id 
      LIMIT 
        1 
    " , array(":user_id" => $user_id));
  }
  function regist($email,$passwd,$nickname) {
    $user_id = $this->db_w->id_gen("test_user_id_gen","user_id");
    $table_user = TableName::user($user_id);
    
    $dt = date("Y/m/d H:i:s");
    $this->db_w->insert($table_user,array(
      "user_id" => $user_id , 
      "email" => $email , 
      "passwd" => $passwd , 
      "nickname" => $nickname ,
      "status" => 1, 
      "ins_dt" => $dt , 
      "upd_dt" => $dt , 
    ));
    return $user_id;
  }
}
?>

見た目は複雑そうですが、やっていることは非常に単純で、SELECTやINSERT時に、user_idによってSQLが実行されるテーブルを分けているだけとなります。

さて、会員制のシステム、というと、通常はログイン認証があるかと思います。ログイン認証は通常、会員IDのような自動採番のIDだと覚えにくいため、例えば、メールアドレスとパスワードなどで行うことが多いかと思います。しかし、ログイン認証をメールアドレスで行う場合、ちょっと困った事になります。

というのは、会員テーブルはuser_idによって分割されているため、user_idがわからないと、どのテーブルに該当のデータがあるか計算できないからです。これではすべてのテーブルをしらみつぶしに調べなければならなくなりますが、それは手間ですし、負荷も高いのでできれば避けたいところです。

また、コミュニティサイトであれば、ニックネームなどによる会員検索などの機能もありますが、これも同じくuser_idがわからないため（というより複数のuser_idにまたがるため）そのままでは実装できません。

実はここにこの設計の弱点があって、だから設計の時点でできるだけこういう問題が発生しないようなものを分割のキーとするように十分、工夫しなければならないのですが、会員の場合、他の人のページでメールアドレスをGETで引き回すわけにもいきませんし、会員検索もあるのでどれをキーにしても同じ問題が発生します。

そういう場合には、user_idに関係なく検索できるようなテーブルを別に作成します。

上記の場合、test_user__*テーブルからはemail,passwdを取り除いたほうが、重複がなくて実装がきれいなのですが、個人的にはあまり余計なSELECT文を発行させたくないので、ちょっと冗長ですが、test_userテーブルとtest_user_email_listテーブルの両方に残しておこうと思います。

そして、以下のように実装を変更させれば完了です。

test_userのINSERT/UPDATE時に同時に、test_user_email_list、test_user_searchを更新するようにする
メールアドレス、パスワードによるログインの場合は、test_user_email_listテーブルからuser_idを取得し、そこからtest_user__*テーブルに対してSELECTする
会員検索はtest_user_searchに対して検索をかけ、取得したレコードのuser_idからtest_user__*に対して検索をかける

具体的なプログラムは省略しますが、上記のように実装すれば、テーブルを分割したままで横断的なSELECTもかけれるようになります。

また、場合によっては、たとえばメールアドレスをキーにtest_user_email_listを分割したり、test_user_searchのような全文検索をMySQL以外のシステムで構築したりなどの工夫もできるかと思います。

次回からは論理削除されたテーブルをmyisampackテーブルと呼ばれる圧縮化テーブルに移動して、論理削除されたテーブルを分割する方法について書きます。