跨Oracle服务器MyBatis批量复制百万数据过慢的优化咨询

一、当前实现的核心问题及修正方案 #

1. 一次性加载全量数据到内存 #

getEmployeeEnt()直接返回100万条记录，会导致：

• 内存占用暴增，引发频繁GC甚至OOM
• 跨网络传输超大列表，耗时极长
  修正方案：
  将查询改为分页模式，例如每次查询1000~5000条，循环处理：

public List<EmployeeEnt> getEmployeeEnt(int pageNum, int pageSize) {
    Map<String, Object> params = new HashMap<>();
    params.put("offset", (pageNum - 1) * pageSize);
    params.put("pageSize", pageSize);
    return employeeSessionMapper.getEmployeeEntByPage(params);
}

对应的Mapper XML使用Oracle分页语法实现：

<select id="getEmployeeEntByPage" parameterType="map" resultType="EmployeeEnt">
    SELECT empId, empName, ... 
    FROM EMPLOYEE_SOURCE
    ORDER BY empId
    OFFSET #{offset} ROWS FETCH NEXT #{pageSize} ROWS ONLY
</select>

2. 并行流+单条插入的错误用法 #

parallel().forEach()结合单条insert存在多个问题：

• MyBatis的BATCH executor是单会话批量优化，并行流会创建多线程，无法利用批量提交优势，反而引发连接竞争、线程切换开销
• 事务是线程绑定的，并行线程会脱离原事务上下文，导致事务失效或异常
  修正方案：
  替换为普通循环+批量插入，利用MyBatis的foreach生成批量SQL，或每攒够N条就手动flush：

@Transactional
public void insertEmployeeEnt(List<EmployeeEnt> employeeEntList) {
    int batchSize = 1000;
    for (int i = 0; i < employeeEntList.size(); i += batchSize) {
        int end = Math.min(i + batchSize, employeeEntList.size());
        List<EmployeeEnt> batch = employeeEntList.subList(i, end);
        employeeSessionMapper.batchInsertEmployeeEnt(batch);
        sqlSessionTemplate.flushStatements();
    }
    sqlSessionTemplate.clearCache();
}

对应的Mapper XML批量插入：

<insert id="batchInsertEmployeeEnt" parameterType="java.util.List">
    INSERT INTO EMPLOYEE_DET(empId, empName, ...)
    VALUES
    <foreach collection="list" item="emp" separator=",">
        (#{emp.empId}, #{emp.empName}, ...)
    </foreach>
</insert>

3. Service层手动实例化DAO的错误 #

new EmployeeDAO()会绕过Spring容器管理，导致：

• 依赖的SqlSessionTemplate、EmployeeMapper无法正确注入
• 事务、连接池等Spring特性完全失效
  修正方案：
  通过Spring注入EmployeeDAO实例：

@Service
public class EmployeeService {
    private final EmployeeDAO employeeDAO;

    // 构造注入
    public EmployeeService(EmployeeDAO employeeDAO) {
        this.employeeDAO = employeeDAO;
    }

    public void copyEmployeeData() {
        employeeDAO.truncateEmployeeEnt();
        int total = employeeDAO.getEmployeeTotalCount(); // 先获取总条数
        int pageSize = 1000;
        int totalPages = (total + pageSize - 1) / pageSize;
        for (int page = 1; page <= totalPages; page++) {
            List<EmployeeEnt> batch = employeeDAO.getEmployeeEnt(page, pageSize);
            employeeDAO.insertEmployeeEnt(batch);
        }
    }
}

4. 其他优化点 #

• 目标表索引优化：插入前禁用非主键索引，插入完成后重建，减少索引维护开销
• Oracle参数调整：在insert语句中添加/*+ APPEND */提示开启DIRECT PATH INSERT，增大目标库的LOG_BUFFER、DB_WRITER_PROCESSES等参数
• 事务粒度调整：每批数据提交一次事务，避免超大事务占用数据库资源

二、CSV+SQL*Loader的可行性分析 #

完全可行，这是跨服务器批量迁移Oracle数据的最优方案之一，性能远高于JDBC批量插入，核心优势：

• SQL*Loader是Oracle原生批量加载工具，支持DIRECT PATH模式，绕过数据库缓冲区直接写入数据文件，大幅降低IO开销
• 支持并行加载、数据校验、错误处理，适配百万级甚至千万级数据迁移

实现步骤 #

1. 分页导出CSV：从源库分页查询数据，逐批写入CSV文件（注意处理特殊字符：如引号、换行符，可将字段用双引号包裹）
2. 编写SQL*Loader控制文件（示例employee.ctl）：

LOAD DATA
INFILE 'employee.csv'
BADFILE 'employee.bad'
DISCARDFILE 'employee.dsc'
APPEND
INTO TABLE EMPLOYEE_DET
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
    empId,
    empName,
    ... -- 对应目标表字段
)

3. Java调用SQL*Loader：通过ProcessBuilder执行sqlldr命令：

public void loadDataWithSqlLoader() throws IOException, InterruptedException {
    ProcessBuilder pb = new ProcessBuilder(
        "sqlldr",
        "userid=target_user/target_password@target_db",
        "control=employee.ctl",
        "log=employee.log",
        "direct=true" // 开启DIRECT PATH模式
    );
    Process process = pb.start();
    // 处理进程输出，避免阻塞
    try (InputStream in = process.getInputStream();
         InputStream err = process.getErrorStream()) {
        byte[] buffer = new byte[1024];
        while (in.read(buffer) != -1) {}
        while (err.read(buffer) != -1) {}
    }
    int exitCode = process.waitFor();
    if (exitCode != 0) {
        throw new RuntimeException("SQL*Loader执行失败，退出码：" + exitCode);
    }
}

注意事项 #

• 源库是生产环境，分页查询可添加/*+ NO_INDEX */提示降低查询优先级，避免影响生产业务
• CSV文件可分批生成，避免单个文件过大
• 确保目标服务器安装了Oracle客户端，且sqlldr命令可被Java进程访问
• 提前备份目标表数据，避免数据丢失

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