数据库性能优化—全局优化思路
一、數(shù)據(jù)庫(kù)訪問優(yōu)化法則
要正確的優(yōu)化SQL，我們需要快速定位能性的瓶頸點(diǎn)，也就是說快速找到我們SQL主要的開銷在哪里？
大多數(shù)情況性能最慢的設(shè)備會(huì)是瓶頸點(diǎn)，如下載時(shí)網(wǎng)絡(luò)速度可能會(huì)是瓶頸點(diǎn)，本地復(fù)制文件時(shí)硬盤可能會(huì)是瓶頸點(diǎn)，為什么這些一般的工作我們能快速確認(rèn)瓶頸點(diǎn)呢，因?yàn)槲覀儗?duì)這些慢速設(shè)備的性能數(shù)據(jù)有一些基本的認(rèn)識(shí)，如網(wǎng)絡(luò)帶寬是2Mbps，硬盤是每分鐘7200轉(zhuǎn)等等。因此，為了快速找到SQL的性能瓶頸點(diǎn)，我們也需要了解我們計(jì)算機(jī)系統(tǒng)的硬件基本性能指標(biāo)，下圖展示的當(dāng)前主流計(jì)算機(jī)性能指標(biāo)數(shù)據(jù)：
1、從圖上可以看到基本上每種設(shè)備都有兩個(gè)指標(biāo)：
- 延時(shí)（響應(yīng)時(shí)間）：表示硬件的突發(fā)處理能力；
- 帶寬（吞吐量）：代表硬件持續(xù)處理能力。
2、從上圖可以看出，計(jì)算機(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)。
3、根據(jù)數(shù)據(jù)庫(kù)知識(shí)，我們可以列出每種硬件主要的工作內(nèi)容：
CPU及內(nèi)存：緩存數(shù)據(jù)訪問、比較、排序、事務(wù)檢測(cè)、SQL解析、函數(shù)或邏輯運(yùn)算；
網(wǎng)絡(luò)：結(jié)果數(shù)據(jù)傳輸、SQL請(qǐng)求、遠(yuǎn)程數(shù)據(jù)庫(kù)訪問（dblink）；
硬盤：數(shù)據(jù)訪問、數(shù)據(jù)寫入、日志記錄、大數(shù)據(jù)量排序、大表連接。
4、根據(jù)當(dāng)前計(jì)算機(jī)硬件的基本性能指標(biāo)及其在數(shù)據(jù)庫(kù)中主要操作內(nèi)容，可以整理出如下圖所示的性能基本優(yōu)化法則：
由于每一層優(yōu)化法則都是解決其對(duì)應(yīng)硬件的性能問題，所以帶來的性能提升比例也不一樣。傳統(tǒng)數(shù)據(jù)庫(kù)系統(tǒng)設(shè)計(jì)是也是盡可能對(duì)低速設(shè)備提供優(yōu)化方法，因此針對(duì)低速設(shè)備問題的可優(yōu)化手段也更多，優(yōu)化成本也更低。我們?nèi)魏我粋€(gè)SQL的性能優(yōu)化都應(yīng)該按這個(gè)規(guī)則由上到下來診斷問題并提出解決方案，而不應(yīng)該首先想到的是增加資源解決問題。
以下是每個(gè)優(yōu)化法則層級(jí)對(duì)應(yīng)優(yōu)化效果及成本經(jīng)驗(yàn)參考：
| 優(yōu)化法則 | 性能提升效果 | 優(yōu)化成本 | 優(yōu)化手段 |
| 減少數(shù)據(jù)訪問 | 1~1000 | 低 | 1、創(chuàng)建并使用正確的索引 2、只通過索引訪問數(shù)據(jù) 3、優(yōu)化SQL執(zhí)行計(jì)劃 |
| 返回更少數(shù)據(jù) | 1~100 | 低 | 1、數(shù)據(jù)分頁(yè)處理：客戶端分頁(yè)、應(yīng)用程序分頁(yè)、sql分頁(yè) 2、只返回需要的字段 |
| 減少請(qǐng)求次數(shù) | 1~20 | 低 | 1、批提交batch DML 2、In List：一個(gè)ID一個(gè)請(qǐng)求發(fā)給數(shù)據(jù)庫(kù)改成In List的寫法 3、設(shè)置Fetch Size 4、使用存儲(chǔ)過程 5、優(yōu)化業(yè)務(wù)邏輯 6、使用ResultSet游標(biāo)處理記錄 |
| 減少服務(wù)器CPU開銷 | 1~5 | 低 | 1、使用綁定變量 2、合理使用排序 3、減少比較操作 4、大量復(fù)雜運(yùn)算在客戶端處理 |
| 利用更多資源 | @~10 | 高 | 1、客戶端多進(jìn)程并行訪問 2、數(shù)據(jù)庫(kù)并行處理 |
二、數(shù)據(jù)庫(kù)優(yōu)化手段詳解
1、減少數(shù)據(jù)訪問
1）創(chuàng)建并使用正確的索引
如果我們把一個(gè)表的內(nèi)容認(rèn)為是一本字典，那索引就相當(dāng)于字典的目錄，如下圖所示：
一般在什么字段上建索引？這是一個(gè)非常復(fù)雜的話題，需要對(duì)業(yè)務(wù)及數(shù)據(jù)充分分析后再能得出結(jié)果。主鍵及外鍵通常都要有索引，其它需要建索引的字段應(yīng)滿足以下條件：
1）字段出現(xiàn)在查詢條件中，并且查詢條件可以使用索引；
2）語(yǔ)句執(zhí)行頻率高，一天會(huì)有幾千次以上；
3）通過字段條件可篩選的記錄集很小，那數(shù)據(jù)篩選比例是多少才適合？
這個(gè)沒有固定值，需要根據(jù)表數(shù)據(jù)量來評(píng)估，以下是經(jīng)驗(yàn)公式，可用于快速評(píng)估：
小表(記錄數(shù)小于10000行的表)：篩選比例<10%；
大表：(篩選返回記錄數(shù))<(表總記錄數(shù)*單條記錄長(zhǎng)度)/10000/16，單條記錄長(zhǎng)度≈字段平均內(nèi)容長(zhǎng)度之和+字段數(shù)*2
2）只通過索引訪問數(shù)據(jù)
有些時(shí)候，只訪問表中的幾個(gè)字段，并且字段內(nèi)容較少，我們可以為這幾個(gè)字段單獨(dú)建立一個(gè)組合索引，這樣就可以直接只通過訪問索引就能得到數(shù)據(jù)，一般索引占用的磁盤空間比表小很多，所以這種方式可以大大減少磁盤IO開銷。
如：select id,name from company where type='2';，如果這個(gè)SQL經(jīng)常使用，我們可以在type,id,name上創(chuàng)建組合索引
create index my_comb_index on company(type,id,name);
有了這個(gè)組合索引后，SQL就可以直接通過my_comb_index索引返回?cái)?shù)據(jù)，不需要訪問company表。
還是拿字典舉例：有一個(gè)需求，需要查詢一本漢語(yǔ)字典中所有漢字的個(gè)數(shù)，如果我們的字典沒有目錄索引，那我們只能從字典內(nèi)容里一個(gè)一個(gè)字計(jì)數(shù)，最后返回結(jié)果。如果我們有一個(gè)拼音目錄，那就可以只訪問拼音目錄的漢字進(jìn)行計(jì)數(shù)。如果一本字典有1000頁(yè)，拼音目錄有20頁(yè)，那我們的數(shù)據(jù)訪問成本相當(dāng)于全表訪問的50分之一。
切記，性能優(yōu)化是無止境的，當(dāng)性能可以滿足需求時(shí)即可，不要過度優(yōu)化。在實(shí)際數(shù)據(jù)庫(kù)中我們不可能把每個(gè)SQL請(qǐng)求的字段都建在索引里，所以這種只通過索引訪問數(shù)據(jù)的方法一般只用于核心應(yīng)用，也就是那種對(duì)核心表訪問量最高且查詢字段數(shù)據(jù)量很少的查詢。
3）優(yōu)化SQL執(zhí)行計(jì)劃
?
2、返回更少的數(shù)據(jù)
1）數(shù)據(jù)分頁(yè)處理
| 客戶端(應(yīng)用程序或?yàn)g覽器)分頁(yè) | 數(shù)據(jù)：應(yīng)用服務(wù)器 -> (本地應(yīng)用程序或?yàn)g覽器)，在應(yīng)用程序或?yàn)g覽器內(nèi)部通過本地代碼進(jìn)行分頁(yè)處理。 | 編碼簡(jiǎn)單，減少客戶端與應(yīng)用服務(wù)器網(wǎng)絡(luò)交互次數(shù) | 首次交互時(shí)間長(zhǎng)，占用客戶端內(nèi)存 | 客戶端與應(yīng)用服務(wù)器網(wǎng)絡(luò)延時(shí)較大，但要求后續(xù)操作流暢，如手機(jī)GPRS，超遠(yuǎn)程訪問（跨國(guó)）等等。 |
| 應(yīng)用服務(wù)器分頁(yè) | 數(shù)據(jù)：數(shù)據(jù)庫(kù)服務(wù)器 -> 應(yīng)用服務(wù)器，在應(yīng)用服務(wù)器內(nèi)部再進(jìn)行數(shù)據(jù)篩選。 以下是一個(gè)應(yīng)用服務(wù)器端Java程序分頁(yè)的示例： List list=executeQuery(“select * from employee order by id”);Int count= list.size();List subList= list.subList(10, 20);? | 編碼簡(jiǎn)單，只需要一次SQL交互，總數(shù)據(jù)與分頁(yè)數(shù)據(jù)差不多時(shí)性能較好。 | 總數(shù)據(jù)量較多時(shí)性能較差。 | 數(shù)據(jù)庫(kù)系統(tǒng)不支持分頁(yè)處理，數(shù)據(jù)量較小并且可控。 |
| 數(shù)據(jù)庫(kù)SQL分頁(yè) | 采用數(shù)據(jù)庫(kù)SQL分頁(yè)需要兩次SQL完成：一個(gè)SQL計(jì)算總數(shù)量，一個(gè)SQL返回分頁(yè)后的數(shù)據(jù) | 性能好 | 編碼復(fù)雜，各種數(shù)據(jù)庫(kù)語(yǔ)法不同，需要兩次SQL交互。 | ? |
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、字段變更時(shí)提前發(fā)現(xiàn)問題，減少程序BUG
5、如果訪問的所有字段剛好在一個(gè)索引里面，則可以使用純索引訪問提高性能。
缺點(diǎn)：增加編碼工作量
由于會(huì)增加一些編碼工作量，所以一般需求通過開發(fā)規(guī)范來要求程序員這么做，否則等項(xiàng)目上線后再整改工作量更大。
如果你的查詢表中有大字段或內(nèi)容較多的字段，如備注信息、文件內(nèi)容等等，那在查詢表時(shí)一定要注意這方面的問題，否則可能會(huì)帶來嚴(yán)重的性能問題。如果表經(jīng)常要查詢并且請(qǐng)求大內(nèi)容字段的概率很低，我們可以采用分表處理，將一個(gè)大表分拆成兩個(gè)一對(duì)一的關(guān)系表，將不常用的大內(nèi)容字段放在一張單獨(dú)的表中。如一張存儲(chǔ)上傳文件的表：
T_FILE（ID,FILE_NAME,FILE_SIZE,FILE_TYPE,FILE_CONTENT）我們可以分拆成兩張一對(duì)一的關(guān)系表：
T_FILE（ID,FILE_NAME,FILE_SIZE,FILE_TYPE）T_FILECONTENT（ID, FILE_CONTENT）?????????通過這種分拆，可以大大提少T_FILE表的單條記錄及總大小，這樣在查詢T_FILE時(shí)性能會(huì)更好，當(dāng)需要查詢FILE_CONTENT字段內(nèi)容時(shí)再訪問T_FILECONTENT表。
轉(zhuǎn)自：http://my.oschina.net/xianggao/blog/87216
另有參考?http://my.oschina.net/xianggao/blog/87448?數(shù)據(jù)庫(kù)性能優(yōu)化之SQL語(yǔ)句優(yōu)化2
http://my.oschina.net/xianggao/blog/87450?數(shù)據(jù)庫(kù)性能優(yōu)化之SQL語(yǔ)句優(yōu)化3
http://my.oschina.net/xianggao/blog/87453?數(shù)據(jù)庫(kù)性能優(yōu)化之SQL語(yǔ)句優(yōu)化4
http://my.oschina.net/xianggao/blog/87223??關(guān)于如何形成一個(gè)好的數(shù)據(jù)庫(kù)設(shè)計(jì)
總結(jié)
以上是生活随笔為你收集整理的数据库性能优化—全局优化思路的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 消息队列—主要消息中间件优势对比
- 下一篇： 性能优化—代码层面优化