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