我正在尝试为超过3m行优化单个列的更新。列数据存储在一个Dataframe中（行sql索引是已知的）当前我使用的代码是（使用sqlalchemy进行连接）
conn = getConnection(db).connect()
trans = conn.begin()
try:
i=0
for index, row in data.iterrows():
if not np.isnan(row[colName]):
i+=1
sql = 'update data set `{0}`= {1} where data_id={2};'.format(colName, row[colName], index)
conn.execute(sql)
if i>10000:
i = 0
trans.commit()
trans = conn.begin()
trans.commit()
except Exception as e:
trans.rollback()
conn.close()以下是一些innodb变量
innodb_buffer_pool_size = 402653184
innodb_io_capacity = 200如何优化这段代码，因为目前我遇到了多次触发回滚的情况。
1条答案
按热度按时间bjg7j2ky1#
10k是一个不合理的大块头。
UPDATE做了很多事情分析语句并决定查询计划。
准备回滚（复制更新前的行）；
更新行（读、修改、写）；
将任何辅助索引更改添加到更改缓冲区，这最终将导致对索引的写入。
我建议每丛不超过1000个
COMMIT). 1000和10000之间的效率差（假设没有超时）可能小于1%。当前代码为每个迭代执行所有4个步骤。
建议先建立tmp表，然后使用multi表
UPDATE更新可能更快，也可能更快：需要
CREATE，等等，临时表（一次/束）插入一个或多个束的行。这可以（而且应该）优化为一个
INSERT包含许多行的语句(同样，我建议不要超过1000。）如果这是一个“事务”表类型，那么ROLLBACK这里也有考虑。您保存了#1（解析），但仍然需要为更新的每一行执行#2-4。
在任何情况下，它都是更多的代码。将10000改为1000是对你的问题的简单而有效的答案。