SQLアンチパターンを読みました

この本の目的

• この本の目的は、SQLを使う必要があるソフトウェア開発者がSQLで効率的にソフトウェアを開発できるようにすること。

前提知識復習

論理設計と物理設計
交差テーブル：中間テーブルのこと

データベース論理設計のアンチパターン

1章 ジェイウォーク(信号無視)

❌ カンマ区切りのフォーマットのリストを格納する
- 特定の製品が検索できなくなる
- JOINに手間がかかる
- COUNT, SUM, AVGなどの関数が使いにくくなる
- 更新しにくくなる
- カンマ区切りのリストの値が無効な入力かどうか検証しづらくなる
- 詰め込めるリストの数に制限ができてしまう

⭕️ 交差テーブル(中間テーブル)を作る

👀 あえてチェックボックスを配列で持たせようかなと思ったけど、やっぱりチェックボックスの項目用のテーブルを作ろう👀

2章 ナイーブツリー(素朴な木)

例えば、ニュースアプリのコメント機能を作るとき…

❌ 常に親のみに依存する
- parent_idを持たせるような設計を「隣接リスト」というが、階層の深さに応じてSQLを固定しなければならない。
- 編集・メンテナンスも大変

⭕️ 代替ツリーモデルを使用する
- 経路列挙モデル(関連するコメントのpathを格納する)
- 入れ子集合モデル(ノードの番号と、階層の深さを一緒に格納する)
- 閉包テーブルモデル(先祖/子孫関係を共有するノードの組み合わせを格納する)

👀 今回、経路列挙モデルが良さそうだと思ったけど、prismaがPostgreSQL のltree 拡張に対応していないらしいのでやめておくことに。さらに、再帰クエリもサポートしていないみたい…？(2024年4月現在)
親子関係を作る構造は、諦めようかな…。

3章 とりあえずID

❌ 「id」という列を持たせる
⭕️ わかりやすい列名にする
bag_id, account_idなどの列名にする

4章 外部キー嫌い

❌ 外部キー制約を使用しない
⭕️ 外部キー制約をつける

5章 エンティティ・アトリビュート・バリュー

本よりこちらの記事の方がわかりやすかったです。
https://note.com/standenglish/n/n4bdb1d5f2a80

6章ポリモーフィック関連

❌ 二重目的の外部キーを使用する(Bugsのコメントと、FeatureRequestsのコメントのコメントを同じテーブルにする、など)
⭕️ リレーションシップを単純化する
- Commentsテーブルから、BugsComments, FeaturesCommentsテーブルの中間テーブルを作る

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

❌ tag1, tag2, tag3のように汎用的な複数の列を定義する
⭕️ 従属テーブルを作成する

8章 メタデータトリブル(メタデータ大繁殖)

❌ スケーラビリティを増やすために、同じ内容のテーブルや列を増やす
⭕️ 水平パーティショニング, 垂直パーティショニング, 従属テーブルの導入

データベース物理設計のアンチパターン

9章 ラウンディングエラー(丸め誤差)

❌ SQLでFLOATを使用する
⭕️ NUMERICデータ型を使用する

10章 サーティワンのフレーバー

❌ さっくり言うと列にENUMの値を直接入れる
⭕️ 参照テーブルを作成し、外部キー制約をつける

11章 ファントムファイル(幻のファイル)

❌ 画像などの大きなファイルを外部に保存し、保存先だけ格納する
- トランザクションが分離されちゃうのが良くないらしい。
⭕️ BLOB型を採用する

12章 インデックスショットガン

❌ 全くインデックスを使用しない / 闇雲にインデックスを使用する
- 主キーのインデックスは自動的に生成される
⭕️ 「MENTOR」の原則に基づいて効果的なインデックス管理を行う
- Measure(測定) … スロークエリログなどを使って、クエリを計測する。
- Explain(解析) … クエリが遅くなっている原因を特定する
- Nominate(指名) … クエリ実行計画を読んで、インデックスを使わないでテーブルにアクセスしている箇所を探す
- Test(テスト) … クエリが早くなったかテストする
- Optimize(最適化) … キャッシュメモリ意識する
- Rebuild(再構築) … 更新や削除の多いテーブルでは、インデックスが不均衡になることがあるので、定期的に再構築する。

クエリのアンチパターン

13章 フィア・オブ・ジ・アンノウン(恐怖のunknown)

❌ 正しく理解しないでNULLを使う
⭕️ NULLを一意の値として使う
- NULLはtrueでもfalseでもない
- NULLの検索はIS NULLを使う
- NOT NULL制約を使う

14章 アンビギュアスグループ(曖昧なグループ)

❌ グループ内の最大値を持つ行を取得する際に、他の行も取得しようとする
⭕️ 曖昧ではない列を使用する
- 関数従属性のある列、相関サブクエリ、導出テーブル、JOINなどなど

15章 ランダムセレクション

❌ ランダムな結果を返すSQLクエリが必要になったときに、データをランダムにソートする
⭕️ 特定の順番に依存しないようにする

16章 プアマンズ・サーチエンジン(貧者のサーチエンジン)

❌ LIKE述語、正規表現を使う
⭕️ 適切なツールを使う
- 全文検索などはベンダー拡張を使う。ドキュメントを読む。
- サードパーティのサーチエンジンを使う。

17章 スパゲッティクエリ

❌複雑な用件をひとつのクエリで書こうとする
⭕ 課題をひとつずつ取得する

18章 ワンショットカラム(暗黙の列)

❌*やワイルドカードも使って、必要のないカラムも取得する
⭕取得する列名も明示的に指定する

アプリケーション開発のアンチパターン

19章 リーダブルパスワード(読み取り可能パスワード)

❌パスワードを平文で格納する
⭕ソルトをつけてパスワードハッシュを格納する

20章 SQLインジェクション

❌ 未検証の入力をコードとして実行する
⭕ 無効な値を取り除く、動的な値をパラメータ化したり、レビューをしてもらって防ぐ

21章 シュードキー・ニートフリーク(疑似キー潔癖症)

❌ 欠番を埋めようとする
⭕️ 整数は余程のことがない限り枯渇しないので欠番は埋めない。GUIDを使用する。

22章 シー・ノー・エビル (臭い物に蓋)

❌ データベースAPIのエラーを無視する。アプリケーション内のSQLしか読まない。
⭕️ 戻り値と例外をチェックする。実際に構築されたSQLクエリを使用する。

23章 ディプロマティック・イミュニティ(外交特権)

❌ DBを(悪い意味で) 特別扱いし、ソースコードのバージョン管理を行わない・テストを書かない・仕様書、ドキュメントを書かない。
⭕️ DBも同じようにER図を書いてドキュメントに残したり、バージョン管理をしたり、テストを書いたりする。

24章 マジックビーンズ(魔法の豆)

❌ モデルがActive Recordそのものになり、データベーススキーマに強く依存させてしまう。コントローラーにビジネスロジックを書いてしまう。
⭕️ モデルがアクティブレコードを持つようにする。オブジェクト指向設計　開発者ガイドラインに従って設計する。

25章 砂の城

❌ 障害への対策を怠る
⭕️ ベンチマーク、テスト、例外処理、バックアップ、高可用性、万が一の際のデータセンターへの引き継ぎ、運用ポリシーの策定

感想

久しぶりに大規模なテーブル設計をすることになったので、読むことにしました。

3年前くらい前から積読リストに載っていたのですが、出産育児もあったので全然本が読めなくて、最近になってようやく読めて嬉しいです！内容もすごく理解できるようになってました😭

今の業務には関係ないなって箇所は、インデックスだけ増やせるようにさらっと読みました。

随時更新したいと思います✌️