Python中SQLite插入超长字符串速度极慢的原因探究

核心原因 #

• 磁盘IO开销剧增：5000单词的字符串约占30-40KB存储空间，是短字符串（如"hello"）的上万倍。500条长字符串总数据量可达15-20MB，SQLite每次插入都要写入磁盘，IO耗时与数据量直接挂钩，这是速度变慢的主因。
• 单事务日志成本高：默认情况下，每条INSERT都是独立事务，完成一条就要写入日志并执行磁盘同步（fsync）。长字符串会让日志写入量暴增，且每次同步的耗时也会随数据量变大而增加。
• 缓存命中率下降：SQLite的页缓存容量有限，长字符串可能占用多个数据库页，快速填满缓存后，后续操作需要频繁从磁盘读写页，进一步拖慢速度。
• 字符串处理额外消耗：长字符串的编码校验、长度计算、存储结构维护（如变长字符串的指针操作），相比短字符串会消耗更多CPU资源，累积后也会影响整体插入速度。

优化方法 #

• 批量事务插入：将多条INSERT合并到一个事务中，示例：

  BEGIN TRANSACTION;
  -- 批量插入多条数据
  INSERT INTO table_name VALUES (?,?,?);
  INSERT INTO table_name VALUES (?,?,?);
  -- ...
  COMMIT;
  

  这能把多次磁盘同步操作缩减为1次，是提升批量插入速度最有效的手段，通常能带来几十倍的速度提升。
• 启用WAL模式：执行PRAGMA journal_mode=WAL;切换到WAL日志模式。WAL比默认的DELETE模式更适合批量写入，支持读写并发，且日志写入开销更低。
• 增大页缓存：通过PRAGMA cache_size=-20000;调整缓存大小（数值单位为KB，-20000代表20MB），让更多数据留在内存中，减少磁盘IO次数。
• 调整同步级别：若能接受极低的数据丢失风险（如突发断电），可执行PRAGMA synchronous=NORMAL;（适合多数场景），或PRAGMA synchronous=OFF;（速度最快但风险最高），减少强制磁盘同步的频率。
• 保留参数化查询：你现在使用的INSERT INTO table_name VALUES (?,?,?)参数化查询是正确的，它避免了重复解析SQL的开销，继续保持即可。

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