1、 SQL數(shù)據(jù)庫調(diào)優(yōu)技巧指南 對于SQL調(diào)優(yōu)，要調(diào)就調(diào)到極致，因為在一個并發(fā)量很大的業(yè)務系統(tǒng)中，對于頻繁執(zhí)行的單條SQL性能的提升，可能對整體數(shù)據(jù)庫的性能提升都有很大的意義。但是遇到order by字段后面的字段，特別是當這個字段不在過濾條件中時，是加到索引里面呢，還是不加到索引里面呢，加進去會不會沒有起到提升性能的作用，反而讓索引變得更加復雜，給系統(tǒng)帶來不必要的額外負擔？但是如果直接忽略掉這個問題，很可能這個提升系統(tǒng)性能的機會就被錯過了。這里就和大家探討一下，面對order by字段后面的條件，特別是這個條件不在過濾條件中時，到底要不要加入索引中，對于SQL調(diào)優(yōu)這筆賬，索引中加入order b

2、y字段，是賺了還是賠了? Part 1先來一個小實驗，熱一下身。通過多次復制dba_objects中的數(shù)據(jù)，生成測試表T1，大約1000萬行數(shù)據(jù)。做一個簡單的查詢，查詢T1表中object_id最小的10行數(shù)據(jù)，select * from (select * from T1 order by object_id) where rownum=10，耗時Elapsed: 00:00:35.92，執(zhí)行計劃如下：執(zhí)行計劃中可以看到，先作了一個全表掃，取到了結果集11M行（可以粗略理解為11百萬行，這個測試表T1行數(shù)為11943842）。然后作了一個排序，截取最小的10條記錄，最后返回結果。下面我們在o

3、bject_id字段上建一個索引I_T1_ORDER3，作一個比較。耗時從剛才的35秒，直接降到了 Elapsed: 00:00:00.01，提升性能的效果非常明顯。索引和執(zhí)行計劃如下：從執(zhí)行計劃中可以看到，優(yōu)化器直接從索引中找到了最小的10條記錄，然后回表取得結果集返回。相比上一個執(zhí)行計劃，省去了全表掃描，省去了排序，所以執(zhí)行時間和系統(tǒng)資源消耗都大大減少。在這里作一個簡單的分析，首先索引和數(shù)據(jù)不同，是按照有序的排列存儲的，當結果集要求按照順序取得一部分數(shù)據(jù)時，索引的功效會體現(xiàn)的非常明顯，本次查詢就是要取得object_id最小的10條記錄。其次，建立索引系統(tǒng)只需要消耗一次資源完成排序過程，而

4、如果沒有索引，執(zhí)行不同的語句可能每次都要經(jīng)歷排序的過程，會消耗更多的系統(tǒng)資源。從這個實驗看，在order by字段建索引是非常劃算的，而且order by字段并不一定非要加入到where條件中也可以生效。這里要和大家分享一個自己踩到的“坑”：就是起初在建了索引I_T1_ORDER3后，這條查詢語句的執(zhí)行計劃并不選擇索引，增加了hint提示也不選擇。明顯使用索引會好，為什么優(yōu)化器偏偏不選擇索引呢，而且是加了hint也不走。在修改object_id列為非空屬性（NOT NULL）后，優(yōu)化器才選擇了這個索引。這里是這么理解的：如果這一列存在NULL值，NULL值是沒有大小這一說法的，而且不會被保存在

5、索引中。如果優(yōu)化器無法確定該列沒有NULL值，為了保證結果集的準確性，寧愿選擇更慢的全表掃描，也不會選擇走可能存在NULL的索引，即使用戶指定了hint也不會選擇（這里的幾句話有點繞，大家耐心讀一下）。從這一點來看，開發(fā)Oracle優(yōu)化器的小伙伴是非?？孔V的。 Part 2上面的實驗中order by字段加入索引的作用非常明顯?？墒窃趯嶋H生產(chǎn)環(huán)境中，能有這么簡單的SQL來給DBA調(diào)優(yōu)的機會并不多，實際生產(chǎn)中的SQL往往要更復雜一些。下面我們就把測試變得復雜一點，復制測試表T1，生成測試表T2，查詢object_type類似INDEX中object_id最小的10條記錄，select * fro

6、m (select * from T2 where object_type like %INDEX% order by object_id) where rownum avm 當前系統(tǒng)中已經(jīng)激活的虛擬內(nèi)存頁的數(shù)量（該數(shù)值不包含文件系統(tǒng)緩存）vmstat - fre 系統(tǒng)中平均空閑頁的數(shù)量（不能完全代表系統(tǒng)中可用的空閑內(nèi)存：文件系統(tǒng)緩存駐留內(nèi)存，并不會返還給空閑列表，除非被虛擬內(nèi)存管理器盜?。﹕vmon - clnt與in use交叉項 代表有多少內(nèi)存被文件系統(tǒng)使用（加上free項，可以初步認為是該系統(tǒng)中可以被應用程序所使用的內(nèi)存）第二步 數(shù)據(jù)庫級別性能1. db2grep -dump | mo

7、re 查看服務器安裝了幾個DB2版本2. ps -elf | grep db2inst1 查看數(shù)據(jù)庫進程的CPU計數(shù)器3. db2 get dbm cfg | grep -i dft_mon 確認快照打開4. 實例級快照，了解當前實例有多少應用程序在執(zhí)行db2 get snapshot for database manager - Remote connections & Local connections5. 數(shù)據(jù)庫級快照連接數(shù)信息：applications connected currently，appls executing in db manager currently鎖信息：鎖總數(shù)，

8、鎖等待數(shù)量，鎖等待總時間，當前數(shù)據(jù)庫鎖列表占用內(nèi)存，死鎖次數(shù)，鎖升級次數(shù)，鎖超時次數(shù)排序信息：-排序是CPU殺手，過多的排序會造成CPU的極大消耗；-排序溢出是說，如果排序堆無法容納排序數(shù)據(jù)，就會被溢出到臨時空間；-排序是一種狀態(tài)，根源在SQL語句；數(shù)據(jù)索引I/O信息：-邏輯讀 DB2向緩沖池請求的次數(shù) 邏輯讀越多，需要的物理I/O就越少-物理讀 如果請求的數(shù)據(jù)頁不在緩沖池，需要從磁盤中讀取數(shù)據(jù)頁的次數(shù)吞吐量或事務信息：提交/回滾事務數(shù)，執(zhí)行動態(tài)和靜態(tài)語句次數(shù)，增刪改查次數(shù)( rows read / rows selected ) 是一個非常重要的性能指標，它表示為了檢索一行數(shù)據(jù)需要讀取多少行

9、，該值越大，表示代價越高，需要的I/O越多，可調(diào)優(yōu)的余地越大事務日志信息：日志I/O在很大程度上會影響數(shù)據(jù)庫整體的性能6. 應用程序快照在數(shù)據(jù)庫快照中發(fā)現(xiàn)存在大量的邏輯讀，通過應用程序快照可以細化到某條特定的語句7. 表空間快照在數(shù)據(jù)庫快照中發(fā)現(xiàn)存在大量的邏輯讀，通過表空間快照可以輕松地定位哪個表空間被頻繁使用8. 表快照如果發(fā)現(xiàn)一個表的頁數(shù)很少，但是讀的行數(shù)非常多，那么可以合理地猜測該表在某些查詢語句中可能處于NLJOIN的內(nèi)部子節(jié)點9. 動態(tài)SQL快照：SQL執(zhí)行次數(shù)，總共讀的行數(shù)，消耗的CPU，邏輯物理讀數(shù)量，排序數(shù)量等第三步 內(nèi)存使用監(jiān)控1. db2pd -osinfo系統(tǒng)內(nèi)存使用情況2. db2pd -dbptnmem整個實例的內(nèi)存使用情況3. db2pd -memsets內(nèi)存段使用情況在實例中會有多個不同的內(nèi)存段，每一個內(nèi)存段