この記事はエイチームブライズアドベントカレンダー13日目の記事です。
#はじめに
SQLアンチパターンについて改めて見返してみて、思い当たるところがたくさんあり、意識できていないなーと思っていたので、
この機会に一旦まとめてみました。
##1. ジェイウォーク（信号無視）
複数の値を格納するためにカンマ区切りの文字列を、そのまま文字列として格納してしまう。
→交差テーブルを作成する。
##2. ナイーブツリー（素朴な木）
ツリー構造を利用するための手段は簡単に５つ。
・ 隣接リスト（もっともシンプル。実装も利用も容易だが、隣接した要素以外にアクセスするクエリが大変）
・ 再帰クエリ構文（MySQLでは現在できない）
・ 経路列挙（パス文字列みたいなのを用意して前方一致での検索を容易にする）
・ 入れ子集合（二分木みたいにツリー情報を保持させるがメンテが大変）
・ 閉包テーブル（要素と子孫の対応テーブルを別に持たせる。交差テーブルと似た発想だけどテーブルが増える）
→用途によって使い分ける。
##3. IDリクワイアド（とりあえずID）
全テーブルにID列を持たせる。
→状況に応じて適切に調整する。
##4. キーレスエントリ（外部キー嫌い）
外部キー制約を使用しない。
参照整合性を使わない。
→外部キー制約を宣言する。
##5. EAV（エンティティ・アトリビュート・バリュー）
Attribute（属性）とValue（値）のペアを１行として登録すること。
汎用的な属性テーブルを使用する。
→サブタイプのモデリングを行う。
##6. ポリモーフィック関連
ある一つのカラムが複数のテーブルを参照しているようなパターン。
外部キー宣言ができなくなるので参照整合性制約が保証できない。
関連する全テーブルＪＯＩＮしないといけない。
→リレーションシップを単純化する。
交差テーブルの作成
共通の親テーブルの作成
##7. マルチカラムアトリビュート（複数列属性）
複数の値を保持するために複数の列を定義する。
→従属テーブルを作成する。
##8. メタデータトリブル（メタデータ大増殖）
テーブル名や列名に番号などを入れて増やす。
１つのテーブルの行数が多すぎるために複数のテーブルに分割する。
テーブルや列をコピーする。
→パーティショニングと正規化を行う。
##9. ラウンディングエラー（丸め誤差）
FLOAT型のカラムだと2進数では小数点の表現に限界があるため丸められてしまい誤差が起きてしまう。
→NUMERIC 、DECIMAL データ型を使用する
正確な 10 進数値が必要な場合は、NUMERIC を使う。
できる限り、FLOAT 型は使わない。
##10. サーティワンフレーバー
CREATE TABLE時にCHECK制約を入れたりENUM型（SQL非標準）を使う。
→列に入力する値を限定するときは、
値セットが固定されている場合はメタデータを、
流動的な場合はデータを用いる。
##11. ファントムファイル（幻のファイル）
画像などのバイナリファイルをストレージにおいて、そのパスをDBに格納する。
・ファイル削除時における問題
・トランザクションの分離
・ロールバック時における問題
・バックアップを同期
・SQLアクセス権限の管理ができない
・ファイルパスはデータ型ではないので、参照整合性やパスのチェックを行えない。
→必要である場合、BLOB型を選択する。
##12. インデックスショットガン（闇雲インデックス）
間違ったインデックスを使うと以下のようなことが起きる。
・インデックスを全く定義しないか、少ししかインデックスを定義しなくなってしまう
・インデックスを定義しすぎるか、役立たないインデックスを定義してしまう
・インデックスを活用しないクエリを発行してしまう
→闇雲にインデックスを張るべきではない。
##13. フェア・オブ・ジ・アンノウン（恐怖のunknown）
一般値を NULL に相当するものとして扱う。
SQLは、null を False、空文字列と異なる特殊な値をとして区別する。
null をさけるために一般値を使う。
→NULL を一意な値として使う。
NOT NULL 制約を使う。
データ型を問わず、欠けている値には NULL を用いるようにする。
##14. アンビギュアスグループ（曖昧なグループ）
グループ内で最大値を持つ行を取得する。
非グループ化列を参照する。
→曖昧でない列を使用する
・関数従属性のある列にのみクエリを実行する。
・相関サブクエリを使用する。
・導出テーブルを使用する。
・ＪＯＩＮを使用する。
・他の列に対しても集約関数を使用する。
・グループごとにすべての値を連結する。
##15. ランダムセレクション
ランダムな結果を返すSQLクエリ。
→特定の順番に依存しない。
1と最大値の間のランダムなキー値を選択する。
すべてのキー値のリストを受けとり、ランダムに1つを選択する。
##16. プアマンズ・サーチエンジン（貧者のサーチエンジン）
SQLのパターンマッチング述語を使う。
・Like演算子
・MySQLの正規表現述語
→適切なツールを使用する(SQLを用いない)
SQL標準に準拠しつつ、部分文字列マッチングより効率的な転置インデックスを用いる。
##17. スパゲッティクエリ
ある事柄を説明するために、必要以上に多くの実体を仮定するべきではない。
複雑なクエリはスパゲッティになりがち。
→シンプルなクエリに分割する。
##18. インプリジットカラム（暗黙の列）
タイプ数を減らす。
→必要な列だけを指定するようにする。
##19. リーダブルパスワード（読み取り可能パスワード）
パスワードを平文で格納する。
→ソルトをつけてパスワードハッシュを格納する。
SQLでパスワードを平文として扱わないこと。
パスワードをリカバリーするのではなく、リセットする。
一意なtokenを識別子として用いる。
##20. SQLインジェクション
アプリケーションのセキュリティ上の不備を意図的に利用し、
アプリケーションが想定しないSQL文を実行させる。
→入力のフィルタリング。
動的値のパラメーター化。
動的値を引用符で囲む。
ユーザーの入力をコードから隔離する。
コードレビューをする。
##21. ジュードギー・ニートフリーク（疑似キー潔癖症）
欠番を詰める。
新しい行の挿入時に、欠番番号のうち最も小さいものを割り当てる。
→主キーの値は一意で非NULL の値である必要がある。
ルールはそれだけで連続する必要がない。
##22. シー・ﾉー・エビル（臭いものに蓋）
戻り値を無視する。
ＳＱＬしか読まない。
肝心な部分を見逃す。
→戻り値と例外をチェックする。
ステップをたどり直す。
##23. ディプロマティック・イミュニティ（外交特権）
ＳＱＬを特別扱いする。
何度も使う処理はベストプラクティスを適用。
その場限りのコードは削除する。
→文書化
バージョン管理
テスティング
##24. マジックビーンズ（魔法の豆）
モデルがアクティブレコードそのもの。
→モデルがアクティブレコードを「持つ」ようにする。
##25. 砂の城
サービスの安定稼働。
→ベンチマーク
テスト環境の構築
例外処理
バックアップ
高可用性
ディザスタリカバリ
運用ポリシーの想定
#おわりに
改めてまとめてみると意識できていない部分も多く、知らないうちにやってしまってることも多い。次回はもっと掘り下げてそれぞれ実際の事例と共に書ければと思います。
#参考
・SQLアンチパターン
#お知らせ
エイチームブライズでは一緒に活躍してくれる優秀な人材を募集中です。
興味のある方はぜひともエイチームグループ採用ページ（Webエンジニア詳細ページ）よりお問い合わせ下さい。
#明日
エイチームブライズアドベントカレンダー13日目の記事は、いかがでしたでしょうか。
明日は @sho0211 が「google homeがうちにやってきた」について書いてくれるようです。お楽しみに！