PostgreSQL中数据批量导入优化方法-sql批量导入数据

现在很多企业都将数据库逐渐由Mysql转向了更加强大而且开源的PostgreSQL数据库。在数据迁移过程中，PostgreSQL数据库导入大量数据时候非常缓慢，本文我们就来说说PostgreSQL数据库批量导入数据时的优化方法和策略。

概述

考虑PostgreSQL数据库批量导入数据时性能缓慢的原因，无非有几个因素：索引，触发器，外键，GUID主键，还有可能是预写日志(WAL)。我们就从这几个影响因素着手优化。当然有可能，本文说的这些技巧都不能有效问题，遇到这样的问题时候，就需要我们具体问题具体分析，并针对性的解决。

关闭日志记录

对于PostgreSQL 9.5及更高版本，可以先将目标表更改为UNLOGGED，然后在加载数据后将其更改回LOGGED：

ALTER TABLE <target table> SET UNLOGGED 
<批量导入数据…> 
ALTER TABLE <target table> LOGGED

UNLOGGED模式可以确保PostgreSQL不会在变量导入数据时将表写操作记录到预写日志(WAL)，从而极大的优化导入过程。但是，由于未记录操作，因此如果在加载过程中发生崩溃或服务器关机等故障，则无法恢复数据。PostgreSQL重新启动后将自动截断任何未记录的表。

另外，未记录的表不会复制到备用服务器。在这种情况下，必须在加载之前删除现有的复制，并在加载之后重新创建。根据主节点中的数据量和备用数据库的数量，重建复制的时间可能会很长，对于高可用性要求来说这是不可接受的。

建议采用以下方法，将数据批量插入未记录的表中：

在将表和数据更改为未记录模式之前对其进行备份;
数据加载完成后，重新创建对备用服务器的任何复制;

对可以轻松重新填充的表使用UNLOGGED的批量插入(例如，大型查找表或维度表)。

删除索引

数据库索引可能在批量数据插入期间导致严重的延迟。因为添加数据过程，对应的索引条目需要实时更新。

建议在开始批量插入之前尽可能删除目标表中的索引，并在导入完成后重新创建索引。同样，在大型表上创建索引可能很耗时，但是比在加载过程中更新索引要快。

DROP INDEX <index_name1>, <index_name2> … <index_name_n> 
<批量导入数据…> 
CREATE INDEX <index_name> ON <target_table>(column1, …,column n)

创建索引之前，临时提高maintenance_work_mem配置参数可能会有帮助。增加的工作内存可以帮助更快地创建索引。

为了安全起见的另一种选择是使用现有数据和索引在同一数据库中复制目标表。然后，测试有索引和删除索两种情况下批量导入数据的性能对比，然后根据测试结果选择更好的方法。

删除外键

和索引一样，外键约束也会影响大批量导入的性能。因为导入过程中必须检查插入的每个行数据的每个外键是否存在相应的主键。当批量导入时，必须为每一行触发该触发器检查外键，从而增加了开销。

除非受到业务规则的限制，否则建议先从目标表中删除所有外键，在单个事务中加载数据，然后在提交事务后重新创建外键。

ALTER TABLE <target_table> 
DROP CONSTRAINT <foreign_key_constraint> 
 
 
BEGIN TRANSACTION 
<批量导入数据…> 
COMMIT 
 
 
ALTER TABLE <target_table> 
ADD CONSTRAINT <foreign key constraint> 
FOREIGN KEY (<foreign_key_field>) 
REFERENCES <parent_table>(<primary key field>)...

同样增加maintenance_work_mem配置参数也能提高重新创建外键约束的性能。

暂停触发器

INSERT或DELETE触发器(如果导入过程还涉及从目标表中删除记录)可能会导致批量数据导入延迟。这是因为每个触发器将具有需要检查的逻辑，并且需要在每行被插入或删除后立即完成操作。

建议在批量导入数据之前禁用目标表中的所有触发器，并在导入完成后再启用它们。禁用所有触发器也会强制执行外键约束检查的系统触发器。

ALTER TABLE <target table> DISABLE TRIGGER ALL 
<批量导入数据…> 
ALTER TABLE <target table> ENABLE TRIGGER ALL

使用多值INSERT

对于成批数据加载，运行数千个或数十万个INSERT语句可能是个糟糕的选择。因为查询优化器必须解析和准备每个单独的INSERT命令，然后进行所有约束检查，作为单独的事务运行并记录日志。而使用多值单个INSERT语句可以节省这些不必要的开支。

INSERT INTO <target_table> (<column1>, <column2>, …, <column_n>) 
VALUES 
(<value a>, <value b>, …, <value x>), 
(<value 1>, <value 2>, …, <value n>), 
(<value A>, <value B>, …, <value Z>), 
(<value i>, <value ii>, …, <value L>), 
...

多值INSERT性能受现有索引的影响。建议在运行命令之前先删除索引，然后再创建索引。

另一个需要注意的地方是PostgreSQL可用于运行多值INSERT的内存量。运行多值INSERT时，RAM中必须容纳大量输入值，并且除非有足够的可用内存，否则该过程可能会失败。

建议将设置effective_cache_size参数到50%，并将shared_buffer设为机器的总内存的参数设为25%。为了安全起见，将导入划分为多条的多值INSERT，每个语句的值不要超过1000行。

使用COPY命令

建议使用PostgreSQL COPY命令从一个或多个文件导入数据。COPY针对批量数据导入会进行额外的优化，比运行大量INSERT语句甚至多值INSERTS的都要快。

COPY <target table> [( column1>, … , <column_n>)] 
FROM '<文件路径>' 
WITH (<option1>, <option2>, … , <option_n>)

使用COPY的还有很多的优势：

它支持文本和二进制文件导入;
本质上是事务性的;
它允许指定输入文件的结构;
它可以使用WHERE子句有条件地导入数据。

运行ANALYZ

这与提高批量数据导入性能无关，但是强烈建议在批量导入之后立即在目标表上运行ANALYZE命令。大量的新导入的行将大大改变数据表中列中的数据分布，并且会使表的统计信息都过时。当用查询优化器使用过时的统计信息时，查询性能可能会非常慢。运行ANALYZE命令将确保更新统计信息。

总结

对于数据库应用程序来说，可能并非每天都会进行批量数据导入，但是在运行时会对查询性能产生影响。这就是为什么有必要尽可能缩短导入时间。DBA可以最大程度地减少意外的事情之一就是在具有类似服务器规格和PostgreSQL配置的开发或准线上环境中进行性能测试并进行优化。每种数据加载方案都是不同的，最好尝试每种方法并找到最好最快的方法。