PostgreSQL數(shù)據(jù)庫(kù)導(dǎo)入大量數(shù)據(jù)時(shí)如何優(yōu)化

來源 | OSCHINA 社區(qū)、作者 | PostgreSQLChina

鏈接：https://my.oschina.net/postgresqlchina/blog/5568852

在使用 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)化手段。可以結(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ù)不完整的問題。

      postgres=#??\echo?:AUTOCOMMITonpostgres=#?\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ì)消失。（慎重考慮索引帶來的影響）

三、刪除外鍵約束

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

我們創(chuàng)建了一個(gè)高質(zhì)量的技術(shù)交流群，與優(yōu)秀的人在一起，自己也會(huì)優(yōu)秀起來，趕緊 點(diǎn)擊加群 ，享受一起成長(zhǎng) 的快樂。

四、增大 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 來禁用歸檔和流復(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í)，所有臟頁都必須被刷寫到磁盤上。通過在批量數(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)化過的；它沒 INSERT 那么靈活，但是在大量裝載數(shù)據(jù)的情況下，導(dǎo)致的荷載也少很多。因?yàn)?COPY 是單條命令，因此填充表的時(shí)候就沒有必要關(guān)閉自動(dòng)提交了。

如果不能使用 COPY，可以使用 PREPARE 來創(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 的直接模式下，它將跳過共享緩沖區(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)致表的查詢性能較差。

我們創(chuàng)建了一個(gè)高質(zhì)量的技術(shù)交流群，與優(yōu)秀的人在一起，自己也會(huì)優(yōu)秀起來，趕緊點(diǎn)擊加群，享受一起成長(zhǎng)的快樂。另外，如果你最近想跳槽的話，年前我花了2周時(shí)間收集了一波大廠面經(jīng)，節(jié)后準(zhǔn)備跳槽的可以點(diǎn)擊這里領(lǐng)取！

推薦閱讀

• 稀土開發(fā)者大會(huì)講師 PPT 精華送上，通關(guān)全靠你自己了！
  
• 如何使用 Docker 高效搭建本地開發(fā)環(huán)境（詳細(xì)教程）
  
• 1 億巨資開發(fā)的防疫 APP，兩年多只找到 2 例確診
  

··································

你好，我是程序猿DD，10年開發(fā)老司機(jī)、阿里云MVP、騰訊云TVP、出過書創(chuàng)過業(yè)、國(guó)企4年互聯(lián)網(wǎng)6年 。 從普通開發(fā)到架構(gòu)師、再到合伙人。一路過來，給我最深的感受就是一定要不斷學(xué)習(xí)并關(guān)注前沿。只要你能堅(jiān)持下來，多思考、少抱怨、勤動(dòng)手，就很容易實(shí)現(xiàn)彎道超車！ 所以，不要問我現(xiàn)在干什么是否來得及。如果你看好一個(gè)事情，一定是堅(jiān)持了才能看到希望，而不是看到希望才去堅(jiān)持。相信我，只要堅(jiān)持下來，你一定比現(xiàn)在更好！ 如果你還沒什么方向，可以先關(guān)注我， 這里會(huì)經(jīng)常分享一些前沿資訊，幫你積累彎道超車的資本。

點(diǎn)擊 領(lǐng)取2022最新10000T學(xué)習(xí)資料