「SQLアンチパターン」まとめ

# はじめに
今回も書籍のまとめです。
積ん読になっていた[SQLアンチパターン](https://www.oreilly.co.jp/books/9784873115894/)をちゃんと読むことにしました。

# 1章. ジェイウォーク（信号無視）
- 1つのカラムに、以下のようにカンマ(`,`)区切りの値を入れてはいけない
- 解決策としては、交差テーブルを作成する

# 2章. ナイーブツリー（素朴な木）
- ツリー状の階層構造を、1テーブルで表現してはいけない
- 解決策としては、代替ツリーモデルを使用する
  - 経路列挙
  - 入れ子集合
  - 閉包テーブル

# 3章. IDリクワイアド（とりあえずID）
- 主キーのカラム名を常に`id`にしない
- 状況に応じてわかりやすいカラム名にする
  - `bug_id`・`product_id`など

# 4章. キーレスエントリ（外部キー嫌い）
- 外部キー制約の目的は、DBのアーキテクチャを単純化すること
- 外部キー制約を使わないと、参照整合性を保証するコードを書く責任が生じる

# 5章. EAV（エンティティ・アトリビュート・バリュー）
- 汎用的な属性テーブルを使用してはいけない
- 属性名をレコードに持たせる以下のようなテーブル

| issue_id | attr_name | attr_value |
| -------- | --------- | ---------- |
| 1        | title     | SQL Antipatterns |
| 1        | year_of_publication| 2013 |
| 1        | author | Bill Karwin    |
- 解決策として、以下の方法を検討する
  - シングルテーブル継承
  - 具象テーブル継承
  - クラステーブル継承
  - 半構造化データ

# 6章. ポリモーフィック関連
- 参照する親テーブルがレコードによって変わる場合、ポリモーフィック関連を使ってはいけない
- 外部キーによる参照整合性制約を定義できなくなるため

| comment_id | issue_type | issue_id | comment |
| ---------- | ---------- | -------- | ------- |
| 1          | Bugs       |1         | クラッシュします |
| 2          | FeatureRequests | 7 | 良いアイデア |

- 解決策として、以下を検討する
  - 交差テーブルの作成
  - 共通の親テーブルの作成

# 7章. マルチカラムアトリビュート（複数列属性）
- `1章. ジェイウォーク（信号無視）`と同じテーマの問題
- １つのテーブルに属するべき属性に複数の値がある場合、`tag1`・`tag2`・`tag3`というように同じカラムを増やしてはいけない
- 従属テーブルを作れば良い

# 8章. メタデータトリブル（メタデータ大増殖）
- `Bugs_2008`・`Bugs_2009`・`Bugs_2010`というように、同じ構成のテーブルを年や月ごとに複数作ってはいけない
- 解決策としては、パーティショニングと正規化を行う
  - 水平パーティショニング(シャーディング)
  - 垂直パーティショニング
  - 従属テーブルの導入

# 9章. ラウンディングエラー（丸め誤差）
- 小数を扱う際に、`FLOAT`型や`DOUBLE`型を使うと正確な値を保持できない
  - 3分の1のような有理数は、0.3333…のような循環小数となり、無限精度が必要になる
- 正確な値を保持するには、`NUMERIC`型、または`DECIMAL`型を用いて固定精度の小数点数を表すようにする

# 10章. サーティワンフレーバー（31のフレーバー）
- ENUMのように限定する値を列定義で指定してはいけない
- しかし、限定する値が変わらない場合はENUMの採用は適している
  - 例えば`左/右`・`有効/無効`・`オン/オフ`を指定する場合
- 解決策としては、限定する値をデータで指定する
  - 参照テーブル`BugStatus`を作成し、`Bugs.status`に外部キー制約で参照する

# 11章. ファントムファイル（幻のファイル）
- 画像などのメディアファイルを物理ファイルとして保持するのではなく、データベースに`BLOB`型として保持する方法もある
- MySQLには、最大16MBのデータを格納できる`MEDIUMBLOB`型がある
- Oracleにも、最大2GBの`LONG RAW`型や、最大128TB(10.1以降)の`BLOB`型がある

# 12章. インデックスショットガン（闇雲ショットガン）
- 闇雲にインデックスを使ってはいけない
- 全くインデックスを使わないのもいけない
- **MENTORの原則**に基づいて、効果的なインデックス管理を行うようにする
  - Measure(測定)
  - Explain(解析)
  - Nominate(指名)
  - Test(テスト)
  - Optimize(最適化)
  - Rebuild(再構築)

# 13章. フィア・オブ・ジ・アンノウン（恐怖のunknown）
- NULLを一般値として使う、または一般値をNULLとして使ってはいけない
- `IS NULL`・`IS NOT NULL`を使って検索を行うようにする

# 14章. アンビギュアスグループ（曖昧なグループ）
- グループ内の最大値（または最小値や平均値）を持つ行を取得する時に、その行のグループ化されていない他の属性まで取得してはいけない
- **単一値の原則(Single-Value Rule)**を守る
  - SELECT句の選択リストに列挙される全ての列は、行グループごとに単一の値の行でなければならない

# 15章. ランダムセレクション
- データをランダムにソートしてはいけない
  - ランダムにソートを行い、最初の行を1行取得する

    ```sql
    SELECT * FROM Bugs ORDER BY RAND() LIMIT 1;
    ```
  - データ量が増えると、クエリのパフォーマンスが低下してしまう
- 解決策としては、特定の順番に依存しないようにする
  - 1と最大値の間のランダムなキー値を選択する

    ```sql
    SELECT b1.*
    FROM Bugs AS b1
    INNER JOIN (
      SELECT CIEL(RAND() * (SELECT MAX(bug_id) FROM Bugs)) AS rand_id
    ) AS b2 ON b1.bug_id = b2.rand_id;
    ```
  - 欠番の穴の後にあるキー値を選択する
 
    ```sql
    SELECT b1.* 
    FROM Bugs AS b1
    INNER JOIN (
      SELECT CEIL(RAND() * (SELECT MAX(bug_id) FROM Bugs)) AS bug_id
    ) AS b2 ON b1.bug_id >= b2.bug_id
    ORDER BY b1.bug_id LIMIT 1;
    ```

# 16章. プアマンズ・サーチエンジン（貧者のサーチエンジン）
- 文字列比較のためのパターンマッチ記述を使ってはいけない
  - インデックスが効かずスロークエリになってしまう

  ```sql
  SELECT * FROM Bugs WHERE descroption LIKE '%crash%';
  ```
- MySQLならフルテキストインデックスを使う
  - MATCH関数
- Oracleもテキストインデックスを使う
  - CONTAINTS演算子

# 17章. スパゲッティクエリ
- 複雑な問題を1ステップ（1クエリ）で解決しようとしてはいけない
- 複雑な問題は、ワンステップずつ複数のクエリに分割して解決する

# 18章. インプリシットカラム（暗黙の列）
- カラムをワイルドカード`*`で指定してはいけない
- カラム名を明示的に指定するようにする

# 19章. リーダブルパスワード（読み取り可能パスワード）
- パスワードは平文で格納してはいけない
- ソルトを付けてパスワードハッシュを格納するようにする

# 20章. SQLインジェクション
- 動的SQLを記述する際、アプリケーションから送られてくる値をそのまま実行してはいけない
- プリペアドステートメントを使うのが最善策

# 21章. シュードキー・ニートフリーク（疑似キー潔癖症）
- IDなどの主キーをAUTOINCREMENTで連番にしている場合、欠番を埋めようとしてはいけない
- 主キーは各行を識別するためのものだが、ルールはそれだけなので連続している必要はない

# 22章. シー・ノー・エビル（臭いものに蓋）
- 戻り値やステータスコードを見逃してはいけない
- 戻り値と例外のチェックを行なって、ステップに水がないことを保証するようにする

# 23章. ディプロマティック・イミュニティ（外交特権）
- データベースもアプリケーションコードと同様に、**品質保証(Quality Assurance: QA)**に取り組むべきである
- **品質保証(Quality Assurance: QA)**は、以下の3つから成り立つ
  - プロジェクト要件の明確な定義・文書化
  - 要件に対する解決策の設計・構築
  - 解決策が要件を満たしていることの確認・テスト

# 24章. マジックビーンズ（魔法の豆）
- `model`が`ActiveRecord`そのものになってはいけない
  - `ActiveRecord`は`model`をデータベーススキーマに強く依存させてしまう
  - `ActiveRecord`はCRUD機能を公開してしまう
  - `ActiveRecord`はドメインモデル貧血症をもたらす
- 解決策としては、`model`が`ActiveRecord`を「持つ」ようにする

# 25章. 砂の城
- サービスを安定稼働させるために、性能問題や障害が発生した時の対処を想定しておく
  - ベンチマーク
     - どの程度まで処理可能かをベンチマークしておく
  - テスト環境の構築
     - 本番環境で使用しているものと同じものを1セット用意する
  - 例外処理
     - DBMSを用いたアプリケーションでは、適切な例外処理を実装しておくことが必須
  - バックアップ
     - データが破壊された際、サービスの最後の生命線となるのがバックアップ
  - 高可用性
     - マシンを冗長化する仕組みを考えておく
  - ディザスタリカバリ
     - 災害や故障などでデータセンターが使用不可になった場合、他のデータセンターで処理を引き継げるように考慮しておく
  - 運用ポリシーの策定