SQL 语言基础（DDL / DML / DCL / TCL） · DML — 数据增删改

如何高效地删除一张大表中 90% 的数据？为什么不能直接 DELETE？

How to efficiently delete 90% of the data from a large table? Why not just

答案语言

考察要点

这道题面试官想看你是否理解：

DML（DELETE）与 DDL（TRUNCATE/CREATE）操作在数据库内部执行的本质区别，尤其是在处理海量数据时的性能和资源开销。
InnoDB 存储引擎的 MVCC、事务日志（redo/undo log）、锁机制以及空间管理（高水位线） 对 DELETE 操作的影响。
解决问题的逆向思维能力：当删除大部分数据的成本极高时，能否想到保留小部分数据是更优的解法。

核心概念

直接对大表执行 DELETE 之所以不可行，是因为其背后高昂的代价。这主要由 InnoDB 的几个核心机制决定：

事务与日志 (Transaction & Logging)
- DELETE 是 DML 操作，完全支持事务。 这意味着每一行删除都会被记录到 undo log（用于回滚和 MVCC）和 redo log（用于崩溃恢复）。
- Binlog 记录： 如果开启了 binlog，每一行的删除操作（或基于语句的 DELETE 语句本身）都会被记录下来，用于主从复制。删除 90% 的数据会产生海量的日志，可能撑爆磁盘，并导致严重的主从延迟。
MVCC (多版本并发控制)
- InnoDB 中，DELETE 操作并非立即物理删除数据行，而是将其标记为“已删除”，并由后台的 Purge 线程择机回收。
- 在 Purge 完成前，这些“已删除”的行仍然占据着物理空间，并作为历史版本存在，以供其他并发的、启动时间更早的事务读取。删除大量数据会瞬间产生巨量的“垃圾”数据，导致表膨胀。
锁机制 (Locking)
- DELETE 操作会对自己要删除的行加上排他锁（X Lock）。如果 WHERE 条件无法精确命中索引，可能会导致大范围的间隙锁（Gap Lock）甚至表锁，严重阻塞其他业务的并发写入和更新。
磁盘空间与碎片 (Disk Space & Fragmentation)
- 即便 Purge 线程回收了已删除行所占的空间，这部分空间也不会立即返还给操作系统。这被称为“高水位线（High Water Mark）”问题。
- 这些被释放的空间会留在表空间文件（.ibd 文件）内部，形成内部碎片，可供后续的 INSERT 操作复用。但如果后续没有大量写入，表文件的大小不会减小，造成空间浪费。若要收缩空间，需要执行 OPTIMIZE TABLE 或重建表，而这些操作通常是阻塞的且耗时很长。

操作	`DELETE FROM t WHERE ...`	`TRUNCATE TABLE t`
类型	DML	DDL
事务性	支持，可回滚	不支持，隐式提交
日志量	巨大（undo, redo, binlog）	极小（仅记录 DDL 操作）
触发器	触发	不触发
`MVCC`	产生大量历史版本	无
锁	行锁/间隙锁/表锁	表级元数据锁
空间回收	不自动回收给 OS，产生碎片	回收给 OS，无碎片
自增值	不重置	重置
速度	极慢	极快

多种解法

核心思路是“保留需要的数据，而不是删除不要的数据”。

解法 1：`CREATE TABLE ... SELECT` + `RENAME` (最优解)

此方法适用于可以接受短暂服务中断（通常在秒级）的场景，是处理此类问题的最佳实践。

步骤:

创建新表并导入需要保留的数据 (10%)。

sql

 1-- 推荐先创建表结构，再插入数据，这样可以保证索引、约束等结构被完整复制
 2CREATE TABLE t_large_new LIKE t_large;
 3
 4-- 将需要保留的数据插入新表
 5INSERT INTO t_large_new SELECT * FROM t_large WHERE [保留数据的条件];

或者，如果不需要保留原表的索引和约束，可以一步到位：

sql

 1CREATE TABLE t_large_new AS SELECT * FROM t_large WHERE [保留数据的条件];

原子性地重命名表，完成切换。
sql
```
 1RENAME TABLE t_large TO t_large_archive, t_large_new TO t_large;
```
RENAME TABLE 是一个原子操作，非常快，能最大程度缩短服务不可用的时间。
删除旧表。
sql
```
 1DROP TABLE t_large_archive;
```

版本兼容性: ✅ 所有 MySQL 版本。
复杂度: 时间复杂度主要在 INSERT ... SELECT，约为 $O(N_{kept})$ ，其中 $N_{kept}$ 是保留的数据量。远小于 DELETE 的 $O(N_{deleted})$ 。空间复杂度需要额外 $O(N_{kept})$ 的存储。
索引依赖: SELECT 部分的 WHERE 条件最好有索引，以加速数据查找。

解法 2：分区表 `DROP PARTITION` (特定场景最优解)

如果大表已经根据时间等维度做了分区，并且删除条件恰好与分区键吻合，那么这是最高效、最理想的方案。

前提: 表 t_large 已按月分区，需要删除某个或某些月的数据。

sql

 1-- DDL for a partitioned table example
 2CREATE TABLE sales (
 3    id INT AUTO_INCREMENT,
 4    sale_date DATE,
 5    amount DECIMAL(10, 2),
 6    PRIMARY KEY (id, sale_date)
 7)
 8PARTITION BY RANGE (YEAR(sale_date)) (
 9    PARTITION p2022 VALUES LESS THAN (2023),
10    PARTITION p2023 VALUES LESS THAN (2024),
11    PARTITION p_future VALUES LESS THAN MAXVALUE
12);

操作:

sql

 1-- 直接删除整个分区，这是一个元数据操作，瞬间完成
 2ALTER TABLE sales DROP PARTITION p2022;

版本兼容性: ✅ 所有支持分区的 MySQL 版本。
复杂度: $O(1)$ ，几乎是瞬时操作，因为它只修改表的元数据，物理上相当于直接删除分区对应的文件。
索引依赖: 无需索引，操作与分区定义直接相关。

解法 3：循环批量 `DELETE` (在线业务无奈之选)

如果业务完全不能停机，且无法使用在线 schema 变更工具，只能退而求其次，采用分批删除的方式，将影响降到最低。

思路: 通过一个循环，每次只删除一小部分数据（如 1000 行），然后暂停一小段时间，避免单次操作产生过大的事务和日志，把对数据库的冲击分散开。

sql

 1-- 使用脚本或存储过程执行
 2DELIMITER $$
 3CREATE PROCEDURE batch_delete_large_table()
 4BEGIN
 5    DECLARE rows_affected INT;
 6    SET rows_affected = 1; -- 初始化，保证循环至少执行一次
 7
 8    WHILE rows_affected > 0 DO
 9        -- 找到要删除的主键，确保走主键索引，避免锁升级
10        DELETE FROM t_large 
11        WHERE [删除数据的条件] 
12        LIMIT 1000; -- 每次删除 1000 条
13
14        SET rows_affected = ROW_COUNT(); -- 获取上一条语句影响的行数
15
16        DO SLEEP(0.1); -- 暂停 0.1 秒，将 CPU 和 IO 资源让出
17    END WHILE;
18END$$
19DELIMITER ;
20
21CALL batch_delete_large_table();

版本兼容性: ✅ 所有 MySQL 版本。
复杂度: 时间复杂度仍为 $O(N_{deleted})$ ，但由于分批和休眠，总耗时会非常长。
索引依赖: WHERE 条件必须能高效利用索引（最好是主键或唯一索引），否则每次 DELETE 都会是小范围的表扫描，性能极差。

常见低分回答

直接 DELETE FROM t_large WHERE ...; —— 这是最典型的错误。候选人没有意识到大批量 DELETE 的性能灾难，包括巨量日志、锁竞争、MVCC 开销和空间不回收等问题。
回答 TRUNCATE TABLE t_large; —— 混淆了 TRUNCATE 和 DELETE 的语义。TRUNCATE 用于清空整张表，而题目要求是删除 90% 的数据，保留 10%。这表明候选人对 SQL 命令的适用场景理解不清。
循环 DELETE 但不加 LIMIT 或 sleep —— 虽然想到了分批，但 DELETE FROM t WHERE [非索引列] = 'value' 这样的语句，在没有 LIMIT 的情况下，依然会尝试锁定所有满足条件的行，可能一次性就锁住 90% 的数据，和直接 DELETE 没区别。不加 sleep 则可能持续高负载压垮数据库或导致主从严重延迟。
在 RENAME 方案中，忽略了索引、外键、触发器 —— 只说了 CREATE TABLE ... AS SELECT 和 RENAME，但没提新表会丢失原表的索引、外键约束和触发器，需要手动重建。这在生产环境中是致命的疏漏，体现了候选人缺乏实践经验。

工程实践与优化

监控先行: 在执行任何大规模数据变更前，必须监控数据库的关键指标，如 CPU、IOPS、主从延迟、活跃线程数、锁等待等。
使用专业工具: 对于方案 1，可以使用 pt-online-schema-change 或 gh-ost 等工具来实现在线的数据腾挪。它们通过创建“幽灵表”和触发器，能以极小的锁和业务影响来完成表的重建。对于方案 3，Percona Toolkit 中的 pt-archiver 是一个成熟、安全的批量归档/删除工具，它内置了防止主从延迟过大、控制负载的机制。
执行计划分析 (EXPLAIN): 无论是 INSERT ... SELECT 还是批量 DELETE，都必须先用 EXPLAIN 分析 SELECT 或 WHERE 部分，确保查询能用上索引，避免全表扫描。关键看 type (理想是 ref, range, eq_ref) 和 key (是否用了预期索引)。
事务控制: 批量 DELETE 应该在每次 DELETE 后显式或隐式提交事务，以释放锁并清理日志。长时间持有的大事务是数据库的噩梦。
外键处理: 如果表上有外键，方案 1 在 DROP TABLE 时会失败。需要先 SET FOREIGN_KEY_CHECKS=0;，操作完成后再 SET FOREIGN_KEY_CHECKS=1;，并在新表上重建外键约束。

常见追问与延伸题目

追问：如果业务完全不能停，RENAME 方案怎么做到对业务无感知？
- 回答要点: 这就需要借助在线 schema 变更工具，如 gh-ost 或 pt-online-schema-change。它们的原理是：
  1. 创建一个与原表结构相同的新表（幽灵表）。
  2. 在新表上执行数据迁移（即只 INSERT 需要保留的 10% 数据）。
  3. 在原表上创建触发器（INSERT/UPDATE/DELETE），将迁移过程中的增量变更同步到新表。
  4. 当新表数据与原表追平后，通过一次原子性的 RENAME 操作完成切换，最后清理原表和触发器。整个过程对业务几乎无感知，只有最后切换瞬间有极短暂的锁。
追问：刚才提到的批量 DELETE，如果执行到一半数据库实例重启了，会发生什么？数据会不一致吗？
- 回答要点: 不会不一致。因为 InnoDB 是支持 ACID 的事务性存储引擎。
  1. 对于已提交的批次: 已经 COMMIT 的 DELETE 操作是持久化的。重启后，这部分数据确实被删除了。
  2. 对于正在执行但未提交的批次: 数据库崩溃时，这个批次的事务尚未提交。在实例恢复期间，InnoDB 会通过 undo log 对这个未完成的事务进行回滚。因此，这个批次的数据会恢复到删除前的状态。
  3. 结论: 数据始终保持一致性。重启后，只需从断点处重新执行删除脚本即可。
追问：如果这张表是 PostgreSQL，解决方案有什么不同？
- 回答要点: 核心思路类似，但实现细节和术语有差异。
  1. CREATE TABLE ... AS SELECT 方案: 完全适用，PostgreSQL 也支持 CREATE TABLE ... AS SELECT 和 ALTER TABLE ... RENAME TO。
  2. 空间回收: PostgreSQL 的 DELETE 同样不会立即回收空间，而是将行标记为 "dead tuples"。需要通过 VACUUM 命令（特别是 VACUUM FULL）来回收空间和整理碎片。VACUUM FULL 会锁表，而常规的 AUTOVACUUM 进程则在后台无锁地清理。
  3. 在线工具: PostgreSQL 生态中没有像 gh-ost 这样普适的工具，但可以通过触发器和逻辑复制等方式手动实现类似的在线数据迁移。
  4. 分区表: PostgreSQL 的分区表功能非常强大，DROP PARTITION (在 PG 中是 DETACH PARTITION 然后 DROP TABLE) 同样是最高效的方式。