Python向SQLite插入百万级数据过慢，求优化方案

核心问题诊断 #

当前性能暴跌的主要原因：

• 表中缺少针对(move, next)组合的索引，数据量增大后每次查询都要全表扫描，耗时剧增；
• 先查询再判断更新/插入的逻辑，导致大量重复SQL交互，随着数据量上升，IO和CPU开销指数级增长。

具体优化措施 #

1. 添加复合唯一索引

这是最关键的优化步骤，直接解决全表扫描问题：

CREATE UNIQUE INDEX idx_move_next ON moves(move, next);

创建后，SQLite能通过索引快速定位(move, next)对应的行，查询和更新操作耗时会从O(n)降到O(log n)。

2. 替换"先查后更"逻辑为批量冲突更新

放弃先查询再判断的做法，直接利用SQLite的冲突处理语法，一步完成插入或更新：

• 使用ON CONFLICT（SQLite 3.24.0及以上版本支持）：

  INSERT INTO moves(move, next) VALUES (?, ?)
  ON CONFLICT(move, next) DO UPDATE SET count = count + 1;
  

• 批量处理：将多条(move, next)数据打包，用executemany一次性提交，比如每积累1000条执行一次，大幅减少数据库交互次数。

3. 调整事务提交策略

不要每局游戏结束就提交事务，改为按固定数量的操作批次提交（比如每处理5000条数据提交一次），或者设置更大的事务间隔。频繁提交事务会触发大量磁盘IO，是性能瓶颈之一。

4. 优化SQLite内存与IO配置

在初始化数据库时添加以下配置，进一步提升性能：

# 增大内存缓存（单位为页，默认4KB/页，这里设置为100000页=400MB）
cursor.execute("PRAGMA cache_size = 100000;")
# 限制WAL日志大小，避免日志膨胀
cursor.execute("PRAGMA journal_size_limit = 100000000;")
# 关闭外键检查（无外键需求时）
cursor.execute("PRAGMA foreign_keys = OFF;")

5. 复用预处理SQL语句

在Python中提前准备好SQL语句，避免每次执行都重新解析：

# 提前初始化语句
update_insert_stmt = """
INSERT INTO moves(move, next) VALUES (?, ?)
ON CONFLICT(move, next) DO UPDATE SET count = count + 1
"""
# 批量执行
cursor.executemany(update_insert_stmt, batch_data_list)

6. 压缩FEN字符串（可选）

FEN字符串长度较长，可通过压缩减少存储和IO开销：

• 用zlib压缩FEN字符串后以BLOB类型存储，查询时再解压；
• 自定义FEN编码规则，缩短字符串长度（比如将重复符号用更短标记替代）。

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