Zenn
📘

SQLアンチパターン 要約 第二部

2024/07/23に公開
SQL
技術書
読書
tech

SQLアンチパターンを読んだ際の学習ノートです。本書は4部に分けられており、学習メモ2では第二部「データベース物理設計のアンチパターン」をまとめます。

https://amzn.to/3S7Rv6O

学習メモ1はこちら

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

小数の扱いに関するアンチパターンです。

目的

小数点を正しく扱い、正確な計算を行う。

アンチパターン: FLOATデータ型を使用する

FLOATデータ型を使用するのはアンチパターンです。

何がまずいか

FLOAT型はIEEE754標準に従って実装されています。
浮動小数点数は2進数で表現されるため、多くの場合、数値は正確に収めることができず、近似値が用いられます。
例えば、59.95を格納しようとした場合、59.950000762939のようになります。

特に積の計算を行う際は誤差部分の影響が大きくなるため、要注意です。

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

科学技術計算を行うアプリケーションであればFLOATが最適です。

解決策: NUMERICデータ型を使用する

NUMERIC型を用いて固定小数点数を表すようにすると良いです。

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

NUMERICの第一引数は精度、第二引数はスケールと呼ばれ、精度は小数点部分も含めた桁数の最大値、スケールは小数点部分の桁数の最大値です。
hoursは精度が9なので、123456789は格納できますが、1234567890は格納できません。また、スケールは2なので、1234567.89は格納できますが、1234567.890は格納できません。

NUMERICであれば、FLOAT型とは異なり、有理数を丸めることなく格納できます。59.95は59.95と記録することができるので、予期しない計算結果になりにくいです。

なお、DECIMAL型もNUMERIC型と同じ振る舞いをし、データ型の間に違いはありません。

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

目的

列を特定の値に限定したいです。例えば、statusカラムに入る値が'NEW', 'IN PROGRESS', 'FIXED'のどれかになるようにしたいです。

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

以下のようにCHECK制約やENUMを利用して目的を果たすことはできますが、これはアンチパターンです。

-- チェック制約の例
status VARCHAR(20) CHECK (status IN ('NEW', 'IN PROGRESS', 'FIXED'))

MySQLではENUMが利用できます。

-- ENUMの例
status ENUM('NEW', 'IN PROGRESS', 'FIXED')

なぜダメなのか

  1. 有効値の取得が難しい
    statusを更新できる画面に入力可能な文字列をドロップダウンで選択できるようにしたい場合、どのように実装すれば良いでしょうか。

    • 'NEW', 'IN PROGRESS', 'FIXED'を直接ハードコードした場合、新しい文字列を許可する際にこちらも変更しなければなりません。
    • SELECT DISTINCT status FROM Bugs;のようなクエリでは、事前に全ての文字列のレコードが入っている必要があります
    • ENUMの中身などのメタデータ取得のためのシステムビューのクエリは複雑になりがち

  2. 有効値の追加削除が面倒
    有効値の追加、削除ではALTER TABLEを実行する必要があります。
    データベース製品によってはテーブルが空でない限り、列定義を変更できないものもあり、
    この場合はテーブルのアクセスをブロックした上で、テーブル内容をダンプし、テーブルを再定義し、保存したデータを読み込むという作業が必要になります（この作業はETLと呼ばれる）。
    また、例えば'FIXED'を'CODE COMPLETE'と'VERIFIED'の2つのステージに分けたくなった場合、過去の'FIXED'をどちらに振り分けるのか、あるいはNULLやその他のデフォルト値にするのか考える必要があります。

  3. 移植が困難
    さらに、CHECK制約は各種データベース製品間で仕様が統一されておらず、ENUMデータ型もMySQLに固有なので移植が困難になります。

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

Left/Right, ON/OFF など、有効値の変更が不要と断定できる場合は問題ないです。

解決策: 限定する値をデータで指定する

参照テーブルを作り、有効値を一つずつ追加していく方法が良いです。

CREATE TABLE BugStatus (
    status VARCHAR(20) PRIMARY KEY
)

INSERT INTO BusStatus (status) VALUES ('NEW'), ('IN PROGRESS'), ('FIXED');

その上で、statusに外部キー制約を宣言します。

CREATE TABLE Bugs (
...略
status VARCHAR(20)
FOREIGN KEY (status) REFERENCES BusStatus(status) 
    ON UPDATE CASCADE
);

1の有効値を取得するにはSELECT status FROM BugStatus ORDER BY statusのようなクエリで可能です。

2についてはINSERT INTO BugStatus (status) VALUES ('DUPLICATE');で挿入ができ、
変更はON UPDATE CASCADEオプションを指定してある場合はUPDATE BugStatus SET status = 'INVALID' WHERE stauts = 'BOGUS';でできます。
値の削除は難しい場合がありますが、有効無効を管理できるカラムを追加することができます。

ALTER TABLE BugStatus ADD COLUMN active ENUM('INACTIVE', 'ACTIVE') NOT NULL DEFAULT 'ACTIVE';

参照テーブルを用いた解決策は標準的なSQL機能を利用しているため、移植が容易です。

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

目的

画像をはじめとする大容量メディアファイルを格納したい

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

画像のデータをBLOBとしてデータベースに直接書き込むか、ファイルシステムにファイルとして格納し、そのパスをVARCHARとして格納するかは
ソフトウェア開発者の間で議論の魔都になっています。ファイルパスを書き込む派が大多数ですが、これにはリスクも存在します。

なぜダメなのか

デメリットの一つにガベージコレクションの問題があります。レコードを削除した際に外部に保存した画像ファイルを削除するようにアプリケーションを設計していない限り、
孤児となったファイルが蓄積されていきます。

トランザクション分離の問題もあり、例えば更新時にはコミットが完了する前にファイルが見えてしまいます。
削除のクエリをロールバック時にはファイルが消えて元に戻せなくなる可能性もあります。

ファイルパスはただの文字列として格納されるだけなので、DBでは正当なパス名かどうかは検証されません。また、指定したパスにファイルが存在することも検証できません。
この辺りのロジックはアプリケーションコードに依存します。

DBのバックアップツールにおいて外部ファイルは対象でないことや、SQLアクセス権限と外部ファイルのアクセスは異なる点も注意すべきです。

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

データサイズの大きいオブジェクトを外部のファイルシステムに保存することには以下のようなメリットがあります。

  • DBの容量を減らすことができる
  • DBのバックアップが短時間で終了する
  • 外部ファイルのプレビューや編集が容易になる

これらの利点がプロジェクトにとって特に重要であり、デメリットも大きな問題にならないのであれば、こちらを採用しても良いでしょう。

解決策

アンチパターンの節の問題のいずれかに該当する場合はBLOB型を採用しましょう。

screenshot_image BLOB,

BLOB型の最大サイズはデータベース製品によって異なるものの、ほとんどの場合画像を格納するのに十分な容量があります。
MySQLでは最大16メガバイトを保存できるMEDIUM BLOB型があります。
また、DBに便利な機能が組み込まれていることもあり、例えばMySQLのLOAD_FILE関数はファイルの読み込みとBLOB列への格納に使用できます。

Update Screenshots
SET screenshote_image = LOAD_FILE('/image/screenshot-1.jpg')
WHERE image_id = 1;

また、MySQLには読み込み結果をそのままファイルに格納する機能もあります

SELECT screenshot_image
INTO DUMPFILE 'images/screenshot-1.jpg'
FROM Screenshots
WHERE image_id = 1;

12章 インデックスショットガン

目的

パフォーマンスの最大化をしたいです

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

インデックスを理解しないままどこにインデックスを貼るかの判断をしてしまうと以下のようなミスが起こります

  • インデックスを全くかほとんど定義しない
  • インデックスを多く定義しすぎるか、役立たないインデックスを定義してしまう
  • インデックスを活用しないクエリを実行してしまう

以下の例では役に立たないインデックスがいくつかあります。

CREATE TABLE Bugs (
   bug_id SERIAL PRIMARY KEY,
   date_reported DATE 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について、主キーはほとんどのデータベースで自動的にインデックスが作成されるため、冗長である可能性が高いです。
2について、長い文字列を格納するデータ型へのインデックスはサイズが大きくなるほか、サマリー情報が格納されている列にソートや検索を行うことはあまり考えられません。
3について、こちらも同じく、特定の列を検索することはほとんどないと考えられます。
4について、複合インデックスはいくつもメリットがありますが、多くの場合冗長であったり、使用頻度が低くなりがちです。
また、複合インデックスは列の順序が重要で、検索、結合、ソートの条件では列を定義した順に使わなければなりません。

インデックスを定義するとオーバーヘッドが発生するため、インデックスを使うクエリを実行しない場合は定義するべきではありません。

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

汎用的なデータベースの設計ではどのようなクエリを最適化しなければならないか理解していなければ、最適なインデックスは分かりません。
ですが、十分な情報に基づいた検討をすべきです。

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

最適なインデックス作成を行うためのチェックリストとして、MENTORがあります。
MENTORはMeasure（測定）、 Explain（解析）、Nominate（指名）、 Test（テスト）Optimize（最適化）、Rebuild（再構築）の頭文字です。

Measure

最大のコストがかかっている操作を識別しましょう。例えば、MySQLでは指定された閾値より実行時間が長くかかったクエリを記録できるスロークエリログという機能があります。
アプリケーションで時間のかかるクエリを特定しておけばどのクエリから最適化を着手するべきかが分かります。
実行に最も時間がかかるクエリでも、頻度が低い場合にはアプリケーションにとって最大のコストであるとは限りません。その場合は実行頻度の高いクエリの最適化に注目しましょう。

クエリパフォーマンスの測定中は、クエリ結果のキャッシュを全て無効化しましょう。キャッシュが残っていると正確な測定ができません。

プロファイリング機能を利用して、本番のデータベースについて、どのくらい時間が掛かっているかの統計値を収集できます。新たなボトルネックが生まれていないことを確認するため、
定期的にプロファイリングデータを監視しましょう。また、プロファイリングツールを使用する際にはオーバーヘッドが生じるため、測定終了後には無効化するか報告レートを低くしましょう。

Explain

最もコストのかかるクエリを特定した後は、EXPLAINを利用してクエリ実行計画（QEP）レポートを取得しましょう。
一般的なQEPレポートでは、クエリに関連するテーブル、オプティマイザによるインデックスの選択理由、テーブルへのアクセスの順番が示されます。
読み方は https://dev.mysql.com/doc/refman/8.4/en/using-explain.html を参考にしてください。

Nominate

QEPを読んでインデックスを活用せずにアクセスしている箇所を探します。
クエリのトレース統計を収集し、変更を提案してくれるツールもあります。例えば、MySQLにはMySQL Enterprise Query Analyzerがあります。

Test

テストではインデックスの作成後、再度クエリのプロファイリングを行、効果を確認します。

Optimize

インデックスは使用頻度の高いデータ構造であるため、キャッシュメモリに格納されやすいです。データベースサーバではキャッシュに割り当てるメモリ量を設定できます。
どのくらいのメモリを割り当てるべきか正解はありません。

キャッシュへの格納をデータベースに依存するのではなく、あらかじめインデックスをキャッシュメモリにロードすることでメリットが得られる場合もあります。
MySQLではLOAD INDEX INTO CACHEステートメントを利用できます。

Rebuild

インデックスは長期にわたる追加や削除で不均衡になり、パフォーマンスが落ちる可能性があります。そのため、できる限りインデックスの効率を固めたいのであれば定期的なメンテナンスが必要です。
MySQLでは ANALYZE TABLEやOPTIMIZE TABLEなどのコマンドを利用することができます。

インデックスを再構築する頻度に正解はありません。

GitHubで編集を提案

Discussion