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の組み合わせで復元できる