アカウント毎にパーティションするという発想は有効ですか？

こんばんは。
MySQLのパーティショニング機能ですが、以下のケースで有効に働くかどうか分かりません。

・全てのテーブルを、ユーザーアカウント単位でHASH パーティショニングする
たとえばWeb上でゲームや交換日記ができるシステム開発を行い、以下のようなテーブルを設計します。

MySQL
1-- 得点テーブル
2CREATE TABLE score (
3    score_id INT NOT NULL,
4    user_id INT NOT NULL,
5    battle_score INT NOT NULL,
6    bonus_score INT NOT NULL,
7    get_medals INT NOT NULL,
8    created_at DATETIME NOT NULL,
9    PRIMARY KEY(id)
10)
11PARTITION BY HASH(score_id)
12PARTITIONS 300;
13
14-- 日記テーブル
15CREATE TABLE diary (
16    diary_id INT NOT NULL,
17    user_id INT NOT NULL,
18    diary_title VARCHAR(100) NOT NULL,
19    diary_detail TEXT NOT NULL,
20    created_at DATETIME NOT NULL,
21    PRIMARY KEY(diary_id)
22)
23PARTITION BY HASH(user_id)
24PARTITIONS 300;

パーティション数は仮に300で区切って、ユーザーテーブル(アカウント数)は300レコードしか投入できないようにします。

MySQL
1-- ユーザーテーブル 300人までしか登録できない
2CREATE TABLE user (
3    user_id INT AUTO_INCREMENT NOT NULL,
4    mail_add VARCHAR(255) NOT NULL,
5    twitter_id VARCHAR(255) NOT NULL,
6    facebook_id VARCHAR(255) NOT NULL,
7    PRIMARY KEY(id)
8)

ユーザーは300に達するまで徐々に増えていき、各テーブルにもuser_idが1から300までを持つデータが徐々に増えていきます。
テーブルのレコード数は、少ないものは合計で数千レコード、多いものは合計で数十億レコードぐらいまで増加します。ただし、ユーザーの活動状況によって、どのテーブルにどのuser_idのレコードが多いかは、まちまちです。たとえば、pointテーブルでは、user_id=7のデータは700万レコードあり、user_id=45のデータは400レコードしかないという感じです。
テーブル数は100～200ぐらいあります。

すべてのテーブルをパーティションすることで、あるユーザーのでデータが大量に増えても、ほかのユーザーの影響がないようにするために、上記の設計を考えましたが、この発想は有効でしょうか？

長文すみませんが、ご確認をお願いします。

行動規範の内容に同意します

回答1件

ベストアンサー

まずPARTITIONSの理解を間違われているかと。。。 PARTITIONS 300ならパーティションが300個作られるだけで、ユーザーテーブルは300件を超えて登録可能です。(HASHの場合は、分割の方式がMOD(HASHに設定した値, パーティション数)の値によって対象のパーティションに格納されます）
もしも300件しか登録しないのであればidなりにprimaryキーをつけて、300件迄しか登録できない処理は別途アプリケーション側等で制御した方が良いかと思います。

追記
もし、ユーザー情報が300件までしか登録されないことが確約されているとすれば以下が良いかなと感じました。
userテーブルはidをprimaryにして管理する。
scoreテーブルとかはPARTITION BY KEY(user_id)のように指定しておけばユーザーidでパーティションが切られることになるので良いと思います。
また、ユーザーによって件数の振れ幅が大きいということもあるので、サブパーティションを切っておくと良いかもですね。

投稿2016/03/18 17:26

編集2016/03/18 17:41

fagai

総合スコア2158

SuguruShibata

2016/03/19 08:01

ご返信誠に有難うございます。はい、ユーザーテーブルに300行を超えるデータを入れられることは認識しています。運用で、300行入った段階でそれ以上データを入れないようにする予定でした。分かりづらくてすみません。お話を伺ったところ、有効という感じでしょうか。サブパーティションについても調べてみます。有難うございました。