達人に学ぶDB設計 徹底指南書 初級者で終わりたくないあなたへ
- 作者: ミック
- 出版社/メーカー: 翔泳社
- 発売日: 2012/03/16
- メディア: 単行本（ソフトカバー）
- 購入: 21人 クリック: 316回
- この商品を含むブログ (24件) を見る
DB設計をする上で必要な知識が詰まっている本だった。もっと早く読んでおくべきだった。マストです。
唯一の正解などなく、整合性・パフォーマンスのトレードオフとコストの問題を考えて適切な選択をすること、それこそがDBエンジニアの腕の見せ所であるということを学んだ。
以下、まとめ。
DOA データ志向アプローチ
設計を制する者はシステム開発を制す。それはシステムがデータのフォーマットに合わせて作られるから（システムに合わせてデータを作るのではない）。
データベースデータ設計がシステムの品質を最も大きく左右する。ソフトウェアというのは、言ってみれば「データの流通機構」であって、どのようなプログラムが必要になるかは、どのようなデータをどういうフォーマットで設計するかに左右される。
データベースを制する者がシステムを制す。データベースは、システムの中心であると同時に、システム開発の中心でもある。
システム開発の素人に、予備知識を何も与えないままで開発を行なわせると、ほぼ十中八九がプログラムから設計を始めます。
3層スキーマについて復習
スキーマ=データ構造やフォーマット
外部スキーマ（外部モデル）＝ビューの世界
概念スキーマ（論理データモデル）＝テーブルの世界
内部スキーマ（物理データモデル）＝ファイルの世界
論理設計のステップ
エンティティの抽出→エンティティの定義→正規化→ER図の作成
エンティティの抽出
エンティティとは現実世界に存在するデータの集合体を現す言葉。日本語だと「実体」を意味するが物理的実体を伴っているとは限らない。「社員」や「注文履歴」など。
システムのためにどのようなエンティティが必要になるのか、どのようなデータを扱う必要があるのかを考える作業。要件定義と重なる。エンティティはデータを「属性(attribute)」という形で保持する。
内部スキーマ
テーブルやインデックスの物理定義。物理設計。
物理設計のステップ
テーブル定義→インデックス定義→ハードのサイジング→ストレージの冗長構成の決定→ファイルの物理配置決定
サイジング全般
データベースにおいては、データの整合性とパフォーマンスの間に強いトレードオフが存在します。つまり、整合性を高くしようとするとパフォーマンスが犠牲になり、パフォーマンスを追求すると整合性を犠牲にする、という二律背反の原則です。データベース設計とは、限られた予算制約の中で、両端の間の平衡点を見つけ出そうとする努力だと言っていいでしょう。
サイジングはパフォーマンスを決定する。性能問題のほとんどはストレージのIOネックから引き起こされる。
ディスクの見積もり
データベース内に格納するデータ量は物理的なテーブル定義とインデックス定義が終わらなければ計算できない。
論理設計と正規化
テーブル名の命名規則
アルファベット+数字+アンダーバー
ハイフンは使ってはいけない
またテーブル名の先頭を数字から始めてはいけない
正規化とはなにか
正規形とはデータの冗長性を排除し一貫性と効率性を保持するためのデータ形式。
冗長性や一貫性がないと、同じデータが複数のテーブルに存在して無駄なデータと更新処理が発生する、データの整合性が取れないなどの問題が発生する。
こうした更新時の不整合の問題を解決するために正規化を行う。
第一正規化
繰り返し要素の排除。主キーによって一意にデータを特定できるようにする。
・関数従属性とは
入力Xに対して出力Yを一つに決めるもの。YはXに従属する。
Xの値が決まるとYの値が一意に決まる
デジタル大辞泉の解説
かん‐すう〔クワン‐｜カン‐〕【関数／×函数】
１ 《function》二つの変数x、yがあって、xの値が決まると、それに対応してyの値が一つ決まるとき、yはxの関数であるという。
https://kotobank.jp/word/%E9%96%A2%E6%95%B0-2523
第2正規化
主キーの一部の列に対して従属する列がある部分従属関数の排除
第3正規化
主キー以外の列の推移的関数従属の排除
[SQL] 16. データベースの設計 2 | TECHSCORE(テックスコア)
I-22-10. データベース正規化の具体的な考え方と手順 | 日本OSS推進フォーラム
基本的に第3正規化まで行うべき。パフォーマンスなどの問題があるなら非正規化する。
正規化によってテーブルが分割され整合性が担保されるが、テーブルの結合が行われることによってパフォーマンスが下がる。
サマリデータを冗長に保持すると正規形に違反するが検索を高速化できる。
「非正規化」はあくまでも最後の手段であるという姿勢でのぞむ、というものだ。要するに、十分に正規化された設計をあきらめてもよいのは、パフォーマンスを向上させるためのその他すべての戦略が要件を満たさない場合だけである。
I-22-10. データベース正規化の具体的な考え方と手順 | 日本OSS推進フォーラムより引用
非正規化の状態から正規化するときでも同じなのですが、ともあれ、論理設計をする際には「システムの品質は（ひいては開発が成功するかどうかは）今ここで決まる！」という気概を持って臨む必要があります。かつ、論理設計を担当する人間は、正規形の理論を理解しているだけでなく、それによって生じる様々なトレードオフを知り尽くしたうえで、あらゆる要件を同時に満たせる平衡点を探し出せる能力が必要とされるのです。
データモデルの後からの変更は影響範囲が大きい・・・。
インデックス
B-treeインデックスはO(logn)
フルスキャンは O(n)
ソートはコストが高い
ソートというのは、かなりコストの高い演算です。ソートはDBMS内部で専用のメモリ領域が割り当てられており、その内部に一時的にデータを保持して実施されますが、大量データのソートが必要な場合、メモリに載りきらないためにあふれてしまうことがあります。その場合、DBMSは一時的にディスクへデータを書き出します。この場合のI/Oコストが非常に大きなものになるのです。したがって、SQL文を記述する際は、極力大きなソートを避けることがパフォーマンス上は望ましい、ということになります。
インデックスをつけるときは1.データ量、2.カーディナリティ、3.選択条件・結合条件に使用されるカラムを考慮する。
- データ量の目安は1万件。
- カーディナリティの目安は全体のレコード数の5%に絞り込めるだけのカーディナリティがあるかどうか。つまり値がうまく平均的に分散していれば効果が高い。
- インデックス列に以下の適用をしていないこと
- 演算していないこと
- SQL関数を使用していないこと
- IS NULLを使用している
IS NULLはインデックスを使うのか - istyle Tech Blog
MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.2.1.8 IS NULL の最適化
- 否定形を使用している
- ORを使用している
- 後方一致・中間一致のLIKE演算子を使用している
- 暗黙の型変換を行っている
- インデックスの順番が適切であること
B-treeインデックスは更新していくとバランスが崩れて性能が劣化する。
第6回 パフォーマンスの基礎である索引について
より高速なデータアクセスのために最適なインデックスを選択して作成する - ベストプラクティス| Alibaba Cloud ドキュメントセンター
アンチパターン
情報は可能な限り分割したほうが良い
分割されたものを結合することはできるが結合されたものを分割することは難しい。例えば名前を保存する場合姓カラムと名カラムで分割する。
プログラミングおよびシステム開発という仕事全体が、データによって規定されるからです。下手くそなデータ構造に対して、エレガントなプログラミングを行なうことは不可能なのです。データ構造がダメな状態で、プログラミングによって挽回することはできません。システム開発において最も重要なのは、コーディングではなくデータ設計なのです。
代理キー問題
一般的な原則としては、極力代理キーの使用は避けて、自然キーによる解決を図るべきです。その主な理由は、代理キーがそもそも論理的には不要なキーのため、論理モデルをわかりにくくしてしまうからです。
ジョー・セルコは「オートナンバーを主キーに使うことは、データモデルを欠いている証拠だ」と、自然キー以外を主キーに使うことを厳しく批判しています。
フレームワークのmigrationを使うと自動でidがつくので、int型のAUTO INCREMENTのidカラムを主キーにしたテーブルを多用していたのだがこれはよくなかったのだろうか。複合主キーにするべきだったのか。トレードオフの問題なのか。
漢(オトコ)のコンピュータ道: ナチュラルキーとサロゲートキーについての議論
【DB】 サロゲートキー（代理キー）の初歩
