Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SQLアンチパターン読書メモ

データベース論理設計のアンチパターン

ジェイウォーク

  • 🙅‍♂️ 複数の値をカンマ区切りで表現する
  • 交差テーブルを作ろう

ナイーブツリー

  • 🙅‍♂️ 再帰構造の表現に parent_id を使う
  • 経路列挙
  • パス形式の値を持たせる
  • 入れ子集合
  • 深さを数値で持たせる
  • 閉包テーブル
  • 関連を別テーブルに持たせる
  • 🤔 1-n の2テーブル作ったらダメなんかな?

IDリクワイアド

  • 🙅‍♂️ 何でもかんでも擬似キーを使う
  • 分かりやすい列名にする
  • 規約に縛られすぎない
  • 自然キーと複合キーを活用する
  • 🤔 サロゲートキーは命綱みたいなとこある
    • とりあえず付けとけは思考停止だったかもなぁ

キーレスエントリ

  • 🙅‍♂️ 外部キーを使わない
  • 外部キー制約を使う
    • ミスを未然に使う

エンティティ・アトリビュート・バリュー

  • 🙅汎用的な属性テーブルを使う
  • シングルテーブル継承
  • type カラムを作って、一つのテーブルで表現する
  • 具象テーブル継承
  • サブタイプごとにテーブルを作成する
  • クラステーブル継承
    • 基底のテーブルとサブタイプのテーブルを作る
  • 半構造化データ
    • LOB 列に XML や JSON で格納する
  • 🤔基本はクラステーブル継承で、必要に応じて半構造化データを使うのがよさそう

ポリモーフィック関連

  • 🙅‍♂️ 二重目的の外部キー
  • type 列を用意した判定
  • 交差テーブルを作る
  • 必要に応じて Unique 制約を付与
  • 共通の親テーブルを作る

マルチカラムアトリビュート

  • 🙅‍♂️ 複数の列を定義する
    • tag1, tag2, tag3,,みたいなの
  • 従属テーブルを作る
  • 意味合いが異なるなら互換性がある値でもよい
    • 報告者アカウント、担当者アカウント、テストアカウント、みたいなの

メタデータトリブル

  • 🙅‍♂️ メタデータのテーブルや列をコピーする
    • bugs_2018, bugs_2019,,みたいなの
  • パーティショニングを行う
  • 従属テーブルを使う
  • アーカイブが目的なら許容される
  • 🤔 スケーラビリティが目的ならパーティショニングするよりかクラウドで従属テーブルがよさげ
    • アーカイブも BigQuery とかに逃せば

データベース物理設計のアンチパターン

ラウンディングエラー

  • 🙅‍♂️ FLOAT データ型を使用する
    • 無限精度の値がある
    • 丸めが避けられない
  • NUMERIC または DECIMAL を用いる
    • 精度とスケールを指定できる

サーティワンフレーバー

  • 🙅‍♂️ 値の制約を列定義で行う
    • CHECK 制約や ENUM データ型
  • 限定する値はデータで指定する
    • ステータスマスタみたいなものを作る

ファントムファイル

  • 🙅‍♂️ DB にはパスを格納し、実体は別にある
  • BLOB 型を必要に応じて採用する
  • 🤔 筆者は自分が少数派であることを認識している
  • 🤔 言われるとなるほど確かにと思うこともある
    • パスは型による保証がない
    • トランザクションが分離
    • ロールバック
    • アクセス権限
    • etc

インデックスショットガン

  • 🙅‍♂️ インデックスを闇雲に貼る、あるいは貼らない
  • 😲 インデックスは SQL 規格にない
  • MENTOR の原則に基づいてインデックスを管理する
    • Mesure (測定)
      • スロークエリログを見る
      • キャッシュは無効化する
      • アプリケーションのプロファイリングをする
    • Explain (解析)
      • 実行計画を取得する
    • Nominate (指名)
      • 実行計画からネックを特定する
      • カバーリングインデックス
        • 全ての列をインデックスにすることでテーブルアクセスを不要にする
        • 😳 やんちゃだ。。
    • Test (テスト)
      • 変更の効果を計測する
    • Optimize (最適化)
      • キャッシュメモリの量を設定する
      • MySQL はインデックスをあらかじめキャッシュにロードできる
        • LOAD INDEX INTO CACHE
  • Rebuild (再構築)
    • インデックスのメンテナンス
    • MySQL なら ANALIZE TABLE
    • 実行頻度に正解はない

クエリのアンチパターン

フィア・オブ・ジ・アンノウン

  • 🙅‍♂️ NULL を一般値として扱う
  • 🙅‍♂️ 一般値を NULL として扱う
  • NULL は FALSE や 0 や空文字ではない
  • 🙅‍♂️ NULL を使いたくないがために "unknown" のような特殊値を使う
    • NULL を使うこと自体がアンチパターンなわけではない
  • NULL を一意な値として使う
  • NULL が意味をなさない場合には NOT NULL 制約を宣言する
  • COALESCE 関数を使う
    • NULL の代わりに動的なデフォルト値を取得できる

アンビギュアスグループ

  • 🙅‍♂️ 非グループ化列を参照する
  • 関数従属性のある列のみにクエリを実行する
    • 基本はこれで、後は必要に応じて考えればいい気がする

ランダムセレクション

  • 🙅‍♂️ データをランダムにソートする
    • インデックスが使えないのでテーブルスキャンが走る
  • 1 と最大値の間のランダムなキー値を選択する
    • 連番が前提ならこれ
  • 欠番の穴の後にあるキー値を選択
    • 欠番があるならこれ
  • キー値のリストを取得した後、ランダムに選択
    • クエリが二回になる
    • 🤔 全件取得なので微妙
  • オフセットを用いてランダムに行を選択
    • 🤔 これならまぁ。。
  • ベンダー依存の解決策
    • サンプル取得の機能が用意されている製品がある

プアマンズ・サーチエンジン

  • 🙅‍♂️ 全文検索にパターンマッチ述語を使用する
    • LIKE や正規表現は重い
  • ベンダー拡張を使う
    • 🤔 PostgresSQL の TSVECTOR は便利そう
  • サードパーティーのサーチエンジンを使う
    • Sphinx Search
    • Apache Lucene
    • 🤔 Elastic Search 使えば良さそう

スパゲッティクエリ

  • 🙅‍♂️ 複雑な問題をひとつのクエリで解決しようとする
    • リレーションを持たないテーブルだとデカルト積が発生する
  • 分割統治を行う
  • クエリを分ける
  • UNIONを用いる
  • CASE 式と SUM 関数を組み合わせる
  • SQL を生成する SQL を書く
  • 節約の原則
    • まったく同じ予測をする2つの競合する理論があるときは、単純な方が優れている

インプリシットカラム

  • 🙅‍♂️ 列名を省略する
    • ワイルドカードなど
  • 明示的に指定する
  • ワイルドカードを使えない局面はいずれ訪れる

アプリケーション開発のアンチパターン

リーダブルパスワード

  • 🙅‍♂️ パスワードを平文で格納する
  • ソルトを付けてパスワードハッシュを格納する
    • MD5 は使わない
    • ソルトを付けて辞書攻撃を防ぐ
  • クエリからパスワードを隠す
    • ハッシュ化してからクエリを発行する
  • パスワードをリカバリーせず、リセットする
  • "あなたが読み取れるものは、攻撃者にも読み取れます"

SQL インジェクション

  • 🙅‍♂️ 動的 SQL に未検証の入力を使用する
  • 入力のフィルタリング
  • プリペアドステートメントの使用
  • 動的値を引用符で囲う
  • プリペアドステートメントは万能ではない
    • IN 句で使えない
      • IN 述語のパラメータ化
    • 識別子、予約語等では使えない
  • ユーザーの入力をコードから隔離
    • 識別子と入力値の紐付けをクエリの外で行う
  • コードレビュー

シュードキー・ニートフリーク

  • 🙅‍♂️ 擬似キーの欠番を埋める
  • 欠番は埋めない
  • 行番号と擬似キーは別物
  • ROW_NUMBER 関数や LIMIT 句を使う
  • 整数の擬似キーは枯渇するか?
    • 毎秒1個としても 32 ビットなら 136 年かかる
    • 64 ビットなら毎秒 100 万個でも 58 万 4542 年かかる
  • GUID (グローバル識別子) も擬似キーに使える
    • 事実上一意
    • 16 バイトなので、サイズが大きい
  • 意味のあるキーは自然キーにする

シー・ノー・エビル

  • 🙅‍♂️ 肝心な部分を見逃す
    • データベース API の戻り値を無視する
    • アプリケーションコード内の SQL しか読まない
  • 戻り値と例外をチェックする
  • 実際に構築された SQL クエリを使用する

ディプロマティック・イミュニティ

  • 🙅‍♂️ SQL を特別扱いする
  • 包括的に品質問題に取り組む
  • 文書化
  • バージョン管理
  • テスティング
  • 複数のブランチ
    • アプリケーションの各ブランチ向けにそれぞれデータベースインスタンスを構築する

マジックビーンズ

  • 🙅‍♂️ モデルがアクティブレコードそのもの
    • データベーススキーマに強く依存する
    • ドメインモデル貧血症をもたらす
  • モデルがアクティブレコードを持つようにする

砂の城

  • 🙅‍♂️ 想定不足
  • 本番と同じテスト環境を1セット用意するのが理想
  • 例外処理を実装する
    • デッドロックやロック待ちタイムアウトはどの製品でも起こる
  • バックアップ
  • 高可用性
  • ディザスタリスカバリ
  • 運用ポリシーの策定
    • 高可用性の限界を超えた障害
    • 問題の調査
    • 性能の劣化
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.