MySQLのDELETE、DROP、TRUNCATEの違いについて説明した記事は多く存在しますが、それらは主に使い方の違いに焦点を当てているため、全体像の理解が難しいことがあります。基本原理から説明する記事が少ないため、ここで仕組みを整理してみました。

1. Delete

1. 動作仕組み

delete文はデータを一行ずつ削除し、トランザクションログを記録する必要もあるため、比較的時間がかかります。

データを削除する単純なdelete文でも、Parser(文法と構文がチェック), Optimizer(実行計画を立てクエリ最適化), Excutor(実行計画の実行)、最後にストレージエンジンによって提供される読み取り/書き込みAPIを呼び出してデータを削除することになります
テーブルにインデックスがある場合は、インデックスも削除されます
同時に、データの削除に加えて、redoログとbinlogログも記録する必要があることも忘れてはいけません

2. binlog内容

バイナリログ形式設定には3つのformatがあります。

STATEMENT形式: クエリ文そのものを記録
ROW形式(MySQL 5.7と8.0ではデフォルト値): 各行の変更を記録
MIXED形式: 基本はSTATEMENT形式、now(), UUID()などデータ固定ではない場合は、ROW形式に自動的に切り替え

mysqlのbinlog形式がrowである場合、binglogにどのように記録されるかを見てみましょう。

CREATE TABLE example (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);
 
INSERT INTO example (name) VALUES ('Alice'), ('Bob'), ('Charlie');

上記の操作を実行すると、テーブル内のデータは次のようになります。

id	name
1	Alice
2	Bob
3	Charlie

-- Whereを指定せず、tableのすべてのデータを削除
DELETE FROM example;

ROW 形式では、binlog は各行の削除操作を記録します (各行のすべての列の内容が記録されます。100万行がある場合、binlogは100行のデータを記録します。binlogの記録に非常に多くの時間がかかります。）

Binlogの内容:

# at 1234
#230801 10:00:00 server id 1  end_log_pos 1267  Table_map: `database`.`example` mapped to number 100
# at 1267
#230801 10:00:00 server id 1  end_log_pos 1300  Delete_rows: table id 100
### DELETE FROM `database`.`example`
### WHERE
###   @1=1
###   @2='Alice'
# at 1300
#230801 10:00:00 server id 1  end_log_pos 1333  Delete_rows: table id 100
### DELETE FROM `database`.`example`
### WHERE
###   @1=2
###   @2='Bob'
# at 1333
#230801 10:00:00 server id 1  end_log_pos 1366  Delete_rows: table id 100
### DELETE FROM `database`.`example`
### WHERE
###   @1=3
###   @2='Charlie'

3. redo logの内容

Transaction ID: トランザクションの一意の識別子
Log Sequence Number(LSN): REDO ログのシーケンスを識別するために使用される- ログシーケンス番号
Operation: DELETE などの特定のデータベース操作
Table: テーブル名
Row ID : 行の一意の識別子
Old Values : 操作前のデータ値

### REDO LOG ENTRY ###
Transaction ID: 12345
Log Sequence Number (LSN): 987654321
Operation: DELETE
Table: example
Row ID: 1
Old Values: { id: 1, name: 'Alice' }
 
### REDO LOG ENTRY ###
Transaction ID: 12345
Log Sequence Number (LSN): 987654322
Operation: DELETE
Table: example
Row ID: 2
Old Values: { id: 2, name: 'Bob' }
 
### REDO LOG ENTRY ###
Transaction ID: 12345
Log Sequence Number (LSN): 987654323
Operation: DELETE
Table: example
Row ID: 3
Old Values: { id: 3, name: 'Charlie' }

REDOログは物理ログであり、データページの変更を物理レベルで記録し、データベースクラッシュ後の回復に使用されます。
Binglogは論理ログを記録し、PITRデータ回復およびデータレプリケーションに使用されます。
DELETE FROM example;クエリが実行されると、テーブル内のデータ量が非常に大きい場合、テーブルのすべての行のデータがそれぞれのログに記録されます。ログ書き込みには非常に時間がかかります

4.AUTO_INCREMENTの値:初期化されない

INSERT INTO example (name) VALUES ('Dave'), ('Eve');

上記の操作を実行すると、テーブル内のデータは次のようになります。
自動インクリメントされる主キーはリセットされず、新しく挿入されたデータはid以前の最大値から継続されます。

id	name
4	Dave
5	Eve

2. drop table

1. 動作仕組み

drop tableはテーブルのメタデータファイル (.frmファイル) とデータファイル (.ibd) を直接削除するため、非常に高速です。これにより、テーブル関連の記憶領域がすべてリリースされます。

.frmファイル：
- テーブルの構造定義を保存するために使用されます。カラム、インデックス、制約などの情報が含まれます
- テーブルごとに、一つの.frmファイルがあります
.ibdファイル：
- テーブルデータとインデックスを保存するファイル
- InnoDBストレージエンジンのテーブルの場合、データとインデックスは.ibdファイルに保存されます

2. sample

テーブルを作成してデータを挿入する
```
CREATE TABLE example (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

INSERT INTO example (name) VALUES ('Alice'), ('Bob'), ('Charlie');
```
上記のクエリ文を実行すると、MySQLのデフォルトのデータディレクトリに2つのファイルを作成します。
```
/var/lib/mysql/your_database/example.frm
/var/lib/mysql/your_database/example.ibd
```
- example.frm:テーブルの構造定義
- example.ibd:テーブルのデータとインデックス
テーブルの削除
```
DROP TABLE example;
```

example.frmとexample.ibdファイルが削除され、それによってテーブル関連のストレージスペースをすべてリリースします
データを1行ずつ処理せずにファイルを直接削除するため、高速な操作です
binlog formatがROWに設定されていても、binlogで記録されるのはdelete文そのものです

# at 120
#220803 16:38:20 server id 1 end_log_pos 100 Query thread_id=2 exec_time=0 error_code=0 
SET TIMESTAMP=1659571100/*!*/; 
DROP TABLE `test`.`employees`/*!*/;

3. truncate table

1. 動作仕組み

truncate tableは、実際に内部でdrop tableとcreate tableが実行されます。だからtruncate tableを実行するにはdrop tableの権限が必要です。

内部のdrop tableの動作：
- .ibdファイルが削除されますが、テーブル構造の再定義を避けるために.frmファイルは残ります。これにより、テーブルの再構築プロセスが簡素化されます
- binlogでは削除されたデータが行ごとに記録されず、操作はdelete fromよりははるかに高速ですが、テーブル構造を再初期化する必要があるため、drop tableよりはわずかに遅くなります。
内部のcreate動作:
- .frmファイルを利用して、テーブル構造を再構築
- テーブルの構造を再初期化し、AUTO_INCREMENTの値はリッセットされる(1 から開始)

2. sample

truncate実行前に、データディレクトリに以下のファイルが存在：
```
/var/lib/mysql/your_database/example.frm
/var/lib/mysql/your_database/example.ibd
```
truncate実行
```
TRUNCATE TABLE example;
```

# `truncate`実行後`.bd`ファイルが削除され、`.frm`ファイルは残る
/var/lib/mysql/your_database/example.frm

binlog:
binlogはROW形式であっても、truncate talbeのクエリ文そのものしか記録されず、削除されたデータの内容はbinlogに記録されません。
```
# at 180
#220803 16:38:20 server id 1 end_log_pos 180 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1659571100/*!*/;
TRUNCATE TABLE `test`.`employees`/*!*/;
```

AUTO_INCREMENTの値がリセットされます

INSERT INTO example (name) VALUES ('Eve'), ('Frank');

上記の操作を実行すると、テーブル内のデータは次のようになります。AUTO_INCREMENTの値はリセットされ、新しく挿入されたデータは1から始まります。

id	name
4	Eve
5	Frank

4. まとめ

	delete	truncate	drop
SQL種類	DML	DDL	DDL
影響範囲	・テーブルの一部のデータ（`where`あり）・テーブルの全データ(`where`なし)	テーブル全体(`truncate table`:`where`利用不可)	・テーブル全体(`where`利用不可) ・データベース全体(`drop database`)
削除内容	・データ一件ずつ削除・テーブル構造保持 (`.ibd`ファイルと`.frm`ファイル保持)	データのみ削除、テーブル構造保持 (`.ibd`ファイル削除、`.frm`ファイル保持)	データとテーブル構造を全て削除（`.ibd`ファイルと`.frm`ファイル両方削除）
必要な権限	delete権限	drop権限	drop権限
transaction途中のrollback	サポート	サポートしない	サポートしない
ログへの記録内容	binlogに削除前後の行の全データ	`truncate table`クエリ文のみ	`drop table`のクエリ文のみ
実行速度	最も遅い（データの削除とインデックスの再構築が必要）	速い	最も速（drop `>` truncate `>` delete）
`AUTO_INCREMENT`の値	初期化されない	初期化される	-
復旧可否	binlogから削除前のデータ復旧可能	不可能※	不可能※

AWS RDSやAuroraなどのクラウドデータベースを利用する場合は、ポイントインタイムリカバリ（PITR）の機能を利用して、特定の時点までのデータ変更が復元できます。この機能は、データベースを誤った操作(例えば誤ってtableあるいはデータベース全体を削除してしまった)や障害が発生した時点の直前まで戻すことができます。PITRの仕組みは以下のようになっています：
- 自動バックアップ：
  データベースシステムは、定期的に完全バックアップを作成します。これにより、データベース全体のスナップショットが保存されます。
- binlog：
  データベース内のすべての変更（挿入、更新、削除）は、binlogに記録されます。このログは、変更が発生した順序で保存されます。
- 復元プロセス：
  - PITRを実行するには、まず最新(その時点に最も近い)の完全バックアップ(自動または手動 )を復元
  - その後、特定の時点(truncateやdrop実行前の時点)までbinlogを適用(binlog中のクエリ文を再度実行する)して、データベースを目的の状態に戻す
  - binlog中のクエリ文を再度実行するため、ログが多ければ多いほど、時間がかかります
delete文で削除されたデータはMysqlbinlogのツールを利用してbinlogを解析するだけで復元できる。 truncateとdrop文で削除されたデータはbinlogだけで復元できず、完全バックアップ + binlogの組み合わせで復元できる