db2執行計畫介紹

標籤：方式   索引   對象   bdc   oci   mmu   ima   介紹   最佳化   

在資料庫調優過程中，SQL語句往往是導致效能問題的主要原因，而執行計畫則是解釋SQL語句執行過程的語言，只有充分讀懂執行計畫才能在資料庫效能最佳化中做到遊刃有餘。

 

常見的關係型資料庫中，雖然執行計畫的表示方法各自不同，但執行原理卻大同小異。在我看來，SQL語句的執行過程中總共包含兩個關鍵環節：

• 讀取資料的方式（scan）：包含表掃描和索引掃描

• 表之間如何進行串連（join）：包含Nest Loop 、Merge Join、Hash

  join及半串連等、多表間的串連順序選擇

 

 

只要掌握這兩個關鍵環節，我們就可以迅速識別SQL語句在當前資料庫中的執行邏輯，發現執行計畫中存在的問題及隱患。由於不同資料庫之間對於執行計畫的表示方法各不相同。對於DB2資料庫，很多人並不能理解執行計畫所表達的含義，接下來我們就通過實際案例詳細探討DB2資料庫中執行計畫的各個要點。

 

01

讀取資料的方式

談到讀取資料就離不開資料庫中的物理對象：表及索引。因此談到資料讀取的方式，只需理解表掃描及索引掃描，就能基本把握資料的來源。

02

全表掃描

 

在DB2 LUW中可以通過四種方式擷取語句的執行計畫（db2expln工具），雖然工具不同，但展示的內容基本相似，最大的區別就是詳細程度。詳細程度由大到小排序分別是：。為了方便展示，我們在這裡只討論通過db2expln所展示的執行計畫資訊。首先我們來看一個db2expln抓取的完整的執行計畫。

 

db2expln展示的資訊總共分為三部分，分別是當前SQL 陳述式，執行計畫詳細資料及執行計畫圖示（需添加-g參數才能展示），第一部分是當前採集執行計畫的語句（見圖1-1）：

 

圖1-1

 

這部分內容基本一目瞭然，需要注意的就是裡面:HV … :HI… 的資訊是DB2將參數改寫的資訊（基本可以忽略）。

 

接下來我們來看最關鍵的部分，執行計畫的詳細資料（見圖1-2）：

 

 

這部分首先包括當前環境的字元集（codepage），下面是根據統計資訊評估出的成本及返回行數。在這個例子中執行成本為124470，返回行數為1行。從第6行開始的位置內容比較重要，我們採取逐行解釋：

 

第三部分為執行計畫圖，我們可以通過執行計畫圖快速直接地看出SQL語句的執行過程，執行計畫圖的讀取順序是從下往上，從左往右，按照編號從大到小的順序進行閱讀。比如在這個例子中，首先看第三步，顯示對錶進行表掃描操作（TBSCAN），然後對掃描的結果進行group by操作並將最終結果返回，這條SQL語句就執行完畢了。

 

 

03

索引掃描

接下來來看個索引掃描的例子，為了快速理解這個執行計畫，我們直接先看執行計畫圖，可以看到這個SQL語句首先讀取索引，擷取RID後到表裡擷取其他資料，進行group by後將結果返回。

 

 

 

其他部分和上一個例子差不多，就不一一詳細介紹了，主要看索引掃描的相關細節。從下面的資訊可以看出用到的索引中包含4個欄位，但這條SQL只用到了一個欄位。其他三個欄位都沒用使用。如果該表上有其他索引包含這條SQL所使用的更多的欄位時，這個索引肯定不是最佳選擇。

 

 

 

資料讀取的方式還有更多的細節，這裡暫時不一一討論了，但不論對資料採用何種方式讀取，最核心的內容還是資料從哪裡讀取，簡單來說就是有沒有更好的索引可以替代當前的掃描策略，所以，當我們對SQL語句進行最佳化時，第一步就是需要考慮當前的讀取方式是否足夠有效。 

 

04

表串連的方式

接下來我們來談表之間的串連，寫過SQL的童鞋都知道，寫SQL時Join方式可以有很多種情況：inner join,left join,right join,full join等，還包括一些子查詢，比如exist 或者In等方式。對於星型查詢，DB2 10以後還支援ZZJOIN。

05

Nest Loop(NLJOIN)

Nest Loop是最簡單的一種串連方式，資料庫會根據表中的記錄數選擇內表及外表，在定義內外表後，首先會對外表進行全表掃描，然後重複掃描內表並與外表中的每一條記錄進行匹配，最終返回程式所需的結果集。

 

 

 

因此NLJOIN的總成本大約為外表掃描的成本+外表返回的行數×內表掃描的成本。NLJOIN作為使用情境最多的串連方式，當外表匹配行數較少或內外表行數差距較大時效率較高，但也正因為NLJOIN的運行方式，也經常會發生效能隱患。 

06

Merge Join(MSJOIN)

合并串連是為瞭解決Nest Loop中存在的一些問題所採用的一種串連方式，MSJOIN會將需要串連的兩張表進行排序，並將排序後的結果集按照交叉方式匹配，最終返回串連後的結果。

 

 

 

MSJOIN的總成本大約為單次外表掃描的成本＋單次內表掃描的成本＋排序成本。MSJOIN常見的情境通常是SQL需要返回排序結果，亦或者主外表都比較大的情況，此外MSJOIN只能應用於SQL語句中包含唯一串連謂詞的情況，當主外表數量級都比較大，且串連謂詞上都存在索引時，MSJOIN的效率較高（避免了排序成本），通常MSJOIN比較穩定，即使統計資訊估算錯誤，也不會導致執行效率出現較大的偏差。

07

Hash join(HSJOIN)

 

HSJOIN是一種比較進階的串連方式，進行串連前首先會將外表根據串連謂詞進行雜湊產生雜湊表，然後將雜湊表與內表進行串連並返回結果。與MSJOIN類似，HSJOIN也只對內外表分別進行一次掃描，同時HSJOIN也支援多串連謂詞。在兩張大表通過多串連謂詞進行串連時效率很高。 

 

 

HSJOIN的掃描成本約為內表掃描成本＋外表掃描成本。但需要注意的是，產生的雜湊表會存放在排序堆中，一旦排序堆記憶體溢出，會額外產生大量的物理IO，這點需要特別注意。

08

半串連（semi-join）

半串連屬於一種比較奇怪的串連方式，在很多資料裡並沒有將其劃分到串連方式中，因為有的時候，從執行計畫中根本看不到串連操作符，比如下面這個SQL：

 

這是一個典型的子查詢，我們可以從SQL語句中猜出大概邏輯，首先會讀取子查詢中的表，然後根據返回的內容與外部表格進行匹配並返回結果。但從執行計畫圖中並不能看到任何關於串連的資訊。

 

 

執行計畫圖中並沒有顯示任何join的資訊，只是多個對象進行了fetch，但從文字描述中可以看到更詳細的內容。

 

 

 

資料流1首先會對內部表進行全表掃描（ANY／ALLSubquery），讀取後的結果與外部表格進行匹配，匹配到結果後不繼續掃描立刻返回結果（EXISTS Subquery）。

 

09

多表間的串連順序選擇

 

不論在同一條SQL語句中包含了多少張表串連，在同一時刻只有兩張表進行串連，但多表間的串連順序也是決定效能的主要原因。資料庫對於表的順序的選擇，往往根據兩個表之間串連後得出的行數進行排序，如果統計資訊與實際情況偏差較大，有可能會導致由於串連順序不當而導致的效能問題。 

10

總結

通過對執行計畫的解析，我們講解了SQL執行過程中對於效能影響較大的各個要點，但如何在生產上保持SQL的高效穩定，還需對執行計畫進行更深入的理解。下面就這方面解答一些同學們常見的疑問：

 

Q & A

Q1：在查詢時，有一個驅動表，通常是from後的第一個表，後面一堆左串連右串連，這個驅動表如何選擇？對效能有影響嗎？自己人為該順序不會影響執行計畫？

A1：在資料庫中，會根據當前表的情況進行內外表的選擇，SQL語句中的寫法只能從一定程度上決定串連次序，但不會做串連中內外表的決策。

舉個例子，select*from a,(select*from b,c where b.id=c.id)where……，比如這個SQL，在寫法中指明了需要先將b c表串連，再與a表串連，但在串連時候的方式以及串連時候內表外表的選擇，都由資料庫決定。

 

 

Q2：關於串連方式的選擇，是由串連的兩個表和串連的欄位是否排序決定的？

A2：這個不絕對，但是會作為選擇的因素之一。

 

Q4：訪問某表的access plan改變了，統計資訊沒變，是什麼情況？這是最佳化器自動調整了嗎？可是最佳化器根據統計資訊產生訪問計劃，按道理應該是不會變的啊？

A4：執行計畫的選擇會根據資料庫參數，統計資訊作為參考，但在編譯過程中資料庫還會收集一些物理資訊。比如資料的物理分布可能會對掃描的方式產生影響。

 

Q5：這個物理資訊是什麼，是資料表空間資訊嗎？

A5：表在物理中存放的情況。

 

Q6：有什麼手段跟蹤一個SQL完整的執行過程，包括你說的動態收集物理資訊？

A6：可以抓trace或者stack。db2trc，和db2pd –stack。

  

Q7：老師，db2的最佳化器是對越複雜的sql支援的越好嗎？有這個說法嗎？

A7：db2的最佳化器對複雜SQL的支援在關係型資料庫裡應該是最好的，但是對於聯機交易系統來講，我覺得SQL的穩定性比較重要。但複雜SQL牽扯到的變化因素太多，任何一個表的統計資訊改變都有可能導致SQL效能下降，所以在聯機交易系統不太推薦寫複雜SQL。

 

Q8：那我們寫sql時該怎麼注意呢？NL join類似笛卡爾集，時間複雜度最高，其次是merge。我覺得從sql上避免不了，因為選擇了那個列，就基本確定了連線類型。

A8：在編寫SQL的時候很難決定用什麼串連方式，但有些需要注意的地方，比如避免多張大表串連，這些在開發過程中還是可以辦到的。

 

Q9：hash串連，如果探測表很大，內建表很小，hash的成本顯示很高，因為探測表做了表掃描，沒有用到索引，這種如何最佳化，只能減少探測表的返回集嗎？

A9：可以在探測表上建立適當索引。

 

Q10：對錶做完統計更新後需要做rbind嗎？

A10：這個需要取決於你的應用是靜態SQL還是動態SQL。靜態SQL的話執行計畫在bind的時候保持在資料庫中，統計資訊更新後建議rebind，但動態就沒必要了。

  

Q11：通常謂詞出現在索引的第一個欄位應該就是有效索引，可有時候這個索引存在，但是個複合索引，跑db2advis時卻建議在這個謂詞上建立新的單一的索引，為什麼資料庫不用現有的複合？

A11：複合索引並不一定高效，這個需要根據資料分布來判斷，如果單一索引的Clusterration非常好（也就是和表存放的順序匹配度非常高），這樣可以用到大量預取操作，效能會比同步讀好很多。

 

Q12：嵌入式C、C++、COBOL的包BND（包括靜態SQL），要綁，  使用者SP也建議綁定吧？

A12：UDI的成本其實很大程度上和表設計有關。比如在做DML語句的時候發生行溢出和頁重組，帶來的消耗遠大於**索引。相關資訊可以看db2pd -tcb或者snapshot for table。

 

Q13：請問一下對於表壓縮有什麼建議？比如要做大表的壓縮，有沒有一些量化指標供參考，因為有一些表開了壓縮批次**較多記錄時候影延長了批次1/3的時間。

A13：對於壓縮，需要分析當前資料庫的瓶頸在哪。壓縮是以cpu為代價降低磁碟io，如果瓶頸在磁碟io上，肯定會有協助，但如果瓶頸在cpu上只會雪上加霜。

 

Q14：調整APPENDON呢？有沒有量化的一些指標？

A14：這個不是很好量化。對於磁碟io瓶頸，可以先從索引，語句甚至表設計入手。如果都已經調整到很好了但還是存在iO瓶頸，同時CPU使用率又比較低（30-40以下）。可以考慮壓縮。

db2執行計畫介紹