生产环境的一张表已经有几千万甚至上亿条数据，现在业务需求要求删除其中的部分数据，但当你执行DELETE语句时，发现数据库CPU飙升、IO占用100%，整个系统响应缓慢甚至无响应...今天就来聊聊千万级大表删除数据的正确姿势，让你避开那些让DBA抓狂的坑！
一、为什么大表删除这么危险？
在开始介绍解决方案之前，我们先来理解为什么大表删除操作会有这么大的风险。
1.1 DELETE操作的本质
-- 看似简单的DELETE语句，背后其实很复杂
DELETE FROM user_behavior WHERE create_time < '2023-01-01';
当我们执行DELETE语句时，数据库实际上做了很多事情：
- 记录事务日志：将删除操作记录到redo log和undo log中
1.2 大表删除的风险
对于千万级的大表，这些操作的成本会成倍放大：
// 模拟大表删除的复杂度
public class LargeTableDeleteRisk {
/**
* 大表删除的风险分析
*/
public void analyzeRisks() {
System.out.println("=== 大表删除的主要风险 ===");
System.out.println("1. 锁竞争：长时间持有表锁或行锁");
System.out.println("2. 事务日志膨胀：产生大量redo/undo日志");
System.out.println("3. 索引维护开销：每个索引都要更新");
System.out.println("4. 空间碎片：删除后产生大量碎片");
System.out.println("5. 主从延迟：主库删除快，从库跟上慢");
System.out.println("6. 内存消耗：缓冲池被大量脏页占据");
}
}
二、常见的错误做法
让我们先来看看一些常见的错误做法，避免踩同样的坑。
2.1 一次性删除所有数据
-- ❌ 错误做法：一次性删除大量数据
DELETE FROM user_behavior WHERE create_time < '2023-01-01';
-- 问题：
-- 1. 事务过大，回滚段可能撑爆
-- 2. 长时间锁表，影响其他业务
-- 3. 如果中断，需要重新开始
2.2 不考虑索引影响
-- ❌ 错误做法：在没有合适索引的字段上删除
DELETE FROM user_behavior WHERE ext_field = 'some_value';
-- 问题：
-- 1. 全表扫描，性能极差
-- 2. CPU和IO资源消耗巨大
-- 3. 可能导致数据库假死
2.3 忽视主从复制延迟
-- ❌ 错误做法：不考虑主从延迟
DELETE FROM user_behavior WHERE id BETWEEN 1 AND 1000000;
-- 问题：
-- 1. 从库复制延迟严重
-- 2. 读写分离架构下可能出现数据不一致
-- 3. 影响业务正常运行
三、正确的删除策略
接下来，我们来看看几种安全有效的删除策略。
3.1 分批删除（推荐）
分批删除是最常用也是最安全的方法：
-- ✅ 正确做法：分批删除
DELIMITER $$
CREATEPROCEDURE batch_delete_user_behavior()
BEGIN
DECLARE done INTDEFAULTFALSE;
DECLARE affected_rows INTDEFAULT0;
-- 设置每次删除的批次大小
SET @batch_size = 1000;
REPEAT
-- 执行删除操作
DELETEFROM user_behavior
WHERE create_time < '2023-01-01'
ORDERBYid
LIMIT @batch_size;
-- 获取受影响的行数
SET affected_rows = ROW_COUNT();
-- 记录日志
SELECTCONCAT('Deleted ', affected_rows, ' rows') AS log_message;
-- 等待一段时间，让系统恢复
DOSLEEP(1);
UNTIL affected_rows = 0 ENDREPEAT;
SELECT'Batch delete completed'ASresult;
END$$
DELIMITER ;
-- 调用存储过程
CALL batch_delete_user_behavior();
3.2 使用游标删除
对于更复杂的删除逻辑，可以使用游标：
DELIMITER $$
CREATEPROCEDURE cursor_delete_user_behavior()
BEGIN
DECLARE done INTDEFAULTFALSE;
DECLARE v_id BIGINT;
DECLARE v_create_time DATETIME;
-- 声明游标
DECLARE cur CURSORFOR
SELECTid, create_time
FROM user_behavior
WHERE create_time < '2023-01-01'
ORDERBYid
LIMIT10000;
DECLARE CONTINUE HANDLERFORNOTFOUNDSET done = TRUE;
-- 打开游标
OPEN cur;
read_loop: LOOP
FETCH cur INTO v_id, v_create_time;
IF done THEN
LEAVE read_loop;
ENDIF;
-- 执行删除
DELETEFROM user_behavior WHEREid = v_id;
-- 每100条提交一次事务
IF v_id % 100 = 0 THEN
COMMIT;
ENDIF;
ENDLOOP;
-- 关闭游标
CLOSE cur;
-- 最终提交
COMMIT;
END$$
DELIMITER ;
3.3 Java程序分批删除
在应用程序层面控制删除节奏更加灵活：
@Service
@Slf4j
publicclass LargeTableDeleteService {
@Autowired
private JdbcTemplate jdbcTemplate;
/**
* 分批删除用户行为数据
* @param beforeDate 删除此日期之前的数据
* @param batchSize 每批删除的数量
* @param sleepTime 每批之间的间隔时间(毫秒)
*/
public void batchDeleteUserBehavior(Date beforeDate, int batchSize, long sleepTime) {
String sql = "DELETE FROM user_behavior WHERE create_time < ? ORDER BY id LIMIT ?";
String countSql = "SELECT COUNT(*) FROM user_behavior WHERE create_time < ?";
int totalDeleted = 0;
int deletedInBatch;
do {
try {
// 执行删除
deletedInBatch = jdbcTemplate.update(sql, beforeDate, batchSize);
totalDeleted += deletedInBatch;
log.info("本次删除 {} 条记录，累计删除 {} 条记录", deletedInBatch, totalDeleted);
// 如果有删除，则等待一段时间
if (deletedInBatch > 0) {
Thread.sleep(sleepTime);
}
} catch (Exception e) {
log.error("删除过程中发生错误", e);
break;
}
} while (deletedInBatch > 0);
log.info("删除完成，总共删除 {} 条记录", totalDeleted);
}
/**
* 获取待删除的数据量
*/
public long getPendingDeleteCount(Date beforeDate) {
String sql = "SELECT COUNT(*) FROM user_behavior WHERE create_time < ?";
return jdbcTemplate.queryForObject(sql, Long.class, beforeDate);
}
}
四、高级删除策略
对于超大规模的数据删除，我们需要更高级的策略。
4.1 表重命名策略
这是处理超大表删除的最佳方案之一：
-- ✅ 高级策略：表重命名 + 分批插入
-- 1. 创建新表结构（相同或优化后的结构）
CREATETABLE user_behavior_new LIKE user_behavior;
-- 2. 将需要保留的数据复制到新表
INSERTINTO user_behavior_new
SELECT * FROM user_behavior
WHERE create_time >= '2023-01-01';
-- 3. 重命名表（瞬间完成）
RENAMETABLE user_behavior TO user_behavior_old,
user_behavior_new TO user_behavior;
-- 4. 删除旧表（可以在业务低峰期执行）
DROPTABLE user_behavior_old;
这种方法的优点：
4.2 分区表删除
如果表已经做了分区，删除会变得非常简单：
-- 查看表的分区情况
SHOW CREATE TABLE user_behavior_partitioned;
-- 删除整个分区（非常快）
ALTER TABLE user_behavior_partitioned DROP PARTITION p2022;
-- 或者truncate分区
ALTER TABLE user_behavior_partitioned TRUNCATE PARTITION p2022;
4.3 使用pt-archiver工具
Percona Toolkit提供了专门用于大表操作的工具：
# 安装Percona Toolkit
yum install percona-toolkit
# 使用pt-archiver删除数据
pt-archiver \
--source h=localhost,D=mydb,t=user_behavior \
--where "create_time < '2023-01-01'" \
--progress 10000 \
--limit 1000 \
--bulk-delete \
--sleep-coef 0.5 \
--no-check-unique-key-change
五、删除操作的监控和优化
5.1 监控删除进度
-- 监控删除进度的SQL
SELECT
TABLE_SCHEMA,
TABLE_NAME,
TABLE_ROWS,
DATA_LENGTH,
INDEX_LENGTH,
(DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1024 AS size_gb
FROM information_schema.TABLES
WHERE TABLE_NAME = 'user_behavior';
5.2 监控系统资源
# 监控MySQL进程
mysqladmin processlist
# 监控系统负载
top -p $(pgrep mysqld)
# 监控磁盘IO
iostat -x 1
# 监控网络流量
iftop
5.3 删除后的优化
-- 删除后优化表结构
OPTIMIZE TABLE user_behavior;
-- 分析表统计信息
ANALYZE TABLE user_behavior;
-- 检查表完整性
CHECK TABLE user_behavior;
六、最佳实践总结
6.1 删除前的准备工作
@Component
@Slf4j
publicclass DeletePreparationService {
@Autowired
private JdbcTemplate jdbcTemplate;
/**
* 删除前检查清单
*/
public DeleteChecklist prepareDelete(String tableName, String condition) {
DeleteChecklist checklist = new DeleteChecklist();
try {
// 1. 估算待删除数据量
String countSql = "SELECT COUNT(*) FROM " + tableName + " WHERE " + condition;
Long count = jdbcTemplate.queryForObject(countSql, Long.class);
checklist.setEstimatedRows(count);
// 2. 检查表大小
String sizeSql = "SELECT " +
"(DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1024 AS size_gb " +
"FROM information_schema.TABLES " +
"WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ?";
Double size = jdbcTemplate.queryForObject(sizeSql, Double.class, tableName);
checklist.setTableSizeGb(size);
// 3. 检查索引情况
String indexSql = "SHOW INDEX FROM " + tableName;
List<Map<String, Object>> indexes = jdbcTemplate.queryForList(indexSql);
checklist.setIndexes(indexes);
// 4. 检查外键约束
String fkSql = "SELECT * FROM information_schema.KEY_COLUMN_USAGE " +
"WHERE REFERENCED_TABLE_NAME = ? AND TABLE_SCHEMA = DATABASE()";
List<Map<String, Object>> foreignKeys = jdbcTemplate.queryForList(fkSql, tableName);
checklist.setForeignKeys(foreignKeys);
checklist.setReady(true);
} catch (Exception e) {
log.error("删除前检查失败", e);
checklist.setReady(false);
checklist.setError(e.getMessage());
}
return checklist;
}
}
6.2 删除执行的最佳实践
@Service
@Slf4j
publicclass SafeDeleteService {
@Autowired
private JdbcTemplate jdbcTemplate;
/**
* 安全删除数据
*/
public void safeDelete(SafeDeleteRequest request) {
String sql = "DELETE FROM " + request.getTableName() +
" WHERE " + request.getCondition() +
" ORDER BY " + request.getOrderColumn() +
" LIMIT ?";
int totalDeleted = 0;
int deletedInBatch;
long startTime = System.currentTimeMillis();
try {
do {
// 开启事务
TransactionTemplate txTemplate = new TransactionTemplate(transactionManager);
deletedInBatch = txTemplate.execute(status -> {
try {
return jdbcTemplate.update(sql, request.getBatchSize());
} catch (Exception e) {
status.setRollbackOnly();
throw e;
}
});
totalDeleted += deletedInBatch;
// 记录进度
if (deletedInBatch > 0) {
long elapsed = System.currentTimeMillis() - startTime;
double rate = totalDeleted / (elapsed / 1000.0);
log.info("已删除 {} 条记录，平均速率 {:.2f} 条/秒", totalDeleted, rate);
// 等待
Thread.sleep(request.getSleepTime());
}
} while (deletedInBatch > 0 && !Thread.currentThread().isInterrupted());
long totalTime = System.currentTimeMillis() - startTime;
log.info("删除完成！总共删除 {} 条记录，耗时 {} 秒", totalDeleted, totalTime / 1000);
} catch (Exception e) {
log.error("删除过程中发生错误", e);
thrownew RuntimeException("删除失败", e);
}
}
}
6.3 删除后的清理工作
-- 删除后的重要操作
-- 1. 优化表结构（回收空间，整理碎片）
OPTIMIZETABLE user_behavior;
-- 2. 更新表统计信息
ANALYZETABLE user_behavior;
-- 3. 检查表完整性
CHECKTABLE user_behavior;
-- 4. 如果使用了binlog，可能需要清理
PURGEBINARYLOGSBEFOREDATE_SUB(NOW(), INTERVAL7DAY);
七、特殊情况处理
7.1 主从架构下的删除
@Service
@Slf4j
publicclass MasterSlaveDeleteService {
@Autowired
@Qualifier("masterJdbcTemplate")
private JdbcTemplate masterJdbcTemplate;
@Autowired
@Qualifier("slaveJdbcTemplate")
private JdbcTemplate slaveJdbcTemplate;
/**
* 主从架构下的安全删除
*/
public void masterSlaveSafeDelete(String tableName, String condition, int batchSize) {
// 1. 先在从库检查数据分布
String slaveCountSql = "SELECT COUNT(*) FROM " + tableName + " WHERE " + condition;
Long slaveCount = slaveJdbcTemplate.queryForObject(slaveCountSql, Long.class);
log.info("从库待删除数据量: {}", slaveCount);
// 2. 在主库执行删除
String masterDeleteSql = "DELETE FROM " + tableName + " WHERE " + condition + " LIMIT ?";
int deleted = masterJdbcTemplate.update(masterDeleteSql, batchSize);
log.info("主库删除 {} 条记录", deleted);
// 3. 等待从库同步
waitForSlaveSync();
}
private void waitForSlaveSync() {
// 等待主从同步完成
try {
Thread.sleep(5000); // 简单等待5秒
} catch (InterruptedException e) {
Thread.currentThread().interrupt();
}
}
}
7.2 有外键约束的删除
-- 查看外键约束
SELECT
CONSTRAINT_NAME,
TABLE_NAME,
COLUMN_NAME,
REFERENCED_TABLE_NAME,
REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME = 'user_behavior';
-- 临时禁用外键检查（谨慎使用）
SET FOREIGN_KEY_CHECKS = 0;
-- 执行删除操作
DELETEFROM user_behavior WHERE create_time < '2023-01-01';
-- 重新启用外键检查
SET FOREIGN_KEY_CHECKS = 1;
八、性能调优建议
8.1 MySQL参数优化
# my.cnf 中的相关配置
[mysqld]
# 增大binlog缓存
binlog_cache_size = 4M
# 调整innodb_buffer_pool_size
innodb_buffer_pool_size = 2G
# 调整日志文件大小
innodb_log_file_size = 512M
# 调整事务隔离级别（根据业务需求）
transaction-isolation = READ-COMMITTED
8.2 系统层面优化
# 调整系统参数
echo 'vm.swappiness = 1' >> /etc/sysctl.conf
sysctl -p
# 调整文件描述符限制
ulimit -n 65535
# 使用SSD存储
# 确保有足够的IO性能支撑删除操作
结语
千万级大表的数据删除是一个需要谨慎对待的操作，不当的处理方式可能导致严重的生产事故。通过本文介绍的各种策略和最佳实践，相信你能更好地应对这类挑战。
记住几个关键点：
阅读原文：原文链接
该文章在 2025/12/10 18:46:13 编辑过
400 186 1886
