成人国产在线小视频_日韩寡妇人妻调教在线播放_色成人www永久在线观看_2018国产精品久久_亚洲欧美高清在线30p_亚洲少妇综合一区_黄色在线播放国产_亚洲另类技巧小说校园_国产主播xx日韩_a级毛片在线免费

資訊專欄INFORMATION COLUMN

PostgreSQL CBO與統(tǒng)計(jì)信息

IT那活兒 / 2274人閱讀
PostgreSQL CBO與統(tǒng)計(jì)信息

當(dāng)數(shù)據(jù)庫(kù)執(zhí)行一條SQL時(shí),從發(fā)起SQL到返回結(jié)果集,數(shù)據(jù)庫(kù)引擎通常有2種方式用來(lái)優(yōu)化執(zhí)行計(jì)劃(執(zhí)行計(jì)劃詳見《窺探PostgreSQL執(zhí)行計(jì)劃》),從而降低資源開銷提升響應(yīng)時(shí)間。具體如下:


  1. 基于規(guī)則的優(yōu)化器(rbo,rule based optimizer),在上古版本中的數(shù)據(jù)庫(kù)較為常見,比如過(guò)去常聽DBA說(shuō)的SQL語(yǔ)句驅(qū)動(dòng)表要放前面,被驅(qū)動(dòng)表要放后面之類的云云,聽的好像很深?yuàn)W,究其本質(zhì)為數(shù)據(jù)庫(kù)引擎按照固定規(guī)則去優(yōu)化執(zhí)行計(jì)劃;這樣的規(guī)則往往較為簡(jiǎn)單比如是否有索引、驅(qū)動(dòng)表是否在前等等。舉個(gè)例子,汽車導(dǎo)航中始發(fā)地到目的地之間有多種路線,rbo中就是按距離規(guī)則來(lái)計(jì)算,不考慮實(shí)際路況比較傻瓜化,現(xiàn)在基本都已經(jīng)淘汰。


  1. 基于成本的優(yōu)化器(cbo,cost based optimizer),同樣的汽車導(dǎo)航例子,cbo中還需要參考路況是否存在堵車,是否存在限速等等,通過(guò)對(duì)比各種行程的耗時(shí)從而選擇最合適的路線。在數(shù)據(jù)庫(kù)中就是通過(guò)對(duì)比各種不同計(jì)劃(是采用hashjoin 還是nestloop,采用全表掃描還是索引掃描等等)的估算開銷(cost), 從中選擇開銷(cost)最低的計(jì)劃,相對(duì)RBO的呆板,CBO顯得較為智能,目前主流數(shù)據(jù)庫(kù)均采用CBO模式。


在CBO模式下,由于開銷都是估算值,所以精確性嚴(yán)重依賴于統(tǒng)計(jì)信息(比如表大小,行數(shù),字段數(shù)據(jù)分布情況,是否存在索引),當(dāng)表的統(tǒng)計(jì)信息越接近表的真實(shí)數(shù)據(jù)時(shí),CBO優(yōu)化器的估算值則越準(zhǔn)確,產(chǎn)生的執(zhí)行計(jì)劃也更佳準(zhǔn)確 ; 當(dāng)統(tǒng)計(jì)信息與真實(shí)數(shù)據(jù)差異過(guò)大時(shí),CBO的估算值精確性將無(wú)法得到保證,這時(shí)往往產(chǎn)生錯(cuò)誤的執(zhí)行計(jì)劃,從而引發(fā)SQL性能類故障。


了解了CBO優(yōu)化器以及統(tǒng)計(jì)信息的概述后,接下來(lái)我們將PostgreSQL為例進(jìn)行實(shí)際案例演示:


先看一個(gè)簡(jiǎn)單的執(zhí)行計(jì)劃:


由于這個(gè)查詢沒(méi)有WHERE子句,它必須掃描表中的所有行,因此計(jì)劃器只能選擇使用一個(gè)簡(jiǎn)單的順序掃描計(jì)劃。被包含在圓括號(hào)中的數(shù)字是(從左至右):


1. 0.00,估計(jì)的啟動(dòng)開銷。在輸出階段可以開始之前消耗的時(shí)間。


2.11173.00,估計(jì)的總開銷。這個(gè)估計(jì)值基于的假設(shè)是計(jì)劃結(jié)點(diǎn)會(huì)被運(yùn)行到完成,即所有可用的行都被檢索。


3.rows=500000,這個(gè)計(jì)劃結(jié)點(diǎn)輸出行數(shù)的估計(jì)值。同樣,也假定該結(jié)點(diǎn)能運(yùn)行到完成。


4.width=70,預(yù)計(jì)這個(gè)計(jì)劃結(jié)點(diǎn)輸出的行平均寬度(以字節(jié)計(jì)算)。


注意各個(gè)版本計(jì)算公式可能存在稍許差異,大致可以參考如下公式

總成本=seq_page_cost*relpages+cpu_tuple_cost*reltuples


這里涉及了postgresql幾個(gè)參數(shù):

seq_page_cost:     連續(xù)塊掃描操作的單個(gè)塊的cost. 例如全表掃描

random_page_cost:  隨機(jī)塊掃描操作的單個(gè)塊的cost. 例如索引掃描

cpu_tuple_cost:     處理每條記錄的CPU開銷(tuple:關(guān)系中的一行記錄)

cpu_index_tuple_cost:掃描每個(gè)索引條目帶來(lái)的CPU開銷

cpu_operator_cost:  操作符或函數(shù)帶來(lái)的CPU開銷.


當(dāng)前實(shí)例參數(shù)默認(rèn)設(shè)置:


當(dāng)前表統(tǒng)計(jì)信息:


帶入公式計(jì)算:

1*6173+0.01*500000 =11173;與前文Cost值11173對(duì)應(yīng)。


再來(lái)看一個(gè)索引掃描的執(zhí)行計(jì)劃:


這里是索引掃描,注意各個(gè)版本計(jì)算公式可能存在稍許差異,大致可以參考如下公式:

cost= 2*random_page_cost + cpu_tuple_cost +cpu_index_tuple_cost +100* cpu_operator_cost

其中random_page_cost *2 為先讀取indexpage, 再回表 datapage從而進(jìn)行了2次隨機(jī)IO。


我們?cè)趤?lái)看一個(gè)統(tǒng)計(jì)信息差異過(guò)大導(dǎo)致的執(zhí)行計(jì)劃錯(cuò)誤的案例:

可以看到在行數(shù)較少時(shí),雖然tid字段有索引,但是CBO考慮到索引的隨機(jī)掃描(random_page_cost)且需要加上回表(再次random_page_cost)的總Cost大于全表的seq_page_cost,則執(zhí)行計(jì)劃選擇了錯(cuò)誤的全表掃描方式。


注:

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)計(jì)信息后,CBO根據(jù)page以及tuple重新進(jìn)行Cost計(jì)算并及時(shí)更新執(zhí)行計(jì)劃為索引掃描。


注:

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)計(jì)信息的準(zhǔn)確性將直接影響到CBOCost計(jì)算的準(zhǔn)確性,可以確認(rèn)統(tǒng)計(jì)信息的維護(hù)工作將是DBA日常工作中的重中之重。下面我們?cè)趤?lái)看一下PostgreSQL中的統(tǒng)計(jì)信息知識(shí)點(diǎn)。


 PostgreSQL的統(tǒng)計(jì)收集器是一個(gè)支持收集和報(bào)告服務(wù)器活動(dòng)信息的子系統(tǒng)。目前這個(gè)收集器可以對(duì)表和索引的訪問(wèn)計(jì)數(shù),計(jì)數(shù)可以按磁盤塊和個(gè)體行來(lái)進(jìn)行。它還跟蹤每個(gè)表中的總行數(shù)、每個(gè)表的清理和分析動(dòng)作的信息。它也統(tǒng)計(jì)調(diào)用用戶定義函數(shù)的次數(shù)以及在每次調(diào)用中花費(fèi)的總時(shí)間。因?yàn)榻y(tǒng)計(jì)信息的收集給系統(tǒng)增加了一些額外負(fù)荷,系統(tǒng)可以被配置為自動(dòng)收集或部分收集或不收集信息。這由配置參數(shù)控制,如下:

track_activities允許監(jiān)控當(dāng)前被任意服務(wù)器進(jìn)程執(zhí)行的命令。

track_counts  控制是否收集關(guān)于表和索引訪問(wèn)的統(tǒng)計(jì)信息。

track_functions啟用對(duì)用戶定義函數(shù)使用的跟蹤。

track_io_timing啟用對(duì)塊讀寫次數(shù)的監(jiān)控。


這里我們將只對(duì)表級(jí)統(tǒng)計(jì)信息收集進(jìn)行介紹:

語(yǔ)法如圖:


Verbose選項(xiàng)輸出詳細(xì)的INFO信息,包含正常行,已刪除的行,數(shù)據(jù)page等等信息。

注:由于pg的mvcc采用tuple模式,刪除的行只是標(biāo)記為delete狀態(tài),數(shù)據(jù)仍然保留在表中,通過(guò)deadrows可用用來(lái)作為vacuum的判斷依據(jù),這里vacuum不在展開介紹。


表的統(tǒng)計(jì)信息收集采樣比例可以在表列級(jí)指定或使用系統(tǒng)參數(shù)default_statistics_target值;該值范圍在1-10000,可以理解為值越大采樣比例越高,耗時(shí)越長(zhǎng),默認(rèn)值為100。日常運(yùn)維過(guò)程中當(dāng)發(fā)現(xiàn)采樣比例不夠時(shí)可在表列級(jí)擴(kuò)大該值。


一旦指定列的statistics值后,PostgreSQL就不再參考默認(rèn)的default_statistics_target值,它會(huì)先去系統(tǒng)表pg_attribute的對(duì)應(yīng)表對(duì)應(yīng)字段的attstattarget值,如果是-1,表示的是該列的取樣顆粒度是采用默認(rèn)的值(default_statistics_target),如果是大于0的,那么就表示是使用著自己手動(dòng)定義的,比如我們修改t_user.tid通過(guò)STATISTICS150,查看attstattarget值的變化:


圖中tid列的statisticstarget值已更新為150.以上就是一個(gè)從CBO優(yōu)化器到統(tǒng)計(jì)信息的一個(gè)完整了解過(guò)程,本文就到此為止,咱們下回見。

文章版權(quán)歸作者所有,未經(jīng)允許請(qǐng)勿轉(zhuǎn)載,若此文章存在違規(guī)行為,您可以聯(lián)系管理員刪除。

轉(zhuǎn)載請(qǐng)注明本文地址:http://systransis.cn/yun/130118.html

相關(guān)文章

  • TiDB 源碼閱讀系列文章(八)基于代價(jià)的優(yōu)化

    摘要:概述本文是源碼閱讀系列文章的第八篇。圖中黑色字體算子為邏輯算子,藍(lán)色字體為物理算子,黃色箭頭為已經(jīng)計(jì)算過(guò)代價(jià)的算子,會(huì)獲取已經(jīng)緩存在哈希表中的結(jié)果,紅色虛線箭頭為不符合的算子。 概述 本文是 TiDB 源碼閱讀系列文章的第八篇。內(nèi)文會(huì)先簡(jiǎn)單介紹制定查詢計(jì)劃以及優(yōu)化的過(guò)程,然后用較大篇幅詳述在得到邏輯計(jì)劃后,如何基于統(tǒng)計(jì)信息和不同的屬性選擇等生成各種不同代價(jià)的物理計(jì)劃,通過(guò)比較物理計(jì)劃的...

    klivitamJ 評(píng)論0 收藏0

發(fā)表評(píng)論

0條評(píng)論

最新活動(dòng)
閱讀需要支付1元查看
<