PostgreSQL中ANALYZE命令的使用
更新時間：2025年06月15日 15:03:19 作者：文牧之
PostgreSQL中ANALYZE用于收集統(tǒng)計信息以優(yōu)化查詢,文中通過示例代碼詳細的介紹了ANALYZE使用,對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧
ANALYZE 是 PostgreSQL 中用于收集數(shù)據(jù)庫對象統(tǒng)計信息的關(guān)鍵命令，這些統(tǒng)計信息對于查詢優(yōu)化器生成高效執(zhí)行計劃至關(guān)重要。
一 ANALYZE 命令
1.1 基本語法
ANALYZE [ ( option [, ...] ) ] [ table_and_columns [, ...] ]
ANALYZE [ VERBOSE ] [ table_and_columns [, ...] ]
where option can be one of:
VERBOSE [ boolean ]
SKIP_LOCKED [ boolean ]
BUFFER_USAGE_LIMIT size
and table_and_columns is:
table_name [ ( column_name [, ...] ) ]
1.2 鎖級別
- 不會鎖表（不阻塞 DML 操作）
- 只獲取
ShareUpdateExclusiveLock鎖- 允許并發(fā)讀取和寫入
- 僅阻塞
ALTER TABLE、DROP TABLE、VACUUM FULL等DDL操作
- 并發(fā)影響：
- ? 允許并發(fā) SELECT/INSERT/UPDATE/DELETE
- ? 允許并發(fā) CREATE INDEX CONCURRENTLY
- ? 阻塞 ALTER TABLE、DROP TABLE、VACUUM FULL
1.3 對索引的影響
當對表執(zhí)行 ANALYZE 時：
- 會收集該表所有索引的統(tǒng)計信息
- 不會重建或修改索引本身
- 僅更新
pg_statistic系統(tǒng)目錄中的統(tǒng)計信息
二 類似操作的鎖對比
| 命令 | 鎖類型 | 是否阻塞DML | 主要用途 |
|---|---|---|---|
| ANALYZE | ShareUpdateExclusiveLock | 否 | 更新統(tǒng)計信息 |
| REINDEX | 排他鎖 | 是 | 重建索引 |
| REINDEX CONCURRENTLY | ShareUpdateExclusiveLock | 否 | 無鎖重建索引 |
| VACUUM | ShareUpdateExclusiveLock | 否 | 清理死元組 |
| VACUUM FULL | 排他鎖 | 是 | 重組表數(shù)據(jù) |
三 使用場景
3.1 常規(guī)維護
-- 分析單個表 ANALYZE customers; -- 分析特定列 ANALYZE customers (customer_id, name); -- 分析整個數(shù)據(jù)庫 ANALYZE;
3.2 數(shù)據(jù)大量變更后
-- 批量導(dǎo)入數(shù)據(jù)后 COPY employees FROM '/path/to/data.csv'; ANALYZE employees;
3.3 性能調(diào)優(yōu)
-- 查詢性能下降時 ANALYZE VERBOSE orders;
四 配置參數(shù)
自動分析：
autovacuum_analyze_scale_factor = 0.1 -- 10%行變化后觸發(fā) autovacuum_analyze_threshold = 50 -- 最少50行變化
采樣設(shè)置：
default_statistics_target = 100 -- 統(tǒng)計信息詳細程度 alter table large_table set (analyze_sample_percentage = 5); -- 對大表減少采樣
五 監(jiān)控分析狀態(tài)
查看最后分析時間：
SELECT schemaname, relname, last_analyze, analyze_count FROM pg_stat_user_tables;
輸出示例：
schemaname | relname | last_analyze | analyze_count ------------+------------------+-------------------------------+--------------- yewu1 | t4 | | 0 yewu1 | t1 | 2025-05-03 18:51:47.366276-07 | 1 yewu1 | t2 | | 0 public | pgbench_history | | 0 yewu1 | test6 | | 0 public | pgbench_tellers | | 0 yewu1 | test5 | | 0 public | pgbench_branches | | 0 yewu1 | test3 | | 0 yewu1 | test2 | | 0 yewu1 | t3 | | 0 public | pgbench_accounts | | 0 yewu1 | test10 | | 0 yewu1 | test4 | | 0 (14 rows)
檢查待分析變更量：
SELECT schemaname, relname,
n_mod_since_analyze,
n_live_tup,
round(n_mod_since_analyze*100.0/nullif(n_live_tup,0),2) as mod_percent
FROM pg_stat_user_tables
ORDER BY n_mod_since_analyze DESC;
輸出示例：
schemaname | relname | n_mod_since_analyze | n_live_tup | mod_percent ------------+------------------+---------------------+------------+------------- yewu1 | t3 | 190 | 10 | 1900.00 yewu1 | test10 | 4 | 4 | 100.00 yewu1 | t2 | 0 | 10000 | 0.00 public | pgbench_history | 0 | 0 | yewu1 | test6 | 0 | 0 | public | pgbench_tellers | 0 | 0 | yewu1 | test5 | 0 | 0 | public | pgbench_branches | 0 | 0 | yewu1 | test3 | 0 | 0 | yewu1 | test2 | 0 | 0 | public | pgbench_accounts | 0 | 0 | yewu1 | t4 | 0 | 10000 | 0.00 yewu1 | test4 | 0 | 0 | yewu1 | t1 | 0 | 0 | (14 rows)
六 性能考慮
資源使用：
- 會消耗CPU和I/O資源
- 對大表可能耗時較長
最佳實踐：
- 在低峰期執(zhí)行大表分析
- 對關(guān)鍵表設(shè)置更頻繁的自動分析
- 超大表考慮減小采樣比例
七 與VACUUM的區(qū)別
| 特性 | ANALYZE | VACUUM |
|---|---|---|
| 主要目的 | 收集統(tǒng)計信息 | 清理死元組 |
| 鎖級別 | ShareUpdateExclusive | 同左(但VACUUM FULL為排他鎖) |
| 是否回收空間 | 否 | 是(VACUUM FULL) |
| 更新統(tǒng)計信息 | 是 | 可選(ANALYZE選項) |
更多詳細信息請查看官方文檔：https://www.postgresql.org/docs/16/sql-analyze.html
PostgreSQL 中的 ANALYZE 命令不會鎖表，是安全的維護操作，可以隨時在生產(chǎn)環(huán)境執(zhí)行。如果需要重建索引（而非更新統(tǒng)計信息），則應(yīng)使用 REINDEX 命令，并注意其鎖行為。
到此這篇關(guān)于PostgreSQL中ANALYZE命令的使用的文章就介紹到這了,更多相關(guān)PostgreSQL ANALYZE內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要介紹了postgresql 除法保留小數(shù)位的實例，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
postgreSQL數(shù)據(jù)庫默認用戶postgres常用命令分享
這篇文章主要介紹了postgreSQL數(shù)據(jù)庫默認用戶postgres常用命令分享，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
PostgreSQL之分區(qū)表(partitioning)
通過合理的設(shè)計，可以將選擇一定的規(guī)則，將大表切分多個不重不漏的子表，這就是傳說中的partitioning。比如，我們可以按時間切分，每天一張子表，比如我們可以按照某其他字段分割，總之了就是化整為零，提高查詢的效能2016-11-11
這篇文章主要介紹了PostgreSQL中的collations用法詳解，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
Postgresql 實現(xiàn)查詢一個表/所有表的所有列名
這篇文章主要介紹了Postgresql 實現(xiàn)查詢一個表/所有表的所有列名，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-12-12
這篇文章主要介紹了PostgreSQL教程(十五)：系統(tǒng)表詳解,本文講解了pg_class、pg_attribute、pg_attrdef、pg_authid、pg_auth_members、pg_constraint、pg_tablespace、pg_namespace、pg_database等表的作用和字段介紹,需要的朋友可以參考下2015-05-05
這篇文章主要介紹了干貨教你在PostgreSql中使用JSON字段,通過本文可掌握在pg數(shù)據(jù)庫中如何正確使用json字段，如何進行數(shù)據(jù)查詢，在where子查詢中如何使用，以及對json值進行聚合查詢使用，需要的朋友可以參考下2022-11-11
這篇文章主要介紹了postgresql 利用xlog進行熱備操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
Vcenter清理/storage/archive空間的處理方式
通過SSH登陸到Vcenter并檢查/storage/archive目錄發(fā)現(xiàn)占用過高,該目錄用于存儲歸檔的日志文件和歷史數(shù)據(jù),解決方案是保留近30天的歸檔文件,這篇文章主要給大家介紹了關(guān)于Vcenter清理/storage/archive空間的處理方式,需要的朋友可以參考下2024-11-11
PostgreSQL中實現(xiàn)數(shù)據(jù)實時監(jiān)控和預(yù)警的步驟詳解
在 PostgreSQL 中實現(xiàn)數(shù)據(jù)的實時監(jiān)控和預(yù)警是確保數(shù)據(jù)庫性能和數(shù)據(jù)完整性的關(guān)鍵任務(wù),以下將詳細討論如何實現(xiàn)此目標,并提供相應(yīng)的解決方案和具體示例,需要的朋友可以參考下2024-07-07