PostgreSQL統(tǒng)計(jì)信息SQL調(diào)優(yōu)的“眼睛”與基石前言在PostgreSQL數(shù)據(jù)庫運(yùn)維和開發(fā)中SQL性能問題時(shí)常讓人頭疼。一條本來很快的查詢隨著數(shù)據(jù)量增長(zhǎng)突然變慢明明建了索引優(yōu)化器卻選擇全表掃描……這些問題的根源往往與統(tǒng)計(jì)信息Statistics息息相關(guān)。統(tǒng)計(jì)信息是PostgreSQL基于成本的優(yōu)化器CBO決策的唯一依據(jù)?？梢哉f統(tǒng)計(jì)信息的準(zhǔn)確與否直接決定了SQL執(zhí)行計(jì)劃的好壞。本文將深入淺出地講解PostgreSQL統(tǒng)計(jì)信息的作用、內(nèi)容、工作原理以及如何通過維護(hù)統(tǒng)計(jì)信息來高效調(diào)優(yōu)SQL。1. 統(tǒng)計(jì)信息是什么為什么重要PostgreSQL本身不“認(rèn)識(shí)”數(shù)據(jù)它依靠統(tǒng)計(jì)信息來了解表中數(shù)據(jù)的分布、行數(shù)、重復(fù)值等特征。當(dāng)執(zhí)行一條SQL時(shí)優(yōu)化器會(huì)利用這些信息估算不同執(zhí)行路徑的代價(jià)Cost并選擇代價(jià)最低的計(jì)劃。統(tǒng)計(jì)信息準(zhǔn)確→ 優(yōu)化器“看得清” → 選擇最優(yōu)計(jì)劃索引掃描、Hash Join等 → SQL飛馳。統(tǒng)計(jì)信息過時(shí)或失真→ 優(yōu)化器“盲人摸象” → 錯(cuò)誤選擇例如小表變大表仍走全表掃描 → SQL慢如蝸牛。因此調(diào)優(yōu)的第一步永遠(yuǎn)是檢查統(tǒng)計(jì)信息是否健康。2. 統(tǒng)計(jì)信息包含哪些內(nèi)容PostgreSQL的統(tǒng)計(jì)信息存儲(chǔ)在系統(tǒng)表pg_class和pg_statistic中通過視圖pg_stats可以方便地查看列級(jí)統(tǒng)計(jì)。2.1 表和索引級(jí)統(tǒng)計(jì)pg_class字段含義reltuples表或索引的行數(shù)估計(jì)值relpages占用的磁盤頁數(shù)8KB/頁這兩個(gè)值是代價(jià)估算的基礎(chǔ)。2.2 列級(jí)統(tǒng)計(jì)pg_stats字段含義調(diào)優(yōu)用途n_distinct不同值的數(shù)量負(fù)數(shù)表示比例判斷列唯一性影響索引選擇most_common_vals(MCV)最常見值列表處理高頻條件時(shí)估算更準(zhǔn)most_common_freqs對(duì)應(yīng)MCV的頻率同上histogram_bounds直方圖邊界均勻分布估算非高頻值的等值或范圍選擇率null_fracNULL值比例影響IS NULL條件correlation物理順序與邏輯順序的相關(guān)性決定索引掃描的額外IO代價(jià)avg_width平均存儲(chǔ)寬度字節(jié)影響內(nèi)存使用和排序代價(jià)3. 優(yōu)化器是如何利用統(tǒng)計(jì)信息的一條SQL從解析到執(zhí)行優(yōu)化器大致經(jīng)歷三個(gè)步驟3.1 估算選擇度Selectivity對(duì)于WHERE條件優(yōu)化器需要知道符合條件的行數(shù)占全表的比例。例如SELECT*FROMordersWHEREstatuspaid;優(yōu)化器查詢pg_stats如果status列的MCV中有paid則直接用其頻率否則利用直方圖或均勻分布估算。3.2 計(jì)算不同執(zhí)行路徑的代價(jià)代價(jià) 磁盤IO CPU計(jì)算 網(wǎng)絡(luò)忽略。每個(gè)操作順序掃描、索引掃描、連接等都有對(duì)應(yīng)的代價(jià)參數(shù)如seq_page_cost、random_page_cost結(jié)合估算的行數(shù)和塊數(shù)計(jì)算出總代價(jià)。3.3 選擇代價(jià)最小的計(jì)劃優(yōu)化器會(huì)枚舉所有可能的連接順序、掃描方式最終選擇總代價(jià)最低者。典型決策包括順序掃描 vs 索引掃描小表或返回大量數(shù)據(jù)時(shí)傾向順序掃描。Nested Loop vs Hash Join vs Merge Join根據(jù)驅(qū)動(dòng)表大小、連接條件選擇。多表連接順序盡量先過濾小表。4. 統(tǒng)計(jì)信息不準(zhǔn)確的典型后果索引失效表實(shí)際有百萬行但reltuples仍為舊值如1000優(yōu)化器認(rèn)為走索引代價(jià)高從而選擇全表掃描。連接選擇錯(cuò)誤錯(cuò)誤估計(jì)驅(qū)動(dòng)表行數(shù)導(dǎo)致本該用Hash Join卻用了Nested Loop性能急劇下降。內(nèi)存分配不當(dāng)work_mem等參數(shù)依賴估算過估或低估都會(huì)影響排序、哈希操作的效率。5. 如何維護(hù)和優(yōu)化統(tǒng)計(jì)信息5.1 保持統(tǒng)計(jì)信息及時(shí)更新開啟 autovacuum默認(rèn)開啟它會(huì)自動(dòng)在數(shù)據(jù)變化達(dá)到閾值時(shí)觸發(fā)ANALYZE更新統(tǒng)計(jì)信息。檢查是否正常運(yùn)行SELECTrelname,last_autoanalyze,autovacuum_countFROMpg_stat_user_tablesWHERErelnameyour_table;手動(dòng)執(zhí)行 ANALYZE在批量導(dǎo)入、大量UPDATE/DELETE后及時(shí)手動(dòng)分析ANALYZEyour_table;-- 只分析指定表ANALYZE;-- 分析整個(gè)庫謹(jǐn)慎使用5.2 提高統(tǒng)計(jì)信息采樣精度默認(rèn)采樣目標(biāo)default_statistics_target 100對(duì)于數(shù)據(jù)傾斜嚴(yán)重的列可增大采樣值-- 會(huì)話級(jí)臨時(shí)調(diào)整SETdefault_statistics_target200;-- 全局調(diào)整修改 postgresql.confdefault_statistics_target200-- 僅針對(duì)特定列推薦ALTERTABLEyour_tableALTERCOLUMNyour_columnSETSTATISTICS1000;調(diào)整后需重新執(zhí)行ANALYZE生效。5.3 處理多列關(guān)聯(lián)擴(kuò)展統(tǒng)計(jì)信息Extended Statistics當(dāng)多個(gè)WHERE條件之間存在依賴關(guān)系時(shí)常規(guī)統(tǒng)計(jì)假設(shè)列獨(dú)立會(huì)嚴(yán)重誤估。例如WHERE city北京 AND district海淀實(shí)際上district幾乎完全取決于city。此時(shí)可創(chuàng)建擴(kuò)展統(tǒng)計(jì)-- 創(chuàng)建多列依賴統(tǒng)計(jì)CREATESTATISTICSstats_city_district(dependencies)ONcity,districtFROMaddresses;-- 創(chuàng)建多列不同值組合統(tǒng)計(jì)更精確CREATESTATISTICSstats_city_distinct(ndistinct)ONcity,districtFROMaddresses;-- 分析表ANALYZEaddresses;然后查詢pg_stats_ext查看擴(kuò)展統(tǒng)計(jì)信息。6. 實(shí)戰(zhàn)檢查統(tǒng)計(jì)信息是否“健康”的常用SQL6.1 查看統(tǒng)計(jì)信息最后一次更新時(shí)間SELECTschemaname,tablename,last_analyze,-- 手動(dòng) ANALYZE 時(shí)間last_autoanalyze,-- autovacuum 自動(dòng)分析時(shí)間n_live_tup,-- 當(dāng)前活躍行數(shù)估計(jì)n_dead_tup-- 死元組數(shù)過大說明需要清理FROMpg_stat_user_tablesWHEREtablenameyour_table;如果last_autoanalyze很早且n_dead_tup很大說明 autovacuum 可能跟不上。6.2 對(duì)比統(tǒng)計(jì)行數(shù)與真實(shí)行數(shù)-- 統(tǒng)計(jì)信息中的行數(shù)SELECTreltuples::bigintFROMpg_classWHERErelnameyour_table;-- 真實(shí)行數(shù)精確計(jì)數(shù)大表慎用SELECTCOUNT(*)FROMyour_table;如果兩者差異超過10%~20%建議執(zhí)行ANALYZE。6.3 查看列統(tǒng)計(jì)詳情SELECTattname,n_distinct,null_frac,correlation,most_common_valsFROMpg_statsWHEREtablenameyour_tableANDattnameyour_column;7. 總結(jié)PostgreSQL的統(tǒng)計(jì)信息是優(yōu)化器的“眼睛”它決定了SQL執(zhí)行計(jì)劃的好壞。在調(diào)優(yōu)過程中請(qǐng)牢記以下幾點(diǎn)統(tǒng)計(jì)信息及時(shí)性確保autovacuum正常工作關(guān)鍵操作后手動(dòng)ANALYZE。統(tǒng)計(jì)信息準(zhǔn)確性針對(duì)傾斜列提高STATISTICS目標(biāo)必要時(shí)使用擴(kuò)展統(tǒng)計(jì)處理列關(guān)聯(lián)。定期巡檢通過系統(tǒng)視圖監(jiān)控統(tǒng)計(jì)信息狀態(tài)防患于未然。當(dāng)你遇到SQL性能突然下降時(shí)不必急于改代碼或加索引先查統(tǒng)計(jì)信息——往往能快速定位并解決問題。掌握統(tǒng)計(jì)信息就掌握了PostgreSQL調(diào)優(yōu)的主動(dòng)權(quán)。