Python调用MySQL存储过程插入5000行延迟过高问题求助

问题背景 #

使用pymysql连接MySQL，调用仅支持单行插入的存储过程插入5000条数据，单条耗时1-2秒，总时长过高。已尝试更换数据库驱动（cymysql、mysqldb）、多线程、连接池、executemany等方案，均未解决性能问题。

相关代码 #

存储过程执行函数 #

def execute_write_stored_procedure(self, procedure_name, args=None):
    try:
        with self.connection.cursor() as cursor:
            cursor.callproc(procedure_name, args)
        self.connection.commit()  
        return True
    except Exception as e:
        print(e)
        raise Exception(f"Error in execute_write_stored_procedure function : {e}") from e 

插入循环代码 #

for row in rows:
   execute_write_stored_procedure('procedure_name',row)

优化方案 #

1. 减少事务提交次数 #

当前代码每次插入都执行commit，会触发InnoDB的日志刷盘操作，这是核心性能瓶颈之一。改为批量提交（例如每100条提交一次）：

首先修改执行函数，去掉内部的commit：

def execute_stored_procedure_no_commit(self, procedure_name, args=None):
    try:
        with self.connection.cursor() as cursor:
            cursor.callproc(procedure_name, args)
        return True
    except Exception as e:
        print(e)
        raise Exception(f"Error in execute_stored_procedure_no_commit function : {e}") from e 

然后调整插入循环，批量提交：

batch_size = 100  # 可根据服务器性能调整
for idx, row in enumerate(rows):
    self.execute_stored_procedure_no_commit('procedure_name', row)
    if (idx + 1) % batch_size == 0:
        self.connection.commit()
# 提交剩余未批量的数据
self.connection.commit()

2. 修改存储过程支持批量处理 #

将多条数据打包为JSON字符串传入存储过程，在存储过程内部拆分并循环调用原单行逻辑，减少Python与数据库的交互次数：

Python端代码：

import json

# 按批次打包数据，比如每100条一批
batch_size = 100
for i in range(0, len(rows), batch_size):
    batch_data = rows[i:i+batch_size]
    json_str = json.dumps(batch_data)
    # 调用修改后的批量存储过程
    self.execute_write_stored_procedure('batch_insert_procedure', (json_str,))

MySQL端创建批量存储过程（需根据原存储过程的参数调整字段提取逻辑）：

DELIMITER //
CREATE PROCEDURE batch_insert_procedure(IN data_json TEXT)
BEGIN
    DECLARE idx INT DEFAULT 0;
    DECLARE total_rows INT;
    SET total_rows = JSON_LENGTH(data_json);
    
    WHILE idx < total_rows DO
        -- 提取单条数据的各个字段（示例按列表索引提取，需匹配你的数据结构）
        SET @col1 = JSON_UNQUOTE(JSON_EXTRACT(data_json, CONCAT('$[', idx, '].0')));
        SET @col2 = JSON_UNQUOTE(JSON_EXTRACT(data_json, CONCAT('$[', idx, '].1')));
        -- 调用原单行插入存储过程
        CALL procedure_name(@col1, @col2);
        SET idx = idx + 1;
    END WHILE;
END //
DELIMITER ;

3. 临时调整MySQL配置 #

• 将innodb_flush_log_at_trx_commit设置为2：该参数控制事务日志刷盘频率，设为2时每秒刷盘一次，能大幅提升批量插入性能（注意：此设置会降低数据持久性，插入完成后建议改回1）。
• 调大innodb_buffer_pool_size：如果服务器内存充足，将该值设为物理内存的50%-70%，让更多数据在内存中处理，减少磁盘IO。

4. 排查存储过程内部性能瓶颈 #

检查原存储过程是否存在以下问题：

• 不必要的触发器、索引更新（插入时频繁更新索引会增加耗时）
• 大量的关联查询或数据校验逻辑，可考虑将部分逻辑移至Python端批量处理
• 锁等待情况：执行SHOW ENGINE INNODB STATUS查看是否存在锁冲突

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