PostgreSQL數據庫性能調優的注意點及pg數據庫性能優化方法是什么

本篇內容主要講解“PostgreSQL數據庫性能調優的注意點及pg數據庫性能優化方法是什么”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“PostgreSQL數據庫性能調優的注意點及pg數據庫性能優化方法是什么”吧!

PostgreSQL 優化思路

優化思路：

0、為每個表執行 ANALYZE

然后分析 EXPLAIN (ANALYZE，BUFFERS) sql。

1、對于多表查詢，查看每張表數據，然后改進連接順序。

2、先查找那部分是重點語句，比如上面SQL，外面的嵌套層對于優化來說沒有意義，可以去掉。

3、查看語句中，where等條件子句，每個字段能過濾的效率。找出可優化處。

比如oc.order_id = oo.order_id是關聯條件，需要加索引

• oc.op_type = 3 能過濾出1/20的數據，

• oo.event_type IN (…) 能過濾出1/10的數據，

這兩個是優化的重點，也就是實現確保op_type與event_type已經加了索引，其次確保索引用到了。

一、排序

• 盡量避免

• 排序的數據量盡量少，并保證在內存里完成排序。

（至于具體什么數據量能在內存中完成排序，不同數據庫有不同的配置：oracle是sort_area_size；postgresql是work_mem (integer)，單位是KB，默認值是4MB。mysql是sort_buffer_size 注意：該參數對應的分配內存是每連接獨占！）

二、索引

• 過濾的數據量比較少，一般來說<20%,應該走索引。20%-40% 可能走索引也可能不走索引。> 40% ，基本不走索引(會全表掃描)

• 保證值的數據類型和字段數據類型要一直。

• 對索引的字段進行計算時，必須在運算符右側進行計算。也就是 to_char(oc.create_date, &lsquo;yyyyMMdd&rsquo;)是沒用的

• 表字段之間關聯，盡量給相關字段上添加索引。

• 復合索引，遵從最左前綴的原則,即最左優先。（單獨右側字段查詢沒有索引的）

三、連接查詢方式

1、hash join

• 放內存里進行關聯。

• 適用于結果集比較大的情況。

• 比如都是200000數據

2、nest loop

• 從結果1 逐行取出，然后與結果集2進行匹配。

• 適用于兩個結果集，其中一個數據量遠大于另外一個時。

• 結果集一：1000

• 結果集二：1000000

四、多表聯查時

在多表聯查時，需要考慮連接順序問題。

1、當postgresql中進行查詢時，如果多表是通過逗號，而不是join連接，那么連接順序是多表的笛卡爾積中取最優的。如果有太多輸入的表， PostgreSQL規劃器將從窮舉搜索切換為基因概率搜索，以減少可能性數目(樣本空間)。基因搜索花的時間少， 但是并不一定能找到最好的規劃。

2、對于JOIN

• LEFT JOIN / RIGHT JOIN 會一定程度上指定連接順序，但是還是會在某種程度上重新排列：

• FULL JOIN 完全強制連接順序。

如果要強制規劃器遵循準確的JOIN連接順序，我們可以把運行時參數join_collapse_limit設置為 1

PostgreSQL提供了一些性能調優的功能

主要有如下幾個方面。

1.使用EXPLAIN

EXPLAIN命令可以查看執行計劃，這個方法是我們最主要的調試工具。

2.及時更新執行計劃中使用的統計信息

由于統計信息不是每次操作數據庫都進行更新的，一般是在 VACUUM 、 ANALYZE 、 CREATE INDEX等DDL執行的時候會更新統計信息，

因此執行計劃所用的統計信息很有可能比較舊。 這樣執行計劃的分析結果可能誤差會變大。

以下是表tenk1的相關的一部分統計信息。

SELECT relname, relkind, reltuples, relpages FROM pg_class WHERE relname LIKE 'tenk1%';

(5 rows)

其中 relkind是類型，r是自身表，i是索引index；reltuples是項目數；relpages是所占硬盤的塊數。

3.明確用join來關聯表

一般寫法：

SELECT * FROM a, b, c WHERE a.id = b.id AND b.ref = c.id;

如果明確用join的話，執行時候執行計劃相對容易控制一些。

例子：

SELECT * FROM a CROSS JOIN b CROSS JOIN c WHERE a.id = b.id AND b.ref = c.id;
SELECT * FROM a JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);

4.關閉自動提交

（autocommit=false）

5.多次插入數據用copy命令更高效

我們有的處理中要對同一張表執行很多次insert操作。這個時候我們用copy命令更有效率。因為insert一次，其相關的index都要做一次，比較花費時間。

6.臨時刪除index

有時候我們在備份和重新導入數據的時候，如果數據量很大的話，要很幾個小時才能完成。這個時候可以先把index刪除掉。導入在建index。

7.外鍵關聯的刪除

如果表的有外鍵的話，每次操作都沒去check外鍵整合性。因此比較慢。數據導入后在建立外鍵也是一種選擇。

8.增加maintenance_work_mem參數大小

增加這個參數可以提升CREATE INDEX和ALTER TABLE ADD FOREIGN KEY的執行效率。

9.增加checkpoint_segments參數的大小

增加這個參數可以提升大量數據導入時候的速度。

10.設置archive_mode無效

這個參數設置為無效的時候，能夠提升以下的操作的速度

• CREATE TABLE AS SELECT

• CREATE INDEX

• ALTER TABLE SET TABLESPACE

• CLUSTER等。

11.最后執行VACUUM ANALYZE

表中數據大量變化的時候建議執行VACUUM ANALYZE。

對生產運行的數據庫要用定時任務crontb執行如下操作：

psql -U username -d databasename -c "vacuum verbose analyze tablename;"

PostgreSQL 參數設置

autovacuum 相關參數

autovacuum：
默認為on，表示是否開起autovacuum。默認開起。特別的，當需要凍結xid時，盡管此值為off，PG也會進行vacuum。 

autovacuum_naptime：
下一次vacuum的時間，默認1min。 這個naptime會被vacuum launcher分配到每個DB上。autovacuum_naptime/num of db。 

log_autovacuum_min_duration：
記錄autovacuum動作到日志文件，當vacuum動作超過此值時。 “-1”表示不記錄。“0”表示每次都記錄。 

autovacuum_max_workers：
最大同時運行的worker數量，不包含launcher本身。 

autovacuum_work_mem：
每個worker可使用的最大內存數。

autovacuum_vacuum_threshold：
默認50。與autovacuum_vacuum_scale_factor配合使用，autovacuum_vacuum_scale_factor默認值為20%。當update,delete的tuples數量超過autovacuum_vacuum_scale_factor*table_size+autovacuum_vacuum_threshold時，進行vacuum。如果要使vacuum工作勤奮點，則將此值改小。 

autovacuum_analyze_threshold：
默認50。與autovacuum_analyze_scale_factor配合使用。

autovacuum_analyze_scale_factor：
默認10%。當update,insert,delete的tuples數量超過autovacuum_analyze_scale_factor*table_size+autovacuum_analyze_threshold時，進行analyze。 

autovacuum_freeze_max_age：200 million。離下一次進行xid凍結的最大事務數。 

autovacuum_multixact_freeze_max_age：
400 million。離下一次進行xid凍結的最大事務數。 

autovacuum_vacuum_cost_delay：
如果為-1，取vacuum_cost_delay值。 

autovacuum_vacuum_cost_limit：
如果為-1，到vacuum_cost_limit的值，這個值是所有worker的累加值。

pg中性能相關常調參數

到此，相信大家對“PostgreSQL數據庫性能調優的注意點及pg數據庫性能優化方法是什么”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！