#ronsakucasual DBの論理削除についてひたすら共有する 論理削除 Casual Talks #1 にいってきたまとめ

@kenchan様がご企画頂いた論理削除 Casual Talks #1に参加してきましたので、そのまとめです。

論理削除というピンポイントな話題なのにこんなに人が集まるのはDBのひとつの魅力ということでしょうか。。。

SQLアンチパターン第26章「とりあえず削除フラグ」

名前

とりあえず削除フラグ

目的

• エンドユーザから見るとデータがないことにしたいけど、実際のデータは消したくない
• 削除したデータを検索したい
• データを消さずにログに残したい
• 誤った操作をなかったことにしたい、直ぐに元に戻したい

アンチパターン

• 例えばis_deletedというカラムがtrueの場合は削除されているとみなす
• メリット
  • update文ならデータが簡単に元に戻せる気がするのでなんとなく安心 -> 俺のupdate文でなんとかなる！！

起こること

• SELECTするときには常にWHERE句が追加で必要になり、コードが削除フラグだらけになる
• 全員テーブル設計に精通してるわけではないので、テーブルによって削除フラグの有無があったりした場合、認識の齟齬を生みやすい
• 例えばrubyでdefault_scopeを用いて、よかれとおもってコードレベルでデフォルトを変えたらバグがたくさん出てくる
• データ不整合と場当たり的クエリの巣窟になる
• 削除フラグの立ったデータが大量に隠れている
• 一律でdeleteフラグがあると、削除しても大丈夫なテーブルであるかのようなイメージを与える。 cf: 論理削除が奪うもの

アンチパターンの見つけ方

• 以下の様な会話からヒントを見つける
• Q この is_deleted 列はどういう目的で必要なのですか？ cf: DELETE_FLAG を付ける前に確認したいこと。
  • A データ上は無い事にしたいけど、実際のデータは消したく ないからです
• Q なぜこのテーブルにも削除フラグが付いているのですか？
  • A プロジェクトのルールで、全てのテーブルに削除フラグを 定義することになっているんです
• Q: この is_deleted2 というカラムは何者ですか?
  • A: ああ、それは管理用フラグです。管理者が非公開設定にしたときにtrueになります。 is_deletedとの組み合わせで表示を制御します

アンチパターンを用いても良い場合：

• 論理削除は物理削除より大概早い
  • UPDATEは行レベルの影響に閉じれる
• 物理削除と天秤にかけた際に、高トラフィックのサイトで論理削除を採用することは大いにあるが、フラグ以外の実現方法で行っている

解決策

• cf 論理削除フラグという名の死亡フラグ
• 解決策の糸口の一つはまだ設計が終わってないということにある

解決策1： せめてフラグではなく削除日

• フラグよりは情報が多いが、NULLを使うとインデックスを使えない
• 世の中の論理削除プラグインは大体この方法をとっている
• フラグにしてよいことはあまりない

解決策1.2 : もうちょっとドメイン言葉を使う

• closed_atというカラムに変えて、NOT NULL制約を設け、未来日のマジックナンバーを用いる

解決策2：それはフラグではなく状態である

• Doctrineというフレームワークはver2から論理削除のサポートがなくなった。
  • 論理削除を行いたいときはstate pattern、つまり状態遷移を考えたほうがいい
  • OR mapperとしては正しい
• 記法されたopenな状態や、表示から消したいというときは、非表示や表示期間の終了として扱えば良い
• 状態遷移をサポートするプラグインは結構ある
  • Actual State Machineを用いたモデルの状態遷移を使ってフローの制御を行う
• 多くは状態遷移という解決策で解決できる

解決策3： 履歴テーブルに移す

• 2つのテーブルの間の整合性はBEFORE INSERTやUPDATEトリガーなどで気合で保つ
• やってみると結構難しい
  • 1個のテーブルだけではなく、お互いのテーブルの関係などを考えると結構大変

解決策4：そもそも削除も更新もしない

• T字型ER手法 原則については論理削除が云々についてを参照。
  • テーブルに状態を持たせないで、イベントとエンティティだけにする
  • 何かを公開終了にするというイベント、インスタンスとして表現する
• 例えばJSOX法や内部監査があるような企業システムは情報を消すのはタブーになっている
  • そのため、消す場合はINSERTで打ち消すようにしている
  • 事実の打ち消しをしていって今の状態がどうなっているかを表現する
  • これなら事実は必ずDBにある
  • パフォーマンスはあまりよくないかもしれないが、耐障害性、パフォーマンスなどはまた別のお話
• CRUDのUとDなんていらなかったんや！！
• それ自身をアーキテクチャにしたのがDatomicという物がある
  • emutableなデータストアで追記型で事実を記述していく
  • 遅くなる問題はクラウドの力で解決している

解決策5： オペミスを防ぐ

• 誤った操作をなかったことにしたい、すぐに元に戻したいという問題については、これをアーキテクチャに組み込むと結構大変
• MySQLの遅延レプリケーション
  • わざとn秒遅らせるスレーブを1個作り、本当の突発的なオペミスに関してはそこから復旧するという手がある
  • オペミスを設計ではなくアーキテクチャの観点から解決する

まとめ

• 考えに考えてupdateのソリューションを取るのはいいが、考えないで削除フラグを採用するのは設計が固まってない・意見を汲み取れてない
  • updateをとるにしてもフラグを使うのはない
• webシステムにおけるRDBMSはトランザクショナルなキャッシュとしての側面と永続的データストアとしての両面をもっている
• 企業システムは発生した事実を余さず記録するトランザクショナルな永続的なデータストアとしての側面が強い

FAQ

• 解決策としてのステータスというカラムをもたせるというありがたみがよくわからない
  • statusが嬉しいのは複数の状態を扱えること
  • 状態が削除しかないのならdelted_atのほうがいい
  • 業務を設計していく時にフラグにしてあとで困るときは2つ以上値があった場合。そのときにis_delted2カラムをたてるという解決策に陥ったりする。本当は無効化している、まだ期限がきてない、表示しないなどの要件があったりする
  • 削除フラグで設計をはじめていたものの正体が状態であったと分かる場合はフラグが数種類になったりする
  • エンティティを設計してみたら、データの流れにおいてフローの制御を行っていたとわかることが多いのでステータスカラムをもたせる
  • ステータスカラムをもたせるとある状態の一覧、操作を切り取っておこなうことができるようになる
    • 例えば中止になったなんらかのイベントやその中止の理由をもってくるなどとりやすい – 要件を満たすというところと、良い点をとれる設計を満たすというバランスをとろうとしてくると、フラグではなく、日付、日付ではなくステータスカラムになっていったりする

MySQLで論理削除と正しく付き合う方法

• MySQLの都合にそってやればいいがそうでない場合は害悪になる
• 本当は論理削除してほしくない
  • 余計なカラムは増えてほしくない
  • 参照されないデータは入ってほしくない
  • 設計的に汚い
• 好きなのはDELETEトリガーでアーカイブ用のテーブルに追い出すこと

考える事

• UNIQUE制約がきかない
  • ナチュラルキーがしっかり定義してあるテーブルのほうが少ない
• カラム文データのサイズが大きくなる
• 削除されないレコードがゴミとして貯まる
• Webサービス削除は少ない
  • UPDATEは存在しても削除という行為が多くない
• tpcc-mysqlだと1/47より少ない
• covering index狙いにくい
  • WHERE狙いのキーのなかにANDで詰め込むことになるが、例えばuser_idにインデックスがはっていればそれだけでソートできたものが、フラグのせいでレコードを一旦読みだしてフラグを評価した後にORDER BYするのでファイルソートになって、ORDER BY狙いのキーはだいたい死ぬ

前提

• サロゲートキーがふられていて、他にユニーク制約がない
• 削除のフラグのカラムはboolか、せめてENUM
• 削除フラグのカラムは全てのセカンダリーインデックスの先頭にふる
• MySQL5.5以降でない
• updateで済ませないといけない理由がある
  • 削除されたデータに運営やバッチがアクセスする
  • ユーザートラフィックで削除対象レコードが数万行規模

サロゲートキー

• ナチュラルキーがある場合に論理削除しちゃうとナチュラルキーで制約できない
• 残念ながらナチュラルキーが使われてるケースのほうが少ない

削除フラグの方

• DATETIME型、TIMESTAMP型は変
• スーパー非表示フラグとか表示ステータスにすればいいのでは
  • そうすればそれはレコードの属性は一つなのでリレーショナルモデル的に納得がいく
  • ステータスならENUM(‘NOT_DELTED’, ‘DELETED_BY_USER’,,,

削除フラグのインデックス

• 全てのセカンダリーインデックスの先頭に削除フラグのカラムが存在しなければいけない
• 全てのクエリーはWHERE句の先頭で削除フラグの等価比較をしなければならない

UPDATEとDELETEの違い

• deleteとupdateはどっちにしてもインデックスを使ってレコードを探す
• deleteの場合はすべての行を削除しないといけないのでPrimaryKeyをたどってその行を消す
  • change bufferという非同期にセカンダリインデックスを更新する仕組みがあるが、すべてのセカンダリインデックスからそのレコードに関する情報を削除しないといけない
• updateはデータのページを持ってきてinplaceで書き換えて、他のインデックスにのっかってればそっちはchange bufferにのっけるが、のっかっていなければなにもしない
• クラスタインデックスが再構成されないというのに大体依存しているが、その書き換えがだいたい重い

MySQLが5.5より前

• 5.5以前はインサートバッファ
• change bufferをサポートしたのが5.5以降
• super_hidden_flagを含めたインデックスを作らないとDELETEより遅い（SELECTが足を引っ張ってる）
  • 参照せずにdeleteやupdateすることはない
• 論理削除はあまり旨味がない

UPDATEで済ませないといけない理由

• 削除されたデータにアクセスする
  • 削除された理由の解析
  • ひとつのコメントを削除すると数万行の削除をすませないといけない場合
  • いつ削除されたかを保持する必要がある
  • 非同期でS3のファイルを消すので、その削除が終わるまで残さないといけない
• ユーザートラフィックで削除対象が数万レコード cf: フレンド・タイムライン処理の原理と実践

まとめ

• サロゲートキーじゃないといいことない
• インデックスがちゃんときってあればシステム的には言うほど変わらない
• 論理削除じゃなくてsuper hidden flagのupdate

FAQ

• DELETEトリガーでの削除テーブルへの追い出しのデメリットは？
  • トラブルシュートが大変
  • トリガーがこけて全てのDELETEがこけるなど

論理削除と、そこでのElasticsearch活用

行ってきた論理削除について

• 削除するデータは、マスターデータではなく、たくさんレコード数があり、削除後はアプリ本体から参照もせず、復活しない。
• 運用上、後から削除データを見れる必要があるケース

削除フラグ

• RDBからレコードを物理削除するのではなく削除の状態を持たせる
  • 普段の参照では削除状態のレコードを隠すクエリやviewを使う
• 利点
  • テーブルにカラムを一つ追加するだけ
  • 復活などが楽だがあまりやらない
  • DELETEよりUPDATEのほうがパフォーマンスがいい
• 欠点
  • テーブル容量が増加しやすい
  • パフォーマンスが悪くなりやすい
  • クエリが非直感的になる。参照の時に条件追加や、削除にUPDATEなど
  • RDBによってはUNIQUEインデックスが使いにくい
  • RDBとして、設計が微妙

削除テーブル

• 削除したレコードを履歴テーブルなどのアーカイブ用のテーブルの避難させる
• 利点
  • 元になるテーブルの容量は増加しにくい
  • 削除テーブルでパーティションをきると古いデータを楽に消せる
• 欠点
  • 外部キー制約が使いにくい

削除ログ

• RDBあから物理削除して削除データを別途ログとしてElasticSearchに記録する
• 削除したデータをJSONでINSERTする

ESでのデータ設計

• 1アクションでRDBの複数のレコードを消す場合はログはまとめて1つ分で入れる
• 実際は検索で使いたいもの以外のフィールドは圧縮して1つにまとめる
• user_idでroutingしてパフォーマンス向上

ESのログの参照

• 専用ライブラリを作って通すことで圧縮を意識せず、RDBと似た感覚でつけるようにしている

ESに格納されるまで

• Fluetndで専用のログファイルを収集して、HadooppにいれてbatchでESにbulk insertしてる
• Hadoopは集約と分析、ESはユーザー単位でのログ検索に使っている

ESでの削除ログ 利点

• RDB側をシンプルに出来る
• DBの容量を気にすることが減る
• でーたのちくせきがしやすい
• 新たなログ追加が楽

ESでの削除ログ 欠点

• 削除後、ESからみられるまでに時間が掛かる
  • 許容できない時はRDBを使ったり、RDB+ESを組み合わせて使う
• 信頼性をより高くしたい時もRDBを使う

まとめ

• 規模が大きめだと削除フラグは辛い
• ESに削除ログを入れると楽
• 状況に応じて削除方法を選択する

FAQ

• ESを使おうとした理由はパフォーマンスの理由？
  • 社内で削除フラグを使っていたが、容量がでかいという問題がおきたので導入した
• ESのデータが壊れた時に壊れてもいい範囲で使っているのか？
  • ESからデータが失われることは考えていなかった
  • 定期的に消してはいるのでそんなにやばいデータでもない
• ESにログを入れるのはDBのストアドプロシジャではなくアプリでやっていると思うが、バッチを作ったとか、ソースコードがわかれるときはどうやってログをもれなく送っているか
  • DBとESは分離して使っているが、DBをトランザクションの中でがっつりロックしてしっかりESにもいれるようにしている

論理削除をしない

• webアプリケーションを作ることが多かったが、論理削除をすることがなかった
• 論理削除と言っても削除していない。
  • 削除していないので削除と呼ぶべきではない
  • 論理的でもsoftでもない

絶版商品に削除フラグを付けて、一覧や検索で表示されないようにしたい

• しかし、過去の購買履歴には表示するし、売上集計の対象になるので削除ではない
  • ほんとうに欲しかったのもはstatus = :絶版
  • 絶版にするイベントを別のカラムにjoinする方法もあるが、絶版であるということをデータで表現したい
  • 表に出したいのは売れるものを出したいというのを素直にコードに書く
• default_scopeは非推奨。
  • もののステータスを考えないといけないところを雑にやってしまっている
  • defaults_scopeは常にくっついてくるので、削除フラグをつかってはじくようなことをしていると、忘れた頃にその脅威がやってくる
  • railsを使うときはdefaults_scopeも使わないようにしよう！

退会した会員はログイン出来ないように論理削除したい

• a: 全部消すが、不安なら必要なところだけアーカイブ
• b: そのまま残してstatus = :ログイン不可にする
• c: 匿名として残す
• d: システムユーザへ付け替えるような処理をたす
• a以外は削除していない
• 論理削除ではなく、やりたいことに沿った設計をする

保管期間の過ぎたデータに削除フラグをたてたい

• ログを取って消す
  • 監査に必要なら他のところに残す
• 古いやつを消したいというニーズは新しいものを表示したいというニーズではないのか
  • 普段見えなくなっていれば十分かもしれない

なぜ論理削除をすべきではないのか

• 大抵は他の実装に落とせる
• 論理削除をしだすと、止め時がわからず全テーブルに削除フラグついたり、つねにJOINしてフラグチェックしたりしないといけない
  • アプリケーションの設計もできなくなる
• データの終わりを設計できていないので、止め時が決められない。
  • どこまでも伝播してアプリケーションコードがひどいことになる
  • 本当は表から見えなくなるデータをちゃんと考えないといけない

論理削除はしない

• データの終わり方を設計するようにする
• 4時熟語でなにか言った気にならない
• cf: DELETE_FLAG を付ける前に確認したいこと。
• そしてdefault_scopeを使わない

FAQ

• 「匿名で残す」をやるとunique制約が維持できくなるのでは
  • そのシステムにおいてユーザーとのデータを同じテーブルにしないようにすればいい -> 正規化
  • ニックネームは一意制約不要で、実はID(PK)だけがあればユーザのアイデンティティになるのでemailは消して良い

少し変わった論理削除 ホスティングサービスにおける論理削除の例

• 論理削除する場合において、データ永続化機構はRDBMSとは限らない
  • 例えばWebサーバではunixユーザを作って、ファイルシステム作って、ファイルにユーザの情報が書いてあったり、RDBMSにデータが格納されていたりなど
• 非RDBMS環境における論理削除
  • usermod -L
  • chmod 000
  • mv hoge hoge.bk
  • rm シンボリックリンク
  • コメントアウト
  • REVOKE
• ホスティングサービスにおける論理削除はRDBMS的論理削除として実装できない。
  • 例1：ユーザーが削除できない！ -> ファイルシステム上の削除とファイルのコメントアウトを実施していて、その際に想定される行がなかったため削除できなかったんです
  • 例2：ユーザが削除される前に新しく状態を追加したいんだけど -> この機能とこの機能を論理削除して、削除フラグとして使っているカラムに新しい値を追加しましょう
• 論理削除と現実的に苦しみを感じないくらいの距離感で生きるのがちょうどいいのでは？
  • 論理削除という単語を出さない
  • 開発者は内部実装を語りすぎるが、非開発者が削除か論理か物理かというのは一切察知しない。ユーザにとっては考える余地すらない
  • 論理削除という言葉本当に的確な表現か？退会ユーザー、無効ユーザ、削除待ユーザなどの用語を開発者と非開発者が合意できるように統一しよう
  • ほんとうに必要なとき以外「論理削除」という単語を避けるようにすれば苦しみがないはず
• 論理削除を避ける
  • 物理削除+ロギングにできない
  • 状態管理ではないか
  • 機能追加、要件の変更に対して開かれているか
  • ビジネス要件として必須の時は頑張る
• 論理削除と言い出すのはデータの持ち方にとらわれ過ぎのでは？
  • 本来注力すべきどうやってフローが動くのかなどのドメインに注力できてないのが原因では？
• ドメイン駆動設計、実践ドメイン駆動設計という本を買うと救われます
• 論理削除という単語はRDBMSだけのものではない
• 論理削除はファイルシステムやUnixユーザーなど様々の形の論理削除が存在しうる
• 永続化機構が複数存在すると論理削除は複雑さの原因になる
  • 複雑な論理削除を生で扱うよりは、的確な状態表現を定義すればハッピーになれるのでは
• 永続化機構が複数存在することで、結果的にドメインへ注力できる

@mogmetの所感

今回は久々のあまり数の多くないDBの勉強会に参加してまいりました。

DBの勉強会もpotatotipsみたいに毎月恒例にtipsを発表するみたいな会があればDB会も賑わうとは思いますが、中々開催されないのはネタをためるのは難しいんですかね…

論理削除だけで様々な視点からの意見を聞くことができてとてもおもしろい勉強会でした。

そして思ったのは、オラクルを用いるといくつかでた論理削除の問題は解決できそうだなと思いました。

• 削除したデータを検索したい
  • フラッシュバック問い合わせでUNDO_RETENTIONの時間の限り、過去のデータが見れます
• データを消さずにログに残したい
  • フラッシュバックアーカイブでアーカイブしておくと、はるか昔のデータの状態見れます
• 誤った操作をなかったことにしたい、直ぐに元に戻したい
  • フラッシュバックトランザクション、フラッシュバックテーブルなどで戻せます

※ただし、お金がある企業に限ります

第2回をもしやるならDBごとの論理削除とは〜みたいな感じでやるとおもしろいかもしれませんね。