關(guān)鍵字:批量數(shù)據(jù)導入,數(shù)據(jù)加載,大量插入,加快,提升速度
多元化選擇時代,人生里很多事物都是如此,凡事都沒有一成不變的方式和方法。不管白貓黑貓,能抓老鼠的就是好貓,適合自己的就是最好的。
提升批量數(shù)據(jù)導入的方法亦是如此,沒有何種方法是最優(yōu)的,應用任何方法前根據(jù)自己的實際情況權(quán)衡利弊,做出選擇。
批量導入數(shù)據(jù)之前,無論采取何種方式,務必做好相應的備份。
導入完成后亦需對相應對象進行ANALYZE操作,這樣查詢優(yōu)化器才會按照最新的統(tǒng)計信息生成正確的執(zhí)行計劃。
下面正式介紹提升批量數(shù)據(jù)導入性能的n種方法。
方法1:禁用自動提交。
psql
\set AUTOCOMMIT off
其他
BEGIN;
執(zhí)行批量數(shù)據(jù)導入
COMMIT;
方法2:設置表為UNLOGGED。
導入數(shù)據(jù)之前先把表改成UNLOGGED模式,導入完成后改回LOGGED模式。
ALTER TABLE tablename SET UNLOGGED;
執(zhí)行批量數(shù)據(jù)導入
ALTER TABLE tablename LOGGED;
優(yōu)點:
導入信息不記錄WAL日志,極大減少io,提升導入速度。
缺點:
1.在replication環(huán)境下,表無法設置為UNLOGGED模式。
2.導入過程一旦出現(xiàn)停電死機等會導致數(shù)據(jù)庫不能干凈關(guān)庫的情況,數(shù)據(jù)庫中所有UNLOGGED表的數(shù)據(jù)將丟失。
方法3:重建索引。
導入數(shù)據(jù)之前先刪除相關(guān)表上的索引,導入完成后重新創(chuàng)建之。
DROP INDEX indexname;
執(zhí)行批量數(shù)據(jù)導入
CREATE INDEX ...;
查詢表上索引定義的方法
1select * from pg_indexes where tablename ='tablename' and schemaname = 'schemaname';
方法4:重建外鍵。
導入數(shù)據(jù)之前先刪除相關(guān)表上的外鍵,導入完成后重新創(chuàng)建之。
ALTER TABLE ...
DROP CONSTRAINT ... ;
執(zhí)行批量數(shù)據(jù)導入
ALTER TABLE ...
ADD CONSTRAINT ...
FOREIGN KEY ...
REFERENCES ...;
相關(guān)信息可查詢pg_constraint。
方法5:停用觸發(fā)器
導入數(shù)據(jù)之前先DISABLE掉相關(guān)表上的觸發(fā)器,導入完成后重新ENABLE之。
ALTER TABLE tablename DISABLE TRIGGER ALL;
執(zhí)行批量數(shù)據(jù)導入
ALTER TABLE tablename ENABLE TRIGGER ALL;
相關(guān)信息可查詢pg_trigger。
方法6:insert改copy
COPY針對批量數(shù)據(jù)加載進行了優(yōu)化。
1COPY ... FROM 'xxx';
方法7:單值insert改多值insert
減少sql解析的時間。
方法8:insert改PREPARE
通過使用PREPARE預備語句,降低解析消耗。
PREPARE fooplan (int, text, bool, numeric) AS
INSERT INTO foo VALUES($1, $2, $3, $4);
EXECUTE fooplan(1, 'Hunter Valley', 't', 200.00);
方法9:修改參數(shù)
增大maintenance_work_mem,增大max_wal_size。
方法10:關(guān)閉歸檔模式,降低wal日志級別。
修改archive_mode參數(shù)控制歸檔開啟和關(guān)閉。降低wal_level值為minimal來減少日志信息記錄。
此法需要重啟數(shù)據(jù)庫,需要規(guī)劃停機時間。此外如有replication備庫,還需考慮對其影響。
文章來源:腳本之家
來源地址:https://www.jb51.net/article/207336.htm
申請創(chuàng)業(yè)報道,分享創(chuàng)業(yè)好點子。點擊此處,共同探討創(chuàng)業(yè)新機遇!