如何提升Python读取CSV写入MySQL的速度？5GB IP地址CSV导入优化

你提到的并行拆分导入方案是可行的，但优先优化现有单进程逻辑即可获得数十倍的性能提升，优化建议如下：

• 移除所有调试打印语句：控制台IO属于高耗时操作，原代码中每行插入都打印SQL和计数器，会拖慢整体速度50%以上。
• 取消逐行事务提交：每次COMMIT都会触发MySQL的磁盘刷写，单行提交的写入TPS仅为数百量级，改为每1000_{10000行提交一次，性能可提升10}50倍。
• 替换单条插入为批量参数化插入：原代码使用字符串拼接生成SQL，不仅有SQL注入风险，执行效率也极低。使用executemany接口批量提交参数化SQL，可再提升数倍性能。
• 单进程性能到瓶颈后可使用拆分多进程导入：将5GB CSV拆分为每个100万_{500万行的子文件，开启4}8个进程分别导入，每个进程单独创建数据库连接，不要共享连接避免线程安全问题。导入前可临时调整MySQL配置：将innodb_flush_log_at_trx_commit设为2、关闭非必要的Binlog、提前删除表的非主键索引导入完成后重建，可进一步降低写入开销。
• 最优方案直接使用MySQL原生LOAD DATA INFILE：跳过Python层的处理，直接由MySQL读取CSV文件导入，性能是Python实现的3~10倍，5GB文件通常十几分钟即可完成导入。

优化后Python单进程实现示例 #

import csv
import mysql.connector

# 批量插入的批次大小，可根据实际情况调整
BATCH_SIZE = 5000

def main():
    cnx = mysql.connector.connect(user='root', password='', host='127.0.0.1', database='ips')
    cursor = cnx.cursor()
    # 参数化插入语句，禁止拼接业务值
    insert_sql = "INSERT INTO ips (ip_start,ip_end,continent) VALUES (%s, %s, %s)"
    batch_data = []

    with open('iplist.csv', 'r', encoding='utf-8') as f:
        csv_reader = csv.reader(f)
        # 如果CSV有表头可执行next(csv_reader)跳过
        for row in csv_reader:
            batch_data.append((row[0], row[1], row[2]))
            # 凑够批次就提交
            if len(batch_data) >= BATCH_SIZE:
                cursor.executemany(insert_sql, batch_data)
                cnx.commit()
                batch_data = []
    # 提交剩余不足批次的数据
    if batch_data:
        cursor.executemany(insert_sql, batch_data)
        cnx.commit()
    
    cursor.close()
    cnx.close()

if __name__ == "__main__":
    main()

LOAD DATA 导入语法示例 #

需提前开启MySQL的local_infile参数，执行SET GLOBAL local_infile = 1;即可：

LOAD DATA LOCAL INFILE '/完整路径/iplist.csv' 
INTO TABLE ips 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\n'
-- 如果CSV有表头就加下面这行，没有则删除
IGNORE 1 ROWS;

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