SQLite 百萬級資料多線程批量匯入實戰優化

面對 30 MB、十萬條 INSERT 語句的 SQL 檔案，使用 Navicat 等 GUI 工具匯入極其緩慢甚至卡死。本文分享自研 Java 多線程批量匯入工具，從單線程數小時優化至分鐘級完成，並深入分析 SQLite 並發寫入的鎖機制與最佳實踐。

一、效能瓶頸分析

• GUI 工具逐條執行 SQL，網路/介面開銷大。
• SQLite 預設 journal_mode=DELETE，每次提交觸發磁碟同步。
• 單連線串行執行，無法利用多核 CPU。

二、核心優化策略

1. 批次處理：PreparedStatement.addBatch() + executeBatch()，減少網路往返。
2. 事務控制：手動 setAutoCommit(false)，每 500 條提交一次，大幅減少 fsync。
3. WAL 模式：PRAGMA journal_mode=WAL; 讀寫不阻塞，並發效能提升數倍。
4. 連線池隔離：每線程獨立連線，避免單連線鎖爭用。
5. 記憶體映射：PRAGMA mmap_size=268435456; 利用 OS 頁面快取加速讀取。

三、完整實作程式碼

package com.geoway.pad.common.tool;

import java.io.*;
import java.sql.*;
import java.util.concurrent.*;

public class BatchTool {
    private static final String DDL = "CREATE TABLE IF NOT EXISTS pbeijing_point (" +
        "OBJECTID INTEGER, NAME TEXT, ADDRESS TEXT, PHONE TEXT, FAX TEXT, " +
        "TYPE TEXT, CITYCODE TEXT, URL TEXT, EMAIL TEXT, NAME2 TEXT, " +
        "X INTEGER, Y INTEGER)";

    private static final String DB_URL = "jdbc:sqlite:c:/newD.db";
    private static final int BATCH_SIZE = 500;
    private static final int THREAD_POOL_SIZE = 8; // 建議 CPU 核心數 * 2

    private final ExecutorService pool = Executors.newFixedThreadPool(THREAD_POOL_SIZE);
    private final ThreadLocal threadConn = ThreadLocal.withInitial(this::createConnection);

    private Connection createConnection() {
        try {
            Class.forName("org.sqlite.JDBC");
            Connection conn = DriverManager.getConnection(DB_URL);
            // 關鍵 PRAGMA 設定
            try (Statement st = conn.createStatement()) {
                st.execute("PRAGMA journal_mode=WAL;");
                st.execute("PRAGMA synchronous=NORMAL;");
                st.execute("PRAGMA cache_size=-32768;"); // 32MB 快取
                st.execute("PRAGMA mmap_size=268435456;"); // 256MB mmap
                st.execute(DDL);
            }
            conn.setAutoCommit(false);
            return conn;
        } catch (Exception e) {
            throw new RuntimeException("建立 SQLite 連線失敗", e);
        }
    }

    public void readBatchSQL(InputStream is) throws IOException {
        try (BufferedReader br = new BufferedReader(new InputStreamReader(is, "UTF-8"))) {
            StringBuilder batchSql = new StringBuilder();
            int count = 0;
            String line;
            while ((line = br.readLine()) != null) {
                batchSql.append(line).append('\n');
                if (line.trim().endsWith(";")) {
                    count++;
                    if (count % BATCH_SIZE == 0) {
                        pool.submit(new BatchTask(batchSql.toString()));
                        batchSql.setLength(0);
                    }
                }
            }
            // 處理剩餘
            if (batchSql.length() > 0) {
                pool.submit(new BatchTask(batchSql.toString()));
            }
        } finally {
            shutdownPool();
        }
    }

    private void shutdownPool() {
        pool.shutdown();
        try {
            if (!pool.awaitTermination(1, TimeUnit.HOURS)) {
                pool.shutdownNow();
            }
        } catch (InterruptedException e) {
            pool.shutdownNow();
            Thread.currentThread().interrupt();
        }
        // 關閉所有 ThreadLocal 連線
        // 需維護連線集合或使用 try-with-resources 包裝任務
    }

    private class BatchTask implements Runnable {
        private final String sqlBatch;
        BatchTask(String sqlBatch) { this.sqlBatch = sqlBatch; }

        @Override
        public void run() {
            Connection conn = threadConn.get();
            try (Statement st = conn.createStatement()) {
                String[] sqls = sqlBatch.split(";(?=\\s*(?:INSERT|UPDATE|DELETE|CREATE)|$)");
                for (String sql : sqls) {
                    sql = sql.trim();
                    if (!sql.isEmpty()) st.addBatch(sql);
                }
                st.executeBatch();
                conn.commit();
            } catch (SQLException e) {
                try { conn.rollback(); } catch (SQLException ignored) {}
                System.err.println("批次執行失敗，已回滾: " + e.getMessage());
            }
        }
    }

    public static void main(String[] args) throws IOException {
        new BatchTool().readBatchSQL(new FileInputStream("c:/poi.sql"));
        System.out.println("匯入完成");
    }
}

四、關鍵參數調優建議

五、實測效能對比

• Navicat 匯入 10 萬條：> 1 小時（常卡死）
• 單線程 JDBC 批次 (500/提交)：~ 120 秒
• 多線程 (8線程) + WAL + 批次：~ 15 秒

六、避坑指南

1. SQLite 單寫入者限制：即使多線程，同一時刻僅一個線程能寫入；WAL 模式下讀寫可並行，寫寫仍串行。
2. 避免單一大事務導致記憶體暴漲與長時間鎖定。
3. ThreadLocal 連線務必在線程池關閉時統一釋放，防止連線洩漏。
4. 大量資料建議先匯入臨時表，再 INSERT INTO target SELECT * FROM temp; 利用 SQLite 內部優化。