1、程序員的數(shù)據(jù)庫性能優(yōu)化 面向程序員的數(shù)據(jù)庫訪問性能優(yōu)化法則 特別說明：1、  本文只是面對數(shù)據(jù)庫應用開發(fā)的程序員，不適合專業(yè)DBA，DBA在數(shù)據(jù)庫性能優(yōu)化方面需要了解更多的知識；2、  本文許多示例及概念是基于Oracle數(shù)據(jù)庫描述，對于其它關系型數(shù)據(jù)庫也可以參考，但許多觀點不適合于KV數(shù)據(jù)庫或內(nèi)存數(shù)據(jù)庫或者是基于SSD技術的數(shù)據(jù)庫；3、  本文未深入數(shù)據(jù)庫優(yōu)化中最核心的執(zhí)行計劃分析技術。 讀者對像：開發(fā)人員：如果你是做數(shù)據(jù)庫開發(fā)，那本文的內(nèi)容非常適合，因為本文是從程序員的角度來談數(shù)據(jù)庫性能優(yōu)化。架構(gòu)師：如果你已經(jīng)是數(shù)據(jù)庫應用的架構(gòu)師，那本文的

2、知識你應該清楚90%，否則你可能是一個喜歡折騰的架構(gòu)師。DBA（數(shù)據(jù)庫管理員）：大型數(shù)據(jù)庫優(yōu)化的知識非常復雜，本文只是從程序員的角度來談性能優(yōu)化，DBA除了需要了解這些知識外，還需要深入數(shù)據(jù)庫的內(nèi)部體系架構(gòu)來解決問題。 引言在網(wǎng)上有很多文章介紹數(shù)據(jù)庫優(yōu)化知識，但是大部份文章只是對某個一個方面進行說明，而對于我們程序員來說這種介紹并不能很好的掌握優(yōu)化知識，因為很多介紹只是對一些特定的場景優(yōu)化的，所以反而有時會產(chǎn)生誤導或讓程序員感覺不明白其中的奧妙而對數(shù)據(jù)庫優(yōu)化感覺很神秘。很多程序員總是問如何學習數(shù)據(jù)庫優(yōu)化，有沒有好的教材之類的問題。在書店也看到了許多數(shù)據(jù)庫優(yōu)化的專業(yè)書籍，但是感覺更多是

3、面向DBA或者是PL/SQL開發(fā)方面的知識，個人感覺不太適合普通程序員。而要想做到數(shù)據(jù)庫優(yōu)化的高手，不是花幾周，幾個月就能達到的，這并不是因為數(shù)據(jù)庫優(yōu)化有多高深，而是因為要做好優(yōu)化一方面需要有非常好的技術功底，對操作系統(tǒng)、存儲硬件網(wǎng)絡、數(shù)據(jù)庫原理等方面有比較扎實的基礎知識，另一方面是需要花大量時間對特定的數(shù)據(jù)庫進行實踐測試與總結(jié)。作為一個程序員，我們也許不清楚線上正式的服務器硬件配置，我們不可能像DBA那樣專業(yè)的對數(shù)據(jù)庫進行各種實踐測試與總結(jié)，但我們都應該非常了解我們SQL的業(yè)務邏輯，我們清楚SQL中訪問表及字段的數(shù)據(jù)情況，我們其實只關心我們的SQL是否能盡快返回結(jié)果。那程序員如何利用已知的知

4、識進行數(shù)據(jù)庫優(yōu)化？如何能快速定位SQL性能問題并找到正確的優(yōu)化方向？面對這些問題，筆者總結(jié)了一些面向程序員的基本優(yōu)化法則，本文將結(jié)合實例來坦述數(shù)據(jù)庫開發(fā)的優(yōu)化知識。一、數(shù)據(jù)庫訪問優(yōu)化法則簡介要正確的優(yōu)化SQL，我們需要快速定位能性的瓶頸點，也就是說快速找到我們SQL主要的開銷在哪里？而大多數(shù)情況性能最慢的設備會是瓶頸點，如下載時網(wǎng)絡速度可能會是瓶頸點，本地復制文件時硬盤可能會是瓶頸點，為什么這些一般的工作我們能快速確認瓶頸點呢，因為我們對這些慢速設備的性能數(shù)據(jù)有一些基本的認識，如網(wǎng)絡帶寬是2Mbps，硬盤是每分鐘7200轉(zhuǎn)等等。因此，為了快速找到SQL的性能瓶頸點，我們也需要了解我們計算機系統(tǒng)

5、的硬件基本性能指標，下圖展示的當前主流計算機性能指標數(shù)據(jù)。 從圖上可以看到基本上每種設備都有兩個指標：延時（響應時間）：表示硬件的突發(fā)處理能力；帶寬（吞吐量）：代表硬件持續(xù)處理能力。 從上圖可以看出，計算機系統(tǒng)硬件性能從高到代依次為：CPUCache(L1-L2-L3)內(nèi)存SSD硬盤網(wǎng)絡硬盤由于SSD硬盤還處于快速發(fā)展階段，所以本文的內(nèi)容不涉及SSD相關應用系統(tǒng)。根據(jù)數(shù)據(jù)庫知識，我們可以列出每種硬件主要的工作內(nèi)容：CPU及內(nèi)存：緩存數(shù)據(jù)訪問、比較、排序、事務檢測、SQL解析、函數(shù)或邏輯運算；網(wǎng)絡：結(jié)果數(shù)據(jù)傳輸、SQL請求、遠程數(shù)據(jù)庫訪問（dblink）；硬盤：數(shù)據(jù)訪問、數(shù)據(jù)

6、寫入、日志記錄、大數(shù)據(jù)量排序、大表連接。 根據(jù)當前計算機硬件的基本性能指標及其在數(shù)據(jù)庫中主要操作內(nèi)容，可以整理出如下圖所示的性能基本優(yōu)化法則： 這個優(yōu)化法則歸納為5個層次：1、  減少數(shù)據(jù)訪問（減少磁盤訪問）2、  返回更少數(shù)據(jù)（減少網(wǎng)絡傳輸或磁盤訪問）3、  減少交互次數(shù)（減少網(wǎng)絡傳輸）4、  減少服務器CPU開銷（減少CPU及內(nèi)存開銷）5、  利用更多資源（增加資源） 由于每一層優(yōu)化法則都是解決其對應硬件的性能問題，所以帶來的性能提升比例也不一樣。傳統(tǒng)數(shù)據(jù)庫系統(tǒng)設計是也是盡可能對低速設備提供優(yōu)化方法，因此針對低

7、速設備問題的可優(yōu)化手段也更多，優(yōu)化成本也更低。我們?nèi)魏我粋€SQL的性能優(yōu)化都應該按這個規(guī)則由上到下來診斷問題并提出解決方案，而不應該首先想到的是增加資源解決問題。以下是每個優(yōu)化法則層級對應優(yōu)化效果及成本經(jīng)驗參考： 優(yōu)化法則 性能提升效果 優(yōu)化成本 減少數(shù)據(jù)訪問 11000 低 返回更少數(shù)據(jù) 1100 低 減少交互次數(shù) 120 低 減少服務器CPU開銷 15 低 利用更多資源 10 高  接下來，我

8、們針對5種優(yōu)化法則列舉常用的優(yōu)化手段并結(jié)合實例分析。 二、Oracle數(shù)據(jù)庫兩個基本概念數(shù)據(jù)塊(Block)數(shù)據(jù)塊是數(shù)據(jù)庫中數(shù)據(jù)在磁盤中存儲的最小單位，也是一次IO訪問的最小單位，一個數(shù)據(jù)塊通常可以存儲多條記錄，數(shù)據(jù)塊大小是DBA在創(chuàng)建數(shù)據(jù)庫或表空間時指定，可指定為2K、4K、8K、16K或32K字節(jié)。下圖是一個Oracle數(shù)據(jù)庫典型的物理結(jié)構(gòu)，一個數(shù)據(jù)庫可以包括多個數(shù)據(jù)文件，一個數(shù)據(jù)文件內(nèi)又包含多個數(shù)據(jù)塊；  ROWIDROWID是每條記錄在數(shù)據(jù)庫中的唯一標識，通過ROWID可以直接定位記錄到對應的文件號及數(shù)據(jù)塊位置。ROWID內(nèi)容包括文件號、對像號、數(shù)據(jù)塊號、

9、記錄槽號，如下圖所示： 三、數(shù)據(jù)庫訪問優(yōu)化法則詳解1、減少數(shù)據(jù)訪問1.1、創(chuàng)建并使用正確的索引數(shù)據(jù)庫索引的原理非常簡單，但在復雜的表中真正能正確使用索引的人很少，即使是專業(yè)的DBA也不一定能完全做到最優(yōu)。索引會大大增加表記錄的DML(INSERT,UPDATE,DELETE)開銷，正確的索引可以讓性能提升100，1000倍以上，不合理的索引也可能會讓性能下降100倍，因此在一個表中創(chuàng)建什么樣的索引需要平衡各種業(yè)務需求。索引常見問題：索引有哪些種類？常見的索引有B-TREE索引、位圖索引、全文索引，位圖索引一般用于數(shù)據(jù)倉庫應用，全文索引由于使用較少，這里不深入介紹。B-TREE索引包括

10、很多擴展類型，如組合索引、反向索引、函數(shù)索引等等，以下是B-TREE索引的簡單介紹：B-TREE索引也稱為平衡樹索引(Balance Tree)，它是一種按字段排好序的樹形目錄結(jié)構(gòu)，主要用于提升查詢性能和唯一約束支持。B-TREE索引的內(nèi)容包括根節(jié)點、分支節(jié)點、葉子節(jié)點。葉子節(jié)點內(nèi)容：索引字段內(nèi)容+表記錄ROWID根節(jié)點，分支節(jié)點內(nèi)容：當一個數(shù)據(jù)塊中不能放下所有索引字段數(shù)據(jù)時，就會形成樹形的根節(jié)點或分支節(jié)點，根節(jié)點與分支節(jié)點保存了索引樹的順序及各層級間的引用關系。         一個普通的BTREE索引結(jié)構(gòu)示意圖

11、如下所示：  如果我們把一個表的內(nèi)容認為是一本字典，那索引就相當于字典的目錄，如下圖所示：    圖中是一個字典按部首+筆劃數(shù)的目錄，相當于給字典建了一個按部首+筆劃的組合索引。一個表中可以建多個索引，就如一本字典可以建多個目錄一樣（按拼音、筆劃、部首等等）。一個索引也可以由多個字段組成，稱為組合索引，如上圖就是一個按部首+筆劃的組合目錄。SQL什么條件會使用索引？當字段上建有索引時，通常以下情況會使用索引：INDEX_COLUMN = ?INDEX_COLUMN > ?INDEX_COLUMN >= ?INDEX_COL

12、UMN < ?INDEX_COLUMN <= ?INDEX_COLUMN between ? and ?INDEX_COLUMN in (?,?,.,?)INDEX_COLUMN like ?|'%'（后導模糊查詢）T1. INDEX_COLUMN=T2. COLUMN1（兩個表通過索引字段關聯(lián)） SQL什么條件不會使用索引？ 查詢條件 不能使用索引原因 INDEX_COLUMN <> ?INDEX_COLUMN not in (?,?,.,?) 不等于操作不能使用索引 function(IND

13、EX_COLUMN) = ?INDEX_COLUMN + 1 = ?INDEX_COLUMN | 'a' = ? 經(jīng)過普通運算或函數(shù)運算后的索引字段不能使用索引 INDEX_COLUMN like '%'|?INDEX_COLUMN like '%'|?|'%' 含前導模糊查詢的Like語法不能使用索引 INDEX_COLUMN is null B-TREE索引里不保存字段為NULL值記錄，因此IS NULL不能使用索引 NUMBER_INDEX_COLUMN='

14、12345'CHAR_INDEX_COLUMN=12345 Oracle在做數(shù)值比較時需要將兩邊的數(shù)據(jù)轉(zhuǎn)換成同一種數(shù)據(jù)類型，如果兩邊數(shù)據(jù)類型不同時會對字段值隱式轉(zhuǎn)換，相當于加了一層函數(shù)處理，所以不能使用索引。 a.INDEX_COLUMN=a.COLUMN_1 給索引查詢的值應是已知數(shù)據(jù)，不能是未知字段值。 注：經(jīng)過函數(shù)運算字段的字段要使用可以使用函數(shù)索引，這種需求建議與DBA溝通。有時候我們會使用多個字段的組合索引，如果查詢條件中第一個字段不能使用索引，那整個查詢也不能使用索引如：我們company表建了一個id+name的組合索引，以下SQL

15、是不能使用索引的Select * from company where name=?Oracle9i后引入了一種index skip scan的索引方式來解決類似的問題，但是通過index skip scan提高性能的條件比較特殊，使用不好反而性能會更差。  我們一般在什么字段上建索引？這是一個非常復雜的話題，需要對業(yè)務及數(shù)據(jù)充分分析后再能得出結(jié)果。主鍵及外鍵通常都要有索引，其它需要建索引的字段應滿足以下條件：1、字段出現(xiàn)在查詢條件中，并且查詢條件可以使用索引；2、語句執(zhí)行頻率高，一天會有幾千次以上；3、通過字段條件可篩選的記錄集很小，那數(shù)據(jù)篩選比例是多少才適合？這個沒有固

16、定值，需要根據(jù)表數(shù)據(jù)量來評估，以下是經(jīng)驗公式，可用于快速評估：小表(記錄數(shù)小于10000行的表)：篩選比例<10%；大表：(篩選返回記錄數(shù))<(表總記錄數(shù)*單條記錄長度)/10000/16      單條記錄長度字段平均內(nèi)容長度之和+字段數(shù)*2 以下是一些字段是否需要建B-TREE索引的經(jīng)驗分類：   字段類型 常見字段名 需要建索引的字段 主鍵 ID,PK 外鍵 PRODUCT_ID,COMPANY_ID,MEMBER_ID,ORD

17、ER_ID,TRADE_ID,PAY_ID 有對像或身份標識意義字段 HASH_CODE,USERNAME,IDCARD_NO,EMAIL,TEL_NO,IM_NO 索引慎用字段,需要進行數(shù)據(jù)分布及使用場景詳細評估 日期 GMT_CREATE,GMT_MODIFIED 年月 YEAR,MONTH 狀態(tài)標志 PRODUCT_STATUS,ORDER_STATUS,IS_DELETE,VIP_FLAG 類型 ORDER_TYPE,IMAGE_TYPE,GENDER,CURRENCY_TYP

18、E 區(qū)域 COUNTRY,PROVINCE,CITY 操作人員 CREATOR,AUDITOR 數(shù)值 LEVEL,AMOUNT,SCORE 長字符 ADDRESS,COMPANY_NAME,SUMMARY,SUBJECT 不適合建索引的字段 描述備注 DESCRIPTION,REMARK,MEMO,DETAIL 大字段 FILE_CONTENT,EMAIL_CONTENT  如何知道SQL是否使用了正確的索引？簡單SQL可以根據(jù)索引使用語法規(guī)則判

19、斷，復雜的SQL不好辦，判斷SQL的響應時間是一種策略，但是這會受到數(shù)據(jù)量、主機負載及緩存等因素的影響，有時數(shù)據(jù)全在緩存里，可能全表訪問的時間比索引訪問時間還少。要準確知道索引是否正確使用，需要到數(shù)據(jù)庫中查看SQL真實的執(zhí)行計劃，這個話題比較復雜，詳見SQL執(zhí)行計劃專題介紹。 索引對DML(INSERT,UPDATE,DELETE)附加的開銷有多少？這個沒有固定的比例，與每個表記錄的大小及索引字段大小密切相關，以下是一個普通表測試數(shù)據(jù)，僅供參考：索引對于Insert性能降低56%索引對于Update性能降低47%索引對于Delete性能降低29%因此對于寫IO壓力比較大的系統(tǒng)，表的索

20、引需要仔細評估必要性，另外索引也會占用一定的存儲空間。 1.2、只通過索引訪問數(shù)據(jù)有些時候，我們只是訪問表中的幾個字段，并且字段內(nèi)容較少，我們可以為這幾個字段單獨建立一個組合索引，這樣就可以直接只通過訪問索引就能得到數(shù)據(jù)，一般索引占用的磁盤空間比表小很多，所以這種方式可以大大減少磁盤IO開銷。如：select id,name from company where type='2'如果這個SQL經(jīng)常使用，我們可以在type,id,name上創(chuàng)建組合索引create index my_comb_index on company(type,id,name);有了這個組合索引

21、后，SQL就可以直接通過my_comb_index索引返回數(shù)據(jù)，不需要訪問company表。還是拿字典舉例：有一個需求，需要查詢一本漢語字典中所有漢字的個數(shù)，如果我們的字典沒有目錄索引，那我們只能從字典內(nèi)容里一個一個字計數(shù)，最后返回結(jié)果。如果我們有一個拼音目錄，那就可以只訪問拼音目錄的漢字進行計數(shù)。如果一本字典有1000頁，拼音目錄有20頁，那我們的數(shù)據(jù)訪問成本相當于全表訪問的50分之一。切記，性能優(yōu)化是無止境的，當性能可以滿足需求時即可，不要過度優(yōu)化。在實際數(shù)據(jù)庫中我們不可能把每個SQL請求的字段都建在索引里，所以這種只通過索引訪問數(shù)據(jù)的方法一般只用于核心應用，也就是那種對核心表訪問量最高且

22、查詢字段數(shù)據(jù)量很少的查詢。1.3、優(yōu)化SQL執(zhí)行計劃SQL執(zhí)行計劃是關系型數(shù)據(jù)庫最核心的技術之一，它表示SQL執(zhí)行時的數(shù)據(jù)訪問算法。由于業(yè)務需求越來越復雜，表數(shù)據(jù)量也越來越大，程序員越來越懶惰，SQL也需要支持非常復雜的業(yè)務邏輯，但SQL的性能還需要提高，因此，優(yōu)秀的關系型數(shù)據(jù)庫除了需要支持復雜的SQL語法及更多函數(shù)外，還需要有一套優(yōu)秀的算法庫來提高SQL性能。目前ORACLE有SQL執(zhí)行計劃的算法約300種，而且一直在增加，所以SQL執(zhí)行計劃是一個非常復雜的課題，一個普通DBA能掌握50種就很不錯了，就算是資深DBA也不可能把每個執(zhí)行計劃的算法描述清楚。雖然有這么多種算法，但并不表示我們無法

23、優(yōu)化執(zhí)行計劃，因為我們常用的SQL執(zhí)行計劃算法也就十幾個，如果一個程序員能把這十幾個算法搞清楚，那就掌握了80%的SQL執(zhí)行計劃調(diào)優(yōu)知識。由于篇幅的原因，SQL執(zhí)行計劃需要專題介紹，在這里就不多說了。 2、返回更少的數(shù)據(jù)2.1、數(shù)據(jù)分頁處理一般數(shù)據(jù)分頁方式有：、客戶端(應用程序或瀏覽器)分頁將數(shù)據(jù)從應用服務器全部下載到本地應用程序或瀏覽器，在應用程序或瀏覽器內(nèi)部通過本地代碼進行分頁處理優(yōu)點：編碼簡單，減少客戶端與應用服務器網(wǎng)絡交互次數(shù)缺點：首次交互時間長，占用客戶端內(nèi)存適應場景：客戶端與應用服務器網(wǎng)絡延時較大，但要求后續(xù)操作流暢，如手機GPRS，超遠程訪問（跨國）等等。、應用服務器分

24、頁將數(shù)據(jù)從數(shù)據(jù)庫服務器全部下載到應用服務器，在應用服務器內(nèi)部再進行數(shù)據(jù)篩選。以下是一個應用服務器端Java程序分頁的示例：List list=executeQuery(“select * from employee order by id”);Int count= list.size();List subList= list.subList(10, 20); 優(yōu)點：編碼簡單，只需要一次SQL交互，總數(shù)據(jù)與分頁數(shù)據(jù)差不多時性能較好。缺點：總數(shù)據(jù)量較多時性能較差。適應場景：數(shù)據(jù)庫系統(tǒng)不支持分頁處理，數(shù)據(jù)量較小并且可控。 、數(shù)據(jù)庫SQL分頁采用數(shù)據(jù)庫SQL分頁需要兩次SQL完成一

25、個SQL計算總數(shù)量一個SQL返回分頁后的數(shù)據(jù)優(yōu)點：性能好缺點：編碼復雜，各種數(shù)據(jù)庫語法不同，需要兩次SQL交互。 oracle數(shù)據(jù)庫一般采用rownum來進行分頁，常用分頁語法有如下兩種： 直接通過rownum分頁：select * from (         select a.*,rownum rn from                &#

26、160;   (select * from product a where company_id=? order by status) a         where rownum<=20) where rn>10;數(shù)據(jù)訪問開銷=索引IO+索引全部記錄結(jié)果對應的表數(shù)據(jù)IO 采用rowid分頁語法優(yōu)化原理是通過純索引找出分頁記錄的ROWID，再通過ROWID回表返回數(shù)據(jù)，要求內(nèi)層查詢和排序字段全在索引里。create index myindex on product(comp

27、any_id,status); select b.* from (         select * from (                   select a.*,rownum rn from           

28、60;                 (select rowid rid,status from product a where company_id=? order by status) a                   where rownum&l

29、t;=20)          where rn>10) a, product bwhere a.rid=b.rowid;數(shù)據(jù)訪問開銷=索引IO+索引分頁結(jié)果對應的表數(shù)據(jù)IO 實例：一個公司產(chǎn)品有1000條記錄，要分頁取其中20個產(chǎn)品，假設訪問公司索引需要50個IO，2條記錄需要1個表數(shù)據(jù)IO。那么按第一種ROWNUM分頁寫法，需要550(50+1000/2)個IO，按第二種ROWID分頁寫法，只需要60個IO(50+20/2); 2.2、只返回需要的字段通過去除不必要的返回字段可以提高性

30、能，例：調(diào)整前：select * from product where company_id=?;調(diào)整后：select id,name from product where company_id=?; 優(yōu)點：1、減少數(shù)據(jù)在網(wǎng)絡上傳輸開銷2、減少服務器數(shù)據(jù)處理開銷3、減少客戶端內(nèi)存占用4、字段變更時提前發(fā)現(xiàn)問題，減少程序BUG5、如果訪問的所有字段剛好在一個索引里面，則可以使用純索引訪問提高性能。缺點：增加編碼工作量由于會增加一些編碼工作量，所以一般需求通過開發(fā)規(guī)范來要求程序員這么做，否則等項目上線后再整改工作量更大。如果你的查詢表中有大字段或內(nèi)容較多的字段，如備注信息、文件內(nèi)容等等，

31、那在查詢表時一定要注意這方面的問題，否則可能會帶來嚴重的性能問題。如果表經(jīng)常要查詢并且請求大內(nèi)容字段的概率很低，我們可以采用分表處理，將一個大表分拆成兩個一對一的關系表，將不常用的大內(nèi)容字段放在一張單獨的表中。如一張存儲上傳文件的表：T_FILE（ID,FILE_NAME,FILE_SIZE,FILE_TYPE,FILE_CONTENT）我們可以分拆成兩張一對一的關系表：T_FILE（ID,FILE_NAME,FILE_SIZE,FILE_TYPE）T_FILECONTENT（ID, FILE_CONTENT）       

32、  通過這種分拆，可以大大提少T_FILE表的單條記錄及總大小，這樣在查詢T_FILE時性能會更好，當需要查詢FILE_CONTENT字段內(nèi)容時再訪問T_FILECONTENT表。 3、減少交互次數(shù)3.1、batch DML數(shù)據(jù)庫訪問框架一般都提供了批量提交的接口，jdbc支持batch的提交處理方法，當你一次性要往一個表中插入1000萬條數(shù)據(jù)時，如果采用普通的executeUpdate處理，那么和服務器交互次數(shù)為1000萬次，按每秒鐘可以向數(shù)據(jù)庫服務器提交10000次估算，要完成所有工作需要1000秒。如果采用批量提交模式，1000條提交一次，那么和服務器交互次數(shù)為1萬次

33、，交互次數(shù)大大減少。采用batch操作一般不會減少很多數(shù)據(jù)庫服務器的物理IO，但是會大大減少客戶端與服務端的交互次數(shù)，從而減少了多次發(fā)起的網(wǎng)絡延時開銷，同時也會降低數(shù)據(jù)庫的CPU開銷。 假設要向一個普通表插入1000萬數(shù)據(jù)，每條記錄大小為1K字節(jié)，表上沒有任何索引，客戶端與數(shù)據(jù)庫服務器網(wǎng)絡是100Mbps，以下是根據(jù)現(xiàn)在一般計算機能力估算的各種batch大小性能對比值： 單位：ms No batch Batch=10 Batch=100 Batch=1000 Batch=10000 服務器事務處理時間 0

34、.1 0.1 0.1 0.1 0.1 服務器IO處理時間 0.02 0.2 2 20 200 網(wǎng)絡交互發(fā)起時間 0.1 0.1 0.1 0.1 0.1 網(wǎng)絡數(shù)據(jù)傳輸時間 0.01 0.1 1 10 100 小計 0.23 0.5 3.2 30.2 300.2 平均每條記錄處理時間 0.23 

35、;0.05 0.032 0.0302 0.03002  從上可以看出，Insert操作加大Batch可以對性能提高近8倍性能，一般根據(jù)主鍵的Update或Delete操作也可能提高2-3倍性能，但不如Insert明顯，因為Update及Delete操作可能有比較大的開銷在物理IO訪問。以上僅是理論計算值，實際情況需要根據(jù)具體環(huán)境測量。 3.2、In List很多時候我們需要按一些ID查詢數(shù)據(jù)庫記錄，我們可以采用一個ID一個請求發(fā)給數(shù)據(jù)庫，如下所示：for :var in ids do begin  select * from

36、 mytable where id=:var;end; 我們也可以做一個小的優(yōu)化， 如下所示，用ID INLIST的這種方式寫SQL：select * from mytable where id in(:id1,id2,.,idn); 通過這樣處理可以大大減少SQL請求的數(shù)量，從而提高性能。那如果有10000個ID，那是不是全部放在一條SQL里處理呢？答案肯定是否定的。首先大部份數(shù)據(jù)庫都會有SQL長度和IN里個數(shù)的限制，如ORACLE的IN里就不允許超過1000個值。另外當前數(shù)據(jù)庫一般都是采用基于成本的優(yōu)化規(guī)則，當IN數(shù)量達到一定值時有可能改變SQL執(zhí)行計劃，從索引訪問變成

37、全表訪問，這將使性能急劇變化。隨著SQL中IN的里面的值個數(shù)增加，SQL的執(zhí)行計劃會更復雜，占用的內(nèi)存將會變大，這將會增加服務器CPU及內(nèi)存成本。評估在IN里面一次放多少個值還需要考慮應用服務器本地內(nèi)存的開銷，有并發(fā)訪問時要計算本地數(shù)據(jù)使用周期內(nèi)的并發(fā)上限，否則可能會導致內(nèi)存溢出。綜合考慮，一般IN里面的值個數(shù)超過20個以后性能基本沒什么太大變化，也特別說明不要超過100，超過后可能會引起執(zhí)行計劃的不穩(wěn)定性及增加數(shù)據(jù)庫CPU及內(nèi)存成本，這個需要專業(yè)DBA評估。 3.3、設置Fetch Size當我們采用select從數(shù)據(jù)庫查詢數(shù)據(jù)時，數(shù)據(jù)默認并不是一條一條返回給客戶端的，也不是一次全

38、部返回客戶端的，而是根據(jù)客戶端fetch_size參數(shù)處理，每次只返回fetch_size條記錄，當客戶端游標遍歷到尾部時再從服務端取數(shù)據(jù)，直到最后全部傳送完成。所以如果我們要從服務端一次取大量數(shù)據(jù)時，可以加大fetch_size，這樣可以減少結(jié)果數(shù)據(jù)傳輸?shù)慕换ゴ螖?shù)及服務器數(shù)據(jù)準備時間，提高性能。 以下是jdbc測試的代碼，采用本地數(shù)據(jù)庫，表緩存在數(shù)據(jù)庫CACHE中，因此沒有網(wǎng)絡連接及磁盤IO開銷，客戶端只遍歷游標，不做任何處理，這樣更能體現(xiàn)fetch參數(shù)的影響：String vsql ="select * from t_employee"PreparedStat

39、ement pstmt = conn.prepareStatement(vsql,ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);pstmt.setFetchSize(1000);ResultSet rs = pstmt.executeQuery(vsql);int cnt = rs.getMetaData().getColumnCount();Object o;while (rs.next()     for (int i = 1; i <= cnt; i+)   

40、0;    o = rs.getObject(i);     測試示例中的employee表有100000條記錄，每條記錄平均長度135字節(jié) 以下是測試結(jié)果，對每種fetchsize測試5次再取平均值：fetchsize  elapse_time（s） 1 20.516 2 11.34 4 6.894 8 4.65 16 3.584 32 2.865 64 2.656

41、 128 2.44 256 2.765 512 3.075 1024 2.862 2048 2.722 4096 2.681 8192 2.715    Oracle jdbc fetchsize默認值為10，由上測試可以看出fetchsize對性能影響還是比較大的，但是當fetchsize大于100時就基本上沒有影響了。fetchsize并不會存在一個最優(yōu)的固定值，因為整體性能與記錄集大小及硬件平臺有關。根據(jù)測試結(jié)

42、果建議當一次性要取大量數(shù)據(jù)時這個值設置為100左右，不要小于40。注意，fetchsize不能設置太大，如果一次取出的數(shù)據(jù)大于JVM的內(nèi)存會導致內(nèi)存溢出，所以建議不要超過1000，太大了也沒什么性能提高，反而可能會增加內(nèi)存溢出的危險。注：圖中fetchsize在128以后會有一些小的波動，這并不是測試誤差，而是由于resultset填充到具體對像時間不同的原因，由于resultset已經(jīng)到本地內(nèi)存里了，所以估計是由于CPU的L1,L2 Cache命中率變化造成，由于變化不大，所以筆者也未深入分析原因。 iBatis的SqlMapping配置文件可以對每個SQL語句指定fetchsiz

43、e大小，如下所示： <select id="getAllProduct" resultMap="HashMap" fetchSize="1000">select * from employee</select> 3.4、使用存儲過程大型數(shù)據(jù)庫一般都支持存儲過程，合理的利用存儲過程也可以提高系統(tǒng)性能。如你有一個業(yè)務需要將A表的數(shù)據(jù)做一些加工然后更新到B表中，但是又不可能一條SQL完成，這時你需要如下3步操作：a：將A表數(shù)據(jù)全部取出到客戶端；b：計算出要更新的數(shù)據(jù)；c：將計算結(jié)果更新到B表。&#

44、160;如果采用存儲過程你可以將整個業(yè)務邏輯封裝在存儲過程里，然后在客戶端直接調(diào)用存儲過程處理，這樣可以減少網(wǎng)絡交互的成本。當然，存儲過程也并不是十全十美，存儲過程有以下缺點：a、不可移植性，每種數(shù)據(jù)庫的內(nèi)部編程語法都不太相同，當你的系統(tǒng)需要兼容多種數(shù)據(jù)庫時最好不要用存儲過程。b、學習成本高，DBA一般都擅長寫存儲過程，但并不是每個程序員都能寫好存儲過程，除非你的團隊有較多的開發(fā)人員熟悉寫存儲過程，否則后期系統(tǒng)維護會產(chǎn)生問題。c、業(yè)務邏輯多處存在，采用存儲過程后也就意味著你的系統(tǒng)有一些業(yè)務邏輯不是在應用程序里處理，這種架構(gòu)會增加一些系統(tǒng)維護和調(diào)試成本。d、存儲過程和常用應用程序語言不一樣，它支

45、持的函數(shù)及語法有可能不能滿足需求，有些邏輯就只能通過應用程序處理。e、如果存儲過程中有復雜運算的話，會增加一些數(shù)據(jù)庫服務端的處理成本，對于集中式數(shù)據(jù)庫可能會導致系統(tǒng)可擴展性問題。f、為了提高性能，數(shù)據(jù)庫會把存儲過程代碼編譯成中間運行代碼(類似于java的class文件)，所以更像靜態(tài)語言。當存儲過程引用的對像(表、視圖等等)結(jié)構(gòu)改變后，存儲過程需要重新編譯才能生效，在24*7高并發(fā)應用場景，一般都是在線變更結(jié)構(gòu)的，所以在變更的瞬間要同時編譯存儲過程，這可能會導致數(shù)據(jù)庫瞬間壓力上升引起故障(Oracle數(shù)據(jù)庫就存在這樣的問題)。 個人觀點：普通業(yè)務邏輯盡量不要使用存儲過程，定時性的ET

46、L任務或報表統(tǒng)計函數(shù)可以根據(jù)團隊資源情況采用存儲過程處理。 3.5、優(yōu)化業(yè)務邏輯要通過優(yōu)化業(yè)務邏輯來提高性能是比較困難的，這需要程序員對所訪問的數(shù)據(jù)及業(yè)務流程非常清楚。舉一個案例：某移動公司推出優(yōu)惠套參，活動對像為VIP會員并且2010年1，2，3月平均話費20元以上的客戶。那我們的檢測邏輯為：if avg_money>20 and vip_flag=true thenbegin  執(zhí)行套參();end; 如果我們修改業(yè)務邏輯為:if avg_money>20 thenbegin  if vip_flag=true then  be

47、gin    執(zhí)行套參();  end;end;通過這樣可以減少一些判斷vip_flag的開銷，平均話費20元以下的用戶就不需要再檢測是否VIP了。 如果程序員分析業(yè)務，VIP會員比例為1%，平均話費20元以上的用戶比例為90%，那我們改成如下：if vip_flag=true thenbegin  if avg_money>20 then  begin    執(zhí)行套參();  end;end;這樣就只有1%的VIP會員才會做檢測平均話費，最終大大減少了SQL的交互次數(shù)。

48、0;以上只是一個簡單的示例，實際的業(yè)務總是比這復雜得多，所以一般只是高級程序員更容易做出優(yōu)化的邏輯，但是我們需要有這樣一種成本優(yōu)化的意識。 3.6、使用ResultSet游標處理記錄現(xiàn)在大部分Java框架都是通過jdbc從數(shù)據(jù)庫取出數(shù)據(jù)，然后裝載到一個list里再處理，list里可能是業(yè)務Object，也可能是hashmap。由于JVM內(nèi)存一般都小于4G，所以不可能一次通過sql把大量數(shù)據(jù)裝載到list里。為了完成功能，很多程序員喜歡采用分頁的方法處理，如一次從數(shù)據(jù)庫取1000條記錄，通過多次循環(huán)搞定，保證不會引起JVM Out of memory問題。 以下是實現(xiàn)此功能的

49、代碼示例，t_employee表有10萬條記錄，設置分頁大小為1000： d1 = Calendar.getInstance().getTime();vsql = "select count(*) cnt from t_employee"pstmt = conn.prepareStatement(vsql);ResultSet rs = pstmt.executeQuery();Integer cnt = 0;while (rs.next()          cnt = rs.get

50、Int("cnt");Integer lastid=0;Integer pagesize=1000;System.out.println("cnt:" + cnt);String vsql = "select count(*) cnt from t_employee"PreparedStatement pstmt = conn.prepareStatement(vsql);ResultSet rs = pstmt.executeQuery();Integer cnt = 0;while (rs.next()   &

51、#160;      cnt = rs.getInt("cnt");Integer lastid = 0;Integer pagesize = 1000;System.out.println("cnt:" + cnt);for (int i = 0; i <= cnt / pagesize; i+)          vsql = "select * from (select * from t_employee

52、 where id>? order by id) where rownum<=?"         pstmt = conn.prepareStatement(vsql);         pstmt.setFetchSize(1000);         pstmt.setInt(1, lastid);  

53、0;      pstmt.setInt(2, pagesize);         rs = pstmt.executeQuery();         int col_cnt = rs.getMetaData().getColumnCount();         Object o;  

54、;       while (rs.next()                    for (int j = 1; j <= col_cnt; j+)                 

55、60;           o = rs.getObject(j);                                     

56、; lastid = rs.getInt("id");                  rs.close();         pstmt.close(); 以上代碼實際執(zhí)行時間為6.516秒 很多持久層框架為了盡量讓程序員使用方便，封裝了jdbc通過statement執(zhí)行數(shù)據(jù)返回到resultset的細節(jié)，導致程序

57、員會想采用分頁的方式處理問題。實際上如果我們采用jdbc原始的resultset游標處理記錄，在resultset循環(huán)讀取的過程中處理記錄，這樣就可以一次從數(shù)據(jù)庫取出所有記錄。顯著提高性能。這里需要注意的是，采用resultset游標處理記錄時，應該將游標的打開方式設置為FORWARD_READONLY模式(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY)，否則會把結(jié)果緩存在JVM里，造成JVM Out of memory問題。 代碼示例： String vsql ="select * from t_e

58、mployee"PreparedStatement pstmt = conn.prepareStatement(vsql,ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);pstmt.setFetchSize(100);ResultSet rs = pstmt.executeQuery(vsql);int col_cnt = rs.getMetaData().getColumnCount();Object o;while (rs.next()       

59、0;  for (int j = 1; j <= col_cnt; j+)                    o = rs.getObject(j);         調(diào)整后的代碼實際執(zhí)行時間為3.156秒 從測試結(jié)果可以看出性能提高了1倍多，如果采用分頁模式數(shù)據(jù)庫每次還需發(fā)生磁盤IO的話那性能可以提高更多。

60、iBatis等持久層框架考慮到會有這種需求，所以也有相應的解決方案，在iBatis里我們不能采用queryForList的方法，而應用該采用queryWithRowHandler加回調(diào)事件的方式處理，如下所示： MyRowHandler myrh=new MyRowHandler();sqlmap.queryWithRowHandler("getAllEmployee", myrh); class MyRowHandler implements RowHandler     public void handleRow(Obj

61、ect o)        /todo something     iBatis的queryWithRowHandler很好的封裝了resultset遍歷的事件處理，效果及性能與resultset遍歷一樣，也不會產(chǎn)生JVM內(nèi)存溢出。 4、減少數(shù)據(jù)庫服務器CPU運算4.1、使用綁定變量綁定變量是指SQL中對變化的值采用變量參數(shù)的形式提交，而不是在SQL中直接拼寫對應的值。非綁定變量寫法：Select * from employee where id=1234567綁定變量寫法：Selec

62、t * from employee where id=?Preparestatement.setInt(1,1234567) Java中Preparestatement就是為處理綁定變量提供的對像，綁定變量有以下優(yōu)點：1、防止SQL注入2、提高SQL可讀性3、提高SQL解析性能，不使用綁定變更我們一般稱為硬解析，使用綁定變量我們稱為軟解析。第1和第2點很好理解，做編碼的人應該都清楚，這里不詳細說明。關于第3點，到底能提高多少性能呢，下面舉一個例子說明： 假設有這個這樣的一個數(shù)據(jù)庫主機：2個4核CPU  100塊磁盤，每個磁盤支持IOPS為160業(yè)務應用的SQL如下

63、：select * from table where pk=?這個SQL平均4個IO（3個索引IO+1個數(shù)據(jù)IO）IO緩存命中率75%（索引全在內(nèi)存中，數(shù)據(jù)需要訪問磁盤）SQL硬解析CPU消耗：1ms  （常用經(jīng)驗值）SQL軟解析CPU消耗：0.02ms（常用經(jīng)驗值） 假設CPU每核性能是線性增長，訪問內(nèi)存Cache中的IO時間忽略，要求計算系統(tǒng)對如上應用采用硬解析與采用軟解析支持的每秒最大并發(fā)數(shù)： 是否使用綁定變量 CPU支持最大并發(fā)數(shù) 磁盤IO支持最大并發(fā)數(shù) 不使用 2*4*1000=8000 100*160=

64、16000 使用 2*4*1000/0.02=400000 100*160=16000  從以上計算可以看出，不使用綁定變量的系統(tǒng)當并發(fā)達到8000時會在CPU上產(chǎn)生瓶頸，當使用綁定變量的系統(tǒng)當并行達到16000時會在磁盤IO上產(chǎn)生瓶頸。所以如果你的系統(tǒng)CPU有瓶頸時請先檢查是否存在大量的硬解析操作。 使用綁定變量為何會提高SQL解析性能，這個需要從數(shù)據(jù)庫SQL執(zhí)行原理說明，一條SQL在Oracle數(shù)據(jù)庫中的執(zhí)行過程如下圖所示：   當一條SQL發(fā)送給數(shù)據(jù)庫服務器后，系統(tǒng)首先會將SQL字符串進行hash運

65、算，得到hash值后再從服務器內(nèi)存里的SQL緩存區(qū)中進行檢索，如果有相同的SQL字符，并且確認是同一邏輯的SQL語句，則從共享池緩存中取出SQL對應的執(zhí)行計劃，根據(jù)執(zhí)行計劃讀取數(shù)據(jù)并返回結(jié)果給客戶端。如果在共享池中未發(fā)現(xiàn)相同的SQL則根據(jù)SQL邏輯生成一條新的執(zhí)行計劃并保存在SQL緩存區(qū)中，然后根據(jù)執(zhí)行計劃讀取數(shù)據(jù)并返回結(jié)果給客戶端。為了更快的檢索SQL是否在緩存區(qū)中，首先進行的是SQL字符串hash值對比，如果未找到則認為沒有緩存，如果存在再進行下一步的準確對比，所以要命中SQL緩存區(qū)應保證SQL字符是完全一致，中間有大小寫或空格都會認為是不同的SQL。如果我們不采用綁定變量，采用字符串拼接

66、的模式生成SQL,那么每條SQL都會產(chǎn)生執(zhí)行計劃，這樣會導致共享池耗盡，緩存命中率也很低。 一些不使用綁定變量的場景：a、數(shù)據(jù)倉庫應用，這種應用一般并發(fā)不高，但是每個SQL執(zhí)行時間很長，SQL解析的時間相比SQL執(zhí)行時間比較小，綁定變量對性能提高不明顯。數(shù)據(jù)倉庫一般都是內(nèi)部分析應用，所以也不太會發(fā)生SQL注入的安全問題。b、數(shù)據(jù)分布不均勻的特殊邏輯，如產(chǎn)品表，記錄有1億，有一產(chǎn)品狀態(tài)字段，上面建有索引，有審核中，審核通過，審核未通過3種狀態(tài)，其中審核通過9500萬，審核中1萬，審核不通過499萬。要做這樣一個查詢：select count(*) from product where status=?采用綁定變量的話，那么只會有一個執(zhí)行計劃，如果走索引訪問，那么對于審核中查詢很快，對審核通過和審核不通過會很