現在很多企業都將數據庫逐漸由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配置的開發或準線上環境中進行性能測試並進行優化。每種數據加載方案都是不同的，最好嘗試每種方法並找到最好最快的方法。

相關文章