SQL中通过SELECT插入2500万数据超时失败，求分批插入方案

完全可以通过分批插入的方式解决这个问题，核心思路是将原SELECT语句的结果作为数据源，通过分页逻辑拆分出小批次数据逐一插入，直到所有数据处理完成。以下是主流数据库的具体实现方案：

MySQL/MariaDB 实现 #

• 利用LIMIT配合循环变量控制分批，每次插入指定数量的数据
• 示例代码（替换你的原查询和表字段即可）：

SET @offset = 0;
SET @batch_size = 100000; -- 可根据服务器性能调整，建议从10万开始测试

REPEAT
  INSERT INTO target_table (col1, col2, col3)
  SELECT col1, col2, col3
  FROM (
    -- 直接粘贴你的原SELECT语句，无需修改
    SELECT original_col1 AS col1, original_col2 AS col2, original_col3 AS col3
    FROM source_table
    -- ... 原查询的WHERE、JOIN等逻辑
  ) AS sub_query
  LIMIT @offset, @batch_size;

  SET @offset = @offset + @batch_size;
UNTIL ROW_COUNT() = 0 END REPEAT;

• 关键说明：ROW_COUNT()返回上一次INSERT的影响行数，当返回0时表示没有剩余数据，自动终止循环。

SQL Server 实现 #

• 借助OFFSET ... FETCH NEXT分页语法，结合WHILE循环实现分批
• 示例代码：

DECLARE @offset INT = 0;
DECLARE @batch_size INT = 100000;

WHILE 1 = 1
BEGIN
  INSERT INTO target_table (col1, col2, col3)
  SELECT col1, col2, col3
  FROM (
    -- 直接放入你的原SELECT语句
    SELECT original_col1 AS col1, original_col2 AS col2, original_col3 AS col3
    FROM source_table
    -- ... 原查询的其他逻辑
  ) AS sub_query
  ORDER BY unique_column -- 必须指定唯一排序字段，避免数据重复/遗漏
  OFFSET @offset ROWS FETCH NEXT @batch_size ROWS ONLY;

  IF @@ROWCOUNT = 0 BREAK;
  SET @offset = @offset + @batch_size;
END

• 关键说明：排序字段建议使用主键或唯一索引列，确保分页逻辑稳定可靠。

Oracle 实现 #

兼容旧版本（11g及以下） #

DECLARE
  v_offset NUMBER := 0;
  v_batch_size NUMBER := 100000;
  v_count NUMBER;
BEGIN
  LOOP
    INSERT INTO target_table (col1, col2, col3)
    SELECT col1, col2, col3
    FROM (
      SELECT original_col1 AS col1, original_col2 AS col2, original_col3 AS col3,
             ROWNUM AS rn
      FROM (
        -- 原SELECT语句必须包含排序，保证数据顺序稳定
        SELECT original_col1, original_col2, original_col3
        FROM source_table
        -- ... 原查询逻辑
        ORDER BY unique_column
      )
      WHERE ROWNUM <= v_offset + v_batch_size
    )
    WHERE rn > v_offset;

    v_count := SQL%ROWCOUNT;
    EXIT WHEN v_count = 0;
    v_offset := v_offset + v_batch_size;
  END LOOP;
  COMMIT;
END;
/

12c+版本（支持OFFSET语法） #

DECLARE
  v_offset NUMBER := 0;
  v_batch_size NUMBER := 100000;
  v_count NUMBER;
BEGIN
  LOOP
    INSERT INTO target_table (col1, col2, col3)
    SELECT col1, col2, col3
    FROM (
      -- 直接放入你的原SELECT语句
      SELECT original_col1 AS col1, original_col2 AS col2, original_col3 AS col3
      FROM source_table
      -- ... 原查询逻辑
    )
    ORDER BY unique_column
    OFFSET v_offset ROWS FETCH NEXT v_batch_size ROWS ONLY;

    v_count := SQL%ROWCOUNT;
    EXIT WHEN v_count = 0;
    v_offset := v_offset + v_batch_size;
  END LOOP;
  COMMIT;
END;
/

通用优化建议 #

• 调整批次大小：根据服务器硬件配置测试最优值，太小会增加循环开销，太大仍可能导致超时，建议在5万-20万之间测试。
• 临时禁用索引/约束：插入前禁用目标表的非必要索引、外键和触发器，插入完成后重新启用，能显著提升插入速度。
• 事务控制：每批次插入后单独提交，避免大事务占用过多日志空间；若需保证数据原子性，可最后统一提交，但需注意日志容量。
• 监控资源：执行过程中关注CPU、内存、磁盘IO和日志使用率，避免资源耗尽导致失败。

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