PostgreSQL--性能優(yōu)化的幾個(gè)小tip

一、回收磁盤(pán)空間

在PostgreSQL中，使用delete和update語(yǔ)句刪除或更新的數(shù)據(jù)行并沒(méi)有被實(shí)際刪除，而只是在舊版本數(shù)據(jù)行的物理地址上將該行的狀態(tài)置為已刪除或已過(guò)期。因此當(dāng)數(shù)據(jù)表中的數(shù)據(jù)變化極為頻繁時(shí)，那么在一段時(shí)間之后該表所占用的空間將會(huì)變得很大，然而數(shù)據(jù)量卻可能變化不大。要解決該問(wèn)題，需要定期對(duì)數(shù)據(jù)變化頻繁的數(shù)據(jù)表執(zhí)行VACUUM操作。現(xiàn)在新版PostgreSQL是自動(dòng)執(zhí)行VACUUM的

成都創(chuàng)新互聯(lián)主營(yíng)武侯網(wǎng)站建設(shè)的網(wǎng)絡(luò)公司,主營(yíng)網(wǎng)站建設(shè)方案,重慶APP開(kāi)發(fā),武侯h5成都微信小程序搭建,武侯網(wǎng)站營(yíng)銷(xiāo)推廣歡迎武侯等地區(qū)企業(yè)咨詢

使用VACUUM和VACUUM FULL命令回收磁盤(pán)空間
postgres=# vacuum arr_test;
postgres=# vacuum full arr_test;

創(chuàng)建測(cè)試數(shù)據(jù)：
postgres=# create table arr (id serial, value int, age int)  #創(chuàng)建測(cè)試表
postgres=# insert into arr (value, age) select generate_series(1, 1000000) as value, (random()*(10^2))::integer;  #插入100W測(cè)試數(shù)據(jù)
postgres=# select pg_relation_size('arr');   #查看表大小
 pg_relation_size 
------------------
         44285952
(1 row)
postgres=# delete from arr where id<300000;  #刪除299999條數(shù)據(jù)
DELETE 299999
postgres=# select pg_relation_size('arr');   #再次查看表大小，沒(méi)有變化
pg_relation_size 
------------------
         44285952
(1 row)
postgres=# vacuum full arr;    #vacuum表，再次查看表大小，明顯變小了
VACUUM
postgres=# select pg_relation_size('arr');
 pg_relation_size 
------------------
         30998528
(1 row)
postgres=# update arr set age=10000 where id>=300000 and id<600000;   #更新30W條數(shù)據(jù)
UPDATE 300000
postgres=# select pg_relation_size('arr');  #查看表大小，明顯再次增大
 pg_relation_size 
------------------
         44285952
(1 row)

二、重建索引

在PostgreSQL中，為數(shù)據(jù)更新頻繁的數(shù)據(jù)表定期重建索引(REINDEX INDEX)是非常有必要的。對(duì)于B-Tree索引，只有那些已經(jīng)完全清空的索引頁(yè)才會(huì)得到重復(fù)使用，對(duì)于那些僅部分空間可用的索引頁(yè)將不會(huì)得到重用，如果一個(gè)頁(yè)面中大多數(shù)索引鍵值都被刪除，只留下很少的一部分，那么該頁(yè)將不會(huì)被釋放并重用。在這種極端的情況下，由于每個(gè)索引頁(yè)面的利用率極低，一旦數(shù)據(jù)量顯著增加，將會(huì)導(dǎo)致索引文件變得極為龐大，不僅降低了查詢效率，而且還存在整個(gè)磁盤(pán)空間被完全填滿的危險(xiǎn)。
對(duì)于重建后的索引還存在另外一個(gè)性能上的優(yōu)勢(shì)，因?yàn)樵谛陆⒌乃饕?，邏輯上相互連接的頁(yè)面在物理上往往也是連在一起的，這樣可以提高磁盤(pán)頁(yè)面被連續(xù)讀取的幾率，從而提高整個(gè)操作的IO效率

postgres=# REINDEX INDEX testtable_idx;

三、重新收集統(tǒng)計(jì)信息

PostgreSQL查詢規(guī)劃器在選擇最優(yōu)路徑時(shí)，需要參照相關(guān)數(shù)據(jù)表的統(tǒng)計(jì)信息用以為查詢生成最合理的規(guī)劃。這些統(tǒng)計(jì)是通過(guò)ANALYZE命令獲得的，你可以直接調(diào)用該命令，或者把它當(dāng)做VACUUM命令里的一個(gè)可選步驟來(lái)調(diào)用，如VACUUM ANAYLYZE table_name，該命令將會(huì)先執(zhí)行VACUUM再執(zhí)行ANALYZE。與回收空間(VACUUM)一樣，對(duì)數(shù)據(jù)更新頻繁的表保持一定頻度的ANALYZE，從而使該表的統(tǒng)計(jì)信息始終處于相對(duì)較新的狀態(tài)，這樣對(duì)于基于該表的查詢優(yōu)化將是極為有利的。然而對(duì)于更新并不頻繁的數(shù)據(jù)表，則不需要執(zhí)行該操作。
我們可以為特定的表，甚至是表中特定的字段運(yùn)行ANALYZE命令，這樣我們就可以根據(jù)實(shí)際情況，只對(duì)更新比較頻繁的部分信息執(zhí)行ANALYZE操作，這樣不僅可以節(jié)省統(tǒng)計(jì)信息所占用的空間，也可以提高本次ANALYZE操作的執(zhí)行效率。這里需要額外說(shuō)明的是，ANALYZE是一項(xiàng)相當(dāng)快的操作，即使是在數(shù)據(jù)量較大的表上也是如此，因?yàn)樗褂昧私y(tǒng)計(jì)學(xué)上的隨機(jī)采樣的方法進(jìn)行行采樣，而不是把每一行數(shù)據(jù)都讀取進(jìn)來(lái)并進(jìn)行分析。因此，可以考慮定期對(duì)整個(gè)數(shù)據(jù)庫(kù)執(zhí)行該命令。
事實(shí)上，我們甚至可以通過(guò)下面的命令來(lái)調(diào)整指定字段的抽樣率

如：
ALTER TABLE testtable ALTER COLUMN test_col SET STATISTICS 200
注意：該值的取值范圍是0--1000，其中值越低采樣比例就越低，分析結(jié)果的準(zhǔn)確性也就越低，但是ANALYZE命令執(zhí)行的速度卻更快。如果將該值設(shè)置為-1，那么該字段的采樣比率將恢復(fù)到系統(tǒng)當(dāng)前默認(rèn)的采樣值，我們可以通過(guò)下面的命令獲取當(dāng)前系統(tǒng)的缺省采樣值。
postgres=# show default_statistics_target;
     default_statistics_target
    ---------------------------
     100
    (1 row)
    從上面的結(jié)果可以看出，該數(shù)據(jù)庫(kù)的缺省采樣值為100(10%)。

新聞標(biāo)題：PostgreSQL--性能優(yōu)化的幾個(gè)小tip
本文地址：http://muchs.cn/article28/jpjdjp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供商城網(wǎng)站、網(wǎng)站改版、網(wǎng)站維護(hù)、外貿(mào)網(wǎng)站建設(shè)、小程序開(kāi)發(fā)、App設(shè)計(jì)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)