『SQL アンチパターン』を流し読みしました。私はデータベースのスペシャリストではないこともあり、正直なところデータベースを適切に使えていなかった場合があるように思います。章題と、そこで学んだことを書いていきます。
書籍全体の紹介としては、以下のような記事が既にありますので、この記事は個人的に勉強になったと思った部分のみ取り上げています。
はじめに
データベースと関係ないのですが、indexes と indices の使い分けについて、初めて知りました。
データベース関連の用語として用いられる場合、index は順番に並べられた情報の集合を意味します。この場合の index の望ましい複数形は indexes です。他の文脈では、index は indicator を意味することがあり、この場合の複数形は indices です。
Kotlin で添え字の複数形として indices を使いますが、添え字は indicator ということでしょうね。
2章 ナイーブツリー (素朴な木)
木構造の表現方法にはいくつかありますが、著者は隣接リスト (Adjacency List) モデルを素朴な木としてアンチパターンとしています。紹介されているモデルについて、少し補いつつ利点と欠点を列挙してみました。
隣接リスト
利点
- 開発者の多くが知っていて理解しやすい
- 葉の追加が容易
- ノードやサブツリーの移動が容易
- 直近の親や子の検索が容易
- 刊行当時からサポートされていた SQL Server、Oracle (11g)、DB2、PostgreSQL に加え、SQLite 3.8.3 以降、MySQL 8 など、当時サポートされていなかった処理系でも WITH RECURSIVE でサブツリーをクエリーできる
欠点
- 非葉ノードの削除（サブツリー全体の削除や子ノードの昇格）が困難
- WITH RECURSIVE は読み書きしづらい
- MySQL 5 のような WITH RECURSIVE を使えない処理系では SQL で深さ制限のないクエリーを書けない
経路列挙 (Path Enumeration)
利点
- 子孫や先祖を LIKE 述語を使って、隣接リストよりも簡単かつ高速にクエリーできる
- 葉の追加が簡単
- セパレーター間の要素の長さが一定ならば、深さ優先探索のような順序に簡単にソートできる
「他の行を修正せずに、非葉ノードを挿入できます」とされていましたが、どこかの親子関係の間に新しくノードを追加する際には既存の経路列を更新する必要があるので誤りのように思います。サブツリーの新規追加時のことを言っているのだとすれば誤りではありませんが。
欠点
- 経路の正確な形成を保証できない
- VARCHAR 列の長さに制限がある
入れ子集合 (Nested Set)
利点
- 非葉ノードを削除すると、削除されたノードの子孫が自動的に昇格する
欠点
- ノードの挿入や移動などのツリー操作が他のモデルよりも複雑
- 直近の親の特定が、外部結合を用いた、やや複雑なクエリーになる
個々のノードの操作ではなく、サブツリーに対する迅速かつ容易なクエリー実行が重要な場合に適しているそうです。
閉包テーブル (Closure Table)
隣接リストのように直接の親子関係だけを格納するのではなく、閉包テーブルに先祖-子孫関係を全部入れるモデルです。
利点
- 先祖や子孫の取得が簡単
- 葉ノードの挿入や削除も比較的簡単
- サブツリー全体の削除も簡単
- 組織図の従業員のように、ノードの関連付けの変更が簡単
- 入れ子集合よりもシンプル
- 閉包テーブルに経路長の列を追加すれば、直近の子や親へのクエリーも簡単
利点しか紹介されていませんでした！
どの設計を使うべきか
まとめの表を書籍より引用します。以下の再帰クエリは、WITH RECURSIVE がサポートされている場合の隣接リスト モデルのことです。
表 2-5 階層的なデータ設計の比較
設計 テーブル数 子へのクエリ実行 ツリーへのクエリ実行 挿入 削除 参照整合性維持 隣接リスト 1 簡単 難しい 簡単 簡単 可能 再帰クエリ 1 簡単 簡単 簡単 簡単 可能 経路列挙 1 簡単 簡単 簡単 簡単 不可 入れ子集合 1 難しい 難しい 難しい 難しい 不可 閉包テーブル 2 簡単 簡単 簡単 簡単 可能
再帰クエリーの読み書きしづらさを考えると、木をクエリーするなら閉包テーブルがわかりやすさと速度のバランスが良いのかなと思わせる表ですが、行数が多くなりすぎる恐れはありますね。簡単とされている操作でも、場合によっては隣接リストほど簡単ではないような気もします。
循環があるグラフを扱う際に隣接リスト以外の効率的な表現があるのか疑問に思いました。「グラフ構造も対象にした書籍に Vadim Tropashko 著『SQL Design Patterns』[Tro06] があります。」と、別の書籍が紹介されていたので、グラフを扱う機会があれば見てみようと思います。あるいは、グラフ DB を使うほうが良いかもしれませんね。
ミック著『SQL 実践入門』でどう扱われていたか
『SQL 実践入門』では閉包テーブルは紹介されていませんでしたので、大きく隣接リストの一種とされているのだろうと思います。経路列挙モデルは「更新がほとんど発生しないケースで力を発揮する」とされていました。その上で、入れ子集合モデルで先祖をクエリーする例が紹介されていました。入れ子集合モデルは理解がやや難しいにも関わらず紹介されているのは、上で紹介した表のように単純に閉包テーブルを選べば良いわけではなく、要件に応じた使い分けが必要とされているということなのだろうと思います。
紹介されていないモデル
上記記事のコメントで、Fertile Forest Model というものが紹介されていました。
3章 ID リクワイアド (とりあえず ID)
「3.2.4 USING を使用する」という節があり、USING の使用をアンチパターンとされているのか気になったのですが、そういうわけではなく、むしろ全テーブルで共通の「id」という列を使うのを避け「bug_id」のように何の ID か明示することで USING を使って簡潔に書けると推奨されていました。
なぜか周囲で USING を使うのが自分だけなのですが、NATURAL JOIN のようにうっかり主キーではない共通列で結合してしまうことがなく、NATURAL JOIN 同様に SELECT * で余計な列が出なくなるので、もう少し使われても良い構文だと思っています。
4章 キーレスエントリ (外部キー嫌い)
私は外部キーを設定する主義なのですが、使わない場合もあると聞いたことがあります。本書でアンチパターンを用いてもよい場合としては、外部キーを用いた関連付けを行えない、極端に柔軟なデータベース設計を扱わなければならない場合が挙げられていました。
17章 スパゲッティクエリ
本書では1つの複雑なクエリーで結果を出そうとすることを戒めています。
複雑な1つのクエリーで解決すべきでない理由
- うっかり CROSS JOIN を発生させがち
- クエリーの記述や修正、デバッグが難しくなる
とは言っても、分析クエリーは複雑になりがちですよね。折衷案としては、WITH に分割したクエリーを書き、最終的に一つの SELECT 文にする方法があるだろうと思います。
18章 インプリシットカラム (暗黙の列)
タイプ数を減らそうとして、INSERT 文に列を指定しなかったり、SELECT * の結果から n 番目の列を取得したりしていると、ALTER TABLE ADD/DROP COLUMN を実行した後に動かなくなったり違う結果を返すようになったりしてしまう欠点があるとのことです。
本書では「ワイルドカードの使用は、アドホックな SQL を素早く書きたい場合には妥当」とされていました。
清書するクエリーではコーディング規約などで使用を禁じられていることが多いかと思いますが、それ以外の場面では、今どちらが該当する状況なのか、適切に見分ける必要がありそうです。
20章 SQL インジェクション
以下の一つの技法では不足するので、適切に使い分けなくてはならないとのことです。
- 値のエスケープ
- プリペアド ステートメント
- ストアド プロシージャー
- データ アクセス フレームワーク
また、以下の技法を全て習得し、状況に応じて適切に使い分ける必要があるとのことです。
- 入力のフィルタリング
- 動的値のパラメーター化
- 動的値を引用符で囲む
- ユーザーの入力をコードから隔離する
- 他の開発者にコードをレビューしてもらう
監訳注として、「安全な SQL の呼び出し方」が紹介されていました。2023 年現在もこれが最新版のようです。
24章 マジックビーンズ (魔法の豆)
MVC の全てのモデル クラスがアクティブ レコードの基底クラスを継承するのは「『黄金のハンマー (Golden Hammer)』アンチパターン [BMMM98] の例」とされています。『エリック・エヴァンスのドメイン駆動設計』が紹介されていました。MVC モデルのプログラムを書く機会が少ないせいかちょっと理解できなかったので、今後そのような機会があったら読み直したいと思います。
自分ならやらないと思う章が多かったり、最後の方は疲れてしまったりと、かなりの斜め読みになってしまいました。必要になったらきちんと読み直さないといけないですね。SQL を適切に使うことの難しさを改めて感じました。
原著のほうは、SQL Antipatterns, Volume 1 が 2022 年に出ていました。2分冊でしょうか。最新の情報で更新されていると思いますので、きちんと読み直す際には原著の購入も検討したいと思います。