萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> PHP 程式設計

Python 多進程高效導入百萬級 CSV 至 MySQL 實戰指南

背景與挑戰

處理 3GB(2100 萬筆)與 7GB(3500 萬筆)兩個巨型 CSV 檔案,單進程寫入耗時極長。採用多進程生產者-消費者模型,配合批次插入、延遲建立索引、編碼轉換等策略,大幅提升匯入效能。

核心優化策略

  1. 批次插入:每 5000 筆打包成一條 INSERT ... VALUES (...),(...),減少網路往返。
  2. 先寫入後建索引:匯入期間禁用索引,完成後再 CREATE INDEX,避免 B+ 樹頻繁分裂。
  3. 生產者-消費者:主進程讀檔推入佇列,多個 Worker 進程消費寫入。
  4. 控制 Worker 數量:預設 10 個,避免壓垮 MySQL 連接數與 CPU。
  5. 髒資料隔離:欄位數不符的列寫入 _dirty_rows.csv 供事後修正。
  6. 編碼轉換:原始 GBK 轉 UTF-8,避免中文亂碼。
  7. Click 封裝 CLI:提供 gbk_to_utf8 與 load 兩條指令,生產環境易操作。

完整程式碼解析

1. 環境與常數

import codecs, csv, logging, multiprocessing, os, warnings
import click, MySQLdb, sqlalchemy
warnings.filterwarnings('ignore', category=MySQLdb.Warning)

BATCH = 5000
DB_URI = 'mysql://root@localhost:3306/example?charset=utf8'
engine = sqlalchemy.create_engine(DB_URI)

2. 取得資料表欄位

def get_table_cols(table):
    sql = 'SELECT * FROM `{table}` LIMIT 0'.format(table=table)
    res = engine.execute(sql)
    return res.keys()

3. 批次寫入函式

def insert_many(table, cols, rows, cursor):
    sql = 'INSERT INTO `{table}` ({cols}) VALUES ({marks})'.format(
        table=table,
        cols=', '.join(cols),
        marks=', '.join(['%s'] * len(cols))
    )
    cursor.execute(sql, *rows)
    logging.info('process %s inserted %s rows into table %s', os.getpid(), len(rows), table)

4. Worker 進程邏輯

def insert_worker(table, cols, queue):
    rows = []
    cursor = sqlalchemy.create_engine(DB_URI)  # 每進程獨立連線
    while True:
        row = queue.get()
        if row is None:  # 結束信號
            if rows:
                insert_many(table, cols, rows, cursor)
            break
        rows.append(row)
        if len(rows) == BATCH:
            insert_many(table, cols, rows, cursor)
            rows = []

5. 並行匯入協調器

def insert_parallel(table, reader, w=10):
    cols = get_table_cols(table)
    queue = multiprocessing.Queue(maxsize=w * BATCH * 2)  # 防止記憶體堆積
    workers = []
    for i in range(w):
        p = multiprocessing.Process(target=insert_worker, args=(table, cols, queue))
        p.start()
        workers.append(p)
        logging.info('starting # %s worker process, pid: %s...', i+1, p.pid)

    dirty_file = './{}_dirty_rows.csv'.format(table)
    xf = open(dirty_file, 'w')
    writer = csv.writer(xf, delimiter=reader.dialect.delimiter)

    for line in reader:
        if len(line) != len(cols):  # 髒資料
            writer.writerow(line)
            continue
        clean_line = [None if x == 'NULL' else x for x in line]
        queue.put(tuple(clean_line))
        if reader.line_num % 500000 == 0:
            logging.info('put %s tasks into queue.', reader.line_num)

    xf.close()
    logging.info('send close signal to worker processes')
    for _ in range(w):
        queue.put(None)
    for p in workers:
        p.join()

6. GBK 轉 UTF-8 工具函式

def convert_file_to_utf8(f, rv_file=None):
    if not rv_file:
        name, ext = os.path.splitext(f)
        if isinstance(name, str): name = name.encode('utf8')
        rv_file = '{}_utf8{}'.format(name, ext)
    logging.info('start to process file %s', f)
    with open(f) as infd, open(rv_file, 'w') as outfd:
        lines = []
        chunk = 200000
        first_line = infd.readline().strip(codecs.BOM_UTF8).strip() + '\n'
        lines.append(first_line)
        for line in infd:
            clean_line = line.decode('gb18030').encode('utf8').rstrip() + '\n'
            lines.append(clean_line)
            if len(lines) == chunk:
                outfd.writelines(lines)
                lines = []
        outfd.writelines(lines)

7. Click 指令介面

@click.group()
def cli():
    logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(name)s - %(message)s')

@cli.command('gbk_to_utf8')
@click.argument('f')
def convert_gbk_to_utf8(f):
    convert_file_to_utf8(f)

@cli.command('load')
@click.option('-t', '--table', required=True, help='表名')
@click.option('-i', '--filename', required=True, help='輸入檔案')
@click.option('-w', '--workers', default=10, help='worker 數量,預設 10')
def load_fac_day_pro_nos_sal_table(table, filename, workers):
    with open(filename) as fd:
        fd.readline()  # skip header
        reader = csv.reader(fd)
        insert_parallel(table, reader, w=workers)

if __name__ == '__main__':
    cli()

執行方式

# 1. 轉碼
python import_tool.py gbk_to_utf8 huge.csv
# 2. 匯入
python import_tool.py load -t my_table -i huge_utf8.csv -w 10

注意事項

  • 生產環境建議使用 PyMySQL 或 mysqlclient 替代 MySQLdb。
  • 若資料量超億級,考慮 LOAD DATA INFILE 配合分區表。
  • 佇列大小 maxsize=w*BATCH*2 需根據記憶體調整。
copyright © 萬盛學電腦網 all rights reserved