一、为什么SQLite批量插入会变慢？
当我们需要往SQLite数据库里一次性插入大量数据时，经常会发现速度特别慢。这就像往一个小箱子里塞太多东西，每次只能放一件，自然就快不起来。SQLite默认每次插入都会开启一个事务，频繁的磁盘IO操作是性能瓶颈的主要原因。
举个例子，假设我们要插入10万条学生记录：
# 技术栈：Python + SQLite3
import sqlite3
import time
conn = sqlite3.connect('school.db')
cursor = conn.cursor()
# 创建学生表
cursor.execute('''CREATE TABLE IF NOT EXISTS students
(id INTEGER PRIMARY KEY, name TEXT, score REAL)''')
start_time = time.time()
# 错误示范：逐条插入
for i in range(100000):
cursor.execute("INSERT INTO students VALUES (?, ?, ?)",
(i, f"Student_{i}", 80.5))
print(f"逐条插入耗时：{time.time() - start_time:.2f}秒")
conn.close()
运行这段代码，你会发现插入10万条数据可能需要几十秒甚至更长时间。这显然不能满足我们处理大量数据的需求。
二、批量插入的三大优化方案
1. 使用事务包装批量操作
SQLite默认自动提交事务，每次插入都会产生磁盘写入。我们可以手动开启事务，等所有插入完成后再一次性提交：
# 技术栈：Python + SQLite3
conn = sqlite3.connect('school.db')
cursor = conn.cursor()
start_time = time.time()
try:
conn.execute("BEGIN TRANSACTION") # 开启事务
for i in range(100000):
cursor.execute("INSERT INTO students VALUES (?, ?, ?)",
(i, f"Student_{i}", 80.5))
conn.commit() # 提交事务
except:
conn.rollback() # 出错回滚
print(f"事务批量插入耗时：{time.time() - start_time:.2f}秒")
conn.close()
这样处理后，速度能提升几十倍！因为磁盘写入从10万次减少到1次。
2. 使用executemany批量执行
SQLite提供了专门的批量执行方法，比循环执行execute更高效：
# 技术栈：Python + SQLite3
conn = sqlite3.connect('school.db')
cursor = conn.cursor()
# 准备批量数据
data = [(i, f"Student_{i}", 80.5) for i in range(100000)]
start_time = time.time()
cursor.executemany("INSERT INTO students VALUES (?, ?, ?)", data)
conn.commit()
print(f"executemany批量插入耗时：{time.time() - start_time:.2f}秒")
conn.close()
这种方法代码更简洁，性能也更好，适合数据已经准备好的场景。
3. 使用内存数据库临时存储
对于特别大的数据集，可以先用内存数据库处理，再导出到文件：
# 技术栈：Python + SQLite3
# 创建内存数据库
memory_conn = sqlite3.connect(':memory:')
memory_cursor = memory_conn.cursor()
memory_cursor.execute('''CREATE TABLE students
(id INTEGER PRIMARY KEY, name TEXT, score REAL)''')
# 在内存中快速插入
data = [(i, f"Student_{i}", 80.5) for i in range(100000)]
memory_cursor.executemany("INSERT INTO students VALUES (?, ?, ?)", data)
memory_conn.commit()
# 导出到文件
file_conn = sqlite3.connect('school.db')
memory_conn.backup(file_conn)
memory_conn.close()
file_conn.close()
内存操作比磁盘快得多，适合做中间处理。
三、进阶优化技巧
1. 调整PRAGMA参数
SQLite有一些隐藏设置可以提升性能：
# 技术栈：Python + SQLite3
conn = sqlite3.connect('school.db')
# 优化设置
conn.execute("PRAGMA journal_mode = WAL") # 使用WAL日志模式
conn.execute("PRAGMA synchronous = NORMAL") # 平衡安全性和性能
conn.execute("PRAGMA cache_size = -8000") # 增加缓存大小
# 然后执行批量插入...
这些设置可以根据实际需求调整，一般能带来20%-50%的性能提升。
2. 分批处理超大数据
当数据量特别大时（比如上千万条），即使使用批量插入也可能内存不足。这时需要分批处理：
# 技术栈：Python + SQLite3
def batch_insert(data, batch_size=50000):
conn = sqlite3.connect('school.db')
cursor = conn.cursor()
for i in range(0, len(data), batch_size):
batch = data[i:i+batch_size]
cursor.executemany("INSERT INTO students VALUES (?, ?, ?)", batch)
conn.commit() # 每批提交一次
conn.close()
# 生成100万条测试数据
big_data = [(i, f"Student_{i}", 80.5) for i in range(1000000)]
batch_insert(big_data)
分批处理既能控制内存使用，又能保持较好的性能。
四、实战应用与注意事项
应用场景
这些优化技巧特别适合：
- 从CSV/Excel导入大量数据到SQLite
- 迁移数据库内容
- 定期批量更新数据
- 数据分析和报表生成
技术优缺点
优点：
- 性能提升显著（10-100倍）
- 代码改动小，易于实现
- 不依赖额外工具
缺点：
- 大事务可能占用较多内存
- 出错时需要重试整个批次
- 需要根据数据量调整参数
注意事项
- 备份重要数据：批量操作前先备份，防止数据丢失
- 错误处理：确保有完善的事务回滚机制
- 内存监控：处理超大文件时注意内存使用情况
- 索引影响：批量插入前可暂时删除索引，完成后再重建
总结
通过本文介绍的优化方法，你应该能够轻松应对SQLite中的大数据导入问题。记住三个关键点：
- 一定要用事务包装批量操作
- 根据数据量选择合适的批量大小
- 适当调整PRAGMA参数提升性能
下次遇到SQLite插入慢的问题，不妨试试这些方法，相信会有立竿见影的效果！
评论