画像データを扱うとき、パス情報だけを MySQL に格納し、画像データ自体はファイルシステムや AWS S3 などに直接置くのが一般的です。なぜなら画像データは巨大で MySQL で扱うのには重いからです。かつてはコンピュータのリソースが貧弱でしたのでこの設計は適切だったと思うのですが、近年コンピュータは圧倒的に高性能になりました。もしかしたら現代のコンピュータなら画像データを MySQL で扱うことも出来るかもしれないと思い試してみました。
なぜ MySQL で画像データを扱いたいかというと、ひとえに ACID 特性のためです。画像データ処理もトランザクションに含めてしまうことができれば、画像データを含めて不整合のないデータを実現できます。迷子の画像データのことを考えなくて済むわけです。バックアップだって、DB ダンプ一発で全部まとめて取れるわけですし。
スキーマ定義とテストデータ
こんな感じのテーブルを用意しました。images がパス名と画像データも格納するテーブルで、images_light が比較用にパス名だけを格納するテーブルです。
root@localhost [test]> CREATE TABLE images (
-> id INTEGER PRIMARY KEY AUTO_INCREMENT,
-> pathname VARCHAR(255),
-> image LONGBLOB
-> );
Query OK, 0 rows affected (0.28 sec)
root@localhost [test]> CREATE TABLE images_light (
-> id INTEGER PRIMARY KEY AUTO_INCREMENT,
-> pathname VARCHAR(255)
-> );
Query OK, 0 rows affected (0.05 sec)
テストに使用する画像は定番でレナさんにご登場願おうと思ったのですが、21世紀になって20年も経過してるのにヌード画像というのもどうかという気もします。それにレナさん自身がもうこの画像は使わないで欲しいと仰ってるそうなので止めておきましょう。代わりに自分で撮った適当な写真で、私のソウルフードであるスパゲッティーのパンチョのナポリタンメガ盛りにしました。ファイルサイズは 3.85MB です。
テスト用のスクリプトはこんな感じで作りました。画像を100枚登録します。
$ cat gen1.pl
use strict;
use warnings;
print "INSERT INTO images (pathname, image) VALUES \n";
my $first = 1;
for(my $i = 0; $i < 100; $i++) {
print ",\n" if(!$first);
print "('pancho.jpg', LOAD_FILE('/var/db/mysql_secure/pancho.jpg'))";
$first = 0;
}
print ";\n";
$ perl gen1.pl > import1.sql
比較用のパス名だけを格納するテスト用スクリプトも用意しました。
$ cat gen2.pl
use strict;
use warnings;
print "INSERT INTO images_light (pathname) VALUES\n";
my $first = 1;
for(my $i = 0; $i < 100; $i++) {
print ",\n" if(!$first);
print "('pancho.jpg')";
$first = 0;
}
print ";\n";
$ perl gen2.pl > import2.sql
実験
それぞれ mysql コマンドで実行します。
$ /usr/bin/time mysql test -u root -pxxxxxxxx < import1.sql
mysql: [Warning] Using a password on the command line interface can be
insecure.
83.38 real 0.02 user 0.00 sys
$ /usr/bin/time mysql test -u root -pxxxxxxxx < import2.sql
mysql: [Warning] Using a password on the command line interface can be
insecure.
0.68 real 0.02 user 0.01 sys
画像1枚あたり1秒以下で格納できてますから、使い物にならないほど遅いわけではなさそうです。とはいえ、パス名だけを格納する場合に比べたら圧倒的に遅いですけれどね。
DB ファイルサイズも調べてみます。
$ sudo ls -lh /var/db/mysql/test/
total 401680
-rw-r----- 1 mysql mysql 112K 5月 26 07:40 images_light.ibd
-rw-r----- 1 mysql mysql 392M 5月 26 07:39 images.ibd
1枚あたり 3.92MB 程度ですから、ストレージ効率もめちゃめちゃ悪いわけではありませんね。
ということで、画像データを MySQL に入れるのは全くあり得ない選択肢でもないなという感想を持ちました。もちろん、システムの他の要件との兼ね合いによって最終的に採用するかどうかは変わってくるのですが。