Python使用MySQLdb批量插入时，Commit方式的性能优化咨询

Great question—this is a classic bottleneck when dealing with bulk inserts in Python, and the answer boils down to minimizing unnecessary round-trips to your database. Let’s break down the options from worst to best:

1. 每次插入后Commit（绝对要避免） #

If you’re committing after every single INSERT, that’s almost certainly why you’re seeing poor performance. Every commit forces a network round-trip to the database, plus disk I/O to write transaction logs. For thousands of inserts, that means thousands of expensive overhead operations.

Here’s what this anti-pattern looks like:

for row in thousand_rows:
    cursor.execute("INSERT INTO my_table (col1, col2) VALUES (%s, %s)", row)
    conn.commit()  # Terrible for bulk operations!

2. 批量提交（显著提升性能） #

The simplest fix is to batch your commits—wait until you’ve inserted a chunk of rows (like 100-1000, depending on your database) before committing. This cuts down the number of round-trips from thousands to just a handful.

Example code:

batch_size = 500
insert_count = 0
conn.autocommit = False  # Make sure auto-commit is off

for row in thousand_rows:
    cursor.execute("INSERT INTO my_table (col1, col2) VALUES (%s, %s)", row)
    insert_count += 1
    if insert_count % batch_size == 0:
        conn.commit()
        print(f"Committed {insert_count} rows")

# Don't forget the final batch!
if insert_count % batch_size != 0:
    conn.commit()

Adjust batch_size based on your database: smaller batches (100-200) work better for SQLite to avoid long locks, while PostgreSQL/MySQL can handle larger batches (500-1000) without issues.

3. 批量插入语句 + 单次Commit（最优解之一） #

If your database supports multi-value inserts (most do: PostgreSQL, MySQL, SQLite), you can combine all your inserts into a single SQL statement, then commit once. This reduces both the number of execute calls and commit calls to just one.

Example for PostgreSQL/MySQL:

# Build a single INSERT statement with multiple value pairs
value_placeholders = ", ".join(["(%s, %s)"] * len(thousand_rows))
sql = f"INSERT INTO my_table (col1, col2) VALUES {value_placeholders}"

# Flatten your row data into a single list of parameters
params = [val for row in thousand_rows for val in row]

cursor.execute(sql, params)
conn.commit()

Alternatively, use executemany (most database drivers optimize this under the hood):

cursor.executemany("INSERT INTO my_table (col1, col2) VALUES (%s, %s)", thousand_rows)
conn.commit()

executemany is cleaner than building a giant SQL string and works well for thousands of rows—just make sure your driver supports it efficiently (most modern ones do).

4. 数据库原生批量导入（超大数据量的终极优化） #

If you’re dealing with millions of rows (not just thousands), skip SQL inserts entirely and use your database’s native bulk import tools:

• PostgreSQL: COPY command (use psycopg2’s copy_from method)
• MySQL: LOAD DATA INFILE
• SQLite: executemany is usually sufficient, but sqlite3 has optimizations like isolation_level=None for bulk writes

For thousands of rows, though, the batch insert + single commit approach is more than enough.

Key Takeaways #

• Never commit after every insert—this kills performance with redundant overhead.
• Batch commits are the minimum fix for bulk operations.
• Batch insert statements or executemany + single commit will give you the best performance for thousands of rows.
• Adjust batch sizes based on your database type to avoid locking or transaction log bloat.

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