SQL -优化大容量插入和大联接的性能？

Question

我正在将日志文件的ETL操作到PostgreSQL数据库中，并希望了解更多用于优化将数据加载到简单星型模式的性能的各种方法。

为了将这个问题放在上下文中，下面是我目前所做工作的概述：

records)

• Re-create

1. 删除所有外键和唯一约束，
2. 导入数据(~1亿
3. 的约束并在事实表上运行分析。

)

导入数据是通过从文件中加载来完成的。对于每个文件：

1)使用副本( PostgreSQL大容量上传工具)将数据加载到临时表中。

2)使用插入的新数据更新9个维度表中的每个表，例如：

INSERT INTO host (name)
SELECT DISTINCT host_name FROM temp_table
EXCEPT
SELECT name FROM host;
ANALYZE host;

分析是在INSERT末尾运行的，目的是在数以千万计的更新过程中保持统计数据的最新(这是明智的还是必要的？至少它似乎没有显著降低性能)。

3)然后用一个不神圣的9路连接更新事实表：

INSERT INTO event (time, status, fk_host, fk_etype, ... ) 
SELECT t.time, t.status, host.id, etype.id ... 
FROM temp_table as t 
JOIN host ON t.host_name = host.name
JOIN url ON t.etype = etype.name
... and 7 more joins, one for each dimension table

有没有更好的方法让我视而不见？

Answer 1

我尝试过几种不同的方法来尝试标准化从源输入的数据，通常我发现您现在使用的方法是我的选择。它容易遵循和小的变化保持小。在第2阶段中，尝试从维度表中返回生成的id只会产生复杂的事情，并且通常会生成太多的小查询，无法有效地处理大型数据集。Postgres应该是非常有效的，你的“邪恶连接”在现代版本和使用“选择不同，除了选择”对我很好的工作。其他人可能更了解，但我发现你目前的方法是我的推想方法。

Answer 2

在第二阶段中，您知道每个维度的主键(在您插入数据之后)，但是您正在丢弃这个信息，然后在第3阶段使用“邪恶”9路连接重新发现它。

相反，我建议创建一个sproc，以便插入到事实表中；例如，insertXXXFact(...)，它按照命名约定getOrInsertXXXDim调用许多其他的sproc(每个维度一个)，其中XXX是所讨论的维度。这些链式程序中的每一个都将查找或插入给定维度的新行(从而确保引用的完整性)，并且应该返回事实表应该引用的维度的主键。这将大大减少您在第3阶段中需要做的工作，该阶段现在简化为表单insert into XXXFact values (DimPKey1, DimPKey2, ... etc.)的调用。

我们在getOrInsertXXX sprocs中采用的方法是，如果一个虚拟值不可用，则插入一个虚拟值，并在以后有一个单独的清理过程来识别和充实这些值。