我的MariaDB批量更新2800万行数据是否存在瓶颈？该如何优化？

10万行更新耗时50秒，每秒仅约2000次操作，对于本地数据库的主键匹配更新来说确实偏慢，正常场景下应该能达到每秒数万次的更新效率，以下是可落地的优化方向：

• 使用预编译语句游标
  默认游标可能会把executemany拆解为单条语句循环执行，换成预编译游标可以真正批量发送请求，减少交互开销。示例：

  from mariadb import connect, cursors
  
  conn = connect(...)
  cursor = conn.cursor(cursorclass=cursors.CursorPrepared)  # 启用预编译游标
  update_query = "UPDATE table SET column = %s WHERE `index` = %s"
  %time cursor.executemany(update_query, update_data)
  conn.commit()
  

• 调整批次大小并手动管理事务
  10万行的批次不一定是最优值，可以测试20万、50万等不同批次（注意内存占用上限）。同时关闭自动提交，手动批量提交事务，减少事务日志刷写的开销：

  conn.autocommit = False
  try:
      cursor.executemany(update_query, update_data)
      conn.commit()
  except Exception as e:
      conn.rollback()
      raise e
  

• 临时禁用索引与约束
  你更新的column字段建有索引，每次更新都会触发索引维护，这是核心性能瓶颈之一：

  • 若使用InnoDB引擎，临时关闭唯一检查和外键检查：

    SET UNIQUE_CHECKS = 0;
    SET FOREIGN_KEY_CHECKS = 0;
    -- 执行更新操作
    SET UNIQUE_CHECKS = 1;
    SET FOREIGN_KEY_CHECKS = 1;
    

  • 若使用MyISAM引擎，可直接禁用/重建索引：

    ALTER TABLE table DISABLE KEYS;
    -- 执行更新操作
    ALTER TABLE table ENABLE KEYS;
    

• 用LOAD DATA + 关联更新替代executemany
  这是效率最高的批量更新方式，利用数据库原生的批量导入能力：

  1. 将更新数据保存为CSV文件（格式：index值,column新值）
  2. 创建临时表并导入数据：

     CREATE TEMPORARY TABLE temp_update (
         `index` INT PRIMARY KEY,
         column VARCHAR(255)  -- 与原表字段类型保持一致
     );
     LOAD DATA LOCAL INFILE '/path/to/update_data.csv' 
     INTO TABLE temp_update 
     FIELDS TERMINATED BY ',' 
     LINES TERMINATED BY '\n'
     (`index`, column);
     

  3. 关联原表批量更新：

     UPDATE table t 
     JOIN temp_update tu ON t.`index` = tu.`index` 
     SET t.column = tu.column;
     

• 调整本地数据库配置
  修改my.cnf（或my.ini）配置，提升写入性能：

  • 增大innodb_buffer_pool_size：设置为物理内存的50%-70%，让更多数据缓存到内存，减少磁盘IO
  • 调大innodb_log_file_size：比如设置为1G，减少日志刷写频率
  • 临时设置innodb_flush_log_at_trx_commit=2：牺牲极小的数据安全性（仅本地操作可接受），大幅提升写入速度

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