【SQL】アンチパターン

目次

• 前置き
• ジェイウォーク
• ナイーブツリー
• EAV(エンティティ・アトリビュート・バリュー)
• ポリモーフィック関連
• 丸め誤差
• 31フレーバー
• ファントムファイル
• スパゲッティクエリ

前置き

この記事のSQLはMySQLで書かれています。
また、全てオライリー SQLアンチパターンから学びのあったものだけ引用しています。

ジェイウォーク

概要

　カンマ区切りフォーマットなどのリストデータを格納すること。

INSERT INTO customer (customer_id, item_id) ('xxxx', 'a,b,c,d');

問題点

• リストの長さに制限がある。
• データ型が文字列型になるため、誤ったデータが入る可能性がある。
• 検索が行いにくい
• etc

解決策

　交差テーブルを作成する。一つのカラムに複数の値をいれないことで、テーブル結合や検索が容易になる。

CREATE TABLE contacts (
    customer_id BIGINT NOT NULL,
    item_id BIGINT NOT NULL,
    PRIMARY KEY (customer_id, item_id),
    FOREIGN KEY (customer_id) customer(customer_id),
    FOREIGN KEY (item_id) item(item_id)
);

---

目次へ

ナイーブツリー

概要

　コメントの親子関係把握の場合などデータが階層構造になる場合に、テーブル内に親子情報を持たせること。ただし、再帰クエリをサポート指定いるRDBの場合はアンチパターンの対象ではない。

CREATE TABLE comments(
    comment_id SERIAL PRIMARY KEY,
    parent_id BIGINT UNSIGNED,  # 親のcomment_idが入る。
    FOREIGN KEY (parent_id) REFERENCES comments(comment_id)
);

問題点

• 階層が深くなるほどSQLが複雑になる。
• CASCADE制約をつけられないため、データの削除が煩わしい。

解決策

　RDBMSが再帰クエリをサポートしていない場合、わかりやすさを考慮して親子関係を保持するテーブルを用意するのが良い。ただしデータ量が増えてしまうためトレードオフにはなる。

CREATE TABLE paths (
    parent_id BIGINT UNSIGNED NOT NULL,
    child_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (parent_id, child_id),
    FOREIGN KEY (parent_id) REFERENCES comments(comment_id),
    FOREIGN KEY (child_id) REFERENCES comments(comment_id)
);

目次へ

EAV(エンティティ・アトリビュート・バリュー)

概要

　あるKeyに対するValueを保持させたい場合、楽な設計方法として汎用的な属性テーブルを使用してしまうこと。

CREATE TABLE target (
    target_id SERIAL PRIMARY KEY,
);
INSERT INTO target (target_id) VALUES (100);

CREATE TABLE target_attribute (
    target_id BIGINT UNSIGNED NOT NULL,
    attr_name VARCHAR(20) NOT NULL,
    attr_value VARCHAR(20) NOT NULL,
    PRIMARY KEY (target_id, attr_name),
    FOREIGN KEY (target_id) PREFERENCES target(target_id)
);
INSERT INTO target_attribute (target_id, attr_name, attr_value)
    VALUES
        (100, 'attr1', '1')
        (100, 'attr2' '2017');

問題点

• SQLのデータ型を使用できないため、フォーマットのチェックが必要になる。
• 必須な属性を設定することができない。
• 参照整合性を強制できない。

解決策

シングルテーブル継承

　attributeを全て一つのテーブルのカラムとして管理する。

CREATE TABLE target (
    target_id SERIAL,
    attr1 VARCHAR(20) NOT NULL,
    attr2 VARCHAR(20)
    PRIMARY KEY (target_id, attr1)
);

具象テーブル継承

　サブタイプごとにテーブルを作成する。

CREATE TABLE target_attr1 (
    target_id SERIAL,
    value VARCHAR(20) NOT NULL,
    PRIMARY KEY (target_id, value)


CREATE TABLE target_attr2 (
    target_id SERIAL,
    value VARCHAR(20) NOT NULL,
    PRIMARY KEY (target_id, value)
);

目次へ

ポリモーフィック関連

概要

　異なる属性やテーブルに対する共通的なカラムを準備すること。例えば、commentsテーブルの中にコメント種別を格納するcomment_typeカラムを用意し、"sports_news" や "economic_news"など属性を判断できるようなデータを入れること。ただし、ORMを利用する場合は親テーブルの作成に影響を受けないようにするため、ポリモーフィック関連をあえて使用する場合がある。

CREATE TABLE comments(
    comment_id SERIAL PRIMARY KEY,
    comment_content TEXT,
    news_type VARCHAR(20) NOT NULL,
    news_id BIGINT UNSIGNED NOT NULL
);

INSERT INTO comments (comment_id, comment_content, news_type, news_id)
    VALUES
        (100, 'comment1', 'sports', 2000);

INSERT INTO comments (comment_id, comment_content, news_type, news_id)
    VALUES
        (101, 'comment2', 'economic', 3000);

問題点

• 他テーブルを柔軟に参照できる一方、news_idなどに参照制約をかけることができない。

解決策

参照を逆にする。

　共通的なカラムを用意せず、各親テーブルに参照カラムを追加する。

CREATE TABLE comments(
    comment_id SERIAL PRIMARY KEY,
    content TEXT
);

CREATE TABLE economic_news(
    news_id SERIAL PRIMARY KEY,
    sentence TEXT,
    comment_id VARCHAR(20) NOT NULL,
    FOREIGN KEY (comment_id) REFERENCES comments(comment_id)
);

CREATE TABLE sports_news(
    news_id SERIAL PRIMARY KEY,
    sentence TEXT,
    comment_id VARCHAR(20) NOT NULL,
    FOREIGN KEY (comment_id) REFERENCES comments(comment_id)
);

交差テーブルの作成

　中間テーブルを作成し、接続したいテーブルを外部参照させる。検索条件を柔軟に変更できるがテーブル数が増えてしまう。

CREATE TABLE economic_news (
    news_id SERIAL PRIMARY KEY,
    sentence TEXT
);
CREATE TABLE economic_news_comments (
    news_id SERIAL PRIMARY KEY,
    comment_id VARCHAR(20) NOT NULL,
    FOREIGN KEY (news_id) REFERENCES economic_news(news_id),
    FOREIGN KEY (comment_id) REFERENCES comments(comment_id)
);

CREATE TABLE sports_news (
    news_id SERIAL PRIMARY KEY,
    sentence TEXT
);
CREATE TABLE sports_news_comments (
    news_id SERIAL PRIMARY KEY,
    comment_id VARCHAR(20) NOT NULL,
    FOREIGN KEY (news_id) REFERENCES sports_news(news_id),
    FOREIGN KEY (comment_id) REFERENCES comments(comment_id)
);

共通的な親テーブルの作成

　中間テーブルとして全ての親となるテーブルを作成する。

CREATE TABLE news (
    news_id SERIAL PRIMARY KEY
);
  
CREATE TABLE economic_news (
    news_id BIGINT UNSIGNED,
    FOREIGN KEY (news_id) REFERENCES news(news_id)
);

CREATE TABLE sports_news (
    news_id BIGINT UNSIGNED,
    FOREIGN KEY (news_id) REFERENCES news(news_id)
);

CREATE TABLE comments (
    news_id BIGINT UNSIGNED,
    content TEXT,
    FOREIGN KEY (news_id) REFERENCES news(news_id)
);

目次へ

丸め誤差

概要

　カラムにFLOAT, REAL, DOUBLE, PRECISION などの小数点数値を扱うデータ型を設定すること。SQLは実数を2進数形式でエンコードするため、10進数から2進数に変換したとき無限小数となるデータは丸め誤差が発生してしまう。

問題点

• FLOAT型のデータをSELECTして乗算した際など、数値に誤差が発生してしまう。

解決策

　NUMERICまたはDECIMALデータ型を用いて桁数を指定しておく。

CREATE TABLE comment_rate (
    news_id BIGINT,
    rate DECIMAL(5,2),
    FOREIGN KEY (news_id) references news(news_id)
);

INSERT INTO comment_rate
    VALUES
        (11, 123.45);

目次へ

31フレーバー

概要

　データの値を限定したいとき、列定義で指定すること。例えばCHECK制約やENUM型を用いるなど。

# CHECK制約を用いたパターン
CREATE TABLE news (
    news_id BIGINT,
    news_type VARCHAR(20) CHECK (news_type IN ('sports', 'economic'))
);

# ENUM型を用いたパターン
CREATE TABLE news (
    news_id BIGINT,
    news_type ENUM ('sports', 'economic'),
    PRIMARY KEY (news_id)
);

問題点

• どのような値が入るのか列定義から確認しなくてはいけない(SELECT文で確認できない)
• 設定する値を殖やす場合、列定義を更新する必要があり拡張しづらい
• RDB移植の際にCHECKやENUMを使用していると製品間の仕様差異に引っかかる

解決策

　限定する値をデータ(マスターテーブル)で指定する。例では外部参照させることで値を限定させると同時に、値の種別追加などがINSERT文のみで実現可能になっている。

CREATE TABLE news_type (
    news_type VARCHAR(20),
    PRIMARY KEY (news_type)
);

INSERT INTO news_type VALUES ('sports');
INSERT INTO news_type VALUES ('economic');

CREATE TABLE news (
    news_id BIGINT,
    news_type VARCHAR(20),
    PRIMARY KEY (news_id),
    FOREIGN KEY (news_type) REFERENCES news_type(news_type)
);

# 成功
INSERT INTO news VALUES (1, 'sports');

# エラー
INSERT INTO news VALUES (1, 'not_exist');

目次へ

ファントムファイル

概要

　画像データなどバイナリファイルのデータをDBで取り扱う際、データベース内にbyte列として管理せずに物理ファイルとして管理すること。(MySQLではBLOB型として画像データをDB内に保存することができる。)

CREATE TABLE screenshots (
    image_id BIGINT,
    image_path TEXT,
    PRIMARY KEY (image_id)
);

問題点

• データと実体の削除タイミングが異なるため、不整合が発生する可能性がある (トランザクション分離の問題)
• ファイル削除をアプリケーション側で作りこむ必要がある

例外

　以下のような場合には物理ファイルを用意した方が適している。

• データベース容量を減らしたいとき
• バックアップ容量削減やバックアップ実行時間を短縮したいとき

解決策

　画像ファイルをDBでのみ管理する。この項目に関してはトレードオフが発生するため状況に応じて判断すること。

CREATE TABLE screenshots (
    image_id BIGINT,
    image BLOB,
    PRIMARY KEY (image_id)

目次へ

スパゲッティクエリ

概要

　複雑な問題を一つのSQLで解決しようとして、読みづらいSQLを作成すること。例えばいくつものテーブルをJOINするなど。

解決策

• SQLを分ける。
• UNIONを用いる。
• CASE式とSUM関数を組み合わせる。