PostgreSQL 優(yōu)化思路

優(yōu)化思路：

0、為每個表執(zhí)行 ANALYZE

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

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

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

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

比如oc.order_id = oo.order_id是關(guān)聯(lián)條件，需要加索引

• oc.op_type = 3 能過濾出1/20的數(shù)據(jù)，
• oo.event_type IN (…) 能過濾出1/10的數(shù)據(jù)，

這兩個是優(yōu)化的重點，也就是實現(xiàn)確保op_type與event_type已經(jīng)加了索引，其次確保索引用到了。

一、排序

• 盡量避免
• 排序的數(shù)據(jù)量盡量少，并保證在內(nèi)存里完成排序。

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

二、索引

• 過濾的數(shù)據(jù)量比較少，一般來說<20%,應(yīng)該走索引。20%-40% 可能走索引也可能不走索引。> 40% ，基本不走索引(會全表掃描)
• 保證值的數(shù)據(jù)類型和字段數(shù)據(jù)類型要一直。
• 對索引的字段進行計算時，必須在運算符右側(cè)進行計算。也就是 to_char(oc.create_date, ‘yyyyMMdd’)是沒用的
• 表字段之間關(guān)聯(lián)，盡量給相關(guān)字段上添加索引。
• 復(fù)合索引，遵從最左前綴的原則,即最左優(yōu)先。（單獨右側(cè)字段查詢沒有索引的）

三、連接查詢方式

1、hash join

• 放內(nèi)存里進行關(guān)聯(lián)。
• 適用于結(jié)果集比較大的情況。
• 比如都是200000數(shù)據(jù)

2、nest loop

• 從結(jié)果1 逐行取出，然后與結(jié)果集2進行匹配。
• 適用于兩個結(jié)果集，其中一個數(shù)據(jù)量遠大于另外一個時。
• 結(jié)果集一：1000
• 結(jié)果集二：1000000

四、多表聯(lián)查時

在多表聯(lián)查時，需要考慮連接順序問題。

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

2、對于JOIN

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

如果要強制規(guī)劃器遵循準(zhǔn)確的JOIN連接順序，我們可以把運行時參數(shù)join_collapse_limit設(shè)置為 1

PostgreSQL提供了一些性能調(diào)優(yōu)的功能

主要有如下幾個方面。

1.使用EXPLAIN

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

2.及時更新執(zhí)行計劃中使用的統(tǒng)計信息

由于統(tǒng)計信息不是每次操作數(shù)據(jù)庫都進行更新的，一般是在 VACUUM 、 ANALYZE 、 CREATE INDEX等DDL執(zhí)行的時候會更新統(tǒng)計信息，

因此執(zhí)行計劃所用的統(tǒng)計信息很有可能比較舊。 這樣執(zhí)行計劃的分析結(jié)果可能誤差會變大。

以下是表tenk1的相關(guān)的一部分統(tǒng)計信息。

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

(5 rows)

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

3.明確用join來關(guān)聯(lián)表

一般寫法：

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

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

例子：

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.關(guān)閉自動提交

（autocommit=false）

5.多次插入數(shù)據(jù)用copy命令更高效

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

6.臨時刪除index

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

7.外鍵關(guān)聯(lián)的刪除

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

8.增加maintenance_work_mem參數(shù)大小

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

9.增加checkpoint_segments參數(shù)的大小

增加這個參數(shù)可以提升大量數(shù)據(jù)導(dǎo)入時候的速度。

10.設(shè)置archive_mode無效

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

• CREATE TABLE AS SELECT
• CREATE INDEX
• ALTER TABLE SET TABLESPACE
• CLUSTER等。

11.最后執(zhí)行VACUUM ANALYZE

表中數(shù)據(jù)大量變化的時候建議執(zhí)行VACUUM ANALYZE。

對生產(chǎn)運行的數(shù)據(jù)庫要用定時任務(wù)crontb執(zhí)行如下操作：

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

PostgreSQL 參數(shù)設(shè)置

autovacuum 相關(guān)參數(shù)

autovacuum：
默認(rèn)為on，表示是否開起autovacuum。默認(rèn)開起。特別的，當(dāng)需要凍結(jié)xid時，盡管此值為off，PG也會進行vacuum。 

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

log_autovacuum_min_duration：
記錄autovacuum動作到日志文件，當(dāng)vacuum動作超過此值時。 “-1”表示不記錄?！?”表示每次都記錄。 

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

autovacuum_work_mem：
每個worker可使用的最大內(nèi)存數(shù)。

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

autovacuum_analyze_threshold：
默認(rèn)50。與autovacuum_analyze_scale_factor配合使用。

autovacuum_analyze_scale_factor：
默認(rèn)10%。當(dāng)update,insert,delete的tuples數(shù)量超過autovacuum_analyze_scale_factor*table_size+autovacuum_analyze_threshold時，進行analyze。 

autovacuum_freeze_max_age：200 million。離下一次進行xid凍結(jié)的最大事務(wù)數(shù)。 

autovacuum_multixact_freeze_max_age：
400 million。離下一次進行xid凍結(jié)的最大事務(wù)數(shù)。 

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

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

pg中性能相關(guān)常調(diào)參數(shù)

總結(jié)

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。