千万级大表如何删除数据？

事确 · 5 天前

前言

今天我们来聊聊一个让很多DBA和开发者头疼的话题——千万级大表的数据删除。
有些小伙伴在工作中，一遇到大表数据删除就手足无措，要么直接DELETE导致数据库卡死，要么畏手畏脚不敢操作。
我见过太多因为大表删除操作不当导致的"血案"：数据库长时间锁表、业务系统瘫痪、甚至主从同步延迟。
今天跟大家一起专门聊聊千万级大表数据删除的话题，希望对你会有所帮助。
一、为什么大表删除这么难？

在深入技术方案之前，我们先搞清楚为什么千万级大表的数据删除会如此困难。
有些小伙伴可能会想："不就是个DELETE语句吗，有什么难的？"
其实这里面大有学问。
数据库删除操作的底层原理

为了更直观地理解数据库删除操作的工作原理，我画了一个删除操作的底层流程图：

从这张图可以看出，一个简单的DELETE语句背后隐藏着这么多复杂的操作。
让我们详细分析每个环节的挑战：
1. 事务和锁的挑战

-- 一个看似简单的删除操作
DELETE FROM user_operation_log
WHERE create_time < '2023-01-01';
-- 实际上MySQL会这样处理：
-- 1. 获取表的写锁
-- 2. 逐行扫描10,000,000条记录
-- 3. 对每条匹配的记录：
-- - 写入undo log（用于回滚）
-- - 写入redo log（用于恢复）
-- - 更新所有相关索引
-- - 标记记录为删除状态
-- 4. 事务提交后才真正释放空间

复制代码

2. 资源消耗问题

磁盘I/O：undo log、redo log、数据文件、索引文件的大量写入
CPU：索引维护、条件判断、事务管理
内存：Buffer Pool管理、锁信息维护
网络：主从同步数据量巨大

3. 业务影响风险

锁等待超时：其他查询被阻塞
主从延迟：从库同步跟不上
磁盘空间：undo log暴增导致磁盘写满
性能下降：数据库整体性能受影响

有些小伙伴可能会问："我们用的是云数据库，这些问题还存在吗？"
我的经验是：云数据库只是降低了运维复杂度，但底层原理和限制依然存在。
二、方案一：分批删除（最常用）

分批删除是最基础也是最常用的方案，核心思想是"化整为零"，将大操作拆分成多个小操作。
实现原理

具体实现

方法1：基于主键分批

-- 存储过程实现分批删除
DELIMITER $$
CREATE PROCEDURE batch_delete_by_id()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE batch_size INT DEFAULT 1000;
DECLARE max_id BIGINT;
DECLARE min_id BIGINT;
DECLARE current_id BIGINT DEFAULT 0;
-- 获取需要删除的数据范围
SELECT MIN(id), MAX(id) INTO min_id, max_id
FROM user_operation_log
WHERE create_time < '2023-01-01';
WHILE current_id < max_id DO
-- 每次删除一个批次
DELETE FROM user_operation_log
WHERE id BETWEEN current_id AND current_id + batch_size - 1
AND create_time < '2023-01-01';
-- 提交事务，释放锁
COMMIT;
-- 休眠一下，让数据库喘口气
DO SLEEP(0.1);
-- 更新进度
SET current_id = current_id + batch_size;
-- 记录日志（可选）
INSERT INTO delete_progress_log
VALUES (NOW(), current_id, batch_size);
END WHILE;
END$$
DELIMITER ;

复制代码

方法2：基于时间分批

// Java代码实现基于时间的分批删除
@Service
@Slf4j
public class BatchDeleteService {
@Autowired
private JdbcTemplate jdbcTemplate;
/**
* 基于时间范围的分批删除
*/
public void batchDeleteByTime(String tableName, String timeColumn,
Date startTime, Date endTime,
int batchDays) {
Calendar calendar = Calendar.getInstance();
calendar.setTime(startTime);
int totalDeleted = 0;
long startMs = System.currentTimeMillis();
while (calendar.getTime().before(endTime)) {
Date batchStart = calendar.getTime();
calendar.add(Calendar.DAY_OF_YEAR, batchDays);
Date batchEnd = calendar.getTime();
// 确保不超过结束时间
if (batchEnd.after(endTime)) {
batchEnd = endTime;
}
String sql = String.format(
"DELETE FROM %s WHERE %s BETWEEN ? AND ? LIMIT 1000",
tableName, timeColumn
);
int deleted = jdbcTemplate.update(sql, batchStart, batchEnd);
totalDeleted += deleted;
log.info("批次删除完成: {}-{}, 删除{}条, 总计{}条",
batchStart, batchEnd, deleted, totalDeleted);
// 控制删除频率，避免对数据库造成过大压力
if (deleted > 0) {
try {
Thread.sleep(500); // 休眠500ms
} catch (InterruptedException e) {
Thread.currentThread().interrupt();
break;
}
} else {
// 没有数据可删，跳到下一个时间段
continue;
}
// 每删除10000条记录一次进度
if (totalDeleted % 10000 == 0) {
logProgress(totalDeleted, startMs);
}
}
log.info("删除任务完成! 总计删除{}条记录, 耗时{}秒",
totalDeleted, (System.currentTimeMillis() - startMs) / 1000);
}
private void logProgress(int totalDeleted, long startMs) {
long costMs = System.currentTimeMillis() - startMs;
double recordsPerSecond = totalDeleted * 1000.0 / costMs;
log.info("删除进度: {}条, 速率: {}/秒, 耗时: {}秒",
totalDeleted, String.format("%.2f", recordsPerSecond), costMs / 1000);
}
}

复制代码

方法3：使用LIMIT分批删除

-- 简单的LIMIT分批删除
DELIMITER $$
CREATE PROCEDURE batch_delete_with_limit()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE batch_size INT DEFAULT 1000;
DECLARE total_deleted INT DEFAULT 0;
WHILE done = 0 DO
-- 每次删除1000条
DELETE FROM user_operation_log
WHERE create_time < '2023-01-01'
LIMIT batch_size;
-- 检查是否还有数据
SET done = ROW_COUNT() = 0;
SET total_deleted = total_deleted + ROW_COUNT();
-- 提交释放锁
COMMIT;
-- 休眠控制频率
DO SLEEP(0.1);
-- 每删除10000条输出日志
IF total_deleted % 10000 = 0 THEN
SELECT CONCAT('已删除: ', total_deleted, ' 条记录') AS progress;
END IF;
END WHILE;
SELECT CONCAT('删除完成! 总计: ', total_deleted, ' 条记录') AS result;
END$$
DELIMITER ;

复制代码

分批删除的最佳实践

批次大小选择
- 小表：1000-5000条/批次
- 大表：100-1000条/批次
- 需要根据实际情况调整
休眠时间控制
- 业务高峰期：休眠1-2秒
- 业务低峰期：休眠100-500毫秒
- 夜间维护：可不休眠或短暂休眠
监控和调整
- 监控数据库负载
- 观察主从同步延迟
- 根据实际情况动态调整参数

三、方案二：创建新表+重命名

当需要删除表中大部分数据时，创建新表然后重命名的方式往往更高效。
实现原理

具体实现

-- 步骤1: 创建新表（结构同原表）
CREATE TABLE user_operation_log_new LIKE user_operation_log;
-- 步骤2: 导入需要保留的数据
INSERT INTO user_operation_log_new
SELECT * FROM user_operation_log
WHERE create_time >= '2023-01-01';
-- 步骤3: 创建索引（在数据导入后创建，效率更高）
ALTER TABLE user_operation_log_new ADD INDEX idx_create_time(create_time);
ALTER TABLE user_operation_log_new ADD INDEX idx_user_id(user_id);
-- 步骤4: 数据验证
SELECT
(SELECT COUNT(*) FROM user_operation_log_new) as new_count,
(SELECT COUNT(*) FROM user_operation_log WHERE create_time >= '2023-01-01') as expected_count;
-- 步骤5: 原子切换（需要很短的表锁）
RENAME TABLE
user_operation_log TO user_operation_log_old,
user_operation_log_new TO user_operation_log;
-- 步骤6: 删除旧表（可选立即删除或延后删除）
DROP TABLE user_operation_log_old;

复制代码

Java代码辅助实现

@Service
@Slf4j
public class TableRebuildService {
@Autowired
private JdbcTemplate jdbcTemplate;
/**
* 重建表方式删除数据
*/
public void rebuildTableForDeletion(String sourceTable, String condition) {
String newTable = sourceTable + "_new";
String oldTable = sourceTable + "_old";
try {
// 1. 创建新表
log.info("开始创建新表: {}", newTable);
jdbcTemplate.execute("CREATE TABLE " + newTable + " LIKE " + sourceTable);
// 2. 导入需要保留的数据
log.info("开始导入保留数据");
String insertSql = String.format(
"INSERT INTO %s SELECT * FROM %s WHERE %s",
newTable, sourceTable, condition
);
int keptCount = jdbcTemplate.update(insertSql);
log.info("成功导入{}条保留数据", keptCount);
// 3. 创建索引（可选，在导入后创建索引效率更高）
log.info("开始创建索引");
createIndexes(newTable);
// 4. 数据验证
log.info("开始数据验证");
if (!validateData(sourceTable, newTable, condition)) {
throw new RuntimeException("数据验证失败");
}
// 5. 原子切换
log.info("开始表切换");
switchTables(sourceTable, newTable, oldTable);
// 6. 删除旧表（可选立即或延后）
log.info("开始删除旧表");
dropTableSafely(oldTable);
log.info("表重建删除完成!");
} catch (Exception e) {
log.error("表重建过程发生异常", e);
// 清理临时表
cleanupTempTable(newTable);
throw e;
}
}
private void createIndexes(String tableName) {
// 根据业务需要创建索引
String[] indexes = {
"CREATE INDEX idx_create_time ON " + tableName + "(create_time)",
"CREATE INDEX idx_user_id ON " + tableName + "(user_id)"
};
for (String sql : indexes) {
jdbcTemplate.execute(sql);
}
}
private boolean validateData(String sourceTable, String newTable, String condition) {
// 验证新表数据量是否正确
Integer newCount = jdbcTemplate.queryForObject(
"SELECT COUNT(*) FROM " + newTable, Integer.class);
Integer expectedCount = jdbcTemplate.queryForObject(
"SELECT COUNT(*) FROM " + sourceTable + " WHERE " + condition, Integer.class);
return newCount.equals(expectedCount);
}
private void switchTables(String sourceTable, String newTable, String oldTable) {
// 原子性的表重命名操作
String sql = String.format(
"RENAME TABLE %s TO %s, %s TO %s",
sourceTable, oldTable, newTable, sourceTable
);
jdbcTemplate.execute(sql);
}
private void dropTableSafely(String tableName) {
try {
jdbcTemplate.execute("DROP TABLE " + tableName);
} catch (Exception e) {
log.warn("删除表失败: {}, 需要手动清理", tableName, e);
}
}
private void cleanupTempTable(String tableName) {
try {
jdbcTemplate.execute("DROP TABLE IF EXISTS " + tableName);
} catch (Exception e) {
log.warn("清理临时表失败: {}", tableName, e);
}
}
}

复制代码

适用场景

需要删除表中超过50%的数据
业务允许短暂的写停顿（重命名时需要）
有足够的磁盘空间存储新旧两个表

四、方案三：分区表删除

如果表已经做了分区，或者可以改造为分区表，那么删除数据就会变得非常简单。
实现原理

具体实现

方法1：使用现有分区表

-- 查看表的分区情况
SELECT table_name, partition_name, table_rows
FROM information_schema.partitions
WHERE table_name = 'user_operation_log';
-- 直接删除整个分区（秒级完成）
ALTER TABLE user_operation_log DROP PARTITION p202201, p202202;
-- 定期删除过期分区的存储过程
DELIMITER $$
CREATE PROCEDURE auto_drop_expired_partitions()
BEGIN
DECLARE expired_partition VARCHAR(64);
DECLARE done INT DEFAULT FALSE;
-- 查找需要删除的分区（保留最近12个月）
DECLARE cur CURSOR FOR
SELECT partition_name
FROM information_schema.partitions
WHERE table_name = 'user_operation_log'
AND partition_name LIKE 'p%'
AND STR_TO_DATE(REPLACE(partition_name, 'p', ''), '%Y%m') < DATE_SUB(NOW(), INTERVAL 12 MONTH);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
read_loop: LOOP
FETCH cur INTO expired_partition;
IF done THEN
LEAVE read_loop;
END IF;
-- 删除过期分区
SET @sql = CONCAT('ALTER TABLE user_operation_log DROP PARTITION ', expired_partition);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- 记录日志
INSERT INTO partition_clean_log
VALUES (NOW(), expired_partition, 'DROPPED');
END LOOP;
CLOSE cur;
END$$
DELIMITER ;

复制代码

方法2：改造普通表为分区表

-- 将普通表改造成分区表
-- 步骤1: 创建分区表
CREATE TABLE user_operation_log_partitioned (
id BIGINT AUTO_INCREMENT,
user_id BIGINT,
operation VARCHAR(100),
create_time DATETIME,
PRIMARY KEY (id, create_time) -- 分区键必须包含在主键中
) PARTITION BY RANGE (YEAR(create_time)*100 + MONTH(create_time)) (
PARTITION p202201 VALUES LESS THAN (202202),
PARTITION p202202 VALUES LESS THAN (202203),
PARTITION p202203 VALUES LESS THAN (202204),
PARTITION p202204 VALUES LESS THAN (202205),
PARTITION pfuture VALUES LESS THAN MAXVALUE
);
-- 步骤2: 导入数据
INSERT INTO user_operation_log_partitioned
SELECT * FROM user_operation_log;
-- 步骤3: 切换表
RENAME TABLE
user_operation_log TO user_operation_log_old,
user_operation_log_partitioned TO user_operation_log;
-- 步骤4: 定期维护：添加新分区
ALTER TABLE user_operation_log REORGANIZE PARTITION pfuture INTO (
PARTITION p202205 VALUES LESS THAN (202206),
PARTITION p202206 VALUES LESS THAN (202207),
PARTITION pfuture VALUES LESS THAN MAXVALUE
);

复制代码

Java代码实现分区管理

@Service
@Slf4j
public class PartitionManagerService {
@Autowired
private JdbcTemplate jdbcTemplate;
/**
* 自动管理分区
*/
@Scheduled(cron = "0 0 2 * * ?") // 每天凌晨2点执行
public void autoManagePartitions() {
log.info("开始分区维护任务");
try {
// 1. 删除过期分区（保留最近12个月）
dropExpiredPartitions();
// 2. 创建未来分区
createFuturePartitions();
log.info("分区维护任务完成");
} catch (Exception e) {
log.error("分区维护任务失败", e);
}
}
private void dropExpiredPartitions() {
String sql = "SELECT partition_name " +
"FROM information_schema.partitions " +
"WHERE table_name = 'user_operation_log' " +
"AND partition_name LIKE 'p%' " +
"AND STR_TO_DATE(REPLACE(partition_name, 'p', ''), '%Y%m') < DATE_SUB(NOW(), INTERVAL 12 MONTH)";
List<String> expiredPartitions = jdbcTemplate.queryForList(sql, String.class);
for (String partition : expiredPartitions) {
try {
jdbcTemplate.execute("ALTER TABLE user_operation_log DROP PARTITION " + partition);
log.info("成功删除分区: {}", partition);
// 记录操作日志
logPartitionOperation("DROP", partition, "SUCCESS");
} catch (Exception e) {
log.error("删除分区失败: {}", partition, e);
logPartitionOperation("DROP", partition, "FAILED: " + e.getMessage());
}
}
}
private void createFuturePartitions() {
// 创建未来3个月的分区
for (int i = 1; i <= 3; i++) {
LocalDate futureDate = LocalDate.now().plusMonths(i);
String partitionName = "p" + futureDate.format(DateTimeFormatter.ofPattern("yyyyMM"));
int partitionValue = futureDate.getYear() * 100 + futureDate.getMonthValue();
int nextPartitionValue = partitionValue + 1;
try {
String sql = String.format(
"ALTER TABLE user_operation_log REORGANIZE PARTITION pfuture INTO (" +
"PARTITION %s VALUES LESS THAN (%d), " +
"PARTITION pfuture VALUES LESS THAN MAXVALUE)",
partitionName, nextPartitionValue
);
jdbcTemplate.execute(sql);
log.info("成功创建分区: {}", partitionName);
logPartitionOperation("CREATE", partitionName, "SUCCESS");
} catch (Exception e) {
log.warn("创建分区失败（可能已存在）: {}", partitionName, e);
}
}
}
private void logPartitionOperation(String operation, String partition, String status) {
jdbcTemplate.update(
"INSERT INTO partition_operation_log(operation, partition_name, status, create_time) VALUES (?, ?, ?, NOW())",
operation, partition, status
);
}
}

复制代码

七、方案对比与选择指南

为了帮助大家选择合适的方案，我整理了详细的对比表：
方案对比矩阵

方案适用场景优点缺点风险等级分批删除小批量删除，
删除比例50%，
可接受短暂停写执行速度快，
整理表碎片需要停写，
需要额外空间高分区删除表已分区或可分区秒级完成，
无性能影响需要前期规划，
改造成本低在线同步要求零停机，
重要业务表业务无感知，
安全可靠实现复杂，
周期较长中专业工具复杂场景，
超大表操作功能强大，
自动优化学习成本，
依赖外部工具中选择决策流程图

实战建议

测试环境验证：任何删除方案都要先在测试环境验证
备份优先：删除前一定要备份数据
业务低峰期：选择业务低峰期执行删除操作
监控告警：实时监控数据库状态，设置告警阈值
回滚预案：准备完善的回滚方案

更多项目实战：susan.net.cn/project
总结

经过上面的详细分析，我们来总结一下千万级大表数据删除的核心要点。
核心原则

安全第一：任何删除操作都要确保数据安全
影响最小：尽量减少对业务的影响
效率优先：选择最适合的高效方案
可监控：整个过程要可监控、可控制

技术选型口诀

根据多年的实战经验，我总结了一个简单的选型口诀：

看分区，判比例，定方案

有分区：直接删除分区最快

删的少：分批删除最稳妥

删的多：重建表最高效

不能停：在线同步最安全

最后的建议

大表数据删除是一个需要谨慎对待的操作，我建议大家：

预防优于治疗：通过数据生命周期管理，定期清理数据
架构要合理：在设计阶段就考虑数据清理策略
工具要熟练：掌握各种删除工具的使用方法
经验要积累：每次操作后都要总结经验教训

记住：没有最好的方案，只有最适合的方案。

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

赙浦 · 昨天 02:13

不错，里面软件多更新就更好了

账号		自动登录	找回密码
密码			立即注册

千万级大表如何删除数据？

相关帖子

回复

浏览过的版块

签约作者

千万级大表如何删除数据？

相关帖子

相关推荐

回复

浏览过的版块

签约作者