Go to Qiita Advent Calendar 2024 Top
LoginSignup
6
5

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@picapica

SQLアンチパターンまとめ

Posted at

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

  • カンマ区切りでリストを格納する

    • DBの文字数制約に引っかかる
    • 整合性の問題が発生する
    • joinできなくてきつい
    • 検索もしづらい

  • 多対多のデータは中間テーブルをおくの鉄板

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

  • スレッドのようにツリー構造になるデータの格納方法
  • 下記のようなテーブル定義だと再帰処理を含むSQLが必要になり、パフォーマンス上の問題がある
  • かきは隣接リストと呼ばれるものになる
CREATE TABLE Comments (
 comment_id SERIAL PRIMARY KEY,
 parent_id BIGINT UNSIGNED,
 comment TEXT NOT NULL,
 FOREIGN KEY (parent_id) REFERENCES Comments(comment_id)
);
  • 解決策
    • 経路列挙モデル（Path Enumeration）
      • path カラムを作り、そこにファイルシステムのようなpathを格納する
      • path: /1/2/6/7 のようなイメージになる
      • この場合like検索で比較的容易に探索可能
      • ただしジェイウォークと構造は同じなので、データ生合成の問題は孕んでいる
    • 入れ子集合（Nested Set）
      • 下記のようにツリー構造ではなく、集合として捉える。
      • https://gihyo.jp/dev/serial/01/sql_academy2/000501
      • 左端の座標と右端の座標がわかればその下にある集合は左端と右端の間にあるもの、というもの
    • 閉包テーブル（Closure Table）
      • 下記のようなTreePathsを作成して直接の親子関係だけでなく、ツリー全てのパスを保存する
CREATE TABLE Comments (
 comment_id SERIAL PRIMARY KEY,
 bug_id BIGINT UNSIGNED NOT NULL,
 author BIGINT UNSIGNED NOT NULL,
 comment_date DATETIME NOT NULL,
 comment TEXT NOT NULL,
 FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id),
 FOREIGN KEY (author) REFERENCES Accounts(account_id)
);
CREATE TABLE TreePaths (
 ancestor BIGINT UNSIGNED NOT NULL,
 descendant BIGINT UNSIGNED NOT NULL,
 PRIMARY KEY (ancestor, descendant),
 FOREIGN KEY (ancestor) REFERENCES Comments(comment_id),
 FOREIGN KEY (descendant) REFERENCES Comments(comment_id)
);

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

  • ナチュラルキーでユニークになりそうなものにも関わらず、サロゲートキーを指定してしまうこと
  • ぶっちゃけほとんどのF/Wでサロゲートキーを指定するようにしているので、アンチパターンではないと思っている。
  • けど選択肢があることを知らないのはNGだと個人的に思っている。

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

  • 外部キー制約はDBのアーキテクチャを単純化するためのもの
  • 外部キー制約を使用しないのはアンチパターン
    • 完璧なコードを前提にしている
    • 小テーブルの状態を確認した上でレコードの更新をするのをAPレベルで保証する必要があること

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

  • 下記のようにkey - valueを各行に保存する汎用的な属性テーブルを作成するやり方
  • アプリ側で小クラス間で異なるプロパティを持っている時に、カラムにnullが多くならない。
  • 新しい属性をサポートする時に列を増やす必要がない
CREATE TABLE Issues (
 issue_id SERIAL PRIMARY KEY
);
INSERT INTO Issues (issue_id) VALUES (1234);
CREATE TABLE IssueAttributes (
 issue_id BIGINT UNSIGNED NOT NULL,
 attr_name VARCHAR(100) NOT NULL,
 attr_value VARCHAR(100),
 PRIMARY KEY (issue_id, attr_name),
 FOREIGN KEY (issue_id) REFERENCES Issues(issue_id)
);
INSERT INTO IssueAttributes (issue_id, attr_name, attr_value)
 VALUES
 (1234, 'product', '1'),
 (1234, 'date_reported', '2009-06-01'),
 (1234, 'status', 'NEW'),
 (1234, 'description', ' 保存処理に失敗する '),
 (1234, 'reported_by', 'Bill'),
 (1234, 'version_affected', '1.0'),
 (1234, 'severity', ' 機能の損失 '),
 (1234, 'priority', 'HIGH');
  • データの整合性をどう保つか
    • 必須の属性を設定できない NOT NULLを設定できない
    • SQLのデータ型を使用できない
    • left outer join を使用したSQLを発行しがちである

解決策：サブタイプのモデリングを行う

  • シングルテーブル継承(SIT)
    • 小クラスの属性値も含めて全ての属性を1つのテーブルに格納するやり方
  • 具象テーブル継承
    • 小クラスの種類ごとにテーブルを作成するやり方
    • 全てのオブジェクトをそのサブタイプであるかに関わらず取得したい場合はviewを定義するやり方がある
CREATE VIEW Issues AS
 SELECT b.issue_id, b.reported_by, ... 'BUG' AS issue_type
 FROM Bugs AS b
 UNION ALL
 SELECT f.issue_id, f.reported_by, ... 'FEATURE' AS issue_type
 FROM FeatureRequests AS f;
  • クラステーブル継承
    • オブジェクト指向を模倣して基底テーブルと追加のテーブルを作成してleft outer joinをするやり方
CREATE TABLE Issues (
 issue_id SERIAL PRIMARY KEY,
 reported_by BIGINT UNSIGNED NOT NULL,
 product_id BIGINT UNSIGNED,
 priority VARCHAR(20),
 version_resolved VARCHAR(20),
 status VARCHAR(20),
 FOREIGN KEY (reported_by) REFERENCES Accounts(account_id),
 FOREIGN KEY (product_id) REFERENCES Products(product_id)
);
CREATE TABLE Bugs (
 issue_id BIGINT UNSIGNED PRIMARY KEY,
 severity VARCHAR(20),
 version_affected VARCHAR(20),
 FOREIGN KEY (issue_id) REFERENCES Issues(issue_id)
);
CREATE TABLE FeatureRequests (
 issue_id BIGINT UNSIGNED PRIMARY KEY,
 sponsor VARCHAR(50),
 FOREIGN KEY (issue_id) REFERENCES Issues(issue_id)
);
  • 半構造化データ
    • issue_type と attributes のカラムを追加
    • attributes にはjsonなどのシリアライズした値を格納するやり方

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

  • 複数のテーブルからアクセスできるように type 属性を追加して、複数の親テーブルとjoinできるようにするやり方
    • ただしコメントがついているIssueなどを割り出す時に複数のtableを left outer joinする必要があるので、クエリが重くなりがちになる
CREATE TABLE Comments (
 comment_id SERIAL PRIMARY KEY,
 issue_type VARCHAR(20), -- 'Bugs' または 'FeatureRequests' が格納される
 issue_id BIGINT UNSIGNED NOT NULL,
 author BIGINT UNSIGNED NOT NULL,
 comment_date DATETIME,
 comment TEXT,
 FOREIGN KEY (author) REFERENCES Accounts(account_id)
);

解決策

  • 中間テーブルの作成
    • コメントに関連のあるIssueを探索する時も基本的には中間テーブルを見れば良い
    • Issueを全て取得したい場合もunionを使用すれば、取得可能
CREATE TABLE BugsComments (
 issue_id BIGINT UNSIGNED NOT NULL,
 comment_id BIGINT UNSIGNED NOT NULL,
 PRIMARY KEY (issue_id, comment_id),
 FOREIGN KEY (issue_id) REFERENCES Bugs(issue_id),
 FOREIGN KEY (comment_id) REFERENCES Comments(comment_id)
);
CREATE TABLE FeaturesComments (
 issue_id BIGINT UNSIGNED NOT NULL,
 comment_id BIGINT UNSIGNED NOT NULL,
 PRIMARY KEY (issue_id, comment_id),
 FOREIGN KEY (issue_id) REFERENCES FeatureRequests(issue_id),
 FOREIGN KEY (comment_id) REFERENCES Comments(comment_id)
);
  • 共通の親テーブルの作成
CREATE TABLE Issues (
 issue_id SERIAL PRIMARY KEY
 . . .
);
CREATE TABLE Bugs (
 issue_id BIGINT UNSIGNED PRIMARY KEY,
 FOREIGN KEY (issue_id) REFERENCES Issues(issue_id),
 . . .
);
CREATE TABLE FeatureRequests (
 issue_id BIGINT UNSIGNED PRIMARY KEY,
 FOREIGN KEY (issue_id) REFERENCES Issues(issue_id),
 . . .
);
CREATE TABLE Comments (
 comment_id SERIAL PRIMARY KEY,
 issue_id BIGINT UNSIGNED NOT NULL,
 author BIGINT UNSIGNED NOT NULL,
 comment_date DATETIME,
 comment TEXT,
 FOREIGN KEY (issue_id) REFERENCES Issues(issue_id),
 FOREIGN KEY (author) REFERENCES Accounts(account_id)
);

7. マルチカラムアトリビュート

  • アンチパターン：複数の列を定義する

    • tag1 / tag2/ tag3のような複数の列を定義する

  • 解決策

    • 従属するTagsテーブルを作成する

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

  • 列にbug2009などの年月を表すカラムが出てきた時
  • 定期的にカラムの追加が必要になってしまう

解決策

  • 水平パーティショニングの使用
    • MySQL等でデフォルトで搭載されている機能
CREATE TABLE Bugs (
 bug_id SERIAL PRIMARY KEY,
 -- 他の列 . . .
 date_reported DATE
) PARTITION BY HASH ( YEAR(date_reported) )
 PARTITIONS 4;

  • 垂直パーティショニングの使用

    • 水平パーティショニングが行で分割するのに対して、垂直パーティショニングは列で分割をするようにする

  • 従属テーブルの導入

CREATE TABLE ProjectHistory (
 project_id BIGINT,
 year SMALLINT,
 bugs_fixed INT,
 PRIMARY KEY (project_id, year),
 FOREIGN KEY (project_id) REFERENCES Projects(project_id)
);

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

  • アンチパターン：FLOAT データ型を使用する
    • float型を使用する限り丸めを避けることができない
    • 循環小数などが該当する
  • NUMERIC / DECIMALなどを使用することで、指定した精度で計算が行われるようにする

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

  • enumの定義をMySQLの機能で定義する場合
    • マイグレーションが困難
  • 解決策
    • 限定する値をデータで指定する
    • BugStatusというマスタテーブルを作成する
    • これはアプリ側に寄せても問題ないと思う
    • テーブルにマスタを作成する場合はBugsテーブルにBugStatusへの外部制約をかけることができる
    • テーブルを分割する方が移植が容易

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

  • 画像をはじめとする大容量メディアファイルをどのように格納するか

  • アンチパターン：物理ファイルの使用を必須と思い込む

    • ファイル削除時の問題
      • レコードに保存がされているパスを削除しても、物理画像が削除されない
    • トランザクション分離の問題
      • コミット前に他のユーザーに画像が見えてしまう
      • ロールバックする時に画像を復旧することができない
    • DBバックアップツール使用時の問題
      • DBのバックアップ時とストレージのスナップショットとの断面で整合性をとることが不可能

  • 解決策

    • 必要に応じて BLOB 型を採用する
    • 要するにデータストアを分割しない
    • 動画ファイルとかを保存するのは確実に無理なので、全然解決策になっていない感isある

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

  • アンチパターン：闇雲にインデックスを使用する

    • インデックスをまったく定義しないか、少ししかインデックスを定義しなくなってしまう。
    • インデックスを多く定義し過ぎるか、役立たないインデックスを定義してしまう。
    • インデックスを活用しないクエリを実行してしまう。

  • 解決策：「MENTOR」の原則に基づいて効果的なインデックス管理を行う

    • MENTORとは、Measure（測定）、Explain（解析）、Nominate（指名）、Test（テスト）、Optimize（最適化）、Rebuild（再構築）の頭文字をとったものです。

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

  • SQLは通常のプログラミング言語とのNULLとは役割が異なる
  • 無（null）や不明（unknown）、適用不能（inapplicable）に相当する値を、予約語 NULL で表現します。
  • アンチパターン：NULL を一般値として使う、または一般値を NULL として使う
  • 解決策：NULL を一意な値として使う
    • nullの制御文には is null or is not null しか使えない

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

  • 目的：グループ内で最大値を持つ行を取得する
    • 単一値の原則（Single-Value Rule）
      • group byした場合はそれぞれの単一の値が入る
      • 下記の場合に MAX(date_reported) は単一の値になるが、bug_idは最大値の結果を汲み取ってくれない（悲しい特性である）
SELECT product_id, MAX(date_reported) AS latest, bug_id
FROM Bugs INNER JOIN BugsProducts USING (bug_id)
GROUP BY product_id;

解決策

  • 関数従属性のある列のみにクエリを実行する
    • bug_idを取得しない
SELECT product_id, MAX(date_reported) AS latest
FROM Bugs INNER JOIN BugsProducts USING (bug_id)
GROUP BY product_id;
  • 相関サブクエリを使用する
    • ただしパフォーマンスは微妙なので注意が必要
SELECT bp1.product_id, b1.date_reported AS latest, b1.bug_id
FROM Bugs b1 INNER JOIN BugsProducts bp1 USING (bug_id)
WHERE NOT EXISTS
 (SELECT * FROM Bugs b2 INNER JOIN BugsProducts bp2 USING (bug_id)
 WHERE bp1.product_id = bp2.product_id
 AND b1.date_reported < b2.date_reported);
  • 導出テーブルを使用する
    • group byした結果を導出テーブルとして使用して、複数条件でjoinをする
SELECT m.product_id, m.latest, MAX(b1.bug_id) AS latest_bug_id
FROM Bugs b1 INNER JOIN
 (SELECT product_id, MAX(date_reported) AS latest
 FROM Bugs b2 INNER JOIN BugsProducts USING (bug_id)
 GROUP BY product_id) m
 ON b1.date_reported = m.latest
GROUP BY m.product_id, m.latest;
  • JOIN を使用する
SELECT bp1.product_id, b1.date_reported AS latest, b1.bug_id
FROM Bugs b1
INNER JOIN BugsProducts bp1
 ON b1.bug_id = bp1.bug_id
LEFT OUTER JOIN (
 Bugs AS b2 INNER JOIN BugsProducts AS bp2
 ON b2.bug_id = bp2.bug_id
 )
 ON (bp1.product_id = bp2.product_id
 AND (b1.date_reported < b2.date_reported
 OR b1.date_reported = b2.date_reported AND b1.bug_id < b2.bug_id))
WHERE b2.bug_id IS NULL;
  • 他の列に対しても集約関数を使用する
    • 最大のbug_idが最新の日付を持っていることを保証できる場合のみ使用できる
SELECT product_id, MAX(date_reported) AS latest,
 MAX(bug_id) AS latest_bug_id
FROM Bugs INNER JOIN BugsProducts USING (bug_id)
GROUP BY product_id;
  • グループごとにすべての値を連結する
    • GROUP_CONCATを使用する
SELECT product_id, MAX(date_reported) AS latest,
 GROUP_CONCAT(bug_id) AS bug_id_list
FROM Bugs INNER JOIN BugsProducts USING (bug_id)
GROUP BY product_id;
6
5
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
6
5

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?