14.4. 向數(shù)據(jù)庫(kù)中添加記錄

第一次填充數(shù)據(jù)庫(kù)時(shí)可能需要做大量的表插入。下面是一些建議，可以盡可能高效地處理這些事情。

14.4.1. 關(guān)閉自動(dòng)提交

關(guān)閉自動(dòng)提交，并且只在每次(數(shù)據(jù)拷貝)結(jié)束的時(shí)候做一次提交。在純 SQL 里，這就意味著在開(kāi)始的時(shí)候發(fā)出BEGIN并且在結(jié)束的時(shí)候執(zhí)行COMMIT。有些客戶端的庫(kù)可能背著你干這些事情，這種情況下你必須確信只有在你確實(shí)要那些庫(kù)干這些事情的時(shí)候它才做。如果你允許每個(gè)插入都獨(dú)立地提交，那么PostgreSQL會(huì)為所增加的每行記錄做大量的處理。在一個(gè)事務(wù)里完成所有插入的動(dòng)作的最大的好處就是，如果有一條記錄插入失敗，那么，到該點(diǎn)為止的所有已插入記錄都將被回滾，這樣你就不會(huì)很難受地面對(duì)一個(gè)只裝載了一部分?jǐn)?shù)據(jù)的表。

14.4.2. 使用`COPY`

使用COPY在一條命令里裝載所有記錄，而不是一連串的INSERT命令。 COPY命令是為裝載數(shù)量巨大的數(shù)據(jù)行優(yōu)化過(guò)的；它沒(méi)INSERT那么靈活，但是在大量裝載數(shù)據(jù)的情況下，導(dǎo)致的荷載也少很多。因?yàn)?tt class="COMMAND">COPY是單條命令，因此填充表的時(shí)候就沒(méi)有必要關(guān)閉自動(dòng)提交了。

如果你不能使用COPY，那么使用PREPARE來(lái)創(chuàng)建一個(gè)預(yù)備INSERT，然后使用EXECUTE多次效率更高。這樣就避免了重復(fù)分析和規(guī)劃INSERT的開(kāi)銷。不同接口以不同的方式提供該功能，可參閱接口文檔中的"prepared statements"章節(jié)。

請(qǐng)注意，在裝載大量數(shù)據(jù)行的時(shí)候，COPY幾乎總是比INSERT快，即使使用了PREPARE并且把多個(gè)INSERT命令綁在一個(gè)事務(wù)中也是這樣。

同樣的事務(wù)中，COPY命令比CREATE TABLE或TRUNCATE更快。這這些情況下，不需要寫WAL，因?yàn)樵谝粋€(gè)錯(cuò)誤的情況下，文件中新增的數(shù)據(jù)會(huì)被刪掉。然而，只有當(dāng)wal_level設(shè)置為minimal（此時(shí)所有的命令必須寫WAL）才會(huì)考慮這種情況。

14.4.3. 刪除索引

如果你正在裝載一個(gè)新創(chuàng)建的表，最快的方法是創(chuàng)建表，用COPY批量裝載，然后創(chuàng)建表需要的任何索引。在已存在數(shù)據(jù)的表上創(chuàng)建索引要比遞增地更新所裝載的每一行記錄要快

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

14.4.4. 刪除外鍵約束

和索引一樣，"批量地"檢查外鍵約束比一行行檢查更高效。因此，也許我們先刪除外鍵約束，裝載數(shù)據(jù)，然后重建約束會(huì)更高效。同樣，裝載數(shù)據(jù)和缺少約束而失去錯(cuò)誤檢查之間也有一個(gè)平衡

更重要的是，當(dāng)用已經(jīng)存在的外鍵向表中導(dǎo)入數(shù)據(jù)時(shí)，每個(gè)新行需要一個(gè)在服務(wù)器的待觸發(fā)事件列表中的條目。載入中幾百萬(wàn)行會(huì)導(dǎo)致觸發(fā)事件隊(duì)列溢出可用內(nèi)存，造成不能接受的交換，甚至是徹底失敗的命令。因此在錄入大量數(shù)據(jù)是，可能需要?jiǎng)h除并重建外鍵。如果不想臨時(shí)移除約束，那唯一的辦法就是講事務(wù)分解成一個(gè)個(gè)更小的事務(wù)。

14.4.5. 增大`maintenance_work_mem`

在裝載大量的數(shù)據(jù)的時(shí)候，臨時(shí)增大 maintenance_work_mem 配置變量可以改進(jìn)性能。這個(gè)參數(shù)也可以幫助加速CREATE INDEX和ALTER TABLE ADD FOREIGN KEY命令。它不會(huì)對(duì)COPY本身有多大作用，所以這個(gè)建議只有在你使用上面的兩個(gè)技巧時(shí)才有效

14.4.6. 增大`checkpoint_segments`

臨時(shí)增大checkpoint_segments配置變量也可以讓大量數(shù)據(jù)裝載得更快。這是因?yàn)橄?span id="wjcelcm34c" class="PRODUCTNAME">PostgreSQL里面裝載大量的數(shù)據(jù)可以導(dǎo)致檢查點(diǎn)操作 (由配置變量checkpoint_timeout聲明)比平常更加頻繁發(fā)生。在發(fā)生一個(gè)檢查點(diǎn)的時(shí)候，所有臟數(shù)據(jù)都必須刷新到磁盤上。通過(guò)在大量數(shù)據(jù)裝載的時(shí)候臨時(shí)增加checkpoint_segments，所要求的檢查點(diǎn)的數(shù)目可以減少。

14.4.7. 禁用WAL歸檔和流復(fù)制

當(dāng)使用WAL歸檔或流復(fù)制向一個(gè)安裝中錄入大量數(shù)據(jù)時(shí)，在錄入結(jié)束時(shí)，執(zhí)行一次新的基礎(chǔ)備份比執(zhí)行一次增量WAL更快。為了防止錄入時(shí)的增量WAL，將wal_leveltominimal， archive_modetooff和max_wal_senders設(shè)置為0來(lái)禁用歸檔和流復(fù)制。但需要注意的是，修改這些設(shè)置需要重啟服務(wù)。

除了避免歸檔或處理WAL數(shù)據(jù)的WAL發(fā)送的時(shí)間之外，這樣做，實(shí)際上使某些命令更快，因?yàn)樗麄儽贿@是為完全不需要寫WAL（如果wal_level為minimal）。（在最后時(shí)，相比較寫WAL，運(yùn)行fsync可以保證故障安全性的開(kāi)銷更低）命令如下：

CREATE TABLE AS SELECT
CREATE INDEX(and variants such as ALTER TABLE ADD PRIMARY KEY)
ALTER TABLE SET TABLESPACE
CLUSTER
COPY FROM，當(dāng)目的表已經(jīng)創(chuàng)建或在這之前已刪除（同一個(gè)事務(wù)中）

14.4.8. 運(yùn)行`ANALYZE`

不管什么時(shí)候，如果你在增加或者更新了大量數(shù)據(jù)之后，運(yùn)行 ANALYZE都是個(gè)好習(xí)慣。運(yùn)行ANALYZE 或者VACUUM ANALYZE可以保證規(guī)劃器有表數(shù)據(jù)的最新統(tǒng)計(jì)。如果沒(méi)有統(tǒng)計(jì)數(shù)據(jù)或者統(tǒng)計(jì)數(shù)據(jù)太陳舊，那么規(guī)劃器可能選擇很差勁的查詢規(guī)劃，導(dǎo)致表的查詢性能惡化。需要注意的是，如果啟用了autovacuum守護(hù)進(jìn)程，也會(huì)自動(dòng)運(yùn)行ANALYZE；參閱 Section 23.1.3和Section 23.1.5。

14.4.9. pg_dump的一些注意事項(xiàng)

pg_dump生成的轉(zhuǎn)儲(chǔ)腳本自動(dòng)使用上面的若干個(gè)技巧，但不是全部。要盡可能快地裝載pg_dump轉(zhuǎn)儲(chǔ)，我們需要手工做幾個(gè)事情。請(qǐng)注意，這些要點(diǎn)適用于恢復(fù)一個(gè)轉(zhuǎn)儲(chǔ)，而不是創(chuàng)建一個(gè)轉(zhuǎn)儲(chǔ)的時(shí)候。同樣的要點(diǎn)也適用于使用psql或pg_restore 從pg_dump歸檔文件裝載數(shù)據(jù)的時(shí)候。

缺省的時(shí)候，pg_dump使用COPY，在它生成一個(gè)完整的模式和數(shù)據(jù)的轉(zhuǎn)儲(chǔ)的時(shí)候，它會(huì)很小心地先裝載數(shù)據(jù)，然后創(chuàng)建索引和外鍵。因此，在這個(gè)情況下，頭幾條技巧是自動(dòng)處理的。你需要做的只是

在裝載轉(zhuǎn)儲(chǔ)腳本之前設(shè)置比正常狀況大的maintenance_work_mem和 checkpoint_segments值。
如果使用WAL歸檔或流復(fù)制，在轉(zhuǎn)儲(chǔ)時(shí)，可以考慮禁用這些。將archive_mode 設(shè)置為off，wal_level設(shè)置為minimal以及 max_wal_senders設(shè)置為0（在錄入dump前）來(lái)實(shí)現(xiàn)。最后，將它們?cè)O(shè)回正確的值，并執(zhí)行一次新的基礎(chǔ)備份。
考慮是否在一個(gè)事務(wù)中轉(zhuǎn)儲(chǔ)所有的dump。可以在psql或pg_restore 中使用-1或--single-transaction選項(xiàng)來(lái)實(shí)現(xiàn)。當(dāng)使用這個(gè)模式時(shí)，即使是一個(gè)很小的錯(cuò)誤也會(huì)回滾所有的轉(zhuǎn)儲(chǔ)。根據(jù)數(shù)據(jù)間的相關(guān)性，最好手動(dòng)清理或不。單一事務(wù)模式下，同時(shí)關(guān)閉WAL歸檔，COPY命令會(huì)更快。
如果多CPU支持?jǐn)?shù)據(jù)庫(kù)服務(wù)，可以考慮使用pg_restore's--jobs 選項(xiàng)。允許并發(fā)錄入數(shù)據(jù)和創(chuàng)建索引。
最后，運(yùn)行ANALYZE。

只保存數(shù)據(jù)的轉(zhuǎn)儲(chǔ)仍然會(huì)使用COPY，但是它不會(huì)刪除或者重建索引，并且它不會(huì)自動(dòng)修改外鍵 [1] 因此，在裝載只有數(shù)據(jù)的轉(zhuǎn)儲(chǔ)的時(shí)候，是否使用刪除以及重建索引和外鍵等技巧完全取決于你。裝載數(shù)據(jù)的時(shí)候，增大checkpoint_segments仍然是有用的，但是增大maintenance_work_mem就沒(méi)什么必要了；你只是應(yīng)該在事后手工創(chuàng)建索引和外鍵的事后增大它。最后結(jié)束時(shí)不要忘記ANALYZE 命令。獲得更多信息參考Section 23.1.3和Section 23.1.5

Notes

[1]	你可以通過(guò)使用`--disable-triggers`選項(xiàng)的方法獲取關(guān)閉外鍵的效果。不過(guò)要意識(shí)到這么做是消除，而不只是推遲違反外鍵約束，因此如果你使用這個(gè)選項(xiàng)，將有可能插入壞數(shù)據(jù)。

亚洲国产日韩欧美一区二区三区,精品亚洲国产成人av在线,国产99视频精品免视看7,99国产精品久久久久久久成人热,欧美日韩亚洲国产综合乱