面向程序员的数据库访问性能优化法则
面向程序員的數據庫訪問性能優化法則
?
特別說明：
1、 ? 本文只是面對數據庫應用開發的程序員，不適合專業 DBA ， DBA 在數據庫性能優化方面需要了解更多的知識；
2、 ? 本文許多示例及概念是基于 Oracle 數據庫描述，對于其它關系型數據庫也可以參考，但許多觀點不適合于 KV 數據庫或內存數據庫或者是基于 SSD 技術的數據庫；
3、 ? 本文未深入數據庫優化中最核心的執行計劃分析技術。
?
讀者對像：
開發人員： 如果你是做數據庫開發，那本文的內容非常適合，因為本文是從程序員的角度來談數據庫性能優化。
架構師： 如果你已經是數據庫應用的架構師，那本文的知識你應該清楚 90% ，否則你可能是一個喜歡折騰的架構師。
DBA （數據庫管理員）： 大型數據庫優化的知識非常復雜，本文只是從程序員的角度來談性能優化， DBA 除了需要了解這些知識外，還需要深入數據庫的內部體系架構來解決問題。
?
引言
在網上有很多文章介紹數據庫優化知識，但是大部份文章只是對某個一個方面進行說明，而對于我們程序員來說這種介紹并不能很好的掌握優化知識，因為很多介紹只是對一些特定的場景優化的，所以反而有時會產生誤導或讓程序員感覺不明白其中的奧妙而對數據庫優化感覺很神秘。
很多程序員總是問如何學習數據庫優化，有沒有好的教材之類的問題。在書店也看到了許多數據庫優化的專業書籍，但是感覺更多是面向 DBA 或者是 PL/SQL 開 發方面的知識，個人感覺不太適合普通程序員。而要想做到數據庫優化的高手，不是花幾周，幾個月就能達到的，這并不是因為數據庫優化有多高深，而是因為要做 好優化一方面需要有非常好的技術功底，對操作系統、存儲硬件網絡、數據庫原理等方面有比較扎實的基礎知識，另一方面是需要花大量時間對特定的數據庫進行實 踐測試與總結。
作為一個程序員，我們也許不清楚線上正式的服務器硬件配置，我們不可能像 DBA 那樣專業的對數據庫進行各種實踐測試與總結，但我們都應該非常了解我們 SQL 的業務邏輯，我們清楚 SQL 中訪問表及字段的數據情況，我們其實只關心我們的 SQL 是否能盡快返回結果。那程序員如何利用已知的知識進行數據庫優化？如何能快速定位 SQL 性能問題并找到正確的優化方向？
面對這些問題，筆者總結了一些面向程序員的基本優化法則，本文將結合實例來坦述數據庫開發的優化知識。
一、數據庫訪問優化法則簡介
要正確的優化 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 種優化法則列舉常用的優化手段并結合實例分析。
?
二、 Oracle 數據庫兩個基本概念
數據塊 (Block)
數據塊是數據庫中數據在磁盤中存儲的最小單位，也是一次 IO 訪問的最小單位，一個數據塊通常可以存儲多條記錄，數據塊大小是 DBA 在創建數據庫或表空間時指定，可指定為 2K 、 4K 、 8K 、 16K 或 32K 字節。下圖是一個 Oracle 數據庫典型的物理結構，一個數據庫可以包括多個數據文件，一個數據文件內又包含多個數據塊；
?
?
ROWID
ROWID 是每條記錄在數據庫中的唯一標識，通過 ROWID 可以直接定位記錄到對應的文件號及數據塊位置。 ROWID 內容包括文件號、對像號、數據塊號、記錄槽號，如下圖所示：
?
三、數據庫訪問優化法則詳解
1 、減少數據訪問
1.1 、創建并使用正確的索引
數據庫索引的原理非常簡單，但在復雜的表中真正能正確使用索引的人很少，即使是專業的 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 壓力比較大的系統，表的索引需要仔細評估必要性，另外索引也會占用一定的存儲空間。
?
1.2 、只通過索引訪問數據
有些時候，我們只是訪問表中的幾個字段，并且字段內容較少，我們可以為這幾個字段單獨建立一個組合索引，這樣就可以直接只通過訪問索引就能得到數據，一般索引占用的磁盤空間比表小很多，所以這種方式可以大大減少磁盤 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 請求的字段都建在索引里，所以這種只通過索引訪問數據的方法一般只用于核心應用，也就是那種對核心表訪問量最高且查詢字段數據量很少的查詢。
1.3 、優化 SQL 執行計劃
SQL 執行計劃是關系型數據庫最核心的技術之一，它表示 SQL 執行時的數據訪問算法。由于業務需求越來越復雜，表數據量也越來越大，程序員越來越懶惰， SQL 也需要支持非常復雜的業務邏輯，但 SQL 的性能還需要提高，因此，優秀的關系型數據庫除了需要支持復雜的 SQL 語法及更多函數外，還需要有一套優秀的算法庫來提高 SQL 性能。
目前 ORACLE 有 SQL 執行計劃的算法約 300 種，而且一直在增加，所以 SQL 執行計劃是一個非常復雜的課題，一個普通 DBA 能掌握 50 種就很不錯了，就算是資深 DBA 也不可能把每個執行計劃的算法描述清楚。雖然有這么多種算法，但并不表示我們無法優化執行計劃，因為我們常用的 SQL 執行計劃算法也就十幾個，如果一個程序員能把這十幾個算法搞清楚，那就掌握了 80% 的 SQL 執行計劃調優知識。
由于篇幅的原因， SQL 執行計劃需要專題介紹，在這里就不多說了。
?
2 、返回更少的數據
2.1 、數據分頁處理
一般數據分頁方式有：
2.1.1 、客戶端 ( 應用程序或瀏覽器 ) 分頁
將數據從應用服務器全部下載到本地應用程序或瀏覽器，在應用程序或瀏覽器內部通過本地代碼進行分頁處理
優點：編碼簡單，減少客戶端與應用服務器網絡交互次數
缺點：首次交互時間長，占用客戶端內存
適應場景：客戶端與應用服務器網絡延時較大，但要求后續操作流暢，如手機 GPRS ，超遠程訪問（跨國）等等。
2.1.2 、應用服務器分頁
將數據從數據庫服務器全部下載到應用服務器，在應用服務器內部再進行數據篩選。以下是一個應用服務器端 Java 程序分頁的示例：
List list=executeQuery(“select * from employee order by id”);
Int count= list.size();
List subList= list.subList(10, 20);
?
優點：編碼簡單，只需要一次 SQL 交互，總數據與分頁數據差不多時性能較好。
缺點：總數據量較多時性能較差。
適應場景：數據庫系統不支持分頁處理，數據量較小并且可控。
?
2.1.3 、數據庫 SQL 分頁
采用數據庫 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);
?
2.2 、只返回需要的字段
通過去除不必要的返回字段可以提高性能，例：
調整前： 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 表。
?
3 、減少交互次數
3.1 、 batch DML
數據庫訪問框架一般都提供了批量提交的接口， 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 | 0.1 | 0.1 | 0.1 | 0.1 |
| 服務器 IO 處理時間 | 0.02 | 0.2 | 2 | 20 | 200 |
| 網絡交互發起時間 | 0.1 | 0.1 | 0.1 | 0.1 | 0.1 |
| 網絡數據傳輸時間 | 0.01 | 0.1 | 1 | 10 | 100 |
| 小計 | 0.23 | 0.5 | 3.2 | 30.2 | 300.2 |
| 平均每條記錄處理時間 | 0.23 | 0.05 | 0.032 | 0.0302 | 0.03002 |
?
從上可以看出， Insert 操作加大 Batch 可以對性能提高近 8 倍性能，一般根據主鍵的 Update 或 Delete 操作也可能提高 2-3 倍性能，但不如 Insert 明顯，因為 Update 及 Delete 操作可能有比較大的開銷在物理 IO 訪問。以上僅是理論計算值，實際情況需要根據具體環境測量。
?
3.2 、 In List
很多時候我們需要按一些 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 評估。
?
3.3 、設置 Fetch Size
當我們采用 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 ） |
| 1 | 20.516 |
| 2 | 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>
?
3.4 、使用存儲過程
大型數據庫一般都支持存儲過程，合理的利用存儲過程也可以提高系統性能。如你有一個業務需要將 A 表的數據做一些加工然后更新到 B 表中，但是又不可能一條 SQL 完成，這時你需要如下 3 步操作：
a ：將 A 表數據全部取出到客戶端；
b ：計算出要更新的數據；
c ：將計算結果更新到 B 表。
?
如果采用存儲過程你可以將整個業務邏輯封裝在存儲過程里，然后在客戶端直接調用存儲過程處理，這樣可以減少網絡交互的成本。
當然，存儲過程也并不是十全十美，存儲過程有以下缺點：
a 、不可移植性，每種數據庫的內部編程語法都不太相同，當你的系統需要兼容多種數據庫時最好不要用存儲過程。
b 、學習成本高， DBA 一般都擅長寫存儲過程，但并不是每個程序員都能寫好存儲過程，除非你的團隊有較多的開發人員熟悉寫存儲過程，否則后期系統維護會產生問題。
c 、業務邏輯多處存在，采用存儲過程后也就意味著你的系統有一些業務邏輯不是在應用程序里處理，這種架構會增加一些系統維護和調試成本。
d 、存儲過程和常用應用程序語言不一樣，它支持的函數及語法有可能不能滿足需求，有些邏輯就只能通過應用程序處理。
e 、如果存儲過程中有復雜運算的話，會增加一些數據庫服務端的處理成本，對于集中式數據庫可能會導致系統可擴展性問題。
f 、為了提高性能，數據庫會把存儲過程代碼編譯成中間運行代碼 ( 類似于 java 的 class 文件 ) ，所以更像靜態語言。當存儲過程引用的對像 ( 表、視圖等等 ) 結構改變后，存儲過程需要重新編譯才能生效，在 24*7 高并發應用場景，一般都是在線變更結構的，所以在變更的瞬間要同時編譯存儲過程，這可能會導致數據庫瞬間壓力上升引起故障 (Oracle 數據庫就存在這樣的問題 ) 。
?
個人觀點：普通業務邏輯盡量不要使用存儲過程，定時性的 ETL 任務或報表統計函數可以根據團隊資源情況采用存儲過程處理。
?
3.5 、優化業務邏輯
要通過優化業務邏輯來提高性能是比較困難的，這需要程序員對所訪問的數據及業務流程非常清楚。
舉一個案例：
某移動公司推出優惠套參，活動對像為 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
? 執行套參 ();
end;
?
如果我們修改業務邏輯為 :
select avg(money) as? avg_money from bill where phone_no='13988888888' and date between '201001' and '201003';
if avg_money>20 then
begin
? select vip_flag from member where phone_no='13988888888';
? if vip_flag=true then
? begin
??? 執行套參 ();
? end;
end;
通過這樣可以減少一些判斷 vip_flag 的開銷，平均話費 20 元以下的用戶就不需要再檢測是否 VIP 了。
?
如果程序員分析業務， VIP 會員比例為 1% ，平均話費 20 元以上的用戶比例為 90% ，那我們改成如下：
select vip_flag from member where phone_no='13988888888';
if 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
? begin
??? 執行套參 ();
? end;
end;
這樣就只有 1% 的 VIP 會員才會做檢測平均話費，最終大大減少了 SQL 的交互次數。
?
以上只是一個簡單的示例，實際的業務總是比這復雜得多，所以一般只是高級程序員更容易做出優化的邏輯，但是我們需要有這樣一種成本優化的意識。
?
3.6 、使用 ResultSet 游標處理記錄
現在大部分 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;
while (rs.next()) {
???????? 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);
ResultSet rs = pstmt.executeQuery();
Integer cnt = 0;
while (rs.next()) {
???????? 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 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 問題。
?
代碼示例：
?
String vsql ="select * from t_employee";
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()) {
???????? 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 內存溢出。
?
4 、減少數據庫服務器 CPU 運算
4.1 、使用綁定變量
綁定變量是指 SQL 中對變化的值采用變量參數的形式提交，而不是在 SQL 中直接拼寫對應的值。
非綁定變量寫法： Select * from employee where id=1234567
綁定變量寫法：
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 | 100*160=16000 |
?
從以上計算可以看出，不使用綁定變量的系統當并發達到 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';// 使用索引
?
4.2 、合理使用排序
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 執行計劃優化專題。
?
4.3 、減少比較操作
我們 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 溝通并確認準確的原因。
?
4.4 、大量復雜運算在客戶端處理
什么是復雜運算，一般我認為是一秒鐘 CPU 只能做 10 萬次以內的運算。如含小數的對數及指數運算、三角函數、 3DES 及 BASE64 數據加密算法等等。
如果有大量這類函數運算，盡量放在客戶端處理，一般 CPU 每秒中也只能處理 1 萬 -10 萬次這樣的函數運算，放在數據庫內不利于高并發處理。
?
5 、利用更多的資源
5.1 、客戶端多進程并行訪問
多進程并行訪問是指在客戶端創建多個進程 ( 線程 ) ，每個進程建立一個與數據庫的連接，然后同時向數據庫提交訪問請求。當數據庫主機資源有空閑時，我們可以采用客戶端多進程并行訪問的方法來提高性能。如果數據庫主機已經很忙時，采用多進程并行訪問性能不會提高，反而可能會更慢。所以使用這種方式最好與 DBA 或系統管理員進行溝通后再決定是否采用。
?
例如：
我們有 10000 個產品 ID ，現在需要根據 ID 取出產品的詳細信息，如果單線程訪問，按每個 IO 要 5ms 計算，忽略主機 CPU 運算及網絡傳輸時間，我們需要 50s 才能完成任務。如果采用 5 個并行訪問，每個進程訪問 2000 個 ID ，那么 10s 就有可能完成任務。
那是不是并行數越多越好呢，開 1000 個并行是否只要 50ms 就搞定，答案肯定是否定的，當并行數超過服務器主機資源的上限時性能就不會再提高，如果再增加反而會增加主機的進程間調度成本和進程沖突機率。
?
以下是一些如何設置并行數的基本建議：
如果瓶頸在服務器主機，但是主機還有空閑資源，那么最大并行數取主機 CPU 核數和主機提供數據服務的磁盤數兩個參數中的最小值，同時要保證主機有資源做其它任務。
如果瓶頸在客戶端處理，但是客戶端還有空閑資源，那建議不要增加 SQL 的并行，而是用一個進程取回數據后在客戶端起多個進程處理即可，進程數根據客戶端 CPU 核數計算。
如果瓶頸在客戶端網絡，那建議做數據壓縮或者增加多個客戶端，采用 map reduce 的架構處理。
如果瓶頸在服務器網絡，那需要增加服務器的網絡帶寬或者在服務端將數據壓縮后再處理了。
?
5.2 、數據庫并行處理
數據庫并行處理是指客戶端一條 SQL 的請求，數據庫內部自動分解成多個進程并行處理，如下圖所示：
?
?
并不是所有的 SQL 都可以使用并行處理，一般只有對表或索引進行全部訪問時才可以使用并行。數據庫表默認是不打開并行訪問，所以需要指定 SQL 并行的提示，如下所示：
select /*+parallel(a,4)*/ * from employee;
?
并行的優點：
使用多進程處理，充分利用數據庫主機資源（ CPU,IO ），提高性能。
并行的缺點：
1 、單個會話占用大量資源，影響其它會話，所以只適合在主機負載低時期使用；
2 、只能采用直接 IO 訪問，不能利用緩存數據，所以執行前會觸發將臟緩存數據寫入磁盤操作。
?
注：
1 、并行處理在 OLTP 類系統中慎用，使用不當會導致一個會話把主機資源全部占用，而正常事務得不到及時響應，所以一般只是用于數據倉庫平臺。
2 、一般對于百萬級記錄以下的小表采用并行訪問性能并不能提高，反而可能會讓性能更差。
?
葉正盛（ MKing ）
2010-12-3
轉載于:https://www.cnblogs.com/chaunqi/archive/2011/05/26/tt125.html
總結
以上是生活随笔為你收集整理的面向程序员的数据库访问性能优化法则的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 【热烈祝贺】俺们的S5PV210 工控板
- 下一篇： poj2231