バグ解消法、お役立ち情報など

SQLアンチパターンの内容をまとめます その2

black cat

内容まとめ

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

FLOAT型を使うことにより、丸め誤差が発生してしまうアンチパターンです。

解決策

NUMERIC型を使いましょう！

ALTER TABLE Bugs ADD COLUMN hours NUMERIC(9, 2)

NUMERIC型は、FLOAT型とは違い丸め誤差が発生しません。

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

テーブルに格納する値がすでに全て判明している場合に、安易にCHECK制約やENUMを使用してしまうというアンチパターンです。

後日新たに値を追加することになってしまうと、面倒なことになります。

解決策

別テーブルを作成し、外部キー制約を付与しましょう！

BugStatusテーブル
CREATE TABLE BugStatus (
  status VARCHAR(20) PRIMARY KEY
);
Bugsテーブル
CREATE TABLE bugs (
  status VARCHAR(20),
  FOREIGN KEY (status) REFERENCES BugStatus(status)
    ON UPDATE CASCADE
);

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

カラムの値としてサーバー上のファイルのパスを保持してしまう、というアンチパターンです。

もしカラムの値が消えてしまったら、どのファイルがどこにあるかわからなくなってしまいます！

解決策

BLOB型を採用しましょう！

BLOB型であれば、画像などのバイナリデータをそのまま格納することができます。

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

インデックスについて理解していないために発生するアンチパターンです。

具体的には以下のような状況が起きてしまいます。

  • インデックスを全く使用しない
  • インデックスを多く定義しすぎるか、役に立たないインデックスを定義してしまう
  • インデックスを活用しないクエリを使用してしまう

解決策

MENTOR原則にもとづいて効果的なインデックス管理を行いましょう！

MENTOR原則とは、以下の頭文字をとった言葉です。

  • Measure（測定）
  • Explain（解析）
  • Nominate（指名）
  • Test（テスト）
  • Optimize（最適化）
  • Rebuild（再構築）

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

nullを一般値として扱ってしまう、というアンチパターンです。

計算式などでnullが絡むと、思いがけない結果になるというのはよくある話だと思います。

だからといってnullを必要以上に避けてしまうと、これまたアンチパターンとなります。

解決策

nullを一意な値として扱いましょう！

nullを計算に使ったり、結合したりしてはいけません。

必要に応じてNOT NULL制約を使用しましょう。

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

GROUP BYで集計した場合にMAX関数を使用すると、想定通りの最大値が得られない、というアンチパターンです。

解決策

関数従属性のある列のみにクエリを実行しましょう！

GROUP BYで指定した列のみを取得する、というイメージです。

他にも、サブクエリを使用することでも解決できます。

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

ランダムにソートしてしまうことによりパフォーマンスが落ちる、というアンチパターンです。

以下のようなクエリが一例です。

ランダムにソートしてしまうクエリ
SELECT * FROM Bugs ORDER BY RAND() LIMIT 1;

RAND関数を使用することで、インデックスが効かなくなってしまいます。

解決策

欠番のないテーブルであることが保証されているのであれば、以下のクエリが最適です。

欠番がないテーブルの前提
SELECT b1.*
FROM Bugs b1
INNER JOIN (
  SELECT CEIL(RAND() * (SELECT MAX(bug_id) FROM Bugs)) rand_id
) b2 ON b1.bug_id = b2.rand_id;

欠番が存在する場合は、アプリケーション側で対応するのが良さそうだと感じました。

例えばLaravelでは、以下のように記述することでランダムな値を取得できます。

Laravel
Model::inRandomOrder()->first();

Collectionではrandomメソッドが使えます。

ランダムに1件取得
Collection::random();

以上です。

次回もお楽しみに！

← TOP
バグ解消法、お役立ち情報など