PostgreSQL中怎么批量導(dǎo)入數(shù)據(jù)

這篇文章將為大家詳細(xì)講解有關(guān)PostgreSQL中怎么批量導(dǎo)入數(shù)據(jù)，文章內(nèi)容質(zhì)量較高，因此小編分享給大家做個(gè)參考，希望大家閱讀完這篇文章后對(duì)相關(guān)知識(shí)有一定的了解。

概述

考慮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ù)&hellip;> 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ì)很長，對(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> &hellip; <index_name_n> <批量導(dǎo)入數(shù)據(jù)&hellip;> CREATE INDEX <index_name> ON <target_table>(column1, &hellip;,column n)

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

為了安全起見的另一種選擇是使用現(xiàn)有數(shù)據(jù)和索引在同一數(shù)據(jù)庫中復(fù)制目標(biāo)表。然后，測試有索引和刪除索兩種情況下批量導(dǎo)入數(shù)據(jù)的性能對(duì)比，然后根據(jù)測試結(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ù)&hellip;> 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ù)&hellip;> 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>, &hellip;, <column_n>) VALUES (<value a>, <value b>, &hellip;, <value x>), (<value 1>, <value 2>, &hellip;, <value n>), (<value A>, <value B>, &hellip;, <value Z>), (<value i>, <value ii>, &hellip;, <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>, &hellip; , <column_n>)] FROM '<文件路徑>' WITH (<option1>, <option2>, &hellip; , <option_n>)

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

• 它支持文本和二進(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ì)信息。

關(guān)于PostgreSQL中怎么批量導(dǎo)入數(shù)據(jù)就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，可以學(xué)到更多知識(shí)。如果覺得文章不錯(cuò)，可以把它分享出去讓更多的人看到。