fc2ブログ

makopi23のブログ

makopi23が日々の生活で感じたことを気ままに綴るブログです。

SQLアンチパターン読書会 ：番外編「論理削除の四方山話」に参加しました

2015/4/27(月) SQLアンチパターン読書会 ：番外編「論理削除の四方山話」に参加してきました。

DoorKeeper
https://sqlap.doorkeeper.jp/events/23719

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

SQLアンチパターンSQLアンチパターン
(2013/01/26)
Bill Karwin

商品詳細を見る


場所は前回と同じく、品川シーサイドにある株式会社マーベラスさんです。
参加者は14名かな。

前回の読書会で全25章を終えたので、今回は特別編のような位置づけなのです。
今回のテーマは「論理削除」！

そして今回はなんと @t_wada さんに加え、そのお父さんであり書籍の監訳者でもある和田省二氏も参加してくださいました！
以前、DevLoveでも和田さん親子によるSQLアンチパターンのイベントがありましたね。
そんときは私も参加してブログにも書きました。

makopi23のブログ： 「SQLアンチパターン・レトロスペクティブ　- データベース危篤患者の救出 -」に参加しました

もう2年以上前になります。私がこの書籍を買ったのは、このDevLoveの講演を聴講したのがキッカケでした。

あと、和田さん親子＆データベースと言えば、このイベント動画！

この日の読書会でも紹介されてました。

あと、ちょうど１ヶ月ほど前、論理削除ネタがホットになった時期がありましたね。
キッカケは確か、Qiitaの以下コンテンツだったかと思います。


このネタでTLが賑わったのは記憶に新しいところです。
このあたりは一通り目を通してから参加しました。

■ 発表
主催の @natsu_nanana さんによる「論理削除」のアンチパターン紹介です。
「論理削除」は書籍に無いため、みんなの前提と認識を合わせるためにもこの紹介はとても助かりました。
（スライドが公開されたらココに貼る！）

■ LT　「オレオレO/Rマッパーで論理削除を実装した時のこと」
Seasarのコミッターでもある @setoazusa さんによるLTです。
論理削除フラグを用意すると、テーブルのJOIN時に "フラグON/OFFの２パターン×JOIN２テーブル＝４パターン" を考慮しなければいけなくなる話がありました。
更に楽観排他カラムや自動採番カラムが絡むと、組み合わせが状態爆発するとのこと。
あと、「あるある」なのが、SQLを手書きさせた時に削除フラグをWhere句に指定し忘れる、というのがありました。
（スライドが公開されたらココに貼る！）

■ ディスカッション
今回もディスカッションしたいネタをみんなで付箋に書き出しました。
20150427_sqlap2.jpg

■ 論理削除とキーレスエントリー
  • 論理削除を用いると、一意制約や参照制約を意味のないものにしてしまう。
  • なぜなら、親エンティティが論理削除されていても外部キーとしては存在しているので登録できてしまうから。
  • そうならないためにはアプリケーション側でチェックする必要がある。
  • 論理削除を採用した時点で、DBの参照制約により整合性のあるデータしか格納されないようにする、ということはできなくなる。

■ 物理削除を行わない場面とは
  • Webアプリとかでは、パフォーマンスの問題を考慮して物理削除を行わない場合がある。
  • 秒間１億のトランザクションを捌くのに、物理削除は難しい。


■ 赤黒処理

■ そもそもDBからデータを削除しないといけない時ってどんな時？
  • 不要となった個人情報を、セキュリティの観点で削除する時とかでは。
  • でも、それを運用要件として設計するのがエンジニアの仕事だよね。安易に論理/物理削除には繋げられない。


■ 「CRUD」の「U」と「D」いらなくね？

■ 和田省二氏直伝： 「CRUD」の「U」と「D」を使わないデータ管理
  • 削除や上書き更新は行うべきではない。なぜなら、削除や上書き更新は参照先を変えてしまうから。
  • ではどうするかと言うと、例えば「製品名」が変更になる場合は、DBの既存のレコードを「削除」したり「更新」したりするのではなく、新たな「製品名」を持つレコードをテーブルに「追加」することで対応すべき。
  • 更に、レコードに「日付」のカラムを持たせ、日付の情報で過去と現在の製品名を区別させる。
  • ただ、検索が大変になってしまうが、そこは上手い方法がある。
20150427_sqlap1.jpg
  • 上記で、「製品名」が「key1」カラムに該当するとする。
  • まず、図の「C1」カラムのように「更新次数」を表すカラムを設けなさい。
    • 「更新次数」カラムが0のデータが最新で、以降、1, 2, と増えていくたびに過去へ遡って古いデータを表すようにする。
    • ポイントは、Latestフラグを設けたりMax関数を使って最新レコードを判別するのではなく、「更新次数」カラムを設けて最新を0と表現する点。
    • 「製品名」が変更になった場合、そのレコードを新規追加し、更新次数を0とする。他のレコードは更新次数をすべて +1 してズラす。
    • レコード自体を「U」や「D」するのではなく、更新次数のみ「U」することで対応するのがポイント。
  • 主キーは代理キー（サロゲートキー）としてIDカラムを設け、key1カラムとC1カラムは代替キーの扱いとなる。
  • レコードには「開始日付」と「終了日付」を持たせる。
    • あるレコードの「終了日付」と、次に新しいレコードの「開始日付」は、一致させること。
    • 日付に連続性を持たせ、Select文のWhere句で "where 開始日付 <= Target and Target < 終了日付"のように指定する。
    • 下限を ≦ にして上限を < にする点がポイント。これによりレコードの連続性を保つ。betweenは使えない。
    • あるレコードの開始日付と終了日付を同じ値にすることで、そのレコードを論理削除したことと同一になる。
  • レコードが追加になるたびに更新次数カラムの１ズラしが発生するため、レコード追加の頻度がそれほど多くない場合ことが前提のアーキテクチャである点に注意すること。
  • あと、このレベルの設計は論理設計ではなく物理設計になる点を考慮すること。


■ 論理削除したデータって、どうやって削除前の状態に戻すの？
  • 論理削除フラグを1から0に戻すだけ、という簡単な話ではない。
  • お客さんから、「そのデータだけ削除前に戻してよ」と言われても、削除時のトランザクションを考えながら戻すのって難しい。
  • そもそも、そのレコード（リソース）の開始時期と終了時期が分かってないと、削除や復元は管理できない。
  • そうういう意味でも、レコードに「開始時期」と「終了時期」のカラムを持たせる。
  • そのお客さんが求めていたものって、Windowsでいう「ごみ箱」だよね。
  • 本当にそれが必要なら、モデル上にゴミ箱が表現されているべき。


■ 操作ミスによる誤削除の防止
  • 操作ミスで誤削除してしまっても戻せるように、いったんは論理削除し、定期バッチで一括で物理削除するという設計があった。
  • いや、それは論理削除フラグで対処するべきではないはず。
  • 操作ミスで誤削除してしまっても戻せる必要があるなら、それを業務要件として論理設計に反映させないといけない。


■ 「論理削除」という用語

■ 論理削除の回避
  • 論理削除が許される場合というのは、無いのでは・・・。
  • テーブルに「～フラグ」というカラムはほとんど必要ない。ほとんどの場合は、設計や状態遷移でカバーできるはず。
  • フラグ系のカラムを導入した瞬間、テーブルを全件舐めてフラグチェックする必要が生じる。
  • アーカイブ系のテーブルや履歴テーブルを別途用意したりすることで、無用な論理削除を回避できることも多い。


■ 「削除」はユビキタス言語になり得るか？
  • お客さんは「削除」という用語を使うけど、それって本当に「削除」なのかをきちんとヒアリングすることが重要。


■ 終了後の飲み会
@t_wada さんは残念ながら不参加でしたが、和田省二さん初参加！


@t_wada 氏がRuby系のイベントで泥酔した時の話など、過去の暗黒史を和田省二さんにみんなで吹き込むなど。
みんなヒドいｗ



和田省二さん、ほんと最後まで紳士でした。

★感想：
論理削除って今までそれほど意識せず、まぁそんなもんかな的な感じで使ってたんですが、この日の議論を通じて、その闇を垣間見ることが出来ました。
思ったより皆さん、論理削除は使うべきではない、という主張を持っておられるようでしたね。
いろいろ勉強になりました。

あと、@t_wada さんと和田パパさんの掛け合いも面白かった。
やはり @t_wada さんもお父さん意識するんですね。いつもと違う振る舞いも見られたりｗ

論理削除、新しい章としていつの日か書籍にも入るといいですねー

みなさん、ありがとーございました。
関連記事

コメント

コメントの投稿


管理者にだけ表示を許可する

トラックバック

トラックバック URL
http://makopi23.blog.fc2.com/tb.php/176-7ed41664
この記事にトラックバックする(FC2ブログユーザー)

-->