SQLアンチパターン読書会 「ファントムファイル」に参加しました

2013/11/7(木) SQLアンチパターン読書会 「ファントムファイル」に参加してきました。

DoorKeeper
http://sqlap.doorkeeper.jp/events/6957

以下の書籍をターゲットとした読書会なのです。

SQLアンチパターン (2013/01/26) Bill Karwin 商品詳細を見る

場所はいつもの湯島、株式会社アルティネットさんです。
いつも会場提供ありがとうございます。

参加者は9人かな。
最初はおなじみの顔ぶれ8人で始まりましたが、1時間ほど遅れて1名、新規の参加がありました。
新しい風が入るのは良いですね～

今回は２６のアンチパターンの中で最も意見が分かれると言われる「ファントムファイル」がターゲットです。

---

■アジェンダ
今回は @akuraru さんがアジェンダ資料を作成して説明してくださいました。感謝～

ファントムファイル from Akura Pi

LOBロケータが興味深いですね。これについては後述。

---

■ディスカッション
いつもと同様、ディスカッションしたいネタを付箋に書き出しました。


以下、当日の個人メモ。
---

■LOBロケータ
・外部のファイルと関連付けられたポインタのこと。
・OracleだとBFILE型が該当するらしい。
　→　BFILE型とは、DB外に保存される大きなバイナリ・ファイルへのロケータを格納するデータ型のことらしい。

・この型を使うと、DBにはファイルパスしか格納されていないが、外部のファイルと関連付けられる。
　→　DBに格納されているのはファイルパスの文字列だけど、ファイルと関連づいていることをOracleが知っている。

・ちょとOracleのマニュアルを調べてみた↓
　①BLOB、CLOBおよびNCLOBは、データベース表領域に永続的に格納され、これらのデータ型に対する操作はすべて、トランザクション制御の下で実行されます。
　②BFILEデータは、トランザクション制御下にはなく、データベース・バックアップでは保存されません。
　→ BFILEはトランザクション制御下に無いらしい！残念。。。

　出典： http://docs.oracle.com/cd/E49329_01/java.121/b71308/oralob.htm


■トランザクション分離レベル
・ファイルを外部に格納すると、ファイルの変更がコミット前に他の人に見えちゃう。
　→　（これは多分、ファントム・リード(Phantom Read) の一種になるのかな。。。）
・トランザクション分離レベル： wikipedia
・トランザクション分離レベルはDBMSで設定できる。
　→　実際のプロジェクトでは、トランザクション分離レベルの設定をあまりやってないことが多い。
　　　（DBMSのデフォルトのまま、ということが多い）


■ファイルパスをDBに格納する弊害
・ファイルパスをDBに格納すると、開発環境と本番環境でディレクトリ構成が異なっている場合にバグやエラーの温床となりやすい。


■BLOBの利点
・マルチテナントアーキテクチャと相性がいい。
　→　同じ環境上に複数のテナントが入る場合、DBにバイナリを突っ込むと、他のテナントからは見えなくできる。
　→　逆に、外部にバイナリを置くと、他から見えてしまう。
・ただ、今ならマルチテナントとして同一環境に乗せるより、仮想環境をそもそも分けるべき。
・クラウド環境でファイルシステムが自由に使えない場合は、BLOBを使うのが有効。
・バイナリの履歴を管理しなければならない場合、BLOBでDBに突っ込むのはアリ。


■BLOBの欠点
・BLOBを使うと、BLOBの数だけ、HTTPリクエストのコネクションが多くなるらしい。
・BLOBにバイナリを格納すると、毎回DBにバイナリを取りに行かないといけないので、負荷分散できない。
・画像だと、並列アクセスできるようにディスクに置いておけば性能が上がる。
・BLOBはDBから取り出すまでサイズが不明なので、DBエンジンの最適化が効かない。
・HTTP通信では、If-Modified-Sinceという設定をリクエストヘッダに付けることで、バイナリをキャッシュしてくれる。
　→DBにバイナリを入れるとこの仕組みが使えないので、自分でキャッシュの仕組みを作り込まないといけない。
・BLOBに画像を格納すると、気軽に中身を確認できない。アプリ通さないと画像を見れなかったりとか。


■画像ファイルのBLOB格納
・ファイルシステムに画像を格納しようとすると、日本語不可、などの制約が掛かることがある。
・そのため、DBに画像ファイル名を格納するためのカラムを用意して、その名称でダウンロードさせるようにすることが多い。
・ファイル名、Content-Type、ファイルサイズ、の3種類は、画像と一緒にDBに格納することが多い。


■日本語ファイル名の扱い
・ブラウザによって、ファイル名の文字コードの扱いが違う。
・ファイル名はOSとサーバの組み合わせの関係で、そのまま使えないことが多い。
・日本語ファイル名でアップロードされてきたものをUTF-8で保存して、エンドユーザに返すときにUTF-8でエンコードして返すような工夫が必要。
・HTTPヘッダのContent-Dispositionフィールドに文字コードを指定することで、ファイル名の文字化けを回避したりする工夫が必要なことがあるらしい。
・日本語ファイル名の扱いは面倒くさいので、ダウンロードの際にユーザにファイル名を入れさせて、自己責任な設計にしたことがあった。
・空白込みのファイル名とかも在り得るので、扱いは面倒。
・ブラウザがデータを表示させるときのHTTPヘッダを見ると勉強になる。


■11.2.5節　アクセス権限
・ファイルシステムへのアクセス権限は付与しにくいため、誰でもアクセスできてしまう。
　→　バイナリファイルを格納すると、第三者に見えてしまう。
・SQLアクセス権限はGLANTを使ってDB接続ユーザごとに設定でき、アクセス制限をDBレベルで制御できる。
　→　BLOBとしてバイナリを格納すれば、必要なユーザのみ参照を制限できる。
・コネクションユーザによって権限を変える、という制御は、Webアプリではあまりやらない。
・SQLインジェクションでDROP TABLEできてしまうのは、サーバにその権限を与えているから。
　→　CREATEとかDROPとかは業務でやらないはずだから、REVOKEで権限を削除しておくべき。
・DBAがいないプロジェクトだと、全権ユーザにしていたりとかがよく起こる。
・ガチガチのとこなら、参照系テーブルを全部Viewにして、物理的に更新できないようにしたりすることがある。


■11.2.節　バックアップ
・DBでバイナリ格納用にスキーマを分けると、バックアップ運用が楽になる。


■バイナリをBLOBに格納するか、DB外部にファイルとして格納するかの判断
・どちらにするかは、パフォーマンスを取るか、整合性を取るかで変わる。
・外部の第三者に見えないようにするのか（BLOB採用）、外部から見えてよいが性能を重視するのか、状況による。
・アンチパターンなのは、「バイナリはファイルシステムに入れるのが正解」と固定概念で考えてしまうこと。
・ファイルシステムに置くとこーゆうことが出来なくなりますよ、ということを知った上でやることが重要。


■バイナリをBLOBに格納する時の工夫点
・システムが稼動開始し保守フェーズに入ると、メンテのためにSELECT * のSQLを発行することが多い。
　→　そのテーブルにBLOBカラムが途中にあると、SELECT * の取得結果が悲惨なことになる。
・そこで、主キーとBLOBカラムだけ持つようなバイナリ格納用テーブルを別で用意する。
　→　そのテーブルへの外部参照キーを設定しておき、バイナリが必要になった時だけJOINするようにする。
　→　こうすると、毎回BLOBを取得する必要がなくなり、参照性や性能が向上する。


■Last Resource Commit Optimization (LRCO)
・トランザクションに参加できない処理を最後に扱いましょう。
　→　例えば、ファイルシステムにあるファイルの削除など、後戻りできない処理は最後の方にずらす。
・JavaのAPサーバの世界はトランザクションが強い。その理由は２フェーズコミットにある。
　→　２フェーズコミットに乗れるなら整合性の面で安全である。
　→　２フェーズコミットの仕組みに乗れない処理は、一番最後にやるようにすべき。
　→　例えばファイルの移動なら、別の場所にファイルを置いておき、一番最後にリネームでパチッと切り替える。
・PHPとかは、この仕組みがないので絶望することになる・・・
・有償のアプリケーションサーバ（WebSphereとかWebLogicとか）がなぜ高価なのかというと、それはトランザクションモニタが高価だから。
・トランザクションモニタが高価なのは、２フェーズコミットが困難だから。
・トランザクションモニタは、エンタープライズ分野で性能よりも整合性を重視する時代に発達した。


■ファイルアップローダを作ろう
・URL： http://rosylilly.hatenablog.com/entry/2013/10/21/190019
・ファイルアップロードを実現するには、バイナリとかストリームとかURLとか、考えないといけないことが多いので、すごく勉強になる。

---

★感想：
ファントムファイルはシンプルなので、議題がそれほど出ないのでは、という意見も当初ありました。
ところがどっこい、とても多くの議論ポイントが出てくる出てくる！
とても有意義なディスカッションでした。

あとディスカッションでも話に出ましたが、このアンチパターンの本質は「バイナリはファイルシステムに入れるのが正解」と固定概念で考えてしまうことかもしれません。
いわゆる思考停止ですね。
BLOBで格納するかDB外部に格納するかは、両者の利点と欠点をきちんと理解した上で選択することが大事！

あとは、Last Resource Commit Optimization (LRCO)という概念も大変勉強になりました。
LOBロケータや２フェーズコミットの仕組みも大変興味深いです。

ファントムファイル、奥深し！

参加者・関係者の皆様、ありがとうございました～