在dba的日常工作中,時(shí)常會收到應(yīng)用側(cè)的通知“XX幫看看某數(shù)據(jù)庫是不是有問題,SQL跑不動了,好慢之類的等等”。這種一般都是SQL性能類問題,通過執(zhí)行計(jì)劃我們將直觀的確認(rèn)SQL性能是否存在隱患,掌握執(zhí)行計(jì)劃這項(xiàng)技能就顯得尤為重要,下面就一起來學(xué)習(xí)一下pg的執(zhí)行計(jì)劃吧。
首先我們先要搞懂SQL執(zhí)行計(jì)劃到底是個(gè)什么東東,簡單的來講就是SQL語句在數(shù)據(jù)庫內(nèi)部從取數(shù)據(jù)到返回結(jié)果集的一個(gè)完整過程,通過這個(gè)過程,我們可以看到SQL每一步的開銷,進(jìn)而判斷SQL響應(yīng)是否正常.那在pg中執(zhí)行計(jì)劃又包含4大類型,如下:
1.控制節(jié)點(diǎn)(ControlNode):
Append/Uniq組織多個(gè)字表或子查詢的執(zhí)行節(jié)點(diǎn),主要用于union操作.
2.掃描節(jié)點(diǎn)(ScanNode):
用于掃描表等對象以獲取結(jié)果集,比如常見得全表掃描(SeqScan),索引掃描(IndexScan).
3.物化節(jié)點(diǎn)(MaterializationNode):
緩存執(zhí)行結(jié)果,等待后續(xù)調(diào)用,比如用來排序、聚合計(jì)算等
4.連接節(jié)點(diǎn)(JoinNode):
多表連接時(shí),表連接方式,比如hashjoin,nestedloopjoin ,merge join 等等.
逼逼了這么多,接下來我們還是制作2張表進(jìn)行實(shí)戰(zhàn)練習(xí),畢竟實(shí)戰(zhàn)岀真知。
t_user,用戶表,并填充50萬行數(shù)據(jù)信息.
t_cust,cust表,以tid關(guān)聯(lián)t_user表,并填充150萬行數(shù)據(jù)信息.具體如下:
在pg中查看執(zhí)行計(jì)劃使用explain命令,語法如下:
EXPLAIN [ ( option[, ...] ) ] statement
EXPLAIN [ ANALYZE ][ VERBOSE ] statement
這里option可選項(xiàng)如下:
ANALYZE [boolean ]
VERBOSE [boolean ]
COSTS [ boolean]
BUFFERS [boolean ]
TIMING [ boolean]
SUMMARY [boolean ]
FORMAT { TEXT |XML | JSON | YAML }
注意EXPLAIN默認(rèn)不執(zhí)行語句,只顯示估算信息,EXPLAIN ANALYZE會實(shí)際執(zhí)行語句且輸出真實(shí)消耗信息,當(dāng)我們診斷DML語句時(shí)就要格外小心ANALYZE選項(xiàng)以免影響生產(chǎn)數(shù)據(jù)。
先來看一個(gè)簡單查詢計(jì)劃:
這里SeqScan on t_user即上文提及的掃描節(jié)點(diǎn)(ScanNode),代表著以全表掃描的方式訪問t_user表。圓括號中cost單位毫秒,0.00代表這一步計(jì)劃的啟動開銷。12423為這一步計(jì)劃的總開銷.rows為結(jié)果行數(shù),width為平均行的字節(jié)數(shù)。
注意這里都是估算,查看實(shí)際執(zhí)行的信息需要使EXPLAINANALYZE,如下:
其中actual為實(shí)際耗時(shí)信息,以及最后輸出的該語句的總時(shí)長。相較于總執(zhí)行時(shí)長60.519ms的開銷,主要都消耗在了掃描節(jié)點(diǎn)(60.494ms)上,且掃描節(jié)點(diǎn)下的Filter和RowsRemoved byFilter輸出也提示基本上是99%的過濾性;這一步計(jì)劃就可以發(fā)現(xiàn)存在優(yōu)化空間,在tid字段上建立索引再來觀察執(zhí)行計(jì)劃.如下:
可以看到表的掃描節(jié)點(diǎn)已經(jīng)更新成索引掃描(Indexscan using idx_user),sql Execution time從60ms下降到0.08ms,提升明顯。
再來看一個(gè)帶有控制節(jié)點(diǎn)的計(jì)劃:
由于這條語句使用了union操作,所以計(jì)劃中便出現(xiàn)了Append控制節(jié)點(diǎn)(ControlNode),對它的2個(gè)掃描子節(jié)點(diǎn)進(jìn)行數(shù)據(jù)合并.注意這2個(gè)子掃描節(jié)點(diǎn)由于是同層關(guān)系(以->觀察),執(zhí)行順序?qū)⒂缮现料?
接下來是帶有物化節(jié)點(diǎn)(MaterializationNode)的計(jì)劃:
這里的GroupAggregate以及Sort節(jié)點(diǎn)便是物化節(jié)點(diǎn)。其使用子掃描節(jié)點(diǎn)的數(shù)據(jù)進(jìn)行排序和分組計(jì)算。需要注意由于->存在不同層級,則最里層最先執(zhí)行,即第一步執(zhí)行SeqScan返回結(jié)果集,第二步處理的是其子項(xiàng)第一步的結(jié)果,所以這里就出現(xiàn)了物化節(jié)點(diǎn),第三步同理,處理的是第二步的結(jié)果,同樣是物化節(jié)點(diǎn)。
最后我們在來看2種常見的表連接執(zhí)行計(jì)劃:
第一種HashJoin:
既然是表連接,必然會出現(xiàn)連接節(jié)點(diǎn);那圖中黃色部分就代表這是一個(gè)使用HashJoin算法關(guān)聯(lián)的連接節(jié)點(diǎn)。根據(jù)由里至外,由上至下規(guī)則;則過程如下:
第一步:對t_user進(jìn)行全表掃描,根據(jù)tid< 150000過濾350001行,返回149999行;
第二步:對第一步的結(jié)果集進(jìn)行Hash處理生成HashBuckets;
第三步:對t_cust進(jìn)行全表掃描,返回結(jié)果集1500000行;
第四步:根據(jù)HashJoin條件,使用步驟三的結(jié)果集與HashBuckets關(guān)聯(lián)取出匹配的行,返回結(jié)果集.225044行;
第五步:對第三步hash結(jié)果集進(jìn)行聚合算并最終返回結(jié)果集1行。
第二種NestedLoop:
圖中黃色部分就代表這是一個(gè)使用NestedLoop算法關(guān)聯(lián)的連接節(jié)點(diǎn);具體執(zhí)行過程如下:
第一步:t_cust.cname字段存在索引,則使用索引掃描返回結(jié)果集.rows=3;
第二步:根據(jù)Nestedloop算法,使用步驟一的結(jié)果集(rows=3),以這3行結(jié)果集中的tid(JoinFilter)作為條件循環(huán)掃描t_user表3次(單次掃描處理500000行),最終NestedLoop過濾1499999行數(shù)據(jù).返回1行結(jié)果集(rows=1)在這個(gè)計(jì)劃中可以看到循環(huán)體內(nèi)(Seq Scan on t_user a),掃描的結(jié)果集較大,且過濾性非常高,適合在該字段上創(chuàng)建索引。
在t_user.tid字段上創(chuàng)建索引在來觀察執(zhí)行計(jì)劃:
可以看到仍然是NestedLoop表關(guān)聯(lián),其中子項(xiàng)掃描節(jié)點(diǎn)變化為同層關(guān)系;具體過程如下:
第一步:索引掃描t_cust表,返回結(jié)果集rows=3;
第二步:根據(jù)NestedLoop算法,使用步驟一結(jié)果集中的tid循序使用新建的tdx_user_tid索引掃描3次,并最終返回結(jié)果集rows=3。
從整個(gè)執(zhí)行時(shí)間來看從6271ms縮短到0.151ms,提升非常明顯.
從上面2個(gè)表關(guān)聯(lián)案例中我們可以發(fā)現(xiàn)HashJoin適用于大結(jié)果集的表關(guān)聯(lián),并不關(guān)注最終返回結(jié)果集,分析型場景較多;而NestedLoop往往最終返回結(jié)果集較小,通常都是交易型場景,要求驅(qū)動表謂詞條件相對精確,縮小結(jié)果集以降低循環(huán)次數(shù),以及被驅(qū)動表的關(guān)聯(lián)條件上需要?jiǎng)?chuàng)建索引,提升循環(huán)體內(nèi)部的掃描效率;總體而言NestedLoop出現(xiàn)問題的情況比較多,需要額外注意。
通過以上幾種案例解讀,可以發(fā)現(xiàn)各種數(shù)據(jù)庫執(zhí)行計(jì)劃都大同小異 ,問題的類型以及優(yōu)化思路也基本類似,本文就到此為止,后續(xù)小短文敬請期待。
文章版權(quán)歸作者所有,未經(jīng)允許請勿轉(zhuǎn)載,若此文章存在違規(guī)行為,您可以聯(lián)系管理員刪除。
轉(zhuǎn)載請注明本文地址:http://specialneedsforspecialkids.com/yun/130161.html
摘要:年之前,微信支付業(yè)務(wù)快速發(fā)展,需要一款數(shù)據(jù)庫能夠安全高效的支撐微信支付商戶系統(tǒng)核心業(yè)務(wù),這個(gè)重任落在了騰訊數(shù)據(jù)庫團(tuán)隊(duì)自研上。由于是用于微信支付的核心數(shù)據(jù)庫,騰訊被定位為安全高效,穩(wěn)定,可靠的數(shù)據(jù)庫集群。 歡迎大家前往騰訊云+社區(qū),獲取更多騰訊海量技術(shù)實(shí)踐干貨哦~ 本文由李躍森發(fā)表于云+社區(qū)專欄李躍森,騰訊云PostgreSQL首席架構(gòu)師,騰訊數(shù)據(jù)庫團(tuán)隊(duì)架構(gòu)師,負(fù)責(zé)微信支付商戶系統(tǒng)核心數(shù)...
摘要:前面我們簡單闡述了分布式數(shù)據(jù)庫的架構(gòu),并通過一條簡單的查詢語句解釋了分布式的執(zhí)行計(jì)劃。 引言 第八屆中國架構(gòu)師大會(SACC2016)10月27號到29號在北京萬達(dá)索菲特大飯店成功舉辦。大會以架構(gòu)創(chuàng)新之路為主題,云集了國內(nèi)外頂尖專家,共同探討云計(jì)算和大數(shù)據(jù)等技術(shù)背景下,如何通過架構(gòu)創(chuàng)新及各種IT新技術(shù)來帶動企業(yè)轉(zhuǎn)型增效。作為一家專注于云端數(shù)據(jù)倉庫的初創(chuàng)公司,酷克數(shù)據(jù)受邀在SACC201...
摘要:收集有關(guān)數(shù)據(jù)庫中表的內(nèi)容的統(tǒng)計(jì)信息。預(yù)計(jì)的該規(guī)劃節(jié)點(diǎn)的行平均寬度單位字節(jié)。其中上層節(jié)點(diǎn)的開銷將包括其所有子節(jié)點(diǎn)的開銷。一般而言,頂層的行預(yù)計(jì)數(shù)量會更接近于查詢實(shí)際返回的行數(shù)。 問題場景 有model Account,SQLAlchemy 查詢語句如下: query = Account.query.filter(Account.id.in_(account_ids)).order_by(...
摘要:收集有關(guān)數(shù)據(jù)庫中表的內(nèi)容的統(tǒng)計(jì)信息。預(yù)計(jì)的該規(guī)劃節(jié)點(diǎn)的行平均寬度單位字節(jié)。其中上層節(jié)點(diǎn)的開銷將包括其所有子節(jié)點(diǎn)的開銷。一般而言,頂層的行預(yù)計(jì)數(shù)量會更接近于查詢實(shí)際返回的行數(shù)。 問題場景 有model Account,SQLAlchemy 查詢語句如下: query = Account.query.filter(Account.id.in_(account_ids)).order_by(...
閱讀 1353·2023-01-11 13:20
閱讀 1699·2023-01-11 13:20
閱讀 1211·2023-01-11 13:20
閱讀 1901·2023-01-11 13:20
閱讀 4161·2023-01-11 13:20
閱讀 2745·2023-01-11 13:20
閱讀 1394·2023-01-11 13:20
閱讀 3664·2023-01-11 13:20
极致性价比!云服务器续费无忧!
Tesla A100/A800、Tesla V100S等多种GPU云主机特惠2折起,不限台数,续费同价。
NVIDIA RTX 40系,高性价比推理显卡,满足AI应用场景需要。
乌兰察布+上海青浦,满足东推西训AI场景需要