『SQLアンチパターン』まとめその2（9〜12章）

SQLアンチパターン の9〜12章をまとめました。
この本では以下の4つのパートに分かれており、

1. データベース論理設計のアンチパターン（1〜8章）
2. データベース物理設計のアンチパターン（9〜12章）
3. クエリのアンチパターン（13〜18章）
4. アプリケーション開発のアンチパターン（19〜25章）

この記事では「2. データベース物理設計のアンチパターン」について記述します。

#目次

第9章-Rounding Errors（丸め誤差）
第10章-31 Flavors（31のフレーバー）
第11章-Phantom Files（幻のファイル）
第12章-Index Shotgun（闇雲インデックス）

第9章-Rounding Errors（丸め誤差）

少数を扱う場合に、データ型にFLOAT型を設定するアンチパターンです。

デメリット：丸め誤差が生じる

SQLのFLOAT型では「IEEE754標準」という規格に従って、実数を2進数形式でエンコードされます。
浮動小数点数を2進数形式で表現するため、無限精度が必要な値は「丸め誤差」が生じてしまいます。

例えば、10進数で0.1（有限小数）を2進数に変換すると、

• 0.000110011001100...

となります。「小数点第5位以下切り捨て」で、再び10進数に変換すると、
0.1 - 0.09375 = __0.00625の誤差__が生じてしまいます。

解決策：NUMERIC型かDECIMAL型を使用

固定小数形式のNUMERIC型やDECIMAL型を用いることで、固定制度の小数点数で表現できます。
固定小数点数は小数点の位置が決まっているため、データの解釈が簡単になる反面、表現できる数値の範囲が限られます。
データ型を定義するときに、精度も一緒にデータ型の引数として指定する必要があります。

.sql

ALTER TABLE table_name ADD COLUMN column_name NUMERIC(○,○);

• 第1引数：精度
  • この列の値として使用可能な10進数の桁の総数
• 第2引数：スケール
  • 小数点以下に格納できる桁数

第10章-31 Flavors（31のフレーバー）

限定する値を列定義で指定するアンチパターンです。

例：ユーザー情報を管理するusersテーブル

.sql

CREATE TABLE bugs (
 user_id   PRIMARY KEY,
 user_role ENUM('admin', 'user') NOT NULL,
 name      VARCHAR(10) NOT NULL,
 email     VARCHAR(80) NOT NULL,
);

「user_role」列のようにENUM型で格納できる値を指定することで、無効な値の入力を防止できます。
ENUM型の他にも、以下の方法で入力値を限定することができます。

• 列にCHECK特性を設ける
• ドメイン（DOMAIN）を用いる
• ユーザ定義型（UDT）を用いる
• 許可する値を指定したトリガーを記述する

デメリット

①格納されている値の取得が面倒な場合がある

例えば、usersテーブル内に登録されているデータが全て「user_role = ‘admin’」の場合は、全取得を行っても「admin」しか値が返ってきません。

.sql

SELECT user_role from users;

ユーザー情報の変更画面などで、この結果を用いて選択肢を生成しようとすると、選択肢には「admin」しか表示されないといった問題が生じてしまいます。

②値の追加・削除、更新でカラムの再定義が必要

新しく値を追加する場合は追加（または削除）の構文がないため、ALTER文などで定義し直さなければいけません。

例えば、「user_role」にownerを追加するとします。

.sql

ALTER TABLE users MODIFY COLUMN status ENUM('admin', 'user', 'owner');

さらにALTER文を実行するために、テーブル全体をロックするなど、同時に他のクライアントから接続されていないことを保証する必要があります。
また、値を変更する場合には同様にALTER文などを使用する必要があります。
しかし、値が紐付いている状態では実行できないため、複数のクエリを実行する必要があります。
例えば、「user_role」のadminをownerに変更する場合は、複数回クエリを実行しなければいけません。

.sql

-- 1. ownerをENUMに追加
ALTER TABLE users MODIFY COLUMN status ENUM ('admin', 'user', 'owner');
-- 2. adminをownerに更新
UPDATE users SET user_role = 'owner' WHERE user_role = 'admin';
-- 3. ENUMからadminを削除
ALTER TABLE users MODIFY COLUMN status ENUM ('user', 'owner');

また、廃止された値を参照している既存の行をそのままにしておくケースもあります。
その場合、ユーザーインターフェース側で表示させないようにする必要があります。

③移植が困難

データベース製品によって列定義を限定する方法は統一されていないため、移植が困難な場合があります。
（例えば、ENUM型はMySQL固有の機能）

アンチパターンを用いても良い場合

以下のような相互排他的な2つの値など、値の変更が不要な場合です。

• 左/右
• 有効/無効
• オン/オフ
• 内部/外部

解決策：参照テーブルの作成

今回の例だと、usersテーブルに紐づくuser_rolesテーブルを作成し、usersテーブルでは外部キー制約を宣言することで解決できます。

.sql

CREATE TABLE user_roles (
 role_id PRIMARY KEY,
 user_id NOT NULL,
 role    VARCHAR(10) NOT NULL,
 active  ENUM ('active', 'inactive') NOT NULL,
);

.sql

CREATE TABLE users (
 user_id PRIMARY KEY,
 role_id ENUM('admin', 'user') NOT NULL,
 name    VARCHAR(10) NOT NULL,
 email   VARCHAR(80) NOT NULL,
 FOREIGN KEY (role_id) REFERENCES user_roles (role_id),
);

usersテーブルで使用されていなくても、全ての値を取得できるようになります。

.sql

SELECT role from user_roles;

また、値の追加・削除・更新もALTER文を使用せずに行えるため、容易になります。

.sql

-- 値の追加
INSERT INTO user_roles (role) VALUES ('owner');
-- 値の削除
DELETE FROM user_roles WHERE role = 'owner';
-- 値の更新
UPDATE user_roles SET role = 'owner' WHERE role = 'admin';

廃止された値を参照している既存の行をそのままにしておく場合は、外部キーを設定しているため行の削除はできないので、参照テーブルに廃止を区別する列を追加することで解決することができます。
例ではuser_rolesテーブルの「active」列で、有効な値を検索する際はactive、無効な場合はinactiveを条件に検索することで、廃止された値を参照している既存の行をそのままにしておくケースに対応することができます。

.sql

-- 値の削除（廃止された値を残す場合）
UPDATE user_roles SET active = 'inactive' WHERE role = 'admin';
-- 有効な値の検索
SELECT role FROM user_roles WHERE active = 'active';

また、参照テーブルは標準的なSQLの機能でデータベース製品によって差異がないため、移植は容易となります。

第11章-Phantom Files（幻のファイル）

画像などのバイナリファイルをストレージに保存して、そのファイルパスをVARCHAR型などで定義した列に格納するアンチパターンです。

デメリット

①ファイルパスの削除だけでは画像は削除されない

削除の際にファイルパスだけでなく、参照先のファイルも削除する操作が必要になります。
画像を削除しないと、どこで使用されたかわからない画像データのみが取り残されて、蓄積してしまいます。

②ロールバック時にトランザクション分離が起こる

データベースのデータの更新や削除処理は、トランザクションをコミットするまで他のクライアントには変更が見えないようになっています。
しかし、ファイルがデータベースの外にある場合は、ファイルの変更や削除を行うと他のクライアントはすぐに変更が反映されたり、アクセスできなくなってしまいます。

このことがロールバック時に大きな問題となってしまいます。
通常のデータベース設計では、何かしらのエラーが発生した場合はトランザクションをロールバックします。
ロールバックにより画像の参照先のファイルパスは復元することができますが、外部に画像を保存している場合には、__ファイルはロールバック対象外のため削除されたまま__となってしまいます。

③バックアップの保証ができない

データベース製品は、基本的にバックアップを支援するクライアントツールを提供しています。
しかし、バックアップツールには文字列で格納されたファイルパスの参照先のファイルをバックアップの対象にできません。
そのために、画像ファイルは別途ファイルシステム用のバックアップツールを実行する必要が出てきてしまいます。
また、仮に両方のバックアップを行ったとしても、データベースの方のバックアップ時のトランザクションと同期していることは保証できません。

④データベースで検証できない

格納されている画像のパスはただの文字列であり、ファイルパスが正しいものであるかはデータベースでは検証できません。
当然ですが、ファイル名の変更やファイルの移動や削除を行ってもデータベース内のファイルパスは自動的に更新されません。
データの整合性を維持するために、アプリケーション側でコードを記述する必要が出てきます。

アンチパターンを用いてもよい場合

• データベースの容量を減らしたい場合
• データベースのバックアップを短時間で終了したい、バックアップファイルの容量を抑えたい場合
• 画像ファイルの加工を容易に行いたい場合
  • データベースの外にあるとプレビューや編集が容易になるため

解決策：画像のバイナリデータをBLOB型に保存する

このBLOBデータ型は画像をデータベース内部に格納しているため、上記のデメリットは解消されます。

• データベースのファイルパスの削除時に、画像も自動的に削除されるようになる
• 画像の変更はコミットするまで他のクライアントから参照できない
  • トランザクションをロールバックすると画像も復元される
• データベースのバックアップ時には、全ての画像が含まれる
• 誤ったファイルパスを格納するリスクがない
  • アプリケーション側で、データの整合性を維持するためのコードを記述する必要がない

第12章-Index Shotgun（闇雲インデックス）

インジェクションを理解しないで、インデックスの使用可否を決定してしまうアンチパターンです。
インデックスを理解していないと、以下の３つのどれかのパターンに陥ってしまいます。

①インデックスを全く定義しないか、少ししか定義しない

テーブルに新しいデータが追加されたり既存のデータが更新されたりすると、インデックス自身も自動的に更新されます。
そのため、何千万行レベルのINSERTやUPDATEを行ってしまうと、その分時間もかかってしまいます。
一部の開発者は「オーバーヘッドが無駄なものだ」という考えを持ち、インデックスそのものを排除するということが起こります。
しかし、インデックスにはオーバーヘッドを正当化するだけのメリットがあるため、一概に排除すべきではありません。
インデックスを定義していないと、テーブルの全検索が増えてしまい、パフォーマンスが落ちてしまいます。
通常のアプリケーションでは、更新よりも参照の方が多いため、インデックス維持のためのオーバーヘッドを取り戻せます。

②インデックスを多く定義し過ぎるか、役に立たないインデックスを定義してしまう

インデックスのメリットを得られるのは、インデックスを使用している列のクエリを実行するときです。
そのため、使用されないインデックスを作成する必要はありません。
インデックスを理解していないと、どのインデックスがどんなクエリに役立つかわからないため、テーブルの複数列や列の組み合わせに対してインデックスを定義してしまいます。
使用しない列に対してインデックスを定義した場合、メリットを得られずに無駄なオーバーヘッドを増やす要因となってしまいます。

例：バグを管理するためのbugsテーブル

.sql

CREATE TABLE bugs (
 bug_id        PRIMARY KEY,
 date_reported DATE NOT NULL,
 summary       VARCHAR(80) NOT NULL,
 status        VARCHAR(10) NOT NULL,
 hours         NUMERIC(9,2),
 INDEX(bug_id),                     --(1)
 INDEX(summary),                    --(2)
 INDEX(hours),                      --(3)
 INDEX(bug_id,date_reported,status) --(4)
);

(1)〜(4)は不要なインデックスの例です。

• (1)プライマリーキーのため、インデックスが自動生成されるため、定義する必要がない
• (2)長い文字列を格納するデータ型へのインデックスは、他と比較してサイズが大きくなってしまう
  • サマリー情報などで検索やソートをかけることはあまり考えられない
• (3)時間に対しても索やソートをかけることはあまり考えられない
• (4)複合インデックスは左から絞り込まれるため、bug_idが一意のためこのように設定するメリットがない

③インデックスを活用しないクエリを実行してしまう

クエリによってはインデックスが活用されないことがあります。
このようなクエリを使用していると、インデックスを作成した意味がなくなってしまいます。
いくつか例を示します。

.sql

-- 複合インデックスで先頭のカラムを無視するクエリは、無駄になってしまいます
SELECT * FROM accounts ORDER BY first_name, last_name;

-- インデックスの順番は月を基準とした検索に役に立ちません（インデックスの順番は年ごとに1/1を開始日とした日付に基づき、どの年にも4月は存在するため）
SELECT * FROM bugs WHERE MONTH(date_reported) = 4;

/*
first_nameの特定はできず、加えて以下のクエリと同じ扱いになります
SELECT* FROM accounts WHERE last_name = 'hoge'
UNION
FROM accounts WHERE first_name = 'hoge'
*/
SELECT * FROM accounts WHERE last_name = 'hoge' OR first_name = 'hoge';

-- 文字列のどの部分とも一致する可能性があるため、ソートされたインデックスデータは役に立ちません。
SELECT * FROM bugs WHERE description LIKE '%crash%';

解決策：「MENTOR」の原則に基づいたインデックス管理を行う

• MENTOR
• M：Measure（測定）
• E：Explain（解析）
• N：Nominate（指名）
• T：Test（テスト）
• O：Optimize（最適化）
• R：Rebuild（再構築）

Measure（測定）

DB処理の測定とパフォーマンスが悪いクエリを抽出します。
測定機能は、それぞれのデータベースによって提供されているものが異なります。

データベース製品	使用する機能	機能の概要
Microsoft	SQL Server Profiler	SQLのトレース機能と、トレース結果のレポートと分析
Oracle	TKPROF	SQLのトレース機能と、トレース結果のレポートと分析
MySQL	スロークエリログ	指定された閾値より実行時間が長くかかったクエリを記録
PostgresSQL	log_min_duration_statement	指定された閾値より実行時間が長くかかったクエリを記録
PostgresSQL	pgFouine	クエリログの分析と注目すべきクエリの識別を支援

Explain（解析）

クエリが遅くなっている原因を解析します。
そのためにクエリの実行計画(Query Execution Plan:QEP)を取得します。
これによって、クエリ実行にどのインデックスを使うかを判断することができます。

各データベース製品におけるQEP取得方法

データベース製品	QEPレポート機能
IBM DB2	EXPLAIN,db2explnコマンド または Visual Explain
Microsoft SQL Server	SET SHOWPLAN_XML または Display Execution Plan
MySQL	EXPLAIN
Oracle	EXPLAIN PLAN
PostgresSQL	EXPLAIN
SQLite	EXPLAIN

Nominate（指名）

クエリの実行計画から、インデックスが未定義といったクエリ内のボトルネック箇所を特定します。

Test（テスト）

インデックスを作成後(インデックス作成でなくても何か策を講じたら）、処理時間を測定など、再び実行計画を確認します。

Optimize（最適化）

キャッシュのサイズなど、インデックスが格納されるメモリ量に過不足がないか確認します。

Rebuild（再構築）

インデックスを再構築します。
できる限りインデックスの効率を高めたいのであれば、以下のコマンドを用いて定期的にメンテナンスする必要があります。

各データベース製品におけるインデックスのメンテナンスコマンド

データベース製品	インデックスのメンテナンスコマンド
IBM DB2	REBUILD INDEX
Microsoft SQL Server	ALTER INDEX ... REORGANIZE, ALTER INDEX ... REBUILD, または DBCC DBREINDEX
MySQL	ANALYZE TABLE または OPTIMIZE TABLE
Oracle	ALTER INDEX ... REBUILD
PostgresSQL	VACUUM または ANALYZE
SQLite	VACUUM