如何加快40GB TXT com区域文件解析并逐行插入SQLite数据库的速度

一、批量插入适配调整 #

你的性能瓶颈主要来自两个核心问题：一是SQLite默认单条插入自动开启事务刷盘，IO开销极高；二是正则表达式每次调用都重复编译，浪费CPU资源。调整步骤如下：

1. 核心优化点 #

• 改用显式事务：攒够固定条数（比如1000条）再统一提交一次事务，避免每次单条写入刷盘
• 预编译正则：Regex仅需编译一次即可全局使用，不要放在调用函数内重复初始化
• 可选进阶优化：如果数据量极大，可以动态生成多占位符的批量INSERT语句，进一步降低SQL解析开销

2. 调整后可运行代码 #

extern crate regex;
use regex::Regex;
use rusqlite::{params, Connection, Result, Transaction};
use std::time::Instant;

#[derive(Debug)]
struct Domain {
    id: i32,
    domain: String,
}

// 批量插入阈值，可根据实际测试调整，1000-10000区间性能通常最优
const BATCH_SIZE: usize = 1000;

fn main() -> std::io::Result<()> {
    // 预编译正则，全局仅执行一次
    let domain_re = Regex::new(r"(?m).*?.com").unwrap();

    let mut reader = my_reader::BufReader::open("data/com_practice.txt")?;
    let mut buffer = String::new();
    let mut conn = Connection::open("/Users/alex/Projects/domain_randomizer/data/domains.db").unwrap();

    // 效率统计初始化
    let start_time = Instant::now();
    let mut total_lines = 0;
    let mut total_inserted = 0;

    // 开启第一个事务
    let mut tx = conn.transaction().unwrap();
    let mut current_batch_count = 0;

    while let Some(line) = reader.read_line(&mut buffer) {
        let line_content = line?.trim();
        total_lines += 1;

        // 解析域名
        let domain = domain_re.captures(line_content).unwrap().get(0).unwrap().as_str();

        // 写入当前事务暂存
        tx.execute(
            "INSERT INTO domains (domain) VALUES (?1)",
            params![domain],
        ).unwrap();
        current_batch_count += 1;
        total_inserted += 1;

        // 达到批量阈值就提交事务，开启新批次
        if current_batch_count >= BATCH_SIZE {
            tx.commit().unwrap();
            tx = conn.transaction().unwrap();
            current_batch_count = 0;
            println!("已提交{}条，当前耗时{:?}", total_inserted, start_time.elapsed());
        }
    }

    // 提交最后不足一批的剩余数据
    if current_batch_count > 0 {
        tx.commit().unwrap();
        println!("已提交最后{}条，插入流程完成", current_batch_count);
    }

    // 输出最终效率统计
    let total_time = start_time.elapsed();
    println!("===== 运行统计结果 =====");
    println!("总解析行数：{}", total_lines);
    println!("总插入条数：{}", total_inserted);
    println!("总耗时：{:?}", total_time);
    println!("平均每秒处理行数：{:.2}", total_lines as f64 / total_time.as_secs_f64());
    println!("平均每秒插入条数：{:.2}", total_inserted as f64 / total_time.as_secs_f64());

    // 原有查询验证逻辑保留
    let mut stmt = conn.prepare("SELECT id, domain FROM domains LIMIT 10").unwrap();
    let domain_iter = stmt.query_map(params![], |row| {
        Ok(Domain {
            id: row.get(0)?,
            domain: row.get(1)?,
        })
    }).unwrap();

    for domain in domain_iter {
        println!("Found domain {:?}", domain.unwrap());
    }

    Ok(())
}

mod my_reader {
    use std::{
        fs::File,
        io::{self, prelude::*},
    };

    pub struct BufReader {
        reader: io::BufReader<File>,
    }

    impl BufReader {
        pub fn open(path: impl AsRef<std::path::Path>) -> io::Result<Self> {
            let file = File::open(path)?;
            let reader = io::BufReader::new(file);
            Ok(Self { reader })
        }

        pub fn read_line<'buf>(
            &mut self,
            buffer: &'buf mut String,
        ) -> Option<io::Result<&'buf mut String>> {
            buffer.clear();
            self.reader
                .read_line(buffer)
                .map(|u| if u == 0 { None } else { Some(buffer) })
                .transpose()
        }
    }
}

二、方案效果对比方法 #

调整BATCH_SIZE的数值（比如分别设置为100、1000、5000、10000）运行代码，通过输出的统计数据就能直观对比不同方案的优劣：

• 总耗时越短越好
• 每秒插入条数越高越好
• 注意SQLite的批量插入性能在单批次1000-10000条区间达到峰值，超过这个阈值后收益会逐步下降，甚至因为事务占用内存过高出现性能倒退。

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