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

概述

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

關(guān)閉日志記錄

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

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

UNLOGGED模式可以確保PostgreSQL不會(huì)在變量導(dǎo)入數(shù)據(jù)時(shí)將表寫操作記錄到預(yù)寫日志(WAL)，從而極大的優(yōu)化導(dǎo)入過程。但是，由于未記錄操作，因此如果在加載過程中發(fā)生崩潰或服務(wù)器關(guān)機(jī)等故障，則無法恢復(fù)數(shù)據(jù)。PostgreSQL重新啟動(dòng)后將自動(dòng)截?cái)嗳魏挝从涗浀谋怼?/p>

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

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

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

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

刪除索引

數(shù)據(jù)庫索引可能在批量數(shù)據(jù)插入期間導(dǎo)致嚴(yán)重的延遲。因?yàn)樘砑訑?shù)據(jù)過程，對(duì)應(yīng)的索引條目需要實(shí)時(shí)更新。

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

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

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

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

刪除外鍵

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

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

ALTER TABLE <target_table> 
DROP CONSTRAINT <foreign_key_constraint> 
 
 
BEGIN TRANSACTION 
<批量導(dǎo)入數(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ā)器(如果導(dǎo)入過程還涉及從目標(biāo)表中刪除記錄)可能會(huì)導(dǎo)致批量數(shù)據(jù)導(dǎo)入延遲。這是因?yàn)槊總€(gè)觸發(fā)器將具有需要檢查的邏輯，并且需要在每行被插入或刪除后立即完成操作。

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

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

使用多值INSERT

對(duì)于成批數(shù)據(jù)加載，運(yùn)行數(shù)千個(gè)或數(shù)十萬個(gè)INSERT語句可能是個(gè)糟糕的選擇。因?yàn)椴樵儍?yōu)化器必須解析和準(zhǔn)備每個(gè)單獨(dú)的INSERT命令，然后進(jìn)行所有約束檢查，作為單獨(dú)的事務(wù)運(yùn)行并記錄日志。而使用多值單個(gè)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)有索引的影響。建議在運(yùn)行命令之前先刪除索引，然后再創(chuàng)建索引。

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

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

使用COPY命令

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

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

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

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

運(yùn)行ANALYZ

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

總結(jié)

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