在dba的日常工作中,時常會收到應用側的通知“XX幫看看某數據庫是不是有問題,SQL跑不動了,好慢之類的等等”。這種一般都是SQL性能類問題,通過執行計劃我們將直觀的確認SQL性能是否存在隱患,掌握執行計劃這項技能就顯得尤為重要,下面就一起來學習一下pg的執行計劃吧。
首先我們先要搞懂SQL執行計劃到底是個什么東東,簡單的來講就是SQL語句在數據庫內部從取數據到返回結果集的一個完整過程,通過這個過程,我們可以看到SQL每一步的開銷,進而判斷SQL響應是否正常.那在pg中執行計劃又包含4大類型,如下:
1.控制節點(ControlNode):
2.掃描節點(ScanNode):
3.物化節點(MaterializationNode):
4.連接節點(JoinNode):
多表連接時,表連接方式,比如hashjoin,nestedloopjoin ,merge join 等等.
t_user,用戶表,并填充50萬行數據信息.
t_cust,cust表,以tid關聯t_user表,并填充150萬行數據信息.具體如下:
在pg中查看執行計劃使用explain命令,語法如下:
EXPLAIN [ ( option[, ...] ) ] statement
EXPLAIN [ ANALYZE ][ VERBOSE ] statement
ANALYZE [boolean ]
VERBOSE [boolean ]
COSTS [ boolean]
BUFFERS [boolean ]
TIMING [ boolean]
SUMMARY [boolean ]
FORMAT { TEXT |XML | JSON | YAML }
先來看一個簡單查詢計劃:
這里SeqScan on t_user即上文提及的掃描節點(ScanNode),代表著以全表掃描的方式訪問t_user表。圓括號中cost單位毫秒,0.00代表這一步計劃的啟動開銷。12423為這一步計劃的總開銷.rows為結果行數,width為平均行的字節數。
注意這里都是估算,查看實際執行的信息需要使EXPLAINANALYZE,如下:
其中actual為實際耗時信息,以及最后輸出的該語句的總時長。相較于總執行時長60.519ms的開銷,主要都消耗在了掃描節點(60.494ms)上,且掃描節點下的Filter和RowsRemoved byFilter輸出也提示基本上是99%的過濾性;這一步計劃就可以發現存在優化空間,在tid字段上建立索引再來觀察執行計劃.如下:
再來看一個帶有控制節點的計劃:
接下來是帶有物化節點(MaterializationNode)的計劃:
最后我們在來看2種常見的表連接執行計劃:
第一種HashJoin:
既然是表連接,必然會出現連接節點;那圖中黃色部分就代表這是一個使用HashJoin算法關聯的連接節點。根據由里至外,由上至下規則;則過程如下:
第一步:對t_user進行全表掃描,根據tid< 150000過濾350001行,返回149999行;
第二步:對第一步的結果集進行Hash處理生成HashBuckets;
第三步:對t_cust進行全表掃描,返回結果集1500000行;
第四步:根據HashJoin條件,使用步驟三的結果集與HashBuckets關聯取出匹配的行,返回結果集.225044行;
第五步:對第三步hash結果集進行聚合算并最終返回結果集1行。
第二種NestedLoop:
圖中黃色部分就代表這是一個使用NestedLoop算法關聯的連接節點;具體執行過程如下:
第一步:t_cust.cname字段存在索引,則使用索引掃描返回結果集.rows=3;
第二步:根據Nestedloop算法,使用步驟一的結果集(rows=3),以這3行結果集中的tid(JoinFilter)作為條件循環掃描t_user表3次(單次掃描處理500000行),最終NestedLoop過濾1499999行數據.返回1行結果集(rows=1)在這個計劃中可以看到循環體內(Seq Scan on t_user a),掃描的結果集較大,且過濾性非常高,適合在該字段上創建索引。
在t_user.tid字段上創建索引在來觀察執行計劃:
第一步:索引掃描t_cust表,返回結果集rows=3;
第二步:根據NestedLoop算法,使用步驟一結果集中的tid循序使用新建的tdx_user_tid索引掃描3次,并最終返回結果集rows=3。
從整個執行時間來看從6271ms縮短到0.151ms,提升非常明顯。
從上面2個表關聯案例中我們可以發現HashJoin適用于大結果集的表關聯,并不關注最終返回結果集,分析型場景較多;而NestedLoop往往最終返回結果集較小,通常都是交易型場景,要求驅動表謂詞條件相對精確,縮小結果集以降低循環次數,以及被驅動表的關聯條件上需要創建索引,提升循環體內部的掃描效率;總體而言NestedLoop出現問題的情況比較多,需要額外注意。
通過以上幾種案例解讀,可以發現各種數據庫執行計劃都大同小異 ,問題的類型以及優化思路也基本類似,本文就到此為止,后續小短文敬請期待。
文章版權歸作者所有,未經允許請勿轉載,若此文章存在違規行為,您可以聯系管理員刪除。
轉載請注明本文地址:http://specialneedsforspecialkids.com/yun/129249.html
摘要:年之前,微信支付業務快速發展,需要一款數據庫能夠安全高效的支撐微信支付商戶系統核心業務,這個重任落在了騰訊數據庫團隊自研上。由于是用于微信支付的核心數據庫,騰訊被定位為安全高效,穩定,可靠的數據庫集群。 歡迎大家前往騰訊云+社區,獲取更多騰訊海量技術實踐干貨哦~ 本文由李躍森發表于云+社區專欄李躍森,騰訊云PostgreSQL首席架構師,騰訊數據庫團隊架構師,負責微信支付商戶系統核心數...
摘要:收集有關數據庫中表的內容的統計信息。預計的該規劃節點的行平均寬度單位字節。其中上層節點的開銷將包括其所有子節點的開銷。一般而言,頂層的行預計數量會更接近于查詢實際返回的行數。 問題場景 有model Account,SQLAlchemy 查詢語句如下: query = Account.query.filter(Account.id.in_(account_ids)).order_by(...
摘要:收集有關數據庫中表的內容的統計信息。預計的該規劃節點的行平均寬度單位字節。其中上層節點的開銷將包括其所有子節點的開銷。一般而言,頂層的行預計數量會更接近于查詢實際返回的行數。 問題場景 有model Account,SQLAlchemy 查詢語句如下: query = Account.query.filter(Account.id.in_(account_ids)).order_by(...
閱讀 1347·2023-01-11 13:20
閱讀 1686·2023-01-11 13:20
閱讀 1133·2023-01-11 13:20
閱讀 1861·2023-01-11 13:20
閱讀 4104·2023-01-11 13:20
閱讀 2705·2023-01-11 13:20
閱讀 1386·2023-01-11 13:20
閱讀 3599·2023-01-11 13:20