SQLアンチパターン簡単まとめ

「SQLアンチパターン」について、自分の勉強がてら内容を簡単にまとめました。

「とりあえずSQLは触れてWebアプリが作れる」「シンプルなアプリを実務で作ったことがある」くらいの人が読むととても勉強になると思います。

また、「現場での良くない設計」が詳細に言語化されているので、経験豊富な人でも新たな発見があると思います。

詳しく知りたい方は是非本を買って実際に読んでみて欲しいです。

第一章　ジェイウォーク

パターン

• 1カラムにコンマ区切りで値を入れて複数の紐付きを表現しようとする

なぜダメか

• 検索しづらい

• 文字列カラムの文字数制限という暗黙の制約を受ける

• バリデーションかけられない

解決策

• 交差テーブルの作成

第二章　ナイーブツリー

パターン

• 木構造を表現するとき、常に親のみに依存する(parent_idだけ持ってしまう)

• 隣接リスト

なぜダメか

• 階層を深く掘ろうとするとそのぶんSQLをかかないといけない

• ノードの削除がしづらい

解決策

• 経路列挙、入れ子集合、閉包テーブル

第三章　IDリクワイアド

パターン

• 全てのテーブルにidを作成してしまう

なぜダメか

• 冗長になる可能性がある

• 脳死でidカラムを作成してしまうことで論理的な一意性とズレる

• シーケンスで作成されるid==主キーではない

解決策

• ORMによってはサロゲートキーの利用が前提となっていることもあるのでそれには従った方がいい

• 主キーに明確な名前をつける

• 規約に縛られない

• 柔軟に複合キーを使う

第四章　キーレスエントリ

パターン

• 外部キー制約を使用しない

なぜダメか

• 参照整合性をアプリケーションで再実装する必要がある

• 壊れた参照をどうするかという問題が生じる

• アドホックなSQLによって壊れる

解決策

• 外部キーを使う

第五章　EAV

パターン

• 可変属性を表現するために attr_name と attr_value を持つテーブルを作成する

なぜダメか

*NULL制約をつけられない

• 値が全部文字列型のため無効なデータをDBで弾けない

• 参照整合性を強制できない

• OUTER JOINを使うため遅い

解決策

• スキーマレスなデータはRDB以外を使う

• シングルテーブル継承、具象テーブル継承、クラステーブル継承、半構造化データ

第六章　ポリモーフィック関連

プロミスキャスアソシエーションとも呼ばれる

パターン

• 一つのモデルに対して複数のモデルを関連させるために、typeとidを持つ

なぜダメか

• 参照整合性が担保されない

• 外部結合が発生する

解決策

• 交差テーブルを作る

• 共通の親テーブルの作成

第七章　マルチカラムアトリビュート(複数列属性)

パターン

• 一つのエンティティに複数の値（ex. 記事に対するタグ付けなど）を格納したいときに、tag1,tag2,tag3のようなカラムを作って横持ちで対応する

なぜダメか

• 検索がしづらい

• 更新もしづらい

• 一意性の保証ができない

• 列数が足りなくなる可能性がある

解決策

• 従属テーブルを作る

第八章　メタデータトリブル（メタデータ大増殖）

パターン

• テーブルや列をコピーする

• データに依存して、テーブルを作成しようとする

なぜダメか

• 列数が多すぎるテーブルを作成したり、行数が少ない多数のテーブルを作らないといけなくなる

• 新たなデータのために新たなテーブルを作らないといけない場合がある

• テーブル間の整合性がとりづらい

• 参照整合性がとりづらい（主キーを設定できない

解決策

• 行で分割する水平パーティショニング（SQL標準では無いが各種DBがそれぞれ独自の方法をサポートしている）

• 列で分割する垂直パーティショニング。特にBLOBやTEXTで有効。BLOBやTEXTを SELECT * で取得すると遅くなる。

• 従属テーブルの導入

第九章　ラウンディングエラー

パターン

• データベースの中で小数値を扱いたいとき、FLOAT型を用いる

なぜダメか

• 浮動小数点の丸め誤差が生じて計算があわない

解決策

• NUMERIC型、あるいはDECIMAL型を用いる

• FLOATは概数として扱われるべき。

第十章　サーティワンフレーバー

パターン

列に入る値を限定するためにCHECK制約を用いたいが、限定する値を列定義でベタ書きする

なぜダメか

• 値の廃止が難しい

• CHECK制約、ドメイン、ユーザ型は各種データベース製品で仕様が統一されていない

解決策

• 参照テーブルを作成し、外部キー制約をつけることで列に入る値を制限する。単純なSQLで表現できるので管理がラク

第十一章　ファントムファイル

パターン

• 画像のような大容量ファイルを格納するとき、リンクだけをデータベースに格納してファイルの実体を外部ストレージに保存する

なぜダメか

• レコードを消してもファイルの削除は担保されない（外部ストレージにゴミデータが残る）

• ロールバックで元に戻せない

• バックアップツールもサポートされないので、復旧したデータと画像を紐付ける処理は自分で書く必要がある

• 外部ファイルはSQLのアクセス権限の影響を受けない

解決策

• 必要に応じてBLOB型を検討する

画像を外部ファイルに格納することが常に最善の方法であるというプログラマーの一般論にただ従うのではなく、
十分な情報に基づき、よく検討したうえで判断しましょう、

第十二章　インデックスショットガン

インデックスに対する知識不足による間違ったインデックスの使用

パターン

• インデックスを全く使用しない

• テーブルの全ての列と組み合わせにインデックスを張ってしまう

• インデックスが効かないクエリを発行する

なぜダメか

• パフォーマンスを劣化させる

解決策

MENTORチェックリストに基づいて考察* 検証する

• Meature（測定）

• Explain（解析）

• Nominate（指名）

• Test（テスト）

• Optimize（最適化）

• Rebuild（再構築）

第十三章　フィア・オブ・ジ・アンノウン

パターン

NULLを含む列に対してクエリを書くときに、NULLを一般値として扱う、あるいは一般値をNULLとして扱う

なぜダメか

• NULLを含んだ演算はすべて結果がNULLになる

• NULLは比較できない

• NULL恐怖症に陥り、NULLで扱うべき値を実体値として扱うことにしても、事態はよくならない。

解決策

• NULLを一意な値として扱う

• スカラー値、論理式でのNULLの演算結果を理解する（直観と異なるものがある）

• 検索はIS NULLを利用

• 列にNOT NULL制約を付与する。アプリケーションコードに頼るのではなく、データベースで一貫した制約を強制する

第十四章　アンビギュアスグループ

パターン

• グループ内の最大値が見つかったレコードの他の属性も取得しようとする

なぜダメか

• GROUP BYで列挙されていない列で値が一意に決まるとは限らない

解決策

• 関数従属性のある列のみにクエリを実行

• 相関サブクエリの使用

• 導出テーブルの使用

• JOIN

• 他の列に対しても集計関数を利用

• グループごとに全ての値を連結

第十五章　ランダムセレクション

パターン

• rand() を用いてランダムにソートを行い、最初の行をフェッチする

なぜダメか

• インデックスが効かない

• テーブル全体をスキャンするのに最初の数行しか利用しないのでパフォーマンスの無駄が大きい

解決策

• idが1から最大値までの間の値をランダムに選択する(idが連続して欠損していない場合のみ）

• 欠番の穴の後にあるキー値を選択する

• id のリストを一回全件取得して、アプリケーション側でランダム選択してもう一度SELECTクエリを発行する

• オフセットを使ってランダムに行選択する

• ベンダー依存のsample関数を使う

第十六章　プアマンズサーチエンジン

パターン

• LIKEや正規表現によるパターンマッチ

なぜダメか

• インデックスが効かず必ずテーブルスキャンになる

• 単純なパターンマッチでは意図しないマッチが生じる

解決策

• 全文検索エンジンの利用

• ベンダー拡張としての全文検索エンジンの利用。MySQLならフルテキストインデックス

• SphinxやLucene(ElasticSearch)等、SQLと独立して動く検索エンジンの活用

• 転置インデックスの自作（そこそこ実装コストかかる）

第十七章　スパゲッティクエリ

パターン

• 複雑な問題を一つのSQLで解決しようとする

なぜダメか

• メンテが困難

• 結合や相関サブクエリなど手の込んだSQLクエリは一般にシンプルなクエリと比べて遅い

解決策

• 分割統治

• クエリを複数に分割してワンステップずつ実行

• そもそも仕事レベルでタスクを分割して処理

第十八章　インプリシットカラム

パターン

SQLを短くするために SELECT * を用いる。

なぜダメか

• 列の追加、削除、名前変更などを行うと、クエリ結果に生じた変化をコードがうまく扱えなくなる（カラムには実際には順番があり、添え字がずれる）

• 全列フェッチするのでデータ量が多くなる

解決策

• 列名を明示的に指定する

第十九章　リーダブルパスワード

パターン

• パスワードを平文で格納

なぜダメか

• セキュリティリスク

解決策

• ソルトをつけてパスワードハッシュを格納する

• パスワードはリカバリーではなくリセットする

第二十章　SQLインジェクション

パターン

• 動的にSQLを構築するときに検証していない入力をコードとして実行してしまう

なぜダメか

• SQLステートメントを乗っ取り、テーブルの削除、ユーザーへのなりすましなどあらゆることができる

解決策

• プリペアドステートメントの利用

• ユーザーの入力をコードから隔離（プリペアドステートメントが利用できない場面において有効）

• コードレビュー

メモ

• 万能薬のように使われるプリペアドステートメントも完璧ではない。例えば値のリストをパラメータにはできないし、テーブル識別子や列名もパラメータにできない

第二十一章　シュードキー* ニートフリーク（疑似キー潔癖症）

パターン

• idが連番になっていないものを埋めようとする。

なぜダメか

• 欠番を特定するために自己結合クエリを発行する必要がある

• 排他制御の問題で、一方でエラーになる場合がある

• 欠番を埋められたとしても、シーケンスは最後に生成した値をもとに作られるので新たな欠番が作られる

• 欠番は正当な理由による行の削除やロールバックの結果

解決策

• 疑似キーを再利用しない。これはいかなる場合でも正当化されない。

• 主キー列の値に何らかの意味を持たせてはならない。

• GUIDの利用を検討する

• 上司のシステム知識をマネジメントする。対話、見積もり、自然キーの利用。

メモ

• 32ビット整数値は、毎秒1個のペースで生成されていったとしても、枯渇するまで136年、64ビット整数値は58万年を要する。ゆえに「idの整数値が枯渇していくのではないか」という問いは無視して良い。

• UUIDが衝突する確率は、100年間 毎秒10億のUUIDを生成し続けた後に、1つのUUIDを生成、その生成したUUIDが過去のUUIDと衝突する確率は50%

第二十二章　シーノーエビル（臭い物に蓋）

パターン

• データベースAPIの戻り値を無視する、アプリケーションコード内に点在するSQLしか読まない、例外処理をやらない

なぜダメか

• 戻り値を無視するので問題に気づくことが出来ない

• 構築されたSQLを見ずにSQLを構築するコードを追うことによって時間を浪費する

解決策

• 戻り値と例外のチェック

• 実際に構築されたSQLを使用する

第二十三章　ディプロマティック・イミュニティ（外交特権）

パターン

• アプリケーション開発のベストプラクティスを実践しているチームでも、SQLになるとそれをしない

なぜダメか

• いわゆる技術的負債を残す

解決策

• データベース開発においても、文書化、バージョン管理、テスティングのベストプラクティスを守る。

• DDL、seed data, データベース管理スクリプト、トリガーとプロシージャ、ER図とドキュメントをバージョン管理に載せる。

• 開発、テスト、ステージング、デプロイを行うアプリケーションそれぞれにデータベースインスタンスを作る。各開発者のデータベースも用意。

第二十四章　マジックビーンズ

パターン

• Modelを単純化しようとした結果、ModelがActiveRecordそのものになってしまう

• コントローラにビジネスロジックが流出し、コードが蔓のように絡まる

なぜダメか

• モデルがデータベーススキーマに依存してしまう

• モデルが外部にCRUDを公開してしまうため、予期しない使い方をされることがある

• モデルの凝集度を下げてしまい、ドメインモデル貧血症

• ユニットテストが難しくなる

解決策

• モデルがアクティブレコードを持つようにする

• DAOを用いてデータベースアクセスを分離し、データベースと独立してユニットテストを行えるようにする

第二十五章　砂の城

パターン

• 障害を想定せずにサービスを運用しようとする。

なぜダメか

• 実運用ではかならずトラブルが発生するから

解決策

• トラブルは日常的に起きるものとして、可能な限り想定する

• ベンチマーク、テスト環境の構築、例外処理、バックアップ、可用性、災害からの復旧

• 運用ポリシーの策定

リンク

SQLアンチパターン