達人に学ぶDB設計指南書読書メモ
概要
『達人に学ぶDB設計 徹底指南書』の読書メモです。
学生インターンしていた頃に一度オススメされて読んだのですが、だいぶ時間が空いてしまったので、基本に帰るためにもう一度読んでみました。
アウトプットとして、業務でのDB設計などに活かすことを目標として、重要だと思ったことや、新しい気づきなどをまとめておきます。
1. 概論
データベース設計が重要な理由として
- プロセスであるソフトウェアは変化するのに対して、データは永続的であること
- どのようなプログラムが必要になるかは、データがどういうフォーマットで保存されているかに依存するということ
の二つが挙げられていました。 またデータは大きく分けて、三層に分けれており、
- 外部スキーマ = Viewの世界
- 概念スキーマ = テーブルの世界であり、この設計のことを論理設計という(正規化など)
- 内部スキーマ = ファイルの世界であり、この設計のことを物理設計という(インデックスの定義など)
そもそもの前提として、概念スキーマが必要な理由として
概念スキーマがないと、ユーザーからの見え方を変更したい場合に、内部スキーマを変更する必要が生じ、変更に対する柔軟性がなくなってしまう
ということが挙げられていました。
少し本筋とはずれますが、このようにある概念の存在意義がわからないときは、「それがないとどうなるか？」という問いについて考えてみると、その有用性が明らかになります。
2. 物理設計
データベースにおいては、データの整合性とパフォーマンスの間に強いトレードオフが存在する
このようなトレードオフの中で、この二者の平衡点を見つけていくことがデータベース設計と言えます。
具体的ない物理設計のステップ:
- テーブル定義
- インデックス定義
- ハードウェアのサイジング
- どれだけ速いか、どれだけ多いか という要件を元に決定
- ストレージの冗長構成決定
- RIAD: 複数の独立したディスクを用いることによ理、信頼性と性能を向上させる技術
- ファイルの物理配置決定
- データファイル、インデックスファイル、システムファイル、一時ファイル、ログファイル
- 理想としては、ファイルのI/Oを分散させるために、異なるディスクに配置するのが良い
3. 論理設計と正規化
そもそもテーブルとは、共通点を持つレコードの集合であると定義されている。 ゆえに、
テーブルは同じ種類のものの集合であり、テーブル名は全て複数形または複数名詞で書ける by ジョー・セルコ
と言える。
3.3 正規化
正規化はデータの冗長性をなくしていく作業であり、更新時のデータの不整合を防止することを目的としている
また正規化を進めるほど、データの整合性は向上していくが、検索性能が劣化していくため、通常は第三正規化まで行えばOK
5. 論理設計とパフォーマンス〜正規化の欠点と非正規化
正規化の最大の欠点は、SQLパフォーマンスの劣化です。 その対処方法としては、
- SQLに対するチューニング
- 非正規化
の二つが考えられますが、著者のミックさんは
原則としては、非正規化は許さない
という立場を取っています。
基本的に正規化はテーブルを分割することであり、それゆえにデータアクセス時には、テーブル同士の結合(JOIN)が必要になるため、パフォーマンスの劣化につながりやすくなります。 もちろん結合する際に、なるべく絞り込んでから結合するなどすれば比較的パフォーマンスの劣化を防ぐことはできます。
5.2 非正規化とパフォーマンス
- サマリーデータの冗長性 正規化を崩し、サマリーデータをカラムとして持つことでパフォーマンスの向上は見込めるが、更新において問題が発生しうるというケース。
自分の場合、このような集計が必要な場合は、事前にBigQueryなどで集計したものを使うという形を取ることが多いのですが、データのリアルタイム性などが求められる場合はそうもいかなそうです。ただ正規化を崩し、サマリーデータをカラムに持つことは致命的な問題を引き起こしうると感じるので、 - ユーザーのリクエストの中で処理しない(非同期で処理する) - キャッシュしておく
などの方法で回避できれば、そちらを選んだ方が良さそうです。
- 選択条件の冗長性 検索性を高めるために、カラムに検索のための選択条件を追加し、正規化を崩すというケース(第2正規化を崩す)
5.3 冗長性とパフォーマンスのトレードオフ
あらためて非正規化のデメリットは - 検索のパフォーマンスup, 更新のパフォーマンスdown - データのリアルタイム性低下 - 更新のコストが高いために、非同期で更新されることになり、結果としてリアルタイム性が低下する
思ったこと
とある通り、筆者は非正規化を完全に否定しているわけではないと感じました。
正規化を守りつつ、パフォーマンスを向上させるためのありとあらゆる手段を使い、それでもなお要件を満たさない場合にのみ手段として考えうるのが非正規化であり、そのデメリットを明確に理解した上で、本当に必要であれば、時として、取りうる選択肢であると感じました(原則としては、正規化は高次であることが正義)
6. データベースとパフォーマンス
B-Tree Indexを作るべき列
- 大規模なテーブル
- カーディナリティの高い列に作成する
- WHEREの選択条件、または結合条件、ソートの条件に使用されている列
- indexがあれば、既にsort済みなので、sort処理が不要になる
- WHERE, JOIN, ORDER BY, GROUP BY
memo: データ量が少ない場合は、フルスキャンの方が早いケースがある
B-Tree Indexが効かないケース
- 該当の列に対して、演算を行なっている
- SQL関数を適用している
- IS NULL述語を使っている(基本的には、NULLのあたいはインデックに保持されていない)
- 否定形を用いている(利用しても、検索範囲が広すぎるため)
- これが常に正しいのかは、あまり自信がない
- ORを用いている→INを使えばOK
- 後方一致、中間一致のLIKE文を使っている
- 暗黙の型変換を行なっている
6.4 統計情報
DBMSがどのようなSQL文を発行するかは、データの統計情報を元に策定している。 ユーザーがデータベースに対して、SQL文を発行した場合、以下のような処理が内部的には行われている。
テーブルのデータが大きく変わった場合統計情報が実際のデータの統計情報とずれ、オプティマイザが正しい経路の選択ができなくなるので、注意が必要。
また基本的には統計情報は自動で更新されるため、データ量が増えていくにつれ、実行計画が変更され、急にパフォーマンスが劣化するということが起きうる。
ここら辺の詳しいことは、以下のスライドにもまとまっています。
実際のRailsアプリケーションで意識したいポイントなどは、以下の記事などが参考になりそうです。
# 実行計画をみる Model.some_query.explain # 実際に発行されるSQLをみる Model.some_query.to_sql
7. 論理設計のバッドノウハウ
- 配列を入れる
- SQLのデータ型としては、配列が取り入られたが普及はしなかった
- 原則情報が可能な限り分割する(名前なら、性と名に、メールアドレスなら、アドレス本体と、ドメイン、など)
- ダブルミーニング(列の中身が途中から変わる)
- 単一参照テーブル OTLT
- type という名前を持つカラムがいる場合
- ポリモーフィックなテーブル
- テーブル分割
- 水平分割
- 垂直分割
- 基本的には分割せず、パフォーマンスの向上のためであれば、パーティションというデータベースの機能を使う
まとめ
全部ではないですが、読んでいて印象に思ったメモをざっとまとめてみました。 まだ業務で大規模なテーブル設計を行うことがあまりないので、自分自身が直面したことのある問題は少ないのですが、意識していないと、やってしまいかねないと感じたもの(一見良さそうに見えるもの)もいく浸かるので、注意しておきたいです。
また普段BigQueryでクエリを書くような癖で、RDBに対して、クエリを発行すると、重大なパフォーマンスの低下を招きうることもヒシヒシと感じられたので、パフォーマンスの章は定期的に読み返したいです。
