はじめに

今回も書籍のまとめです。
積ん読になっていたSQLアンチパターンをちゃんと読むことにしました。

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行取得する
```
SELECT * FROM Bugs ORDER BY RAND() LIMIT 1;
```
- データ量が増えると、クエリのパフォーマンスが低下してしまう

解決策としては、特定の順番に依存しないようにする

1と最大値の間のランダムなキー値を選択する

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;

欠番の穴の後にあるキー値を選択する

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章. プアマンズ・サーチエンジン（貧者のサーチエンジン）

文字列比較のためのパターンマッチ記述を使ってはいけない
- インデックスが効かずスロークエリになってしまう

  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を用いたアプリケーションでは、適切な例外処理を実装しておくことが必須
- バックアップ
  - データが破壊された際、サービスの最後の生命線となるのがバックアップ
- 高可用性
  - マシンを冗長化する仕組みを考えておく
- ディザスタリカバリ
  - 災害や故障などでデータセンターが使用不可になった場合、他のデータセンターで処理を引き継げるように考慮しておく
- 運用ポリシーの策定

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

はじめに

1章. ジェイウォーク（信号無視）

2章. ナイーブツリー（素朴な木）

3章. IDリクワイアド（とりあえずID）

4章. キーレスエントリ（外部キー嫌い）

5章. EAV（エンティティ・アトリビュート・バリュー）

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

7章. マルチカラムアトリビュート（複数列属性）

8章. メタデータトリブル（メタデータ大増殖）

9章. ラウンディングエラー（丸め誤差）

10章. サーティワンフレーバー（31のフレーバー）

11章. ファントムファイル（幻のファイル）

12章. インデックスショットガン（闇雲ショットガン）

13章. フィア・オブ・ジ・アンノウン（恐怖のunknown）

14章. アンビギュアスグループ（曖昧なグループ）

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

16章. プアマンズ・サーチエンジン（貧者のサーチエンジン）

17章. スパゲッティクエリ

18章. インプリシットカラム（暗黙の列）

19章. リーダブルパスワード（読み取り可能パスワード）

20章. SQLインジェクション

21章. シュードキー・ニートフリーク（疑似キー潔癖症）

22章. シー・ノー・エビル（臭いものに蓋）

23章. ディプロマティック・イミュニティ（外交特権）

24章. マジックビーンズ（魔法の豆）

25章. 砂の城