2016年10月15日土曜日

MySQL テーブル設計

MySQLというか、RDSでのDB設計・・・カラム(フィールド)の設計について

まず初心者が行ってしまうのは、1レコード(行)に全ての情報をつめこんでしまう設計。
後で一切いじることのない完結した物なら問題ありませんが、大抵は追加追加でどんどん膨れ上がってしまいます。

また、一行に詰め込むにはあまりにも無駄に膨大なカラム数になる場合もあります。
あまり増えると、1行のデータ上限にひっかかり、エラー発生という落とし穴もあります。
TEXT等の長いカラムは、1レコードあたり10カラムもあるといつエラーになってもおかしくありません。

一番やってほしくないカラム構成は

id, data1, data2, data3......

同じ意味のカラムがぽんぽんぽんと、何も考えないで作った感丸出しですね。
こういう場合は、重複しているカラムごとに1レコードにします。

id, key, data

1行ですんでいたのが複数行になり、行が大量になってしまうではないかと思うかもしれませんが、いいんです。
スピードもデータ容量も誤差の範囲です。 扱い方が変わるだけです。

キーバリュー(key-value)方式みたいな単純な構造にすることで、拡張性が飛躍的にあがります。
今までカラムを増やすたびにDB操作だったのが、その必要がなくなるり、動的にカラムを扱えるようになります。
扱い方次第では、行ロックである InnoDB の恩恵も強くうけられるようになります。


次に、どうしても1レコードに収めたい。しかしカラムは今後増えて行く可能性がるから柔軟にしたい。
これを解決できるのは、増えて行くカラムは検索対象にならないというのが大前提です。
id,data
dataの値は配列をシリアライズして圧縮をかけた物にします。
配列なので、自由自在にデータ項目を増やせます。
検索できないので出番は少ないですが、大きなシステムでは使うことがあります。


以上の3パーンが基本となり、それぞれのデータをどの方式で格納するかを決めます。
迷ったら2番目が後々楽ですが、最初のコーディングが少し多くて面倒かな。
実際は、マスターテーブルと関連付けて少し複雑になる場合が多いです。

具体例で言いますと、会員情報のテーブル構成では

member_id, pw
member_id, key, value
このように2つのテーブルにわけます。1つ目はマスターテーブルと呼ばれる物ですね。
マスターテーブルでは、ログイン時に必要な会員IDとパスワードのみを保存します。
会員情報本体のカラム構成が変化しないとわかっていれば、1行にまとめても良いです。

もちろんこれは絶対的な設計ではなく、要件によって使い分けてください。