自分がテーブル設計をするときによく気をつけることを箇条書きにしました。
--
業務フローを正確に理解・把握する
業務フローの理解が甘い場合、客先検収時に項目の不備が発見されたり、
フロー成立に不可欠な要素の見落としが生じることがあります。
データベース設計は業務フローと密接に関係しているため、業務フローを正確に把握するよう
努める必要があります。
--
保存が必要なデータ項目は漏らさず事前に収集しておく
--
基本は正規化を行う
正規化がすべてではない場合もあります。
書き込み頻度が高いテーブルは敢えて非正規化したほうがいい場合もあるため、
書き込みパフォーマンスを確認の上、正規化・非正規化の選択を行うようにします。
でも基本は正規化したほうがいい場合が圧倒的多数です。
--
集計済みデータを分解できない構造にしない
集計済みテーブルしかなく、集計前データが存在しないようなテーブル設計はできる
限り行わないようにします。
万一集計ロジックに不具合が発覚した場合、集計前データがあれば再集計で対応できますが
データ量節約のために集計前データを破棄する設計にしてしまうと、過去の不具合を訂正できない
システムになり、扱う業務によっては本格的に致命的な不具合になる場合がありますので、
リスクを検討し、集計元データを残さない場合、その根拠とリスク対策を明文化しておくと安心です。
--
並び順を制御する列を用意しておく
マスタのときは特にですが、例えば販売管理などの見積書の明細格納テーブルに
順序制御を入れていなくて後で明細の順序を変えたいと言われることが過去にあったため、
予想がされる場合はソート列の検討をいれています。
--
運用で使われるSQLのたたき台はできるだけ自分で書き起こしておく
適切なインデックスを設計するためには論理的なリレーション設計だけでなく、
実際にSQLを書き出してきちんとクエリプランを把握しておく必要があります。
できる限り自分で書いておいたほうがいいです。
--
言うまでもなく適切にインデックスを貼る
応答性能に極端な影響がでますのでここはきちんと検討します。
事前にSQLを書き起こしていればある程度の目安は立ちますので、比較的容易な作業になります。
--
ディスク容量はインデックスファイルの量もちゃんと含めて検討する
当たり前なんですが、運用がすすんで特定のテーブルに1000万件以上データが格納されていて、
止むに止まれずインデックスを貼ったらディスク容量が急増し、運用計画に支障がでてし まった、
という経験がありました。データ件数が非常に大きくなるテーブルが複数あるときは
特にインデックスファイルの容量も注意しておいたほうがいいです。
--
件数が非常に大きくなるテーブルは事前にパーティショニングテーブルにしておく
日別・会員別・訪問IP別サマリーを格納し、データを表示したい。
なんて要望で、会員数が万単位であることがわかっている場合、
数年以上利用すると数百万件になることが容易に想定されます。
そういった場合、データ件数が原因でSELECTパフォーマンスが落ちますので、
月別にパーティショニングを貼っておくとパフォーマンスを安定させることができます。
また、数百万件のテーブルで2年以上前のデータを一括削除したいとか
要件がでたとき削除だけでかなりの時間ロックが発生するため、
夜間や休日メンテを余儀されなくなりますが、
パーティションテーブルであればDROPかTRUNCATEするだけですので
DELETEよりは圧倒的に早く終わりますので、オンタイムでの対応も可能になります。
--
書き込みが頻繁なテーブルはパーティション設計を行い、運用後はできるだけそっとしておけるように慎重に設計する
大量のDELETE / SELECT / UPDATE / ALTER、リアルタイム集計情報を提供するなどが必要と
されるのであれば、パーティションは必須です。
応答時間が数秒のクエリを書き込み以外で発生させると、
対象SQLが書き込み要求を待ち行列に追加し、一瞬にしてパフォーマンスが急低下し、
DBが応答不能になる経験がありました。
書き込みが大量に発生するテーブルでリアルタイム集計を運用しないよう、要件を調整する
リアルタイム集計は別テーブルに書き出しを行って常に件数が少ないテーブルで扱うなど
結構な工夫をしないといけません。
--
パーティショニングは銀の弾丸ではない
パーティショニングはすべての問題を解決してくれるわけではなく、
ノーメンテでテーブルが100以上になるとパフォーマンス劣化につながりもする。
有用な対策にはなり得るけれど、特性と事前の調査をきちんと行った上で採用する。
同じテーブルに書き込みと読み込みが同時に走る場合は、設計確定前に必ず負荷テストを行う
先に記述した通り、書き込みが高頻度に走っているテーブルの最新行を頻繁に取得するような
テーブルが存在する場合は軽いアクセス過多であっという間に遅延につながる場合がある。
なので、簡単なスクリプトで実際の頻度を想定した書き込みを連続して行うスクリプトと
最新レコードを含む検索クエリを同時に流し、負荷テストを行っておき、
実運用上で障害に繋がらないか簡易にでも確認をしておく必要がある。
--
番外編 (物理サーバの場合)
特に件数が大きく、読み込みも同時に発生するテーブルはテーブルスペースを割り当て、独立したディスクを割り当てる
RAIDに任せておけばいいんじゃない？
っていうのもアリですが、ディスクを分けると他のテーブルのデータ更新や検索の影響を
受けなくなり安定しますので、物理を扱える場合はディスクの割り当て設計も有用なアプローチです。
ただ、バックアップからの復元の際にディスク割り当ての情報が伝わっていなかったりすると
クリティカルな不具合が発生したりするので気をつけてください。
--
追記 2017.12
ディスクスペースが全体の50%程度に収まるような運用を行う。
全体のディスク容量が大きなサーバでデータベース運用をしていて、
定期的にフルバックアップを行う運用をしている場合、
圧縮に必要なディスクスペースやテンポラリで使うディスクスペースが想定以上に膨れてしまい、運用に支障をきたす場合があります。
80%を超えるディスク使用量が常態化しているサーバだと、
何かと不調に見舞われることがあります。
顕著な例だと、ディスク負荷が高い時間が普段以上に続き、フロントエンドのパフォーマンスが急激に落ちたり、応答待ちが増えることでコネクション数が一時的に増加して、知らない間に接続がきられていたり。
頻度が一定でない挙動不安定に近いことが起きることがあります。
ディスク容量の監視は運用継続することをお勧めします。