レポート：SQLアンチパターン

はじめに

「SQLアンチパターン」の書籍を参考とした学習内容のメモ。

SQLアンチパターン

データベースの論理設計や物理設計で陥りやすい失敗を避けるための方法を一部抜粋、要約して本レポートにまとめています。

1. ジェイウォーク（第一章より）

＜目的＞

1対多の関連を作ろうとして、複数の値を持つ属性を1つの列に格納してしまう。

＜アンチパターン＞

カンマ区切りフォーマットのリスト格納

user_id	goods_ids
1	2,7,12

＜Why＞

• 検索がしづらい（パターンマッチ構文の危うさ）
• 属性の更新と削除がしづらい（SQLの冗長化とコードによるデータ整形が必要になる）
• バリデーションがかけられない
• リストの長さが文字列カラムの文字数制限にしばられる

＜解決策＞

交差テーブル（2つのテーブルを参照する外部キーを持つテーブル）の作成

＜見解＞

• 外部キー制約により参照整合性の保証ができ不明な値のバリデーションが可能
• 将来的にもリスト内の各要素への個別アクセスが不要の場合でカンマ区切りフォーマットのデータが必要の場合はアンチパターンを用いても良いが非正規化のため要検討

2. メタデータトリブル（第八章より）

＜目的＞

スケーラビリティを高めるため

＜アンチパターン＞

テーブルや列をコピーしてしまう

＜Why＞

• 行数を減らすために列数が多いテーブルや行数が少ない多数のテーブルを作成しなければならなくなる
• 新しいデータのために新たなテーブルを作成しなければならない場合がある（年毎のデータを複数分割時に翌年のテーブル作成忘れると更新時エラー発生）
• 分割されたテーブル間での主キーの設定にコストがかかる
• クエリの冗長化

＜解決策＞

• 水平パーティショニング（行で分割。各種DBで独自のパーティション機能あり）
• 垂直パーティショニング（列で分割。可変長データ型など列の一部のサイズが大きい場合に有効）

＜見解＞

• 単にテーブルを増殖させるのではなく、パーティショニングでアプリケーションからは1つのテーブルとして扱うようにした上で、年度ごとに分割などしてテーブル全体を走査せずに指定の期間だけをみるようにする。
• ただし、実装済みのアプリケーションに適用する場合には、対象のデータの影響範囲要確認。

3. インデックスショットガン（第十二章より）

＜目的＞

パフォーマンスを最適化させるため

＜パターン＞

闇雲に間違ったインデックスを使用する

• インデックスを全く定義しない
• インデックスを多く定義しすぎるか、役立たないインデックスの定義
  • 主キー：自動的にインデックス作成のため、明示的に定義するのは冗長
  • 長い文字列型：サイズが大きくなる
  • 複合インデックス：検索条件、結合条件、ソート順において、列を定義した順（左から右）に使わなければいけないので注意が必要。
• インデックスが効かないクエリの実行（LIKE述語使用時など）

＜Why＞

パフォーマンスを劣化させてしまう

＜解決策＞

チェックリスト「MENTOR」に基づいて管理する

• Measure（測定）
• Explain（解析）
• Nominate（指名）
• Test（テスト）
• Optimize（最適化）
• Rebuild（再構築）

＜見解＞

• チェックリストを用いることで、インデックスをはる根拠をはっきりさせる。
• スロークエリ特定→原因解析→新しいインデックスの作成かLIKE述語の代わりに全文検索の導入など→クエリの効果検証→定期的にインデックスのメンテナンス

4. アンビギュアスグループ（第十四章より）

＜目的＞

グループ内の最大値と、その最大値が見つかった行の他の属性も取得する。

＜パターン＞

非グループ化列を参照する

-- bug_idが非グループ化列 クエリエラーとなるか日付とbug_idの行が一致しない事象が発生する
SELECT product_id, MAX(reported_on) AS latest, bug_id
FROM Bugs INNER JOIN BugsProducts USING (bug_id)
GROUP BY product_id

＜Why＞

• GROUP BYされていない列で値が一意に決まるとは限らない
• reported_on列で複数のバグが同じ報告日付を持ち、その日付が最大値である場合、どのbug_id値を返せばよいか不明となる。
• クエリで、MAXとMINの両方を指定した場合、どちらの行の値に紐づくbug_idを取得していいのか不明となる。

＜解決策＞

• 曖昧な列を削除（bug_idが不要な場合、bug_idを参照しない。）
• 相関サブクエリを使用
• 導出テーブルを使用（中間結果をJOIN）

＜見解＞

• product_idをGROUP BY句で集約した際、対応する複数のbug_idの全ての値を表すことができないため、JOIN等で解決する。ただ、パフォーマンスの低下を考慮する必要あり。

総括

前述のジェイウォークや、冗長になりがちなIDリクワイアドなど、どこかで見たことがある馴染み深いものや、複雑な問題を1つのクエリで解決しようとするスパゲッティクエリなど、これらの内容は過去に経験したことがあるか、現在も直面しているものが多く、再認識の意味でも非常に勉強になりました。SQLのスキルを向上させることを目的とする場合、この本を読了することは非常に有意義だと考えます。