現(xiàn)在很多企業(yè)都將數(shù)據(jù)庫逐漸由Mysql轉(zhuǎn)向了更加強大而且開源的PostgreSQL數(shù)據(jù)庫。在數(shù)據(jù)遷移過程中，PostgreSQL數(shù)據(jù)庫導入大量數(shù)據(jù)時候非常緩慢，本文我們就來說說PostgreSQL數(shù)據(jù)庫批量導入數(shù)據(jù)時的優(yōu)化方法和策略。

概述

考慮PostgreSQL數(shù)據(jù)庫批量導入數(shù)據(jù)時性能緩慢的原因，無非有幾個因素：索引，觸發(fā)器，外鍵，GUID主鍵，還有可能是預(yù)寫日志(WAL)。我們就從這幾個影響因素著手優(yōu)化。當然有可能，本文說的這些技巧都不能有效問題，遇到這樣的問題時候，就需要我們具體問題具體分析，并針對性的解決。

關(guān)閉日志記錄

對于PostgreSQL 9.5及更高版本，可以先將目標表更改為UNLOGGED，然后在加載數(shù)據(jù)后將其更改回LOGGED：

ALTER TABLE <target table> SET UNLOGGED 
<批量導入數(shù)據(jù)…> 
ALTER TABLE <target table> LOGGED 

UNLOGGED模式可以確保PostgreSQL不會在變量導入數(shù)據(jù)時將表寫操作記錄到預(yù)寫日志(WAL)，從而極大的優(yōu)化導入過程。但是，由于未記錄操作，因此如果在加載過程中發(fā)生崩潰或服務(wù)器關(guān)機等故障，則無法恢復數(shù)據(jù)。PostgreSQL重新啟動后將自動截斷任何未記錄的表。

另外，未記錄的表不會復制到備用服務(wù)器。在這種情況下，必須在加載之前刪除現(xiàn)有的復制，并在加載之后重新創(chuàng)建。根據(jù)主節(jié)點中的數(shù)據(jù)量和備用數(shù)據(jù)庫的數(shù)量，重建復制的時間可能會很長，對于高可用性要求來說這是不可接受的。

建議采用以下方法，將數(shù)據(jù)批量插入未記錄的表中：

• 在將表和數(shù)據(jù)更改為未記錄模式之前對其進行備份;
• 數(shù)據(jù)加載完成后，重新創(chuàng)建對備用服務(wù)器的任何復制;

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

刪除索引

數(shù)據(jù)庫索引可能在批量數(shù)據(jù)插入期間導致嚴重的延遲。因為添加數(shù)據(jù)過程，對應(yīng)的索引條目需要實時更新。

建議在開始批量插入之前盡可能刪除目標表中的索引，并在導入完成后重新創(chuàng)建索引。同樣，在大型表上創(chuàng)建索引可能很耗時，但是比在加載過程中更新索引要快。

DROP INDEX <index_name1>, <index_name2> … <index_name_n> 
<批量導入數(shù)據(jù)…> 
CREATE INDEX <index_name> ON <target_table>(column1, …,column n) 

創(chuàng)建索引之前，臨時提高maintenance_work_mem配置參數(shù)可能會有幫助。增加的工作內(nèi)存可以幫助更快地創(chuàng)建索引。

為了安全起見的另一種選擇是使用現(xiàn)有數(shù)據(jù)和索引在同一數(shù)據(jù)庫中復制目標表。然后，測試有索引和刪除索兩種情況下批量導入數(shù)據(jù)的性能對比，然后根據(jù)測試結(jié)果選擇更好的方法。

刪除外鍵

和索引一樣，外鍵約束也會影響大批量導入的性能。因為導入過程中必須檢查插入的每個行數(shù)據(jù)的每個外鍵是否存在相應(yīng)的主鍵。當批量導入時，必須為每一行觸發(fā)該觸發(fā)器檢查外鍵，從而增加了開銷。

除非受到業(yè)務(wù)規(guī)則的限制，否則建議先從目標表中刪除所有外鍵，在單個事務(wù)中加載數(shù)據(jù)，然后在提交事務(wù)后重新創(chuàng)建外鍵。

ALTER TABLE <target_table> 
DROP CONSTRAINT <foreign_key_constraint> 
 
 
BEGIN TRANSACTION 
<批量導入數(shù)據(jù)…> 
COMMIT 
 
 
ALTER TABLE <target_table> 
ADD CONSTRAINT <foreign key constraint> 
FOREIGN KEY (<foreign_key_field>) 
REFERENCES <parent_table>(<primary key field>)... 

同樣增加maintenance_work_mem配置參數(shù)也能提高重新創(chuàng)建外鍵約束的性能。

暫停觸發(fā)器

INSERT或DELETE觸發(fā)器(如果導入過程還涉及從目標表中刪除記錄)可能會導致批量數(shù)據(jù)導入延遲。這是因為每個觸發(fā)器將具有需要檢查的邏輯，并且需要在每行被插入或刪除后立即完成操作。

建議在批量導入數(shù)據(jù)之前禁用目標表中的所有觸發(fā)器，并在導入完成后再啟用它們。禁用所有觸發(fā)器也會強制執(zhí)行外鍵約束檢查的系統(tǒng)觸發(fā)器。

ALTER TABLE <target table> DISABLE TRIGGER ALL 
<批量導入數(shù)據(jù)…> 
ALTER TABLE <target table> ENABLE TRIGGER ALL 

使用多值INSERT

對于成批數(shù)據(jù)加載，運行數(shù)千個或數(shù)十萬個INSERT語句可能是個糟糕的選擇。因為查詢優(yōu)化器必須解析和準備每個單獨的INSERT命令，然后進行所有約束檢查，作為單獨的事務(wù)運行并記錄日志。而使用多值單個INSERT語句可以節(jié)省這些不必要的開支。

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性能受現(xiàn)有索引的影響。建議在運行命令之前先刪除索引，然后再創(chuàng)建索引。

另一個需要注意的地方是PostgreSQL可用于運行多值INSERT的內(nèi)存量。運行多值INSERT時，RAM中必須容納大量輸入值，并且除非有足夠的可用內(nèi)存，否則該過程可能會失敗。

建議將設(shè)置effective_cache_size參數(shù)到50%，并將shared_buffer設(shè)為機器的總內(nèi)存的參數(shù)設(shè)為25%。為了安全起見，將導入劃分為多條的多值INSERT，每個語句的值不要超過1000行。

使用COPY命令

建議使用PostgreSQL COPY命令從一個或多個文件導入數(shù)據(jù)。COPY針對批量數(shù)據(jù)導入會進行額外的優(yōu)化，比運行大量INSERT語句甚至多值INSERTS的都要快。

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

使用COPY的還有很多的優(yōu)勢：

• 它支持文本和二進制文件導入;
• 本質(zhì)上是事務(wù)性的;
• 它允許指定輸入文件的結(jié)構(gòu);
• 它可以使用WHERE子句有條件地導入數(shù)據(jù)。

運行ANALYZ

這與提高批量數(shù)據(jù)導入性能無關(guān)，但是強烈建議在批量導入之后立即在目標表上運行ANALYZE命令。大量的新導入的行將大大改變數(shù)據(jù)表中列中的數(shù)據(jù)分布，并且會使表的統(tǒng)計信息都過時。當用查詢優(yōu)化器使用過時的統(tǒng)計信息時，查詢性能可能會非常慢。運行ANALYZE命令將確保更新統(tǒng)計信息。

總結(jié)

對于數(shù)據(jù)庫應(yīng)用程序來說，可能并非每天都會進行批量數(shù)據(jù)導入，但是在運行時會對查詢性能產(chǎn)生影響。這就是為什么有必要盡可能縮短導入時間。DBA可以最大程度地減少意外的事情之一就是在具有類似服務(wù)器規(guī)格和PostgreSQL配置的開發(fā)或準線上環(huán)境中進行性能測試并進行優(yōu)化。每種數(shù)據(jù)加載方案都是不同的，最好嘗試每種方法并找到最好最快的方法。