はじめに
こんにちは。小川です。
以前から気になっていた「SQLアンチパターン」という本を読みました。
とても良い本で今まで気づかずにやっていたことがSQLアンチパターンなんだと知ることができました。
勉強になったので、学んだことを言語化して量が多いのでいくつかの記事に分けて紹介していこうと思います。
今回は第Ⅱ部のDB物理設計のアンチパターンについて記載します。
この記事は「SQLアンチパターン」の気づき（DB物理設計1）の続きになります。
11章.ファントムファイル（幻のファイル）
アンチパターン
画像をファイルシステムにファイルとして格納し、ファイルパスをVARCHARとしてデータベースに格納するアンチパターンです。
デメリット
- ファイルの削除時における問題
DBで画像へのパスを削除しても、そのパスの指定先のファイルは削除されない。
参照元の行を削除すると同時に、対応する画像ファイルも自動的に削除されるように設計しない限り、「孤児」となったファイルが蓄積されてしまう - トランザクション分離の問題
一般的な設計では、エラーが発生した際にはトランザクションをロールバックします。
DELETEによって参照元の行と同時に対応する画像ファイルも削除する場合に、この変更をロールバックするとDBの業削除はロールバック対象なので元に戻りますが、ファイルはロールバック対象ではないので削除されたままになる。 - データベースのバックアップツール使用時における問題
データベース製品のバックアップツールには、VARCHAR型に格納されたパス名が参照する先のファイルをバックアップ対象にすることができない。
そのために、画像ファイルは別途ファイルシステム用のバックアップツールを実行する必要がある。 - 格納されている画像のパスはただの文字列のため、ファイルパスが正しいものであるかはデータベースでは検証できない。
解決策：必要に応じてBLOB型を採用する
画像を外部ファイルではなく、DBの内部に格納することを考えるべきです。
ほぼすべてのDB製品は、あらゆるバイナリ型を格納できるBLOB型をサポートしている。
12章.インデックスショットガン（闇雲インデックス）
アンチパターン
インデックスを使うか否かの判断をインデックスを理解しないまま使用してしまうアンチパターンです。
デメリット
闇雲にインデックスを使用してしまうと以下の３つのミスのどれかが起こってしまいます。
- インデックスを全く定義しないか、少ししかインデックスを定義しなくなる
- インデックスを多く定義しすぎるか、役立たないインデックスを定義してしまう
- インデックスを活用しないクエリを実行してしまう
解決策：「MENTOR」の原則に基づいて効果的なインデックス管理を行う
最適なインデックス作成を行うためのチェックリストとして、MENTORというものがあります。
MENTORとはMeasure(測定)、Explain(解析)、Nominate(指名)、Test(テスト)、Optimize(最適化)、Rebuild(再構築)のことです。
・Measure：クエリの実行時間を測定し、最大のコストがかかっている操作を識別する
・Explain：実行計画によって、クエリ実行にどのインデックスを使うか判断する
・Nominate：クエリがインデックスを使わないでテーブルにアクセスしている箇所を探す
・Test：インデックス作成後、どの程度の効果をもたらしたか確認する
・Optimize：キャッシュに割り当てるシステムのメモリ量を調整する
・Rebuild：インデックスを再構築する
データとクエリについての理解を深め、MENTORの原則に基づいてインデックスを管理することが大切
まとめ
今回は第Ⅱ部のDB物理設計のアンチパターンについて一部まとめてみました。
この記事はこの本を読んで、私がこういう理解をしましたということを言語化してみました。
とても勉強になる本なので皆様もぜひ読んでみてください。
以上、小川でした。