PostgreSQL CBO與統(tǒng)計信息
點擊上方“IT那活兒”公眾號，關(guān)注后了解更多內(nèi)容，不管IT什么活兒，干就完了！！！當(dāng)數(shù)據(jù)庫執(zhí)行一條SQL時，從發(fā)起SQL到返回結(jié)果集，數(shù)據(jù)庫引擎通常有2種方式用來優(yōu)化執(zhí)行計劃(執(zhí)行計劃詳見《窺探PostgreSQL執(zhí)行計劃》),從而降低資源開銷提升響應(yīng)時間。具體如下：在CBO模式下，由于開銷都是估算值，所以精確性嚴(yán)重依賴于統(tǒng)計信息(比如表大小,行數(shù),字段數(shù)據(jù)分布情況,是否存在索引),當(dāng)表的統(tǒng)計信息越接近表的真實數(shù)據(jù)時,CBO優(yōu)化器的估算值則越準(zhǔn)確,產(chǎn)生的執(zhí)行計劃也更佳準(zhǔn)確 ; 當(dāng)統(tǒng)計信息與真實數(shù)據(jù)差異過大時,CBO的估算值精確性將無法得到保證，這時往往產(chǎn)生錯誤的執(zhí)行計劃，從而引發(fā)SQL性能類故障。了解了CBO優(yōu)化器以及統(tǒng)計信息的概述后，接下來我們將PostgreSQL為例進行實際案例演示：由于這個查詢沒有WHERE子句，它必須掃描表中的所有行，因此計劃器只能選擇使用一個簡單的順序掃描計劃。被包含在圓括號中的數(shù)字是（從左至右）：
- 0.00,估計的啟動開銷。在輸出階段可以開始之前消耗的時間。
- 11173.00,估計的總開銷。這個估計值基于的假設(shè)是計劃結(jié)點會被運行到完成，即所有可用的行都被檢索。
- rows=500000,這個計劃結(jié)點輸出行數(shù)的估計值。同樣，也假定該結(jié)點能運行到完成。
- width=70,預(yù)計這個計劃結(jié)點輸出的行平均寬度（以字節(jié)計算）。
注意各個版本計算公式可能存在稍許差異，大致可以參考如下公式：總成本=seq_page_cost*relpages+cpu_tuple_cost*reltuples這里涉及了postgresql幾個參數(shù)：
- seq_page_cost：連續(xù)塊掃描操作的單個塊的cost. 例如全表掃描.
- random_page_cost：隨機塊掃描操作的單個塊的cost. 例如索引掃描.
- cpu_tuple_cost：處理每條記錄的CPU開銷（tuple：關(guān)系中的一行記錄）.
- cpu_index_tuple_cost：掃描每個索引條目帶來的CPU開銷.
- cpu_operator_cost：操作符或函數(shù)帶來的CPU開銷.
當(dāng)前實例參數(shù)默認(rèn)設(shè)置：1*6173+0.01*500000 =11173;與前文Cost值11173對應(yīng)。
這里是索引掃描，注意各個版本計算公式可能存在稍許差異，大致可以參考如下公式：cost= 2*random_page_cost + cpu_tuple_cost +cpu_index_tuple_cost +100* cpu_operator_cost其中random_page_cost *2 為先讀取indexpage, 再回表 datapage從而進行了2次隨機IO。我們再來看一個統(tǒng)計信息差異過大導(dǎo)致的執(zhí)行計劃錯誤的案例：可以看到在行數(shù)較少時，雖然tid字段有索引，但是CBO考慮到索引的隨機掃描(random_page_cost)且需要加上回表(再次random_page_cost)的總Cost大于全表的seq_page_cost,則執(zhí)行計劃選擇了錯誤的全表掃描方式。注：
Cost全表=1* 4+ 59*0.01 = 4.59,與圖中估算值1.74存在些許差異.
Cost索引=2*4+0.01+0.25=8.26,與圖中估算值8.44存在些許差異.
由于全表Cost比索引Cost低，則CBO優(yōu)化器將選擇全表掃描方式.
在我們發(fā)起analyzetable更新統(tǒng)計信息后,CBO根據(jù)page以及tuple重新進行Cost計算并及時更新執(zhí)行計劃為索引掃描。注：
- Cost全表=3703*4 + 299908*0.01 =15111;大致估算值.
- Cost索引=2*4+0.01+0.25=8.26,與圖中估算值8.44存在些許差異.
- 由于索引Cost比全表Cost低，則CBO優(yōu)化器將選擇索引掃描方式.
由于統(tǒng)計信息的準(zhǔn)確性將直接影響到CBOCost計算的準(zhǔn)確性，可以確認(rèn)統(tǒng)計信息的維護工作將是DBA日常工作中的重中之重。下面我們在來看一下PostgreSQL中的統(tǒng)計信息知識點：PostgreSQL的統(tǒng)計收集器是一個支持收集和報告服務(wù)器活動信息的子系統(tǒng)。目前這個收集器可以對表和索引的訪問計數(shù)，計數(shù)可以按磁盤塊和個體行來進行。它還跟蹤每個表中的總行數(shù)、每個表的清理和分析動作的信息。它也統(tǒng)計調(diào)用用戶定義函數(shù)的次數(shù)以及在每次調(diào)用中花費的總時間。因為統(tǒng)計信息的收集給系統(tǒng)增加了一些額外負(fù)荷，系統(tǒng)可以被配置為自動收集或部分收集或不收集信息。這由配置參數(shù)控制，如下：
- track_activities允許監(jiān)控當(dāng)前被任意服務(wù)器進程執(zhí)行的命令。
- track_counts 控制是否收集關(guān)于表和索引訪問的統(tǒng)計信息。
- track_functions啟用對用戶定義函數(shù)使用的跟蹤。
- track_io_timing啟用對塊讀寫次數(shù)的監(jiān)控。
這里我們將只對表級統(tǒng)計信息收集進行介紹：Verbose選項輸出詳細(xì)的INFO信息,包含正常行，已刪除的行,數(shù)據(jù)page等等信息。注：由于pg的mvcc采用tuple模式,刪除的行只是標(biāo)記為delete狀態(tài)，數(shù)據(jù)仍然保留在表中，通過deadrows可用用來作為vacuum的判斷依據(jù),這里vacuum不再展開介紹。表的統(tǒng)計信息收集采樣比例可以在表列級指定或使用系統(tǒng)參數(shù)default_statistics_target值;該值范圍在1-10000，可以理解為值越大采樣比例越高，耗時越長,默認(rèn)值為100。日常運維過程中當(dāng)發(fā)現(xiàn)采樣比例不夠時可在表列級擴大該值。一旦指定列的statistics值后，PostgreSQL就不再參考默認(rèn)的default_statistics_target值，它會先去系統(tǒng)表pg_attribute的對應(yīng)表對應(yīng)字段的attstattarget值：
- 如果是-1，表示的是該列的取樣顆粒度是采用默認(rèn)的值（default_statistics_target）；
- 如果是大于0的，那么就表示是使用著自己手動定義的。
比如我們修改t_user.tid通過STATISTICS150，查看attstattarget值的變化：
圖中tid列的statisticstarget值已更新為150.以上就是一個從CBO優(yōu)化器到統(tǒng)計信息的一個完整了解過程，本文就到此為止。
文章版權(quán)歸作者所有，未經(jīng)允許請勿轉(zhuǎn)載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。
轉(zhuǎn)載請注明本文地址：http://specialneedsforspecialkids.com/yun/129247.html
