SQL設計の基本
概要
このページは、RDMS設計の基本的な流れにそって筆者が勉強した箇所について記載しています。
RDBMSの設計
RDBMSの設計をするにあたり、大まかに論理設計・物理設計のフェーズがあると思います。
論理設計では、RDBMSで扱うデータについて、どういったものを保存するのか定義を決める段階で物理設計では、論理設計で設計したデータ定義に基づいて、インデックスやどういったDBにデータを保存するのか決めます。
各フェーズをさらに詳細な流れを以下に記載します。
論理設計
- エンティティの抽出
- エンティティの定義
- 正規化
- ER図の作成
論理設計
エンティティの抽出
システムにどういったエンティティ（データ）が必要か考えるフェーズのこと。
エンティティを考える中で、システムの要件定義が見えてくることもあり、要件定義の段階で行うことが多いいかもしれません。
エンティティの定義
各エンティティがどのようなデータを保持するかを決めるフェーズです。
実際にテーブルを作成した際に、主キーや外部キーをどうするのか、複数の列を組み合わせて複合キーを作成するのか考えていきます。
また、複数テーブル間で外部キーを使用してデータを保存する場合は、親と子のデータをまとめて削除・更新するかなども考えます（カスケードという）
※外部キーが設定されている場合、データの削除は子から順に操作するのがいい。
※テーブルのデータは可能な限りNULLにしない
主キーや外部キー
主キーや外部キーは以下の内容について気をつけた方がいい。
- 主キーを設定する際は可変長文字列はやめておく。
こうした文字列は、同じ名前であっても微妙に異なる表記になることがあるため、ヒットするはずの文字列でキーが ヒットしなかったり、キーが重複しているように見えてしまったりすることがあるため。 - キーに使用する列は、必ず何らかのコードやIDといった表記体系のきっちり定まったデータを、固定長文字列のデータ型に格納するようにする。
制約
テーブルには登録・更新・削除を行う際に以下のような制約を課すことができる。
- NOT NULL制約
列に対してNULLを代入できないようにする - 一意制約
列の値で重複できないようにする - CHECK制約
列の取りうる範囲を制限する
（たとえば、「年齢」列についてなら、「20 ～ 65までの整数」や、「部署」列ならば「’開発’、’人事’、’営業’のいずれかの文字列」)
命名規則
名前に使える文字集合は、半角のアルファベット、半角の数字、アンダーバー（_）が使用できる。
またテーブル名は複数形で命名したほうがいいとされている。
(テーブルには複数のデータが保存されるため、当たり前といえば当たり前かも)
正規化
エンティティ（テーブル）について、システムでの利用がスムーズに行なえるよう整理することで、データの冗長性を排除して、一貫性と効率性を保持するためにテーブルを分割していく作業です。
正規化は第1 ～ 5正規化まで存在しますが、基本的には第3正規化まで行うことで多いい。また、正規化を行うことでデーブルを分割していくため、データを取り出す際にテーブル同士の結合を行うことになり、パフォーマンスが落ちてしまうことがある。（テーブルの結合は高負荷のため）
そのため、パフォーマンスのために、あえて第2正規化までしか行わいケースも存在する。この辺りはトレードオフだと思うので、実際の業務ではチームメンバーや上長に相談するといいと思う。
以下に第1正規化から第3正規化までのやり方と特徴を記載していく。
第1正規化
第1正規化の定義は、1つのセルの中には1つの値しか含まないとなっています。
（主キーが各列の値を一意に判定することができないため）
そのため、表のセルの中に複数の値が入っていた場合は、行を増やしたり、テーブルを追加する必要があります。
また主キーがわかれば、列が判定することができる関係を関数従属性と呼びます。
※今後図を作成予定
第2正規化
第2正規化では、部分関数従属を排除していきます。
部分関数従属とは、主キーの一部の列に対して従属する列がある場合指します。
※今後図を作成予定
これに対して、主キーを構成するすべての列に従属性がある場合を、完全関数従属と呼び、第2正規化は完全関数従属のテーブルを作成することを目指しています。
解決方法としては、部分関数充足の関係にある主キーと従属列だけ独立したテーブルにすれば解消することができます。
テーブルを独立させることで、誤ったデータの登録を防ぐことができます。
第3正規形
テーブル内に存在する段階的な従属関係、推移的関数従属をなくことを目的として実施されます。
※今後図を作成予定
解決方法
第2正規化の時と同様にテーブルを分割することで解消することができます。
正規化とパフォーマンス
正規化によってパフォーマンスが低下することは上記に記載しましたが、非正規化を行うことで検索のパフォーマンスを改善することができます。しかし、誤ったデータが登録・更新されてしまう危険性やデータが登録できないことがあるため、原則正規化は行った方がいいとされています。
非正規化以外のパフォーマンス向上の方法としては、パーティーションやインデックスなどが考えることができます。
ER図の作成
ER図は、Entity-Relationship Diagramの略で、正規化を行ったテーブル同士を図を用いて構成や関係性について記載していきます。
※今後図を作成予定
物理設計
論理設計の結果から、データを格納するための物理的な領域や格納方法を決める工程です。以下に物理設計の流れを記載します。
- テーブルの定義
- インデックスの定義
- データベースサーバーの選定
テーブル定義
論理設計で定義された概念スキーマをもとに、それをDBMS内部に格納するためのテーブル単位に変換していく作業のこと
インデックス定義
パフォーマンス向上を目的として、インデックス定義を作成する。
（主キーは元からインデックスが作成されているため不要）
データベースサーバーの定義
システムで使用するデータ容量の見積りからスペック(CPU、メモリ、ストレージ)を考えたりする。
精度の高いサイジングは難しいため、スペックは安全な領域まであげ、スケーラビリティの高い構成を組むことが大切。
番外編-バックアップ・リカバリ設計
システムのDBを設計する際にバックアップについても検討する機会があると思います。
以下に、バックアップ設計について記載していきます。
バックアップ設計を行う際は、以下の点を考慮する
・いつ時点の状態に復旧させる必要があるか。そもそも復旧の必要があるか
・バックアップに使用できる時間（バックアップウィンドウ）
・リカバリに使用できる時間（リカバリウィンドウ）
・何世代までのデータを残す必要があるか（保管用の媒体サイズに影響）
AWSではレプリケーションやクラスタリング構成を使用すれば、バックアップの問題は解消することができそう。
バックアップを検討した結果、バックアップが必要だった場合は次にバックアップの種類を検討する。
フルバックアップ
言葉どおり、ある時点で保存されているデータをすべてバックアップする方法。
ずべてのデータのバックアップをとる為、バックアップに時間がかかりリソースへの負担も大きい。
差分バックアップ
特定の日にフルバックアップを取得し、その日以降は差分のみバックアップする方法
リカバリする際に、フルバックアップしたファイルが使えなくなった場合、バックアップをすることができない。
増分バックアップ
特定の日にフルバックアップを取得し、その日以降は、その日の変更分のみバックアップする方法。
リカバリ設計
バックアップ設計とリカバリ設計はセットで実施することが一般的なため、バックアップ方式が決まれば、自動的に決まってくることが多いいと思います。
リカバリ設計が完了後は、手順を確認することを忘れないようすることが大切です。
SQLのパフォーマンス
DBのパフォーマンスはSQLのチューニングもですが、設計においても重要なポイントが2つ存在します。
・インデックス
・統計情報
インデックス
プログラミングの表現で（x,a）という形式の配列のことで、xはキー値、aはデータ or データのポインタを意味します。xを入力すればaのデータがわかるといった具合ですね。
特徴
- インデックスを作成したからといって、アプリケーションのコードに影響を与えません
- テーブルのデータに影響を与えない。
- それでいて性能改善の効果が大きい!。
種類
DBMSによって使用できるインデックスに種類が存在しますが、頻繁に使用されるのは1種類しか存在しません。以下の他にもビットマップインデックス、ハッシュインデックスなどがありますが、実際に使うことはまれみたいです。
B-tree
B-treeは非常にパフォーマンスがいいわけではなく幅広い対応範囲から多くの開発者の間で使われているみたいです。
B-treeの構造の図
特徴
B-treeは、等号（=）による検索のみならず、不等号（<、>、<=、>=）やBET WEENといった範囲検索の条件に対しても、高速化を可能とします。
またB-treeインデックスを構築した際は、キー値をソートして保持します。
実は以下のSQLを実施する場合も暗黙的にソート処理が行わています。
集約関数（COUNT、SUM、AVG、MAX、MIN）
ORDER BY句 集合演算（UNION、INTERSECT、EXCEPT） OLAP関数（RANK、ROW_NUMBERなど）
ソート処理はかなりコストの高い演算ですので、極力大きなソート処理は避けた方がいいとされています。
ですが、B-treeインデックスが存在する列でソートする場合、既にソート処理がされているためスキップすることが可能になります。これは、ソート処理をチューニングする大きな助けになります!!
B-treeインデクスの設計方針
B-treeインデックスを設計する際には、以下3つの指針を元に設計を行うようにした方がいいとされています。
・大規模なテーブルに対して作成する（1万レコード以上）
データ数が少ないとインデックスではなくフルスキャンの方が早くなることがあるため。
・カーディナリティの高い列に作成する
カーディナリティとは、列にどれぐらいの種類の多さを持つのかを表す概念です。
全体のレコード数の5%程度に絞り込めるだけの数があれば、インデックスを作成する必要があると判断することができます。
※複合列に対しては複合列の組わせて考える。また組み合わせが多いい方を先頭にすることで効率的になる。
・SQL文でWHERE句の選択条件、または結合条件に使用されている列に作成する。
※選択条件や結合条件にインデックスを作成する場合、インデックス列に対して以下の内容を気をつける。
・演算を行わないこと
・SQL関数を適用していないこと
・IS NULL述語を使っていないこと
・否定形を持ちていないこと（<>）
・ORを用いないこと。（INに書きかえることで回避できる）
・後方一致・中間一致のLIKE述語を用いていること（前方一致の場合のみインデックスが使用される）
・暗黙の型変換を行っていないこと（明示的に条件に使用する値のデータ型を列のデータ型に合わせてやること）
・その他注意事項
インデックスは検索機能を向上させることができるが、更新性能は劣化させてしまう。
定期的なメンテナンスを行い、インデックスの再構築を行うことが性能を維持する為には推奨されているようです
統計情報
統計情報とはテーブルやインデックスなど「データ」についてのデータ、「メタデータ」のことをいいます。
DBはこのメタデータを頼りにSQLのアクセスパスを決定しています。
※今後図を作成予定
統計情報の収集タイミング
データが大きく更新された後、なるべく早く実施した方がいいとされています。
「更新」には、INSERT ／ UPDATE ／ DELETEのいずれも該当します。
デフォルトだとテーブルの10%の更新があった場合に、自動的に更新されるようです。
アンチパターン
※今後記載予定
参考資料
- 達人に学ぶSQL徹底指南書 第2版 初級者で終わりたくないあなたへ
まとめ
テーブル設計を行う際は以下の流れで実施する。
また実施後はチェックを行うことで、作業漏れを防止できると思います。
各項目で気をつけるべき点があるので、各項目の内容を確認した上で実施すること。
またアンチパターンがあるので、アンチパターンに該当しないように設計を行うこと
- エンティティの抽出
- エンティティの定義
- 正規化
- ER図の作成
- テーブルの定義
- インデックスの定義
- データベースサーバーの定義(運用で問題なければ初回構築のみ)
Discussion