数据库查询优化

2 投票

8 回答

2690 浏览

提问于 2025-04-15 23:31

好的，我的朋友们，我又来请教你们了 :P

我遇到一个问题，我有一个Python脚本在修复一些数据库的问题，但它运行得太慢了，主要的更新语句是这个：

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

这个语句大约被调用了9500次，每次用不同的新名字和用户ID组合……

有没有什么建议可以加快这个过程？也许有办法让我只用一个查询就能完成所有更新？

任何帮助都非常感谢！

PS：我使用的数据库是Postgres。

批量更新 SQL查询数据库设计数据库优化 postgres 查询性能

8 个回答

首先，不要用 % 操作符来构建你的 SQL 语句。相反，应该把你的参数元组作为第二个参数传给 cursor.execute，这样就不需要手动给参数加引号了，而且可以用 %s 来表示所有的参数：

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

这样做很重要，可以防止 SQL 注入攻击。

针对你的问题，你可以通过在 userid 列上创建一个索引来加快更新速度，这样数据库就可以在 O(1) 的常数时间内完成更新，而不是扫描整个数据库表，这样的时间复杂度是 O(n)。既然你使用的是 PostgreSQL，这里是创建索引的语法：

CREATE INDEX username_lookup ON jiveuser (userid);

编辑：由于你的评论显示你已经在 userid 列上创建了索引，所以你可能没有太多办法来加速这个查询。因此，你的主要选择是接受这种慢速，因为这听起来像是一次性修复某个问题，或者按照 VeeArr 的建议，测试一下 cursor.executemany 是否能给你带来足够的提升。

回答于 2025-04-15 由 Python大师

分享举报

之所以花这么长时间，可能是因为你开启了自动提交功能，每次更新都在自己的事务中进行。

这样做很慢，因为即使你有一个带电池的RAID控制器（当然，所有数据库服务器上都应该有这个），每次事务提交时，系统还是需要对这个设备进行写入，以确保数据的持久性。

解决办法是每个事务处理多于一行的数据。但也不要把事务做得太大，否则会遇到其他问题。可以尝试每处理10,000行的更改就提交一次，作为一个粗略的估计。

回答于 2025-04-15 由 Python大师

分享举报

把所有数据插入到另一个空表里（我们叫它 userchanges），然后一次性更新：

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

可以查看这个文档，了解如何使用 COPY 命令来批量加载你的数据。

还有一些提高数据库填充性能的技巧。

回答于 2025-04-15 由 Python大师

分享举报

数据库查询优化

8 个回答

撰写回答