网络笔记本执行SQLite插入慢10倍的原因及优化方案探讨

为什么每次insert后commit会在网络环境变慢 #

SQLite的commit操作会强制把内存中的数据页同步写入磁盘。本地运行时磁盘IO速度快，这个操作的延迟可以忽略；但在WiFi连接的共享存储场景下，每次commit都要通过网络完成数据同步，WiFi的延迟和带宽限制会把这个单次操作的开销放大数倍，这就是你看到速度慢10倍的核心原因。

统一commit的潜在影响 #

你提到的把commit放到遍历结束后统一执行的方案确实能解决速度问题，但需要注意几个风险：

• 数据丢失风险：如果遍历过程中程序崩溃、网络断开或者主电脑意外关机，所有未提交的插入数据都会丢失。网络环境下这类故障的概率比本地更高，需要评估数据的重要性和容错能力。
• 锁阻塞问题：SQLite在写操作时会持有写锁，长时间不提交会导致锁持有时间变长，其他访问该共享数据库的进程（如果有的话）会被阻塞，直到commit完成。
• 内存占用：未提交的插入数据会暂存于SQLite的内存缓存中，如果CSV数据量极大，可能会占用较多系统内存，但一般批量导入场景下这个问题不突出。

适合网络场景的其他优化方案 #

除了统一commit，还有几个更适配网络环境的优化方式：

• 使用executemany批量插入：把多行插入数据打包成一个executemany调用，减少与数据库的交互次数，进一步降低网络开销。同时可以设置批次大小（比如每1000条提交一次），平衡速度和数据丢失风险。
• 调整同步模式：把SQLite的synchronous参数从默认的FULL改为NORMAL或OFF。FULL模式会强制每次commit都刷写磁盘，NORMAL仅在关键操作时刷写，OFF则完全不做强制同步，能大幅提升写性能，但数据丢失风险会上升，适合有备份或可接受部分数据丢失的场景。修改方式是在连接数据库时添加参数：conn = sqlite3.connect('your_db.db', synchronous='NORMAL')
• 开启WAL模式：Write-Ahead Logging（WAL）模式下，commit操作是追加日志而非直接修改数据库文件，能显著提升写性能，尤其是在并发场景下。开启方式为连接后执行：conn.execute('PRAGMA journal_mode=WAL;')。注意WAL模式需要SQLite 3.7.0以上版本，且共享存储需支持文件锁（Windows共享一般满足）。
• 先将CSV拷贝到本地：如果CSV文件体积不大，先把共享的CSV下载到网络笔记本本地，再读取本地文件进行插入操作，减少读取CSV时的网络IO开销。

代码修改示例 #

原单次插入代码（慢的原因） #

def insert(self, params):
    self.cur.execute("INSERT INTO your_table (col1, col2) VALUES (?, ?)", params)
    self.conn.commit()

优化后的批量插入代码 #

# 新增批量插入方法
def batch_insert(self, data_list):
    self.cur.executemany("INSERT INTO your_table (col1, col2) VALUES (?, ?)", data_list)
    self.conn.commit()

# 遍历CSV时的调用方式
data_batch = []
batch_size = 1000  # 可根据数据量调整
for row in csv_reader:
    data_batch.append((row["col1"], row["col2"]))
    if len(data_batch) >= batch_size:
        self.batch_insert(data_batch)
        data_batch = []
# 处理剩余的未提交数据
if data_batch:
    self.batch_insert(data_batch)

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