SQL アンチパターン

作者: Bill Karwin,和田卓人(監訳),和田省二(監訳),児島修
出版社/メーカー: オライリージャパン
発売日: 2013/01/26
メディア: 大型本
購入: 9人クリック: 698回
この商品を含むブログ (41件) を見る

ファントムファイル(幻のファイル)

画像などのバイナリファイルをストレージにおいて、そのパスをDBに格納するのをアンチパターンとして紹介している
本書でも絶対にストレージに置いてはダメだとは書いては無い、常に2つの設計を検討すると書いてある
私はDBよりも安価になっていくS3などのストレージに保存した方がいいと思っていて、その場合にはどのようなテーブル構成がいいのかを記録しておこうと思って書く

まずはファントムファイルとは何かをざっとまとめとく

画像のファイルパスを保存する場合

下記のようなテーブルを作成してファイルパスを保存する

CREATE TABLE Screenshots (
  bug_id BIGINT UNSIGNED NOT NULL,
  image_id BIGINT UNSIGNED NOT NULL,
  screenshot_path VARCHAR(100),
  caption VARCHAR(100),
  PRIMARY KEY (bug_id, image_id),
  FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id)
);

screenshot_path VARCHAR(100)で画像のファイルパスを保存
この設計がアンチパターンと紹介されている

DBではなくストレージにファイルを格納する場合のリスク

DELETEしても実画像ファイルが一緒に削除されることが保証されていない(「孤児」となったファイルが蓄積されていく恐れ)
DBとファイルのトランザクションの問題
- ファイルの内容を変更するとDBでコミットされる前に、他のクライアントがその変更されたファイルを参照する可能性がある
- 削除する場合は、ファイルを削除してDBでコミットに失敗した場合にファイルは元に戻らない、あるいは先にコミットしてファイルの削除に失敗したら孤児ファイルになる
バックアップが難しい
- DBのバックアップツールでファイルパスだとその時点のデータをexportすることが保証されない
- ファイルのバックアップも別途実施する必要がある
- 2つのバックアップを仮に同時に行ったとしてもトランザクション付きでバックアップツールを実行できる訳でもないので厳密にその時のスナップショットを取るのが難しい
RDBにあるアクセス権限制御が使えない(GRANT/REVOKE)
ファイルパスはSQLデータ型ではないのでDBで検証できない
- 外部ファイルにはFKを付けられない
- カラムをNot Nullにしても実データは本当にそのパスにあるかわからない

本に書いてあるアンチパターンを用いてもよい場合

データベースの容量を減らしたい
データベースのバックアップを短時間で終了したい
バックアップファイルの容量を抑えたい
- 画像をデータベースに格納しないことで巨大なサイズのデータベースをバックアップするよりも管理がしやすくなる
画像ファイルの加工が容易になる

画像のバイナリデータをBLOBに保存する

下記のようなDDLを用いてバイナリデータを保存することで上述したリスクを軽減できる

CREATE TABLE Screenshots (
  bug_id BIGINT UNSIGNED NOT NULL,
  image_id SERIAL NOT NULL,
  screenshot_image BLOB,
  caption VARCHAR(100),
  PRIMARY KEY (bug_id, image_id),
  FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id)
);

screenshot_image BLOBで画像を保存

ここまでが本に記載してある内容

それでもファイルをストレージサーバに置く理由

ストレージと言ってもクラウドストレージを使う、大体の理由はクラウドストレージの利点を受けたいからですね

DBサーバは比較的高価でS3などのファイルストレージは安い、これからも安くなっていく事が見込まれる
サーバのファイル容量を気にしなくていい
期限付きURLの発行などがクラウドストレージの機能で可能
Webサーバを経由せずに配信が可能になる
認証をかけたい時はWebサーバで受けて、X-Reproxy-URL, X-Accel-Redirectヘッダーを付けてNginx経由で配信させる手段を取れる
- (クラウドストレージの認証機能も要件にあえば使える)

ファイルパスを保存するときのテーブル設計

前振りが長かったのですが、ここからが本題です。

上記にあげたリスクの中で下記のDBとファイルのトランザクションの問題が一番嫌だなーと思っていて、その対応のためのテーブル設計案です。

ファイルの内容を変更するとDBでコミットされる前に、他のクライアントがその変更されたファイルを参照する可能性がある

削除する場合は、ファイルを削除してDBでコミットに失敗した場合にファイルは元に戻らない、あるいは先にコミットしてファイルの削除に失敗したら孤児ファイルになる

例としてGmailの用にメールを送信するアプリで添付ファイルが一つしか付けられないという事にしましょう。問題をシンプルにするために添付ファイルは1対Nではなく1対1にします。ストレージはS3を例にします。

メールのリソースに対してパスを持つテーブル

単純に作ろうとすると下記のようになりますが、これをやってしまうとトランザクションの問題が顕著に影響を受けて困ります。

CREATE TABLE Emails (
  id BIGINT UNSIGNED NOT NULL,
  subject VARCHAR(255),
  body VARCHAR(255),
  to_address VARCHAR(255),
  file_path VARCHAR(255),
  PRIMARY KEY (id)
)

ブラウザでメールの削除ボタンを押したとして、削除しようとした時に一緒にS3のファイルも消す必要がありますが、S3のAPIを使って削除もしようとするとレスポンスが遅くなりますし、ファイルを削除してDBでコミットに失敗した場合にファイルは元に戻らないみたいなことが発生する可能性があります。

ファイルはファイル専用のテーブルを作る

CREATE TABLE Emails (
  id BIGINT UNSIGNED NOT NULL,
  subject VARCHAR(255),
  body VARCHAR(255),
  to_address VARCHAR(255),
  cloud_storage_id VARCHAR(255),
  PRIMARY KEY (id)
)

CREATE TABLE CloudStorages (
  id BIGINT UNSIGNED NOT NULL,
  file_path    VARCHAR(255),
  delete_flag SMALLINT(1) NOT NULL,
  PRIMARY KEY (id)
)

各機能のテーブルに直接FilePathを保存しないようにして、削除ボタンをクリックされた場合はEmailsレコードは単純にDELETEして、CloudStorageレコードはdelete_flagを更新するようにし、非同期で別途削除するようにします。

こうすることでファイルを削除してDBでコミットに失敗した場合にファイルは元に戻らないみたいなことが発生しないようになります。

ファイルの内容を変更するとDBでコミットされる前に、他のクライアントがその変更されたファイルを参照する可能性がある

この問題については、ファイルを更新する際にCloudStoragesテーブルにINSERTしてEmailsのcloud_storage_idをINSERTしたIDに変更し、元のCloudStoragesレコードはdelete_flagを更新します。

別解としてCloudStorageテーブルを作成しなくても、Emailsを削除するときに削除されたという削除イベントのテーブルを作成し、そこにfile_pathを持たせることで単純にパスを持つテーブルでも対応しようと思えばできます。

※論理削除フラグは〜って言いたくなったらstatusカラムに変更するかw CloudStorageテーブルを作成した場合でも削除イベントのCloudStorageテーブルを作って下さい。今回の話題とは直接関係ないので割愛します。

あおうさ＠日記

#SQLアンチパターンファントムファイルに立ち向かう