MySQLのDELETE、DROP、TRUNCATEの根本的な違いを理解していますか

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

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

https://qiita.com/dennis_wang/items/52d025ce176cf1a6082d#1-mysql%E3%81%AE%E5%85%A8%E4%BD%93%E7%9A%84%E3%81%AA%E3%82%A2%E3%83%BC%E3%82%AD%E3%83%86%E3%82%AF%E3%83%81%E3%83%A3

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/28664/74733545-faef-b648-9101-a614fe7b008d.png)
- データを削除する単純な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にどのように記録されるかを見てみましょう。
```sql
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  |

```sql
-- 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の値:初期化されない
```sql
INSERT INTO example (name) VALUES ('Dave'), ('Eve');
```
上記の操作を実行すると、テーブル内のデータは次のようになります。
自動インクリメントされる主キーはリセットされず、新しく挿入されたデータはid以前の最大値から継続されます。
| id | name |
|:-----------|------------:|
| 4   | Dave    |
| 5   | Eve      | 

# 2. drop table
## 1. 動作仕組み
`drop table`はテーブルのメタデータファイル (`.frm`ファイル) とデータファイル (`.ibd`) を直接削除するため、非常に高速です。これにより、テーブル関連の記憶領域がすべてリリースされます。
 1. `.frm`ファイル：
     - テーブルの構造定義を保存するために使用されます。カラム、インデックス、制約などの情報が含まれます
     - テーブルごとに、一つの`.frm`ファイルがあります
2. `.ibd`ファイル：
    - テーブルデータとインデックスを保存するファイル
    - InnoDBストレージ エンジンのテーブルの場合、データとインデックスは`.ibd`ファイルに保存されます

## 2. sample
1. テーブルを作成してデータを挿入する
    ```sql
    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`:テーブルのデータとインデックス

2. テーブルの削除
    ```sql
    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`実行
    ```sql
    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`の値がリセットされます
    ```sql
    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`あり）<br>・テーブルの全データ(`where`なし)| テーブル全体(`truncate table`:`where`利用不可)  |・テーブル全体(`where`利用不可)<br> ・データベース全体(`drop database`)|
| 削除内容 | ・データ一件ずつ削除<br>・テーブル構造保持<br>(`.ibd`ファイルと`.frm`ファイル保持) | データのみ削除、テーブル構造保持<br>(`.ibd`ファイル削除、`.frm`ファイル保持) |データとテーブル構造を全て削除（`.ibd`ファイルと`.frm`ファイル両方削除）|
| 必要な権限| delete権限| <font color="red">drop権限</font> |drop権限 |
| transaction途中のrollback | サポート| サポートしない |サポートしない|
| ログへの記録内容 | binlogに削除前後の行の全データ | `truncate table`クエリ文のみ|`drop table`のクエリ文のみ|
| 実行速度| 最も遅い（データの削除とインデックスの再構築が必要）	| 速い|最も速（drop `>` truncate `>` delete）|
| `AUTO_INCREMENT`の値|初期化されない|初期化される|-|
| 復旧可否| binlogから削除前のデータ復旧可能|不可能※|不可能※|

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