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

はじめに

• YUZURIHAの松村です。
• 久しぶりに書籍のまとめになります。
• SQLアンチパターンの第2版が出版されたので、購入しました。
• 個人的には初版もしっかり読んでおり業務でも役立つことがあるため、初版から第2版でどのように変わったのかを中心にまとめて行きたいと思います。
• なお、今回も購入にはmiiveという弊社で導入している福利厚生サービスを活用しています。

miiveとは

• miiveとはプリペイド式のクレジットカードで、「食事」または「学び」に使用できるポイントが毎月3,500円分支給されます。
• 私は主に書籍の購入で活用していますが、毎月書籍購入で3,500円分の補助があるのは本当にありがたいです！
• 弊社でのmiive導入については以下の記事で詳しく紹介しているので、興味のある方は是非ご覧ください。

1章. アンチパターンとは何か?

• 第2版で新たに追加された章になります。
• 本題に入る前に、そもそも「アンチパターンとは何か？」から丁寧に説明してくれています。
• アンチパターンには、大きく分けると以下4つのカテゴリーがあります。
  • データベース論理設計のアンチパターン
    • テーブルや列、リレーションシップの設計など
  • データベース物理設計のアンチパターン
    • インデックスの定義、データ型の決定など
    • DDL(データ定義言語)を使う
  • クエリのアンチパターン
    • SLECT、UPDATE、DELETEなど
    • DML(データ操作言語)を使う
  • アプリケーション開発のアンチパターン
    • 他の言語(Python、Java、Cなど)で開発されたアプリケーションとともにSQLを使う
• 2章目以降は、これらのカテゴリーごとの4部構成となっています。

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

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

• 1つのカラムに、カンマ(,)区切りの値を入れるアンチパターンです。
• 交差テーブルを作成することが解決策になります。

ミニ・アンチパターン：CSV列を複数の行に分割する

• ※ 第2版では、新たにミニ・アンチパターンが追加されている章があります。
• CSV形式の文字列を複数行に分割して、あたかも最初から1行に1つの値で格納されていたかのように見せかけると、データの表示方法を変更する必要があります。

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

• ツリー状の階層構造を、1テーブルで表現するアンチパターンです。
• 解決策としては、代替ツリーモデルを使用する以下4つの方法があります。
  • 再帰クエリ
  • 経路列挙
  • 入れ子集合
  • 閉包テーブル

隣接リストと再帰クエリ

• 隣接リストとは、各レコードが「自分の親はどれか」というparent_idを持つことによって親子関係を管理する方法です。
• この隣接リストで格納された階層をサポートするデータベースの機能として、再帰クエリがあります。
• 初版が刊行された2013年当時は、この再帰クエリが実装されているデータベースはまだ広く普及していませんでした。
• しかし第2版が刊行された現在は、主要なデータベース製品に再帰クエリが実装されたことで、この隣接リストが大きな問題ではなくなりました。
• これは初版から第2版での大きな変化と言えます。

ミニ・アンチパターン：私のコンピューターでは動作しているのに

• 再帰クエリを使うためにWITH句の後に共通テーブル式(CTE) を記述する方法は、使っているデータベースのバージョンによってはサポートされていない場合があります。
• この問題を回避するために、本番環境・開発環境など各環境で同一(または近い)バージョンを使いましょう、というお話です。

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

• 主キーのカラム名を常にidにするアンチパターンです。
• bug_idやproduct_idなど、状況に応じた適切なカラム名にしましょう。

ミニ・アンチパターン：BIGINTは十分に大きい？

• idカラムのデータ型がINTだと、32ビット数なので不十分な場合があります。
• しかしBIGINTだと64ビットなので32ビット数の2乗になります。2^32倍なので、十分に大きいと言えます。

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

• データベースのアーキテクチャを単純化するために、外部キー制約があります。
• この外部キー制約を使わないというアンチパターンです。
• 外部キー制約を使わないと、参照整合性を保証するためのアプリケーションコードを書く責任が生じてしまいます。
• データベースでのミスの発生を未然に防ぐために外部キー制約を使いましょう、というお話です。

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

• 以下のような属性名をレコードに持たせるテーブルを使用してはいけない、というアンチパターンです。

issue_id	attr_name	attr_value
1234	date_reported	2009-06-01
1234	description	保存処理に失敗する
1234	priority	HIGH

• アンチパターンを用いてもよい場合として、非リレーショナルなデータ管理が必要なら以下のような技術を使うべきです。(*が付いているのは第2版で新たに追加されたもの)
  • Berkeley DB
  • DynamoDB *
  • Elasticsearch *
  • Hadoop / HBase
  • MongoDB
  • Redis
• 解決策としては、サブタイプのモデリングを行う以下4つの方法があります。
  • シングルテーブル継承
  • 具象テーブル継承
  • クラステーブル継承
  • 半構造化データ

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

• 同じ外部キーなのに、レコードによって参照する親テーブルが異なるアンチパターンです。
• 以下のようなcommentsテーブルがあるとします。

comment_id	issue_type	issue_id	comment
6789	Bugs	1234	クラッシュします
9876	FeatureRequests	2345	良いアイデア

• このcommentsテーブルでは、参照する親テーブル(bugsまたはfeature_requests)に応じた名前をissue_typeカラムに格納しています。
• こうするとissue_idカラムに外部キー制約を定義することができず、参照整合性が保証されなくなります。
• 解決策として、以下の方法があります。
  • 交差テーブルの作成
  • 共通の基底テーブルの作成

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

• 複数の値を持つ属性を格納するために複数の列を定義してはいけない、というアンチパターンです。
• 具体的には、以下のような構造のテーブルです。

bug_id	description	tag1	tag2	tag3
1234	保存処理でクラッシュする	crash	NULL	NULL
3456	パフォーマンスの向上	printing	performance	NULL
5678	XMLのサポート	NULL	NULL	NULL

• 解決策としては、従属テーブルを作ります。

ミニ・アンチパターン：価格の保存

• 商品の購入記録をデータベースに格納するEコマースシステムなどでは、商品の価格が変わる可能性があります。
• この場合は「どのユーザがいつどの商品を購入したか」を保持する多対多関連の交差テーブルを定義して、この交差テーブルに購入時の価格を持たせるのが良いでしょう。

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

• 以下のような同じ構成のテーブルを年や月ごとに複数作ってはいけない、というアンチパターンです。
  • Bugs_2019
  • Bugs_2020
  • Bugs_2021
• 解決策としては、パーティショニングと正規化を行います。
  • 水平パーティショニング(シャーディング)
  • 垂直パーティショニング
  • メタデータトリブル列の修正(従属テーブルの導入)

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

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

• 金額計算など小数を扱う際にFLOAT型を使うと正確な値を保持できない、というアンチパターンです。
  • REAL型とDOUBLE PRECISION型も同様です。
• 3分の1のような有理数は、0.3333…のような循環小数となり無限精度が必要になります。
• 解決策としては、NUMERIC型(またはDECIMAL型)を使って精度(総桁数) とスケール(小数点以下の桁数) を指定しましょう。

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

• 列を特定の値に限定する場合、その値をENUM型で指定してはいけない、というアンチパターンです。
• ENUM型で新たな値を追加・削除する際にはALTER文が必要となり、複雑でコストがかかります。
• アンチパターンを用いてもよい場合としては、以下のように限定する値が変わらない場合です。
  • 左/右
  • 有効/無効
  • オン/オフ
• 解決策としては、限定する値をデータで指定することです。
  • 参照テーブルBugStatusを作成し、Bugs.statusに外部キー制約で参照するようにします。

ミニ・アンチパターン：予約語

• 予約語を識別子として扱う方法はデータベースによって異なります。
• 例: 予約後orderを識別子として扱う場合
  • 標準SQL仕様

    SELECT * FROM Bugs WHERE "order" = 123;
    

  • MySQL

    SELECT * FROM Bugs WHERE `order` = 123;
    

  • SQL Server

    SELECT * FROM Bugs WHERE [order] = 123;
    

• また、データベースのバージョンをアップグレードする際は新たな予約語が追加されるため、注意が必要です。

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

• 画像などメディアファイルのデータを保持する方法は、物理ファイルを使用するだけではなく「バイナリデータとしてデータベースに格納する」という方法もあります。
• データベースに格納する場合、VARCHAR型ではなくBLOB型で格納するのが良いです。
• BLOB型は、ほとんどのデータベース製品でサポートされています。

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

• SQLのパフォーマンス最適化の方法としてインデックスを使うのが良いですが、闇雲にインデックスを使ってはいけません、というアンチパターンです。
• MENTORの原則に基づいて、効果的なインデックス管理を行うようにしましょう。
  • Measure(測定)
  • Explain(解析)
  • Nominate(指名)
  • Test(テスト)
  • Optimize(最適化)
  • Rebuild(再構築)

ミニ・アンチパターン：すべての列にインデックスを作成する

• 使用される可能性があるすべてのインデックスを作成することは、すべての列にインデックスを定義するよりもはるかに難しいです。
• 「使用される可能性があるすべてのインデックス」の数は、列数の階乗になります。
• 例えば5列のテーブルの場合、5 × 4 × 3 × 2 = 120 になります。

第III部 クエリのアンチパターン

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

• NULLを一般値として使ってはいけない、というアンチパターンです。
• ちゃんとIS NULL・IS NOT NULLを使うようにしましょう。

ミニ・アンチパターン：NOT IN（NULL）

• 以下のようにNOT INにNULLを指定すると、NEWを除くすべての行にマッチするように見えます。しかし、実際にはどの行にもマッチしません。

  SELECT * FROM Bugs WHERE status NOT IN (NULL, 'NEW');
  

• このSELECT文は、以下のように書き換えることができます。

  SELECT * FROM Bugs WHERE NOT (status = NULL) AND NOT (status = 'NEW');
  

  • NOT (status = NULL)はunknownとなり、ANDで他の条件と組み合わせています。
  • そのためどの行を評価しても式全体はunknownとなり、どの行ともマッチしなくなるというわけです。

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

• GROUP BYを使う際に、GROUP BY句に含めていない非グループ化列をSELECT句に含めてはいけない、というアンチパターンです。
• 単一値の原則(Single-Value Rule) を守ることが大切です。
• 解決策としては曖昧でない列を使用することで、そのために以下の方法があります。(*が付いているのは第2版で新たに追加されたもの)
  • 関数従属性のある列のみにクエリを実行する
  • ウィンドウ関数を使用する *
    • MySQL8.0以上など、最近のデータベース製品にはウィンドウ関数が実装されています。
  • 相関サブクエリを使用する
  • 導出テーブルを使用する
  • JOINを使用する
  • 他の列に対しても集約関数を使用する
  • グループごとにすべての値を連結する
  • 独自ソリューションを使用する *
    • MySQLにはANY_VALUEという非標準関数があり、単一値の原則に反するような列も含めることができます。

ミニ・アンチパターン：ポータブルSQL

• データベース製品ごとにSQLの実装は異なります。
• そのため、開発者はすべてのデータベース製品に共通するポータブルな(移植性の高い) SQLコードを書こうとしがちです。
• しかし、それは以下2つの問題を引き起こします。
  • データベース製品独自の機能を使う機会を失う。
  • SQL標準機能でさえ同じように実装されていないので、そもそもポータブルにはならない。
    • 標準SQL言語の機能だけでクエリを書いても、データベース製品によって動作が異なるケースがあります。

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

• ランダムに1行を取得したい場合にソートをランダムにしてはいけない、というアンチパターンです。
• 以下のようなクエリを発行すると、データ量が増えた時にコストがかかってパフォーマンスが低下してしまいます。

  SELECT * FROM Bugs ORDER BY RAND() LIMIT 1;
  

• 解決策としては特定の順番に依存しないようにすることで、以下の方法があります。(*が付いているのは第2版で新たに追加されたもの)
  • 最小値と最大値の間からランダムにキー値を選択する
  • 欠番の次のキー値を選択する
  • すべてのキー値のリストを受け取り、ランダムに1つを選択する
  • オフセットを用いてランダムに行を選択する
  • ベンダー依存の解決策
  • リデュース・リユース・リサイクル *
    • ランダムに選択した値を、複数回再利用する方法です。

ミニ・アンチパターン：クエリでランダムに複数行を取得する

• 複数行を取得したい場合も、以下のようなクエリを発行するとコストがかかりすぎます。

  SELECT * FROM Bugs ORDER BY RAND() LIMIT 5;
  

• 代替案としては、単一行を選択するクエリを必要な回数繰り返す方法があります。
• しかし、この代替案には以下の注意点があります。
  • ランダムな1行の選択を繰り返すと、同じ行が複数回選択される可能性があります。
  • これは、重複チェックしてリトライを繰り返すことで回避できます。
  • しかし、例えば4行しかないテーブルから5行取得しようとすると、リトライが無限ループになってしまいます。
  • そのため、この無限ループを回避するロジックも必要になります。

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

• 全文検索したい時にパターンマッチ記述を使ってはいけない、というアンチパターンです。
• 以下のようなクエリを発行すると、インデックスが効かずスロークエリになってしまいます。

  SELECT * FROM Bugs WHERE descroption LIKE '%crash%';
  

解決策

• 解決策としては、全文検索に適切なツールを使うことです。
• ツールには、ベンダー拡張のものとサードパーティのものがあります。

ベンダー拡張

データベース製品ごとに、それぞれ以下の独自機能が用意されています。

• MySQLのフルテキストインデックス
• Oracleでのテキストインデックス
• Microsoft SQL Serverでの全文検索
• PostgreSQLでのテキスト検索
• SQLiteでの全文検索（FTS）

サードパーティのサーチエンジン

データベース製品の独自機能に依存せずに全文検索したい場合は、データベースから独立した以下のような検索エンジンを使うのが良いでしょう。(*が付いているのは第2版で新たに追加されたもの)

• Sphinx Search
• Apache Lucence
• ElasticsearchとOpenSearch *
  • Apache Lucenceエンジンを利用しています。
  • ElasticsearchはElastic社の製品で、分析と可視化のための追加機能が多くあります。
  • Amazon社がElasticsearchをフォークして開発したのが、OpenSearchです。

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

• 複雑な問題を1ステップ（1クエリ）で解決しようとしてはいけない、というアンチパターンです。

• 2つのテーブルの各行をすべて結合して1つのテーブルにしてしまうデカルト積が生じて、意図に反した結果になる可能性があります。

• また、クエリ記述や修正、デバッグが難しくなるという弊害もあります。

• 複雑な問題は、ワンステップずつ複数のクエリに分割して解決するのが良いでしょう。

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

• SELECT文を使う際に列をワイルドカード*で指定してはいけない、というアンチパターンです。
• テーブルに後で列が追加された場合、その追加された列も取得してしまいます。
• また、すべての列を取得するのでパフォーマンスも低下します。
• 列名を明示的に指定するようにしましょう。

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

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

• パスワードを平文で格納してはいけない、というアンチパターンです。
• パスワードは、ソルトを付けてハッシュ化して格納するようにしましょう。

ミニ・アンチパターン：ハッシュ文字列をVARCHAR型で格納する

• ハッシュ化したパスワードを格納する際に、文字列に必要な容量がわからない場合はVARCHAR(255)を使いましょう。
• どのハッシュアルゴリズムを使うのかわかっている場合は、アルゴリズムごとに決まった長さになるので固定長のCAHR型を使いましょう。
  • ex. SHA-256なら256ビットで64文字なのでCHAR(64)

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

• 動的SQLを記述する際、ユーザが入力した値を未検証のまま実行してはいけない、というアンチパターンです。
• SQLインジェクションを防御するために、状況に応じて以下の技法を適切に使い分けるようにしましょう。
  • 値のエスケープ
  • クエリパラメータ
  • ストアドプロシージャ
  • データアクセスフレームワーク

ミニ・アンチパターン：引用符内のクエリパラメータ

• LIKE句のパターンマッチ式でプレースホルダの前後にワイルドカード%を追加したい場合、?を含めた文字列を引用符で囲うと、プレースホルダではなく文字列リテラルとして扱われてしまいます。

  SELECT * FROM Bugs WHERE summary LIKE '%?%';
  

  • summaryカラムに?という文字列が含まれる行がヒットします。
• プレースホルダを引用符で囲わずに文字列連結すると、意図した通りLIKE句のパターンマッチ式でクエリパラメータとして扱われます。

  SELECT * FROM Bugs WHERE summary LIKE CONCAT('%', ?, '%');
  

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

• IDなどの主キーを自動インクリメントで連番にしている場合、欠番を埋めようとしてはいけない、というアンチパターンです。
• 主キーは各行を一意にして識別するためのものですが、ルールはそれだけなので連続している必要はありません。

ミニ・アンチパターン：グループごとの自動インクリメント

• 例えば、スポーツ選手のチーム別・シーズン別でのランキングや顧客ごとの請求書などで、以下のような要件が必要になることがあります。
  • 「自動インクリメントする列は必要だが、その値はサブグループごとに1から始めなければならない」
• 良い解決策としては、ウィンドウ関数ROW_NUMBERやPARTITION BYオプションを使ってクエリ実行時に行に番号を割り当てることです。

  SELECT bug_id, author, comment,
    ROW_NUMBER() ORVER (PARTITION BY bug_id
      ORDER BY comment_data) AS comment_number
  FROM comments;
  

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

• 戻り値やステータスコードや例外を見逃してはいけない、というアンチパターンです。
• 戻り値と例外のチェックを行なって、ステップにミスがないことを保証するようにしましょう。
• また、デバッグではクエリを構築するコードではなく実際に構築されたクエリを使用することが重要です。

ミニ・アンチパターン：構文エラーメッセージ解読のすすめ

• SQL構文エラーの報告に関しては、MySQLがわかりやすくて優秀です。
• 逆にOracleのエラーメッセージは、わかりにくくてひどいことで有名です。
• わかりにくくても、できる限りそこから何かを得ようと努めることが大切です。

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

• データベースもアプリケーションコードと同様に、品質保証(Quality Assurance: QA) に取り組むべきである、という話です。
• 品質保証は、文書化・ソースコードのバージョン管理・テストのベストプラクティスに従うことで達成できます。

ミニ・アンチパターン：名前の変更

• 既存のテーブル名を変更する際、データベースが先かアプリケーションが先か、という問題が生じます。
• アプリケーションを停止できるなら、データベース内のテーブル名とアプリケーションコードの変更を同時に行えるので問題はありません。
• しかし、ダウンタイム無しの無停止でアプリケーションを実行する必要がある場合、全く同時に変更はできないのでテーブル名が一致せずにエラーが生じてしまいます。
• 解決策としては、以下の2つがあります。
  • 新名称のテーブルを作成
    • まず新名称のテーブルを新規作成します。
    • 次に新規作成したテーブルを使用するように、アプリケーションコードを徐々に移行して行きます。
  • ビューの使用
    • 既存テーブルを新名称に変更し、それと同時に旧名称のビューを作成します。
    • このビューは、新名称となったテーブルの「エイリアス」のような役割を果たします。

25章. スタンダード・オペレーティング・プロシージャ（さびついた開発標準）

• 第2版で新たに追加された章になります。
• 「これまでそうだったから」という理由だけで何らかのテクノロジーを使い続けてはいけない、というアンチパターンです。
• 解決策としては、現代のアプリケーションアーキテクチャを採用するようにしましょう。

ミニ・アンチパターン：MySQLのストアドプロシージャ

• MySQLのストアドプロシージャは、2005年のバージョン5.0で導入されました。
• しかし、当時MySQLを使っていた開発者のほとんどはアプリケーションコードやORMクラスからクエリ実行することを好んでいたので、プロシージャには改善の需要があまりありませんでした。
• そのため、MySQLでプロシージャを広範囲に使用する際には、以下の点を理解しておくべきです。
  • パッケージの使用
  • デバッグ
  • テスト
  • コンパイル
  • デプロイ
  • シャードアーキテクチャの使用

第V部 外部キーのミニ・アンチパターン

• 第2版で新たに追加されたボーナスとしての部になります。

26章. 標準SQLにおける外部キーの誤った使い方

• 外部キーは、ANSI/ISO SQL標準の一部です。
• 外部キーに関す間違いの多くは、使用しているデータベース製品固有のものではありません。
• その標準SQLにおける外部キー制約の一般的なミニ・アンチパターンは、以下になります。
  • 参照方向を逆にしようとする
  • 作成前のテーブルを参照しようとする
  • 親テーブルのキーを参照してはいけない
  • 複合キーの列後に個別の制約を作成しようとする
  • 間違った列順で外部キーを定義しようとする
  • データ型の不一致
  • 文字照合順序の不一致
  • 孤立したデータを作成しようとする
  • NULLにできない列に対してSET NULLオプションを使おうとする
  • 重複する制約識別子を作成しようとする
  • 互換性のないテーブルタイプを使用してしまう

27章. MySQLにおける外部キーの誤った使い方

• MySQL 8.0における外部キー制約の一般的なミニ・アンチパターンは、以下になります。
  • 互換性のないストレージエンジンを使おうとする
  • 外部キーに大きなデータ型を使おうとする
  • 一意でないインデックスへの外部キーを定義しようとする
  • インライン参照構文を使おうとする
  • デフォルト参照構文を使おうとする
  • 互換性のないテーブルタイプを使おうとする

最後に

• 初版から12年が経過しているということで、第2版で新たに追加された内容が多かったですね。
• 特に各章の末尾に追加されたミニ・アンチパターンについては、実務的な内容が多く「私自身もこの問題にハマったな」とか「こうやって解決したな」と思ったので、とても良い復習になりました。
• 一方で、以下のように初版にはあったけど第2版では削除された章もありました。
  • マジックビーンズ（魔法の豆）
    • MVCのmodelがActiveRecordそのものになってはいけない、というアンチパターンでした。
    • これが削除されたということは、12年間でのRuby on Railsのバージョンアップに伴いActiveRecordも洗練されていった証左だと思います。
  • 砂の城
    • データベースを使ったサービスを安定稼働させるために、性能問題や障害が発生した時の対処を想定しておきましょう、というお話でした。
    • これは、現在でもとても大切な内容だと思います。
    • 本書自体からは削除されているものの、オライリーの公式サイトにて日本語版の付録として公開されています。
      • https://www.oreilly.co.jp/pub/9784814400744/sql-antipatterns-2nd-sunanoshiro.pdf
• データベース自体は昔からある信頼性の高い技術なので、スキルとして一度身に着けておくと錆びつかないものだと思います。
• それでもこの12年間の中で進化を続けて来たことがわかったので、使う・使わないは別として各データベース製品のバージョンアップを随時追っていくことが大切だなと感じました。