MySQL は、システム上必要な場合以外は使っていない。
MySQL が嫌いなわけではなく、
「何かみんな MySQL だなぁ…俺も MySQLer になろうかなぁ。」
と思ったことは数知れず。
でも、PostgreSQL にしかない機能の恩恵に、度々、与ってしまっているので、「乗り換え」には至らず。
それにしても、いろいろな RDBMS があるものだ ( RDBMS比較資料[PDFです] )。
各 RDBMS の特徴は、DB の設計や、チューニングの際に、顕著になるのではないだろうか?
「管理しやすくて、検索が速くて、更新もサクサクできる」
そんな DB はどうしたら実現できるだろうか…と考えていくと、自然と実装の話になっていく。
(もちろん DB を抽象化してポータブルに作っておく事が要求される場合は別。)
ということで、前置きが長くなったが、 PostgreSQL でいろいろ試したメモ。
データのセグメンテーション
PostgreSQL のデータは、cluster ⊃ database ⊃ schema ⊃ tableという階層の最下層であるところの 'table' に保存されるが、
これらの各階層で権限の設定ができる。
「複数ユーザ(100 アカウント程度)を想定した場合、どの階層で分断するのが現実的か?」
というのを、改めてマジメに考えてみた。
以下、 アカウント [user] のデータ識別子を [user] とする。
-
table :
=> create table [user]_table(...); => grant all on [user]_table to [user];
却下
(テーブルの一覧がすごい事になるため)。
-
schema :
$ createuser -P [user]; => /* public は PUBLIC に grant されてるので消す。*/ => drop schema public; => create schema [user] authorization [user];
却下
(
テーブルの一覧には [user] schema に属するテーブルしか現れないが、
スーパーユーザにも見えにくくなるため。
)
-
database :
$ createdb [user]; $ createuser -P [user];
採用
(もっとも現実的)
-
cluster :
$ initdb -D /path/to/[user];
で、ポートを切替えて運用。
…却下でしょう。
当たり前の結論に至り、なんか安心。
選択型データの保存形式
例えば、「
好きな果物はなんですか?
A. りんご
B. みかん
C. いちご
...
P. いちじく
」
という問いに対する回答を保存したい場合、
- RAW: 'りんご'とか'いちご'という内容を保存すべきか?
- CODE: 'A'とか'C'というコードを保存すべきか?
◆RAW に対する CODE の strong / weak points :
[管理系]
Ｏ コードと内容との対応を外部で一元管理できるので修正が容易。
Ｘ コードと内容との対応表が無いとデータとして成立しない。
[検索系]
Ｏ データが簡潔に表せるのでちょっと速いかも。
Ｘ 特になし。
[更新系]
Ｏ 特になし。
Ｘ 特になし。
管理系の視点で CODE のほうが有利と判断する。
これも「正規化」に通じる当たり前の結論。
ただし、コードと内容との対応表(マスターテーブル) の死守が前提。
データ構造の保存
例えば、
members =
[
{
'name': '太郎',
'gender': '男'
'fruits': [1, 3]
},
{
'name': '花子',
'gender': '女'
'fruits': [2, 3, 11]
}
];
というデータ構造を保存したい場合、
-
NORMAL:
データの値 1 つをフィールド 1 つに( 1 対 1 に)保存するべきか?
上の例の場合、正規化後のテーブルは 3 つか 4 つになるはず。
-
BULK:
上の例(意図的に JSON で記述してある)のように、
「データのラベルや値を含む構造そのものを適切に表現する文字列」
として保存するべきか?
PHPコードそのもの( array('name'=>'太郎',...) )、
それを serialize(); したもの、あるいは、XML などを想定している。
また、少し毛色が違うが、PostgreSQL の配列型データも、BULK の一種として考える。
◆NORMAL に対する BULK の strong / weak points :
[管理系]
Ｏ データ構造とテーブル定義の結合度が低いので、より汎用性がある。
Ｘ データを直接操作しにくい ?
=> PostgreSQL の配列型では問題無し。
[検索系]
Ｏ 主テーブルを検索するだけで、該当するデータ構造を取得できる。
Ｘ 直積を作りにくい ?
=> PostgreSQL の配列型では問題無し。
[更新系]
Ｏ 主テーブルを更新するだけで、該当するデータ構造を更新できる。
Ｘ データ構造の部分的な更新がしにくい ?
=> PostgreSQL の配列型では問題無し。
総合的に視て、BULK というか「 PostgreSQL の配列型」のほうが有利と判断する。
以下、具体例
- NORMAL の場合:
create table fruit ( fruit_id serial, name text ); create table member ( member_id serial, name text, gender text ); create table fruit_to_member ( fruit_to_member_id serial, member_id integer, fruit_id integer ); insert into fruit(name) values('りんご'); insert into fruit(name) values('みかん'); insert into fruit(name) values('いちご'); insert into member(name,gender) values('太郎','男'); insert into member(name,gender) values('花子','女'); -- make relations; insert into fruit_to_member(member_id,fruit_id) values(1,1); insert into fruit_to_member(member_id,fruit_id) values(1,3); -- make another relations; insert into fruit_to_member(member_id,fruit_id) values(2,2); insert into fruit_to_member(member_id,fruit_id) values(2,3); select * from member,fruit where fruit.fruit_id in ( select fruit_id from fruit_to_member where fruit_to_member.member_id = member.member_id ); member_id | name | gender | fruit_id | name -----------+------+--------+----------+-------- 1 | 太郎 | 男 | 1 | りんご 1 | 太郎 | 男 | 3 | いちご 2 | 花子 | 女 | 2 | みかん 2 | 花子 | 女 | 3 | いちご (4 rows) Time: 0.784 ms - BULK の場合:
create table fruit ( fruit_id serial, name text ); create table member ( member_id serial, name text, gender text, fruit smallint[] ); insert into fruit(name) values('りんご'); insert into fruit(name) values('みかん'); insert into fruit(name) values('いちご'); -- make record, with ARRAY; insert into member(name,gender,fruit) values('太郎','男','{1,3}'); -- make another record, with ARRAY; insert into member(name,gender,fruit) values('花子','女','{2,3}'); /* * where 2 = ANY(some_array) * (古い記法: where some_array *= 2) */ select * from member,fruit where fruit.fruit_id = ANY(member.fruit) order by member_id; member_id | name | gender | fruit | fruit_id | name -----------+------+--------+-------+----------+-------- 1 | 太郎 | 男 | {1,3} | 1 | りんご 1 | 太郎 | 男 | {1,3} | 3 | いちご 2 | 花子 | 女 | {2,3} | 2 | みかん 2 | 花子 | 女 | {2,3} | 3 | いちご (4 rows) Time: 0.506 ms
