" />
本ページはプロモーションが含まれています。

スポンサーリンク

データベース

SQLアンチパターンのまとめ

データベース論理設計のSQLアンチパターン

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

ジェイウォーク（信号無視）パターンの説明

カンマ区切りフォーマットのリストを格納する。（カラムに）

カラムにカンマ区切りなどのリストを保持すること。スカラ値になっていない。第一正規化されていない状態。

第一正規化されていないのがNGなのは言うまでもない。
・リストの中身を検索するときにパターンマッチが必要になる。
・カラムの長さが決定できない。
・カラムの長さを一度決定すると、増やしたい場合にテーブル定義の変更が必要になる。
など、様々なデメリットがある。

ジェイウォーク（信号無視）の解決策

交差テーブルを作成する。

検索が容易になり、検索時にインデックスが効くようになり、パフォーマンスが向上するなどのメリットがある。

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

ナイーブツリー（素朴な木）パターンの説明

常に親のみに依存する。

親子関係をRDBで表現するときに、親レコードのIDだけ持つという設計。すると、階層が深くなったときに、SELECT文のJOINが深くなってしまう。

例）組織図（上司–部下）、スレッド形式の掲示板、

ナイーブツリー（素朴な木）パターンの解決策

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

IDリクワイアド（とりあえずID）パターンの説明

すべてのテーブルに「id」列を用いる

どんなテーブルにも代理キー（サロゲートキー）を付与するテーブル設計のこと。

IDリクワイアド（とりあえずID）パターンの解決策

状況に応じて適切に調整する

自然キー、複合キーを使うようにする。

主キーの名前は、主キーが識別するテーブルのレコードを表すような名前が良い。そのため、複数テーブルで主キーに同じ名前を使うべきではない。

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

キーレスエントリ（外部キー嫌い）パターンの説明

外部キー制約を使用しない。

参照整合性（親レコードが存在すること）をアプリケーションのロジックで確認する必要が生じるデメリットがある。具体的には、余計なSELECT文が増える。

ただし、ストレージエンジンにMyISAMを使用している場合は、外部キー制約は使用できない。

キーレスエントリ（外部キー嫌い）パターンの解決策

外部キー制約を使用する。

EVA（エンティティ・アトリビュート・バリュー）

EVA（エンティティ・アトリビュート・バリュー）パターンの説明

NG：汎用的な属性テーブルを使用（作成）している。

可変属性（同一テーブルにおいて、レコードによってある値が格納されているカラムが異なる）を扱いたいときに発生するアンチパターン。

EVA（エンティティ・アトリビュート・バリュー）パターンの解決策

サブタイプのモデリングを行う。（通常のテーブル形式に落とし込む）

  • シングルテーブル継承
    • 同一テーブルに属性カラム、属性Aの場合のカラム...、属性Bの場合のカラム...を持たせる。
  • 具象テーブル継承
    • 属性A用のテーブル、属性B用のテーブルに分ける。
  • クラステーブル継承
    • 具象テーブル継承のうち、共通属性を別テーブルに切り出す。

などのモデリング手法がある。

ポリモーフィック関連

ポリモーフィック関連パターンの説明

NG：二重目的の外部キーを使用する。

複数候補の親テーブルを参照したいときに発生するアンチパターン。（関連が可変であるテーブル設計）

ポリモーフィック関連パターンの解決策

関連（リレーションシップ）を単純化する

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

マルチカラムアトリビュート（複数列属性）パターンの説明

マルチカラムアトリビュート（複数列属性）パターンの解決策

データベース物理設計のSQLアンチパターン

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

ラウンディングエラー（丸め誤差）パターンの説明

NG：小数値が入るカラムにFloatやDouble型を指定している。

FloatやDouble型の値を加減剰余したときに、丸め誤差が発生する。

ラウンディングエラー（丸め誤差）パターンの解決策

DECIMALまたはNUMERIC型を使用して小数点を表すようにする。

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

カラムに格納できる値を限定したい時は、CHECK制約よりも別テーブルで管理する方が柔軟である。

サーティーワンフレーバー（31のフレーバー）パターンの説明

NG例：ALTER TABLE Xxx ADD COLUMN status VARCHAR(20) CHECK (status IN (‘NEW’, ‘EDITING’, ‘PUBLISHED’);

列に格納できる値を限定された値に制限することはとても有用である。
しかし、CHECK制約を使うと、限定する値を変更するときに、テーブル定義を変更しないといけなくなり、サーバ停止が必要になってしまう。

サーティーワンフレーバー（31のフレーバー）パターンの解決策

Step1.列に格納できる値を保持するテーブルを作成する。

CREATE TABLE ArticleStatus(
status VARCHAR(20) PRIMIARY KEY
)

Step2.親テーブルのstatusに外部キー制約を貼っておく。

限定する値を変更するときはINSERT文やUPDATE文が使えるようになる。
削除するときは、有効かどうか示すカラムを追加して対応できる。
このように柔軟性がアップする。

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

ファイルはDBの外部に置くのが一般的だが、BLOB型の使用も検討すること。

ファントムファイル（幻のファイル）パターンの説明

NG例：DBにはファイルのパスを文字列型で保持する。

レコードと外部に置いたファイルの整合性を保つために、アプリケーション側で整合させるロジックを書く必要が生じるのが最大のデメリット。

・レコードだけ削除したとき、外部にファイルが残ってしまう。
・ロールバックしたときに、外部にファイルが残ってしまう。

ファントムファイル（幻のファイル）パターンの解決策

BLOB型の使用を検討する。

BLOBにすることで、データとファイルの整合性が容易に取れるようになる。

  • パスを元に外部ファイルにアクセスするステップや通信がなくなる。
  • レコードを削除すると、画像も自動で削除される。
  • ロールバックすると、画像も元の状態に戻る。
  • DBバックアップをすると、画像もバックアップできる。

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

インデックスは適切に使用すること。闇雲に貼りまくるのはNG。

インデックスショットガン（闇雲インデックス）パターンの説明

NG例：インデックスがまったく作成されていない or インデックスが作成されすぎている。

DBのパフォーマンスを改善する最善の方法はインデックスを効果的に使用することである。

インデックスを更新するためのオーバーヘッドが大きいという話もある。しかし、実運用ではSELECTによるインデックスの使用の方が、INSERTやUPDATEによるインデックスの更新よりも何百倍も多いものである。

インデックスショットガン（闇雲インデックス）パターンの解決策

MENTORの法則に基づいて、正当な理由を元にインデックスを作成する。

  1. Measure(測定)・・・MySQLのスロークエリログ、Postgresのlog_min_duration_statementなどを使って、アプリ実行中の遅いクエリを特定する。
  2. Explain(解析)・・・クエリの実行計画(Query Execution Plan:QEP)を取得する。
  3. Nominate(指名)・・・実行計画を読んで、インデックスを使用しないでテーブルアクセスしている箇所を特定する。
  4. Test(テスト)・・・インデックスを作成後、再び実行計画を確認する。
  5. Optimize(最適化)・・・インデックスが格納されるメモリ量に過不足がないか確認する。
  6. Rebuild(再構築)・・・必要に応じてインデックスを更新したり削除したりする。

SQLクエリのアンチパターン

フィア・オブ・ジ・アンノウン（恐怖のUnknown）

NULLを適切に扱うこと。

フィア・オブ・ジ・アンノウン（恐怖のUnknown）パターンの説明

NG：NULLを一般値として扱っている or 一般値をNULLとして扱っている

NULLを許可するカラムをSELECTするときに、 文字連結やtrue/falseの判断をすると、NULLが返ってくるので注意しよう。

また、NULLを

フィア・オブ・ジ・アンノウン（恐怖のUnknown）パターンの解決策

  • NULL不許可カラムには、NOT NULL制約をつける。
  • NULL許可カラムをSELECTするときは、COALESCE関数を使う。
  • NULL許可カラムの値有無はIS NULL or IS NOT NULLを使う。

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

GROUP BY の結果が曖昧な結果にならないよう気をつけよう。

アンビギュアスグループ（曖昧なグループ）パターンの説明

NG例：GROUP BYに指定していないカラムを、集約関数を使わずにSELECTしている。

Postgre SQLやOracleでは、結果が曖昧になる場合はエラーを返してくれます。
MySQLは、モードによっては曖昧でもエラーを返さないので注意が必要です。

アンビギュアスグループ（曖昧なグループ）パターンの解決策

  • サブクエリやJOINを使用して曖昧な結果にならないようにする。
  • 曖昧な結果になるカラムに対して集約関数が適用できないか考えてみる。

ランダムセレクション

テーブルから適当な1行（サンプルレコード）をSELECTするときは注意しよう。

ランダムセレクションパターンの説明

NG例：SELECT * FROM Xxx ORDER BY RAND() LIMIT 1;
このように、ランダムにソートすると、インデックスが効かないため、データ数が増えるにつれて非常に遅くなってしまう。

ランダムセレクションパターンの解決策

・MySQL/PostgreSQLの場合、このようにする。
Step1.ランダムな行番号を取得する
SELECT FLOOR(RAND()* (SELECT COUNT(*) FROM Xxx));
Step2.ランダムな行番号を指定して取得する
SELECT * FROM Xxx LIMIT 1 OFFSET = step1の結果;
・Oracleの場合、SAMPLE句が使える。
・SQL Serverの場合、TABLESAMPLE句が使える。

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

全文検索をするときは、LIKE ‘%hoge%’ではなく、適切な拡張機能・ツールを使うこと。

プアマンズ・サーチエンジン（貧者のサーチエンジン）パターンの説明

NG例： SELECT * FROM Xxx LIKE ‘%検索ワード%’

  • LIKE述語はインデックスが効かないため、速度が低下してしまう。
  • 意図しないマッチが生じてしまう。

プアマンズ・サーチエンジン（貧者のサーチエンジン）パターンの解決策

全文検索エンジン（Sphinx SearchやApache Lucene）を使用する。または、 RDBMSごとの適切な拡張機能を使用する。

・MySQLの場合
MyISAMストレージエンジンのみ、フルテキストインデックスが使用できる。
（ストレージエンジンはテーブルごとに指定可能）
VARCHAR or TEXTカラムに対して、 FULLTEXT INDEXを作成する。
MATCH関数を指定してSELECT文を実行する。

・Postgre SQLの場合
コンテンツというカラムを検索したい場合、
TEXT型とTSVECTOR型のカラムを用意し、コンテンツを同期させておく。
同期には組み込みのtsvector_update_triggerを使用すると良い。
TSVECTOR列に対して、GIN（汎用転値インデックス）を作成する。
テキスト検索演算子@@を使用してSELECT文を実行する。

  • SQLアンチパターンは、DB設計の基本を学んだ後で読む本。
  • SQLアンチパターンは、パターン名から中身を想像しにくい。

スポンサーリンク

-データベース