• TRUNCATE 在 InnoDB 中本質(zhì)是 “丟棄舊表空間 + 創(chuàng)建新空表空間”；
• DROP 是 “從數(shù)據(jù)字典移除表元數(shù)據(jù) + 刪除 .ibd 文件”；
• 兩者都不遍歷或修改原有數(shù)據(jù)行，因此無需記錄每行的 Redo。

4.那它們寫的是什么 Redo？

它們寫的是 “元數(shù)據(jù)操作”的 Redo Log，主要包括：

（1）數(shù)據(jù)字典（Data Dictionary）變更

MySQL 8.0 將數(shù)據(jù)字典完全存儲(chǔ)在 InnoDB 表中（如 tables等）。
執(zhí)行 DROP TABLE t 時(shí)，InnoDB 會(huì)：

• 在 tables 中刪除對應(yīng)記錄；
• 這個(gè)刪除操作本身就是一個(gè) DML，會(huì)生成 Redo Log！

所以：雖然用戶表的數(shù)據(jù)沒寫 Redo，但系統(tǒng)表的變更寫了 Redo。

（2）表空間（Tablespace）元信息變更

• TRUNCATE 會(huì)分配新的表空間 ID 或重置空間頭；
• DROP 會(huì)標(biāo)記表空間為“可刪除”并在 purge 階段清理；
• 這些操作涉及 InnoDB 系統(tǒng)頁（如 FSP_HDR、IBUF_BITMAP）的修改，也會(huì)產(chǎn)生 Redo。

（3）DDL 日志（DDL Log Table）

MySQL 8.0 引入了 原子 DDL 機(jī)制，使用一張隱藏的 mysql.innodb_ddl_log 表（臨時(shí)表）記錄 DDL 步驟。
例如 DROP TABLE 可能包含：

1. 刪除索引
2. 刪除表數(shù)據(jù)字典記錄
3. 刪除 .ibd 文件

每一步都記錄到 innodb_ddl_log，而該表的插入/刪除操作同樣會(huì)寫 Redo Log，確保崩潰后能回放或回滾整個(gè) DDL。

可通過 SELECT * FROM mysql.innodb_ddl_log;（需 SUPER 權(quán)限）查看（通常為空，因操作完成后自動(dòng)清理）。

5. 崩潰恢復(fù)時(shí)如何工作？

假設(shè)在 DROP TABLE t 執(zhí)行到一半時(shí) MySQL 崩潰：

1. 重啟時(shí)，InnoDB 進(jìn)行 crash recovery，重放 Redo Log；
2. Redo 中包含：
   • 對 mysql.tables 的刪除記錄；
   • 對 innodb_ddl_log 的寫入；
3. 若 DDL 未完成，InnoDB 會(huì)根據(jù) innodb_ddl_log 自動(dòng)回滾或完成剩余步驟（原子 DDL 保證）；
4. 最終結(jié)果：要么表完全存在，要么完全不存在——不會(huì)出現(xiàn)“半刪除”狀態(tài)。

6. 對比總結(jié)

總結(jié)：

DROP 和 TRUNCATE 之所以“僅寫入 DDL 相關(guān) Redo”，是因?yàn)樗鼈儾恍薷挠脩魯?shù)據(jù)行，但必須持久化“表結(jié)構(gòu)是否還存在”這一元數(shù)據(jù)狀態(tài)。
這些 Redo 記錄的是對 InnoDB 數(shù)據(jù)字典表 和 表空間元信息 的變更，用于在崩潰恢復(fù)時(shí)確保 DDL 操作的原子性與持久性，而非用于恢復(fù)被刪除的數(shù)據(jù)內(nèi)容。

這也是為什么：你可以從崩潰中恢復(fù)出“表是否被刪”的正確狀態(tài)，但無法恢復(fù)被 DROP 或 TRUNCATE 刪除的數(shù)據(jù)本身——因?yàn)閿?shù)據(jù)頁從未被 Redo 記錄過。

三、內(nèi)部處理流程

該流程圖展示了 InnoDB 引擎下三種操作的核心路徑。注意：DELETE 是唯一支持回滾的操作。

四、典型使用場景建議

• 使用 DELETE：

  • 需要按條件刪除部分?jǐn)?shù)據(jù)；
  • 要求操作可回滾（如在事務(wù)中）；
  • 存在 ON DELETE CASCADE 外鍵依賴；
  • 需要觸發(fā) DELETE 觸發(fā)器。

• 使用 TRUNCATE：

  • 需要快速清空整張大表；
  • 不關(guān)心事務(wù)回滾；
  • 希望重置自增 ID；
  • 表無外鍵引用（否則會(huì)報(bào)錯(cuò)）。

• 使用 DROP：

  • 表不再需要，需徹底移除；
  • 釋放磁盤空間是首要目標(biāo)；
  • 重構(gòu)表結(jié)構(gòu)（常配合 CREATE 使用）。

安全提示：生產(chǎn)環(huán)境中，建議對 DROP 和 TRUNCATE 設(shè)置 sql_safe_updates=1 或通過權(quán)限控制限制執(zhí)行。

sql_safe_updates參數(shù)解析

sql_safe_updates 是 MySQL 中的一個(gè)會(huì)話級系統(tǒng)變量，主要用于防止執(zhí)行沒有 WHERE 條件的 UPDATE 和 DELETE 語句，以避免意外的大規(guī)模數(shù)據(jù)修改。

主要作用
當(dāng) sql_safe_updates 設(shè)置為 ON（或 1）時(shí)：

1. 限制 UPDATE 語句

• 必須包含 WHERE 子句
• WHERE 子句中必須使用索引列
• 不能使用 LIMIT 子句（某些 MySQL 版本）

2. 限制 DELETE 語句

• 必須包含 WHERE 子句
• WHERE 子句中必須使用索引列
• 不能使用 LIMIT 子句

五、優(yōu)化DELETE操作的數(shù)據(jù)清理流程

當(dāng)必須使用 DELETE（如按時(shí)間范圍清理日志表）時(shí)，可采用以下策略避免性能災(zāi)難：

1.分批刪除（Chunked Deletion）

-- 示例：每次刪除 10,000 行，避免長事務(wù)和大 Undo
DELETE FROM logs
WHERE create_time < '2023-01-01'
ORDER BY id
LIMIT 10000;

在應(yīng)用層循環(huán)執(zhí)行，直到返回影響行數(shù)為 0。配合 SLEEP(0.1) 減少主從延遲。

2.使用主鍵或索引列排序

確保 ORDER BY 使用聚簇索引（如 InnoDB 的主鍵），避免全表掃描+臨時(shí)排序。

3.控制事務(wù)大小

• 單次 DELETE 不要超過 innodb_log_file_size 的 1/4；

• 可顯式提交每個(gè)批次：

  START TRANSACTION;
  DELETE ... LIMIT 10000;
  COMMIT; -- 避免 Undo Log 過大
  

4.避免在高峰期執(zhí)行

選擇業(yè)務(wù)低峰期，并監(jiān)控 Innodb_rows_deleted、Innodb_buffer_pool_pages_dirty 等指標(biāo)。

5.考慮歸檔替代刪除

對歷史數(shù)據(jù)先 INSERT INTO archive_table SELECT ...，再 DROP 原表分區(qū)（若使用分區(qū)表），效率更高。

“不要?jiǎng)h除舊數(shù)據(jù)，而是保留新數(shù)據(jù)并拋棄舊容器” —— 這是處理海量歷史數(shù)據(jù)清理的高性能范式，尤其在分區(qū)表場景下，效率遠(yuǎn)超 DELETE。

6.調(diào)整 Purge 線程參數(shù)（高級）

innodb_purge_threads = 4        # 默認(rèn) 4，可增至 8（多核）
innodb_max_purge_lag = 100000   # 控制 DML 延遲

六、高頻面試題

Q1：TRUNCATE能回滾嗎？

答：不能。TRUNCATE 是 DDL 操作，在 MySQL 中會(huì)隱式提交當(dāng)前事務(wù)，因此無法回滾。

Q2：DELETE不加 WHERE 會(huì)鎖全表嗎？

答：在 InnoDB 中，DELETE 仍使用行鎖，但會(huì)逐行加鎖，可能因鎖數(shù)量過多導(dǎo)致性能下降或鎖等待，但并非“表鎖”。

Q3：為什么TRUNCATE比DELETE快？

答：因?yàn)?TRUNCATE 不逐行刪除，而是直接釋放數(shù)據(jù)頁并重建空表，不寫 Undo Log，也無需觸發(fā)觸發(fā)器或檢查外鍵。

Q4：DROP TABLE后還能恢復(fù)數(shù)據(jù)嗎？

答：除非有備份或使用了企業(yè)級閃回功能（如 Percona 的 pt-archiver 或 binlog 回放），否則無法直接恢復(fù)。.ibd 文件已被刪除。

Q5：TRUNCATE會(huì)重置自增 ID 嗎？DELETE呢？

答：TRUNCATE 會(huì)重置；DELETE 不會(huì)，即使刪除所有行，下次插入仍從上次最大 ID +1 開始。

總結(jié)

到此這篇關(guān)于MySQL數(shù)據(jù)清除三劍客之DROP、DELETE與TRUNCATE深度對比的文章就介紹到這了,更多相關(guān)MySQL數(shù)據(jù)清除DROP、DELETE與TRUNCATE內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！