Spring Boot JPA执行PostgreSQL批量删除耗时过长问题咨询

核心问题结论 #

单独为id字段添加B树索引可以显著提升当前删除语句的执行效率。
当前使用的四字段联合索引存在两个明显问题：

1. 如果建索引时没有把id放在联合索引的最左列，根据PostgreSQL的最左前缀匹配规则，这个delete语句完全无法命中该索引，会直接走全表扫描，百万级表全表遍历匹配数十万待删记录，耗时必然很高。
2. 就算id是联合索引的最左列，该索引的叶子节点存储了schedule、date、status三个额外字段的值，索引整体体积远大于单字段id索引，数据库扫描索引定位待删行时需要读取更多数据页，IO成本会高出数倍，删除效率依然很差。

具体可落地的优化手段 #

• 补充id字段的单值索引，这是成本最低、见效最快的优化手段，建索引语句参考：

CREATE INDEX CONCURRENTLY idx_rate_id ON rate(id);

提示：生产环境建索引请加CONCURRENTLY参数，避免锁表阻塞正常业务读写。

• 拆分单次删除的数据量，避免长事务锁表。当前单次传入100个id、单个id对应数千条记录，单次删除量可达10万级以上，会长时间占用表锁、堆积WAL日志，同时阻塞同表其他读写请求。建议把待删id拆分，每批控制删除1000_{5000条记录，循环执行，批次间可做100}500ms的短暂停顿，大幅降低数据库瞬时压力。
• 清理表上的冗余索引。PostgreSQL执行删除操作时，每删除一行都需要同步更新该表关联的所有索引，如果表上存在业务不用的多余索引，会成倍放大删除的写入开销，定期清理无用索引可以直接提升删除性能。
• 高频批量删除场景建议改造成分区表。如果后续经常按id批量删除数据，可以将表按id做哈希/范围分区，删除时仅扫描对应分区，数据扫描范围会大幅缩小；如果业务同时存在按date清理历史数据的需求，按date做范围分区后，直接TRUNCATE对应分区的速度比DELETE快2~3个数量级。
• 优化事务粒度。不要将大批量删除操作和其他业务逻辑绑定在同一个长事务中，单独给删除方法设置合理的事务超时时间，避免事务长时间不提交占用数据库连接和锁资源。
• 低峰期大批量删除时可临时关闭外键校验（业务允许前提下）。如果rate表被其他表通过外键关联，删除时数据库会逐行校验外键依赖，确认待删数据无外键关联的话，可临时关闭外键约束校验，删除完成后再重新开启，能省掉大量逐行校验的开销，注意该操作必须在业务低峰期执行，避免产生脏数据。

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