【読書メモ3】SQLアンチパターン
Bill Karwin, 『SQLアンチパターン』, オーム社 を読む。
概要
アプリケーション開発においてRDB/SQLを取り扱う際に陥りやすい失敗（アンチパターン）を25種紹介し、それらの原因分析とより良い方法の紹介を行う書籍。 論理設計、物理設計、クエリの記述、アプリケーション開発という4つのテーマに分かれている。
論理設計のアンチパターン
１．Jaywalk（信号無視）
多対多の関連を表すために交差テーブルを作るのを避け、カンマ区切りリストの文字列をテーブルに格納してしまうパターン。
問題点
- 検索、集約、更新が複雑
- 妥当性検証が困難
- 区切り文字の選択
- リストの長さに限界がある
改善案
- 交差テーブルを作る
２．Naive Tree（素朴な木）
木の表現に必ず隣接リストを使ってしまうパターン。
問題点
- (再帰クエリが使えない場合)定数深さまでの探索しか行えない
改善案
頻繁に使う操作の種類に応じて他の木構造を使う
備考
執筆当時は再帰クエリがまだ完全に普及していなかったため隣接リストがアンチパターンとされているのだろう。 現在では遅れていたMySQLやSQLiteでも再帰クエリが実装されているため、隣接リストを使うのはごく一般的で優れた手法の一つといってもいいと思われる。
３．ID Required（とりあえずID）
すべてのテーブルに「id」という列で疑似キー（代理キー）を作ってしまうパターン。
問題点
- 既に自然キーが存在する場合は、加えた疑似キーが冗長になってしまう
- 元々の自然キーに関して重複を許してしまう（UNIQUE制約を付けることもできるが、そうしたらいよいよ疑似キーの意味が無い）
- 列の意味が分かりにくい
- 結合に際してUSINGを使えない
改善案
状況に応じて適切に調整する。疑似キーが必要なケースもある（自然キーが極端に長い場合や、ORMが自動で作成する場合など）。ただし名前は分かりやすいものにする。
主キーは制約であってデータ型ではない。必ずしも自動インクリメントする整数型である必要はない。
４．Keyless Entry（外部キー嫌い）
外部キー制約を、以下の理由を根拠に付けないパターン。
- データの更新が制約と衝突する。
- 設計の柔軟性が高く、適切な制約を作れない。
- 外部キー制約で作られるインデックスがパフォーマンスに影響すると考える。
- 外部キーで関連しあう2つの列を更新したいときに面倒。
問題点
- DB側で省略したら、品質管理スクリプトは今度は自前で書かなければいけなくなる
- 更新が衝突するリスクもあるし、検出したミスをどう直すのかという問題もある
改善案
外部キー制約を宣言する
- 制約宣言時にON UPDATE CASCADEを用いることで親テーブルの変更が子に自動反映される。他にもON DELETEもある
- 外部キーによってオーバヘッドは生じるが、それは品質管理を別に行った際に必要になるコストよりもはるかに少ない
- データの整合性を維持するために最低限必要なコストということ
５．Entity Attribute Value（エンティティ・アトリビュート・バリュー）
可変個数の属性を表すために、属性名と属性値の組を行にしたテーブルを作成してしまうパターン。 共通の属性と独自の属性を持つ2種類のデータ(ex. Issueを基底としたBugとFeatureRequest)を表現したいときなどに現れうる。
問題点
本質的に、メタデータ（列名）がデータ（属性名の文字列値）に混入してしまっていることが問題である。
- 検索が複雑
- 1行として表示したいとき、結合を属性数だけ行う必要がある
- 整合性の維持が困難
- 必須な属性を指定できない
- 全ての属性値に同じ列が使われるため、データ型を指定できない
- 外部キー制約を作れない
- 属性名のミス修正が困難
改善案
非リレーショナルなデータ管理をしたいならそれ用の技術を使うべき。KVS、ドキュメント指向、列指向データベースなど。
RDBが必要なら、以下の方法がある。
- シングルテーブル継承
- 使いうる属性を全て列挙したテーブルを作成し、その行がどのサブタイプを表現しているか表すtype列で識別する。
- サブタイプ数や属性数が少なく、Active Recordのような単一テーブルアクセスを行いたい場合に優れる
- 具象テーブル継承
- サブタイプごとに個別のテーブルを作成する。
- タイプ間をまたいだ検索をあまり行わない場合に優れる
- クラステーブル継承
- 共通属性を持つ基底テーブルを作り、サブタイプごとに追加属性を持つテーブルを作る。
- 共通属性の検索を行う場合に優れる
- 半構造化データ
６．Polymorphic Association（ポリモーフィック関連）
複数の親テーブルを外部キー制約で参照したいが不可能なため、外部キー列に加えて参照している親テーブルの名前を格納して無理やり実現するパターン。
問題点
5と同様、メタデータがデータに混入してしまっている。
外部キー制約を付けられないため、参照整合性に関する問題が噴出する。
改善案
参照関係が逆になっているので、それを直す
- 交差テーブルを作成する
- 共通の親テーブルを作成してそれを参照する
７．Multicolumn Attribute（複数列属性）
複数の値を持つ属性を格納するため、必要な数だけ列の数を増やしてしまうパターン。
問題点
- 値の検索、更新、削除の際、それらすべての列を確認しなければならない
- 一意性制約が効かない
- 格納したい数が増えたときの対応に困る
改善案
従属テーブルを作成する。アカウントに複数の電話番号を関連付けたいなら、アカウントを参照する電話番号テーブルを作ればよい。
８．Metadata Tribble（メタデータ大増殖）
スケーラビリティを高めるため、テーブルや列を複数個に手動で分割してしまうパターン。
問題点
本質的には、５・６と逆でデータ（年度など）がメタデータ（テーブル名・列名）に混入してしまうことが問題である。
- 増殖するテーブルに対する管理やクエリ実行が大変
- テーブルをまたいだ整合性や一意性の管理は困難
- データの同期が大変
改善案
- 水平パーティショニング・垂直パーティショニングといったDBMSにサポートされている方法を用いる。
- 従属テーブルを導入する
物理設計のアンチパターン
９．Rounding Error（丸め誤差）
正確な値が必要な小数計算でfloat型を使ってしまうパターン。
問題点
浮動小数点型は有限精度10進数を必ずしも有限精度で表現できない。
改善案
SQLには固定小数点のNUMERIC/DECIMAL型があり、桁数と小数点第何位まで正確に表すか指定できる。
１０．31 Flavors（31のフレーバー）
とりえる値が限定されている列（要はenum型）を表すため、CHECK制約の定義に値を列挙してしまうパターン。
問題点
- 有効値の列挙が困難
- 有効値の追加や変更が困難
- ENUM型やユーザ定義型を使っている場合移植が困難
改善案
有効値リストのテーブルを別に作り、そこを外部キー制約で参照する
１１．Phantom File（幻のファイル）
画像などのファイルを取り扱う際、ファイル自体は外部ファイルシステムに、テーブルにはそこへのパスを格納するパターン。
問題点
ファイル自体がDBMSの範疇外にあることに起因する問題
ただし、テーブルのサイズが小さく保たれパフォーマンスが維持しやすい、外部ファイルの一括編集が行いやすいなどの利点もある
改善案
必要に応じてBLOB型にファイルを格納する
１２．Index Shotgun（闇雲インデックス）
インデックスを粗雑に決め打ちしてパフォーマンスが改善されないパターン。
問題点
- インデックスを定義しないなら全ての計算が全探索になってしまう
- 使わない列にも定義してしまうとオーバヘッドだけが膨らむ
改善案
MENTORの原則に基づいてインデックスを管理する
- Measure
- Explain
- Nominate
- Test
- Optimize
- Rebuild
クエリのアンチパターン
１３．Fear of the Unknown（恐怖のunknown）
NULLを値として扱ってしまう、もしくは値をNULLとして扱ってしまうパターン。
NULLが存在しうる列への演算は気を遣わなければならないし、逆に欠損値を表すためにNULL以外の値を使ってしまうことには弊害が生じる。 NULLを避けるのではなく、一意な値として適切に取り扱うこと。
１４．Ambiguous Group（曖昧なグループ）
GROUP BYを用いた際、集約する属性に対する関数従属性を持たない属性を素でSELECTしてしまうパターン。
エラーになったり信頼性の低い結果を返したりする。 単一値の原則に従って集約関数を適切に適用するとよい。
１５．Random Selection（ランダムセレクション）
サンプル行をランダムにフェッチしたいとき、データをランダムソートしたあとに先頭の行を取得するパターン。
問題点
データが多いときに遅い。少ないときはさほど問題でない。
改善案
- 最大値以下のランダムなキー値を選択してフェッチ
- キー値が連続している場合に有効
- ↑と同じだが、欠番がある場合にはその直後のキー値を選択
- 欠番を許すが、厳密に一様なサンプリングができない
- キー値のリストを受け取りランダムに1つを選択する
- キー値が整数である必要すらないがリストサイズが大きくなりやすい。クエリも二度手間
- ROW_NUMBER()と、0と行数の間から取った乱数が一致する行、という条件でフェッチする
- ウィンドウ関数が広くサポートされている現在は確率が一様かつパフォーマンスも良いため最良の選択肢かもしれない
１６．Poorman's Search Engine（貧者のサーチエンジン）
全文検索を行うためにパターンマッチ述語（LIKEや正規表現）を使って頑張ってしまうパターン。
パフォーマンス、スケーラビリティ、処理の煩雑さなどから良い方法とは言い難い。
代わりに専用の全文検索エンジンを構築するのが最善。次善として、反復のコストを減らすために検索結果をキャッシュする。
１７．Spaghetti Query（スパゲッティクエリ）
SQLクエリの数を減らすため、複雑な問題をできるだけ少ない数のSQLクエリに詰め込んで解こうとするパターン。
困難は分割せよ。
１８．Implicit Column（暗黙の列）
タイプ数を減らすため、SELECTやUPDATEにワイルドカードを濫用してしまうパターン。
問題点
- 列の追加・削除・順番変更に際してエラーが発生する
- 全ての列を取得することでパフォーマンスが悪化する
改善案
基本的にすべてのケースにおいて、列名を明示的に指定する
パフォーマンスの最適化にもなるし、ミスの防止にもなる
アプリケーション開発のアンチパターン
１９．Readable Password（読み取り可能パスワード）
パスワードのリカバリを可能にするためにテーブルにパスワード平文を格納してしまうパターン。
問題点
セキュリティリスク。
改善案
- 処理の流れ
- 高度なセキュリティが必要ならPBKDF2やBcryptなどの技術の採用も検討
備考
現在ではpassword+salt+SHA256も必ずしも安全ではないため注意が必要。
２０．SQL Injection
未検証の入力を動的SQLに埋め込んでコードとして実行してしまうパターン。
問題点
セキュリティリスク。
改善案
以下の技法を適切に使い分ける
- 入力のフィルタリング、サニタイジング
- プリペアドステートメントを用いた動的パラメータのクエリからの分離
- 引用符で囲んだ動的値をSQLに直接埋め込む
- オプティマイザを効かせたい場合の極端な選択でセキュリティリスクに注意
- ユーザ入力をコードから隔離する
- コードレビューしてもらう
２１．Pseudo-key Neat-freak（疑似キー潔癖症）
疑似キーに欠損値があってはならないと感じて番号の振り直しを行ってしまうパターン。
疑似キーの値自体に意味はないし、トランザクション分離の問題が生じるし、振り直し前のデータとの不一致が生じるため問題。 欠番は埋めようとしないこと。ナンバリングしたいならROW_NUMBERがあるし、UUIDを用いれば欠損値を感じずに済む。
２２．See No Evil（臭いものに蓋）
コードを簡潔にするため例外処理を実装しないパターン。
２３．Diplomatic Immunity（外交特権）
データベースに関してだけ、ソフトウェア開発に関するベストプラクティスを採用しないパターン。
改善案
- 文書化
- ER図、テーブル・列の説明、関連、トリガ、ストアドプロシージャ、権限、インフラ、ORMなど
- バージョン管理
- テスト
２４．Magic Beans（魔法の豆）
MVCモデルにおいて、M（モデル）を単純化し過ぎてアクティブレコードそのものになってしまうパターン。
問題点
- コントローラに似たようなデータベース操作コードを何度も書くことになる。
- コントローラとアクティブレコード間の依存性が高まり密結合なコードになる。
改善案
モデルクラスがアクティブレコードを持ち、必要な操作をそれぞれメソッドとして持つようにする。
カプセル化・疎結合・高凝集性が達成され、またスタブやモックによるテストが行いやすくなる。
２５．砂の城
災害・不具合時の想定不足。
ベンチマーク・テスト環境の構築・例外処理・バックアップ・高可用性の担保・運用ポリシーの策定などで対処。
総括
内容の共通点から観察されるように、『データベース実践入門』など、後続のその他書籍がこの書籍に影響を受けていることが分かった。
個別の事案を列挙し、陥る良くないパターンとその処方箋を提示する形式が、のちの参照も楽であるため優れている。
