ユーザーが誤って「削除」ボタンを押した時の悪夢
数年前、あるVIP顧客が2億ドンの注文を誤って削除してしまい、一晩中対応に追われたことがあります。当時は物理削除（DELETE）を使用しており、データは即座にストレージから消えてしまいました。バックアップからそのレコードを復元するのに4時間以上かかり、非常に骨の折れるリスクの高い経験でした。
その出来事以来、私は「必要不可欠でない限り、決してデータを本当に削除しない」という原則を掲げています。代わりに**論理削除（Soft Delete）**を使用します。この手法は、レコードに「削除済み」のフラグを立てて画面上から非表示にするだけで、データはデータベース内に安全に残り、必要に応じて救出できるようにするものです。
計画なしに実装すると、システムが肥大化した際にパフォーマンスの問題やユニーク制約（Unique Constraint）のエラーに直面することになります。
なぜBooleanではなくDATETIMEを使うべきなのか？
多くの人は is_deleted に TINYINT(1) を選びがちですが、実務経験上、deleted_at カラムを使用し、型は DATETIME または TIMESTAMP にすることをお勧めします。
ALTER TABLE users ADD COLUMN deleted_at DATETIME DEFAULT NULL;理由は単純です。第一に、監査（オーディット）のために正確な削除日時を把握できること. 第二に、NULL 値はデータの高速なフィルタリングに非常に役立つこと。そして最後に、これが後述するユニークインデックスの問題を解決する鍵となるからです。
削除時には、現在時刻を更新するだけです：
UPDATE users SET deleted_at = NOW() WHERE id = 123;その後のすべてのクエリには WHERE deleted_at IS NULL を含める必要があります。簡単そうに聞こえますが、ここからが本当の技術的な課題の始まりです。
ユニーク制約エラーの根本的な解決策
問題：削除済みのメールアドレスで再登録できない
例えば、users テーブルの email カラムに UNIQUE 制約があるとします。ユーザーAがアカウントを削除しても、レコードは以前のメールアドレスのまま残ります。ユーザーAが同じメールアドレスで再登録しようとすると、MySQLは（論理削除されているにもかかわらず）重複エラー（Duplicate Entry）を返します。
解決策1：複合ユニークインデックスに deleted_at を含める
(email, deleted_at) のペアにユニークインデックスを作成する方法があります。しかし、MySQLには「ユニークインデックス内のカラムに NULL が含まれる場合、NULL != NULL とみなされるため、同一の値を複数挿入できてしまう」という特性があります。これにより、アクティブなアカウント間でのメールアドレスの唯一性が保てなくなる可能性があります。
解決策2：仮想カラム（Virtual Column）の使用（MySQL 8.0以降を推奨）
これは、私がデータを最もクリーンに保つために好んで使う方法です。レコードが削除されていない時だけ値を持つ仮想カラムを作成します：
ALTER TABLE users
ADD COLUMN active_email VARCHAR(255)
GENERATED ALWAYS AS (IF(deleted_at IS NULL, email, NULL)) VIRTUAL;
CREATE UNIQUE INDEX idx_unique_active_email ON users(active_email);この方法では、deleted_at に値がある（削除済み）場合、active_email は NULL になります。MySQLはユニークインデックス内で複数の NULL を許容するため、同じメールアドレスを何度も論理削除できますが、「アクティブ」な状態のメールアドレスは常に一つだけに制限されます。
大規模データベース向けのインデックス最適化
私が管理しているシステムには、50GBを超えるテーブルがあります。すべての SELECT クエリでインデックスなしに deleted_at でフィルタリングを行うと、MySQLは何百万行もの古いデータをスキャンすることになり、レスポンス速度が著しく低下します。
deleted_at 単体でインデックスを貼るのではなく、**複合インデックス（Composite Index）**を使用してください。例えば、status でユーザーを検索することが多い場合は次のようにします：
CREATE INDEX idx_status_active ON users (status, deleted_at);deleted_at をインデックスの最後に配置することで、MySQLのオプティマイザは他の条件でフィルタリングする前に、削除済みのレコードを素早く除外できるようになります。
運用およびデータクリーンアップ戦略
論理削除されたデータは年月とともに蓄積されます。私の統計では、運用開始から2年でこの「ゴミ」データがテーブル容量の30%を占めることもあります。システムを常にスムーズに動作させるには、以下の2つの戦略が必要です：
1. セキュリティのためのデータベースビューの使用
開発者が WHERE deleted_at IS NULL を付け忘れてUI上にデータが漏洩するのを防ぐために、View（ビュー）を作成します：
CREATE VIEW active_users AS
SELECT * FROM users WHERE deleted_at IS NULL;開発チームはこのViewからクエリを投げるだけで済みます。論理削除に関するロジックのリスクは、データベース層で完全に排除されます。
2. 物理削除（Hard Delete）の自動化
長期間削除されたままのデータ（例：1年以上経過）は、通常、復元の価値がありません。私は深夜2時にクリーンアップを行うクローンジョブ（Cronjob）を設定しています：
DELETE FROM users
WHERE deleted_at < DATE_SUB(NOW(), INTERVAL 1 YEAR)
LIMIT 5000; -- テーブルロックを避けるために分割して実行このクリーンアップにより、インデックスのサイズが縮小され、RAMキャッシュの効率が向上し、ストレージコストを大幅に節約できます。
結論として、論理削除はデータに対する優れた安全網です。しかし、安全性と引き換えにパフォーマンスを犠牲にしないよう、仮想カラムや複合インデックスを組み合わせてプロフェッショナルに実装しましょう。この実務に基づいた共有が、あなたのシステムの安定運用に役立つことを願っています。
