特别說明：
1、 本文隻是面對資料庫應用開發的程式員，不适合專業DBA，DBA在資料庫性能優化方面需要了解更多的知識；
2、 本文許多示例及概念是基于Oracle資料庫描述，對于其它關系型資料庫也可以參考，但許多觀點不适合于KV資料庫或記憶體資料庫或者是基于SSD技術的資料庫；
3、 本文未深入資料庫優化中最核心的執行計劃分析技術。
讀者對像：
開發人員：如果你是做資料庫開發，那本文的内容非常适合，因為本文是從程式員的角度來談資料庫性能優化。
架構師：如果你已經是資料庫應用的架構師，那本文的知識你應該清楚90%，否則你可能是一個喜歡折騰的架構師。
DBA（資料庫管理者）：大型資料庫優化的知識非常複雜，本文隻是從程式員的角度來談性能優化，DBA除了需要了解這些知識外，還需要深入資料庫的内部體系架構來解決問題。
引言
在網上有很多文章介紹資料庫優化知識，但是大部份文章隻是對某個一個方面進行說明，而對于我們程式員來說這種介紹并不能很好的掌握優化知識，因為很多介紹隻是對一些特定的場景優化的，是以反而有時會産生誤導或讓程式員感覺不明白其中的奧妙而對資料庫優化感覺很神秘。
很多程式員總是問如何學習資料庫優化，有沒有好的教材之類的問題。在書店也看到了許多資料庫優化的專業書籍，但是感覺更多是面向DBA或者是PL/SQL開發方面的知識，個人感覺不太适合普通程式員。而要想做到資料庫優化的高手，不是花幾周，幾個月就能達到的，這并不是因為資料庫優化有多高深，而是因為要做好優化一方面需要有非常好的技術功底，對作業系統、存儲硬體網絡、資料庫原理等方面有比較紮實的基礎知識，另一方面是需要花大量時間對特定的資料庫進行實踐測試與總結。
作為一個程式員，我們也許不清楚線上正式的伺服器硬體配置，我們不可能像DBA那樣專業的對資料庫進行各種實踐測試與總結，但我們都應該非常了解我們SQL的業務邏輯，我們清楚SQL中通路表及字段的資料情況，我們其實隻關心我們的SQL是否能盡快傳回結果。那程式員如何利用已知的知識進行資料庫優化？如何能快速定位SQL性能問題并找到正确的優化方向？
面對這些問題，筆者總結了一些面向程式員的基本優化法則，本文将結合執行個體來坦述資料庫開發的優化知識。
<b>一、資料庫通路優化法則簡介</b>
要正确的優化SQL，我們需要快速定位能性的瓶頸點，也就是說快速找到我們SQL主要的開銷在哪裡？而大多數情況性能最慢的裝置會是瓶頸點，如下載下傳時網絡速度可能會是瓶頸點，本地複制檔案時硬碟可能會是瓶頸點，為什麼這些一般的工作我們能快速确認瓶頸點呢，因為我們對這些慢速裝置的性能資料有一些基本的認識，如網絡帶寬是2Mbps，硬碟是每分鐘7200轉等等。是以，為了快速找到SQL的性能瓶頸點，我們也需要了解我們計算機系統的硬體基本性能名額，下圖展示的目前主流計算機性能名額資料。
從圖上可以看到基本上每種裝置都有兩個名額：
延時（響應時間）：表示硬體的突發處理能力；
帶寬（吞吐量）：代表硬體持續處理能力。
從上圖可以看出，計算機系統硬體性能從高到代依次為：
CPU——Cache(L1-L2-L3)——記憶體——SSD硬碟——網絡——硬碟
由于SSD硬碟還處于快速發展階段，是以本文的内容不涉及SSD相關應用系統。
根據資料庫知識，我們可以列出每種硬體主要的工作内容：
CPU及記憶體：緩存資料通路、比較、排序、事務檢測、SQL解析、函數或邏輯運算；
網絡：結果資料傳輸、SQL請求、遠端資料庫通路（dblink）；
硬碟：資料通路、資料寫入、日志記錄、大資料量排序、大表連接配接。
根據目前計算機硬體的基本性能名額及其在資料庫中主要操作内容，可以整理出如下圖所示的性能基本優化法則：
這個優化法則歸納為5個層次：
1、 減少資料通路（減少磁盤通路）
2、 傳回更少資料（減少網絡傳輸或磁盤通路）
3、 減少互動次數（減少網絡傳輸）
4、 減少伺服器CPU開銷（減少CPU及記憶體開銷）
5、 利用更多資源（增加資源）
由于每一層優化法則都是解決其對應硬體的性能問題，是以帶來的性能提升比例也不一樣。傳統資料庫系統設計是也是盡可能對低速裝置提供優化方法，是以針對低速裝置問題的可優化手段也更多，優化成本也更低。我們任何一個SQL的性能優化都應該按這個規則由上到下來診斷問題并提出解決方案，而不應該首先想到的是增加資源解決問題。
以下是每個優化法則層級對應優化效果及成本經驗參考：
優化法則
性能提升效果
優化成本
減少資料通路
1~1000
低
傳回更少資料
1~100
減少互動次數
1~20
減少伺服器CPU開銷
1~5
利用更多資源
@~10
高
接下來，我們針對5種優化法則列舉常用的優化手段并結合執行個體分析。
<b>二、Oracle資料庫兩個基本概念</b>
<b>資料塊(Block)</b>
資料塊是資料庫中資料在磁盤中存儲的最小機關，也是一次IO通路的最小機關，一個資料塊通常可以存儲多條記錄，資料塊大小是DBA在建立資料庫或表空間時指定，可指定為2K、4K、8K、16K或32K位元組。下圖是一個Oracle資料庫典型的實體結構，一個資料庫可以包括多個資料檔案，一個資料檔案内又包含多個資料塊；
<b>ROWID</b>
ROWID是每條記錄在資料庫中的唯一辨別，通過ROWID可以直接定位記錄到對應的檔案号及資料塊位置。ROWID内容包括檔案号、對像号、資料塊号、記錄槽号，如下圖所示：
<b>三、資料庫通路優化法則詳解</b>
<b>1、減少資料通路</b>
<b>1.1、建立并使用正确的索引</b>
資料庫索引的原理非常簡單，但在複雜的表中真正能正确使用索引的人很少，即使是專業的DBA也不一定能完全做到最優。
索引會大大增加表記錄的DML(INSERT,UPDATE,DELETE)開銷，正确的索引可以讓性能提升100，1000倍以上，不合理的索引也可能會讓性能下降100倍，是以在一個表中建立什麼樣的索引需要平衡各種業務需求。
索引常見問題：
索引有哪些種類？
常見的索引有B-TREE索引、位圖索引、全文索引，位圖索引一般用于資料倉庫應用，全文索引由于使用較少，這裡不深入介紹。B-TREE索引包括很多擴充類型，如組合索引、反向索引、函數索引等等，以下是B-TREE索引的簡單介紹：
B-TREE索引也稱為平衡樹索引(Balance Tree)，它是一種按字段排好序的樹形目錄結構，主要用于提升查詢性能和唯一限制支援。B-TREE索引的内容包括根節點、分支節點、葉子節點。
葉子節點内容：索引字段内容+表記錄ROWID
根節點，分支節點内容：當一個資料塊中不能放下所有索引字段資料時，就會形成樹形的根節點或分支節點，根節點與分支節點儲存了索引樹的順序及各層級間的引用關系。
一個普通的BTREE索引結構示意圖如下所示：
如果我們把一個表的内容認為是一本字典，那索引就相當于字典的目錄，如下圖所示：
圖中是一個字典按部首+筆劃數的目錄，相當于給字典建了一個按部首+筆劃的組合索引。
一個表中可以建多個索引，就如一本字典可以建多個目錄一樣（按拼音、筆劃、部首等等）。
一個索引也可以由多個字段組成，稱為組合索引，如上圖就是一個按部首+筆劃的組合目錄。
SQL什麼條件會使用索引？
當字段上建有索引時，通常以下情況會使用索引：
INDEX_COLUMN = ?
INDEX_COLUMN > ?
INDEX_COLUMN >= ?
INDEX_COLUMN < ?
INDEX_COLUMN <= ?
INDEX_COLUMN between ? and ?
INDEX_COLUMN in (?,?,...,?)
INDEX_COLUMN like ?||'%'（後導模糊查詢）
T1. INDEX_COLUMN=T2. COLUMN1（兩個表通過索引字段關聯）
SQL什麼條件不會使用索引？
查詢條件
不能使用索引原因
INDEX_COLUMN <> ?
INDEX_COLUMN not in (?,?,...,?)
不等于操作不能使用索引
function(INDEX_COLUMN) = ?
INDEX_COLUMN + 1 = ?
INDEX_COLUMN || 'a' = ?
經過普通運算或函數運算後的索引字段不能使用索引
INDEX_COLUMN like '%'||?
INDEX_COLUMN like '%'||?||'%'
含前導模糊查詢的Like文法不能使用索引
INDEX_COLUMN is null
B-TREE索引裡不儲存字段為NULL值記錄，是以IS NULL不能使用索引
NUMBER_INDEX_COLUMN='12345'
CHAR_INDEX_COLUMN=12345
Oracle在做數值比較時需要将兩邊的資料轉換成同一種資料類型，如果兩邊資料類型不同時會對字段值隐式轉換，相當于加了一層函數處理，是以不能使用索引。
a.INDEX_COLUMN=a.COLUMN_1
給索引查詢的值應是已知資料，不能是未知字段值。
注：
經過函數運算字段的字段要使用可以使用函數索引，這種需求建議與DBA溝通。
有時候我們會使用多個字段的組合索引，如果查詢條件中第一個字段不能使用索引，那整個查詢也不能使用索引
如：我們company表建了一個id+name的組合索引，以下SQL是不能使用索引的
Select * from company where name=?
Oracle9i後引入了一種index skip scan的索引方式來解決類似的問題，但是通過index skip scan提高性能的條件比較特殊，使用不好反而性能會更差。
我們一般在什麼字段上建索引？
這是一個非常複雜的話題，需要對業務及資料充分分析後再能得出結果。主鍵及外鍵通常都要有索引，其它需要建索引的字段應滿足以下條件：
1、字段出現在查詢條件中，并且查詢條件可以使用索引；
2、語句執行頻率高，一天會有幾千次以上；
3、通過字段條件可篩選的記錄集很小，那資料篩選比例是多少才适合？
這個沒有固定值，需要根據表資料量來評估，以下是經驗公式，可用于快速評估：
小表(記錄數小于10000行的表)：篩選比例<10%；
大表：(篩選傳回記錄數)<(表總記錄數*單條記錄長度)/10000/16
單條記錄長度≈字段平均内容長度之和+字段數*2
以下是一些字段是否需要建B-TREE索引的經驗分類：
字段類型
常見字段名
需要建索引的字段
主鍵
ID,PK
外鍵
PRODUCT_ID,COMPANY_ID,MEMBER_ID,ORDER_ID,TRADE_ID,PAY_ID
有對像或身份辨別意義字段
HASH_CODE,USERNAME,IDCARD_NO,EMAIL,TEL_NO,IM_NO
索引慎用字段,需要進行資料分布及使用場景詳細評估
日期
GMT_CREATE,GMT_MODIFIED
年月
YEAR,MONTH
狀态标志
PRODUCT_STATUS,ORDER_STATUS,IS_DELETE,VIP_FLAG
類型
ORDER_TYPE,IMAGE_TYPE,GENDER,CURRENCY_TYPE
區域
COUNTRY,PROVINCE,CITY
操作人員
CREATOR,AUDITOR
數值
LEVEL,AMOUNT,SCORE
長字元
ADDRESS,COMPANY_NAME,SUMMARY,SUBJECT
不适合建索引的字段
描述備注
DESCRIPTION,REMARK,MEMO,DETAIL
大字段
FILE_CONTENT,EMAIL_CONTENT
如何知道SQL是否使用了正确的索引？
簡單SQL可以根據索引使用文法規則判斷，複雜的SQL不好辦，判斷SQL的響應時間是一種政策，但是這會受到資料量、主機負載及緩存等因素的影響，有時資料全在緩存裡，可能全表通路的時間比索引通路時間還少。要準确知道索引是否正确使用，需要到資料庫中檢視SQL真實的執行計劃，這個話題比較複雜，詳見SQL執行計劃專題介紹。
索引對DML(INSERT,UPDATE,DELETE)附加的開銷有多少？
這個沒有固定的比例，與每個表記錄的大小及索引字段大小密切相關，以下是一個普通表測試資料，僅供參考：
索引對于Insert性能降低56%
索引對于Update性能降低47%
索引對于Delete性能降低29%
是以對于寫IO壓力比較大的系統，表的索引需要仔細評估必要性，另外索引也會占用一定的存儲空間。
<b>1.2、隻通過索引通路資料</b>
有些時候，我們隻是通路表中的幾個字段，并且字段内容較少，我們可以為這幾個字段單獨建立一個組合索引，這樣就可以直接隻通過通路索引就能得到資料，一般索引占用的磁盤空間比表小很多，是以這種方式可以大大減少磁盤IO開銷。
如：select id,name from company where type='2';
如果這個SQL經常使用，我們可以在type,id,name上建立組合索引
create index my_comb_index on company(type,id,name);
有了這個組合索引後，SQL就可以直接通過my_comb_index索引傳回資料，不需要通路company表。
還是拿字典舉例：有一個需求，需要查詢一本漢語字典中所有漢字的個數，如果我們的字典沒有目錄索引，那我們隻能從字典内容裡一個一個字計數，最後傳回結果。如果我們有一個拼音目錄，那就可以隻通路拼音目錄的漢字進行計數。如果一本字典有1000頁，拼音目錄有20頁，那我們的資料通路成本相當于全表通路的50分之一。
切記，性能優化是無止境的，當性能可以滿足需求時即可，不要過度優化。在實際資料庫中我們不可能把每個SQL請求的字段都建在索引裡，是以這種隻通過索引通路資料的方法一般隻用于核心應用，也就是那種對核心表通路量最高且查詢字段資料量很少的查詢。
<b>1.3、優化SQL執行計劃</b>
SQL執行計劃是關系型資料庫最核心的技術之一，它表示SQL執行時的資料通路算法。由于業務需求越來越複雜，表資料量也越來越大，程式員越來越懶惰，SQL也需要支援非常複雜的業務邏輯，但SQL的性能還需要提高，是以，優秀的關系型資料庫除了需要支援複雜的SQL文法及更多函數外，還需要有一套優秀的算法庫來提高SQL性能。
目前ORACLE有SQL執行計劃的算法約300種，而且一直在增加，是以SQL執行計劃是一個非常複雜的課題，一個普通DBA能掌握50種就很不錯了，就算是資深DBA也不可能把每個執行計劃的算法描述清楚。雖然有這麼多種算法，但并不表示我們無法優化執行計劃，因為我們常用的SQL執行計劃算法也就十幾個，如果一個程式員能把這十幾個算法搞清楚，那就掌握了80%的SQL執行計劃調優知識。
由于篇幅的原因，SQL執行計劃需要專題介紹，在這裡就不多說了。
<b>2、傳回更少的資料</b>
<b>2.1、資料分頁處理</b>
一般資料分頁方式有：
<b>2.1.1、用戶端(應用程式或浏覽器)分頁</b>
将資料從應用伺服器全部下載下傳到本地應用程式或浏覽器，在應用程式或浏覽器内部通過本地代碼進行分頁處理
優點：編碼簡單，減少用戶端與應用伺服器網絡互動次數
缺點：首次互動時間長，占用用戶端記憶體
适應場景：用戶端與應用伺服器網絡延時較大，但要求後續操作流暢，如手機GPRS，超遠端通路（跨國）等等。
<b>2.1.2、應用伺服器分頁</b>
将資料從資料庫伺服器全部下載下傳到應用伺服器，在應用伺服器内部再進行資料篩選。以下是一個應用伺服器端Java程式分頁的示例：
List list=executeQuery(“select * from employee order by id”);
Int count= list.size();
List subList= list.subList(10, 20);
優點：編碼簡單，隻需要一次SQL互動，總資料與分頁資料差不多時性能較好。
缺點：總資料量較多時性能較差。
适應場景：資料庫系統不支援分頁處理，資料量較小并且可控。
<b>2.1.3、資料庫SQL分頁</b>
采用資料庫SQL分頁需要兩次SQL完成
一個SQL計算總數量
一個SQL傳回分頁後的資料
優點：性能好
缺點：編碼複雜，各種資料庫文法不同，需要兩次SQL互動。
oracle資料庫一般采用rownum來進行分頁，常用分頁文法有如下兩種：
直接通過rownum分頁：
select * from (
select a.*,rownum rn from
(select * from product a where company_id=? order by status) a
where rownum<=20)
where rn>10;
資料通路開銷=索引IO+索引全部記錄結果對應的表資料IO
采用rowid分頁文法
優化原理是通過純索引找出分頁記錄的ROWID，再通過ROWID回表傳回資料，要求内層查詢和排序字段全在索引裡。
create index myindex on product(company_id,status);
select b.* from (
select * from (
select a.*,rownum rn from
(select rowid rid,status from product a where company_id=? order by status) a
where rownum<=20)
where rn>10) a, product b
where a.rid=b.rowid;
資料通路開銷=索引IO+索引分頁結果對應的表資料IO
執行個體：
一個公司産品有1000條記錄，要分頁取其中20個産品，假設通路公司索引需要50個IO，2條記錄需要1個表資料IO。
那麼按第一種ROWNUM分頁寫法，需要550(50+1000/2)個IO，按第二種ROWID分頁寫法，隻需要60個IO(50+20/2);
<b>2.2、隻傳回需要的字段</b>
通過去除不必要的傳回字段可以提高性能，例：
調整前：select * from product where company_id=?;
調整後：select id,name from product where company_id=?;
優點：
1、減少資料在網絡上傳輸開銷
2、減少伺服器資料處理開銷
3、減少用戶端記憶體占用
4、字段變更時提前發現問題，減少程式BUG
5、如果通路的所有字段剛好在一個索引裡面，則可以使用純索引通路提高性能。
缺點：增加編碼工作量
由于會增加一些編碼工作量，是以一般需求通過開發規範來要求程式員這麼做，否則等項目上線後再整改工作量更大。
如果你的查詢表中有大字段或内容較多的字段，如備注資訊、檔案内容等等，那在查詢表時一定要注意這方面的問題，否則可能會帶來嚴重的性能問題。如果表經常要查詢并且請求大内容字段的機率很低，我們可以采用分表處理，将一個大表分拆成兩個一對一的關系表，将不常用的大内容字段放在一張單獨的表中。如一張存儲上傳檔案的表：
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）
通過這種分拆，可以大大提少T_FILE表的單條記錄及總大小，這樣在查詢T_FILE時性能會更好，當需要查詢FILE_CONTENT字段内容時再通路T_FILECONTENT表。
<b>3、減少互動次數</b>
<b>3.1、batch DML</b>
資料庫通路架構一般都提供了批量送出的接口，jdbc支援batch的送出處理方法，當你一次性要往一個表中插入1000萬條資料時，如果采用普通的executeUpdate處理，那麼和伺服器互動次數為1000萬次，按每秒鐘可以向資料庫伺服器送出10000次估算，要完成所有工作需要1000秒。如果采用批量送出模式，1000條送出一次，那麼和伺服器互動次數為1萬次，互動次數大大減少。采用batch操作一般不會減少很多資料庫伺服器的實體IO，但是會大大減少用戶端與服務端的互動次數，進而減少了多次發起的網絡延時開銷，同時也會降低資料庫的CPU開銷。
假設要向一個普通表插入1000萬資料，每條記錄大小為1K位元組，表上沒有任何索引，用戶端與資料庫伺服器網絡是100Mbps，以下是根據現在一般計算機能力估算的各種batch大小性能對比值：
機關：ms
No batch
Batch=10
Batch=100
Batch=1000
Batch=10000
伺服器事務處理時間
0.1
伺服器IO處理時間
0.02
0.2
2
20
200
網絡互動發起時間
網絡資料傳輸時間
0.01
1
10
100
小計
0.23
0.5
3.2
30.2
300.2
平均每條記錄處理時間
0.05
0.032
0.0302
0.03002
從上可以看出，Insert操作加大Batch可以對性能提高近8倍性能，一般根據主鍵的Update或Delete操作也可能提高2-3倍性能，但不如Insert明顯，因為Update及Delete操作可能有比較大的開銷在實體IO通路。以上僅是理論計算值，實際情況需要根據具體環境測量。
<b>3.2、In List</b>
很多時候我們需要按一些ID查詢資料庫記錄，我們可以采用一個ID一個請求發給資料庫，如下所示：
for :var in ids[] do begin
select * from mytable where id=:var;
end;
我們也可以做一個小的優化， 如下所示，用ID INLIST的這種方式寫SQL：
select * from mytable where id in(:id1,id2,...,idn);
通過這樣處理可以大大減少SQL請求的數量，進而提高性能。那如果有10000個ID，那是不是全部放在一條SQL裡處理呢？答案肯定是否定的。首先大部份資料庫都會有SQL長度和IN裡個數的限制，如ORACLE的IN裡就不允許超過1000個值。
另外目前資料庫一般都是采用基于成本的優化規則，當IN數量達到一定值時有可能改變SQL執行計劃，從索引通路變成全表通路，這将使性能急劇變化。随着SQL中IN的裡面的值個數增加，SQL的執行計劃會更複雜，占用的記憶體将會變大，這将會增加伺服器CPU及記憶體成本。
評估在IN裡面一次放多少個值還需要考慮應用伺服器本地記憶體的開銷，有并發通路時要計算本地資料使用周期内的并發上限，否則可能會導緻記憶體溢出。
綜合考慮，一般IN裡面的值個數超過20個以後性能基本沒什麼太大變化，也特别說明不要超過100，超過後可能會引起執行計劃的不穩定性及增加資料庫CPU及記憶體成本，這個需要專業DBA評估。
<b>3.3、設定Fetch Size</b>
當我們采用select從資料庫查詢資料時，資料預設并不是一條一條傳回給用戶端的，也不是一次全部傳回用戶端的，而是根據用戶端fetch_size參數處理，每次隻傳回fetch_size條記錄，當用戶端遊标周遊到尾部時再從服務端取資料，直到最後全部傳送完成。是以如果我們要從服務端一次取大量資料時，可以加大fetch_size，這樣可以減少結果資料傳輸的互動次數及伺服器資料準備時間，提高性能。
以下是jdbc測試的代碼，采用本地資料庫，表緩存在資料庫CACHE中，是以沒有網絡連接配接及磁盤IO開銷，用戶端隻周遊遊标，不做任何處理，這樣更能展現fetch參數的影響：
String vsql ="select * from t_employee";
PreparedStatement 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++) {
o = rs.getObject(i);
}
}
測試示例中的employee表有100000條記錄，每條記錄平均長度135位元組
以下是測試結果，對每種fetchsize測試5次再取平均值：
fetchsize
elapse_time（s）
20.516
11.34
4
6.894
8
4.65
16
3.584
32
2.865
64
2.656
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并不會存在一個最優的固定值，因為整體性能與記錄集大小及硬體平台有關。根據測試結果建議當一次性要取大量資料時這個值設定為100左右，不要小于40。注意，fetchsize不能設定太大，如果一次取出的資料大于JVM的記憶體會導緻記憶體溢出，是以建議不要超過1000，太大了也沒什麼性能提高，反而可能會增加記憶體溢出的危險。
注：圖中fetchsize在128以後會有一些小的波動，這并不是測試誤差，而是由于resultset填充到具體對像時間不同的原因，由于resultset已經到本地記憶體裡了，是以估計是由于CPU的L1,L2 Cache命中率變化造成，由于變化不大，是以筆者也未深入分析原因。
iBatis的SqlMapping配置檔案可以對每個SQL語句指定fetchsize大小，如下所示：
<select id="getAllProduct" resultMap="HashMap" fetchSize="1000">
select * from employee
</select>
<b>3.4、使用存儲過程</b>
大型資料庫一般都支援存儲過程，合理的利用存儲過程也可以提高系統性能。如你有一個業務需要将A表的資料做一些加工然後更新到B表中，但是又不可能一條SQL完成，這時你需要如下3步操作：
a：将A表資料全部取出到用戶端；
b：計算出要更新的資料；
c：将計算結果更新到B表。
如果采用存儲過程你可以将整個業務邏輯封裝在存儲過程裡，然後在用戶端直接調用存儲過程處理，這樣可以減少網絡互動的成本。
當然，存儲過程也并不是十全十美，存儲過程有以下缺點：
a、不可移植性，每種資料庫的内部程式設計文法都不太相同，當你的系統需要相容多種資料庫時最好不要用存儲過程。
b、學習成本高，DBA一般都擅長寫存儲過程，但并不是每個程式員都能寫好存儲過程，除非你的團隊有較多的開發人員熟悉寫存儲過程，否則後期系統維護會産生問題。
c、業務邏輯多處存在，采用存儲過程後也就意味着你的系統有一些業務邏輯不是在應用程式裡處理，這種架構會增加一些系統維護和調試成本。
d、存儲過程和常用應用程式語言不一樣，它支援的函數及文法有可能不能滿足需求，有些邏輯就隻能通過應用程式處理。
e、如果存儲過程中有複雜運算的話，會增加一些資料庫服務端的處理成本，對于集中式資料庫可能會導緻系統可擴充性問題。
f、為了提高性能，資料庫會把存儲過程代碼編譯成中間運作代碼(類似于java的class檔案)，是以更像靜态語言。當存儲過程引用的對像(表、視圖等等)結構改變後，存儲過程需要重新編譯才能生效，在24*7高并發應用場景，一般都是線上變更結構的，是以在變更的瞬間要同時編譯存儲過程，這可能會導緻資料庫瞬間壓力上升引起故障(Oracle資料庫就存在這樣的問題)。
個人觀點：普通業務邏輯盡量不要使用存儲過程，定時性的ETL任務或報表統計函數可以根據團隊資源情況采用存儲過程處理。
<b>3.5、優化業務邏輯</b>
要通過優化業務邏輯來提高性能是比較困難的，這需要程式員對所通路的資料及業務流程非常清楚。
舉一個案例：
某移動公司推出優惠套參，活動對像為VIP會員并且2010年1，2，3月平均話費20元以上的客戶。
那我們的檢測邏輯為：
select avg(money) as avg_money from bill where phone_no='13988888888' and date between '201001' and '201003';
select vip_flag from member where phone_no='13988888888';
if avg_money>20 and vip_flag=true then
begin
執行套參();
如果我們修改業務邏輯為:
select avg(money) as avg_money from bill where phone_no='13988888888' and date between '201001' and '201003';
if avg_money>20 then
select vip_flag from member where phone_no='13988888888';
if vip_flag=true then
begin
執行套參();
end;
通過這樣可以減少一些判斷vip_flag的開銷，平均話費20元以下的使用者就不需要再檢測是否VIP了。
如果程式員分析業務，VIP會員比例為1%，平均話費20元以上的使用者比例為90%，那我們改成如下：
if vip_flag=true then
select avg(money) as avg_money from bill where phone_no='13988888888' and date between '201001' and '201003';
if avg_money>20 then
這樣就隻有1%的VIP會員才會做檢測平均話費，最終大大減少了SQL的互動次數。
以上隻是一個簡單的示例，實際的業務總是比這複雜得多，是以一般隻是進階程式員更容易做出優化的邏輯，但是我們需要有這樣一種成本優化的意識。
<b>3.6、使用ResultSet遊标處理記錄</b>
現在大部分Java架構都是通過jdbc從資料庫取出資料，然後裝載到一個list裡再處理，list裡可能是業務Object，也可能是hashmap。
由于JVM記憶體一般都小于4G，是以不可能一次通過sql把大量資料裝載到list裡。為了完成功能，很多程式員喜歡采用分頁的方法處理，如一次從資料庫取1000條記錄，通過多次循環搞定，保證不會引起JVM Out of memory問題。
以下是實作此功能的代碼示例，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;
cnt = rs.getInt("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);
Integer lastid = 0;
Integer pagesize = 1000;
for (int i = 0; i <= cnt / pagesize; i++) {
vsql = "select * from (select * from t_employee where id>? order by id) where rownum<=?";
pstmt = conn.prepareStatement(vsql);
pstmt.setFetchSize(1000);
pstmt.setInt(1, lastid);
pstmt.setInt(2, pagesize);
rs = pstmt.executeQuery();
int col_cnt = rs.getMetaData().getColumnCount();
Object o;
while (rs.next()) {
for (int j = 1; j <= col_cnt; j++) {
o = rs.getObject(j);
}
lastid = rs.getInt("id");
}
rs.close();
pstmt.close();
以上代碼實際執行時間為6.516秒
很多持久層架構為了盡量讓程式員使用友善，封裝了jdbc通過statement執行資料傳回到resultset的細節，導緻程式員會想采用分頁的方式處理問題。實際上如果我們采用jdbc原始的resultset遊标處理記錄，在resultset循環讀取的過程中處理記錄，這樣就可以一次從資料庫取出所有記錄。顯著提高性能。
這裡需要注意的是，采用resultset遊标處理記錄時，應該将遊标的打開方式設定為FORWARD_READONLY模式(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY)，否則會把結果緩存在JVM裡，造成JVM Out of memory問題。
代碼示例：
pstmt.setFetchSize(100);
int col_cnt = rs.getMetaData().getColumnCount();
for (int j = 1; j <= col_cnt; j++) {
o = rs.getObject(j);
調整後的代碼實際執行時間為3.156秒
從測試結果可以看出性能提高了1倍多，如果采用分頁模式資料庫每次還需發生磁盤IO的話那性能可以提高更多。
iBatis等持久層架構考慮到會有這種需求，是以也有相應的解決方案，在iBatis裡我們不能采用queryForList的方法，而應用該采用queryWithRowHandler加回調事件的方式處理，如下所示：
MyRowHandler myrh=new MyRowHandler();
sqlmap.queryWithRowHandler("getAllEmployee", myrh);
class MyRowHandler implements RowHandler {
public void handleRow(Object o) {
//todo something
iBatis的queryWithRowHandler很好的封裝了resultset周遊的事件處理，效果及性能與resultset周遊一樣，也不會産生JVM記憶體溢出。
<b>4、減少資料庫伺服器CPU運算</b>
<b>4.1、使用綁定變量</b>
綁定變量是指SQL中對變化的值采用變量參數的形式送出，而不是在SQL中直接拼寫對應的值。
非綁定變量寫法：Select * from employee where id=1234567
綁定變量寫法：sd
Select * from employee where id=?
Preparestatement.setInt(1,1234567)
Java中Preparestatement就是為處理綁定變量提供的對像，綁定變量有以下優點：
1、防止SQL注入
2、提高SQL可讀性
3、提高SQL解析性能，不使用綁定變更我們一般稱為硬解析，使用綁定變量我們稱為軟解析。
第1和第2點很好了解，做編碼的人應該都清楚，這裡不詳細說明。關于第3點，到底能提高多少性能呢，下面舉一個例子說明：
假設有這個這樣的一個資料庫主機：
2個4核CPU
100塊磁盤，每個磁盤支援IOPS為160
業務應用的SQL如下：
select * from table where pk=?
這個SQL平均4個IO（3個索引IO+1個資料IO）
IO緩存命中率75%（索引全在記憶體中，資料需要通路磁盤）
SQL硬解析CPU消耗：1ms （常用經驗值）
SQL軟解析CPU消耗：0.02ms（常用經驗值）
假設CPU每核性能是線性增長，通路記憶體Cache中的IO時間忽略，要求計算系統對如上應用采用硬解析與采用軟解析支援的每秒最大并發數：
是否使用綁定變量
CPU支援最大并發數
磁盤IO支援最大并發數
不使用
2*4*1000=8000
100*160=16000
使用
2*4*1000/0.02=400000
從以上計算可以看出，不使用綁定變量的系統當并發達到8000時會在CPU上産生瓶頸，當使用綁定變量的系統當并行達到16000時會在磁盤IO上産生瓶頸。是以如果你的系統CPU有瓶頸時請先檢查是否存在大量的硬解析操作。
使用綁定變量為何會提高SQL解析性能，這個需要從資料庫SQL執行原理說明，一條SQL在Oracle資料庫中的執行過程如下圖所示：
當一條SQL發送給資料庫伺服器後，系統首先會将SQL字元串進行hash運算，得到hash值後再從伺服器記憶體裡的SQL緩存區中進行檢索，如果有相同的SQL字元，并且确認是同一邏輯的SQL語句，則從共享池緩存中取出SQL對應的執行計劃，根據執行計劃讀取資料并傳回結果給用戶端。
如果在共享池中未發現相同的SQL則根據SQL邏輯生成一條新的執行計劃并儲存在SQL緩存區中，然後根據執行計劃讀取資料并傳回結果給用戶端。
為了更快的檢索SQL是否在緩存區中，首先進行的是SQL字元串hash值對比，如果未找到則認為沒有緩存，如果存在再進行下一步的準确對比，是以要命中SQL緩存區應保證SQL字元是完全一緻，中間有大小寫或空格都會認為是不同的SQL。
如果我們不采用綁定變量，采用字元串拼接的模式生成SQL,那麼每條SQL都會産生執行計劃，這樣會導緻共享池耗盡，緩存命中率也很低。
一些不使用綁定變量的場景：
a、資料倉庫應用，這種應用一般并發不高，但是每個SQL執行時間很長，SQL解析的時間相比SQL執行時間比較小，綁定變量對性能提高不明顯。資料倉庫一般都是内部分析應用，是以也不太會發生SQL注入的安全問題。
b、資料分布不均勻的特殊邏輯，如産品表，記錄有1億，有一産品狀态字段，上面建有索引，有稽核中，稽核通過，稽核未通過3種狀态，其中稽核通過9500萬，稽核中1萬，稽核不通過499萬。
要做這樣一個查詢：
select count(*) from product where status=?
采用綁定變量的話，那麼隻會有一個執行計劃，如果走索引通路，那麼對于稽核中查詢很快，對稽核通過和稽核不通過會很慢；如果不走索引，那麼對于稽核中與稽核通過和稽核不通過時間基本一樣；
對于這種情況應該不使用綁定變量，而直接采用字元拼接的方式生成SQL，這樣可以為每個SQL生成不同的執行計劃，如下所示。
select count(*) from product where status='approved'; //不使用索引
select count(*) from product where status='tbd'; //不使用索引
select count(*) from product where status='auditing';//使用索引
<b>4.2、合理使用排序</b>
Oracle的排序算法一直在優化，但是總體時間複雜度約等于nLog(n)。普通OLTP系統排序操作一般都是在記憶體裡進行的，對于資料庫來說是一種CPU的消耗，曾在PC機做過測試，單核普通CPU在1秒鐘可以完成100萬條記錄的全記憶體排序操作，是以說由于現在CPU的性能增強，對于普通的幾十條或上百條記錄排序對系統的影響也不會很大。但是當你的記錄集增加到上萬條以上時，你需要注意是否一定要這麼做了，大記錄集排序不僅增加了CPU開銷，而且可能會由于記憶體不足發生硬碟排序的現象，當發生硬碟排序時性能會急劇下降，這種需求需要與DBA溝通再決定，取決于你的需求和資料，是以隻有你自己最清楚，而不要被别人說排序很慢就吓倒。
以下列出了可能會發生排序操作的SQL文法：
Order by
Group by
Distinct
Exists子查詢
Not Exists子查詢
In子查詢
Not In子查詢
Union（并集），Union All也是一種并集操作，但是不會發生排序，如果你确認兩個資料集不需要執行去除重複資料操作，那請使用Union All 代替Union。
Minus（差集）
Intersect（交集）
Create Index
Merge Join，這是一種兩個表連接配接的内部算法，執行時會把兩個表先排序好再連接配接，應用于兩個大表連接配接的操作。如果你的兩個表連接配接的條件都是等值運算，那可以采用Hash Join來提高性能，因為Hash Join使用Hash 運算來代替排序的操作。具體原理及設定參考SQL執行計劃優化專題。
<b>4.3、減少比較操作</b>
我們SQL的業務邏輯經常會包含一些比較操作，如a=b，a<b之類的操作，對于這些比較操作資料庫都展現得很好，但是如果有以下操作，我們需要保持警惕：
Like模糊查詢，如下所示：
a like ‘%abc%’
Like模糊查詢對于資料庫來說不是很擅長，特别是你需要模糊檢查的記錄有上萬條以上時，性能比較糟糕，這種情況一般可以采用專用Search或者采用全文索引方案來提高性能。
不能使用索引定位的大量In List，如下所示：
a in (:1,:2,:3,…,:n) ----n>20
如果這裡的a字段不能通過索引比較，那資料庫會将字段與in裡面的每個值都進行比較運算，如果記錄數有上萬以上，會明顯感覺到SQL的CPU開銷加大，這個情況有兩種解決方式：
a、 将in清單裡面的資料放入一張中間小表，采用兩個表Hash Join關聯的方式處理；
b、 采用str2varList方法将字段串清單轉換一個臨時表處理，關于str2varList方法可以在網上直接查詢，這裡不詳細介紹。
以上兩種解決方案都需要與中間表Hash Join的方式才能提高性能，如果采用了Nested Loop的連接配接方式性能會更差。
如果發現我們的系統IO沒問題但是CPU負載很高，就有可能是上面的原因，這種情況不太常見，如果遇到了最好能和DBA溝通并确認準确的原因。
<b>4.4、大量複雜運算在用戶端處理</b>
什麼是複雜運算，一般我認為是一秒鐘CPU隻能做10萬次以内的運算。如含小數的對數及指數運算、三角函數、3DES及BASE64資料加密算法等等。
如果有大量這類函數運算，盡量放在用戶端處理，一般CPU每秒中也隻能處理1萬-10萬次這樣的函數運算，放在資料庫内不利于高并發處理。
<b>5、利用更多的資源</b>
<b>5.1、用戶端多程序并行通路</b>
多程序并行通路是指在用戶端建立多個程序(線程)，每個程序建立一個與資料庫的連接配接，然後同時向資料庫送出通路請求。當資料庫主機資源有空閑時，我們可以采用用戶端多程序并行通路的方法來提高性能。如果資料庫主機已經很忙時，采用多程序并行通路性能不會提高，反而可能會更慢。是以使用這種方式最好與DBA或系統管理者進行溝通後再決定是否采用。
例如：
我們有10000個産品ID，現在需要根據ID取出産品的詳細資訊，如果單線程通路，按每個IO要5ms計算，忽略主機CPU運算及網絡傳輸時間，我們需要50s才能完成任務。如果采用5個并行通路，每個程序通路2000個ID，那麼10s就有可能完成任務。
那是不是并行數越多越好呢，開1000個并行是否隻要50ms就搞定，答案肯定是否定的，當并行數超過伺服器主機資源的上限時性能就不會再提高，如果再增加反而會增加主機的程序間排程成本和程序沖突機率。
以下是一些如何設定并行數的基本建議：
如果瓶頸在伺服器主機，但是主機還有空閑資源，那麼最大并行數取主機CPU核數和主機提供資料服務的磁盤數兩個參數中的最小值，同時要保證主機有資源做其它任務。
如果瓶頸在用戶端處理，但是用戶端還有空閑資源，那建議不要增加SQL的并行，而是用一個程序取回資料後在用戶端起多個程序處理即可，程序數根據用戶端CPU核數計算。
如果瓶頸在用戶端網絡，那建議做資料壓縮或者增加多個用戶端，采用map reduce的架構處理。
如果瓶頸在伺服器網絡，那需要增加伺服器的網絡帶寬或者在服務端将資料壓縮後再處理了。
<b>5.2、資料庫并行處理</b>
資料庫并行處理是指用戶端一條SQL的請求，資料庫内部自動分解成多個程序并行處理，如下圖所示：
并不是所有的SQL都可以使用并行處理，一般隻有對表或索引進行全部通路時才可以使用并行。資料庫表預設是不打開并行通路，是以需要指定SQL并行的提示，如下所示：
select /*+parallel(a,4)*/ * from employee;
并行的優點：
使用多程序處理，充分利用資料庫主機資源（CPU,IO），提高性能。
并行的缺點：
1、單個會話占用大量資源，影響其它會話，是以隻适合在主機負載低時期使用；
2、隻能采用直接IO通路，不能利用緩存資料，是以執行前會觸發将髒緩存資料寫入磁盤操作。
1、并行處理在OLTP類系統中慎用，使用不當會導緻一個會話把主機資源全部占用，而正常事務得不到及時響應，是以一般隻是用于資料倉庫平台。
2、一般對于百萬級記錄以下的小表采用并行通路性能并不能提高，反而可能會讓性能更差。
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)