轉(zhuǎn) http://blog.csdn.net/zhushuai1221/article/details/51740846

網(wǎng)上關(guān)于SQL優(yōu)化的教程很多，但是比較雜亂耍攘。近日有空整理了一下洁桌，寫出來跟大家分享一下癞谒，其中有錯誤和不足的地方啊掏，還請大家糾正補(bǔ)充。

這篇文章我花費(fèi)了大量的時間查找資料禾唁、修改效览、排版无切，希望大家閱讀之后，感覺好的話推薦給更多的人丐枉，讓更多的人看到哆键、糾正以及補(bǔ)充。

一瘦锹、百萬級數(shù)據(jù)庫優(yōu)化方案

1.對查詢進(jìn)行優(yōu)化籍嘹，要盡量避免全表掃描，首先應(yīng)考慮在 where 及 order by 涉及的列上建立索引弯院。

2.應(yīng)盡量避免在 where 子句中對字段進(jìn)行 null 值判斷辱士，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描，如：

<pre>select id from t where num is null</pre>

最好不要給數(shù)據(jù)庫留NULL听绳，盡可能的使用 NOT NULL填充數(shù)據(jù)庫.

備注颂碘、描述、評論之類的可以設(shè)置為 NULL椅挣，其他的头岔，最好不要使用NULL。

不要以為 NULL 不需要空間鼠证，比如：char(100) 型峡竣，在字段建立時，空間就固定了量九， 不管是否插入值（NULL也包含在內(nèi)）适掰，都是占用 100個字符的空間的，如果是varchar這樣的變長字段荠列， null 不占用空間攻谁。

可以在num上設(shè)置默認(rèn)值0，確保表中num列沒有null值弯予，然后這樣查詢：

<pre>select id from t where num = 0</pre>

3.應(yīng)盡量避免在 where 子句中使用 != 或 <> 操作符，否則將引擎放棄使用索引而進(jìn)行全表掃描个曙。

4.應(yīng)盡量避免在 where 子句中使用 or 來連接條件锈嫩，如果一個字段有索引，一個字段沒有索引垦搬，將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描呼寸，如：

<pre>select id from t where num=10 or Name = 'admin'</pre>

可以這樣查詢：

<pre>select id from t where num = 10
union all
select id from t where Name = 'admin'</pre>

5.in 和 not in 也要慎用，否則會導(dǎo)致全表掃描猴贰，如：

<pre>select id from t where num in(1,2,3)</pre>

對于連續(xù)的數(shù)值对雪，能用 between 就不要用 in 了：

<pre>select id from t where num between 1 and 3</pre>

很多時候用 exists 代替 in 是一個好的選擇：

<pre>select num from a where num in(select num from b)</pre>

用下面的語句替換：

<pre>select num from a where exists(select 1 from b where num=a.num)</pre>

6.下面的查詢也將導(dǎo)致全表掃描：

<pre>select id from t where name like ‘%abc%’</pre>

若要提高效率，可以考慮全文檢索米绕。

7.如果在 where 子句中使用參數(shù)瑟捣，也會導(dǎo)致全表掃描馋艺。因?yàn)镾QL只有在運(yùn)行時才會解析局部變量，但優(yōu)化程序不能將訪問計劃的選擇推遲到運(yùn)行時迈套；它必須在編譯時進(jìn)行選擇捐祠。然 而，如果在編譯時建立訪問計劃桑李，變量的值還是未知的踱蛀，因而無法作為索引選擇的輸入項(xiàng)。如下面語句將進(jìn)行全表掃描：

<pre>select id from t where num = @num</pre>

可以改為強(qiáng)制查詢使用索引：

<pre>select id from t with(index(索引名)) where num = @num</pre>

.應(yīng)盡量避免在 where 子句中對字段進(jìn)行表達(dá)式操作贵白，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描率拒。如：

<pre>select id from t where num/2 = 100</pre>

應(yīng)改為:

<pre>select id from t where num = 100*****2</pre>

9.應(yīng)盡量避免在where子句中對字段進(jìn)行函數(shù)操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描禁荒。如：

<pre>select id from t where substring(name,1,3) = ’abc’ -–name以abc開頭的id
select id from t where datediff(day,createdate,’2005-11-30′) = 0 -–‘2005-11-30’ --生成的id</pre>

應(yīng)改為:

<pre>select id from t where name like 'abc%'
select id from t where createdate >= '2005-11-30' and createdate < '2005-12-1'</pre>

10.不要在 where 子句中的“=”左邊進(jìn)行函數(shù)猬膨、算術(shù)運(yùn)算或其他表達(dá)式運(yùn)算，否則系統(tǒng)將可能無法正確使用索引圈浇。

11.在使用索引字段作為條件時寥掐，如果該索引是復(fù)合索引，那么必須使用到該索引中的第一個字段作為條件時才能保證系統(tǒng)使用該索引磷蜀，否則該索引將不會被使用召耘，并且應(yīng)盡可能的讓字段順序與索引順序相一致。

12.不要寫一些沒有意義的查詢褐隆，如需要生成一個空表結(jié)構(gòu)：

<pre>select col1,col2 into #t from t where 1=0</pre>

這類代碼不會返回任何結(jié)果集污它，但是會消耗系統(tǒng)資源的，應(yīng)改成這樣：
create table #t(…)

13.Update 語句庶弃，如果只更改1衫贬、2個字段，不要Update全部字段歇攻，否則頻繁調(diào)用會引起明顯的性能消耗固惯，同時帶來大量日志。

14.對于多張大數(shù)據(jù)量（這里幾百條就算大了）的表JOIN缴守，要先分頁再JOIN葬毫，否則邏輯讀會很高，性能很差屡穗。

15.select count(*) from table贴捡；這樣不帶任何條件的count會引起全表掃描，并且沒有任何業(yè)務(wù)意義村砂，是一定要杜絕的烂斋。

16.索引并不是越多越好，索引固然可以提高相應(yīng)的 select 的效率，但同時也降低了 insert 及 update 的效率汛骂，因?yàn)?insert 或 update 時有可能會重建索引罕模，所以怎樣建索引需要慎重考慮涧偷，視具體情況而定志膀。一個表的索引數(shù)最好不要超過6個捏悬，若太多則應(yīng)考慮一些不常使用到的列上建的索引是否有 必要叭喜。

17.應(yīng)盡可能的避免更新 clustered 索引數(shù)據(jù)列薛躬，因?yàn)?clustered 索引數(shù)據(jù)列的順序就是表記錄的物理存儲順序瘾境，一旦該列值改變將導(dǎo)致整個表記錄的順序的調(diào)整街夭，會耗費(fèi)相當(dāng)大的資源触菜。若應(yīng)用系統(tǒng)需要頻繁更新 clustered 索引數(shù)據(jù)列祸轮，那么需要考慮是否應(yīng)將該索引建為 clustered 索引兽埃。

18.盡量使用數(shù)字型字段，若只含數(shù)值信息的字段盡量不要設(shè)計為字符型适袜，這會降低查詢和連接的性能柄错，并會增加存儲開銷。這是因?yàn)橐嬖谔幚聿樵兒瓦B 接時會逐個比較字符串中每一個字符苦酱，而對于數(shù)字型而言只需要比較一次就夠了售貌。

19.盡可能的使用 varchar/nvarchar 代替 char/nchar ，因?yàn)槭紫茸冮L字段存儲空間小疫萤，可以節(jié)省存儲空間颂跨，其次對于查詢來說，在一個相對較小的字段內(nèi)搜索效率顯然要高些扯饶。

20.任何地方都不要使用 select * from t 恒削，用具體的字段列表代替“*”，不要返回用不到的任何字段尾序。

21.盡量使用表變量來代替臨時表钓丰。如果表變量包含大量數(shù)據(jù)，請注意索引非常有限（只有主鍵索引）每币。

22. 避免頻繁創(chuàng)建和刪除臨時表携丁，以減少系統(tǒng)表資源的消耗。臨時表并不是不可使用兰怠，適當(dāng)?shù)厥褂盟鼈兛梢允鼓承├谈行渭纾?dāng)需要重復(fù)引用大型表或常用表中的某個數(shù)據(jù)集時痕慢。但是，對于一次性事件涌矢， 最好使用導(dǎo)出表掖举。

23.在新建臨時表時，如果一次性插入數(shù)據(jù)量很大娜庇，那么可以使用 select into 代替 create table塔次，避免造成大量 log 方篮，以提高速度；如果數(shù)據(jù)量不大励负，為了緩和系統(tǒng)表的資源藕溅，應(yīng)先create table，然后insert继榆。

24.如果使用到了臨時表巾表，在存儲過程的最后務(wù)必將所有的臨時表顯式刪除，先 truncate table 略吨，然后 drop table 集币，這樣可以避免系統(tǒng)表的較長時間鎖定。

25.盡量避免使用游標(biāo)翠忠，因?yàn)橛螛?biāo)的效率較差鞠苟，如果游標(biāo)操作的數(shù)據(jù)超過1萬行，那么就應(yīng)該考慮改寫秽之。

26.使用基于游標(biāo)的方法或臨時表方法之前当娱，應(yīng)先尋找基于集的解決方案來解決問題，基于集的方法通常更有效考榨。

27.與臨時表一樣跨细，游標(biāo)并不是不可使用。對小型數(shù)據(jù)集使用 FAST_FORWARD 游標(biāo)通常要優(yōu)于其他逐行處理方法董虱，尤其是在必須引用幾個表才能獲得所需的數(shù)據(jù)時扼鞋。在結(jié)果集中包括“合計”的例程通常要比使用游標(biāo)執(zhí)行的速度快。如果開發(fā)時 間允許愤诱，基于游標(biāo)的方法和基于集的方法都可以嘗試一下云头，看哪一種方法的效果更好。

28.在所有的存儲過程和觸發(fā)器的開始處設(shè)置 SET NOCOUNT ON 淫半，在結(jié)束時設(shè)置 SET NOCOUNT OFF 溃槐。無需在執(zhí)行存儲過程和觸發(fā)器的每個語句后向客戶端發(fā)送 DONE_IN_PROC 消息。

29.盡量避免大事務(wù)操作科吭，提高系統(tǒng)并發(fā)能力昏滴。

30.盡量避免向客戶端返回大數(shù)據(jù)量，若數(shù)據(jù)量過大对人，應(yīng)該考慮相應(yīng)需求是否合理谣殊。

實(shí)際案例分析：拆分大的 DELETE 或INSERT 語句，批量提交SQL語句
　　如果你需要在一個在線的網(wǎng)站上去執(zhí)行一個大的 DELETE 或 INSERT 查詢牺弄，你需要非常小心姻几，要避免你的操作讓你的整個網(wǎng)站停止相應(yīng)。因?yàn)檫@兩個操作是會鎖表的，表一鎖住了蛇捌，別的操作都進(jìn)不來了抚恒。
　　Apache 會有很多的子進(jìn)程或線程。所以络拌，其工作起來相當(dāng)有效率俭驮，而我們的服務(wù)器也不希望有太多的子進(jìn)程，線程和數(shù)據(jù)庫鏈接春贸，這是極大的占服務(wù)器資源的事情混萝，尤其是內(nèi)存。
　　如果你把你的表鎖上一段時間祥诽，比如30秒鐘譬圣，那么對于一個有很高訪問量的站點(diǎn)來說，這30秒所積累的訪問進(jìn)程/線程雄坪，數(shù)據(jù)庫鏈接厘熟，打開的文件數(shù)，可能不僅僅會讓你的WEB服務(wù)崩潰维哈，還可能會讓你的整臺服務(wù)器馬上掛了绳姨。
　　所以，如果你有一個大的處理阔挠，你一定把其拆分飘庄，使用 LIMIT oracle(rownum),sqlserver(top)條件是一個好的方法。下面是一個mysql示例：

復(fù)制代碼

<pre>while(1){

//每次只做1000條

mysql_query(“delete from logs where log_date <= ’2012-11-01’ limit 1000”);

if(mysql_affected_rows() == 0){

//刪除完成购撼，退出跪削！
　　 　　break；
　　}

//每次暫停一段時間迂求，釋放表讓其他進(jìn)程/線程訪問碾盐。
usleep(50000)

}</pre>

復(fù)制代碼

二、數(shù)據(jù)庫訪問性能優(yōu)化

特別說明：

1揩局、 本文只是面對數(shù)據(jù)庫應(yīng)用開發(fā)的程序員毫玖，不適合專業(yè)DBA，DBA在數(shù)據(jù)庫性能優(yōu)化方面需要了解更多的知識凌盯；

2付枫、 本文許多示例及概念是基于Oracle數(shù)據(jù)庫描述，對于其它關(guān)系型數(shù)據(jù)庫也可以參考驰怎，但許多觀點(diǎn)不適合于KV數(shù)據(jù)庫或內(nèi)存數(shù)據(jù)庫或者是基于SSD技術(shù)的數(shù)據(jù)庫阐滩；

3、 本文未深入數(shù)據(jù)庫優(yōu)化中最核心的執(zhí)行計劃分析技術(shù)县忌。

讀者對像：

開發(fā)人員：如果你是做數(shù)據(jù)庫開發(fā)掂榔，那本文的內(nèi)容非常適合址儒，因?yàn)楸疚氖菑某绦騿T的角度來談數(shù)據(jù)庫性能優(yōu)化。

架構(gòu)師：如果你已經(jīng)是數(shù)據(jù)庫應(yīng)用的架構(gòu)師衅疙，那本文的知識你應(yīng)該清楚90%，否則你可能是一個喜歡折騰的架構(gòu)師鸳慈。

DBA（數(shù)據(jù)庫管理員）：大型數(shù)據(jù)庫優(yōu)化的知識非常復(fù)雜饱溢，本文只是從程序員的角度來談性能優(yōu)化，DBA除了需要了解這些知識外走芋，還需要深入數(shù)據(jù)庫的內(nèi)部體系架構(gòu)來解決問題绩郎。

 在網(wǎng)上有很多文章介紹數(shù)據(jù)庫優(yōu)化知識，但是大部份文章只是對某個一個方面進(jìn)行說明翁逞，而對于我們程序員來說這種介紹并不能很好的掌握優(yōu)化知識肋杖，因?yàn)楹芏嘟榻B只是對一些特定的場景優(yōu)化的，所以反而有時會產(chǎn)生誤導(dǎo)或讓程序員感覺不明白其中的奧妙而對數(shù)據(jù)庫優(yōu)化感覺很神秘挖函。

 很多程序員總是問如何學(xué)習(xí)數(shù)據(jù)庫優(yōu)化状植，有沒有好的教材之類的問題。在書店也看到了許多數(shù)據(jù)庫優(yōu)化的專業(yè)書籍怨喘，但是感覺更多是面向DBA或者是PL/SQL開發(fā)方面的知識津畸，個人感覺不太適合普通程序員。而要想做到數(shù)據(jù)庫優(yōu)化的高手必怜，不是花幾周肉拓，幾個月就能達(dá)到的，這并不是因?yàn)閿?shù)據(jù)庫優(yōu)化有多高深梳庆，而是因?yàn)橐龊脙?yōu)化一方面需要有非常好的技術(shù)功底暖途，對操作系統(tǒng)、存儲硬件網(wǎng)絡(luò)膏执、數(shù)據(jù)庫原理等方面有比較扎實(shí)的基礎(chǔ)知識驻售，另一方面是需要花大量時間對特定的數(shù)據(jù)庫進(jìn)行實(shí)踐測試與總結(jié)。

 作為一個程序員胧后，我們也許不清楚線上正式的服務(wù)器硬件配置芋浮，我們不可能像DBA那樣專業(yè)的對數(shù)據(jù)庫進(jìn)行各種實(shí)踐測試與總結(jié)，但我們都應(yīng)該非常了解我們SQL的業(yè)務(wù)邏輯壳快，我們清楚SQL中訪問表及字段的數(shù)據(jù)情況纸巷，我們其實(shí)只關(guān)心我們的SQL是否能盡快返回結(jié)果。那程序員如何利用已知的知識進(jìn)行數(shù)據(jù)庫優(yōu)化眶痰？如何能快速定位SQL性能問題并找到正確的優(yōu)化方向瘤旨？

面對這些問題，筆者總結(jié)了一些面向程序員的基本優(yōu)化法則竖伯，本文將結(jié)合實(shí)例來坦述數(shù)據(jù)庫開發(fā)的優(yōu)化知識存哲。

 要正確的優(yōu)化SQL因宇，我們需要快速定位能性的瓶頸點(diǎn)，也就是說快速找到我們SQL主要的開銷在哪里祟偷？而大多數(shù)情況性能最慢的設(shè)備會是瓶頸點(diǎn)察滑，如下載時網(wǎng)絡(luò)速度可能會是瓶頸點(diǎn)，本地復(fù)制文件時硬盤可能會是瓶頸點(diǎn)修肠，為什么這些一般的工作我們能快速確認(rèn)瓶頸點(diǎn)呢贺辰，因?yàn)槲覀儗@些慢速設(shè)備的性能數(shù)據(jù)有一些基本的認(rèn)識，如網(wǎng)絡(luò)帶寬是2Mbps嵌施，硬盤是每分鐘7200轉(zhuǎn)等等饲化。因此，為了快速找到SQL的性能瓶頸點(diǎn)吗伤，我們也需要了解我們計算機(jī)系統(tǒng)的硬件基本性能指標(biāo)吃靠，下圖展示的當(dāng)前主流計算機(jī)性能指標(biāo)數(shù)據(jù)。

[圖片上傳失敗...(image-459960-1520414551966)]

從圖上可以看到基本上每種設(shè)備都有兩個指標(biāo)：

延時（響應(yīng)時間）：表示硬件的突發(fā)處理能力足淆；

帶寬（吞吐量）：代表硬件持續(xù)處理能力巢块。

從上圖可以看出，計算機(jī)系統(tǒng)硬件性能從高到代依次為：

CPU——Cache(L1-L2-L3)——內(nèi)存——SSD硬盤——網(wǎng)絡(luò)——硬盤

由于SSD硬盤還處于快速發(fā)展階段巧号，所以本文的內(nèi)容不涉及SSD相關(guān)應(yīng)用系統(tǒng)夕冲。

根據(jù)數(shù)據(jù)庫知識，我們可以列出每種硬件主要的工作內(nèi)容：

CPU及內(nèi)存：緩存數(shù)據(jù)訪問裂逐、比較歹鱼、排序、事務(wù)檢測卜高、SQL解析弥姻、函數(shù)或邏輯運(yùn)算；

網(wǎng)絡(luò)：結(jié)果數(shù)據(jù)傳輸掺涛、SQL請求庭敦、遠(yuǎn)程數(shù)據(jù)庫訪問（dblink）；

硬盤：數(shù)據(jù)訪問薪缆、數(shù)據(jù)寫入秧廉、日志記錄、大數(shù)據(jù)量排序拣帽、大表連接疼电。

根據(jù)當(dāng)前計算機(jī)硬件的基本性能指標(biāo)及其在數(shù)據(jù)庫中主要操作內(nèi)容，可以整理出如下圖所示的性能基本優(yōu)化法則：

[圖片上傳失敗...(image-c710de-1520414551966)]

這個優(yōu)化法則歸納為5個層次：

1减拭、 減少數(shù)據(jù)訪問（減少磁盤訪問）

2蔽豺、 返回更少數(shù)據(jù)（減少網(wǎng)絡(luò)傳輸或磁盤訪問）

3、 減少交互次數(shù)（減少網(wǎng)絡(luò)傳輸）

4拧粪、 減少服務(wù)器CPU開銷（減少CPU及內(nèi)存開銷）

5修陡、 利用更多資源（增加資源）

由于每一層優(yōu)化法則都是解決其對應(yīng)硬件的性能問題沧侥，所以帶來的性能提升比例也不一樣。傳統(tǒng)數(shù)據(jù)庫系統(tǒng)設(shè)計是也是盡可能對低速設(shè)備提供優(yōu)化方法魄鸦，因此針對低速設(shè)備問題的可優(yōu)化手段也更多宴杀，優(yōu)化成本也更低。我們?nèi)魏我粋€SQL的性能優(yōu)化都應(yīng)該按這個規(guī)則由上到下來診斷問題并提出解決方案拾因，而不應(yīng)該首先想到的是增加資源解決問題婴氮。

以下是每個優(yōu)化法則層級對應(yīng)優(yōu)化效果及成本經(jīng)驗(yàn)參考：

|

優(yōu)化法則

|

性能提升效果

|

優(yōu)化成本

|
|

減少數(shù)據(jù)訪問

|

1~1000

|

低

|
|

返回更少數(shù)據(jù)

|

1~100

|

低

|
|

減少交互次數(shù)

|

1~20

|

低

|
|

減少服務(wù)器CPU開銷

|

1~5

|

低

|
|

利用更多資源

|

@~10

|

高

|

接下來，我們針對5種優(yōu)化法則列舉常用的優(yōu)化手段并結(jié)合實(shí)例分析盾致。

接下來，我們針對5種優(yōu)化法則列舉常用的優(yōu)化手段并結(jié)合實(shí)例分析荣暮。

二庭惜、oracle數(shù)據(jù)庫兩個基本概念

數(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ù)塊壁熄；

[圖片上傳失敗...(image-8088f6-1520414551966)]

ROWID是每條記錄在數(shù)據(jù)庫中的唯一標(biāo)識，通過ROWID可以直接定位記錄到對應(yīng)的文件號及數(shù)據(jù)塊位置碳竟。ROWID內(nèi)容包括文件號草丧、對像號、數(shù)據(jù)塊號莹桅、記錄槽號昌执，如下圖所示：

[圖片上傳失敗...(image-100a03-1520414551946)]

三、數(shù)據(jù)庫訪問優(yōu)化法則詳解

減少數(shù)據(jù)訪問

創(chuàng)建并使用正確的索引

數(shù)據(jù)庫索引的原理非常簡單诈泼，但在復(fù)雜的表中真正能正確使用索引的人很少，即使是專業(yè)的DBA也不一定能完全做到最優(yōu)铐达。

索引會大大增加表記錄的DML(INSERT,UPDATE,DELETE)開銷委粉，正確的索引可以讓性能提升100，1000倍以上娶桦，不合理的索引也可能會讓性能下降100倍贾节，因此在一個表中創(chuàng)建什么樣的索引需要平衡各種業(yè)務(wù)需求汁汗。

索引常見問題：

索引有哪些種類？

常見的索引有B-TREE索引栗涂、位圖索引知牌、全文索引，位圖索引一般用于數(shù)據(jù)倉庫應(yīng)用斤程，全文索引由于使用較少角寸，這里不深入介紹。B-TREE索引包括很多擴(kuò)展類型忿墅，如組合索引扁藕、反向索引、函數(shù)索引等等疚脐，以下是B-TREE索引的簡單介紹：

B-TREE索引也稱為平衡樹索引(Balance Tree)亿柑，它是一種按字段排好序的樹形目錄結(jié)構(gòu)，主要用于提升查詢性能和唯一約束支持棍弄。B-TREE索引的內(nèi)容包括根節(jié)點(diǎn)望薄、分支節(jié)點(diǎn)、葉子節(jié)點(diǎn)呼畸。

葉子節(jié)點(diǎn)內(nèi)容：索引字段內(nèi)容+表記錄ROWID

根節(jié)點(diǎn)痕支，分支節(jié)點(diǎn)內(nèi)容：當(dāng)一個數(shù)據(jù)塊中不能放下所有索引字段數(shù)據(jù)時，就會形成樹形的根節(jié)點(diǎn)或分支節(jié)點(diǎn)蛮原，根節(jié)點(diǎn)與分支節(jié)點(diǎn)保存了索引樹的順序及各層級間的引用關(guān)系卧须。

     一個普通的BTREE索引結(jié)構(gòu)示意圖如下所示：

[圖片上傳失敗...(image-be43f4-1520414551990)]

如果我們把一個表的內(nèi)容認(rèn)為是一本字典，那索引就相當(dāng)于字典的目錄儒陨，如下圖所示：

[圖片上傳失敗...(image-65dc7a-1520414551946)]

[圖片上傳失敗...(image-4ff7eb-1520414551946)]

圖中是一個字典按部首+筆劃數(shù)的目錄故慈，相當(dāng)于給字典建了一個按部首+筆劃的組合索引。

一個表中可以建多個索引框全，就如一本字典可以建多個目錄一樣（按拼音察绷、筆劃、部首等等）津辩。

一個索引也可以由多個字段組成拆撼，稱為組合索引，如上圖就是一個按部首+筆劃的組合目錄喘沿。

SQL什么條件會使用索引闸度？

當(dāng)字段上建有索引時，通常以下情況會使用索引：

INDEX_COLUMN = ?

INDEX_COLUMN > ?

INDEX_COLUMN >= ?

INDEX_COLUMN < ?

INDEX_COLUMN <= ?

INDEX_COLUMN between ? and ?

INDEX_COLUMN in (?,?,...,?)

INDEX_COLUMN like ?||'%'（后導(dǎo)模糊查詢）

T1. INDEX_COLUMN=T2. COLUMN1（兩個表通過索引字段關(guān)聯(lián)）

SQL什么條件不會使用索引蚜印？

|

查詢條件

|

不能使用索引原因

|
|

INDEX_COLUMN <> ?

INDEX_COLUMN not in (?,?,...,?)

|

不等于操作不能使用索引

|
|

function(INDEX_COLUMN) = ?

INDEX_COLUMN + 1 = ?

INDEX_COLUMN || 'a' = ?

|

經(jīng)過普通運(yùn)算或函數(shù)運(yùn)算后的索引字段不能使用索引

|
|

INDEX_COLUMN like '%'||?

INDEX_COLUMN like '%'||?||'%'

|

含前導(dǎo)模糊查詢的Like語法不能使用索引

|
|

INDEX_COLUMN is null

|

B-TREE索引里不保存字段為NULL值記錄莺禁，因此IS NULL不能使用索引

|
|

NUMBER_INDEX_COLUMN='12345'

CHAR_INDEX_COLUMN=12345

|

Oracle在做數(shù)值比較時需要將兩邊的數(shù)據(jù)轉(zhuǎn)換成同一種數(shù)據(jù)類型，如果兩邊數(shù)據(jù)類型不同時會對字段值隱式轉(zhuǎn)換窄赋，相當(dāng)于加了一層函數(shù)處理哟冬，所以不能使用索引楼熄。

|
|

a.INDEX_COLUMN=a.COLUMN_1

|

給索引查詢的值應(yīng)是已知數(shù)據(jù)，不能是未知字段值浩峡。

|
|

注：

經(jīng)過函數(shù)運(yùn)算字段的字段要使用可以使用函數(shù)索引可岂，這種需求建議與DBA溝通。

有時候我們會使用多個字段的組合索引翰灾，如果查詢條件中第一個字段不能使用索引缕粹，那整個查詢也不能使用索引

如：我們company表建了一個id+name的組合索引，以下SQL是不能使用索引的

Select * from company where name=?

Oracle9i后引入了一種index skip scan的索引方式來解決類似的問題纸淮，但是通過index skip scan提高性能的條件比較特殊平斩，使用不好反而性能會更差。

|

我們一般在什么字段上建索引咽块？

這是一個非常復(fù)雜的話題绘面，需要對業(yè)務(wù)及數(shù)據(jù)充分分析后再能得出結(jié)果。主鍵及外鍵通常都要有索引糜芳，其它需要建索引的字段應(yīng)滿足以下條件：

1、字段出現(xiàn)在查詢條件中魄衅，并且查詢條件可以使用索引峭竣；

2、語句執(zhí)行頻率高晃虫，一天會有幾千次以上皆撩；

3、通過字段條件可篩選的記錄集很小哲银，那數(shù)據(jù)篩選比例是多少才適合扛吞？

這個沒有固定值，需要根據(jù)表數(shù)據(jù)量來評估荆责，以下是經(jīng)驗(yàn)公式滥比，可用于快速評估：

小表(記錄數(shù)小于10000行的表)：篩選比例<10%；

大表：(篩選返回記錄數(shù))<(表總記錄數(shù)單條記錄長度)/10000/16*

** 單條記錄長度≈字段平均內(nèi)容長度之和+字段數(shù)2*

以下是一些字段是否需要建B-TREE索引的經(jīng)驗(yàn)分類：

| |

字段類型

|

常見字段名

|
|

需要建索引的字段

|

主鍵

|

ID,PK

|
|

外鍵

|

PRODUCT_ID,COMPANY_ID,MEMBER_ID,ORDER_ID,TRADE_ID,PAY_ID

|
|

有對像或身份標(biāo)識意義字段

|

HASH_CODE,USERNAME,IDCARD_NO,EMAIL,TEL_NO,IM_NO

|
|

索引慎用字段,需要進(jìn)行數(shù)據(jù)分布及使用場景詳細(xì)評估

|

日期

|

GMT_CREATE,GMT_MODIFIED

|
|

年月

|

YEAR,MONTH

|
|

狀態(tài)標(biāo)志

|

PRODUCT_STATUS,ORDER_STATUS,IS_DELETE,VIP_FLAG

|
|

類型

|

ORDER_TYPE,IMAGE_TYPE,GENDER,CURRENCY_TYPE

|
|

區(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ī)則判斷盲泛，復(fù)雜的SQL不好辦，判斷SQL的響應(yīng)時間是一種策略键耕，但是這會受到數(shù)據(jù)量寺滚、主機(jī)負(fù)載及緩存等因素的影響，有時數(shù)據(jù)全在緩存里屈雄，可能全表訪問的時間比索引訪問時間還少村视。要準(zhǔn)確知道索引是否正確使用，需要到數(shù)據(jù)庫中查看SQL真實(shí)的執(zhí)行計劃酒奶，這個話題比較復(fù)雜蚁孔，詳見SQL執(zhí)行計劃專題介紹奶赔。

索引對DML(INSERT,UPDATE,DELETE)附加的開銷有多少？

這個沒有固定的比例勒虾，與每個表記錄的大小及索引字段大小密切相關(guān)纺阔，以下是一個普通表測試數(shù)據(jù)，僅供參考：

索引對于Insert性能降低56%

索引對于Update性能降低47%

索引對于Delete性能降低29%

因此對于寫IO壓力比較大的系統(tǒng)修然，表的索引需要仔細(xì)評估必要性笛钝，另外索引也會占用一定的存儲空間。

1.2愕宋、只通過索引訪問數(shù)據(jù)

有些時候玻靡，我們只是訪問表中的幾個字段，并且字段內(nèi)容較少中贝，我們可以為這幾個字段單獨(dú)建立一個組合索引囤捻，這樣就可以直接只通過訪問索引就能得到數(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);

有了這個組合索引后绣否，SQL就可以直接通過my_comb_index索引返回數(shù)據(jù)誊涯，不需要訪問company表。

還是拿字典舉例：有一個需求蒜撮，需要查詢一本漢語字典中所有漢字的個數(shù)暴构，如果我們的字典沒有目錄索引，那我們只能從字典內(nèi)容里一個一個字計數(shù)段磨，最后返回結(jié)果取逾。如果我們有一個拼音目錄，那就可以只訪問拼音目錄的漢字進(jìn)行計數(shù)苹支。如果一本字典有1000頁砾隅，拼音目錄有20頁，那我們的數(shù)據(jù)訪問成本相當(dāng)于全表訪問的50分之一债蜜。

切記琉用，性能優(yōu)化是無止境的，當(dāng)性能可以滿足需求時即可策幼，不要過度優(yōu)化邑时。在實(shí)際數(shù)據(jù)庫中我們不可能把每個SQL請求的字段都建在索引里，所以這種只通過索引訪問數(shù)據(jù)的方法一般只用于核心應(yīng)用特姐，也就是那種對核心表訪問量最高且查詢字段數(shù)據(jù)量很少的查詢晶丘。

1.3、優(yōu)化SQL執(zhí)行計劃

SQL執(zhí)行計劃是關(guān)系型數(shù)據(jù)庫最核心的技術(shù)之一，它表示SQL執(zhí)行時的數(shù)據(jù)訪問算法浅浮。由于業(yè)務(wù)需求越來越復(fù)雜沫浆，表數(shù)據(jù)量也越來越大，程序員越來越懶惰滚秩，SQL也需要支持非常復(fù)雜的業(yè)務(wù)邏輯专执，但SQL的性能還需要提高，因此郁油，優(yōu)秀的關(guān)系型數(shù)據(jù)庫除了需要支持復(fù)雜的SQL語法及更多函數(shù)外萍虽，還需要有一套優(yōu)秀的算法庫來提高SQL性能厘灼。

目前ORACLE有SQL執(zhí)行計劃的算法約300種躁锁，而且一直在增加裁蚁，所以SQL執(zhí)行計劃是一個非常復(fù)雜的課題，一個普通DBA能掌握50種就很不錯了案站，就算是資深DBA也不可能把每個執(zhí)行計劃的算法描述清楚躬审。雖然有這么多種算法，但并不表示我們無法優(yōu)化執(zhí)行計劃蟆盐，因?yàn)槲覀兂Ｓ玫腟QL執(zhí)行計劃算法也就十幾個承边，如果一個程序員能把這十幾個算法搞清楚，那就掌握了80%的SQL執(zhí)行計劃調(diào)優(yōu)知識石挂。

由于篇幅的原因博助，SQL執(zhí)行計劃需要專題介紹，在這里就不多說了誊稚。

2翔始、返回更少的數(shù)據(jù)

2.1罗心、數(shù)據(jù)分頁處理

一般數(shù)據(jù)分頁方式有：

2.1.1里伯、客戶端(應(yīng)用程序或?yàn)g覽器)分頁

將數(shù)據(jù)從應(yīng)用服務(wù)器全部下載到本地應(yīng)用程序或?yàn)g覽器，在應(yīng)用程序或?yàn)g覽器內(nèi)部通過本地代碼進(jìn)行分頁處理

優(yōu)點(diǎn)：編碼簡單渤闷，減少客戶端與應(yīng)用服務(wù)器網(wǎng)絡(luò)交互次數(shù)

缺點(diǎn)：首次交互時間長疾瓮，占用客戶端內(nèi)存

適應(yīng)場景：客戶端與應(yīng)用服務(wù)器網(wǎng)絡(luò)延時較大，但要求后續(xù)操作流暢飒箭，如手機(jī)GPRS狼电，超遠(yuǎn)程訪問（跨國）等等。

2.1.2弦蹂、應(yīng)用服務(wù)器分頁

將數(shù)據(jù)從數(shù)據(jù)庫服務(wù)器全部下載到應(yīng)用服務(wù)器肩碟，在應(yīng)用服務(wù)器內(nèi)部再進(jìn)行數(shù)據(jù)篩選。以下是一個應(yīng)用服務(wù)器端Java程序分頁的示例：

List list=executeQuery(“select * from employee order by id”);

Int count= list.size();

List subList= list.subList(10, 20);

優(yōu)點(diǎn)：編碼簡單凸椿，只需要一次SQL交互削祈，總數(shù)據(jù)與分頁數(shù)據(jù)差不多時性能較好。

缺點(diǎn)：總數(shù)據(jù)量較多時性能較差。

適應(yīng)場景：數(shù)據(jù)庫系統(tǒng)不支持分頁處理髓抑，數(shù)據(jù)量較小并且可控咙崎。

2.1.3、數(shù)據(jù)庫SQL分頁

采用數(shù)據(jù)庫SQL分頁需要兩次SQL完成

一個SQL計算總數(shù)量

一個SQL返回分頁后的數(shù)據(jù)

優(yōu)點(diǎn)：性能好

缺點(diǎn)：編碼復(fù)雜吨拍，各種數(shù)據(jù)庫語法不同褪猛，需要兩次SQL交互。

oracle數(shù)據(jù)庫一般采用rownum來進(jìn)行分頁羹饰，常用分頁語法有如下兩種：

直接通過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;

數(shù)據(jù)訪問開銷=索引IO+索引全部記錄結(jié)果對應(yīng)的表數(shù)據(jù)IO

采用rowid分頁語法

優(yōu)化原理是通過純索引找出分頁記錄的ROWID伊滋，再通過ROWID回表返回數(shù)據(jù)，要求內(nèi)層查詢和排序字段全在索引里严里。

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;

數(shù)據(jù)訪問開銷=索引IO+索引分頁結(jié)果對應(yīng)的表數(shù)據(jù)IO

實(shí)例：

一個公司產(chǎn)品有1000條記錄新啼，要分頁取其中20個產(chǎn)品，假設(shè)訪問公司索引需要50個IO刹碾，2條記錄需要1個表數(shù)據(jù)IO燥撞。

那么按第一種ROWNUM分頁寫法，需要550(50+1000/2)個IO迷帜，按第二種ROWID分頁寫法物舒，只需要60個IO(50+20/2);

2.2、只返回需要的字段

通過去除不必要的返回字段可以提高性能戏锹，例：

調(diào)整前：select * from product where company_id=?;

調(diào)整后：select id,name from product where company_id=?;

優(yōu)點(diǎn)：

1冠胯、減少數(shù)據(jù)在網(wǎng)絡(luò)上傳輸開銷

2、減少服務(wù)器數(shù)據(jù)處理開銷

3锦针、減少客戶端內(nèi)存占用

4荠察、字段變更時提前發(fā)現(xiàn)問題，減少程序BUG

5奈搜、如果訪問的所有字段剛好在一個索引里面悉盆，則可以使用純索引訪問提高性能。

缺點(diǎn)：增加編碼工作量

由于會增加一些編碼工作量馋吗，所以一般需求通過開發(fā)規(guī)范來要求程序員這么做焕盟，否則等項(xiàng)目上線后再整改工作量更大。

如果你的查詢表中有大字段或內(nèi)容較多的字段宏粤，如備注信息脚翘、文件內(nèi)容等等，那在查詢表時一定要注意這方面的問題绍哎，否則可能會帶來嚴(yán)重的性能問題来农。如果表經(jīng)常要查詢并且請求大內(nèi)容字段的概率很低，我們可以采用分表處理崇堰，將一個大表分拆成兩個一對一的關(guān)系表沃于，將不常用的大內(nèi)容字段放在一張單獨(dú)的表中。如一張存儲上傳文件的表：

T_FILE（ID,FILE_NAME,FILE_SIZE,FILE_TYPE,FILE_CONTENT）

我們可以分拆成兩張一對一的關(guān)系表：

T_FILE（ID,FILE_NAME,FILE_SIZE,FILE_TYPE）

T_FILECONTENT（ID, FILE_CONTENT）

     通過這種分拆，可以大大提少T_FILE表的單條記錄及總大小揽涮，這樣在查詢T_FILE時性能會更好抠藕，當(dāng)需要查詢FILE_CONTENT字段內(nèi)容時再訪問T_FILECONTENT表。

3蒋困、減少交互次數(shù)

3.1盾似、batch DML

數(shù)據(jù)庫訪問框架一般都提供了批量提交的接口，jdbc支持batch的提交處理方法雪标，當(dāng)你一次性要往一個表中插入1000萬條數(shù)據(jù)時零院，如果采用普通的executeUpdate處理，那么和服務(wù)器交互次數(shù)為1000萬次村刨，按每秒鐘可以向數(shù)據(jù)庫服務(wù)器提交10000次估算告抄，要完成所有工作需要1000秒。如果采用批量提交模式嵌牺，1000條提交一次打洼，那么和服務(wù)器交互次數(shù)為1萬次，交互次數(shù)大大減少逆粹。采用batch操作一般不會減少很多數(shù)據(jù)庫服務(wù)器的物理IO募疮，但是會大大減少客戶端與服務(wù)端的交互次數(shù)，從而減少了多次發(fā)起的網(wǎng)絡(luò)延時開銷僻弹，同時也會降低數(shù)據(jù)庫的CPU開銷阿浓。

假設(shè)要向一個普通表插入1000萬數(shù)據(jù)，每條記錄大小為1K字節(jié)蹋绽，表上沒有任何索引芭毙，客戶端與數(shù)據(jù)庫服務(wù)器網(wǎng)絡(luò)是100Mbps，以下是根據(jù)現(xiàn)在一般計算機(jī)能力估算的各種batch大小性能對比值：

|

單位：ms

|

No batch

|

Batch=10

|

Batch=100

|

Batch=1000

|

Batch=10000

|
|

服務(wù)器事務(wù)處理時間

|

0.1

|

0.1

|

0.1

|

0.1

|

0.1

|
|

服務(wù)器IO處理時間

|

0.02

|

0.2

|

2

|

20

|

200

|
|

網(wǎng)絡(luò)交互發(fā)起時間

|

0.1

|

0.1

|

0.1

|

0.1

|

0.1

|
|

網(wǎng)絡(luò)數(shù)據(jù)傳輸時間

|

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倍性能退敦，一般根據(jù)主鍵的Update或Delete操作也可能提高2-3倍性能，但不如Insert明顯鹊奖，因?yàn)閁pdate及Delete操作可能有比較大的開銷在物理IO訪問苛聘。以上僅是理論計算值涂炎，實(shí)際情況需要根據(jù)具體環(huán)境測量忠聚。

3.2、In List

很多時候我們需要按一些ID查詢數(shù)據(jù)庫記錄唱捣，我們可以采用一個ID一個請求發(fā)給數(shù)據(jù)庫两蟀，如下所示：

for :var in ids[] do begin

select * from 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個值膛堤。

另外當(dāng)前數(shù)據(jù)庫一般都是采用基于成本的優(yōu)化規(guī)則手趣，當(dāng)IN數(shù)量達(dá)到一定值時有可能改變SQL執(zhí)行計劃，從索引訪問變成全表訪問肥荔，這將使性能急劇變化绿渣。隨著SQL中IN的里面的值個數(shù)增加，SQL的執(zhí)行計劃會更復(fù)雜燕耿，占用的內(nèi)存將會變大中符，這將會增加服務(wù)器CPU及內(nèi)存成本。

評估在IN里面一次放多少個值還需要考慮應(yīng)用服務(wù)器本地內(nèi)存的開銷誉帅，有并發(fā)訪問時要計算本地數(shù)據(jù)使用周期內(nèi)的并發(fā)上限淀散，否則可能會導(dǎo)致內(nèi)存溢出。

綜合考慮蚜锨，一般IN里面的值個數(shù)超過20個以后性能基本沒什么太大變化吧凉，也特別說明不要超過100，超過后可能會引起執(zhí)行計劃的不穩(wěn)定性及增加數(shù)據(jù)庫CPU及內(nèi)存成本踏志，這個需要專業(yè)DBA評估阀捅。

3.3、設(shè)置Fetch Size

當(dāng)我們采用select從數(shù)據(jù)庫查詢數(shù)據(jù)時针余，數(shù)據(jù)默認(rèn)并不是一條一條返回給客戶端的饲鄙，也不是一次全部返回客戶端的，而是根據(jù)客戶端fetch_size參數(shù)處理圆雁，每次只返回fetch_size條記錄忍级，當(dāng)客戶端游標(biāo)遍歷到尾部時再從服務(wù)端取數(shù)據(jù)，直到最后全部傳送完成伪朽。所以如果我們要從服務(wù)端一次取大量數(shù)據(jù)時轴咱，可以加大fetch_size，這樣可以減少結(jié)果數(shù)據(jù)傳輸?shù)慕换ゴ螖?shù)及服務(wù)器數(shù)據(jù)準(zhǔn)備時間烈涮，提高性能朴肺。

以下是jdbc測試的代碼，采用本地數(shù)據(jù)庫坚洽，表緩存在數(shù)據(jù)庫CACHE中戈稿，因此沒有網(wǎng)絡(luò)連接及磁盤IO開銷，客戶端只遍歷游標(biāo)讶舰，不做任何處理鞍盗，這樣更能體現(xiàn)fetch參數(shù)的影響：

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字節(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

|
|

128

|

2.44

|
|

256

|

2.765

|
|

512

|

3.075

|
|

1024

|

2.862

|
|

2048

|

2.722

|
|

4096

|

2.681

|
|

8192

|

2.715

|

[圖片上傳失敗...(image-1cc25e-1520414551988)]

Oracle jdbc fetchsize默認(rèn)值為10般甲，由上測試可以看出fetchsize對性能影響還是比較大的肋乍，但是當(dāng)fetchsize大于100時就基本上沒有影響了。fetchsize并不會存在一個最優(yōu)的固定值敷存，因?yàn)檎w性能與記錄集大小及硬件平臺有關(guān)住拭。根據(jù)測試結(jié)果建議當(dāng)一次性要取大量數(shù)據(jù)時這個值設(shè)置為100左右，不要小于40历帚。注意滔岳，fetchsize不能設(shè)置太大，如果一次取出的數(shù)據(jù)大于JVM的內(nèi)存會導(dǎo)致內(nèi)存溢出挽牢，所以建議不要超過1000谱煤，太大了也沒什么性能提高，反而可能會增加內(nèi)存溢出的危險禽拔。

注：圖中fetchsize在128以后會有一些小的波動刘离，這并不是測試誤差，而是由于resultset填充到具體對像時間不同的原因睹栖，由于resultset已經(jīng)到本地內(nèi)存里了硫惕，所以估計是由于CPU的L1,L2 Cache命中率變化造成，由于變化不大野来，所以筆者也未深入分析原因恼除。

iBatis的SqlMapping配置文件可以對每個SQL語句指定fetchsize大小，如下所示：

<select id="getAllProduct" resultMap="HashMap"** fetchSize="1000"**>

select * from employee

</select>

3.4曼氛、使用存儲過程

大型數(shù)據(jù)庫一般都支持存儲過程豁辉，合理的利用存儲過程也可以提高系統(tǒng)性能。如你有一個業(yè)務(wù)需要將A表的數(shù)據(jù)做一些加工然后更新到B表中舀患，但是又不可能一條SQL完成徽级，這時你需要如下3步操作：

a：將A表數(shù)據(jù)全部取出到客戶端；

b：計算出要更新的數(shù)據(jù)聊浅；

c：將計算結(jié)果更新到B表餐抢。

如果采用存儲過程你可以將整個業(yè)務(wù)邏輯封裝在存儲過程里，然后在客戶端直接調(diào)用存儲過程處理低匙，這樣可以減少網(wǎng)絡(luò)交互的成本旷痕。

當(dāng)然，存儲過程也并不是十全十美努咐，存儲過程有以下缺點(diǎn)：

a苦蒿、不可移植性殴胧，每種數(shù)據(jù)庫的內(nèi)部編程語法都不太相同渗稍，當(dāng)你的系統(tǒng)需要兼容多種數(shù)據(jù)庫時最好不要用存儲過程佩迟。

b、學(xué)習(xí)成本高竿屹，DBA一般都擅長寫存儲過程报强，但并不是每個程序員都能寫好存儲過程，除非你的團(tuán)隊有較多的開發(fā)人員熟悉寫存儲過程拱燃，否則后期系統(tǒng)維護(hù)會產(chǎn)生問題秉溉。

c、業(yè)務(wù)邏輯多處存在碗誉，采用存儲過程后也就意味著你的系統(tǒng)有一些業(yè)務(wù)邏輯不是在應(yīng)用程序里處理召嘶，這種架構(gòu)會增加一些系統(tǒng)維護(hù)和調(diào)試成本。

d哮缺、存儲過程和常用應(yīng)用程序語言不一樣弄跌，它支持的函數(shù)及語法有可能不能滿足需求，有些邏輯就只能通過應(yīng)用程序處理尝苇。

e铛只、如果存儲過程中有復(fù)雜運(yùn)算的話，會增加一些數(shù)據(jù)庫服務(wù)端的處理成本糠溜，對于集中式數(shù)據(jù)庫可能會導(dǎo)致系統(tǒng)可擴(kuò)展性問題淳玩。

f、為了提高性能非竿，數(shù)據(jù)庫會把存儲過程代碼編譯成中間運(yùn)行代碼(類似于java的class文件)蜕着，所以更像靜態(tài)語言。當(dāng)存儲過程引用的對像(表红柱、視圖等等)結(jié)構(gòu)改變后侮东，存儲過程需要重新編譯才能生效，在24*7高并發(fā)應(yīng)用場景豹芯，一般都是在線變更結(jié)構(gòu)的悄雅，所以在變更的瞬間要同時編譯存儲過程，這可能會導(dǎo)致數(shù)據(jù)庫瞬間壓力上升引起故障(Oracle數(shù)據(jù)庫就存在這樣的問題)铁蹈。

個人觀點(diǎn)：普通業(yè)務(wù)邏輯盡量不要使用存儲過程宽闲，定時性的ETL任務(wù)或報表統(tǒng)計函數(shù)可以根據(jù)團(tuán)隊資源情況采用存儲過程處理。

3.5握牧、優(yōu)化業(yè)務(wù)邏輯

要通過優(yōu)化業(yè)務(wù)邏輯來提高性能是比較困難的容诬，這需要程序員對所訪問的數(shù)據(jù)及業(yè)務(wù)流程非常清楚。

舉一個案例：

某移動公司推出優(yōu)惠套參沿腰，活動對像為VIP會員并且2010年1览徒，2，3月平均話費(fèi)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

執(zhí)行套參();

end;

如果我們修改業(yè)務(wù)邏輯為:

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

執(zhí)行套參();

end;

end;

通過這樣可以減少一些判斷vip_flag的開銷习蓬，平均話費(fèi)20元以下的用戶就不需要再檢測是否VIP了纽什。

如果程序員分析業(yè)務(wù)，VIP會員比例為1%躲叼，平均話費(fèi)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

執(zhí)行套參();

end;

end;

這樣就只有1%的VIP會員才會做檢測平均話費(fèi)，最終大大減少了SQL的交互次數(shù)枫慷。

以上只是一個簡單的示例让蕾，實(shí)際的業(yè)務(wù)總是比這復(fù)雜得多，所以一般只是高級程序員更容易做出優(yōu)化的邏輯或听，但是我們需要有這樣一種成本優(yōu)化的意識探孝。

3.6、使用ResultSet游標(biāo)處理記錄

現(xiàn)在大部分Java框架都是通過jdbc從數(shù)據(jù)庫取出數(shù)據(jù)誉裆，然后裝載到一個list里再處理再姑，list里可能是業(yè)務(wù)Object，也可能是hashmap找御。

由于JVM內(nèi)存一般都小于4G元镀，所以不可能一次通過sql把大量數(shù)據(jù)裝載到list里。為了完成功能霎桅，很多程序員喜歡采用分頁的方法處理栖疑，如一次從數(shù)據(jù)庫取1000條記錄，通過多次循環(huán)搞定滔驶，保證不會引起JVM Out of memory問題遇革。

以下是實(shí)現(xiàn)此功能的代碼示例，t_employee表有10萬條記錄揭糕，設(shè)置分頁大小為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();

}

以上代碼實(shí)際執(zhí)行時間為6.516秒

很多持久層框架為了盡量讓程序員使用方便萝快，封裝了jdbc通過statement執(zhí)行數(shù)據(jù)返回到resultset的細(xì)節(jié)，導(dǎo)致程序員會想采用分頁的方式處理問題著角。實(shí)際上如果我們采用jdbc原始的resultset游標(biāo)處理記錄揪漩，在resultset循環(huán)讀取的過程中處理記錄，這樣就可以一次從數(shù)據(jù)庫取出所有記錄吏口。顯著提高性能奄容。

這里需要注意的是，采用resultset游標(biāo)處理記錄時产徊，應(yīng)該將游標(biāo)的打開方式設(shè)置為FORWARD_READONLY模式(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY)昂勒，否則會把結(jié)果緩存在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);

     }

}

調(diào)整后的代碼實(shí)際執(zhí)行時間為3.156秒

從測試結(jié)果可以看出性能提高了1倍多戈盈，如果采用分頁模式數(shù)據(jù)庫每次還需發(fā)生磁盤IO的話那性能可以提高更多。

iBatis等持久層框架考慮到會有這種需求谆刨，所以也有相應(yīng)的解決方案塘娶，在iBatis里我們不能采用queryForList的方法归斤，而應(yīng)用該采用queryWithRowHandler加回調(diào)事件的方式處理，如下所示：

MyRowHandler myrh=new MyRowHandler();

sqlmap.queryWithRowHandler("getAllEmployee", myrh);

class MyRowHandler implements RowHandler {

**public void** handleRow(Object o) {

   //todo something

}

}

iBatis的queryWithRowHandler很好的封裝了resultset遍歷的事件處理血柳，效果及性能與resultset遍歷一樣官册，也不會產(chǎn)生JVM內(nèi)存溢出生兆。

4难捌、減少數(shù)據(jù)庫服務(wù)器CPU運(yùn)算

4.1、使用綁定變量

綁定變量是指SQL中對變化的值采用變量參數(shù)的形式提交鸦难，而不是在SQL中直接拼寫對應(yīng)的值根吁。

非綁定變量寫法：Select * from employee where id=1234567

綁定變量寫法：

Select * from employee where id=?

Preparestatement.setInt(1,1234567)

Java中Preparestatement就是為處理綁定變量提供的對像，綁定變量有以下優(yōu)點(diǎn)：

1合蔽、防止SQL注入

2击敌、提高SQL可讀性

3、提高SQL解析性能拴事，不使用綁定變更我們一般稱為硬解析沃斤，使用綁定變量我們稱為軟解析。

第1和第2點(diǎn)很好理解刃宵，做編碼的人應(yīng)該都清楚衡瓶，這里不詳細(xì)說明。關(guān)于第3點(diǎn)牲证，到底能提高多少性能呢哮针，下面舉一個例子說明：

假設(shè)有這個這樣的一個數(shù)據(jù)庫主機(jī)：

2個4核CPU

100塊磁盤，每個磁盤支持IOPS為160

業(yè)務(wù)應(yīng)用的SQL如下：

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)驗(yàn)值）

SQL軟解析CPU消耗：0.02ms（常用經(jīng)驗(yàn)值）

假設(shè)CPU每核性能是線性增長十厢，訪問內(nèi)存Cache中的IO時間忽略，要求計算系統(tǒng)對如上應(yīng)用采用硬解析與采用軟解析支持的每秒最大并發(fā)數(shù)：

|

是否使用綁定變量

|

CPU支持最大并發(fā)數(shù)

|

磁盤IO支持最大并發(fā)數(shù)

|
|

不使用

|

241000=8000

|

100160=16000*

|
|

使用

|

241000/0.02=400000

|

100160=16000*

|

從以上計算可以看出捂齐，不使用綁定變量的系統(tǒng)當(dāng)并發(fā)達(dá)到8000時會在CPU上產(chǎn)生瓶頸蛮放，當(dāng)使用綁定變量的系統(tǒng)當(dāng)并行達(dá)到16000時會在磁盤IO上產(chǎn)生瓶頸。所以如果你的系統(tǒng)CPU有瓶頸時請先檢查是否存在大量的硬解析操作奠宜。

使用綁定變量為何會提高SQL解析性能筛武，這個需要從數(shù)據(jù)庫SQL執(zhí)行原理說明，一條SQL在Oracle數(shù)據(jù)庫中的執(zhí)行過程如下圖所示：

[圖片上傳失敗...(image-508ee3-1520414551984)]

當(dāng)一條SQL發(fā)送給數(shù)據(jù)庫服務(wù)器后挎塌，系統(tǒng)首先會將SQL字符串進(jìn)行hash運(yùn)算徘六，得到hash值后再從服務(wù)器內(nèi)存里的SQL緩存區(qū)中進(jìn)行檢索，如果有相同的SQL字符榴都，并且確認(rèn)是同一邏輯的SQL語句待锈，則從共享池緩存中取出SQL對應(yīng)的執(zhí)行計劃，根據(jù)執(zhí)行計劃讀取數(shù)據(jù)并返回結(jié)果給客戶端嘴高。

如果在共享池中未發(fā)現(xiàn)相同的SQL則根據(jù)SQL邏輯生成一條新的執(zhí)行計劃并保存在SQL緩存區(qū)中竿音，然后根據(jù)執(zhí)行計劃讀取數(shù)據(jù)并返回結(jié)果給客戶端和屎。

為了更快的檢索SQL是否在緩存區(qū)中，首先進(jìn)行的是SQL字符串hash值對比春瞬，如果未找到則認(rèn)為沒有緩存柴信，如果存在再進(jìn)行下一步的準(zhǔn)確對比，所以要命中SQL緩存區(qū)應(yīng)保證SQL字符是完全一致宽气，中間有大小寫或空格都會認(rèn)為是不同的SQL随常。

如果我們不采用綁定變量，采用字符串拼接的模式生成SQL,那么每條SQL都會產(chǎn)生執(zhí)行計劃萄涯，這樣會導(dǎo)致共享池耗盡绪氛，緩存命中率也很低。

一些不使用綁定變量的場景：

a涝影、數(shù)據(jù)倉庫應(yīng)用枣察，這種應(yīng)用一般并發(fā)不高，但是每個SQL執(zhí)行時間很長燃逻，SQL解析的時間相比SQL執(zhí)行時間比較小序目，綁定變量對性能提高不明顯。數(shù)據(jù)倉庫一般都是內(nèi)部分析應(yīng)用伯襟，所以也不太會發(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í)行計劃只嚣，如果走索引訪問沮稚，那么對于審核中查詢很快，對審核通過和審核不通過會很慢册舞；如果不走索引蕴掏，那么對于審核中與審核通過和審核不通過時間基本一樣；

對于這種情況應(yīng)該不使用綁定變量，而直接采用字符拼接的方式生成SQL盛杰，這樣可以為每個SQL生成不同的執(zhí)行計劃挽荡，如下所示。

select count(*) from product where status='approved'; //不使用索引

select count(*) from product where status='tbd'; //不使用索引

select count() from product where status='auditing';//使用索引*

4.2即供、合理使用排序

Oracle的排序算法一直在優(yōu)化定拟，但是總體時間復(fù)雜度約等于nLog(n)。普通OLTP系統(tǒng)排序操作一般都是在內(nèi)存里進(jìn)行的逗嫡，對于數(shù)據(jù)庫來說是一種CPU的消耗青自，曾在PC機(jī)做過測試，單核普通CPU在1秒鐘可以完成100萬條記錄的全內(nèi)存排序操作祸穷，所以說由于現(xiàn)在CPU的性能增強(qiáng)性穿，對于普通的幾十條或上百條記錄排序?qū)ο到y(tǒng)的影響也不會很大勺三。但是當(dāng)你的記錄集增加到上萬條以上時雷滚，你需要注意是否一定要這么做了，大記錄集排序不僅增加了CPU開銷吗坚，而且可能會由于內(nèi)存不足發(fā)生硬盤排序的現(xiàn)象祈远，當(dāng)發(fā)生硬盤排序時性能會急劇下降，這種需求需要與DBA溝通再決定商源，取決于你的需求和數(shù)據(jù)车份，所以只有你自己最清楚，而不要被別人說排序很慢就嚇倒牡彻。

以下列出了可能會發(fā)生排序操作的SQL語法：

Order by

Group by

Distinct

Exists子查詢

Not Exists子查詢

In子查詢

Not In子查詢

Union（并集）扫沼，Union All也是一種并集操作，但是不會發(fā)生排序庄吼，如果你確認(rèn)兩個數(shù)據(jù)集不需要執(zhí)行去除重復(fù)數(shù)據(jù)操作缎除，那請使用Union All 代替Union。

Minus（差集）

Intersect（交集）

Create Index

Merge Join总寻，這是一種兩個表連接的內(nèi)部算法器罐，執(zhí)行時會把兩個表先排序好再連接，應(yīng)用于兩個大表連接的操作渐行。如果你的兩個表連接的條件都是等值運(yùn)算轰坊，那可以采用Hash Join來提高性能，因?yàn)镠ash Join使用Hash 運(yùn)算來代替排序的操作祟印。具體原理及設(shè)置參考SQL執(zhí)行計劃優(yōu)化專題肴沫。

4.3、減少比較操作

我們SQL的業(yè)務(wù)邏輯經(jīng)常會包含一些比較操作蕴忆，如a=b颤芬，a<b之類的操作，對于這些比較操作數(shù)據(jù)庫都體現(xiàn)得很好，但是如果有以下操作驻襟，我們需要保持警惕：

Like模糊查詢夺艰，如下所示：

a like ‘%abc%’

Like模糊查詢對于數(shù)據(jù)庫來說不是很擅長，特別是你需要模糊檢查的記錄有上萬條以上時沉衣，性能比較糟糕郁副，這種情況一般可以采用專用Search或者采用全文索引方案來提高性能。

不能使用索引定位的大量In List豌习，如下所示：

a in (:1,:2,:3,…,:n) ----n>20

如果這里的a字段不能通過索引比較存谎，那數(shù)據(jù)庫會將字段與in里面的每個值都進(jìn)行比較運(yùn)算，如果記錄數(shù)有上萬以上肥隆，會明顯感覺到SQL的CPU開銷加大既荚，這個情況有兩種解決方式：

a、 將in列表里面的數(shù)據(jù)放入一張中間小表栋艳，采用兩個表Hash Join關(guān)聯(lián)的方式處理恰聘；

b、 采用str2varList方法將字段串列表轉(zhuǎn)換一個臨時表處理吸占，關(guān)于str2varList方法可以在網(wǎng)上直接查詢晴叨，這里不詳細(xì)介紹。

以上兩種解決方案都需要與中間表Hash Join的方式才能提高性能矾屯，如果采用了Nested Loop的連接方式性能會更差兼蕊。

如果發(fā)現(xiàn)我們的系統(tǒng)IO沒問題但是CPU負(fù)載很高，就有可能是上面的原因件蚕，這種情況不太常見孙技，如果遇到了最好能和DBA溝通并確認(rèn)準(zhǔn)確的原因。

4.4排作、大量復(fù)雜運(yùn)算在客戶端處理

什么是復(fù)雜運(yùn)算牵啦，一般我認(rèn)為是一秒鐘CPU只能做10萬次以內(nèi)的運(yùn)算。如含小數(shù)的對數(shù)及指數(shù)運(yùn)算纽绍、三角函數(shù)蕾久、3DES及BASE64數(shù)據(jù)加密算法等等。

如果有大量這類函數(shù)運(yùn)算拌夏，盡量放在客戶端處理僧著，一般CPU每秒中也只能處理1萬-10萬次這樣的函數(shù)運(yùn)算，放在數(shù)據(jù)庫內(nèi)不利于高并發(fā)處理障簿。

5盹愚、利用更多的資源

5.1、客戶端多進(jìn)程并行訪問

多進(jìn)程并行訪問是指在客戶端創(chuàng)建多個進(jìn)程(線程)站故，每個進(jìn)程建立一個與數(shù)據(jù)庫的連接皆怕，然后同時向數(shù)據(jù)庫提交訪問請求毅舆。當(dāng)數(shù)據(jù)庫主機(jī)資源有空閑時脱篙，我們可以采用客戶端多進(jìn)程并行訪問的方法來提高性能族操。如果數(shù)據(jù)庫主機(jī)已經(jīng)很忙時泄鹏，采用多進(jìn)程并行訪問性能不會提高细燎，反而可能會更慢。所以使用這種方式最好與DBA或系統(tǒng)管理員進(jìn)行溝通后再決定是否采用失暂。

例如：

我們有10000個產(chǎn)品ID瓢捉，現(xiàn)在需要根據(jù)ID取出產(chǎn)品的詳細(xì)信息逼裆，如果單線程訪問橱乱，按每個IO要5ms計算辜梳，忽略主機(jī)CPU運(yùn)算及網(wǎng)絡(luò)傳輸時間，我們需要50s才能完成任務(wù)泳叠。如果采用5個并行訪問作瞄，每個進(jìn)程訪問2000個ID，那么10s就有可能完成任務(wù)危纫。

那是不是并行數(shù)越多越好呢宗挥，開1000個并行是否只要50ms就搞定，答案肯定是否定的叶摄，當(dāng)并行數(shù)超過服務(wù)器主機(jī)資源的上限時性能就不會再提高属韧，如果再增加反而會增加主機(jī)的進(jìn)程間調(diào)度成本和進(jìn)程沖突機(jī)率安拟。

以下是一些如何設(shè)置并行數(shù)的基本建議：

如果瓶頸在服務(wù)器主機(jī)蛤吓，但是主機(jī)還有空閑資源，那么最大并行數(shù)取主機(jī)CPU核數(shù)和主機(jī)提供數(shù)據(jù)服務(wù)的磁盤數(shù)兩個參數(shù)中的最小值糠赦，同時要保證主機(jī)有資源做其它任務(wù)会傲。

如果瓶頸在客戶端處理，但是客戶端還有空閑資源拙泽，那建議不要增加SQL的并行淌山，而是用一個進(jìn)程取回數(shù)據(jù)后在客戶端起多個進(jìn)程處理即可，進(jìn)程數(shù)根據(jù)客戶端CPU核數(shù)計算顾瞻。

如果瓶頸在客戶端網(wǎng)絡(luò)泼疑，那建議做數(shù)據(jù)壓縮或者增加多個客戶端，采用map reduce的架構(gòu)處理荷荤。

如果瓶頸在服務(wù)器網(wǎng)絡(luò)退渗，那需要增加服務(wù)器的網(wǎng)絡(luò)帶寬或者在服務(wù)端將數(shù)據(jù)壓縮后再處理了。

5.2蕴纳、數(shù)據(jù)庫并行處理

數(shù)據(jù)庫并行處理是指客戶端一條SQL的請求会油，數(shù)據(jù)庫內(nèi)部自動分解成多個進(jìn)程并行處理，如下圖所示：

[圖片上傳失敗...(image-1dd0ea-1520414551980)]

并不是所有的SQL都可以使用并行處理古毛，一般只有對表或索引進(jìn)行全部訪問時才可以使用并行翻翩。數(shù)據(jù)庫表默認(rèn)是不打開并行訪問，所以需要指定SQL并行的提示，如下所示：

select /+parallel(a,4)/ *** from employee;

并行的優(yōu)點(diǎn)：

使用多進(jìn)程處理嫂冻，充分利用數(shù)據(jù)庫主機(jī)資源（CPU,IO）胶征，提高性能。

并行的缺點(diǎn)：

1桨仿、單個會話占用大量資源弧烤，影響其它會話，所以只適合在主機(jī)負(fù)載低時期使用蹬敲；

2暇昂、只能采用直接IO訪問，不能利用緩存數(shù)據(jù)伴嗡，所以執(zhí)行前會觸發(fā)將臟緩存數(shù)據(jù)寫入磁盤操作急波。

注：

1、并行處理在OLTP類系統(tǒng)中慎用瘪校，使用不當(dāng)會導(dǎo)致一個會話把主機(jī)資源全部占用澄暮，而正常事務(wù)得不到及時響應(yīng)，所以一般只是用于數(shù)據(jù)倉庫平臺阱扬。

2泣懊、一般對于百萬級記錄以下的小表采用并行訪問性能并不能提高，反而可能會讓性能更差麻惶。

參考文章：http://www.cnblogs.com/easypass/archive/2010/12/08/1900127.html

  http://www.cnblogs.com/yunfeifei/p/3850440.html