2024年面向程序員的數(shù)據(jù)庫訪問性能優(yōu)化法則

面對程序員的數(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)楸疚氖菑某绦?/p>

員的角度來談數(shù)據(jù)庫性能優(yōu)化。

架構(gòu)師：假如你已經(jīng)是數(shù)據(jù)庫應(yīng)用的架構(gòu)師，那本文的知識你應(yīng)當(dā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)?/p>

諸多簡介只是對某些特定的場景優(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)當(dāng)非常了解

我們SQL的業(yè)務(wù)邏輯，我們清楚SQL中訪問表及字段的數(shù)據(jù)情況，我們其實(shí)只關(guān)

心我們的SQL是否能盡快返回成果。那程序員怎樣利用已知的知識進(jìn)行數(shù)據(jù)庫優(yōu)

化？怎樣能迅速定位SQL性能問題并找到正確的優(yōu)化方向？

面對這些問題，筆者總結(jié)了某些面對程序員的基本優(yōu)化法則，本文將結(jié)合實(shí)

例來坦述數(shù)據(jù)庫開發(fā)的優(yōu)化知識。

一、數(shù)據(jù)庫訪問優(yōu)化法則簡介

要正確的優(yōu)化SQL，我們需要迅速定位能性的瓶頸點(diǎn)，也就是說迅速找到我

們SQL重要的開銷在哪里？而大多數(shù)情況性能最嚶的設(shè)備會是瓶頸點(diǎn)，如下載時

網(wǎng)絡(luò)速度也許會是瓶頸點(diǎn)，本地復(fù)制文獻(xiàn)時硬盤也許會是瓶頸點(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),

下圖展示的目前主流計算機(jī)性能指標(biāo)數(shù)據(jù)。

10各層次性能匯總

Cache

內(nèi)存

Ill,L2,L3

延時：0.515ns迪時130100ns

帶寬：2O6OGB/S帶寬12-12GB/S

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

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

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

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

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

因?yàn)镾SD硬盤還處在迅速發(fā)展階段，因此本文的內(nèi)容不包括SSD有關(guān)應(yīng)用系

統(tǒng)。

依照數(shù)據(jù)庫知識，我們能夠列出每種硬件重要的工作內(nèi)容：

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

輯運(yùn)算；

網(wǎng)絡(luò)：成果數(shù)據(jù)傳輸、SQL祈求、遠(yuǎn)程數(shù)據(jù)庫訪問(dblink)；

硬盤：數(shù)據(jù)訪問、數(shù)據(jù)寫入、日志統(tǒng)計、大數(shù)據(jù)量排序、大表連接。

依照目前計算機(jī)硬件的基本性能指標(biāo)及其在數(shù)據(jù)庫中重要操作內(nèi)容，能夠整

頓出如下圖所示的性能基本優(yōu)化法則：

數(shù)據(jù)庫訪問優(yōu)化漏斗?:去則

減少Sort

減少CPU及內(nèi)存開銷CPU

開銷BinoVar

利

用

更

噌加資源多

資csdd"

源Htcpmog

這個優(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àn)槊恳粚觾?yōu)化法則都是處理其對應(yīng)硬件的性能問題，因此帶來的性能提升

百分比也不一樣樣。老式數(shù)據(jù)庫系統(tǒng)設(shè)計是也是盡也許對低速設(shè)備提供優(yōu)化措

施，因此針對低速設(shè)備問題的可優(yōu)化伎倆也更多，優(yōu)化成本也更低。我們?nèi)魏我?/p>

個SQL的性能優(yōu)化都應(yīng)當(dāng)按這個規(guī)則由上到下來診療問題并提出處理方案，而不

應(yīng)當(dā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í)例分析。

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

數(shù)據(jù)塊(Block)

數(shù)據(jù)塊是數(shù)據(jù)庫中數(shù)據(jù)在磁盤中存儲的最小單位，也是一次10訪問的最小

單位，一個數(shù)據(jù)塊一般能夠存儲多條統(tǒng)計，數(shù)據(jù)塊大小是DBA在創(chuàng)建數(shù)據(jù)庫或

表空間時指定，可指定為2K、4K、8K、16K或32K字節(jié)。下圖是一個Oracle數(shù)

據(jù)庫經(jīng)典的物理結(jié)構(gòu)，一個數(shù)據(jù)庫能夠包括多個數(shù)據(jù)文獻(xiàn)，一個數(shù)據(jù)文獻(xiàn)內(nèi)乂包

括多個數(shù)據(jù)塊;

Datafile2DatafileN

其它鋪助文件（控制文件、日志文件、參數(shù)文件.…）

ROWID

ROWID是每條統(tǒng)計在數(shù)據(jù)庫中的唯一標(biāo)識，通過ROWID能夠直接定位統(tǒng)計

到對應(yīng)的文獻(xiàn)號及數(shù)據(jù)塊位置。ROWID內(nèi)容包括文獻(xiàn)號、對像號、數(shù)據(jù)塊號、

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

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

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

數(shù)據(jù)庫索引的原理非常簡單，但在復(fù)雜的表中真正能正確使用索引的人極

少，雖然是專業(yè)的DBA也不一定能完全做到最優(yōu)。

索引會大大增加表統(tǒng)計的DML(INSERT,UPDATE,DELETE)開銷，正確的索引能

夠讓性能提升100,1000倍以上，不合理的索引乜也許會讓性能下降100倍，因

此在一個表中創(chuàng)建什么樣的索引需要平衡各種業(yè)務(wù)需求。

索引常見問題：

索引有哪些種類？

常見的索引有B-TREE索引、位圖索引、全文索引，位圖索引一般用于數(shù)據(jù)倉

庫應(yīng)用，全文索引因?yàn)槭褂幂^少，這里不深入簡介。B-TREE索引包括諸多擴(kuò)展類

型，如組合索引、反向索引、函數(shù)索引等等，如下是B-TREE索引的簡單簡介：

B-TREE索引也稱為平衡樹索引(BalanceTree),它是一個按字段排好序的樹形

目錄結(jié)構(gòu)，重要用于提升查詢性能和唯一約束支持。B-TREE索引的內(nèi)容包括根節(jié)

點(diǎn)、分支節(jié)點(diǎn)、葉子節(jié)點(diǎn)。

葉子節(jié)點(diǎn)內(nèi)容：索引字段內(nèi)容十表統(tǒng)計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)示意圖如下所示:

假如我們把一個表的內(nèi)容以為是一本字典，那索引就相稱于字典的目錄，如下圖

所示:

根節(jié)點(diǎn)

?際E城

Sg,>

8t?*CM

8

tt?

at?

■

*a常

zt?

葉子節(jié)點(diǎn)

?

分支節(jié)點(diǎn)！界

鄧T4

r■

六

^羽

sBaj

fl?/

r：Z

Z

gfll

tt刈lg

c^B?

?

ys號e

f葉子節(jié)點(diǎn)內(nèi)到數(shù)據(jù)

七

?

llrv埃號的指計

Si*l*：le

f?<

?>|R

黑t2'

em?

部

S案H;

a<

￡織u

s

一

分支節(jié)點(diǎn)2oS+

刈4

t

湍T1

f>,

w?

t

羽

登?

*

?

圖中是一個字典按部首+筆劃數(shù)的目錄，相稱于給字典建了一個按部首+筆劃的組

合索引。

一個表中能夠建多個索引，就如一本字典能夠建多個目錄同樣（按拼音、筆劃、

部首等等）。

一個索引也能夠由多個字段組成，稱為組合索引，如上圖就是一個按部首+筆劃

的組合目錄。

SQL什么條件會使用索引？

當(dāng)字段上建有索引時，一般如下情況會使用索引:

INDEXCOLUMN=?

INDEX_COLUMN>?

INDEX_COLUMN>=?

INDEX_COLUMN<?

INDEX_COLUMN<=?

INDEX_COLUMNbetween?and?

INDEX_COLUMNin(??...,?)

INDEX_COLUMNIike?||'%'(后導(dǎo)含糊查詢)

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

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

查詢條件不能使用索引原因

INDEX_COLUMN<>?不等于操作不能使用索引

INDEX_COLUMNnotin

function(INDEX_COLUMN)=?通過一般運(yùn)算或函數(shù)運(yùn)算后的索引字

INDEX_COLUMN+1=?段不能使用索引

INDEX_COLUMN11'a'=?

INDEX_COLUMNlike'%'||?含前導(dǎo)含糊查詢的Like語法不能使用

INDEX_COLUMNlike索弓1

INDEXCOLUMNisnullB-TREE索引里不保存字段為NULL值統(tǒng)

計，因此ISNULL不能使用索引

NUMBER_INDEX_COLUMN='12345'Oracle在做數(shù)值比較時需要將兩邊的

CHAR_INDEX_COLUMN=12345數(shù)據(jù)轉(zhuǎn)換成同一個數(shù)據(jù)類型，假如兩邊

數(shù)據(jù)類型不一樣時會對字段值隱式轉(zhuǎn)

換，相稱于加了一層函數(shù)處理，因此不

能使用索引。

a.lNDEX_COLUMN=a.COLUMN_l給索引查詢的值應(yīng)是己知數(shù)據(jù)，不能是

未知字段值。

注：

通過函數(shù)運(yùn)算字段的字段要使用能夠使用函數(shù)索引，這種需求提議與

DBA溝通。

有時候我們會使用多個字段的組合索引，假如查詢條件中第一個字段

不能使用索引，那整個查詢也不能使用索引

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

索引的

Select*fromcompanywherename=?

Oracle9i后引入了一個indexskipscan的索引方式來處理類似的問題,

不過通過indexskipscan提升性能的條件比較特殊，使用不好反而性能會

更差。

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

這是一個非常復(fù)雜的話題，需要對業(yè)務(wù)及數(shù)據(jù)充足分析后再能得出成果。主

鍵及外鍵一般都要有索引，其他需要建索引的字段應(yīng)滿足如下條件：

1、字段出目前查詢條件中，并且查詢條件能夠使用索引；

2、語句執(zhí)行頻率高，一天會有兒千次以上；

3、通過字段條件可篩選的統(tǒng)計集很小，那數(shù)據(jù)篩選百分比是多少才適合？

這個沒有固定值，需要依照表數(shù)據(jù)量來評定，如下是經(jīng)驗(yàn)公式，可用于迅速

評定：

小表（統(tǒng)計數(shù)小于10000行的表）：篩選百分比＜10%;

大表：（篩選返回統(tǒng)計數(shù)）＜（表總統(tǒng)計數(shù)*單條統(tǒng)計長度）/10000/16

單條統(tǒng)計長度?字段平均內(nèi)容長度之和十字段數(shù)*2

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

字

常見字段名

段

類

型

ID,PK

需要

主

鍵

建索

引的

AY」D

」D,P

ADE

」D,TR

RDER

R」D,O

MBE

D,ME

NY」

MPA

ID,CO

UCT_

PROD

外

字段

鍵有

NO

,IM_

L_NO

AILTE

OEM

RD_N

JDCA

AME

ERN

EUS

_COD

HASH

/

f

對/

像

或

身

份

標(biāo)

識

意

義

字

段

索引

DIFIED

_MO

GMT

ATE,

_CRE

GMT

慎用日

字期

TH

,MON

YEAR

段年

，

需要月狀

FLAG

,VIP_

LETE

S_DE

TUS,I

_STA

RDER

US,O

STAT

UCT_

進(jìn)行態(tài)PROD

數(shù)據(jù)標(biāo)

分布志

及使類

TYPE

NCY_

URRE

DER,C

,GEN

TYPE

AGE_

PE,IM

R_TY

用場型ORDE

景詳

細(xì)評

CITY

NCE,

ROVI

TRY,P

COUN

區(qū)

定

域操

R

UDITO

TOR,A

作CREA

人

員

數(shù)

RE

T,SCO

OUN

L,AM

值LEVE

長

JECT

Y,SUB

MAR

E,SUM

NAM

ANY_

OMP

ESS,C

字ADDR

符

描

ETAIL

MO,D

K