在业务开发中，经常需要处理10w级别的MySQL数据插入场景，比如初始化大量测试数据、批量同步上游业务数据等。如果直接使用单条INSERT语句逐条插入，会因为频繁的网络请求、事务提交和索引维护产生极大的性能损耗，完成10w条数据插入可能需要数分钟甚至更久。通过合理的优化手段，可以将插入时间压缩到秒级，大幅提升操作效率。
单条插入的性能问题
默认情况下，每执行一条INSERT语句，MySQL都会单独处理一次请求，同时如果未手动开启事务，每条语句会自动提交事务，产生大量的日志刷盘操作。我们可以简单测试单条插入10w条数据的耗时，示例代码如下：
import pymysql
import time
# 连接MySQL数据库
conn = pymysql.connect(
host='127.0.0.1',
user='root',
password='123456',
database='test_db',
charset='utf8mb4'
)
cursor = conn.cursor()
# 创建测试表
cursor.execute('''
CREATE TABLE IF NOT EXISTS user_test (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50),
age INT
)
''')
conn.commit()
# 单条插入测试
start_time = time.time()
for i in range(100000):
sql = "INSERT INTO user_test (name, age) VALUES ('user_%d', %d)" % (i, i % 100)
cursor.execute(sql)
conn.commit()
end_time = time.time()
print("单条插入10w条数据耗时：%.2f秒" % (end_time - start_time))
cursor.close()
conn.close()
上述代码在我的测试环境中耗时超过120秒，效率非常低下，完全不适合批量数据插入场景。
优化方案：批量插入
MySQL支持一条INSERT语句插入多条数据，这种方式可以减少网络交互次数，同时合并事务提交，大幅提升插入效率。批量插入的SQL语法如下：
INSERT INTO table_name (col1, col2) VALUES (val1_1, val1_2), (val2_1, val2_2), ... (valn_1, valn_2);
我们可以将10w条数据分成多个批次，每个批次生成一条批量插入语句，比如每1000条为一个批次，这样只需要执行100次SQL请求即可完成全部插入。优化后的Python代码示例如下：
import pymysql
import time
conn = pymysql.connect(
host='127.0.0.1',
user='root',
password='123456',
database='test_db',
charset='utf8mb4'
)
cursor = conn.cursor()
# 清空测试表
cursor.execute('TRUNCATE TABLE user_test')
conn.commit()
start_time = time.time()
batch_size = 1000
total = 100000
for i in range(0, total, batch_size):
# 构造批量插入的values部分
values_list = []
for j in range(batch_size):
current_index = i + j
if current_index >= total:
break
values_list.append("('user_%d', %d)" % (current_index, current_index % 100))
values_str = ','.join(values_list)
sql = "INSERT INTO user_test (name, age) VALUES %s" % values_str
cursor.execute(sql)
conn.commit()
end_time = time.time()
print("批量插入10w条数据耗时：%.2f秒" % (end_time - start_time))
cursor.close()
conn.close()
上述代码在我的测试环境中耗时仅3秒左右，相比单条插入效率提升了40倍以上。需要注意的是，批量插入的单批次数据量不是越大越好，过大的批次会导致SQL语句过长，可能触发MySQL的max_allowed_packet参数限制，一般建议单批次数据量控制在1000到5000条之间。
进一步优化：手动控制事务
默认情况下，如果未手动开启事务，每条SQL执行后都会自动提交事务，即使是批量插入语句，每次执行也会触发一次事务提交。我们可以手动开启事务，在所有批次插入完成后再统一提交，减少事务提交的次数。优化后的代码如下：
import pymysql
import time
conn = pymysql.connect(
host='127.0.0.1',
user='root',
password='123456',
database='test_db',
charset='utf8mb4'
)
cursor = conn.cursor()
cursor.execute('TRUNCATE TABLE user_test')
conn.commit()
start_time = time.time()
batch_size = 1000
total = 100000
# 手动开启事务
conn.begin()
try:
for i in range(0, total, batch_size):
values_list = []
for j in range(batch_size):
current_index = i + j
if current_index >= total:
break
values_list.append("('user_%d', %d)" % (current_index, current_index % 100))
values_str = ','.join(values_list)
sql = "INSERT INTO user_test (name, age) VALUES %s" % values_str
cursor.execute(sql)
# 所有插入完成后统一提交事务
conn.commit()
except Exception as e:
# 出现异常回滚事务
conn.rollback()
print("插入失败：%s" % str(e))
end_time = time.time()
print("手动事务+批量插入10w条数据耗时：%.2f秒" % (end_time - start_time))
cursor.close()
conn.close()
加上手动事务控制后，插入耗时进一步降低到2秒左右，因为减少了多次事务提交带来的日志刷盘开销。
其他优化建议
- 插入前可以暂时关闭表的非必要索引，尤其是唯一索引和普通索引，插入完成后再重新创建，减少索引维护的消耗。如果表有自增主键，自增索引的维护开销相对较小，可以保留。
- 如果插入的数据不需要立即被查询，可以将表的存储引擎临时切换为
MyISAM，其插入性能比InnoDB更高，插入完成后再切换回InnoDB。 - 避免在插入语句中使用函数或者复杂的表达式，尽量直接传入原始值，减少MySQL语句解析和执行的开销。
- 如果是从文件导入数据，可以使用MySQL自带的
LOAD DATA INFILE语句，这是MySQL官方提供的最高效的数据导入方式，比批量插入的效率还要高很多，适合超大数据量的导入场景。
不同方案的性能对比
以下是不同插入方案在10w条数据场景下的性能对比结果：
| 插入方案 | 耗时（秒） | 适用场景 |
|---|---|---|
| 单条插入 | 120+ | 仅适合插入数据量极少的场景 |
| 批量插入（自动提交事务） | 3 | 中等数据量批量插入，无事务要求 |
| 批量插入+手动事务 | 2 | 大部分10w级别数据插入场景 |
| LOAD DATA INFILE | 0.5 | 超大数据量从文件导入场景 |
开发者可以根据实际的业务场景选择合适的插入方案，在满足业务需求的前提下尽可能提升插入效率，避免数据插入操作成为系统的性能瓶颈。
MySQL数据插入批量插入batch_insert修改时间：2026-06-26 05:51:32