『SQLアンチパターン』を読みました。
https://www.oreilly.co.jp/books/9784873115894/
せっかくなので内容を残しておきます。
『SQLアンチパターン』とは
DB設計やSQLクエリでやりがちな悪手＝アンチパターンを取り上げ、それぞれに対して具体的な問題点と解決法を紹介する書籍。
個人的に重要だと感じた部分
1. データベース論理設計のアンチパターン（1～8章）
2章 ナイーブツリー
Twitterのツイートにリプライがついて、そこにさらにリプライがついて……のような深さが無制限の木構造を、ノードに親ノードのIDを持たせる単純なテーブル構造で表すと、あるノードの子孫ノード全てを取るようなSQLが書けないというアンチパターン。
代替の設計として3つのモデルが紹介されていましたが、それぞれ一長一短なので、場面によって使い分けが必要です。
ただし、WITH句で再帰クエリを書けるRDBMSでは、親IDを持つ単純な構造でも上手くクエリを書くことができます。
書籍にはMySQLやSQLiteではサポートされていないとありますが、いずれも2020年現在の最新バージョンでは実装済みのようです。
3章 IDリクワイアド
自然キーを利用できるはずのテーブルにもidのようなカラムを持たせてしまうアンチパターン。
個人的にWebアプリ開発はrailsから入ったので、idというカラムがあるのが当たり前だと思っていた節がありましたが、確かに交差テーブルにまでidがあるのは過剰ですね。
2. データベース物理設計のアンチパターン（9～12章）
10章 サーティワンフレーバー
例えば「進行中」「完了」「保留」のような文字列が入る属性列を作る場合、ただの文字列型の列だと「東京」のような関係ない言葉も入れられてしまいます。
CHECK制約を使うと変更に弱かったり、RDBMS間で仕様が異なるので移植しにくいという問題があるのでアンチパターンとされています（ただしデータの選択肢に確実に変更がないならあり）。
利用可能なデータを別のテーブルに格納し外部キー制約を掛けるのが解決法で、これなら追加もINSERT文だけで良く、論理削除を使えば既存のレコードでは使えるが新規では使えないなど、柔軟な対応も可能です。
11章 ファントムファイル
画像などのファイルをDBのカラムとして保存するか外部ファイルとして保存するか問題。
この本の筆者は以下の理由からDBに保存することを推奨しています。
- レコードの削除時にファイルは自動的に削除されない
- トランザクション分離ができない
- ファイルの削除後にロールバックしても元に戻らない
- バックアップを別で取る必要がある
- DBのバックアップとファイルのバックアップの間に更新される可能性もある
- SQLのアクセス権限がファイルには適用されない
- 外部キー制約のように存在するファイルへのパスのみを格納させることができない
3. クエリのアンチパターン（13～18章）
14章 アンビギュアスグループ
GROUP BY句に出てこない列はSELECTできません。それが必要な場合はサブクエリやJOINを上手く使って解決します。
なんですが、最近のPostgreSQLではGROUP BYに主キーを指定すると、そのテーブルの他の値も一意で決まるためSELECTできます（参考）。
新しいPostgreSQLで開発したアプリを古い環境で動かそうとしてハマったことがあります……
15章 ランダムセレクション
レコードをランダムに1行取ってくる、といえば当然のようにORDER BY RANDOM() LIMIT 1だと思っていましたが、これはアンチパターンらしいです。
確かに言われてみればORDER BY RANDOM()ってコスト高そう……
16章 プアマンズ・サーチエンジン
LIKE検索や基本的な正規表現での検索はパフォーマンスもよくないし、単語単位での検索もできない場合があるので、本格的な検索は専用の全文検索エンジンに任せます。
各RDBMSの標準機能や拡張機能を使ったり、DBとは別の検索エンジンを利用する方法が紹介されています。最近だとElasticsearchとかも候補に上がるんでしょうかね。
4. アプリケーション開発のアンチパターン（19～25章）
22章 シー・ノー・エビル
DBエラーのデバッグ時に、SQLそのものでなくSQLを組み立てるコードの方から見てしまうアンチパターン。
仕事でエラー調査を行うこともありますが、エラー発生時には問題のSQLがメールで送信される仕組みになっています。もしどんなSQLでエラーが発生しているかわからなければ、調査の難易度は格段に上がるというのは感覚的にもわかります。