如何提升Peewee批量插入MySQL数据的执行速度？

我本地数据库有一张仅含2个字段的表，只需插入大量新行，主键无需手动指定。但即便是本地库，插入操作也远慢于代码其他部分，希望尽可能提速。用的是Python 3.10.10 + Peewee ORM，当前在多子进程中调用的插入函数如下：

def insert_many_multi(self, data):
        with conn.atomic():
            if len(data)>998:
                for i in range(0, len(data), 998):
                        Binary.insert_many(data[i:i+998], fields=[Binary.id, Binary.value]).execute()
            else:
                Binary.insert_many(data, fields=[Binary.id, Binary.value]).execute()

data是形如[(None, 0), (None, 0), ...]的元组数组。

模型与数据库连接代码：

conn = peewee.MySQLDatabase('Main', user='root', password='',
                         host='127.0.0.1', port=3306)
conn.close()

class BaseModel(peewee.Model):
    class Meta:
        database = conn

class Binary(BaseModel):
    id = peewee.AutoField(column_name='binary_id')
    value = peewee.IntegerField(column_name='value', null=True)
    class Meta:
        table_name = 'Binary'

试过用atomic()事务和不用事务，速度差异极小。

优化方案 #

1. 跳过主键字段，利用AutoField自动生成 #

id是AutoField类型，插入时无需传入None，直接只传递value字段即可。这样能减少数据传输量，同时让MySQL自动处理主键生成，避免不必要的字段解析。

修改后的数据格式为[(0,), (0,), ...]，插入代码简化：

def insert_many_multi(self, data):
    with conn.atomic():
        batch_size = 1000  # 可根据MySQL配置调整为2000或更大值
        for i in range(0, len(data), batch_size):
            Binary.insert_many(data[i:i+batch_size], fields=[Binary.value]).execute()

2. 调整MySQL连接参数，开启批量插入优化 #

初始化MySQLDatabase时添加以下参数，开启MySQL原生的批量插入优化：

conn = peewee.MySQLDatabase(
    'Main',
    user='root',
    password='',
    host='127.0.0.1',
    port=3306,
    charset='utf8mb4',
    sql_mode='STRICT_TRANS_TABLES',
    max_connections=20,
    local_infile=True  # 为后续使用bulk_insert_many做准备
)

3. 使用Peewee的bulk_insert_many高效API #

Peewee的bulk_insert_many底层基于LOAD DATA LOCAL INFILE实现，比普通的insert_many速度提升明显，适合超大量数据插入场景：

def insert_many_multi(self, data):
    with conn.atomic():
        # batch_size可根据服务器性能调整为10000甚至更大
        Binary.bulk_insert_many(data, fields=[Binary.value], batch_size=10000).execute()

注意：需确保MySQL开启了local_infile参数（连接时已配置），且数据库用户拥有对应权限。

4. 优化多进程的连接管理 #

Peewee的数据库连接不是进程安全的，多进程环境下不能共享同一个连接，每个进程需创建独立连接。可以在子进程初始化时重新初始化连接：

def init_child_process():
    global conn
    # 子进程内重新创建数据库连接
    conn = peewee.MySQLDatabase(
        'Main',
        user='root',
        password='',
        host='127.0.0.1',
        port=3306,
        local_infile=True
    )

# 使用multiprocessing时的示例
from multiprocessing import Pool
if __name__ == '__main__':
    with Pool(initializer=init_child_process) as pool:
        pool.map(your_insert_function, data_chunks)

5. 调整MySQL服务器配置 #

修改MySQL的my.cnf（Windows为my.ini）配置，提升写入性能：

• innodb_buffer_pool_size：设置为服务器内存的50%-70%（如8G内存设为5G）
• innodb_log_file_size：增大至256M-1G，减少日志刷盘频率
• innodb_flush_log_at_trx_commit：设为2（牺牲少量持久性换取写入速度，适合批量插入场景）
• innodb_autoinc_lock_mode：设为2（连续自增锁模式，提升批量插入时的并发效率）

修改后重启MySQL服务生效。

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