Zenn
🗃️

SQL で理解する DB の物理削除と論理削除

2021/03/31に公開
SQL
DB
tech

はじめに

株式会社じげん の Web エンジニア、橋濱です！

DB 設計のレコード削除方式に 物理削除 ・ 論理削除 というのがあります。
これらそれぞれの方式を SQL 文とあわせて整理していきたいと思います。

解説は、店舗テーブル shops のレコードを CRUD するケースを想定して進めます。

物理削除って？

まずは物理削除から。
これはまごうことなき削除です。

レコードを DB 上から完全に消し去ってしまう 、基本復元できないやつがそれです。

SQL でいうと DELETE 文で実行する削除に当たります。
上述した shops テーブルの例を使って、店舗の非公開データ（ is_published カラムが FALSE なレコード）を物理削除してみると、こうなります。

-- 物理削除
DELETE
FROM
  shops
WHERE
  is_published IS FALSE
;

じゃあ、論理削除は？

そして、本題の論理削除です。
論理削除は、「削除」といいますが 実際にはレコードを削除しません 。

deleted_at などのカラム（削除フラグ）をテーブルに用意して、その値によって 「これは削除されてるレコードだ」と単に見なす 、概念的な削除です。

削除フラグのカラム値を変えることでレコードの取り扱いを決めるため、SQL でいうなれば UPDATE 文による削除です。
先ほどと同様、 is_published カラムが FALSE な shop レコードを削除してみましょう。

-- 論理削除
UPDATE
  shops
SET
  deleted_at = NOW() -- 実行時の日付を入れる
WHERE
  is_published IS FALSE
;

削除フラグのカラム名は is_deleted だったり deleted_atだったりなんでもいいのですが、Laravel 使いらしく、ここでは deleted_at をとしています。

ここでは deleted_at カラムに日付型の値が

  • 入っていればそのレコードは削除されていると見なし、
  • 入っておらず NULL であれば削除されていないと見なします。

物理削除に対する論理削除のメリット・デメリット

メリット

論理削除は上述のとおり実際には削除しないわけなので、 実行後もそのレコードにアクセスすることができます。
よって当然、以下のことが可能です。

  • 論理削除したレコードを取得
  • 論理削除したレコードを復元
-- 論理削除したレコードを取得
SELECT
  *
FROM
  shops
WHERE
  deleted_at IS NOT NULL
;

-- 論理削除したレコードを復元
UPDATE
  shops
SET
  deleted_at = NULL
WHERE
  deleted_at IS NOT NULL
;
  • ある店舗データが不要になったためアプリケーションからは消し去りたい。ただ、いつかそのデータを 復元 させたくなる可能性がある
  • 間違って店舗データを消してしまったので 復元 させたい

みたいなときに使えますよね。

データを事実上残しておける。
メリットはここに尽きます。

デメリット

  • データが残り続けるため DB 容量を圧迫 してしまうことと
  • 常に削除フラグカラムを考慮 して WHERE 句を記述しなければならないこと

が論理削除のデメリットです。

常に削除フラグカラムを考慮して WHERE 句を記述しなければならない

たとえば is_published が FALSE で、かつ不削除レコードを取得しようとすると、

物理削除で運用されているテーブルであれば

-- 物理削除運用のテーブル
SELECT
  shops
WHERE
  is_published = FALSE
;

これで済むのに対し、論理削除で運用されているテーブルであれば

-- 論理削除運用のテーブル
SELECT
  shops
WHERE
  is_published = FALSE
  AND deleted_at IS NULL -- 不削除レコードを取得するときは、ここがいつも必要
;

としなければなりませんよね。

アプリケーションでフレームワークを使っているなら、プログラムコード上はこのあたりを上手に隠蔽してくれていそうですが、SQL を実行するぶんにはいつも削除フラグが付いてまわるため、 付け忘れ とかも全然あり得ます。
気をつけたいです。

まとめ

商業的に使ってる DB なら基本的に論理削除を選択するのがよさそうな気がしていますが、利用者数が多くなってきてレコード数がモリモリ膨らんでくるとパフォーマンスとかが課題になってきそうです。

その場合、たとえばユーザーアカウントを保存するテーブルのレコードだとすれば、

  • 基本はサービス退会時にレコードを論理削除するが、
  • 1年以上ログインがなければ物理削除する

みたいな掛け合わせでの運用が必要になってくるかもしれません。

Discussion