在使用 PostgreSQL 的時(shí)候，我們某些時(shí)候會(huì)往庫(kù)里插入大量數(shù)據(jù)，例如，導(dǎo)入測(cè)試數(shù)據(jù)，導(dǎo)入業(yè)務(wù)數(shù)據(jù)等等。本篇文章介紹了在導(dǎo)入大量數(shù)據(jù)時(shí)的一些可供選擇的優(yōu)化手段?？梢越Y(jié)合自己的情況進(jìn)行選擇。

一、關(guān)閉自動(dòng)提交

關(guān)閉自動(dòng)提交，并且只在每次 (數(shù)據(jù)拷貝) 結(jié)束的時(shí)候做一次提交。

如果允許每個(gè)插入都獨(dú)立地提交，那么 PostgreSQL 會(huì)為所增加的每行記錄做大量的處理。而且在一個(gè)事務(wù)里完成所有插入的動(dòng)作的最大的好處就是，如果有一條記錄插入失敗， 那么，到該點(diǎn)為止的所有已插入記錄都將被回滾，這樣就不會(huì)面對(duì)只有部分?jǐn)?shù)據(jù)，數(shù)據(jù)不完整的問(wèn)題。

postgres=#  echo :AUTOCOMMITon

postgres=# set AUTOCOMMIT offpostgres=#  echo :AUTOCOMMIToff

二、導(dǎo)入階段不創(chuàng)建索引，或者導(dǎo)入階段刪除索引

如果你正導(dǎo)入一張表的數(shù)據(jù)，最快的方法是創(chuàng)建表，用 COPY 批量導(dǎo)入，然后創(chuàng)建表需要的索引。在已存在數(shù)據(jù)的表上創(chuàng)建索引要比遞增地更新表的每一行記錄要快。

如果你對(duì)現(xiàn)有表增加大量的數(shù)據(jù)，可以先刪除索引，導(dǎo)入表的數(shù)據(jù)，然后重新創(chuàng)建索引。當(dāng)然，在缺少索引的期間，其它數(shù)據(jù)庫(kù)用戶的數(shù)據(jù)庫(kù)性能將有負(fù)面的影響。并且我們?cè)趧h除唯一索引之前還需要仔細(xì)考慮清楚，因?yàn)槲ㄒ患s束提供的錯(cuò)誤檢查在缺少索引的時(shí)候會(huì)消失。（慎重考慮索引帶來(lái)的影響）

三、刪除外鍵約束

和索引一樣，整體地檢查外鍵約束比檢查遞增的數(shù)據(jù)行更高效。所以我們也可以刪除外鍵約束，導(dǎo)入表地?cái)?shù)據(jù)，然后重建約束會(huì)更高效。

四、增大 maintenance_work_mem

在裝載大量的數(shù)據(jù)的時(shí)候，臨時(shí)增大 maintenance_work_mem 可以改進(jìn)性能。這個(gè)參數(shù)也可以幫助加速 CREATE INDEX 和 ALTER TABLE ADD FOREIGN KEY 命令。它不會(huì)對(duì) COPY 本身有很大作用，但是它可以加速創(chuàng)建索引和外鍵約束。

postgres=# show maintenance_work_mem; maintenance_work_mem

----------------------

 64MB

(1 row)

五、單值 insert 改多值 insert

減少 SQL 解析的時(shí)間。

六、關(guān)閉歸檔模式并降低 wal 日志級(jí)別

當(dāng)使用 WAL 歸檔或流復(fù)制向一個(gè)安裝中錄入大量數(shù)據(jù)時(shí)，在導(dǎo)入數(shù)據(jù)結(jié)束時(shí)，執(zhí)行一次新的 basebackup 比執(zhí)行一次增量 WAL 更快。

為了防止錄入時(shí)的增量 WAL，可以將 wal_level 暫時(shí)調(diào)整為 minimal， archive_modet 關(guān)閉，max_wal_senders 設(shè)置為 0 來(lái)禁用歸檔和流復(fù)制。但需修改這些設(shè)置需要重啟服務(wù)。

postgres=# show wal_level; wal_level

-----------

 minimal

(1 row)

postgres=# show  archive_mode; archive_mode

--------------

 off

(1 row)

postgres=# show max_wal_senders; max_wal_senders

-----------------

 0

(1 row)

七、增大 max_wal_size

臨時(shí)增大 max_wal_size 配置變量也可以讓大量數(shù)據(jù)載入更快。這是因?yàn)橄?PostgreSQL 中載入大量的數(shù)據(jù)將導(dǎo)致檢查點(diǎn)的發(fā)生比平常（由 checkpoint_timeout 配置變量指定）更頻繁。

發(fā)生檢查點(diǎn)時(shí)，所有臟頁(yè)都必須被刷寫到磁盤上。通過(guò)在批量數(shù)據(jù)載入時(shí)臨時(shí)增加 max_wal_size，減少檢查點(diǎn)的數(shù)目。

postgres=# show max_wal_size; max_wal_size

--------------

 1GB

(1 row)

八、使用 copy 替代 insert

COPY 針對(duì)批量數(shù)據(jù)加載進(jìn)行了優(yōu)化。

COPY 命令是為裝載數(shù)量巨大的數(shù)據(jù)行優(yōu)化過(guò)的；它沒 INSERT 那么靈活，但是在大量裝載數(shù)據(jù)的情況下，導(dǎo)致的荷載也少很多。因?yàn)?COPY 是單條命令，因此填充表的時(shí)候就沒有必要關(guān)閉自動(dòng)提交了。

如果不能使用 COPY，可以使用 PREPARE 來(lái)創(chuàng)建一個(gè)預(yù)備 INSERT，然后使用 EXECUTE 多次效率更高。這樣就避免了重復(fù)分析和規(guī)劃 INSERT 的開銷。

九、禁用觸發(fā)器

導(dǎo)入數(shù)據(jù)之前先 DISABLE 掉相關(guān)表上的觸發(fā)器，導(dǎo)入完成后重新讓他 ENABLE。

ALTER TABLE tab_1 DISABLE TRIGGER ALL;

導(dǎo)入數(shù)據(jù)ALTER TABLE tab_1 ENABLE TRIGGER ALL;

十、相關(guān)導(dǎo)數(shù)工具：pg_bulkload

pg_bulkload 是 PostgreSQL 的一個(gè)高速數(shù)據(jù)加載工具，相對(duì)于 copy 命令。最大的優(yōu)勢(shì)是速度。在 pg_bulkload 的直接模式下，它將跳過(guò)共享緩沖區(qū)和 WAL 緩沖區(qū)，直接寫入文件。它還包括數(shù)據(jù)恢復(fù)功能，可在導(dǎo)入失敗時(shí)進(jìn)行恢復(fù)。

地址：https://github.com/ossc-db/pg_bulkload

十一、導(dǎo)入數(shù)據(jù)后，使用 analyze

運(yùn)行 ANALYZE 或者 VACUUM ANALYZE 可以保證規(guī)劃器有表數(shù)據(jù)的最新統(tǒng)計(jì)。

如果沒有統(tǒng)計(jì)數(shù)據(jù)或者統(tǒng)計(jì)數(shù)據(jù)太陳舊，那么規(guī)劃器可能選擇性能很差的執(zhí)行計(jì)劃，導(dǎo)致表的查詢性能較差。