国产91对白在线播放,亚洲一区二区不卡视频,久久精品国产2020观看福利

blog已經(jīng)轉(zhuǎn)移至github,大家請訪問 http://qaseven.github.io/

PostgreSQL 數(shù)據(jù)庫維護(hù)

　一、恢復(fù)磁盤空間：

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

　　VACUUM命令存在兩種形式，VACUUM和VACUUM FULL，它們之間的區(qū)別見如下表格：

　　二、更新規(guī)劃器統(tǒng)計：

　　PostgreSQL查詢規(guī)劃器在選擇最優(yōu)路徑時，需要參照相關(guān)數(shù)據(jù)表的統(tǒng)計信息用以為查詢生成最合理的規(guī)劃。這些統(tǒng)計是通過ANALYZE命令獲得的，你可以直接調(diào)用該命令，或者把它當(dāng)做VACUUM命令里的一個可選步驟來調(diào)用，如VACUUM ANAYLYZE table_name，該命令將會先執(zhí)行VACUUM再執(zhí)行ANALYZE。與回收空間(VACUUM)一樣，對數(shù)據(jù)更新頻繁的表保持一定頻度的ANALYZE，從而使該表的統(tǒng)計信息始終處于相對較新的狀態(tài)，這樣對于基于該表的查詢優(yōu)化將是極為有利的。然而對于更新并不頻繁的數(shù)據(jù)表，則不需要執(zhí)行該操作。

　　我們可以為特定的表，甚至是表中特定的字段運行ANALYZE命令，這樣我們就可以根據(jù)實際情況，只對更新比較頻繁的部分信息執(zhí)行ANALYZE操作，這樣不僅可以節(jié)省統(tǒng)計信息所占用的空間，也可以提高本次ANALYZE操作的執(zhí)行效率。這里需要額外說明的是，ANALYZE是一項相當(dāng)快的操作，即使是在數(shù)據(jù)量較大的表上也是如此，因為它使用了統(tǒng)計學(xué)上的隨機(jī)采樣的方法進(jìn)行行采樣，而不是把每一行數(shù)據(jù)都讀取進(jìn)來并進(jìn)行分析。因此，可以考慮定期對整個數(shù)據(jù)庫執(zhí)行該命令。

　　事實上，我們甚至可以通過下面的命令來調(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)的采樣值，我們可以通過下面的命令獲取當(dāng)前系統(tǒng)的缺省采樣值。

postgres=# show default_statistics_target;

default_statistics_target

---------------------------

100

(1 row)

　　從上面的結(jié)果可以看出，該數(shù)據(jù)庫的缺省采樣值為100(10%)。三、VACUUM和ANALYZE的示例：

#1. 創(chuàng)建測試數(shù)據(jù)表。

postgres=# CREATE TABLE testtable (i integer);

CREATE TABLE

#2. 為測試表創(chuàng)建索引。

postgres=# CREATE INDEX testtable_idx ON testtable(i);

CREATE INDEX

#3. 創(chuàng)建批量插入測試數(shù)據(jù)的函數(shù)。

postgres=# CREATE OR REPLACE FUNCTION test_insert() returns integer AS $$

DECLARE

min integer;

max integer;

BEGIN

SELECT COUNT(*) INTO min from testtable;

max := min + 10000;

FOR i IN min..max LOOP

INSERT INTO testtable VALUES(i);

END LOOP;

RETURN 0;

END;

$$ LANGUAGE plpgsql;

CREATE FUNCTION

#4. 批量插入數(shù)據(jù)到測試表(執(zhí)行四次)

postgres=# SELECT test_insert();

test_insert

-------------

(1 row)

#5. 確認(rèn)四次批量插入都成功。

postgres=# SELECT COUNT(*) FROM testtable;

count

-------

40004

(1 row)

#6. 分析測試表，以便有關(guān)該表的統(tǒng)計信息被更新到PostgreSQL的系統(tǒng)表。

postgres=# ANALYZE testtable;

ANALYZE

#7. 查看測試表和索引當(dāng)前占用的頁面數(shù)量(通常每個頁面為8k)。

postgres=# SELECT relname,relfilenode, relpages FROM pg_class WHERE relname = 'testtable' or relname = 'testtable_idx';

relname | relfilenode | relpages

---------------+-------------+----------

testtable | 17601 | 157

testtable_idx | 17604 | 90

#8. 批量刪除數(shù)據(jù)。

postgres=# DELETE FROM testtable WHERE i < 30000;

DELETE 30003

#9. 執(zhí)行vacuum和analyze，以便更新系統(tǒng)表，同時為該表和索引記錄高水標(biāo)記。

#10. 這里需要額外說明的是，上面刪除的數(shù)據(jù)均位于數(shù)據(jù)表的前部，如果刪除的是末尾部分，

# 如where i > 10000，那么在執(zhí)行VACUUM ANALYZE的時候，數(shù)據(jù)表將會被物理的縮小。

postgres=# VACUUM ANALYZE testtable;

ANALYZE

#11. 查看測試表和索引在刪除后，再通過VACUUM ANALYZE更新系統(tǒng)統(tǒng)計信息后的結(jié)果(保持不變)。

postgres=# SELECT relname,relfilenode, relpages FROM pg_class WHERE relname = 'testtable' or relname = 'testtable_idx';

relname | relfilenode | relpages

---------------+-------------+----------

testtable | 17601 | 157

testtable_idx | 17604 | 90

(2 rows)

#12. 再重新批量插入兩次，之后在分析該表以更新其統(tǒng)計信息。

postgres=# SELECT test_insert(); --執(zhí)行兩次。

test_insert

-------------

(1 row)

postgres=# ANALYZE testtable;

ANALYZE

#13. 此時可以看到數(shù)據(jù)表中的頁面數(shù)量仍然為之前的高水標(biāo)記數(shù)量，索引頁面數(shù)量的增加

# 是和其內(nèi)部實現(xiàn)方式有關(guān)，但是在后面的插入中，索引所占的頁面數(shù)量就不會繼續(xù)增加。

postgres=# SELECT relname,relfilenode, relpages FROM pg_class WHERE relname = 'testtable' or relname = 'testtable_idx';

relname | relfilenode | relpages

---------------+-------------+----------

testtable | 17601 | 157

testtable_idx | 17604 | 173

(2 rows)

postgres=# SELECT test_insert();

test_insert

-------------

(1 row)

postgres=# ANALYZE testtable;

ANALYZE

#14. 可以看到索引的頁面數(shù)量確實沒有繼續(xù)增加。

postgres=# SELECT relname,relfilenode, relpages FROM pg_class WHERE relname = 'testtable' or relname = 'testtable_idx';

relname | relfilenode | relpages

---------------+-------------+----------

testtable | 17601 | 157

testtable_idx | 17604 | 173

(2 rows)

#15. 重新批量刪除數(shù)據(jù)。

postgres=# DELETE FROM testtable WHERE i < 30000;

DELETE 19996

#16. 從后面的查詢可以看出，在執(zhí)行VACUUM FULL命令之后，測試表和索引所占用的頁面數(shù)量

# 確實降低了，說明它們占用的物理空間已經(jīng)縮小了。

postgres=# VACUUM FULL testtable;

VACUUM

postgres=# SELECT relname,relfilenode, relpages FROM pg_class WHERE relname = 'testtable' or relname = 'testtable_idx';

relname | relfilenode | relpages

---------------+-------------+----------

testtable | 17602 | 118

testtable_idx | 17605 | 68

(2 rows)

　　四、定期重建索引：

　　在PostgreSQL中，為數(shù)據(jù)更新頻繁的數(shù)據(jù)表定期重建索引(REINDEX INDEX)是非常有必要的。對于B-Tree索引，只有那些已經(jīng)完全清空的索引頁才會得到重復(fù)使用，對于那些僅部分空間可用的索引頁將不會得到重用，如果一個頁面中大多數(shù)索引鍵值都被刪除，只留下很少的一部分，那么該頁將不會被釋放并重用。在這種極端的情況下，由于每個索引頁面的利用率極低，一旦數(shù)據(jù)量顯著增加，將會導(dǎo)致索引文件變得極為龐大，不僅降低了查詢效率，而且還存在整個磁盤空間被完全填滿的危險。

　　對于重建后的索引還存在另外一個性能上的優(yōu)勢，因為在新建立的索引上，邏輯上相互連接的頁面在物理上往往也是連在一起的，這樣可以提高磁盤頁面被連續(xù)讀取的幾率，從而提高整個操作的IO效率。見如下示例：

#1. 此時已經(jīng)在該表中插入了大約6萬條數(shù)據(jù)，下面的SQL語句將查詢該索引所占用的磁盤空間。

postgres=# SELECT relname, pg_relation_size(oid)/1024 || 'K' AS size FROM pg_class WHERE relkind='i' AND relname = 'testtable_idx';

relname | size

----------------+------

testtable_idx | 1240K

(1 row)

#2. 刪除數(shù)據(jù)表中大多數(shù)的數(shù)據(jù)。

postgres=# DELETE FROM testtable WHERE i > 20000;

DELETE 50006

#3. 分析一個該表，以便于后面的SQL語句繼續(xù)查看該索引占用的空間。

postgres=# ANALYZE testtable;

ANALYZE

#4. 從該查詢結(jié)果可以看出，該索引所占用的空間并未減少，而是和之前的完全一樣。

postgres=# SELECT pg_relation_size('testtable_idx')/1024 || 'K' AS size;

size

------

1240K

(1 row)

#5. 重建索引。

postgres=# REINDEX INDEX testtable_idx;

REINDEX

#6. 查看重建后的索引實際占用的空間，從結(jié)果中可以看出索引的尺寸已經(jīng)減少。

postgres=# SELECT pg_relation_size('testtable_idx')/1024 || 'K' AS size;

size

------

368K

(1 row)

#7. 最后一點需要記住的是，在索引重建后一定要分析數(shù)據(jù)表。

postgres=# ANALYZE testtable;

ANALYZE

　　五、觀察磁盤使用情況：

　　1. 查看數(shù)據(jù)表所占用的磁盤頁面數(shù)量。

#relpages只能被VACUUM、ANALYZE和幾個DDL命令更新，如CREATE INDEX。通常一個頁面的長度為8K字節(jié)。

postgres=# SELECT relfilenode, relpages FROM pg_class WHERE relname = 'testtable';

relfilenode | relpages

-------------+----------

16412 | 79

(1 row)

　　2. 查看指定數(shù)據(jù)表的索引名稱和索引占用的磁盤頁面數(shù)量。

postgres=# SELECT c2.relname, c2.relpages FROM pg_class c, pg_class c2, pg_index i

WHERE c.relname = 'testtable' AND c.oid = i.indrelid AND c2.oid = i.indexrelid

ORDER BY c2.relname;

relname | relpages

---------------+----------

testtable_idx | 46

(1 row)

posted on 2014-03-07 10:59 順其自然EVO 閱讀(668) 評論(0) 編輯收藏所屬分類: 數(shù)據(jù)庫

新用戶注冊刷新評論列表


只有注冊用戶登錄后才能發(fā)表評論。




網(wǎng)站導(dǎo)航: 博客園 IT新聞 Chat2DB C++博客博問管理
相關(guān)文章: MySQL數(shù)字輔助表 PLSQL Developer鮮為人知的快捷鍵淘寶分布式配置管理服務(wù)Diamond iOS開發(fā)之FMDB AlwaysOn可用性組功能測試(2)-SQL Server群集故障轉(zhuǎn)移 SQL Server 2008 (R2) 單機(jī)版安裝的先決條件 AlwaysOn可用性組功能測試(2)-SQL Server群集故障轉(zhuǎn)移聊聊Oracle外鍵約束的幾個操作選項 SQL優(yōu)化中索引列使用函數(shù)之靈異事件利用binlog進(jìn)行數(shù)據(jù)庫的還原

qileilove

PostgreSQL 數(shù)據(jù)庫維護(hù)

導(dǎo)航

統(tǒng)計

常用鏈接

留言簿(55)

隨筆分類

隨筆檔案

文章分類

文章檔案

搜索

最新評論

閱讀排行榜

評論排行榜