本文關(guān)鍵詞：數(shù)據(jù)庫(kù)訪問(wèn)，由筆耕文化傳播整理發(fā)布。

特別說(shuō)明：

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

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

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

 

讀者對(duì)像：

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

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

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

 

引言

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

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

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

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

一、數(shù)據(jù)庫(kù)訪問(wèn)優(yōu)化法則簡(jiǎn)介

要正確的優(yōu)化SQL，我們需要快速定位能性的瓶頸點(diǎn)，也就是說(shuō)快速找到我們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ù)。

 

 

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

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

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

 

從上圖可以看出，計(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)。

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

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

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

硬盤：數(shù)據(jù)訪問(wèn)、數(shù)據(jù)寫入、日志記錄、大數(shù)據(jù)量排序、大表連接。

 

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

 

 

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

1、  減少數(shù)據(jù)訪問(wèn)（減少磁盤訪問(wèn)）

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

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

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

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

 

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

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

 

優(yōu)化法則

性能提升效果

優(yōu)化成本

減少數(shù)據(jù)訪問(wèn)

1~1000

低

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

1~100

低

減少交互次數(shù)

1~20

低

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

1~5

低

利用更多資源

@~10

高

 

 

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

 

二、Oracle數(shù)據(jù)庫(kù)兩個(gè)基本概念 數(shù)據(jù)塊(Block)

數(shù)據(jù)塊是數(shù)據(jù)庫(kù)中數(shù)據(jù)在磁盤中存儲(chǔ)的最小單位，也是一次IO訪問(wèn)的最小單位，一個(gè)數(shù)據(jù)塊通�？梢源鎯�(chǔ)多條記錄，數(shù)據(jù)塊大小是DBA在創(chuàng)建數(shù)據(jù)庫(kù)或表 空間時(shí)指定，可指定為2K、4K、8K、16K或32K字節(jié)。下圖是一個(gè)Oracle數(shù)據(jù)庫(kù)典型的物理結(jié)構(gòu)，一個(gè)數(shù)據(jù)庫(kù)可以包括多個(gè)數(shù)據(jù)文件，一個(gè)數(shù)據(jù)文 件內(nèi)又包含多個(gè)數(shù)據(jù)塊；

 

 

ROWID

ROWID是每條記錄在數(shù)據(jù)庫(kù)中的唯一標(biāo)識(shí)，通過(guò)ROWID可以直接定位記錄到對(duì)應(yīng)的文件號(hào)及數(shù)據(jù)塊位置。ROWID內(nèi)容包括文件號(hào)、對(duì)像號(hào)、數(shù)據(jù)塊號(hào)、記錄槽號(hào)，如下圖所示：

 

三、數(shù)據(jù)庫(kù)訪問(wèn)優(yōu)化法則詳解 1、減少數(shù)據(jù)訪問(wèn) 1.1、創(chuàng)建并使用正確的索引

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

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

索引常見(jiàn)問(wèn)題：

索引有哪些種類？

常見(jiàn)的索引有B-TREE索引、位圖索引、全文索引，位圖索引一般用于數(shù)據(jù)倉(cāng)庫(kù)應(yīng)用，全文索引由于使用較少，這里不深入介紹。B-TREE索引包括很多擴(kuò)展類型，如組合索引、反向索引、函數(shù)索引等等，以下是B-TREE索引的簡(jiǎn)單介紹：

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)一個(gè)數(shù)據(jù)塊中不能放下所有索引字段數(shù)據(jù)時(shí)，就會(huì)形成樹形的根節(jié)點(diǎn)或分支節(jié)點(diǎn)，根節(jié)點(diǎn)與分支節(jié)點(diǎn)保存了索引樹的順序及各層級(jí)間的引用關(guān)系。

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

 

 

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

 

 

 

 

 

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

一個(gè)表中可以建多個(gè)索引，就如一本字典可以建多個(gè)目錄一樣（按拼音、筆劃、部首等等）。

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

SQL什么條件會(huì)使用索引？

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

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（兩個(gè)表通過(guò)索引字段關(guān)聯(lián)）

 

SQL什么條件不會(huì)使用索引？

 

查詢條件

不能使用索引原因

INDEX_COLUMN <> ?

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

不等于操作不能使用索引

function(INDEX_COLUMN) = ?

INDEX_COLUMN + 1 = ?

INDEX_COLUMN || 'a' = ?

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

INDEX_COLUMN like '%'||?

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

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

INDEX_COLUMN is null

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

NUMBER_INDEX_COLUMN='12345'

CHAR_INDEX_COLUMN=12345

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

a.INDEX_COLUMN=a.COLUMN_1

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

注：

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

有時(shí)候我們會(huì)使用多個(gè)字段的組合索引，如果查詢條件中第一個(gè)字段不能使用索引，那整個(gè)查詢也不能使用索引

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

Select * from company where name=?

Oracle9i后引入了一種index skip scan的索引方式來(lái)解決類似的問(wèn)題，但是通過(guò)index skip scan提高性能的條件比較特殊，使用不好反而性能會(huì)更差。

 

 

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

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

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

2、語(yǔ)句執(zhí)行頻率高，一天會(huì)有幾千次以上；

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

這個(gè)沒(méi)有固定值，需要根據(jù)表數(shù)據(jù)量來(lái)評(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

 

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

 

 

字段類型

常見(jiàn)字段名

需要建索引的字段

主鍵

ID,PK

外鍵

PRODUCT_ID,COMPANY_ID,MEMBER_ID,ORDER_ID,TRADE_ID,PAY_ID

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

HASH_CODE,USERNAME,IDCARD_NO,EMAIL,TEL_NO,IM_NO

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

日期

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

長(zhǎng)字符

ADDRESS,COMPANY_NAME,SUMMARY,SUBJECT

不適合建索引的字段

描述備注

DESCRIPTION,REMARK,MEMO,DETAIL

大字段

FILE_CONTENT,EMAIL_CONTENT

 

 

如何知道SQL是否使用了正確的索引？

簡(jiǎn)單SQL可以根據(jù)索引使用語(yǔ)法規(guī)則判斷，復(fù)雜的SQL不好辦，判斷SQL的響應(yīng)時(shí)間是一種策略，但是這會(huì)受到數(shù)據(jù)量、主機(jī)負(fù)載及緩存等因素的影 響，有時(shí)數(shù)據(jù)全在緩存里，可能全表訪問(wèn)的時(shí)間比索引訪問(wèn)時(shí)間還少。要準(zhǔn)確知道索引是否正確使用，需要到數(shù)據(jù)庫(kù)中查看SQL真實(shí)的執(zhí)行計(jì)劃，這個(gè)話題比較復(fù) 雜，詳見(jiàn)SQL執(zhí)行計(jì)劃專題介紹。

 

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

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

索引對(duì)于Insert性能降低56%

索引對(duì)于Update性能降低47%

索引對(duì)于Delete性能降低29%

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

 

1.2、只通過(guò)索引訪問(wèn)數(shù)據(jù)

有些時(shí)候，我們只是訪問(wèn)表中的幾個(gè)字段，并且字段內(nèi)容較少，我們可以為這幾個(gè)字段單獨(dú)建立一個(gè)組合索引，這樣就可以直接只通過(guò)訪問(wèn)索引就能得到數(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就可以直接通過(guò)my_comb_index索引返回?cái)?shù)據(jù)，不需要訪問(wèn)company表。

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

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

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

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

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

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

 

2、返回更少的數(shù)據(jù) 2.1、數(shù)據(jù)分頁(yè)處理

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

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

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

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

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

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

2.1.2、應(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);

 

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

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

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

 

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

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

一個(gè)SQL計(jì)算總數(shù)量

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

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

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

 

oracle數(shù)據(jù)庫(kù)一般采用rownum來(lái)進(jìn)行分頁(yè)，常用分頁(yè)語(yǔ)法有如下兩種：

 

直接通過(guò)rownum分頁(yè)：

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ù)訪問(wèn)開銷=索引IO+索引全部記錄結(jié)果對(duì)應(yīng)的表數(shù)據(jù)IO

 

采用rowid分頁(yè)語(yǔ)法

優(yōu)化原理是通過(guò)純索引找出分頁(yè)記錄的ROWID，再通過(guò)ROWID回表返回?cái)?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ù)訪問(wèn)開銷=索引IO+索引分頁(yè)結(jié)果對(duì)應(yīng)的表數(shù)據(jù)IO

 

實(shí)例：

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

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

 

2.2、只返回需要的字段

通過(guò)去除不必要的返回字段可以提高性能，例：

調(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)問(wèn)題，減少程序BUG

5、如果訪問(wèn)的所有字段剛好在一個(gè)索引里面，則可以使用純索引訪問(wèn)提高性能。

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

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

如果你的查詢表中有大字段或內(nèi)容較多的字段，如備注信息、文件內(nèi)容等等，那在查詢表時(shí)一定要注意這方面的問(wèn)題，否則可能會(huì)帶來(lái)嚴(yán)重的性能問(wè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）

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

 

3、減少交互次數(shù) 3.1、batch DML

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

 

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

 

　單位：ms

No batch

Batch=10

Batch=100

Batch=1000

Batch=10000

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

0.1

0.1

0.1

0.1

0.1

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

0.02

0.2

2

20

200

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

0.1

0.1

0.1

0.1

0.1

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

0.01

0.1

1

10

100

小計(jì)

0.23

0.5

3.2

30.2

300.2

平均每條記錄處理時(shí)間

0.23

0.05

0.032

0.0302

0.03002

 

 

從上可以看出，Insert操作加大Batch可以對(duì)性能提高近8倍性能，一般根據(jù)主鍵的Update或Delete操作也可能提高2-3倍性能， 但不如Insert明顯，因?yàn)閁pdate及Delete操作可能有比較大的開銷在物理IO訪問(wèn)。以上僅是理論計(jì)算值，實(shí)際情況需要根據(jù)具體環(huán)境測(cè)量。

 

3.2、In List

很多時(shí)候我們需要按一些ID查詢數(shù)據(jù)庫(kù)記錄，，我們可以采用一個(gè)ID一個(gè)請(qǐng)求發(fā)給數(shù)據(jù)庫(kù)，如下所示：

for :var in ids[] do begin

  select * from mytable where id=:var;

end;

 

我們也可以做一個(gè)小的優(yōu)化， 如下所示，用ID INLIST的這種方式寫SQL：

select * from mytable where id in(:id1,id2,...,idn);

 

通過(guò)這樣處理可以大大減少SQL請(qǐng)求的數(shù)量，從而提高性能。那如果有10000個(gè)ID，那是不是全部放在一條SQL里處理呢？答案肯定是否定的。首先大部份數(shù)據(jù)庫(kù)都會(huì)有SQL長(zhǎng)度和IN里個(gè)數(shù)的限制，如ORACLE的IN里就不允許超過(guò)1000個(gè)值。

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

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

綜合考慮，一般IN里面的值個(gè)數(shù)超過(guò)20個(gè)以后性能基本沒(méi)什么太大變化，也特別說(shuō)明不要超過(guò)100，超過(guò)后可能會(huì)引起執(zhí)行計(jì)劃的不穩(wěn)定性及增加數(shù)據(jù)庫(kù)CPU及內(nèi)存成本，這個(gè)需要專業(yè)DBA評(píng)估。

 

3.3、設(shè)置Fetch Size

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

 

以下是jdbc測(cè)試的代碼，采用本地?cái)?shù)據(jù)庫(kù)，表緩存在數(shù)據(jù)庫(kù)CACHE中，因此沒(méi)有網(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);

    }

}

 

測(cè)試示例中的employee表有100000條記錄，每條記錄平均長(zhǎng)度135字節(jié)

 

以下是測(cè)試結(jié)果，對(duì)每種fetchsize測(cè)試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默認(rèn)值為10，由上測(cè)試可以看出fetchsize對(duì)性能影響還是比較大的，但是當(dāng)fetchsize大于100時(shí)就基本上沒(méi)有影響 了。fetchsize并不會(huì)存在一個(gè)最優(yōu)的固定值，因?yàn)檎w性能與記錄集大小及硬件平臺(tái)有關(guān)。根據(jù)測(cè)試結(jié)果建議當(dāng)一次性要取大量數(shù)據(jù)時(shí)這個(gè)值設(shè)置為 100左右，不要小于40。注意，fetchsize不能設(shè)置太大，如果一次取出的數(shù)據(jù)大于JVM的內(nèi)存會(huì)導(dǎo)致內(nèi)存溢出，所以建議不要超過(guò)1000，太大 了也沒(méi)什么性能提高，反而可能會(huì)增加內(nèi)存溢出的危險(xiǎn)。

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

 

iBatis的SqlMapping配置文件可以對(duì)每個(gè)SQL語(yǔ)句指定fetchsize大小，如下所示：

 

  本文關(guān)鍵詞：數(shù)據(jù)庫(kù)訪問(wèn)，由筆耕文化傳播整理發(fā)布。

本文編號(hào)：93709