php - 如何插入缺失行并更新10M表中已存在的行

Question

问:如何在较短的时间内插入缺失行并更新数据源中存在的行？

存在很少更新的 Postgre 表和不强大的服务器(1Gb RAM 用于 apache2 和中间 CPU)。有 10 M 条记录。有些记录可以更新，有些记录可以添加。

数据源是由 php 脚本生成的大型 CSV 文件。

表结构:

id (auto inc.)
week_number (1-54)
audience_id (int)
channel_id (int)
is_weekend (char, Y or N)
start_time (hours, offset in minutes after midnight)
rating (numberic. In fact main value)

评分字段由下一个字段集决定:week_number、audience_id、channel_id、is_weekend 和 start_time 以后我们将其称为“complex_key”。

我做了什么(php服务器端):

• 通过 foreach(批量选择)循环存在数据库数据，并将对“complex_key”=> 评分存储在 Redis 缓存中。接下来逐行与 csv 进行比较并准备新的数据集。 优化:取消我能做的一切设置，优化数组等结果:失败。内存限制。 (php端，不是redis)
• 使用Pg copy创建临时表。这个想法是使用数据库引擎来比较表并进行所需的更改。类似于 this 。 Ofc 我添加了包含“complex_key”哈希的附加字段并将其标记为索引。结果:如果我们比较 100 万以上的表，工作速度太慢。

Answer 1

使用 PHP 在事务 block (BEGIN...COMMIT) 中创建更新和插入的 SQL 文件。通过命令行 psql 加载文件。此方法不会减少获取/计算时间，但会在更新期间显着提高速度。