如何进一步优化含ON DUPLICATE KEY UPDATE的INSERT代码？

核心问题说明 #

先纠正原修改代码里的无效逻辑：ON DUPLICATE KEY UPDATE col3 = col3, col4 = col4这种写法不会产生任何更新效果（等于把列值设为自身），必须改为col3 = VALUES(col3), col4 = VALUES(col4)，才能用插入的新值覆盖现有记录的对应列。

以下是具体优化方向：

1. 替换单条执行为批量插入 #

当前循环每条数据执行cursor.execute会产生大量数据库交互，是性能瓶颈。改用executemany批量提交，减少交互次数：

for chunk in np.array_split(DataFrame, 20, axis=0):
    # 构造批量插入的参数列表
    values_list = [tuple(row) for row in chunk[['col1', 'col2', 'col3', 'col4']].values]
    # 批量UPSERT语句
    upsert_sql = """
        INSERT INTO table (col1, col2, col3, col4) 
        VALUES (%s, %s, %s, %s) 
        ON DUPLICATE KEY UPDATE 
            col3 = VALUES(col3), 
            col4 = VALUES(col4);
    """
    # 一次提交整批数据
    cursor.executemany(upsert_sql, values_list)
    # 手动提交事务（若未开启自动提交）
    conn.commit()

2. 利用pandas内置方法简化操作 #

用pandas的to_sql结合自定义逻辑，无需手动拆分chunk和循环，代码更简洁高效：

from sqlalchemy import create_engine

# 创建数据库连接
engine = create_engine('mysql+pymysql://用户名:密码@主机地址/数据库名')

# 自定义批量UPSERT方法
def batch_upsert(table, conn, keys, data_iter):
    # 生成批量插入模板
    value_placeholders = ', '.join(['%s'] * len(keys))
    upsert_sql = f"""
        INSERT INTO {table.name} ({', '.join(keys)})
        VALUES ({value_placeholders})
        ON DUPLICATE KEY UPDATE
            col3 = VALUES(col3),
            col4 = VALUES(col4);
    """
    conn.execute(upsert_sql, list(data_iter))

# 执行批量写入
DataFrame.to_sql(
    name='table',
    con=engine,
    if_exists='append',
    index=False,
    method=batch_upsert,
    chunksize=1000  # 按需调整批量大小，建议1000-5000条
)

3. 性能与事务优化 #

• 所有批量操作放在事务中执行，避免单条提交的开销，同时保证数据原子性。
• 调整拆分的chunk大小：不要固定拆成20份，可根据数据总量和数据库配置选择合适的chunksize（比如1000条一批）。
• 若数据库支持，可开启预编译语句，进一步提升批量执行效率。

内容的提问来源于stack exchange，提问作者Angel's Tear