Python向SQLite插入百万条随机数据的提速方法及慢因咨询

• 你主动开启了自动提交模式：db.isolation_level = None 这个配置会让SQLite每执行1条INSERT就自动触发一次事务提交，每次提交都需要执行磁盘刷写操作，100万次随机IO的开销极高，这是最主要的慢因。
• 单条循环插入的额外开销：每次执行execute都要做SQL语法解析、参数绑定、执行上下文初始化，100万次重复操作会浪费大量CPU资源。
• 如果表RandomBase提前创建了索引，每次插入都需要实时更新索引结构，也会额外拖慢插入速度。

• 关闭自动提交，使用事务批量提交
  首先删除db.isolation_level = None这行配置，恢复SQLite默认的事务提交逻辑。你可以选择每插入1000~10000条手动提交一次事务，避免单次提交数据量过大占用过多内存，也避免提交次数过多的IO开销。
• 使用executemany批量执行插入语句
  executemany会一次性完成SQL解析、批量参数绑定，比循环调用execute性能提升至少一个数量级。
• 测试环境下可以临时调整SQLite性能参数
  执行db.execute("PRAGMA synchronous = OFF") 关闭磁盘同步校验，插入速度会进一步提升，缺点是突发掉电可能丢失数据，仅适合测试场景使用。
• 先插入数据再创建索引
  如果表有索引需求，可以先建无索引的表，插入完所有数据后再执行CREATE INDEX语句，比插入过程中实时更新索引快3~5倍。

你之前设置db.isolation_level = None后，SQLite会进入自动提交模式，此时任何手动的BEGIN/COMMIT命令都会被忽略，这就是你遇到事务不生效的核心原因。只需要删除这行配置，即可正常使用事务控制，优化后的参考代码如下：

import sqlite3
import random
import string

db = sqlite3.connect("test.db")
# 移除db.isolation_level = None即可正常控制事务
# 测试场景可加以下两个参数进一步提速，生产环境谨慎使用
db.execute("PRAGMA synchronous = OFF")
db.execute("PRAGMA journal_mode = MEMORY")

def get_random_string(length):
    letters = string.ascii_lowercase
    return ''.join(random.choice(letters) for i in range(length))

BATCH_SIZE = 10000
total_count = 1000000
for batch_idx in range(total_count // BATCH_SIZE):
    # 批量生成当前批次的插入数据
    batch_data = []
    for _ in range(BATCH_SIZE):
        batch_data.append( (get_random_string(8), random.randint(1900,2000)) )
    # 单个批次一次性插入+提交
    db.executemany("INSERT INTO RandomBase (name, number) VALUES (?, ?)", batch_data)
    db.commit()
db.close()

以上优化落地后，100万条数据的插入耗时可以从几十分钟降到10秒以内。

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