H2预建component_schedule表如何通过循环插入数百万行测试数据？

针对需要生成大规模测试数据、避免低效逐行循环插入的需求，可根据实际使用场景选择以下落地实现，单表100万行数据插入耗时可控制在10秒以内：

方案1：数据库端直接批量生成（最优方案） #

H2 内置支持通过系统表SYSTEM_RANGE生成连续数字序列，无需依赖外部程序、无网络传输开销，是插入速度最快的实现方式：

• 操作前先关闭自动提交，避免逐行刷盘损耗性能
• 基于序列值直接拼接随机业务字段，一次性完成批量插入

-- 关闭自动提交
SET AUTOCOMMIT OFF;

-- 生成100万行测试数据（修改SYSTEM_RANGE的终止值即可调整插入总量，如5000000对应500万行）
INSERT INTO component_schedule (
    component_id,
    component_schedule_start_time,
    component_schedule_end_time,
    component_available_then
)
SELECT
    -- 模拟100个组件的排班数据
    CAST(RAND() * 100 + 1 AS BIGINT) AS component_id,
    -- 随机生成2024年全年范围内的排班起始时间
    DATEADD('MINUTE', CAST(RAND() * 525600 AS INT), TIMESTAMP '2024-01-01 00:00:00') AS schedule_start,
    -- 为每个排班随机设置15-120分钟的持续时长，保证结束时间晚于开始时间
    DATEADD(
        'MINUTE',
        CAST(15 + RAND() * 105 AS INT),
        DATEADD('MINUTE', CAST(RAND() * 525600 AS INT), TIMESTAMP '2024-01-01 00:00:00')
    ) AS schedule_end,
    -- 模拟70%的组件为可用状态
    RAND() > 0.3 AS is_available
FROM SYSTEM_RANGE(1, 1000000);

-- 一次性提交所有修改
COMMIT;

注意：如果需要插入千万级以上数据，可将上述INSERT语句拆分为多次执行，每次插入100万行后提交一次，避免单事务内存占用过高。

方案2：JDBC应用层批量插入（适合集成到初始化程序的场景） #

如果需要将数据生成逻辑嵌入应用代码，不要使用逐行execute()的循环写法，需基于预编译语句的批量提交能力实现，核心优化点包括：

• JDBC连接URL开启REWRITE_BATCHED_STATEMENTS参数，让H2自动合并多条INSERT语句减少交互开销
• 关闭自动提交，每攒够固定行数的批次再统一提交
• 单批次大小设置为5000-10000行，避免内存占用过高
  核心实现代码：

// H2连接配置，开启批量重写、设置较长锁超时
String h2Url = "jdbc:h2:./perf_test_db;REWRITE_BATCHED_STATEMENTS=true;LOCK_TIMEOUT=30000";
try (Connection conn = DriverManager.getConnection(h2Url, "sa", "")) {
    conn.setAutoCommit(false);
    String insertSql = "INSERT INTO component_schedule (component_id, component_schedule_start_time, component_schedule_end_time, component_available_then) VALUES (?, ?, ?, ?)";
    try (PreparedStatement pstmt = conn.prepareStatement(insertSql)) {
        final int BATCH_SIZE = 5000;
        final long TOTAL_ROWS = 1_000_000;
        long baseTime = Timestamp.valueOf("2024-01-01 00:00:00").getTime();

        for (long i = 0; i < TOTAL_ROWS; i++) {
            // 生成随机业务值
            long componentId = (long)(Math.random() * 100 + 1);
            long startOffset = (long)(Math.random() * 525600 * 60 * 1000);
            long durationOffset = (long)((15 + Math.random() * 105) * 60 * 1000);
            Timestamp startTime = new Timestamp(baseTime + startOffset);
            Timestamp endTime = new Timestamp(startTime.getTime() + durationOffset);
            boolean available = Math.random() > 0.3;

            pstmt.setLong(1, componentId);
            pstmt.setTimestamp(2, startTime);
            pstmt.setTimestamp(3, endTime);
            pstmt.setBoolean(4, available);
            pstmt.addBatch();

            // 攒够一批就提交
            if ((i + 1) % BATCH_SIZE == 0) {
                pstmt.executeBatch();
                conn.commit();
            }
        }
        // 处理最后不足一个批次的剩余数据
        pstmt.executeBatch();
        conn.commit();
    }
}

性能优化注意事项 #

• 禁止在循环内逐行提交事务，单事务一次提交比逐行提交的速度高数十倍
• 数据插入完成后执行ANALYZE component_schedule更新表统计信息，避免查询优化器生成错误执行计划，影响性能测试结果
• 高压力插入阶段可在H2连接URL中添加SYNC_DELAY=10000参数，将磁盘刷盘延迟调整为10秒，插入完成后再改回默认配置即可

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