Java 大量資料庫匯出檔案：分頁查詢與流式寫入避免 OOM

大量資料匯出核心挑戰與解決策略

一次性 SELECT * 載入百萬筆資料會導致 JVM Heap 爆炸 (OOM)。解決方案：分頁游標 + 流式寫入，記憶體佔用恆定。

分頁匯出核心模式

public class LargeDataExporter {
    private static final int PAGE_SIZE = 5000; // 根據記憶體調整
    private static final String EXPORT_DIR = "/data/export/";

    public void exportByIdCursor(Connection conn, long startId, String outputFile) throws SQLException, IOException {
        String sql = "SELECT id, title, create_time FROM t_test WHERE id > ? ORDER BY id ASC LIMIT ?";
        try (PreparedStatement ps = conn.prepareStatement(sql);
             BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(
                     new FileOutputStream(outputFile), StandardCharsets.UTF_8))) {

            long lastId = startId;
            int totalExported = 0;
            boolean hasMore = true;

            while (hasMore) {
                ps.setLong(1, lastId);
                ps.setInt(2, PAGE_SIZE);
                try (ResultSet rs = ps.executeQuery()) {
                    hasMore = false;
                    while (rs.next()) {
                        hasMore = true;
                        lastId = rs.getLong("id");
                        String line = String.format("%d\t%d\t%s\t%d%n",
                                ++totalExported, lastId,
                                rs.getString("title"),
                                rs.getLong("create_time"));
                        writer.write(line);
                    }
                }
                // 定期 flush 確保資料落盤
                if (totalExported % 50000 == 0) writer.flush();
            }
            writer.flush();
            System.out.printf("匯出完成：共 %d 筆，最後 ID=%d%n", totalExported, lastId);
        }
    }

    public void exportByTimeWindow(Connection conn, long startTimeMillis, String outputFile) throws SQLException, IOException {
        String sql = "SELECT id, title, create_time FROM t_test WHERE create_time > ? ORDER BY create_time ASC LIMIT ?";
        try (PreparedStatement ps = conn.prepareStatement(sql);
             BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(
                     new FileOutputStream(outputFile), StandardCharsets.UTF_8))) {

            long cursorTime = startTimeMillis;
            int totalExported = 0;
            boolean hasMore = true;

            while (hasMore) {
                ps.setLong(1, cursorTime);
                ps.setInt(2, PAGE_SIZE);
                try (ResultSet rs = ps.executeQuery()) {
                    hasMore = false;
                    while (rs.next()) {
                        hasMore = true;
                        cursorTime = rs.getLong("create_time");
                        String line = String.format("%d\t%d\t%s\t%d%n",
                                ++totalExported,
                                rs.getLong("id"),
                                rs.getString("title"),
                                cursorTime);
                        writer.write(line);
                    }
                }
            }
            writer.flush();
        }
    }
}

關鍵優化細節

1. PreparedStatement + 游標分頁：避免 OFFSET 深分頁效能劣化，利用索引鍵 (id 或 create_time) 定位
2. BufferedWriter + 適當緩衝：減少系統呼叫次數，預設 8KB 緩衝區通常足夠
3. try-with-resources：保證連線、Statement、ResultSet、檔案流正確關閉
4. 編碼明確指定 UTF-8：避免中文亂碼
5. 定期 flush：平衡效能與資料安全

進階：資料庫原生匯出工具 (效能更高)

生產環境檢查清單

• [ ] 針對匯出欄位建立覆蓋索引 (Covering Index)
• [ ] 設定資料庫連線池最大連線數，避免佔滿連線
• [ ] 匯出檔案分割策略 (單檔大小、按日期/業務分目錄)
• [ ] 斷點續傳機制：記錄最後游標位置 (ID/時間) 至檢查點檔案
• [ ] 監控磁碟空間、網路頻寬、資料庫負載
• [ ] 壓縮輸出 (GZIPOutputStream) 節省儲存與傳輸頻寬