Open10
SQLアンチパターン読書メモ
mishSQLアンチパターンの読書メモです。
RDSの基礎を押さえつつ失敗例と対処法を学べる初学者にも中級者にも学びが多い書籍。パターン本なので最初から順を追って読む必要がなく目次から興味のある分野を選んで読み進めることができる。
追記
-
1/3読み進めて要約してみる：
- SQLは強力なのでSQLでできることはSQLに任せましょう（アプリ側で頑張る前にSQLでできないか検討するのがよい）
- アプリ側で頑張る必要がある場合はDB設計を見直す必要があるかNoSQLとかRDSより適したサービスがあるかもね
-
2/3読み進めて要約してみる：
-
3/3読み進めて要約してみる：
mish第一章 ジェイウォーク（信号無視）
- 多対多を表現する交差テーブルを端折ってリストでデータを格納する
- バグ管理DBの例：１つのバグに対して複数の担当者をアサインする→担当者のIDをリストで格納するようにしたので文字数制限で限りある人数の担当者しかアサインできなくなった
関連する値の集合を列に収めたい場合
- アンチパターン：リストでまとめて一つの列に格納
- Select、Joinなどを使ってSQLで検索するのが難しくなる
- REGEXなどを使う必要が出てくる
- リストの値を更新したい時、順番が担保されない
- 必要以上のコードを書かなければいけなくなる
- 入力値のバリデーションが困難になる
- DBエラーにならない
- ゴミが入る可能性が上がる
- 区切り文字：データの一部なのかどうかの判別が難しい
アンチパターンを使ってもいいケース
- クエリパフォーマンス向上のための非正規化
- リスト内の各個別要素へのアクセスが不要なケース
解決：交差テーブルを作成する
- 例：１つのバグに対して複数の担当者をアサインする
→ プロダクトテーブルとバグテーブルの間に担当者テーブルを作成する - JOINで検索がしやすくなる
- 存在する担当者のIDのみがアサインできるようにバリデーションを利用できる
mish第二章 ナイーブツリー（素朴な木）
- ナイーブ＝思慮が浅い
- 例：一つの記事に対して複数のコメントが付きコメントはスレッド形式で枝分かれする
- 各コメントに親コメントを参照させる方法をとった場合
- リプライの連鎖が長くなるにつれ単一SQLでの取得が難しくなる
- 各コメントに親コメントを参照させる方法をとった場合
- 隣接リストのクエリ実行
- 隣接リスト：同じテーブル内でID参照する設計
- コメントの親コメント（一つ前のコメント）をID参照する
- 全ての子孫を習得したい場合
- 深い階層のデータにはSQLが使えないのでアプリケーション側で頑張る必要が出てくる
- 操作のたびに大量のデータをコピーするのは非効率
- 隣接リスト：同じテーブル内でID参照する設計
アンチパターンを使ってもいいケース
- 直近の親と子のみに関しては隣接リストは効果的
解決：代替ツリーモデルを利用する
- 階層型データを格納するのに系列列挙モデル(Path Enumeration)、入れ子モデル(Nested Set)、閉包テーブルモデル(Closure Table)がある
- 系列列挙モデル(Path Enumeration)：pathを大きめのVARCHARに設定しパスを/区切りで格納(e.g: '1/2/3/4')
→パスに対してパターン比較を行うことで先祖を取得できる - 入れ子モデル(Nested Set)：nsright, nsleftにそれぞれそのノードより下の階層にある全てのノードが持つ値より小さな値、大きな値を格納し、深さ優先検索で先祖や子孫を特定できるようにする
- 間のデータを削除しても自動的に削除されたノードの子孫は削除されたノードの親の直接の子だとみなされる点が長所
- 閉包テーブルモデル(Closure Table)：ツリー全体のパスをテーブルに格納する方法、よりシンプルなクエリで階層情報が取得可能
mish第三章 リクワイアド（とりあえずID）
- 同じ内容のデータに複数の異なるIDがふられている（ID以外のデータが重複している
- 結果本来よりも多い件数のデータが格納されている
- 全てのテーブルがPrimary Keyを持つことがアンチパターンになっているケース
- 全てのテーブルのPrimary Keyにidという名前をつけない
- 意味がわかる名前にすること
- ORMを使う際は列名をidに統一したほうがいい場合もある
- 自然キーと複合キーを使うことで解決可能
mish第四章 キーレスエントリ（外部キー嫌い）
- 参照整合成
- 外部キー制約
- 一つの列または複数の列に外部キー制約を宣言するとき、これらの列の値が親テーブルの主キー列やユニークキー列に存在しないといけない
- 外部キーを利用しない場合親データが削除されても子データが残るなどの孤児データ発生の原因になり、これの保守もしなくてはいけない
アンチパターンを使ってもいいケース
- 外部キー制約をサポートしていない製品を利用する場合
- 外部キー関連の縛りを乗り越える性質の極端に柔軟な設計にしないといけない時
ほとんどの場合外部キーを素直に使う方が効率が良い
mish第五章 EAV（エンティティ・アトリビュート・バリュー）
- 可変属性をサポートし拡張性の高い設計にしたい
- RDSのメタデータの柔軟性の低さ
- 汎用的な属性テーブルを作ってしまうアンチパターン
- 必須属性を指定できない
- SQLのデータ型が使えない
- NoSQL DBを利用する方が適している場合がある
mish付録A 正規化(normalization)のルール
- RDSには無駄な冗長さのないデータ戦略を設計する際に役立つルールが色々定義されている
- 正規化もその一つ
- 正規化の前に以下のルールが満たされているか確認する
- 行に上下の順番がないか（ORDER BYでソートしない限りSQLは順番を担保しない
- 列に左右の順番がないか
- 重複行を許可していないか
- 全ての列は一つの型を持ち各行に一つの値を持つ
- 行に隠されたコンポーネントがない
正規化の神話（まちがっている）
- 正規化はデータベースを遅くする
- JOINを使う頻度が増えるため
- 正規化とはデータを子テーブルに追い出し擬似キーを使って参照することである
- 正規化とはEAV設計（第五章参照）のようにできる限り属性を切り離すこと
- 第４正規形以上の正規化はしなくてよい
正規化とは何か？
-
人間が理解できる形で現実世界の事実を表現する
-
事実の格納方法から冗長性を排除しデータ以上や不整合を防ぐ
-
整合性制約をサポートする
-
パフォーマンスの改善はここには含まない
-
正規化は正確にデータを格納すること、問題の発生を防ぐことに寄与する
- 不整合や重複を排除する
-
正規化のルールを満たしているテーブルの状態を正規形という
第一正規形
- テーブルがリレーションであること
- テーブルに繰り返しグループがないこと
mish第7章 マルチカラムアトリビュート（複数列属性
- 複数の値を持つ属性を格納する
- ジェイウォークと同じテーマ
- アンチパターン：複数の列を定義する
- tag1, tag2, tag3など
- 検索が面倒なことになる
- 値の追加と削除が複雑になる
- tag123をチェックして空いている箇所にUpdateをかけるようなイメージ
mish第8章 メタデータトリブル
- 水平パーティショニング
- シャーディング
- 巨大化したテーブルを行で分割する
- 物理的には分割されているがSQLで同テーブルを扱うように操作できる
- 垂直パーティショニング
- 列で分割
- 列の一部が大きい場合や滅多に使わない場合に良い
- BLOBやTEXTなどのデータ
- 製品のインストーラ（.exe, .dmg形式のファイル）など
- インストーラだけ別テーブルに格納することでクエリ効率が上がる
- BLOBやTEXTなどのデータ
- 従属テーブル
- データを強引に一行に収めるのではなくテーブルに格納して参照できるようにする
mish第9章 ラウンディングエラー
- 実数の格納にFLOATを使う
- Floatでは無限精度が扱えない
- 0.333333....はどこかで丸められる
- double precision, realも同様で丸みが発生する
- 金融系のシステムだと致命的
- 重ねて計算すると誤差が累積する
- Floatでは無限精度が扱えない
解決策
- Numeric型を使用する
- Decimalでも回避可能
- スケール（小数点以下に格納する桁数）が指定できる
→Float型はあまり使わない方が良い