1 背景概述 在大多數的開發項目中，尤其是集成項目，都會有涉及到數據分析部分的工作，數據分析多數是各種圖表的展現和交互（所謂數據可視化），數據分析的展現速度直接影響著用戶的體驗，而且絕大多數管理系統（MES、PDM/PLM、ERP、SCM、OA、HR等等）的數據都存儲在資料庫中，資料庫相關的性能優化 ...
1 背景概述
在大多數的開發項目中，尤其是集成項目，都會有涉及到數據分析部分的工作，數據分析多數是各種圖表的展現和交互（所謂數據可視化），數據分析的展現速度直接影響著用戶的體驗，而且絕大多數管理系統（MES、PDM/PLM、ERP、SCM、OA、HR等等）的數據都存儲在資料庫中，資料庫相關的性能優化可以較容易提高程式系統的整體性能、提升用戶的體驗，保障項目的順利驗收。對應用程式進行整體的性能優化需要全局考慮，比如：硬體選型、軟體架構、部署架構、程式開發等方面。本文主要側重介紹程式開發部分的資料庫層面的相關優化手段，希望能對大家有所幫助。
2 預期讀者
- 數通暢聯內部員工
- 廣大IT相關從業者
3 優化途徑
無論是開發項目還是集成項目最終的目的是項目的驗收，促進項目的回款，保障公司的資金流進一步的運作。但是如果功能程式的性能不過關，響應速度慢進而影響客戶的體驗，則直接影響著項目的驗收，從而阻礙了公司的正常運作。典型的優化途徑有：硬體選選型、系統軟體、應用程式三個途徑。
3.1 硬體選型
選擇什麼樣的伺服器都會遇到一個相同的問題，那就是選擇什麼硬體配置的伺服器。在日常的項目工作中會將伺服器區分為：應用伺服器、資料庫伺服器、文件伺服器以及其他伺服器。
- 應用伺服器：一般用於業務系統功能的部署以及應用系統的部署伺服器，對CPU、記憶體、要求都比較高。(推薦配置：CPU 3.0G Hz 及以上、4核及以上 記憶體 32G 硬碟 500G(RAID10)) 。
- 資料庫伺服器：數據伺服器對CPU、記憶體、磁碟的要求都很高，在實際應用中如果某個硬體是短板都會帶來性能問題。(推薦配置：CPU 3.0G Hz 及以上、4核及以上，記憶體 16G 及以上、固態硬碟 1T(RAID10))。
- 文件伺服器：文件伺服器主要是對IO、硬碟大小要求較高，記憶體較低。(推薦配置：CPU 3.0G Hz 及以上、記憶體 4G及以上、硬碟 2TB(RAID5))。
- 其他伺服器：至於其它伺服器就看各位的具體需要具體分析了。
通常情況下硬體配置越高，性能越好，但是綜合考慮（money！）硬體配置一般能滿足展望未來3-5年性能要求即可。註意：雲伺服器現在也是可以考慮的選擇。
3.2 系統軟體
在操作系統的選擇上最常見的就是Linux以及Windows，考慮到伺服器的性能、安全性通常我們選擇Linux操作系統。雖然Server版本操作系統本身的性能已經相對穩定，但是我們可以優化對應操作系統的配置來進一步匹配對應項目的性能需求，而Linux系列的操作系統相對來說有更多的優化策略和空間，更重要的是運維尤其遠程運維很方便。
3.3 應用程式
衡量一個程式的標準首當其衝的是程式的安全性，然後則是程式的性能，也就是程式的響應速度。對於程式的保密性要求並不是所有行業均是嚴格要求的那麼對於程式的性能則是不區分行業均是更改的性能帶來更好的體驗。
應用程式的優化必殺技通常來說就是程式（軟體）本身支持水平擴展，很多書籍都有介紹，百度關鍵字：大型系統架構，可以瞭解很多相關知識，水平擴展是另外一個話題，這個話題也會涉及到很多方面，在本文中就不一一贅述。
系統程式的基礎環境調優對應用程式的優化也較為明顯，比如：Java程式的JVM設置、PHP程式的子進程數配置、.NET程式的認證機制、運行庫設置等等。基礎環境調優也不是本文闡述重點。在下麵我們主要對軟體資料庫相關的優化方案中進行詳細介紹。
4 優化方案
雖然NoSQL也開始流行，但是更多場景下它只是資料庫的補充，資料庫自從誕生開始起就牢牢占據著管理軟體後臺存儲的主場，而且從未離開。資料庫層面的性能優化屬於短平快調整就能見效、或者在開發中稍微註意就可以大幅度提升性能的常規系統調優手段。
4.1 整體策略
我們通常需要從整體策略發的角度出發，將資料庫調優從彙總查詢、視圖方式、數據緩存等三個方面來進行。
4.1.1 彙總查詢
在日常工作中如果所涉及的查詢語句較為複雜，或者需要訪問第三方的資料庫，而在訪問第三方資料庫時常常會因為不同的資料庫中不同數據表的讀取頻率不同，進而影響性能。面對這種情況我們通常將需要查詢的內容彙總到中間表，然後直接從中間表進行數據查詢。
4.1.2 視圖方式
一般情況下創建視圖是不會直接提高性能的，但是如果需要查詢的內容涉及到多個數據表之間的關聯且關聯關係較為複查，查詢出的結果集被高頻的訪問。這時如果沒有創建視圖那麼每要查詢這個結果集就需要重新創建SQL，但是如果創建統一的視圖並且在創建視圖是已經進行SQL調優，方便大家的統一調用從而來提升資料庫的性能。
4.1.3 緩存方式
當前查詢結果的結果集是為展現內容提供數據展現，不是交互性數據操作，不經常被改變是我們可以將數據的查詢結果集放入緩存中，這樣在讀取時在緩存中進行獲取，減少了對資料庫的訪問操作進一步的提升程式的響應速率。在程式應用中常見的緩存處理手段如下：
靜態緩存
靜態緩存通常為創建一個靜態的HashMap 變數，在數據獲取是判斷Map中是否含有，如果有在Map變數中獲取，如果沒有則在資料庫中查詢然後放入緩存的Map變數中。
分散式緩存
分散式緩存通常是應用於集群部署的場景，通常應用部署於不同的業務伺服器，通過Redis 或者Mncached來進行分散式緩存的管理。
4.2 常規優化
在資料庫優化的方案中最常見也是性能優化的最關鍵的部分就是資料庫的SQL優化，本篇文章分別在查詢優化、更新優化、其他說明三個方面來進行說明常見的SQL優化。
4.2.1 查詢優化
避免在客戶端返回大數據量
儘量避免在客戶端返回大數據量，若數據量過大，應該考慮相應需求是否合理。如果一定要返回大數據量，考慮使用資料庫分頁來處理。
查詢避免使用*號
SELECT子句中避免使用*號資料庫在解析的過程中,會將*依次轉換成所有的列名，這個工作是通過查詢數據字典完成的，這意味著將耗費更多的時間。如：
|
Select * from emp |
應該為：
|
Select id,name,code from emp |
慎用DISTINCT
用EXISTS替換DISTINCT： 當提交一個包含一對多表信息(比如部門表和雇員表)的查詢時,避免在SELECT子句中使用DISTINCT. 一般可以考慮用EXIST替換, EXISTS 使查詢更為迅速,因為RDBMS核心模塊將在子查詢的條件一旦滿足後,立刻返回結果. 例子：
(低效):
|
SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D , EMP E WHERE D.DEPT_NO = E.DEPT_NO |
(高效):
|
SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT ‘X' FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO); |
UNION和UNION-ALL
用UNION-ALL 替換UNION ( 如果有可能的話)： 當SQL 語句需要UNION兩個查詢結果集合時,這兩個結果集合會以UNION-ALL的方式被合併, 然後在輸出最終結果前進行排序. 如果用UNION ALL替代UNION, 這樣排序就不是必要了. 效率就會因此得到提高. 需要註意的是，UNION ALL 將重覆輸出兩個結果集合中相同記錄. 因此還是要從業務需求分析考慮使用UNION ALL的可行性。
條件子句的註意事項
創建索引
對where中的條件列創建索引，可以加快查詢速度。對於表中的主鍵、外鍵、有對像或身份標識意義的欄位視情況添加索引。
避免null判斷
應儘量避免在 where 子句中對欄位進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描，如：
|
select name from system_users where id is null |
最好不要給資料庫留NULL，儘可能的使用 NOT NULL填充資料庫。備註、描述、評論之類的可以設置為 NULL，其他的，最好不要使用NULL。不要以為 NULL 不需要空間，比如：char(100) 型，在欄位建立時，空間就固定了， 不管是否插入值（NULL也包含在內），都是占用 100個字元的空間的，如果是varchar這樣的變長欄位， null 不占用空間。
可以在id上設置預設值0，確保表中id列沒有null值，然後這樣查詢：
|
select name from system_users where id = 0 |
避免不等於操作
儘量避免在 where 子句中使用 != 或 <> 操作符，否則將引擎放棄使用索引而進行全表掃描。
避免in或not in
in 和 not in 也要慎用，否則會導致全表掃描，如：
|
select id from t where num in(1,2,3) |
對於連續的數值，能用 between 就不要用 in 了：
|
select id from t where num between 1 and 3 |
很多時候用 exists 代替 in 是一個好的選擇：
|
select num from a where num in(select num from b) |
用下麵的語句替換：
|
select num from a where exists(select 1 from b where num=a.num) |
避免對欄位進行函數操作
儘量避免在where子句中對欄位進行函數操作，這將導致引擎放棄使用索引而進行全表掃描。如下：
|
select id from t where substring(name,1,3) = ’abc’ |
查詢所有以abc開頭的名字的id
應改為：
|
select id from t where name like 'abc%' |
4.2.2 更新優化
更新批量使用bach處理
在程式中儘量避免大量的insert或者delete同時處理，如果遇到這種情況需要使用bach進行批量統一處理。
避免大批量的insert和delete
因為這兩個操作是會鎖表的，表一鎖住了，別的操作都進不來了。所以，如果有一個大的處理，一定把其拆分，使用 LIMIT oracle(rownum),sqlserver(top)條件。
Update註意
如果只更改1、2個欄位，不要Update全部欄位，否則頻繁調用會引起明顯的性能消耗，同時帶來大量日誌。
杜絕count(*)
|
select count(*) from table； |
這樣不帶任何條件的count會引起全表掃描，並且沒有任何業務意義，是一定要杜絕的。
4.2.3 事務處理
在資料庫使用中儘量減少長事務
在資料庫中如果涉及到主表、從表、附屬從表，這時如果同時操作三個數據表同時成功以及同時失敗，如果當前數據表的數據量較大，為了降低資料庫的性能壓力，我們可以採用批處理方式分別批處理三個數據表來進行資料庫性能的提升。
減少分散式事務的使用
一般的資料庫均是支持分散式事務，當涉及到跨資料庫的不同數據表的操作時我們可以使用分散式事務。但為了提高性能損耗，儘量減少這種強一致性需求，更多情況下轉化為最終一致性方式來滿足業務需求，通常來說引入消息中間件是這種場景下的常規解決手段。
4.2.4 其他說明
多用varchar和nvarchar
儘可能的使用 varchar/nvarchar 代替 char/nchar ，因為首先變長欄位存儲空間小，可以節省存儲空間，其次對於查詢來說，在一個相對較小的欄位內搜索效率顯然要高些。
減少大欄位的使用
在資料庫中定義類型是儘量避免使用大欄位類型如：BLOB、TEXT、LONG以及Object等大對象的類型
不要在資料庫中存儲文件
在程式設計以及資料庫存儲是不要將圖片文件、其他日誌文件的文件類型存儲於資料庫中，而是在資料庫中存儲文件索引的URL將文件存儲於文件伺服器中。
4.3 配置優化
在進行資料庫連接操作時，我們可以通過選擇合適的驅動、釋放連接池中的資源、選擇符合應用場景的介面，構造只讀結果集來進一步的優化JDBC的配置。下麵我們通過連接處理、匹配介面以及返回結果三個方面進行詳細的說明。
4.3.1 連接處理
對於Java程式而言， Connention的優化通常使用數據連接池（dbcp、proxool、c3p0）來進行Connention對象的管理，這樣程式的靈活性強，便於移植。但要註意的是對象池裡中是沒有回收機制，並且對象池裡有容量限制，對於對象池裡的閑置對象儘早的釋放資源。
下麵來簡單說明不用的連接池的對比：
Dbcp（DataBase connection pool）：是apache上的一個 java連接池項目。
優點：配置方便,可以設置最大和最小連接，連接等待時間等,持續運行的穩定性，速度快。
缺點：沒有自動的去回收空閑連接的功能，大併發量的壓力下穩定性不高，不能夠進行連接池監控。
Proxool：Proxool是一種Java資料庫連接池技術。是sourceforge下的一個開源項目。
優點：可以設置最大和最小連接，具備監控功能。
缺點：明顯的性能問題持，續運行的穩定性不高。
C3p0：是在Hibernate和Spring中預設支持該資料庫連接池，實現了數據源和jndi綁定，支持jdbc3規範和jdbc2的標準擴展。
優點：支持高併發，非同步操作，有自動回收空閑連接功能。
缺點：沒有Dbcp的速度快。
4.3.2 匹配介面
對於Statement對象的優化，我們需要根據不同的應用場合選擇合適的Statement介面。如：
Statement：不帶參數，例如：查詢時，不需要到任何參數。
PreparedStatement： PreparedStatement可以寫參數化查詢，比Statement能獲得更好的性能，可以阻止常見的SQL註入式攻擊，提高安全性。
CallableStatement：專門針對存儲過程，使用它能享受到所有存儲過程帶來的優勢，但也包括存儲過程帶來的劣勢如Java程式可移植性查，依賴資料庫等。
4.3.3 返回結果
優化結果集（ResultSet）查詢時候，返回的結果集有不同的類型。結果集分兩種類型：只讀和可更改。返回的結果集預設就是只讀的。而在Oracle中我們可以設置手工加鎖語句（Select XXX forUpdate）。
明確指定主鍵，並且有此數據則鎖定若無則不鎖定
|
SELECT * FROM products WHERE id='3' FOR UPDATE; |
無主鍵或者主鍵不明確則進行表鎖定
|
SELECT * FROM products WHERE name='Mouse' FOR UPDATE; |
5 個人總結
應用程式優化是一個系統工程，需要綜合考慮，更多時候要提前考慮，在系統架構層面來保障系統具有更多優化的能力。系統運維有一種消極的說法，系統能用就行，不要輕易去改變；但對於系統開發而言，每一次代碼重構都是一次系統調優以及增強調優能力的機會。Devops也慢慢開始盛行了，開發和運營越來越密切，甚至是一套班子兩種角色，你（們）如何選擇？我個人而言，傾向主動調優、擁抱變化，即便可能帶來一些風險。
無論是對公司的產品進行開發還是在項目開發的過程中，要在全局的角度出發整體考慮、制定規範、落實到每一項的工作中，從制度上保障系統性能調優的能力。筆者作為數通暢聯公司的一名技術員工，今天將自己所學所用的常見的資料庫優化相關處理總結出來與大家分享。如果對本文檔相關的描述信息存在疑問歡迎加入數通暢聯官方技術群（299719834）進行討論。
