如何使Python/PostgreSQL更快?

不要浪费时间分析。时间总是在数据库操作中。尽量少做。只是插入的最小数量。

三件事。

一不要反复选择以符合日期、主机名和人员维度。将所有数据一次性提取到Python字典中并在内存中使用。不要重复进行单例选择。使用Python。

二不要更新。

具体来说,不要这样做。这是一个糟糕的代码,有两个原因。

cursor.execute("UPDATE people SET chats_count = chats_count + 1 WHERE id = '%s'" % person_id)

它可以替换为一个简单的SELECT COUNT(*)FROM。从不更新以增加计数。只需使用SELECT语句对其中的行进行计数。[如果无法使用简单的SELECT COUNT或SELECT COUNT(DISTINCT)进行此操作,则会丢失一些数据--您的数据模型应始终提供正确的完整计数。永远不要更新。]

SELECT COUNT(*) 速度不够快(先进行基准测试,然后再执行任何蹩脚的操作),您可以将计数结果缓存到另一个表中。在所有的负载之后。做一个 SELECT COUNT(*) FROM whatever GROUP BY whatever 并将其插入计数表中。不要更新。曾经

cursor.execute( "INSERT INTO ... VALUES( %(x)s, %(y)s, %(z)s )", {'x':person_id, 'y':time_to_string(time), 'z':channel,} )

SQL永远不会改变。绑定的值会更改,但SQL永远不会更改。这要快得多。永远不要动态构建SQL语句。从不

在sql语句中使用绑定变量而不是文字值,并为 每个sql语句都是唯一的,因此下次使用该语句时无需重新分析。从python db api文档:

操作(查询或命令)。 参数可按顺序提供 操作中的变量。变量 在特定于数据库的 符号(参见模块的参数样式

将创建对该操作的引用 由光标保留。如果相同 然后光标可以优化它的属性 行为这是最有效的 执行相同操作的算法 已使用,但使用了不同的参数 受它约束(多次)。

始终使用绑定变量。

在for循环中,您重复地插入到“chats”表中,因此您只需要一个带有绑定变量的sql语句,就可以用不同的值执行。所以你可以把它放在for循环之前:

insert_statement="""
    INSERT INTO chats(person_id, message_type, created_at, channel)
    VALUES(:person_id,:message_type,:created_at,:channel)
"""

然后,代替执行的每个sql语句,将以下内容放在适当的位置:

cursor.execute(insert_statement, person_id='person',message_type='msg',created_at=some_date, channel=3)

这将使事情运行得更快,因为:

1. 游标对象不必每次都重新分析语句
2. db服务器不必生成新的执行计划,因为它可以使用以前创建的执行计划。
3. 您不必调用santitize(),因为绑定变量中的特殊字符不会成为执行的sql语句的一部分。

注意:我使用的绑定变量语法是特定于Oracle的。您必须查看psycopg2库的文档以了解确切的语法。

其他优化:

1. 在每次循环迭代之后,您将使用“更新人员集聊天室”进行递增。让字典将用户映射到chat_count,然后执行您所看到的总数的语句。这将比在每次记录后命中db更快。
2. 对所有查询使用绑定变量。不仅仅是insert语句,我选择它作为示例。
3. 更改所有执行数据库查找的find_*()函数以缓存其结果,这样它们就不必每次都命中数据库。

正如Mark所建议的,使用绑定变量。数据库只需准备每条语句一次,然后为每次执行“填空”。作为一个很好的副作用,它将自动处理字符串引用问题(您的程序没有处理)。

您的log\u主机名、log\u person和log\u date函数正在表上进行不必要的选择。使适当的表属性主键或唯一。然后,不要在插入前检查密钥的存在,只需执行插入即可。如果person/date/hostname已经存在,则插入将因违反约束而失败。(如上所述,如果使用具有单个提交的事务,这将不起作用。)

或者,如果您知道只有您一个人在程序运行时插入表,那么在内存中创建并行数据结构,并在执行插入时在内存中维护它们。例如,在程序开始时,将表中的所有主机名读入关联数组。当您想知道是否要执行插入时,只需执行数组查找。如果未找到条目,请执行插入操作并相应地更新数组。(此建议与事务和单个提交兼容,但需要更多的编程。不过速度会快得多。)

除了@Mark Roddy给出的许多好建议外,请执行以下操作:

• 不要使用 readlines ,您可以迭代文件对象
• 尝试使用 executemany 而不是 execute :尝试批量插入而不是单次插入,这往往更快,因为开销更小。它还减少了提交的数量
• str.rstrip 将很好地工作,而不是用正则表达式剥离新行

批处理插入将暂时占用更多内存,但如果不将整个文件读入内存,这应该没问题。