基于Spring JDBC的Oracle大数据量批量插入优化咨询

1. 改用JDBC批量插入（替代单条INSERT） #

• 放弃执行单条INSERT INTO table(col1,...) VALUES(val1,...)，改用批量提交逻辑，减少数据库网络往返和事务开销：
  • 在Spring中利用JdbcTemplate的batchUpdate方法，将多条记录打包为一个批次提交，建议批次大小设为500-1000条（可根据实际情况调整）。
  • 核心代码示例：

    String sql = "INSERT INTO your_table(col1, col2, ..., col45) VALUES(?, ?, ..., ?)";
    jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
        @Override
        public void setValues(PreparedStatement ps, int i) throws SQLException {
            YourData data = dataList.get(i);
            ps.setString(1, data.getCol1());
            // 依次设置45列对应的值
        }
        @Override
        public int getBatchSize() {
            return dataList.size();
        }
    });
    

2. 手动控制事务，关闭自动提交 #

• 默认自动提交模式下，每条INSERT都会触发一次事务提交，开销极大。
• 用@Transactional注解包裹批量插入逻辑，或手动获取Connection设置setAutoCommit(false)，待所有批次插入完成后再执行commit()，大幅减少事务提交次数。

3. 使用Oracle官方SQL*Loader工具 #

• 这是Oracle专为大批量数据导入优化的工具，绕过SQL解析层直接写入数据文件，效率远高于JDBC插入：
  1. 将数据整理为CSV（或自定义分隔符）格式，确保列顺序与目标表匹配。
  2. 编写控制文件（.ctl），定义数据文件格式、表字段映射规则。
  3. 执行加载命令：sqlldr username/password@database control=your_control.ctl log=load.log
• 18万条数据通常仅需几十秒到1分钟即可完成。

4. 采用Oracle INSERT ALL批量语法 #

• 若不想依赖外部工具，可使用Oracle原生的批量插入语法，减少SQL解析次数：

  INSERT ALL
    INTO your_table(col1, col2, ...) VALUES(val1, val2, ...)
    INTO your_table(col1, col2, ...) VALUES(val3, val4, ...)
    -- 单次最多支持1000条记录
  SELECT * FROM DUAL;
  

• 配合JDBC批量执行该语句，效率优于单条INSERT。

5. 测试环境临时关闭约束与索引 #

• 测试场景下，插入前可临时关闭目标表的非主键约束（如外键、唯一约束）和非必要索引，插入完成后再重建：
  • 关闭约束：ALTER TABLE your_table DISABLE CONSTRAINT constraint_name;
  • 禁用索引：ALTER INDEX index_name UNUSABLE;
• 避免插入时频繁检查约束、更新索引，能大幅提升插入速度（仅适用于测试环境，生产环境谨慎操作）。

6. 优化多线程策略 #

• 现有多线程可能因线程数过多导致连接池耗尽或锁竞争：
  • 根据数据库连接池最大容量设置合理线程数（如连接池最大20，则线程数设为15-20）。
  • 按表拆分任务，每个线程负责单张表的插入，减少跨表锁竞争。

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