論理削除があまり好きではないのですが、これからも使い続けるんだろうなと思いました。

この記事はスターフェスティバル Advent Calendar 2023 14日目の記事です。一本締めが大好きなエンジニアのこんぺいです。こんにちは！

論理削除とは

ChatGPT
論理削除はデータベースで、物理的な削除ではなく論理的な操作によりデータを非表示にする手法です。
通常は「削除フラグ」を使用し、データが削除されたことを示すフラグをセットして非表示にします。
これにより、データの復元が可能で、誤削除やセキュリティのリスクを軽減できます。

今までの経験上カラム名にdeleteとかdeletedとかdisable_flagとかactiveとかが多いイメージです。
過去に触れたアプリケーションでstatus = 0を論理削除として使っているものがあって、それは推測難しいなぁと思った記憶あります。

昔のアプリケーションを触ったり昔を思い出したりすると、一つ一つのアプリケーションが工夫を凝らしてオリジナリティ持っていたなぁと思います。昔と言っても私だと長くて20年くらい前なので、想像もつかないようなユニークなものもあるのかも知れないな、とロマンを感じてみたりもします。カラム名ってユースケースと人智ですよね。

論理削除の逆は物理削除です。物理削除は物理で消しちゃいます。
例えば会員情報を保存していると仮定して退会したから消しますねって場合だとこの違いなわけです。

-- 論理削除例
UPDATE user_info SET deleted = '2023-12-14 12:00:00' WHERE user_id = 100 AND deleted IS NULL;

-- 物理削除例
DELETE FROM user_info WHERE user_id = 100 AND;

論理削除は悪なのか？

ChatGPT
論理削除自体が悪いわけではありません。論理削除は、データの復元が可能であり、誤削除やセキュリティのリスクを軽減できる利点があります。また、法的な観点やデータの保全が求められる場合にも有用です。

そりゃそうなんです。というかなんでもそうなんですけど銀の矢はない訳でとりあえずつけとけ！とか絶対つけるな！というのはやはり乱暴ですし、どちらかが明らかに優れているのであれば早い段階で淘汰されているわけなので、残るには理由があります。
その都度モノによるとしか言えないので、あくまで自分の経験談からあまり好きではない というポエムです。
子供の頃はトマト嫌いだったけど今めちゃくちゃ好きなので、もしかしたら将来すっごく好きになっているかも知れません。

SQL書くのがちょっとめんどい

まあ当たり前なのですがjoin-joinしちゃうような感じだとまあまあ面倒になってきます。
会員情報で各情報が別テーブルに記載されている場合で考えてみると都度書かなきゃいけないのマジ面倒です。
こんな極端な例はあまりないとは思いますが。

SELECT
    user.id,
    user.name,
    user_group.id,
    user_group.name,
FROM users AS user
LEFT JOIN relation_user_groups AS relation ON user.id = relation.user_id
LEFT JOIN user_groups AS user_group ON relation.user_group_id = user_group.id
WHERE user.id = 100 AND user.deleted IS NULL AND relation.deleted IS NULL AND user_groups.deleted IS NULL;

全てのテーブルが物理削除ならWHERE句はuser.id = 100だけで良い訳です。
これが地味に影響ありまして。これが必ずあれば良いのですがない可能性もあるわけです。なので毎回構造を調べないいといけない(or覚えないといけない)。全部無ければ見る必要もないのに。

ユニークキー制約に悩まされる

先ほどのSQLでリレーションするテーブルが例えばこのような構造になっている場合で試すと

CREATE TABLE `relation_user_groups` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `user_group_id` int(11) DEFAULT NULL,
  `deleted` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
)

user_idとuser_group_idは一意であって欲しいわけです。なのでユニーク制約を貼ります。

CREATE TABLE `relation_user_groups` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `user_group_id` int(11) DEFAULT NULL,
  `deleted` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id_user_group_id` (`user_id`,`user_group_id`)
)

これで必ず1対1のデータできたぞやったーー！って思った所でデータを手動で入れる時間違えちゃって論理削除したけど実は合ってた！入れ直そう！としてもユニークキー制約により入りません。
こんなエッジケースならちゃちゃっと物理削除しとけよ、ですが複雑なデータ構造とか重要なデータで履歴は残しておきたい。とかあるかもしれませんが外部キー（Foreign Key)制約とかが入っている場合、物理削除もできなくなる場合があります。そんな時このユニークキー制約にできる事はこのユニークキー制約を外して論理削除列をつけて付け直す事です。

CREATE TABLE `relation_user_groups` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `user_group_id` int(11) DEFAULT NULL,
  `deleted` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id_user_group_id` (`user_id`,`user_group_id`, `deleted`)
)

そしてこのテーブルがもし1億行あった場合一体どれだけ時間がかかるのか。その間アクセス受け付けられないけどどうするのか。大変厄介です。
この場合ユニークキーに入れたくないので論理削除できる余地を残さずレコードをばっさり消すのがいいですよね。履歴残すのはログなり履歴テーブルを作成しそちらに移行させるのが良いと思います。

あとから消すはまずできない

この論理削除の厄介な事は他にもあり、じゃあ論理削除フラグを辞めよう！が出来ない事です。
このカラムの特性上、全てのSQLに書かれていて、消してしまえばSQLがエラーになってしまいます。
1アプリケーション内でしか使用されていないのが保証されているのであれば一括置換とかで良いのですが、データベースは複数のアプリケーションから呼ばれる事が多い、というよりアプリケーションが複数あるような状況だと、安全に削除するには今のテーブルを残しつつテーブルをもう一つ作ってしまうしかないわけです。

これをさらにusersという名前に戻すにはnew_usersを同じ手順でusersを作らないといけません

これはよっぽどじゃないと旨みが少ないです。となると以下のように話がされるわけです。

• これするくらいなら該当箇所を修正してえいや！と変えましょう。
• でも最悪動かなくなりますよね？
• なりますねぇ。安全にやるには結構時間もパワーもかかりますね
• それする意味あります？やらなくてもいいんじゃないっすか？
• そうっすね。。。論理削除フラグを辞めるのを辞めましょう

こうやって引き続き論理削除を使い続ける日々に変化はありません。

終わりに

わかりやすいので論理削除の話にしましたが、
「データベースの構造はそうそう変えられないんだからきちんと設計しろよ！」
という当たり前だけど意識する大事さを持って日々SQLを書いていきたいと思いました。

ここまで読んでくれてありがとうございました