面對程序員的數據庫訪問性能優化法則

尤其闡明：

1、本文只是面對數據庫應用開發的程序員，不適合專業DBA,DBA在數據庫性

能優化方面需要了解更多的知識；

2、本文許多示例及概念是基于Oracle數據庫描述，對于其他關系型數據庫也能

夠參考，但許多觀點不適合于KV數據底或內存數據庫或者是基于SSD技術

的數據庫；

3、本文未深入數據庫優化中最核心的執行計劃分析技術。

讀者對像：

開發人員：假如你是做數據庫開發，那本文的內容非常適合，因為本文是從程序

員的角度來談數據庫性能優化。

架構師：假如你已經是數據庫應用的架構師，那本文的知識你應當清楚90%,否

則你也許是一個喜歡疔騰的架構師。

DBA（數據庫管理員）：大型數據庫優化的知識車常復雜，本文只是從程序員的

角度來談性能優化，DBA除了需要了解這些知識外，還需要深入數據庫的內部體

系架構來處理問題。

引言

在網上有諸多文章簡介數據庫優化知識，不過大部份文章只是對某個一個方

面進行闡明，而對于我們程序員來說這種簡介并不能很好的掌握優化知識，因為

諸多簡介只是對某些特定的場景優化的，因此反而有時會產生誤導或讓程序員感

覺不明白其中的奧妙而對數據庫優化感覺很神秘。

諸多程序員總是問怎樣學習數據庫優化，有無好的教材之類的問題。在書店

也看到了許多數據庫優化的專業書籍，不過感覺更多是面對DBA或者是PL/SQL

開發方面的知識，個人感覺不太適合一般程序員。而要想做到數據庫優化的高

手，不是花幾周，幾個月就能達成的，這并不是因為數據庫優化有多高深，而是

因為要做好優化首先需要有非常好的技術功底，對操作系統、存儲硬件網絡、

數據庫原理等方面有比較扎實的基礎知識，另首先是需要花大量時間對特定的數

據庫進行實踐測試與總結。

作為一個程序員，我們也許不清楚線上正式的服務器硬件配備，我們不也許

像DBA那樣專業的對數據庫進行各種實踐測試與總結，但我們都應當非常了解

我們SQL的業務邏輯，我們清楚SQL中訪問表及字段的數據情況，我們其實只關

心我們的SQL是否能盡快返回成果。那程序員怎樣利用已知的知識進行數據庫優

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

面對這些問題，筆者總結了某些面對程序員的基本優化法則，本文將結合實

例來坦述數據庫開發的優化知識。

一、數據庫訪問優化法則簡介

要正確的優化SQL，我們需要迅速定位能性的瓶頸點，也就是說迅速找到我

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

網絡速度也許會是瓶頸點，本地復制文獻時硬盤也許會是瓶頸點，為何這些一般

的工作我們能迅速確認瓶頸點呢，因為我們對這些慢速設備的性能數據有某些基

本的認識，如網絡帶寬是2MbpS,硬盤是每分鐘7200轉等等。因此，為了迅速

找到SQL的性能瓶頸點，我們也需要了解我們計算機系統的硬件基本性能指標,

下圖展示的目前主流計算機性能指標數據。

10各層次性能匯總

Cache

內存

Ill,L2,L3

延時：0.515ns迪時130100ns

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

從圖上能夠看到基本上每種設備都有兩個指標:

延時（響應時間）：表示硬件的突發處理能刀;

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

從上圖能夠看出，計算機系統硬件性能從高到代依次為：

CPU——Cache(Ll-L2-L3)——內存——SSD硬盤——網絡——硬盤

因為SSD硬盤還處在迅速發展階段，因此本文的內容不包括SSD有關應用系

統。

依照數據庫知識，我們能夠列出每種硬件重要的工作內容：

CPU及內存：緩存數據訪問、比較、排序、事務檢測、SQL解析、函數或邏

輯運算；

網絡：成果數據傳輸、SQL祈求、遠程數據庫訪問(dblink)；

硬盤：數據訪問、數據寫入、日志統計、大數據量排序、大表連接。

依照目前計算機硬件的基本性能指標及其在數據庫中重要操作內容，能夠整

頓出如下圖所示的性能基本優化法則：

數據庫訪問優化漏斗?:去則

減少Sort

減少CPU及內存開銷CPU

開銷BinoVar

利

用

更

噌加資源多

資csdd"

源Htcpmog

這個優化法則歸納為5個層次：

1、減少數據訪問（減少磁盤訪問）

2、返回更少數據（減少網絡傳輸或磁盤訪問）

3、減少交互次數（減少網絡傳輸）

4、減少服務器CPU開銷（減少CPU及內存開銷）

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

因為每一層優化法則都是處理其對應硬件的性能問題，因此帶來的性能提升

百分比也不一樣樣。老式數據庫系統設計是也是盡也許對低速設備提供優化措

施，因此針對低速設備問題的可優化伎倆也更多，優化成本也更低。我們任何一

個SQL的性能優化都應當按這個規則由上到下來診療問題并提出處理方案，而不

應當首先想到的是增加資源處理問題。

如下是每個優化法則層級對應優化效果及成本經驗參考：

優化法則性能提升效果優化成本

減少數據訪問1-1000低

返回更少數據1-100低

減少交互次數1~20低

減少服務器CPU開銷1~5低

利用更多資源@~10局

接下來，我們針對5種優化法則列舉常用的優化伎倆并結合實例分析。

二、Oracle數據庫兩個基本概念

數據塊(Block)

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

單位，一個數據塊一般能夠存儲多條統計，數據塊大小是DBA在創建數據庫或

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

據庫經典的物理結構，一個數據庫能夠包括多個數據文獻，一個數據文獻內乂包

括多個數據塊;

Datafile2DatafileN

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

ROWID

ROWID是每條統計在數據庫中的唯一標識，通過ROWID能夠直接定位統計

到對應的文獻號及數據塊位置。ROWID內容包括文獻號、對像號、數據塊號、

三、數據庫訪問優化法則詳解

1、減少數據訪問

1.1.創建并使用正確的索引

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

少，雖然是專業的DBA也不一定能完全做到最優。

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

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

此在一個表中創建什么樣的索引需要平衡各種業務需求。

索引常見問題：

索引有哪些種類？

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

庫應用，全文索引因為使用較少，這里不深入簡介。B-TREE索引包括諸多擴展類

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

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

目錄結構，重要用于提升查詢性能和唯一約束支持。B-TREE索引的內容包括根節

點、分支節點、葉子節點。

葉子節點內容：索引字段內容十表統計ROWID

根節點，分支節點內容：當一個數據塊中不能放下所有索引字段數據時，就

會形成樹形的根節點或分支節點，根節點與分支節點保存了索引樹的次序及各層

級間的引用關系。

一個一般的BTREE索引結構示意圖如下所示:

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

所示:

根節點

?際E城

Sg,>

8t?*CM

8

tt?

at?

■

*a常

zt?

葉子節點

?

分支節點！界

鄧T4

r■

六

^羽

sBaj

fl?/

r：Z

Z

gfll

tt刈lg

c^B?

?

ys號e

f葉子節點內到數據

七

?

llrv埃號的指計

Si*l*：le

f?<

?>|R

黑t2'

em?

部

S案H;

a<

￡織u

s

一

分支節點2oS+

刈4

t

湍T1

f>,

w?

t

羽

登?

*

?

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

合索引。

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

部首等等）。

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

的組合目錄。

SQL什么條件會使用索引？

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

INDEXCOLUMN=?

INDEX_COLUMN>?

INDEX_COLUMN>=?

INDEX_COLUMN<?

INDEX_COLUMN<=?

INDEX_COLUMNbetween?and?

INDEX_COLUMNin(??...,?)

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

Tl.INDEX_COLUMN=T2.COLUMN1(兩個表通過索引字段關聯)

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

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

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

INDEX_COLUMNnotin

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

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

INDEX_COLUMN11'a'=?

INDEX_COLUMNlike'%'||?含前導含糊查詢的Like語法不能使用

INDEX_COLUMNlike索弓1

INDEXCOLUMNisnullB-TREE索引里不保存字段為NULL值統

計，因此ISNULL不能使用索引

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

CHAR_INDEX_COLUMN=12345數據轉換成同一個數據類型，假如兩邊

數據類型不一樣時會對字段值隱式轉

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

能使用索引。

a.lNDEX_COLUMN=a.COLUMN_l給索引查詢的值應是己知數據，不能是

未知字段值。

注：

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

DBA溝通。

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

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

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

索引的

Select*fromcompanywherename=?

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

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

更差。

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

這是一個非常復雜的話題，需要對業務及數據充足分析后再能得出成果。主

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

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

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

3、通過字段條件可篩選的統計集很小，那數據篩選百分比是多少才適合？

這個沒有固定值，需要依照表數據量來評定，如下是經驗公式，可用于迅速

評定：

小表（統計數小于10000行的表）：篩選百分比＜10%;

大表：（篩選返回統計數）＜（表總統計數*單條統計長度）/10000/16

單條統計長度?字段平均內容長度之和十字段數*2

如下是某些字段是否需要建B-TREE索引的經驗分類:

字

常見字段名

段

類

型

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

對/

像

或

身

份

標

識

意

義

字

段

索引

DIFIED

_MO

GMT

ATE,

_CRE

GMT

慎用日

字期

TH

,MON

YEAR

段年

，

需要月狀

FLAG

,VIP_

LETE

S_DE

TUS,I

_STA

RDER

US,O

STAT

UCT_

進行態PROD

數據標

分布志

及使類

TYPE

NCY_

URRE

DER,C

,GEN

TYPE

AGE_

PE,IM

R_TY

用場型ORDE

景詳

細評

CITY

NCE,

ROVI

TRY,P

COUN

區

定

域操

R

UDITO

TOR,A

作CREA

人

員

數

RE

T,SCO

OUN

L,AM

值LEVE

長

JECT

Y,SUB

MAR

E,SUM

NAM

ANY_

OMP

ESS,C

字ADDR

符

描

ETAIL

MO,D

K