店舗テーブル:店舗コードを主キーとした店舗情報テーブル(店舗所在地など20カラム)店舗件数は1万件です。
店舗ページを表示させる毎に読込みWEB表示させます。
商品テーブル:各店舗が所持する商品(店舗毎にオリジナル商品があり商品に重複はありません。)についての情報。各店舗が所持する商品は20~150です。商品にはサイズがあり在庫がリアルタイムに更新されます。それぞれの在庫がサイズカラムに商品データとともに保存されています。レコード件数は100万件です。商品毎のページも同じく100万件表示されます。
これらのテーブルから店舗毎の商品一覧(店舗WEBページにて表示)在庫がある商品の一覧(商品検索ページから検索、条件に商品の仕様や店舗の所在エリアなどを含むことができます)
商品の在庫情報は秒単位で更新されていきます。
現在上記の2テーブルにて構成しているのですが
より負荷の少ない、レスポンスの良い設計を求めています。
テーブルの構成も含めてPostgreSQLの機能を用いた
よりよい設計のアドバイスをお願いします。
(1)限界
PostgreSQLは追記型(秒単位の更新であればレコードが秒単位で増殖していく)のため
頻繁な更新を行うのは致命傷となる可能性があるという事はご存知だと思います。
↓
それを防ぐためには在庫の修正による変化を最小限に抑える努力が必要となりますよね。
↓
そのための第一歩としては
店舗コード、商品コード、在庫数
の3点だけを持った在庫テーブルを別途用意するといった事が一番になるでしょう。
↓
次のステップとして在庫テーブルを1時間単位に新規作成します。
(PostgreSQLはテーブルをファイルの形でもっているため
DROPしてしまうほうがVACUUMよりも早くディスクスペースの空きを確保できます)
↓
1時間毎にプログラム側でアクセスするテーブルを動的に変化させれば良いでしょう。
(2)カバー
増殖したデータのダイエットとして利用者数の少なくなる時間帯(早朝など)に
VACUUMを実施する必要があることは既にご存知かと思います。
↓
VACUUMの実行はデータベース運用中にも行う事ができますが
完全なダイエットを行うためにはデータベースの運用を停止して
VACUUM FULL を実施するタイミングを用意したほうが良いでしょう。
↓
最初はどのくらいの時間がかかるかわからないので
運用開始時点では毎日早朝は1時間メンテナンスのため止まります。
といったような具体にしておいて、定期メンテナンスを行う事をお勧めします。
正規化するなら、こんな感じだと思います。
1.店舗マスタ
店舗コード(主キー),店舗名,住所,電話番号
2.商品マスタ
商品コード(主キー),商品名
3.サイズ表
サイズコード(主キー),サイズ名
4.商品サイズ組み合わせ
商品コード(組合せ主キー,外部キー),サイズコード(組合せ主キー,外部キー)
5.在庫表
店舗コード(組合せ主キー,外部キー),商品コード(組合せ主キー,組合せ外部キー),サイズコード(組合せ主キー,組合せ外部キー),数量
しかし、レスポンスをあげるために非正規化したということでしょうか。
としたら、INDEXは適切に設定されていますでしょうか。
遅いクエリをEXPLAINで問い合わせ計画を見てみたらどうでしょう。
ありがとうございます。商品サイズに規格がなく実寸でデータ登録されています。他に色や素材などキーとできそうな項目もあります。色や素材も条件として検索させる予定です。正規化によりレスポンスの向上がどの程度見込めるのかも把握できていない状態です。INDEXの設定についてもまだ検討中の段階です。正規化の例が大変参考になりました。どうもありがとうございます。
情報が少ないんで判断しにくいけど、それなりに練られた構成ならあまりテーブル構造は変えない方が良いと思う。
データの偏りにもよるが、そのバージョンであればパーティショニングを検討してみては?
【PostgreSQLウォッチ】第19回 ベータ・リリースを間近に控えたPostgreSQL 8.1:ITpro
あとは基本ですが、ちゃんと商品情報テーブルの店舗コードカラムに INDEX 作っておくとか。
パーティショニングなんていう手もあるのですか。ありがとうございます、じっくり読んで検討してみます。そうですね、商品情報テーブルの店舗コードにも必要ですね。1店舗あたりの商品数が結構多く更新頻度が高いので実際にそれなりのデータ件数用意してテストしてみないとだめですね。
コメントのほうで、PHPで接続するという話が出たので「プリペアードステートメント」についても書いてみます。
クエリを実行する前に構文解析と実行計画作成を行うので、同じSELECT文でWHERE条件だけ変えるような処理を繰り返す場合は有効です。
PHP5で使えます。書き方はここにあります。
http://www.php.net/manual/ja/function.pg-prepare.php
ここの例で、$resultにSELECTの結果が入っています。
他のDBMSに対しても同じようなやり方があると思います。
知りませんでした^^;
ありがとうございます!
是非活用させていただきます。
これもパフォーマンスに大きく影響しそうですね。
■1テーブル1ファイル
http://www.atmarkit.co.jp/news/200408/26/oracle.html
■PostgreSQLのVACUUM FULLはいつ必要か。
http://blog.nomadscafe.jp/archives/000518.html
http://blog.jolt.jp/2006/06/postgresql_vacuum_full.html
-------------------------------------------------
>日の更新件数はおそらく40万件を超えると思います。
上記から考えたベタな方式。
昔からやられているベタな方式ですけど、
「テーブル名_店舗番号」と言うテーブル名で
商品(在庫?)テーブルは作る。
店舗件数は1万件なので設計的にはきれいじゃないとは
思います。
地域とかで10テーブルに分けることができるのなら、
100万件のデータは10万件となります。
VIEWを作って1テーブルに見せかければ参照系も
表面上は問題ないように思えますが、
うまくインデックスとか使ってくれない可能性が高いです。
VIEWを使わないでプログラムでがんばると言う方法もあります。
ありがとうございます!
少し古い記事ですが面白く参考になりました。
VACUUM FULLについてのリンクもありがとうございます。
テーブルは都道府県ごとにわけることができます。
さらに細かいエリアごとにわかれます。
1万店舗中100店舗ほどは複数都道府県にまでまたがります。
検索条件に常に都道府県がはいるため、VIEWの機能は必要ないかもしれません。
ただデータの重複が増えます。
ありがとうございます。
追記
1店舗は1店舗1テーブルですね。
在庫テーブルも都道府県別にわけるべきですか?
大変わかりやすく、理にかなったアドバイスをありがとうございます。在庫テーブルは用意するべきですね。VACUUM FULLについても調べてその必要性を感じました。大変ありがとうございます。