毎週１冊技術書を読んでブログでアウトプットすることが目標で今回が第20弾です。
今回は、達人に学ぶDB設計 徹底指南書 を読みました。
DB設計の基礎知識やポイントを丁寧に解説している本です。
中でも、インデックスを作成するかどうかの指針が書かれているところは、実務で悩んだことがあったのでとても参考になりました。
一からアプリケーションを作る時以外でも、テーブルやカラム追加の際に為になる知識も多かったので、DB設計に自信がない方は是非読んでみて欲しいです。
- 第1章 データベースを制する者はシステムを制す
- 第2章 論理設計と物理設計
- 第3章 論理設計と正規化 〜なぜテーブルは分割する必要があるのか？
- 第4章 ER図 〜複数のテーブルの関係を表現する
- 第5章 論理設計とパフォーマンス 〜正規化の欠点と非正規化
- 第6章 データベースとパフォーマンス
- 第7章 論理設計のバッドノウハウ
- 第8章 論理設計のグレーノウハウ
- 第9章 一歩進んだ論理設計 〜SQLで木構造を扱う
- おわりに
第1章 データベースを制する者はシステムを制す
-
データとは、ある形式（フォーマット）に揃えられた事実のこと。情報とは、データをある文脈・観点に従って集約・加工されたものである
-
データベース設計が重要な理由
-
-
システムの大半のデータはデータベース内に保持されるため
-
-
-
データ設計がシステムの品質を最も大きく左右するため。どのようなプログラムが必要になるかは、どのようなデータをどういうフォーマットで保持するかに左右される
-
-
-
概念スキーマ：開発者から見たデータベースで、テーブル定義のこと（データの要素やデータ同士の関係）
-
第2章 論理設計と物理設計
-
論理設計は次の4つのステップからなる
-
-
エンティティ（現実世界に存在するデータの集合体）の抽出：エンティティをテーブルとする
-
-
-
エンティティの定義：テーブルにどのようなカラムを持つか定義する
-
正規化
-
ER図の作成
-
-
論理設計の結果を受けて、データを格納するための物理的な領域や格納方法を決める工程が物理設計である
-
物理設計には、大きく分類して次の5つのタスクが含まれる
-
-
テーブル定義
-
インデックス定義
-
ハードウェアのサイジング
-
ストレージの冗長構成決定
-
ファイルの物理配置決定
-
第3章 論理設計と正規化 〜なぜテーブルは分割する必要があるのか？
-
テーブル名は必ず複数形で書ける。そうでなければそのテーブルにはどこかに間違いがある
-
主キーは、テーブルに必ず一つだけ存在する。主キーとは、その値を指定すれば必ず1行のレコードを特定できる列の組み合わせ。主キーがテーブルに存在しなければならないので、重複行は存在し得ない
-
外部キーは、2つのテーブル間の列同士で設定するもので、参照整合性制約を課すことが役割である
-
NULLはSQL上で扱う際にいろいろな問題を引き起こすので、可能な限りデータはNULLにしないというのがデータベース設計における大方針。可能な限りNOT NULL制約を付加する
-
正規形とは、データベースで保持するデータの冗長性を排除し、一貫性と効率性を保持するためのデータ形式。正規形のレベルは第5まであるが、通常は第3正規形までで十分である
-
-
第1正規形は、一つのセルの中には一つの値しか含まないというもの
-
-
-
第2正規形は、テーブル内で部分関数従属（主キーの一部の列に対して従属する列の関係）を解消し、完全関数従属のみのテーブルを作ることである。第2正規化は、異なるレベルのエンティティをテーブルとして分離する作業と言い換えることもできる
-
-
-
第3正規化は、テーブル内での推移的関数従属（段階的な従属関係）を解消すること
-
-
正規形はいつでも非正規形に戻すことができる。つまり、高次の正規形は低次の正規形を含んでいる
-
正規化の欠点は、テーブルの数が増えるためにSQLで結合を多用することになり、パフォーマンスが悪化すること
第4章 ER図 〜複数のテーブルの関係を表現する
-
多数のテーブルを管理するために、それぞれのテーブルがどういう意味を持っていて、テーブル同士が互いにどういう関係にあるのかを明示するために作る図をER図（Entity-Relationship Diagram）と呼ぶ
-
ER図を描くときに最初に着目するポイントは、あるテーブルの主キーが、他のテーブルに列として含まれているかどうかである
第5章 論理設計とパフォーマンス 〜正規化の欠点と非正規化
第6章 データベースとパフォーマンス
-
インデックスを使うかどうかは、DBMSが自動的に判断する。したがって、インデックスを使う場合は単純にデータベース側にインデックスを作成すればよく、アプリケーションプログラムの変更の必要はない
-
最もよく使われるB-treeインデックスは、木構造でデータを保持する。最下層のリーフと呼ばれるノードだけが、実データに対するポインタを保持しており、データベースは最上位のノードから順にノードをたどって、リーフから実データを見つける
-
B-treeは、等号による検索のみならず、不等号やBETWEENといった範囲検索の条件に対しても高速化を可能とする。反対に、否定条件はB-treeが効果を持たない
-
ソートはかなりコストの高い演算であり、極力大きなソートを避けることがパフォーマンス上望ましい。COUNT、SUM、MAX、UNIONなどの処理でも暗黙にDBMS内部でソートが行われている。B-treeインデックスは、構築時にキー値をソートして保持するため、B-treeインデックスが存在する列をソートのキーとして指定した場合は、ソート処理をスキップすることが可能でパフォーマンスが向上する
-
B-treeインデックスを作る指針
-
-
大規模なテーブル：レコード数が1万件以下の場合はほぼ効果はない
-
-
-
カーディナリティの高い列：カーディナリティとは、特定の列の値がどのくらいの種類の多さを持つかという概念。例えば性別で、男性・女性・不詳が入る場合、カーディナリティは3となる。特定のキー値を指定した時に、全体のレコード数の5％程度に絞り込めるだけのカーディナリティがあることが目安となる
-
-
-
SQL文でWHERE句の選択条件、または結合条件に使用されている列に作成する
-
-
インデックスが利用できていないパターン
-
-
インデックス列に演算を行なっている：インデックスを作成した列はSQLでそのまま用いるのが原則
-
-
-
索引列に対してSUBSTRなどのSQL関数を適用している
-
-
-
IS NULL述語を使っている：B-treeインデックスは一般的にNULLについてはデータの値と見なさず、保持していない
-
-
-
否定形を用いている
-
-
-
ORを用いている：INで書き換えることでインデックスを用いることは可能
-
-
-
後方一致、または中間一致のLIKE述語を用いている：LIKE述語を使うときは、前方一致検索の場合のみインデックスが使用される
-
-
-
暗黙の型変換を行なっている：列とデータ型の異なる値を条件に指定した場合、DBMSは内部的に暗黙の型変換を行うが、その場合はインデックスは使用されなくなる
-
-
DBMSの主キー制約や一意制約を作成する際には、内部的にはB-treeインデックスを作成しているので、インデックスの作成は不要である
-
インデックスは一般的にテーブルとは独立のオブジェクトとしてDBMS内部に保持される。そのため、インデックスが作成されている対象の列の値が変更されると、インデックス内に保持している値も変更しなければならず、インデックスは更新性能を劣化させてしまう
-
インデックスは、テーブルのデータが更新されていくと、長期的には構造が崩れて性能が劣化してしまう。そのため、運用において定期的なメンテナンスを行うべきである。具体的には、インデックスの再構築を行うことが性能を維持するためには望ましい
-
統計情報はデータが大きく更新された後は、なるべく早く収集すべきである。統計情報を収集するのは、それなりにリソースを消費する処理であるので、基本的にはシステムの使用者が少ない夜間帯に実施する。また、統計情報の収集はDBMSによっては、デフォルトの設定で定期的に実施されていることもある
第7章 論理設計のバッドノウハウ
-
データベースには、意味的に分割できる限りなるべく分割して保持するのが基本方針。分割したものを後で結合するのは簡単にできるのに対し、結合された状態のものを後から分割するのは比較的難しいためである
-
バッドノウハウがよくないのは、システムの品質を左右する上、後から変更するのが容易ではないためである
第8章 論理設計のグレーノウハウ
-
子1、子2、子3のようなカラムを持つ列持ちテーブルは、NULLを使わざるを得なくなるので、極力使うべきではない。基本的には行持ちテーブルを使うべきである
第9章 一歩進んだ論理設計 〜SQLで木構造を扱う
-
リレーショナルデータベースでは、木(tree)構造を表現するのが苦手である
おわりに
ここまで読んでいただきありがとうございます。
具体例も多くとても分かりやすかったので気になる方は是非本書をご覧ください。
次は同じシリーズのSQLの本を読むつもりです。
来週も頑張ります！
