数据库查询优化

Question

好了，我的巨人朋友们，我再一次在你的肩膀上寻找一点空间：

问题是，我有一个python脚本，它正在修复一些数据库问题，但它花费的时间太长了，主要的update语句如下：

cursor.execute("UPDATE jiveuser SET username = '%s' WHERE userid = %d" % (newName,userId))

使用不同的newName和userid对调用了大约9500次...

对如何加快这一过程有什么建议吗？也许有一种方法可以让我只用一个查询就能完成所有更新？

任何帮助都将不胜感激！

PS: Postgres是正在使用的db。

Answer 1

将所有数据插入到另一个空表中(例如，名为userchange)，然后在单个批处理中进行更新：

UPDATE jiveuser
SET username = userchanges.username
FROM userchanges
WHERE userchanges.userid = jiveuser.userid
    AND userchanges.username <> jiveuser.username

有关批量加载数据的信息，请参阅the COPY command上的文档。

还有tips for improving performance when populating a database。

Answer 2

花费这么长时间的原因可能是您启用了自动提交，并且每次更新都在其自己的事务中完成。

这很慢，因为即使你有一个电池后备的raid控制器(当然，你肯定应该在所有的数据库服务器上都有)，它仍然需要在每次提交事务时写入该设备，以确保持久性。

解决方案是在每个事务中执行多个行。但是不要把交易做得太大，否则你也会遇到问题。试着每提交10,000行更改作为粗略的猜测。

Answer 3

您可能需要查看executemany()：Information here