(cache) 画像もDBに格納して管理する－扱いがめんどうなLOB(ラージオブジェクト)は使わない方法も含め

結論：

ブログにせよECサイトにせよ、およそWebサイトを構成しているデータはテキストだけでなく画像もある。
しかしデータベースに格納するのはテキストデータだけで、画像データまでDBに入れるということは考えられていない場合がほとんど。
でもよくよく考えると、データはデータ層＝DB＝に格納する、という一元管理の基本にのっとったほうがやっぱりいいんじゃないだろうか？
容量食いすぎ？いや、Youtubeみたいなサイトならともかく、そもそも普通のWebサイトに載ってる画像なんて容量小さいし、最近ストレージ安いし。
ラージオブジェクト型は扱いづらい？たしかに。でもだったら画像をbase64エンコードしてテキスト化して文字列型カラムにつっこんどくのもいいのでは。
DB層やアプリ層に負荷がかかる？そこはmod_rewriteでキャッシュもどき作戦をとればいい。

というお話。なお以降は初心者でもなんとかわかるように書くために詳しい人にとってはまわりくどくなることをご了承ください。

「Web層－AP層－DB層」
誰もが知ってる基本3層構造である。ちなみに、「プレゼンテーション層－アプリケーション層－データ層」という書き方がされたりもする。残念ながらここで紹介されてた基礎知識には無かったけど。

Web層に使われているのがたいていApacheかIIS。AP層はperl/PHP/Java等でつくられたプログラムコードとそれを実行するためのAPサーバ。JavaだとTomcatが多い。perl/PHPを使っている場合はmod_php5、mod_perlのような形でApacheに寄生する感じで動くのでWeb層とAP層の区別がつかなかったりもする。

そしてDB層。Oracle、MySQL、PostgreSQLといったデータベースソフトが稼動する。ブログサイトなら記事のテキストとか、ECサイトなら商品説明とか注文データとかそういうのが格納される。

で、ここで問題になるのが画像ファイルのとりあつかい。典型的な例で言うと、ECサイトの商品の画像は概念的に言ってどの層に格納されるべきなのか？という問題である。

出来合いのECサイト構築ソフトであれ手作りなWeb-DBシステムであれ、たいていの場合は画像ファイルはファイルのままでファイルシステム上のどこかに格納しておき、ファイル名やその形式等の情報だけをDBに格納しているというケースがほとんどである。

なぜならDBは画像のようなバイナリデータの格納にはいろんな面で弱いから。そもそもデータベースソフトのほとんどが数値あるいは文字のデータを格納、整理する目的で開発されたという歴史的事情もある。

もちろん、最近のモダンなDBのほとんどがラージオブジェクト型とよばれるバイナリデータの取り扱い機能を備えている。がしかし、方言っぽくて面倒な特殊SQL文、少ない技術情報、意外な性能劣化、バックアップ時の思わぬトラブルなどなどいろいろあって個人的にはあまり好きではない。筆者の周辺でもラージオブジェクト型の利用には否定的な意見が多い。(oracleが多いからかなあ）

結局、画像はファイルのままで置いといて、ファイル名だけDBで管理するということになる。がしかし、例えばECサイトでは商品の画像は商品のページで見えさえすればよいというわけではない。商品管理画面のような、いわゆる店の中の人だけが見れるページにおいても、商品画像は見えて、かつ、メンテのために追加したり削除したりも可能でなければならない。

これがデータベースであれば、データを取り扱う画面が複数にわたろうともSQL文を流すだけで見るも書くも消すも自由自在なのだが、データがファイルの形で格納されているとなるとそうも行かない。追加や削除にはファイルの所有権とWeb層、AP層での実行ユーザーの権限とのかねあいが出てくる。そもそもWeb層/AP層が複数台サーバーで構成されていると、NFSサーバをつくってそこに置くとかまでやらなければならなくなる。DB内のデータ（ファイル名とか）との同期にも気を使わなければならない。

というのが、最近常々、疑問というか不満に感じていたことである（前フリ長っ！）。どうにかして画像のようなバイナリデータもDBに格納して文字/数値情報もろとも一元管理できないものか？当たり前とかしょーがないとかで済ますのは簡単だけどそこで思考停止もなあ。

で、考えた。

ラージオブジェクト型じゃなくて、text型でやればいい

例えばPostgreSQLではtext型という、大きな文字列（少なくとも2Gbyteはいける）を格納できるデータ型がある。普通によく使われているので、これで画像データを格納すればいい。格納のときには画像をbase64エンコードで文字列に変換しておく。base64はメールに添付ファイルをつけるときに使われているごく一般的な方式である。PHPでもJavaでもperlでもどんな言語でも共通にbase64形式を取り扱うことができる。

いややっぱり画像データはでかいよ、という話もあるが、よくよく調べてみると5000の商品があるECサイト上の画像データを全部数えてみたら600Mbyte以下でした、なんてことだったりする。 base64エンコードすることでデータサイズが3割増しになり、DBに格納することでさらに内部的な付加情報がついて膨れ上がるが、それでも数ギガに収まってしまう。何年か前ならいざしらず現代のストレージの性能と価格から考えれば微々たる物である。

通常の文字/数値データを格納したテーブルと一緒にしておくとディスクI/O負荷がやばくね？という問題については、別のストレージ上で別のTABLESPACEを切っておいてそこに画像格納用テーブルを置くといったことで対応できるだろう。

DB上の画像データをファイルシステム上にキャッシュしておくためのmod_rewrite活用

画像がDB上にあるということは、例えばHTML上にはこんな感じでimgタグを書くのか？

<img src="getimage.php?imageid=123456" />

いやいや、こんなのをECサイトの商品一覧画面上にたくさん埋め込んでいたら、それこそAP層もDB層も過負荷で悲鳴をあげてしまうだろう。そこで、こうする。

商品一覧画面や商品詳細画面のページ内部に埋め込まれた、商品画像を表示するタグ：

<img src="imgdir/dbimage_123456.jpg" />

Apacheのmod_rewriteの設定：

RewriteCond %{REQUEST_FILENAME} ^dbimage_\d+\.(jpg|gif|png)$
RewriteCond %{REQUEST_FILENAME} !-f
RewriteRule ^.*dbimage_(\d+)\.(jpg|gif|png)$ /getimgfromdb_and_makefile.php?id=$1&type=$2 [R]

このカラクリはこうである。

最初の訪問者が商品番号123456のページにアクセスする。http://ec.example.com/product.php?id=123456 とか。
ブラウザはそのHTML内部に書かれたimgタグにそって、http://ec.example.com/imgdir/dbimage_123456.jpg へ開アクセスする
ここでmod_rewriteが働く。ファイル名が「dbimage_数字.jpg(またはgif/png)」で、かつ、そのファイルが存在しない場合に、404エラーを返すのではなく302リダイレクトで http://ec.example.com/getimgfromdb_and_makefile.php?id=123456&type=jpg　というページに誘導する
ブラウザは素直にアクセスしなおす。（HTML内部の画像のことなのでユーザーの見た目にはわからない）
getimgfromdb_and_makefile.php というわざとらしい名前のスクリプトは、その名の通りDBから画像データを取得しbase64デコードして通常の画像データに戻し、typeを判別してContent-Type: image/jpgヘッダを出して画像データをそのままブラウザに返しつつ、裏では imgdir/dbimage_123456.jpg というパスで画像データをファイルに保存する
二人目以降の訪問者が同じページを訪れるときには、すでに画像ファイルが生成、保存されており、http://ec.example.com/imgdir/dbimage_123456.jpg にアクセスするだけで素直にその画像ファイルを返すので、phpスクリプトが稼動することも無く当然DBアクセスも発生しない。

mod_rewriteというと最近ではSEO対策のためのURLの簡素化の目的で使われるケースが多いが、こんな風なキャッシュもどきにも使える。

これでDBの負荷問題はクリアできる。キャッシュとして残るファイルの後始末だが、ファイルの更新日時を見て、一定期間以上古いファイルを消す、といった大雑把なバッチ処理をcron実行するだけでもいいだろう。それこそ1行コマンドでもできる。
/usr/bin/find /hogehoge/imgdir -type f -mtime +30 | /usr/bin/xargs -r /bin/rm
とか。（上の場合は更新日が30日以上前のファイルを消す）
ファイルを消してしまっても、もし必要ならまた自動的に作成されるのだからあまり慎重に考える必要も無い。

もしもバックヤード画面（商品管理とか）がエンドユーザー用画面とは違うWeb/APサーバー上で稼動していても、同様のキャッシュもどき手法を使えばいい。エンドユーザー向け画面から見るのと同じ画像ファイルを指向させるように気を使う必要が無いのがこの方法のいいところである。「web/apサーバが複数あるからNFS張るとかrsyncで配るとか、、、」といったことも考えなくて済む。「どの機能もあるひとつのDB層を見に行く」というごくごく基本をおさえるだけで済むようになる。

なお念のため書いておくが、Webサイトのロゴ画像とかデザイン調整用の1x1ドットの透過gifとかまでDBに格納する必要はまったくない。そういうのはソースコードと同様にAP層に所属されるものと思っていいはず。ここで言っている画像とはあくまでも「データ」の分類に属するべき＝DB層に格納されるべき＝画像情報を意味している。

その他細かいTIPS

わかりやすくするために上の話では省いたことその１。画像の命名方法。

例えばECサイトの画像だとすると、商品番号をそのままファイル名に当てようと考えるのが普通である。 productimg_0000123.jpg とか。がしかし、商品ひとつにひとつの画像とは限らない。だから productimg_0000123-001、productimg_0000123-002 というふうに枝番をつけたりする。また複数の異なる商品のページで、共通の画像が使われるというケースもあるだろう。そういうのだと商品番号に紐付けるわけにもいかず、結局は命名規則は有名無実となり、画像保存ディレクトリの中はシッチャカメッチャカになってゆく。まったく同じ中身の画像が異なる名前で沢山存在してもうどれがどれやら、みたいな状況もめずらしくない。

だったらもう商品データと画像データの直接的な紐付けはあきらめたほうがいいだろう。そこで、画像データのハッシュ値（MD5やSHA1）を画像の名称に割り当てるという規則にする。「31ec79b6ad821e7c5568170151e65d97」とか。画像を格納するテーブルの構造はこんな感じ。(PostgreSQLを想定)

CREATE TABLE IMGDATA_TBL (
IMGID VARCHAR(32) , -- 画像データのmd5値を想定しているので32バイト
IMGTYPE VARCHAR(3) ,-- 画像の種類、img,gif,pngなど。ファイル化してキャッシュ保存するときの拡張子に使う
IMGDATA TEXT, -- 画像データをbase64エンコードで文字列化した値を入れる
PRIMARY KEY (IMGID) -- プライマリキー
);
（その他、必要に応じて画像の縦横サイズや容量のカラムを用意してもいいかも）

こんな風にしておくと、まったく同じ画像データが異なる名前で複数あるといった状況は避けられる。あるいは同じファイル名だが中の画像は異なるファイルが、あっちのディレクトリとこっちのディレクトリで別々にある、といったややこしい状況も避けられる。

一方で商品データと画像データの紐付けはそれようのテーブルを用意して間接的にヒモづければいい。

CREATE TABLE PRODUCT_IMG_REL_TBL (
PRODUCTID VARCHAR(16), -- 商品番号
IMGID VARCHAR(32), -- 画像番号
PRIMARY KEY (PRODUCTID, IMGID) -- 複合プライマリキー
);

これで、ひとつの商品ページに多数の画像を使用していても、逆にひとつの画像が複数の商品ページで使われていても、対応できる。（必要に応じて表示順位付けのカラムとかも。）

わかりやすくするために上の話では省いたことその２。画像が大量な場合には格納するディレクトリを多階層に分ける。

上の例で行くと、キャッシュとして生成されるファイルは「プレフィクス＋画像自体のハッシュ値＋拡張子」といった命名規則が考えられる。たとえば「dbimage_31ec79b6ad821e7c5568170151e65d97.jpg」。

ところが、調子に乗ってひとつのディレクトリ上に多数のファイルを作成すると、ファイルシステム自体の性能が劣化することがある。筆者の経験上、Linux等で広く使用されているext3ファイルシステムではひとつのディレクトリ内のファイル数が3000とか5000とかを超えるあたりで、読み書きに著しく時間がかかるようになる。lsコマンド叩くだけでも結果出力までしばらく待つような状況はちょっと寒い。

もちろんext3じゃない今風のファイルシステム＝xfsとかReiserfsとか＝ならこうした心配は少ないが、あっちのストレージはext3でこっちのストレージはxfs、とか分けて管理するのも意外と面倒である。

手軽な策は、ディレクトリを分けること。たとえば
dbimage_31ec79b6ad821e7c5568170151e65d97.jpg
というファイルは
imgdir/3/1/dbimage_31ec79b6ad821e7c5568170151e65d97.jpg
というディレクトリに格納する。ハッシュ値はたいてい16進数つまり0-9とa-fの16種類で表現されるため、その16種でディレクトリを掘り、それを2階層にすると16x16=256個のディレクトリに分けることができる。

たとえば10万アイテムの商品があるECサイトで1アイテムあたり画像が3つあるとすると、 10万ｘ３÷256≒1171であり、ディレクトリひとつあたりのファイル数を2000以下くらいには抑えることができる。もっとファイル数が増えそうだということであれば3階層、4階層に増やせば余裕だろう。

ここまでの２つの話をまとめると、こういうことになる。

商品一覧画面や商品詳細画面のページ内部に埋め込まれた、商品画像を表示するタグ：

<img src="imgdir/3/1/dbimage_31ec79b6ad821e7c5568170151e65d97.jpg" />

Apacheのmod_rewriteの設定：

RewriteCond %{REQUEST_FILENAME} ^dbimage_[0-9a-z]{32}+\.(jpg|gif|png)$
RewriteCond %{REQUEST_FILENAME} !-f
RewriteRule ^.*/dbimage_([0-9a-z]{32}+)\.(jpg|gif|png)$ /getimgfromdb_and_makefile.php?id=$1&type=$2 [R]

とりあえずいまわかっている制限事項

実はOracle9iには、「ひとつのSQL文の長さは64Kbyteまで」という制限がある。ということは、「insert into hogehoge_tbl values (id, '画像データをbase64エンコードしたもの．．．．．．')」というSQL文において、画像自体が64Kbyteを超えていたら間違いなくこの制限にひっかかる。64Mbyteならともかく64kでは小さすぎる。つまり画像データをエンコードして文字列としてDBに格納してしまおう作戦はDB層がOracle9iの場合は事実上不可能である。ちなみにOracle10gではこの制限は無くなったらしい。

PostgreSQLではもともとこうした制限は無い（もしあったら巨大な文字列を入れることができるというtext型カラムの存在意義がない）。MySQLだと、SQL文の制限というよりは通信上のパケットサイズかなにかだったか忘れたがそんな制限があったような気がする。

また、DB層での制限だけでなく、AP層とDB層とが通信する領域＝ODBC/JDBCドライバなど＝でのなんらかのデータサイズ制限にひっかかる可能性も考えられる。いずれにせよ自分が使おうとしているハード/ソフト構成のなかで「巨大なSQL文がちゃんとやりとりされるか？」くらいは軽くテストしておいたほうがいいだろう。

長々と書いたがとりあえずここまで。こういうのも一種の「富豪プログラミング」なのかな。

Web屋のネタ帳