优化mysql多个更新
优化mysql多个更新
提问于 2018-06-16 12:07:05
我正在尝试优化超过3M行的单个列的更新。列数据存储在pandas数据帧中(行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
Stack Overflow用户
发布于 2018-06-20 04:32:37
10K是一个不合理的大块。
UPDATE做了很多事情
- 解析语句并决定查询计划。
- 准备回滚(复制行之前的状态(读取、修改、写入)；
- 将任何辅助索引更改添加到更改缓冲区，这最终将导致对索引的写入。
我建议每个簇(即每个COMMIT)不要超过1000个。1000%和10000之间的效率差异(假设没有超时)可能小于1%。
当前代码在每次迭代中执行全部4个步骤。
构建临时表，然后使用多表UPDATE进行更新的建议可能会更快，也可能不会更快：
- 需要对临时表的值或行进行
CREATE等操作。这可以(也应该)优化为包含许多行的单个INSERT语句。(同样，我建议不要超过1000个。)如果这是一个‘事务性’表类型，那么这里也有ROLLBACK方面的考虑。 - 您保存了#1 (解析)，但仍然需要为更新的每一行保存#2-4。
在任何情况下，这都是更多的代码。将10000更改为1000是对您的问题的简单而有效的回答。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50884857
复制相关文章
相似问题