2015さらば、愛しき論理削除。MySQLで大福帳型データベースを実現するツール「daifuku」を作ってみた先のエントリ「論理削除はなぜ「筋が悪い」か」で書いたとおり、データベースに対して行われた操作を記録し、必要に応じて参照したり取り消したりしたいという要求は至極妥当なものですが、多くのRDBは、そのために簡単に使える仕組みを提供していません。ないのなら、作ってみようホトトギスというわけで作った。github.com/kazuho/daifuku注1daifukuは、RDBに対して加えられた変更をトランザクション単位でRDB内にJSONとして記録するためのストアドやトリガを生成するコマンドです。TABLEステートメントなど、必要なSQL文をsetup.sqlファイルに出力します。次に出力内容を確認し、mysqlのルートユーザ権限でSQLを実行すると、準備は完了。setup.sqlあとは、トランザクションの先頭でdaifuku_begin()プロシージャを呼び出せば、以降同一のトランザクション内で加えられた変更は、全てdaifuku_logテーブルに単一のJSON形式の配列として記録されます。daifuku_begin()には、任意の文字列を渡し、RDBの変更とあわせて記録することができるので、更新の意図や操作を行った者の名前等を記録することにより、監査や障害分析を柔軟に行うことが可能になります。記録されるトランザクションログのid(daifuku_log.id)は@daifuku_idというセッション変数に保存されるので、アプリケーションではその値をアプリケーション側のテーブルに記録することで、操作ログをUIに表示したり、Undo機能注2を実装したりすることも可能でしょう。実際に使ってみた例が以下になります。トランザクション内で行ったdirect_messageテーブルとnotificationテーブルに対する操作がJSON形式でdaifuku_logテーブルに保存されていることが確認できます注3。sec)大規模なコンシューマ向けウェブサービスでは、この種のトリガは使いづらいかもしれませんが、バックオフィス向けのソフトウェア等では工数削減と品質向上に役立つ可能性があると思います。アプローチの方が、アプリケーション開発に必要な工数、バグ混入の可能性、データベースのロック時間の極小化等の点において優れていると考えられます。過去の任意のタイミングにおけるデータベースの状態を参照する必要がある場合は、範囲型による時間表現を用いたデータベース設計を行うべきでしょうが、要件が操作の記録やUndoである場合には、そのような過剰な正規化は不要であり悪影響のほうが大きいです注4。MySQLには範囲型ないし。名前の由来は大福帳型データベースです。トランザクション単位のrevertではなくdaifuku_logテーブルに記録されたログを逆順に適用していった際に以前のテーブルの状態に戻ることを保証したい場合は、分離レベルをシリアライザブルに設定しておく必要があります。この点において、更新ログを主、現時点での状態を示すテーブルを従とするアプローチに対し劣位であることは、先の記事でも触れたとおりです。UTF-8の範囲外の値を含む可能性のある型のデータについては、base64エンコードが行われ、それを示すために配列としてログに記録されます。daifukuをご参照ください。操作=トランザクションとは元来リレーションに1対1でマッピングされづらいものなので、過去の操作を取り扱うことが主目的の場合には、無理に正規化を行わない方が都合が良いケースが多いと考えられます。mike-neckのブログ」を読んで。データベース設計において、「テーブルの書き換えをするな、immutableなマスタと更新ログによって全てを構成しろ」というこの記事の主張はモデリング論として全く正しい。残念なことに、ディスクやメモリが貴重な資源だった時代の技術であるRDBは、そのようなモデリングに基づいて設計されたデータベースには必ずしも適していない。第一の問題は、RDBに対してなされる様々な「更新」(トランザクション)は不定形(どのテーブルをどのように修正するかはアプリケーション依存)だという点。不定形な「更新」を時系列にそってRDBに記録していくのは、設計と並走性の点において困難あるいは煩雑なコーディングが必要になる(というか、そのような「イベント」による「変化」はREDOログに書き、その更新された「状態」をテーブルに反映していくというのがRDBの「一般的な使われ方」)。第二の問題は、ほとんどのデータベースアクセスは、更新が蓄積された結果である「現在の状態」を問い合わせるものになるが、immutableなマスタと更新ログによって構成されるデータベース設計においては、そのような問い合わせに効率的に応答するのが難しいという点である。従って、現実的なデータベース設計においては、多くのテーブルが「現在の状態」をもつ、immutableなマスタと更新ログから合成可能な「現在の状態を表現するビュー」を実体化したものとして表現されることになる。ふりかえって、論理削除とはなにか。「現在の状態を表現する実体化ビュー」に、過去の状態(かつて存在したデータであることを意味する「削除済」)をフラグとして付与したものである。「現在の状態を表現する」ことを前提にしたビューであるところのテーブルに、過去の状態の一部だけを表現するフラグを追加するのが「筋が悪い」設計だというのは明らかだ。過去の状態を参照すべき要件があるなら、そのテーブルが表現する情報についてはimmutableなマスタと更新ログを用意し、その射影として表現されるビューとして「現在の状態」を実現すべきである。そのようなビューは自動生成できるならしても良いし、手動で更新するなら、ストアドを使うか、あるいはアプリケーションのデータベースアクセス層において手続きをまとめる方法を考えるべきだろう。immutableなマスタ、更新ログと「現時点のビュー」の3テーブルを準備・運用するというのはそれなりにコストがかかるので、筋が悪い「削除フラグ」(より一般化すれば状態フラグ)を使うかどうかはケースバイケースで判断するのもひとつの見識である。まとめ:データベース設計にあたっては、テーブルを「現在の状態」を表現するものとして設計するか、それとも「immutableなマスタと更新ログ、および現時点の状態を表現するビュー」として設計するかという、2つの選択肢がある前者は簡潔で性能が高くなるが、過去の情報を参照できないという問題がある「削除フラグ」というのは、「現在の状態」を表現するテーブルに過去の状態の一部を表現する機能を場当たり的に足したもの(なので筋が悪い)それよりも、過去の情報を参照する要件がある場合(もしくはそのような要件が発生すると想定される場合)は、テーブル単位で「immutableなマスタと更新ログ」という設計を採用しつつ、現在の情報をビューとして表現することを考えた方がよいこういった点を理解・検討した上で、「それでも削除フラグの方が楽だろう」という判断を下しているなら良いと思います。Furuhashiで挙げられていた疑問点について:効率的にSELECTや更新ができるスキーマを作ろうとすると、VIEWやFUNCTIONなど、側に実装するコードが増えてくる。それらのコードは、上記のようにDB側に実装しても良い(するべき)だろうか?それともアプリケーションに実装するべきだろうか?アプリケーション要件によるでしょう。アプリケーション要件に関わらず正しく動作する方法が何かという問に対しては、RDB側でトリガ(あるいはストアド)として実装するのが安全だというのが答えになるでしょう。アプリケーションが操作可能な処理は「immutableなマスタ」と更新ログテーブルへの追記のみとし、両者への操作から「現在の状態を表現するビュー」を生成すべきであって、「現在の状態を表現するビュー」をアプリケーションが操作し、その変更内容をトリガでログに出力するという方式は避けるべきという話になるかと思います。しないと分離レベルや主キーの発番処理に起因する込み入った条件が色々...続きはお近くのDBAにご相談ください。アプリケーションを拡張した結果として削除フラグの存在が冗長になったのであれば、そのフラグの更新をストアドあるいはトリガで行われるように変更し、書き込み権限をドロップすればいいでしょう。こんな感じかと思います。ソフトウェアサービス企業における技術責任者の最も重要な仕事のひとつが、エンジニアリングの効率化です。そのためには、サービスの初期開発コストだけでなく、運用コストを織り込んだ上で正しい技術的判断を行っていく必要があります。「技術的負債」という言葉は、この運用コスト最適化の重要性を指摘する上で、とてもキャッチーなフレーズだと考えられます。「技術的負債」を産まないように、あるいは負債を早めに返していこうとすると、開発工数が大きくなってしまうという問題もあります。初期開発コストと運用コストのバランス注1を、どのようにとっていけば良いのでしょう?同等の機能を提供する「ソフトA」と「ソフトB」を考えてみます。ソフトAは、初期開発工数が6だが、2年目以降の維持工数が毎年4かかるとします注2。ソフトBは、初期開発工数が10で、維持工数が毎年1かかるとしましょう。ソフトAはPHPファイル1枚に必要な処理をひたすら羅列したクソコード、ソフトBはMVCを念頭にレイヤ分割された、きれいなコードなんてイメージすると、こんな工数感覚になってもおかしくないかと思います。ソフトAとソフトB、どちらを開発すべきかという問いに対する答えが、そのソフトを使い続ける(改善し続ける)期間によって変わることは、表1を見ずとも明らかだと思います。その期間を合理的に見積もる手法が必要になります。14ここで登場するのが「割引率」という概念です。割引率は一般に、将来の発生する可能性のある利益を現在の価値に変換して評価するために用いられる概念ですが、同様に、将来発生する可能性のある費用を評価する目的でも使うことができます注3。新規サービスの場合を考えると、2年目も継続してサービスを改善しつづける可能性は決して高くないと思います(仮に改善を続ける可能性が50%だとすると、割引率(1÷改善継続可能性−1)は100%になります)注4。n年後に改善を続けている可能性が0.5nであるなら、現在価値に修正した累計コストは以下のような表になります。10.9375表からも明らかなように、ソフトAの累計工数がソフトBを上回ることはありません(厳密に言うと、ソフトAの割引後の累計工数が10に収束するのに対し、ソフトBの工数は11に収束する)。この結果はつまり、仮に翌年も継続してサービスを改善しつづける可能性が50%なら、ソフトBのアプローチよりも、ソフトAのアプローチのほうがコスト効率的に優れているということを示しています。逆に、上場企業の事業の基盤となっているようなソフトウェアについては、割引率が10%を切ることがあってもおかしくないはずですが、仮にn年後に改善を続けている可能性が0.9n(割引率11%)であるなら、現在価値に修正した累計コストは下表のようになり、今度は逆に、ソフトBのアプローチの方が優れているということになります。13.0951以上のように、技術選択における正解は割引率の高低によって大きく左右されます。割引率は事業の形態のみならず、ソフトウェアの種類によっても大きく異なります。アプリケーションの順で割引率が高くなっていく印象があります。適切な値がいくらか、ということは俄には答えにくい問題かもしれませんが、「割引率」という概念自体は経営層にとっては常識レベルのものなので、経営層と事業継続にかかる感覚値を普段から共有し、技術判断にフィードバックしていくということが、技術責任者に求められる役割なのかな、と思う次第です。参考リンク:とあるスタートアップを抜け、CTOを辞めた話。企業の情報システム投資では、初期コストに保守等のコストを加えた「総所有コスト(TCO)」の極小化を目標とするのが有効なアプローチだと考えられています。ソフトウェアが陳腐化する外的要因は周辺技術の進歩です。その速度は一定であると置くのが妥当ですから、ソフトウェアの保守コストは毎年一定であると考えるのが正しいことになります。「ソフトウェアの保守が時間とともに難しくなる」と感じられるとしても、それは過去の保守投資が過小であった結果だと解釈すれば良い話です。モデルを無駄に複雑化する理由はありません。事前に使用年数を見積もることが困難なシステムがあるなら、通常のTCOではなく、保守を続ける年数を確率分布として修正したTCOを用いてコストを予測すれば良いということです。割引率は毎年一定になるとは限らないでしょう。2年間で資金を使い切る前提でサービスを提供している場合には、2年間は割引率が低く、3年目にがくっと上がる形(失敗するサービスが過半であるという前提を置くなら、それこそ100%を超える値)になると思います。(5)さらば、愛しき論理削除。