数据库查询优化

Question

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

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

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

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

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

任何帮助将不胜感激！

PS：Postgres是正在使用的数据库。

Answer 1

将所有数据插入另一个空表（例如，称为 userchanges），然后在单个批次中进行更新：

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

请参阅 COPY 命令 用于批量加载数据。

还有填充数据库时提高性能的提示。

Answer 2

首先，不要使用 % 运算符来构建 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 会给你足够的提升。

Answer 3

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

这很慢，因为即使您有一个电池支持的 raid 控制器（当然，您绝对应该在所有数据库服务器上都有该控制器），它仍然需要为每个事务提交写入该设备以确保持久性。

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

Answer 4

您可能需要查看 executemany()：信息 这里

Answer 5

也许您可以在用户 ID 上创建索引来加快速度。

Answer 6

我会对此做一个解释。如果它正在执行索引查找来查找记录（如果您在 userid 上有索引，则应该这样做），那么我看不出您可以采取哪些措施来提高性能。如果它不使用索引，那么诀窍就是找出不使用索引的原因并修复它。

哦，你可以尝试使用准备好的语句。有了 9500 个插入件，这应该会有所帮助。

Answer 7

将其移至存储过程并从数据库自身执行它。

Answer 8

首先确保您在“userid”上有一个索引，这将确保 dbms 不必每次都进行表扫描。

CREATE INDEX jiveuser_userid ON jiveuser (userid);

下一步尝试准备语句，然后对其调用执行。这将使优化器不必每次都检查查询

PREPARE update_username(string,integer) AS UPDATE jiveuser SET username = $1 WHERE userid = $2;
EXECUTE update_username("New Name", 123);

最后，通过关闭自动提交可以挤出更多的性能

\set autocommit off