PostgreSQL 優(yōu)化思路

優(yōu)化思路：

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

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

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

2、先查找那部分是重點(diǎn)語(yǔ)句，比如上面SQL，外面的嵌套層對(duì)于優(yōu)化來(lái)說(shuō)沒(méi)有意義，可以去掉。

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

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

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

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

一、排序

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

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

二、索引

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

三、連接查詢方式

1、hash join

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

2、nest loop

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

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

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

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

2、對(duì)于JOIN

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

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

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

主要有如下幾個(gè)方面。

1.使用EXPLAIN

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

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

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

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

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

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

(5 rows)

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

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

一般寫(xiě)法：

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

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

例子：

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)閉自動(dòng)提交

（autocommit=false）

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

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

6.臨時(shí)刪除index

有時(shí)候我們?cè)趥浞莺椭匦聦?dǎo)入數(shù)據(jù)的時(shí)候，如果數(shù)據(jù)量很大的話，要很幾個(gè)小時(shí)才能完成。這個(gè)時(shí)候可以先把index刪除掉。導(dǎo)入在建index。

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

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

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

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

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

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

10.設(shè)置archive_mode無(wú)效

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

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

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

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

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

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

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

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

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

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

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

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

autovacuum_work_mem：
每個(gè)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ù)量超過(guò)autovacuum_vacuum_scale_factor*table_size+autovacuum_vacuum_threshold時(shí)，進(jìn)行vacuum。如果要使vacuum工作勤奮點(diǎn)，則將此值改小。 

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

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

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

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

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

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

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

總結(jié)

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

最新評(píng)論