数据库查询优化
好我的巨人朋友我再次寻求在你肩膀上的小空间:P数据库查询优化
这里的问题,我有一个固定的一些数据库问题Python脚本,但它走的时间太长,主要更新语句是这样的:
cursor.execute("UPDATE jiveuser SET username = '%s' WHERE userid = %d" % (newName,userId))
,这是获得所谓的不同了newName和用户ID对约9500倍......
就如何加快这一进程有什么建议?也许某种方式可以让我只用一个查询就可以完成所有更新?
任何帮助将不胜感激!
PS:Postgres是正在使用的数据库。
插入所有的数据到另一个空表(称为userchanges,说的)然后更新单批:
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。
首先,做不使用%运算符来构建你的SQL。相反,通过你的参数元组的第二个参数cursor.execute
,这也否定了需要引用你的论点,并允许您使用%s的一切:
cursor.execute("UPDATE jiveuser SET username = %s WHERE userid = %s", (newName, userId))
这一点很重要,以防止SQL Injection attacks。
要回答你的问题,你可以通过创建在userid
列的索引,这将允许数据库在O(1)
不断及时更新,而不必扫描整个数据库表,这是O(n)
加快这些更新。既然你使用PostgreSQL,这里是创建索引的语法:
CREATE INDEX username_lookup ON jiveuser (userid);
编辑:由于您的评论表明您已经对userid
列的索引,有没有什么你可能做,以加快该查询。所以你的主要选择或者生活缓慢,因为这听起来像是一次性的修复 - 破碎的东西,或者按照VeeArr
的建议和测试cursor.executemany是否会给你足够的提升。
也许你可以在用户ID上创建一个索引来加快速度。
我会做一个解释。如果它正在进行索引查找以查找记录 - 如果您在userid上有索引,那么它应该是这样 - 然后我看不到您可以采取哪些措施来提高性能。如果它不使用索引,那么诀窍就是搞清楚为什么不可以修复它。
哦,你可以尝试使用准备好的语句。有了9500插入,这应该有所帮助。
花费这么长时间的原因可能是您启用了自动提交功能,并且每次更新都会在自己的事务中完成。
这很慢,因为即使您拥有电池供电的RAID控制器(当然,您应该在所有数据库服务器上拥有该控制器),但仍需要对每个事务提交进行写入,以确保耐用性。
解决方案是对每个事务执行多个行。但是不要让交易太多,否则你也会遇到问题。尝试提交每10,000行更改作为粗略猜测。
首先确保您有“用户ID”的指数,这将确保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
你能发布的“输出EXPLAIN ANALYZE “? –
2010-06-03 17:57:15
你能否介绍一下多久太久了?十秒钟,五个小时? – Anonymoose 2010-06-06 20:19:26
@Anonymouse我需要它运行在2.5小时,它目前正在采取5 – hdx 2010-06-07 23:20:05