11月17日，ibm資深軟體工程師劉俊老師在db2使用者群進行了一次“淺析db2優化器和成本模型”的線上主題分享。小編特别整理出其中精華内容，供大家學習交流。
嘉賓簡介
ibm資深軟體工程師
自2005年以來一直從事db2性能優化的産品研發，包括visual explain、optimization service center、optimization expert等，在db2查詢優化和性能調優技術上具有多年實踐經驗
幫助ibm技術支援團隊處理客戶送出的db2性能問題，利用産品功能幫助客戶快速解決性能故障
目前緻力于開發ibm data server manager以及optim query(workload) tuner相關功能和部件，包括基于查詢和應用的索引顧問，以及産品自身的性能優化等
曾在developerworks中文和英文網站發表過資料庫優化相關文章
演講實錄
本人在多年的工作中接觸過很多db2的使用者，不管是初學者或者經驗豐富的，他們或多或少對db2的行為産生過疑問，尤其是在sql語句的執行性能方面。他們經常問的問題有：明明這條sql很簡單，隻需要傳回很少的記錄，為什麼db2花了這麼長時間？為什麼這個表上明明有主鍵索引，但是db2好像根本沒使用它？為什麼這個sql有時候快、有時候卻慢得無法忍受，而差別隻是謂詞上的輸入參數不同？……要解答這些問題，我們首先需要了解db2内部是如何處理sql語句，而處理sql語句的關鍵部件就是優化器。
優化器是db2的心髒和靈魂。從功能上講，它等同于一個專家系統，是一個标準規則集合。不管資料實際上是如何存儲和操作的，db2和sql都可以通路該資料。從實體存儲特征中分離出資料通路準則，稱之為“實體資料獨立性”。db2的優化器就是實作該實體資料獨立性的元件。例如，在删除一些索引的情況下，db2仍然可以通過表掃描的方式通路到資料，盡管可能不是那麼高效。又如可以在表中新增加一列，db2仍可操作這些資料而無需更改程式的代碼。所有這些成為可能都是因為對資料的實體通路路徑并不是由程式員在程式中寫死，而是由db2自動生成。我們将資料的通路路徑叫做通路計劃(access plan)，它定義了按什麼方式通路表，使用哪些索引，以及用何種連接配接(join)方法來關聯表或中間結果，最終擷取到期待的結果。
db2優化器
運作時的優化器需要根據許多資訊，并包含着非常複雜的計算過程。若要使優化器的工作方式更加直覺化些，可以将優化器想象成如下包含4個步驟的過程：
解析接收到的sql語句，從文法和語義層面進行校驗，確定正确的sql輸入。
分析目前的環境資訊，生成最優的執行計劃，其中可能也包含對原始sql的改寫。
建立計算機可讀指令來執行優化的sql。
存儲它們以便後續的執行。
對于步驟2，db2優化器是根據什麼來判斷sql的最優執行計劃的呢？實際上，優化器是一個基于成本的優化器(cbo)，這意味着優化器将始終嘗試為查詢制定最少總體成本的執行計劃。要實作這個目标，db2優化器會應用查詢成本公式，該公式對每條可能的執行計劃的4個因素進行評估和權衡：cpu 成本、i/o 成本、db2 系統目錄中的統計資訊和實際的sql 語句。cpu的成本還可以進行細分，包括從緩存中找到資料頁的時間（page cost）和讀取一個資料記錄的同時使用謂詞的時間（scan cost）。我們下面先看一個簡單的例子來了解成本估算：
這個例子很簡單，就是從表t1裡面找到滿足條件的c3的值，條件有兩個：一個是c1 = 100，另一個是c1 > 10。t1這個表上有50個列，分别是c1、c2…c50。t1一共有100,000條記錄，占用資料頁5000。用db2的統計資料表示就是：t1的card=100,000， t1的npages=5000。對于這個查詢中引用到的列一共有三個：c1和c2在where字句裡面的謂詞中出現；c3在select子句裡出現。列出現的位置不同，對統計資料的需求也不同。通常情況下，優化器隻需要謂詞中出現的列的統計資料。在這個例子中，列c1一共有100個不同的值，列c2有1000個不同的值，其中次大值是1000，次小值是1。用統計資料來表示就是：c1的colcard=100，c2的colcard=1000，c2的high2key=1000，c2的low2key=1。把這條查詢格式化一下并且把統計資料都附加上去，如下：
現在按照db2優化器的方法來進行成本估計。首先，優化器會在估計成本前産生多個通路計劃。對于這條查詢來說，基于使用者的表和索引的定義，優化器可以選擇全表掃描和索引掃描兩種方法。在全表掃描的情況下，db2需要通路這個表的所有資料頁，假設通路每個資料頁需要花i/o時間1ms，那麼db2總共花了5000ms把表的資料讀取到緩存中。
接下來計算cpu時間。一共是5000個資料頁在緩存中，當db2做全表掃描時，cpu需要花時間從緩存中找到每一個資料頁，假設每找一個花0.1ms，那麼一共花了500ms的cpu時間（page cost = 500ms）。cpu的另外一部分開銷是讀取一個資料記錄的同時使用謂詞的時間。不同類型的謂詞需要的計算時間不一樣，在這個例子裡面，假設每個謂詞的使用時間都是0.01ms，那麼一共100,000記錄所需要的時間就是100,000 x 2 x 0.01，一共2000ms （scan cost = 2000ms）。進而最終估計出這條查詢在使用全表查詢的情況下的成本：
下面再來看在索引掃描的情況下估計的成本有何不同。首先，假設表t1有一個索引建在列c1上，索引名ix1。ix1上收集了一些基本的統計資訊，例如索引的頁節點占用的資料頁（nleaf）、索引的高度（nlevel）和索引鍵的不同值數（firstkeycard和fullkeycard）。如下：
那麼當db2在進行索引掃描時，主要花的時間分成兩個部分：一是掃描索引花的時間；二是通過索引裡面存的rid（指向資料記錄的指針）讀取表記錄的時間。索引本身存儲是b+樹的結構，db2在掃描索引時是從根節點開始比較，然後一步步定位滿足條件的值在哪個子樹下面，最終定位到頁節點。在這個過程中，db2要通路的非葉節點數量等同于索引的高度nlevel。之後db2通路頁節點的數量和滿足條件的記錄數有關，并非所有的葉節點都需要通路，那麼究竟有多少的葉節點會被讀取呢？這裡就必須要用到計算謂詞選擇性的統計資料firstkeycard，等同于c1的colcard。在這個例子中，謂詞c1 = 100的選擇性是指表t1中滿足這個謂詞條件的記錄數除以t1的總記錄數的比例。db2在通常條件下會認為表的資料分布是均勻的，那麼對于列c1來說，如果c1有colcard個不同的值，那麼每種值的比例就是1/colcard (filter factor, 簡稱ff)，在這個例子中就是1/100，也就是說有1/100的表資料滿足謂詞條件。同樣，在索引的葉節點裡面存的rid也隻需要讀取1/100，也就是1/100 x nleaf ＝ 0.5 ～ 一個索引頁。好了，這樣掃描索引的io時間确定了：
在進行索引掃描時，每通路一個非頁節點，都需要和裡面存儲的鍵值進行比較以确定滿足條件的子樹，是以也花掉一些cpu時間，估算如下：
是以掃描索引一共需要的時間是：io cost + cpu cost = 3.22ms
第二步是通過rid讀取表記錄的時間。需要讀取的表記錄數是card * ff = 100,000 * 1/100 = 1000條記錄，如果按照平均分布來看就是npagaes * ff = 5000 * 1/100 = 50個資料頁，那麼io時間估算如下：
在讀取表記錄的同時需要使用謂詞c2 > 8，需要cpu時間，估算如下：
最後，在讀取表資料階段，db2估算的總時間為：
将兩個階段的時間累加起來就是通過索引ix1來執行sql的總時間：3.22ms + 65ms = 68.22ms。這個時間跟全表掃描（7500ms）相比已經少了很多。是以通常情況下資料庫管理者往往都給表建立一些索引來提高效率。
通過上面這個簡單的例子，我們可以看出db2的成本模型中，會對每種操作進行成本的計算，例如全表掃描、索引掃描、表連接配接、資料排序等等。當然這些計算公式并不是和上述例子中那樣簡單，db2還會考慮更多複雜的因素。但對于很多sql調優的案例，我們知道一個大概的成本估算公式就能進行分析了。筆者在日常工作中也積累了一些常用的技巧，在這裡分享給大家。
選擇索引掃描好還是全表掃描好？這個問題其實就是上面例子的擴充。我們當時在計算中使用了表和索引的統計資料，其中很關鍵的統計資料就是表的資料頁、索引的資料頁和謂詞的選擇性。當我們看到表的資料頁很大、索引的資料頁較小，并且謂詞的選擇性很好的時候，一般索引掃描代價更小。而相反，如果表的資料頁小、索引的資料頁接近、謂詞選擇性也不好的時候，db2更傾向使用全表掃描。更複雜的情況下，我們還需要考慮資料預取和随機io等，本篇将不再細述。
多索引下哪個更好？這個問題很常見。對于特定問題來說，我們會比較每個索引上的鍵所對應的謂詞的選擇性，有時候還要看謂詞的類型和在索引上的使用順序。一般的原則是，當某個索引的第一個鍵和謂詞中某個運算符為“＝”的謂詞恰好對應，并且謂詞的選擇性很強，甚至接近唯一，那麼無疑這個索引肯定是最好的。例如上述例子中，如果c1的colcard接近t1的記錄數，那麼用c1作為第一個鍵的索引将是最優的。db2在選擇索引的時候還會考慮穩定性，例如同樣選擇性的謂詞，一個運算符是“＝”，另一個是“>”，相對來說前者更加穩定，當輸入參數變化時，性能相對更加一緻。當然，有些情況，例如資料傾斜，需要特殊考慮。
什麼情況下需要避免排序？筆者曾經遇到過好多的排序情況，因為db2裡面需要排序的地方太多了，例如sql裡面加上的dinstinct，order by，group by關鍵字，還有表連接配接過程中需要的，例如合并連接配接（merge scan join）等。并不是所有情況下排序都不好，很簡單的例子就是通過一個主鍵索引通路資料，但是需要排序的列不在索引鍵内。這種情況即使把這個列加入到索引鍵内，作用也是非常小的，因為db2通過主鍵掃描後，最後傳回的記錄數最多隻能是一條，根本不需要排序。而真正需要避免排序的情況是要處理的集非常大、代價高的情況。筆者之前遇到的一個例子就是兩個非常大的表進行連接配接，連接配接完了需要做distinct，形成了一個巨大的排序。最終，筆者在檢查了連接配接條件後，把distinct下放到每個表上面，然後建立合适的索引，進而最終避免了排序。
嵌套循環連接配接(nested loop join)有什麼地方需要重點注意？嵌套循環連接配接無疑是db2中最常見的表連接配接方式。它的原理也很簡單，就是先掃描一張表，獲得所有滿足條件的記錄，然後對每一條記錄再根據連接配接條件通路第二張表，這樣經過對第二張表多次掃描後，擷取第二張表上滿足條件的記錄，最後合并在一起。其中最關鍵的地方是第一張表滿足條件的記錄數和第二張表的通路方式。一般來說，如果第一張表滿足條件的記錄數很大，并且第二張表的通路方式不好，例如是全表查詢，那麼必須得重點注意。但我們在通路計劃圖上卻很少見到這種情況，因為db2已經通過統計資料進行了計算，發現這種情況的代價很大，進而選擇了其他的表連接配接方法。往往我們最常見的情況是第一張表滿足條件的記錄數很小，同時第二張表的通路方式是全表掃描。這時我們仍然要重點注意，因為有些情況下db2可能錯誤的估計了第一張表滿足條件的記錄數，最常見的原因有統計資料缺失、謂詞類型難以估計選擇性等。是以這種情況下需要把查詢拆分，在實際環境中運作，看看第一張表實際滿足條件的記錄數到底是多少，如果和db2估算的有很大差異，必須要查明原因再想辦法解決。
到此，讓我們再回頭看文章開頭的那些問題，是不是都能迎刃而解了呢？
q & a
q1：問個nlj的問題，如果隻看qualified row的話，到底是大表放前面好，還是小表？
a：真實的情況是都有可能，因為db2是cost model，是以它必須看後面的表是否有好的通路方式。
q2：請問db2有無第三方工具軟體可以直接跟蹤到最耗資源的sql語句，比如oracle有類似的toad sql？
a：ibm的data server manger可以，有各種監控的配置和告警，現在有免費版可以用。
q3：案例各種操作的代價即假設的0.01ms 0.1ms這些在db2内部是否有參數控制？這些預設的代價如果不合理會影響成本的估算？
a：這是個好問題，我的了解是有預設值，而且每個版本還會變化，否則怎麼能跟上硬體性能的提升呢？例子裡面的這些時間隻是為了友善了解寫的，确實和硬體性能有關。有群友剛才說了用mips來換算（cpu cost的計算的時候應該計算mips，計算用了多少條計算機指令，這樣就不依賴于具體cpu了）。在真實的調優過程中，基本上不會列準确的公式計算，都是估算。
q4：請問，如果sql中的表，資料經常發生變化，對執行計劃有什麼影響？怎樣做才能獲得最優的執行計劃？可能是全表load覆寫，也可能是增量增長，比如使用者的話單表，gsm、gprs話單，每月的資料量都在增長，日表每天更新，月表每月更新。
a：可以在表上加volatile參數，表示這個表的資料經常有大起大落的變化。表上加上volatile後，db2會優先選擇索引掃描，大多數使用者都有定期的runstats，reorg來維護，這樣來保證統計資訊的準确性。
q5：表連接配接比較多的時候怎麼選擇一個合理的連接配接順序？
a：這個問題很好。可以從兩個方面來看：一個是從現有的連接配接順序。既然這個順序是db2選的，那麼肯定有它的理由，前提是它的估算是準的，然而很多情況下估算并不準，就會導緻一些問題，那麼我們就需要找的可能産生問題的地方。第二個方面是從查詢本身來看，在表和表的連接配接關系已經清楚的情況下，哪個表可以作為第一個表，哪個表是第二個，如果需要支援這樣的連接配接順序，是否需要建立新的索引或者修改已有的索引，需要一步步來推導。
q6：優化器在處理一個字段上隻有2個值的索引機制是怎麼樣的？
a：一般不建議在colcard少的字段上建立索引，效率不高。除非是資料有傾斜，而大部分查詢都是傳回少量記錄這種。例如sex字段，如果1%是male，而查詢謂詞中都是sex='m'這種。
q7：db2建索引有什麼最佳實踐？
a：建索引應少而精，應用全局考慮。db2有專用的explain mode可以在compile的同時建議合适的索引。某些情況下，針對于單條查詢，有可能會推薦數量較多，有些是相似的。此時索引優化應該從全局考慮。
q8：想問下關于dpf資料庫：在什麼情況下，應該使用聯合分布鍵？以及聯合分布鍵的hash值，是基于多個字段生成的嗎？
a：dpf的distribution key的調優用的不多，我的了解是根據應用來選擇，如果應用的很多基于多個column的謂詞，形成了一種模式，那麼應該作為聯合分布鍵。
q9：當多表關聯或臨時表關聯，謂詞無法帶入，無法判斷柱狀圖的時候有什麼好辦法？
a：對于連接配接謂詞的選擇性，db2的公示也不是很準。很多情況下需要借助于statistics view這個東西來幫忙
<b>本文來自雲栖社群合作夥伴"dbaplus"，原文釋出時間：2015-11-19</b>