// 慢：逐條 INSERT（20萬(wàn)次網(wǎng)絡(luò)IO）
for (Entity e : list) {
    mapper.insert(e);  // 每條一次網(wǎng)絡(luò)往返
}

// 快：批量 INSERT（100次網(wǎng)絡(luò)IO）
for (int i = 0; i < list.size(); i += 2000) {
    int end = Math.min(i + 2000, list.size());
    mapper.batchInsert(list.subList(i, end));
}

<insert id="batchInsert">
  INSERT INTO my_table (col1, col2, col3) VALUES
  <foreach collection="list" item="item" separator=",">
    (#{item.col1}, #{item.col2}, #{item.col3})
  </foreach>
</insert>

int pageSize = 2000;
int page = 1;
while (true) {
    List<Detail> batch = mapper.selectPage(taskId, page, pageSize);
    if (batch.isEmpty()) break;

    // 校驗(yàn)或入庫(kù)
    processBatch(batch);

    // 回寫(xiě)狀態(tài)
    mapper.batchUpdateStatus(batch);
    page++;
}

public void importData(List<RawData> allData) {
    int batchSize = 2000;
    int total = allData.size();

    for (int i = 0; i < total; i += batchSize) {
        int end = Math.min(i + batchSize, total);
        List<RawData> batch = allData.subList(i, end);

        // 1. 校驗(yàn)
        List<Entity> validList = new ArrayList<>();
        for (RawData raw : batch) {
            if (validate(raw)) {
                validList.add(convert(raw));
            }
        }

        // 2. 批量入庫(kù)
        if (!validList.isEmpty()) {
            mapper.batchInsert(validList);
        }
    }
}

SXSSFWorkbook workbook = new SXSSFWorkbook(200); // 窗口200行
Sheet sheet = workbook.createSheet("數(shù)據(jù)");

int pageSize = 5000;
int page = 1;
int rowIndex = 1;

while (true) {
    List<Entity> batch = mapper.selectPage(page, pageSize);
    if (batch.isEmpty()) break;

    for (Entity e : batch) {
        Row row = sheet.createRow(rowIndex++);
        row.createCell(0).setCellValue(e.getName());
        row.createCell(1).setCellValue(e.getAmount());
    }
    page++;
}

workbook.write(outputStream);
workbook.dispose(); // 清理臨時(shí)文件

ExcelWriter writer = EasyExcelFactory.write(outputStream, ExportRow.class).build();
WriteSheet sheet = EasyExcelFactory.writerSheet("數(shù)據(jù)").build();

int page = 0;
while (true) {
    List<ExportRow> batch = queryData(param, page, 5000);
    if (batch.isEmpty()) break;
    writer.write(batch, sheet);
    page++;
}
writer.finish();

@Service
public class BatchProcessService {

  @Resource
  private DataMapper dataMapper;

  private static final int BATCH_SIZE = 2000;

  /** 批量導(dǎo)入. */
  public void batchImport(List<ImportRow> rawData) {
    List<DataEntity> buffer = new ArrayList<>(BATCH_SIZE);

    for (ImportRow raw : rawData) {
      if (!validate(raw)) continue;
      buffer.add(convert(raw));

      if (buffer.size() >= BATCH_SIZE) {
        dataMapper.batchInsert(buffer);
        buffer.clear();
      }
    }
    // 處理剩余
    if (!buffer.isEmpty()) {
      dataMapper.batchInsert(buffer);
    }
  }

  /** 流式導(dǎo)出. */
  public void streamExport(OutputStream out, QueryParam param) {
    SXSSFWorkbook wb = new SXSSFWorkbook(200);
    try {
      Sheet sheet = wb.createSheet("導(dǎo)出數(shù)據(jù)");
      writeHeader(sheet);

      int page = 1, rowIdx = 1;
      while (true) {
        List<DataEntity> batch = dataMapper.selectByPage(param, page, 5000);
        if (batch.isEmpty()) break;
        for (DataEntity e : batch) {
          writeRow(sheet, rowIdx++, e);
        }
        page++;
      }
      wb.write(out);
    } finally {
      wb.dispose();
    }
  }
}

# 數(shù)據(jù)庫(kù)連接池（批處理需要足夠連接）
spring:
  datasource:
    hikari:
      maximum-pool-size: 20      # 并發(fā)批處理時(shí)需要足夠連接
      connection-timeout: 30000
# MySQL批量操作需要的URL參數(shù)
      url: jdbc:mysql://host:3306/db?rewriteBatchedStatements=true
      #                               ↑ 關(guān)鍵！開(kāi)啟批量重寫(xiě)，性能提升5~10倍

// 慢：逐條UPDATE
for (Detail d : list) {
    mapper.updateStatus(d.getId(), d.getStatus());  // 20萬(wàn)次
}

// 快：CASE WHEN 批量UPDATE
mapper.batchUpdateStatus(list);  // 按批次執(zhí)行

<update id="batchUpdateStatus">
  UPDATE exchange_task_detail
  SET status = CASE id
  <foreach collection="list" item="item">
    WHEN #{item.id} THEN #{item.status}
  </foreach>
  END,
  data = CASE id
  <foreach collection="list" item="item">
    WHEN #{item.id} THEN #{item.data}
  </foreach>
  END
  WHERE id IN
  <foreach collection="list" item="item" open="(" separator="," close=")">
    #{item.id}
  </foreach>
</update>

-- 1. 創(chuàng)建臨時(shí)表并插入數(shù)據(jù)
CREATE TEMPORARY TABLE tmp_update (id INT, status INT);
INSERT INTO tmp_update VALUES (1, 3), (2, 3), ...;
-- 2. JOIN UPDATE
UPDATE exchange_task_detail d
JOIN tmp_update t ON d.id = t.id
SET d.status = t.status;

public void parallelImport(List<Entity> allData) {
    int batchSize = 2000;
    ExecutorService executor = Executors.newFixedThreadPool(4);
    List<Future<?>> futures = new ArrayList<>();

    for (int i = 0; i < allData.size(); i += batchSize) {
        int start = i;
        int end = Math.min(i + batchSize, allData.size());
        List<Entity> batch = allData.subList(start, end);

        futures.add(executor.submit(() -> {
            mapper.batchInsert(batch);
        }));
    }

    // 等待全部完成
    for (Future<?> f : futures) {
        f.get();
    }
    executor.shutdown();
}

// 錯(cuò)誤：20萬(wàn)條全加載到內(nèi)存
List<Entity> all = mapper.selectAll();  // ~200MB

// 正確：分頁(yè)加載，用完即棄
int page = 1;
while (true) {
    List<Entity> batch = mapper.selectPage(page, 5000);  // ~5MB
    if (batch.isEmpty()) break;
    process(batch);
    page++;
    // batch 在下一次循環(huán)時(shí)可被GC
}

// EasyExcel 流式讀取，不會(huì)把20萬(wàn)行全加載到內(nèi)存
EasyExcelFactory.read(inputStream, RowData.class,
    new PageReadListener<RowData>(batch -> {
        // 每100行回調(diào)一次，處理后釋放
        processBatch(batch);
    }, 100))
    .sheet()
    .doRead();