Oracle2PG系列之管理SQL執(zhí)行計(jì)劃

IT那活兒發(fā)布于2023-01-11 13:20 / 1309人閱讀

點(diǎn)擊上方“IT那活兒”，關(guān)注后了解更多精彩內(nèi)容?。?！

前言

在我們?nèi)粘＿\(yùn)維過(guò)程中或多或少都會(huì)碰到SQL執(zhí)行計(jì)劃突變的問(wèn)題，應(yīng)急性解決方案基本都繞不開(kāi)固定執(zhí)行計(jì)劃，Oracle也提供了豐富的工具outline、coe等。在我們最近的老案例Oracle遷移PG中,測(cè)試也發(fā)現(xiàn)了部分SQL在PG庫(kù)中的執(zhí)行計(jì)劃性能較差，由于應(yīng)用遷移工期緊以及程序代碼繁多SQL邏輯層改造成本大，最終使用pg_show_plans與pg_hint_plan這2個(gè)社區(qū)插件在數(shù)據(jù)庫(kù)層調(diào)整執(zhí)行計(jì)劃解決SQL性能問(wèn)題。今天我們就來(lái)總結(jié)一下。

執(zhí)行計(jì)劃

首先PG原生提供了explain命令來(lái)查看SQL執(zhí)行計(jì)劃，但是這個(gè)工具不能查看正在執(zhí)行的SQL執(zhí)行計(jì)劃,這點(diǎn)非常不人性化。而我們根據(jù)query text重新構(gòu)造SQL不僅費(fèi)時(shí)而且DML語(yǔ)句還容易引起誤操作。索性社區(qū)提供了插件pg_show_plans這個(gè)插件可以幫助我們查詢pg_stat_activity中正在執(zhí)行的SQL執(zhí)行計(jì)劃，支持PG主流版本9.5~14。

具體文檔以及安裝這里不在過(guò)多贅述,可以參考：

https://github.com/cybertec-postgresql/pg_show_plans:

安裝完成后,我們構(gòu)建用戶表50萬(wàn)行，類型表500萬(wàn)行，并使用uid進(jìn)行關(guān)聯(lián) ,如下：

使用pg_show_plans進(jìn)行觀察pid：19405的會(huì)話。

圖中plan字段就是實(shí)際的執(zhí)行計(jì)劃，內(nèi)容相對(duì)簡(jiǎn)單,但還是能夠看到關(guān)鍵信息關(guān)聯(lián)條件以及tb_classinfo表的Filter條件、返回行數(shù)等。案例中就是tb_classinfo 表根據(jù)cid 4301211進(jìn)行全表掃描過(guò)濾返回1行數(shù)據(jù)再與tb_userinfo關(guān)聯(lián)查詢，老司機(jī)一看就知道可以在tb_classinfo的cid字段創(chuàng)建索引。

建完索引后，效率提升還是很明顯的。

接下來(lái)就來(lái)看如何綁定一個(gè)SQL的執(zhí)行計(jì)劃,這里社區(qū)插件也相對(duì)較多，我們采用pg_hint_plan。

具體可以參考下面的連接:

https://github.com/ossc-db/pg_hint_plan

繼續(xù)使用上文的案例，我們?cè)趖b_classinfo的cid創(chuàng)建索引后SQL使用索引掃描，這里我們使用Hint改變執(zhí)行計(jì)劃。

使用Hint seqscan成功改變計(jì)劃，我們便可以將此方式存入plan hint表固定這類SQL的執(zhí)行計(jì)劃。

注意在綁定時(shí)常量值可以使用？代替。

在計(jì)劃綁定后，重新發(fā)起SQL查詢,pg_show_plans插件成功捕獲到該SQL并打印出執(zhí)行計(jì)劃是我們綁定的計(jì)劃，在這個(gè)過(guò)程中我們發(fā)現(xiàn)計(jì)劃存入plan table時(shí)沒(méi)有標(biāo)志為，且show_plans里的計(jì)劃也沒(méi)有提示來(lái)自于哪個(gè)plan table的綁定，這方面還不夠友好。

另外Hint種類繁多具體可以參考文檔這里就不再一一介紹,本文就到此為止！