如何正确放置BEGIN TRANSACTION和COMMIT以优化批量INSERT速度？

嘿，我来帮你搞定这个批量插入慢的问题！首先咱们先把事务的正确用法捋清楚，再结合你用5400 RPM硬盘的情况给点额外优化建议。

一、事务语句的正确放置位置 #

你之前速度没提上来，大概率是事务的范围没搞对——要把所有批量INSERT操作整个包裹在一次BEGIN和COMMIT之间，而不是循环里反复开事务，或者只给单条INSERT加事务。

正确的代码结构应该是这样的：

import sqlite3

# 连接目标数据库
conn = sqlite3.connect('your_target_db.db')
cursor = conn.cursor()

try:
    # 只开启一次事务，放在所有插入操作之前
    cursor.execute('BEGIN TRANSACTION')
    
    # 这里是你的批量插入逻辑——比如循环读取源数据库数据然后插入
    for record in your_source_data_list:
        cursor.execute(
            "INSERT INTO target_table (col1, col2, col3) VALUES (?, ?, ?)",
            (record['col1'], record['col2'], record['col3'])
        )
    
    # 所有插入完成后，一次性提交事务
    cursor.execute("COMMIT")
    print("批量插入完成！")
except Exception as e:
    # 出错时回滚，避免脏数据残留
    cursor.execute("ROLLBACK")
    print(f"插入出错，已回滚：{str(e)}")
    raise
finally:
    # 关闭数据库连接
    conn.close()

为什么这样有效？因为SQLite默认会给单条INSERT自动提交事务，这意味着每插一条就要触发一次磁盘写入——5400转硬盘的随机写入速度本来就慢，批量事务把多次磁盘IO合并成一次，能大幅减少等待时间。

二、其他能提速的关键优化（针对5400RPM硬盘） #

除了事务，还有几个点能帮你进一步提升速度，尤其适配慢硬盘的IO瓶颈：

• 用executemany()代替循环execute()
  这个是比事务更立竿见影的优化！它能把多条INSERT打包成一次数据库调用，减少Python和数据库之间的交互开销。示例代码：

  # 先把所有要插入的数据整理成元组列表
  insert_data = [
      (val1, val2, val3),
      (val4, val5, val6),
      # ... 更多待插入数据
  ]
  # 一次执行批量插入
  cursor.executemany(
      "INSERT INTO target_table (col1, col2, col3) VALUES (?, ?, ?)",
      insert_data
  )
  

• 调整SQLite的IO相关参数
  针对慢硬盘，可以通过修改SQLite参数减少磁盘操作频率：

  • 关闭同步（谨慎使用）：conn.execute("PRAGMA synchronous = OFF")，这个会让SQLite不等待磁盘写入确认，速度提升明显，但如果突然断电可能会丢失数据，适合非核心数据或有备份的场景。
  • 增大缓存：conn.execute("PRAGMA cache_size = 20000")（单位是页，默认每页4KB，20000就是80MB缓存），让更多操作在内存完成，减少磁盘读写次数。

• 避免插入过程中的其他磁盘操作
  5400转硬盘的IO带宽有限，如果你在批量插入的同时还在读写其他大文件、或者源数据库也在频繁IO，会严重拖慢速度，尽量让插入过程独占磁盘资源。

总结 #

先把事务放在整个批量插入的最外层，再替换成executemany()，这两个操作应该能让你的速度提升一个量级；如果还不够，再结合硬盘情况调整SQLite的IO参数，应该就能解决5KB/s的慢问题了。

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