基于JPA在MySQL中批量删除大量数据的最佳实践

数据老陈

2026-5-28

MySQL批量删除50万+行数据的最佳实践（不修改配置）

针对你要删除50万+行数据且不能调整MySQL服务器配置的场景，我整理了几个经过生产环境验证的方案，既能规避你遇到的range_optimizer_max_mem_size和max_allowed_packet限制，又能尽量提升删除效率、降低对数据库的影响：

1. 小批次分块+原生SQL范围/批量删除（最推荐）

JPA的deleteInBatch或循环单条删除会生成成百上千条DELETE语句，效率极低；而大批次IN子句又会触发配置阈值。最优的方式是用原生SQL分小批次删除，每次处理固定行数（比如1000条），具体分两种情况：

若有连续的主键/范围条件（比如按ID分段）

如果你的删除条件可以转化为范围查询（比如删除某个时间范围内的数据，或ID连续的一批数据），直接用LIMIT分批执行，这种方式不会触发range optimizer内存限制，且SQL体积极小：

-- 循环执行此语句，直到返回影响行数为0
DELETE FROM event 
WHERE created_at < '2023-01-01' -- 你的过滤条件
LIMIT 1000;

每次删除1000条，执行完检查影响行数，直到没有数据可删为止。这种方式的优势是：SQL简单、不会超过max_allowed_packet、利用索引（如果过滤条件有索引的话）效率极高。

若只能通过非连续ID删除

如果必须通过ID列表删除，把批次大小调小到安全值（比如500条以内，可根据实际测试调整），用IN子句分批执行：

// 先分页查询要删除的ID列表（避免一次性加载50万条到内存）
int pageSize = 500;
int pageNum = 0;
while (true) {
    List<Long> ids = eventRepository.findIdsToDelete(pageNum, pageSize); // 自定义分页查询ID的方法
    if (ids.isEmpty()) break;
    // 执行原生SQL批量删除
    entityManager.createNativeQuery("DELETE FROM event WHERE id IN (:ids)")
                 .setParameter("ids", ids)
                 .executeUpdate();
    pageNum++;
}

这里的关键是分页查询ID，不要一次性把50万条ID加载到内存，同时控制每个批次的ID数量在500以内，避免触发max_allowed_packet和range_optimizer_max_mem_size的限制。

2. 临时表辅助删除（适合复杂过滤条件）

如果你的删除条件非常复杂（比如多表关联过滤），直接写DELETE语句效率低且容易触发配置限制，可以用临时表中转：

创建临时表存储要删除的ID：

CREATE TEMPORARY TABLE temp_delete_ids (id BIGINT PRIMARY KEY);

临时表是会话级别的，不会影响其他业务，且查询效率极高。

批量插入要删除的ID到临时表：

INSERT INTO temp_delete_ids 
SELECT id FROM event 
JOIN other_table ot ON event.ot_id = ot.id 
WHERE ot.status = 'invalid'; -- 你的复杂过滤条件

如果插入的ID数量还是很大，可以分批次插入（比如加LIMIT循环执行）。

分批次从临时表取ID删除：

while (true) {
    // 每次从临时表取1000条ID并删除
    int affected = entityManager.createNativeQuery("""
        DELETE e FROM event e
        JOIN temp_delete_ids t ON e.id = t.id
        LIMIT 1000
        """).executeUpdate();
    if (affected == 0) break;
}
// 最后删除临时表（可选，会话结束会自动销毁）
entityManager.createNativeQuery("DROP TABLE temp_delete_ids").executeUpdate();

这种方式把复杂过滤和删除操作分离，临时表的查询不会触发内存限制，且每次删除的SQL体积很小。

3. 避免加载实体到内存（减少应用端压力）

你之前的代码可能是先把所有要删除的实体查询到应用内存再处理，这不仅占用大量JVM内存，还会因为ORM的对象映射开销拖慢速度。直接在数据库层面执行删除操作，不要把实体拉到应用端，这是提升效率的核心原则之一。

比如不要用：

List<Event> events = eventRepository.findAllByXXX(xxx);
eventRepository.deleteInBatch(events); // 低效且耗内存

而是直接用原生SQL或JPQL的批量删除：

int deletedCount = entityManager.createQuery("DELETE FROM Event e WHERE e.createdAt < :date")
                               .setParameter("date", LocalDate.of(2023, 1, 1))
                               .executeUpdate();

如果这条语句因为数据量太大触发配置限制，就拆成小批次执行（参考方案1）。

额外注意事项

避开业务高峰：删除操作会锁表（InnoDB是行锁，但如果过滤条件无索引会升级为表锁），尽量在低峰期执行。
确保过滤条件有索引：如果删除的过滤条件（比如created_at、id）没有索引，会触发全表扫描，不仅慢还会占用大量数据库资源。
小批次提交：不要把所有删除操作放在一个大事务里，每次小批次提交可以让InnoDB及时清理undo日志，避免磁盘占用过高。
监控数据库状态：执行过程中关注数据库的CPU、磁盘IO、连接数，避免影响正常业务。

内容的提问来源于stack exchange，提问作者Niko

基于JPA在MySQL中批量删除大量数据的最佳实践

1. 小批次分块+原生SQL范围/批量删除（最推荐） #

若有连续的主键/范围条件（比如按ID分段） #

若只能通过非连续ID删除 #

2. 临时表辅助删除（适合复杂过滤条件） #

3. 避免加载实体到内存（减少应用端压力） #