データベース設計について以下の条件に適したテーブル設計をアドバイスください。(PostgreSQL8.1)


店舗テーブル:店舗コードを主キーとした店舗情報テーブル(店舗所在地など20カラム)店舗件数は1万件です。
店舗ページを表示させる毎に読込みWEB表示させます。

商品テーブル:各店舗が所持する商品(店舗毎にオリジナル商品があり商品に重複はありません。)についての情報。各店舗が所持する商品は20~150です。商品にはサイズがあり在庫がリアルタイムに更新されます。それぞれの在庫がサイズカラムに商品データとともに保存されています。レコード件数は100万件です。商品毎のページも同じく100万件表示されます。

これらのテーブルから店舗毎の商品一覧(店舗WEBページにて表示)在庫がある商品の一覧(商品検索ページから検索、条件に商品の仕様や店舗の所在エリアなどを含むことができます)

商品の在庫情報は秒単位で更新されていきます。

現在上記の2テーブルにて構成しているのですが
より負荷の少ない、レスポンスの良い設計を求めています。
テーブルの構成も含めてPostgreSQLの機能を用いた
よりよい設計のアドバイスをお願いします。

回答の条件
  • 1人2回まで
  • 登録:2007/08/23 11:30:43
  • 終了:2007/08/30 11:35:03

回答(5件)

id:kn1967 No.1

kn1967回答回数2915ベストアンサー獲得回数3012007/08/23 14:17:21

ポイント20pt

(1)限界

PostgreSQLは追記型(秒単位の更新であればレコードが秒単位で増殖していく)のため

頻繁な更新を行うのは致命傷となる可能性があるという事はご存知だと思います。

  ↓

それを防ぐためには在庫の修正による変化を最小限に抑える努力が必要となりますよね。

  ↓

そのための第一歩としては

  店舗コード、商品コード、在庫数

の3点だけを持った在庫テーブルを別途用意するといった事が一番になるでしょう。

  ↓

次のステップとして在庫テーブルを1時間単位に新規作成します。

(PostgreSQLはテーブルをファイルの形でもっているため

DROPしてしまうほうがVACUUMよりも早くディスクスペースの空きを確保できます)

  ↓

1時間毎にプログラム側でアクセスするテーブルを動的に変化させれば良いでしょう。


(2)カバー

増殖したデータのダイエットとして利用者数の少なくなる時間帯(早朝など)に

VACUUMを実施する必要があることは既にご存知かと思います。

  ↓

VACUUMの実行はデータベース運用中にも行う事ができますが

完全なダイエットを行うためにはデータベースの運用を停止して

VACUUM FULL を実施するタイミングを用意したほうが良いでしょう。

  ↓

最初はどのくらいの時間がかかるかわからないので

運用開始時点では毎日早朝は1時間メンテナンスのため止まります。

といったような具体にしておいて、定期メンテナンスを行う事をお勧めします。

id:edge05

大変わかりやすく、理にかなったアドバイスをありがとうございます。在庫テーブルは用意するべきですね。VACUUM FULLについても調べてその必要性を感じました。大変ありがとうございます。

2007/08/23 15:44:07
id:Yota No.2

Yota回答回数453ベストアンサー獲得回数282007/08/23 15:16:00

ポイント20pt

正規化するなら、こんな感じだと思います。

1.店舗マスタ

店舗コード(主キー),店舗名,住所,電話番号

2.商品マスタ

商品コード(主キー),商品名

3.サイズ表

サイズコード(主キー),サイズ名

4.商品サイズ組み合わせ

商品コード(組合せ主キー,外部キー),サイズコード(組合せ主キー,外部キー)

5.在庫表

店舗コード(組合せ主キー,外部キー),商品コード(組合せ主キー,組合せ外部キー),サイズコード(組合せ主キー,組合せ外部キー),数量

しかし、レスポンスをあげるために非正規化したということでしょうか。

としたら、INDEXは適切に設定されていますでしょうか。

遅いクエリをEXPLAINで問い合わせ計画を見てみたらどうでしょう。

id:edge05

ありがとうございます。商品サイズに規格がなく実寸でデータ登録されています。他に色や素材などキーとできそうな項目もあります。色や素材も条件として検索させる予定です。正規化によりレスポンスの向上がどの程度見込めるのかも把握できていない状態です。INDEXの設定についてもまだ検討中の段階です。正規化の例が大変参考になりました。どうもありがとうございます。

2007/08/23 15:58:46
id:b-wind No.3

b-wind回答回数3344ベストアンサー獲得回数4402007/08/23 17:05:48

ポイント20pt

情報が少ないんで判断しにくいけど、それなりに練られた構成ならあまりテーブル構造は変えない方が良いと思う。

データの偏りにもよるが、そのバージョンであればパーティショニングを検討してみては?

【PostgreSQLウォッチ】第19回 ベータ・リリースを間近に控えたPostgreSQL 8.1:ITpro


あとは基本ですが、ちゃんと商品情報テーブルの店舗コードカラムに INDEX 作っておくとか。

id:edge05

パーティショニングなんていう手もあるのですか。ありがとうございます、じっくり読んで検討してみます。そうですね、商品情報テーブルの店舗コードにも必要ですね。1店舗あたりの商品数が結構多く更新頻度が高いので実際にそれなりのデータ件数用意してテストしてみないとだめですね。

2007/08/23 18:13:08
id:Yota No.4

Yota回答回数453ベストアンサー獲得回数282007/08/24 09:32:26

ポイント20pt

コメントのほうで、PHPで接続するという話が出たので「プリペアードステートメント」についても書いてみます。

クエリを実行する前に構文解析と実行計画作成を行うので、同じSELECT文でWHERE条件だけ変えるような処理を繰り返す場合は有効です。

PHP5で使えます。書き方はここにあります。

http://www.php.net/manual/ja/function.pg-prepare.php

ここの例で、$resultにSELECTの結果が入っています。

他のDBMSに対しても同じようなやり方があると思います。

id:edge05

知りませんでした^^;

ありがとうございます!

是非活用させていただきます。

これもパフォーマンスに大きく影響しそうですね。

2007/08/24 11:37:00
id:KUROX No.5

KUROX回答回数3542ベストアンサー獲得回数1402007/08/24 22:10:50

ポイント20pt

■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を使わないでプログラムでがんばると言う方法もあります。

id:edge05

ありがとうございます!

少し古い記事ですが面白く参考になりました。

VACUUM FULLについてのリンクもありがとうございます。

テーブルは都道府県ごとにわけることができます。

さらに細かいエリアごとにわかれます。

1万店舗中100店舗ほどは複数都道府県にまでまたがります。

検索条件に常に都道府県がはいるため、VIEWの機能は必要ないかもしれません。

ただデータの重複が増えます。

ありがとうございます。


追記

1店舗は1店舗1テーブルですね。

在庫テーブルも都道府県別にわけるべきですか?

2007/08/25 20:06:25
  • id:KUROX
    ORACLEなら
    http://biz.rivus.jp/words/rownum.html
    とかがあるので、
    検索した結果が10万件のうち表示分の20件を取得できる
    のですが、

    PostgreSQLはどうやって解決するのでしょうか?
  • id:edge05
    PostgreSQLではLIMIT,OFFSETなどで実現できると思います。
    ROWNUMにあたるのがLIMIT
    OFFSETでは20件目から30件目というような指定もできます。
  • id:KUROX
    どうもありがとうございます。勉強になりました。
  • id:kn1967
    (1)扱う商品が同じようなものばかりであれば
    商品コード化する事によって管理効率があがる場合が多いです。
    (2)扱う商品が多種多様に渡る場合であれば
    ”サイズ違い=別製品”,"色違い=別製品"と考えて
    商品コードを個別に与えるか、もしくは単純に枝番を与えるかしたほうが
    管理効率があがる場合が多いです。

    店舗数が1万という事から考えると(2)が当てはまると思いましたし
    PostgreSQLに特化したような話でも無いので正規化の話はしなかったのですが
    (1)が当てはまるのであれば正規化から考える必要があるでしょう。

    インデックスについては主キーと外部キーは当然として
    商品分類によって複数の店舗にまたがった検索をさせるのであれば
    商品分類コードのようなものも必要になったりするでしょうし
    もう少し検討の余地がありそうですね。

    >KUROX氏へ
    質問なら質問投稿すべきでしょ。
    他の人の質問へ書き込んで質問するようなためにコメント欄があるわけじゃないし
    多数回答している暇があるのだから、それくらい調べれば直でしょ

    もう少し考えてから行動しましょうよ。
  • id:Yota
    2 回答者です。
    商品ごとにバーコードが付いていると仮定すると、それを商品明細コードにして単品ごとに在庫を把握したらどうでしょう。
    商品明細テーブル
    商品明細コード(主キー),商品名,色,素材,サイズ
    在庫テーブル
    店舗コード(組合せ主キー,外部キー),商品明細コード(組合せ主キー,外部キー),数量
    あとは結合によっていかようにも検索できます。

  • id:Yota
    >PHPを用いての構築でデータベースはパフォーマンスに大きく開きが
    >あるのならMySQLでも検討したいです。Oracleが良いのでしょうがフ
    >リーのDBが条件です。
    OracleでもMySQLも同じくRDBの理論に基づいてつくられているので、まずテーブルの構造をクエリに対して最適化したほうがいいですよ。
    それとMySQLについては、トランザクションがなかった昔の評判が残っているだけのような気が。
  • id:edge05
    kn1967さんYotaさんわざわざありがとうございます!
    これほどのデータ件数のものを作ったことがなく正規化についても
    それほど意識して作ったことがないのです^^;
    自分のサイトなのでじっくり考えて進めていきたいと思います。

    商品について説明不足でした。すいません。
    バーコードもなければ規格もありません。
    商品情報についてはペットにたとえると一番近いと思います。
    全長・体重・年齢・色・種別・性別・お店からのコメントなどになります。
    サイトからは今存在するその地域の商品一覧という形になります。
    なので「在庫が在る商品+地域」といったページが存在します。
    商品は在庫数がすくなく今日はあるが明日は無いでも明後日にはあるといったことがよくあります。
    1つの商品が近隣地域をまたがることもあります。
    「在庫がある商品+地域」から種別・色など条件を
    プラスさせて検索をかけることができます。

    PHPを用いての構築でデータベースはパフォーマンスに大きく開きがあるのなら
    MySQLでも検討したいです。Oracleが良いのでしょうがフリーのDBが条件です。

    わざわざご回答ありがとうございました。
  • id:kn1967
    PostgreSQLという話だったので話題にはしませんでしたが
    MySQLを使うほうが対応しやすい案件かもしれません。
    phpでの構築という手間の点では、それぞれ専用の関数が用意されているので
    どちらでも大差ありませんがMySQLのほうがネットで検索しやすいという点はプラスになるでしょう。

    パフォーマンスについては、正規化などは言うに及ばず
    それぞれのデータベースにあわせたチューニングによって決まります。
    PostgreSQLは
    共有メモリの調整などを行う事でパフォーマンスは飛躍的に上がります。
    MySQLは最初からそこそこ高速ですが
    その反面弄る所が少なくてチューニングは出来ないに近いです。
    チューニングに関してはサーバー管理者権限が必要になりますので別の話になりますね。

    チューニングの問題もありますがアクセス件数の問題もありますので
    いずれのデータベースを選択する場合でも共用サーバーではなく
    専用サーバー(VPSではなく1台丸ごと占有)を用意するほうがよろしいでしょう。
  • id:KUROX
    参考にならないと思いますが、質問にも丁寧にお答えして
    いただいてるので、私の考察を書きます。

    DB一般的に見た場合、1万件、100万件は多い件数だとは
    今の時代としては思えない。LIMITとかを使えば、参照系は
    問題ないと思われるし、あとはインデックスキーの張り方
    とかで調整したほうが良いと思う。

    問題は、更新系の問題に絞られます。

    私は3回答者の
    「練られた構成ならあまりテーブル構造は変えない方が良いと思う」
    意見にほぼ同意。商品台帳の情報の関係が分からないですが直感。

    2テーブルから、3テーブルに構造をあまり変えずに変更。
    1テーブルは更新がかかるフィールドを集めたテーブル
    2テーブルは参照が主なテーブル。

    商品台帳を参照系と更新系に分けるイメージ。

    テーブルを更新系と参照系に分けることで、更新系に問題が
    あった場合でもアルゴリズムを変えるのがある程度容易いと
    思うから。
  • id:b-wind
    ざっと流れを追う限りでは、DB自体のチューニングなどはひとまず置いておいて、基本のテーブル設計の正規化・SQL, INDEX の最適化・適切なアルゴリズムの選択あたりを見直すほうが良いと思う。

    DBのチューニングは特定の要件に絞ればパフォーマンスに大きな影響が出る場合もあるけど、この程度の件数ではその必要性は感じない。

    DBの選択なんてその後でも十分。
  • id:KUROX
    ORACLEなら、間違いなく質問主の2テーブル構成でも
    全然問題ないし、適度には正規化されていると思います。
  • id:edge05
    Yota様ありがとうございます。
    確かに現状ではPostgerSQLに魅力を感じます。
    そうですよね、テーブル構造をもうちょっとつめて考えないと進めようがないですね^^;

    kn1967様またまたありがとうございます。
    サーバは専用で用意しようと考えております。
    適切なチューニングが行えるように知識を得ていこうと思います。
    色々な角度から示唆いただきありがとうございます。

    KROX様ありがとうございます。
    大変合理的に思えました。回答としていただいても十分にありがたい内容です。
    またコメントいただくようなことがあれば是非回答からお願いします。
    VACUUM FULLの間のサーバー停止なども考えるとOracleの魅力を感じますが
    資金に余裕はありません^^;

    b-wind様ありがとうございます。
    質問した内容が少し広すぎました^^;まずは設計というとですね
    設計ありきのINDEX・チューニングですしね^^;

    よくよく考えてみると、地域と商品のデータが多対多の構造なのですよね・・^^;
    テーブルもうひとつ必要ですね^^;;
    PostgreSQLにておいてもデータベース用のサーバを外部接続し、停止中はサーバーをもう一台用意しておいてそちらでVACUUM FILLの間運用する。
    などと考えましたがそういう運用もありですかね?^^;

    個人的にPostgreSQL8.3の新機能のHOTに期待しています。

    内容が不明瞭にもかかわらずたくさんのご回答とコメントをありがとうございました。
    自分が質問せぬまま、ことを進めていては大変なことになっていたと
    容易に想像できます。^^;
    何が重要でどこからかんがえて行けばよいのか、という私が知りたかったことを知ることができました。
    ほんとうにありがとうございます。
  • id:Yota
    >PostgreSQLにておいてもデータベース用のサーバを外部接続し、停止 >中はサーバーをもう一台用意しておいてそちらでVACUUM FILLの間運
    >用する。
    vacuumについても、もう少し研究されたほうがいいと思います。
    普通はスケジューリングで夜中にやる(FULLである必要があるかどうか)とか、autovacuumを使うとかではないかと思います。
    マニュアル参照。
    http://www.postgresql.jp/document/pg815doc/html/sql-vacuum.html
    http://www.postgresql.jp/document/pg815doc/html/maintenance.html#AUTOVACUUM
  • id:edge05
    Yota様色々気にかけていただいてありがとうございます。
    通常はFULLまで必要なさそうですね。。
    在庫情報の更新頻度が非常に高いです。
    日の更新件数はおそらく40万件を超えると思います。
    autoVACUUMのオーバーヘッドすら節約したいです。
    スケジューリングでアクセスの少ない時間に行うべきですね。

    FSMの設定が適切にでき、運用が安定するまで
    運用開始当初はVACUUM FULLも試してみたいと思います。
    ありがとうございました。
  • id:Yota
    書き忘れましたがANALYZEも忘れないでね。
  • id:edge05
    はい、ありがとうございます。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません