使用MySQL Workbench导入16G CSV遇Error Code:5内存不足问题求助

问题场景 #

Win10系统（8GB内存）下，使用MySQL Workbench 8.0将16GB |分隔的CSV文件导入MySQL 8.0.32时，触发错误：
Error Code: 5. Out of memory (Needed 784334881 bytes)

使用的导入语句：

LOAD DATA INFILE "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/IT14_Prov20230412P.csv"
INTO TABLE _original
CHARACTER SET latin1
FIELDS TERMINATED BY '|'
IGNORE 1 ROWS;

原My.ini配置：

innodb_buffer_pool_size=1G
innodb_buffer_pool_instances=8

已尝试将innodb_buffer_pool_size修改为2G，但问题未解决；用Python遍历CSV文件时，到第411452行机器无报错冻结，暂不确定是否与内存错误相关。Python测试代码：

with open(file_name, "r", encoding='latin-1') as file:
csv_dict_reader = csv.DictReader(file, delimiter='|')
for row in csv_dict_reader:
print(csv_dict_reader.line_num - 1, row)

解决方案 #

1. 拆分大CSV文件 #

16GB单文件远超8GB内存承载能力，先将文件拆分为多个小文件再分批导入。以下是Python拆分脚本：

import csv

# 每10万行拆分一个文件，可根据内存情况调整
chunk_size = 100000 
source_file = "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/IT14_Prov20230412P.csv"

with open(source_file, 'r', encoding='latin-1') as infile:
    reader = csv.reader(infile, delimiter='|')
    # 保留表头
    header = next(reader)
    chunk_num = 1
    current_chunk = [header]
    
    for row in reader:
        current_chunk.append(row)
        if len(current_chunk) == chunk_size:
            # 写入拆分后的文件
            with open(f"chunk_{chunk_num}.csv", 'w', encoding='latin-1', newline='') as outfile:
                writer = csv.writer(outfile, delimiter='|')
                writer.writerows(current_chunk)
            current_chunk = [header]
            chunk_num += 1
    # 处理剩余不足一个chunk的行
    if current_chunk:
        with open(f"chunk_{chunk_num}.csv", 'w', encoding='latin-1', newline='') as outfile:
            writer = csv.writer(outfile, delimiter='|')
            writer.writerows(current_chunk)

2. 调整MySQL内存及批量导入相关配置 #

仅调整innodb_buffer_pool_size不足以解决问题，需修改以下参数（编辑my.ini后重启MySQL服务）：

# 缓冲池设置为内存的50%-60%，8GB内存建议设为4G
innodb_buffer_pool_size=4G
innodb_buffer_pool_instances=4

# 增大批量插入缓存
bulk_insert_buffer_size=256M

# 调大redo日志文件，减少刷盘频率
innodb_log_file_size=1G
innodb_log_buffer_size=64M

# 确保能容纳单行最大数据
max_allowed_packet=256M

3. 优化LOAD DATA INFILE导入语句 #

通过关闭不必要的检查和事务优化，降低内存占用：

-- 临时关闭自动提交、唯一性检查、外键检查
SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;

-- 导入单个拆分后的文件
LOAD DATA INFILE "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/chunk_1.csv"
INTO TABLE _original
CHARACTER SET latin1
FIELDS TERMINATED BY '|'
IGNORE 1 ROWS;

-- 恢复原有设置
COMMIT;
SET unique_checks=1;
SET foreign_key_checks=1;
SET autocommit=1;

4. 排查CSV文件异常行 #

Python遍历到特定行冻结，大概率是该行数据存在异常（如超长字段、格式错误）。修改脚本定位异常行：

import csv

file_name = "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/IT14_Prov20230412P.csv"

with open(file_name, "r", encoding='latin-1') as file:
    csv_dict_reader = csv.DictReader(file, delimiter='|')
    try:
        for row in csv_dict_reader:
            print(csv_dict_reader.line_num - 1, row)
    except Exception as e:
        print(f"异常行号: {csv_dict_reader.line_num}, 错误信息: {str(e)}")
        # 可选：跳过异常行继续遍历
        while True:
            try:
                next(csv_dict_reader)
            except:
                break

定位到异常行后，修复数据再重新导入。

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