資料庫優化效能解析

青盞發表於2018-02-25

1.資料庫訪問優化法則


要正確的優化SQL,我們需要快速定位能性的瓶頸點,也就是說快速找到我們SQL主要的開銷在哪裡?而大多數情況效能最慢的裝置會是瓶頸點,如下載時網路速度可能會是瓶頸點,本地複製檔案時硬碟可能會是瓶頸點,為什麼這些一般的工作我們能快速確認瓶頸點呢,因為我們對這些慢速裝置的效能資料有一些基本的認識,如網路頻寬是2Mbps,硬碟是每分鐘7200轉等等。因此,為了快速找到SQL的效能瓶頸點,我們也需要了解我們計算機系統的硬體基本效能指標,下圖展示的當前主流計算機效能指標資料。

 

從圖上可以看到基本上每種裝置都有兩個指標:

延時(響應時間):表示硬體的突發處理能力;

頻寬(吞吐量):代表硬體持續處理能力。

 

從上圖可以看出,計算機系統硬體效能從高到代依次為:

CPU——Cache(L1-L2-L3)——記憶體——SSD硬碟——網路——硬碟

由於SSD硬碟還處於快速發展階段,所以本文的內容不涉及SSD相關應用系統。

根據資料庫知識,我們可以列出每種硬體主要的工作內容:

CPU及記憶體:快取資料訪問、比較、排序、事務檢測、SQL解析、函式或邏輯運算;

網路:結果資料傳輸、SQL請求、遠端資料庫訪問(dblink);

硬碟:資料訪問、資料寫入、日誌記錄、大資料量排序、大表連線。

 

根據當前計算機硬體的基本效能指標及其在資料庫中主要操作內容,可以整理出如下圖所示的效能基本優化法則:

 

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

1、  減少資料訪問(減少磁碟訪問)

2、  返回更少資料(減少網路傳輸或磁碟訪問)

3、  減少互動次數(減少網路傳輸)

4、  減少伺服器CPU開銷(減少CPU及記憶體開銷)

5、  利用更多資源(增加資源)

 

由於每一層優化法則都是解決其對應硬體的效能問題,所以帶來的效能提升比例也不一樣。傳統資料庫系統設計是也是儘可能對低速裝置提供優化方法,因此針對低速裝置問題的可優化手段也更多,優化成本也更低。我們任何一個SQL的效能優化都應該按這個規則由上到下來診斷問題並提出解決方案,而不應該首先想到的是增加資源解決問題。

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

 

優化法則

效能提升效果

優化成本

減少資料訪問

1~1000

返回更少資料

1~100

減少互動次數

1~20

減少伺服器CPU開銷

1~5

利用更多資源

@~10

 

接下來,我們針對5種優化法則列舉常用的優化手段並結合例項分析。

 

二、Oracle資料庫兩個基本概念

資料塊(Block)

資料塊是資料庫中資料在磁碟中儲存的最小單位,也是一次IO訪問的最小單位,一個資料塊通常可以儲存多條記錄,資料塊大小是DBA在建立資料庫或表空間時指定,可指定為2K、4K、8K、16K或32K位元組。下圖是一個Oracle資料庫典型的物理結構,一個資料庫可以包括多個資料檔案,一個資料檔案內又包含多個資料塊;

 

 

ROWID

ROWID是每條記錄在資料庫中的唯一標識,通過ROWID可以直接定位記錄到對應的檔案號及資料塊位置。ROWID內容包括檔案號、對像號、資料塊號、記錄槽號,如下圖所示:

 

三、資料庫訪問優化法則詳解

1、減少資料訪問

1.1、建立並使用正確的索引

資料庫索引的原理非常簡單,但在複雜的表中真正能正確使用索引的人很少,即使是專業的DBA也不一定能完全做到最優。

索引會大大增加表記錄的DML(INSERT,UPDATE,DELETE)開銷,正確的索引可以讓效能提升100,1000倍以上,不合理的索引也可能會讓效能下降100倍,因此在一個表中建立什麼樣的索引需要平衡各種業務需求。

索引常見問題:

索引有哪些種類?

常見的索引有B-TREE索引、點陣圖索引、全文索引,點陣圖索引一般用於資料倉儲應用,全文索引由於使用較少,這裡不深入介紹。B-TREE索引包括很多擴充套件型別,如組合索引、反向索引、函式索引等等,以下是B-TREE索引的簡單介紹:

B-TREE索引也稱為平衡樹索引(Balance Tree),它是一種按欄位排好序的樹形目錄結構,主要用於提升查詢效能和唯一約束支援。B-TREE索引的內容包括根節點、分支節點、葉子節點。

葉子節點內容:索引欄位內容+表記錄ROWID

根節點,分支節點內容:當一個資料塊中不能放下所有索引欄位資料時,就會形成樹形的根節點或分支節點,根節點與分支節點儲存了索引樹的順序及各層級間的引用關係。

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

 

 

如果我們把一個表的內容認為是一本字典,那索引就相當於字典的目錄,如下圖所示:

 

 

 

 

圖中是一個字典按部首+筆劃數的目錄,相當於給字典建了一個按部首+筆劃的組合索引。

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

一個索引也可以由多個欄位組成,稱為組合索引,如上圖就是一個按部首+筆劃的組合目錄。

SQL什麼條件會使用索引?

當欄位上建有索引時,通常以下情況會使用索引:

INDEX_COLUMN = ?

INDEX_COLUMN > ?

INDEX_COLUMN >= ?

INDEX_COLUMN < ?

INDEX_COLUMN <= ?

INDEX_COLUMN between ? and ?

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

INDEX_COLUMN like ?||'%'(後導模糊查詢)

T1. INDEX_COLUMN=T2. COLUMN1(兩個表通過索引欄位關聯)

 

SQL什麼條件不會使用索引?

 

查詢條件

不能使用索引原因

INDEX_COLUMN <> ?

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

不等於操作不能使用索引

function(INDEX_COLUMN) = ?

INDEX_COLUMN + 1 = ?

INDEX_COLUMN || 'a' = ?

經過普通運算或函式運算後的索引欄位不能使用索引

INDEX_COLUMN like '%'||?

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

含前導模糊查詢的Like語法不能使用索引

INDEX_COLUMN is null

B-TREE索引裡不儲存欄位為NULL值記錄,因此IS NULL不能使用索引

NUMBER_INDEX_COLUMN='12345'

CHAR_INDEX_COLUMN=12345

Oracle在做數值比較時需要將兩邊的資料轉換成同一種資料型別,如果兩邊資料型別不同時會對欄位值隱式轉換,相當於加了一層函式處理,所以不能使用索引。

a.INDEX_COLUMN=a.COLUMN_1

給索引查詢的值應是已知資料,不能是未知欄位值。

注:

經過函式運算欄位的欄位要使用可以使用函式索引,這種需求建議與DBA溝通。

有時候我們會使用多個欄位的組合索引,如果查詢條件中第一個欄位不能使用索引,那整個查詢也不能使用索引

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

Select * from company where name=?

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

 

我們一般在什麼欄位上建索引?

這是一個非常複雜的話題,需要對業務及資料充分分析後再能得出結果。主鍵及外來鍵通常都要有索引,其它需要建索引的欄位應滿足以下條件:

1、欄位出現在查詢條件中,並且查詢條件可以使用索引;

2、語句執行頻率高,一天會有幾千次以上;

3、通過欄位條件可篩選的記錄集很小,那資料篩選比例是多少才適合?

這個沒有固定值,需要根據表資料量來評估,以下是經驗公式,可用於快速評估:

小表(記錄數小於10000行的表):篩選比例<10%;

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

      單條記錄長度≈欄位平均內容長度之和+欄位數*2

 

以下是一些欄位是否需要建B-TREE索引的經驗分類:

 

 

欄位型別

常見欄位名

需要建索引的欄位

主鍵

ID,PK

外來鍵

PRODUCT_ID,COMPANY_ID,MEMBER_ID,ORDER_ID,TRADE_ID,PAY_ID

有對像或身份標識意義欄位

HASH_CODE,USERNAME,IDCARD_NO,EMAIL,TEL_NO,IM_NO

索引慎用欄位,需要進行資料分佈及使用場景詳細評估

日期

GMT_CREATE,GMT_MODIFIED

年月

YEAR,MONTH

狀態標誌

PRODUCT_STATUS,ORDER_STATUS,IS_DELETE,VIP_FLAG

型別

ORDER_TYPE,IMAGE_TYPE,GENDER,CURRENCY_TYPE

區域

COUNTRY,PROVINCE,CITY

操作人員

CREATOR,AUDITOR

數值

LEVEL,AMOUNT,SCORE

長字元

ADDRESS,COMPANY_NAME,SUMMARY,SUBJECT

不適合建索引的欄位

描述備註

DESCRIPTION,REMARK,MEMO,DETAIL

大欄位

FILE_CONTENT,EMAIL_CONTENT

 

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

簡單SQL可以根據索引使用語法規則判斷,複雜的SQL不好辦,判斷SQL的響應時間是一種策略,但是這會受到資料量、主機負載及快取等因素的影響,有時資料全在快取裡,可能全表訪問的時間比索引訪問時間還少。要準確知道索引是否正確使用,需要到資料庫中檢視SQL真實的執行計劃,這個話題比較複雜,詳見SQL執行計劃專題介紹。

 

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

這個沒有固定的比例,與每個表記錄的大小及索引欄位大小密切相關,以下是一個普通表測試資料,僅供參考:

索引對於Insert效能降低56%

索引對於Update效能降低47%

索引對於Delete效能降低29%

因此對於寫IO壓力比較大的系統,表的索引需要仔細評估必要性,另外索引也會佔用一定的儲存空間。

 

1.2、只通過索引訪問資料

有些時候,我們只是訪問表中的幾個欄位,並且欄位內容較少,我們可以為這幾個欄位單獨建立一個組合索引,這樣就可以直接只通過訪問索引就能得到資料,一般索引佔用的磁碟空間比表小很多,所以這種方式可以大大減少磁碟IO開銷。

如:select id,name from company where type='2';

如果這個SQL經常使用,我們可以在type,id,name上建立組合索引

create index my_comb_index on company(type,id,name);

有了這個組合索引後,SQL就可以直接通過my_comb_index索引返回資料,不需要訪問company表。

還是拿字典舉例:有一個需求,需要查詢一本漢語字典中所有漢字的個數,如果我們的字典沒有目錄索引,那我們只能從字典內容裡一個一個字計數,最後返回結果。如果我們有一個拼音目錄,那就可以只訪問拼音目錄的漢字進行計數。如果一本字典有1000頁,拼音目錄有20頁,那我們的資料訪問成本相當於全表訪問的50分之一。

切記,效能優化是無止境的,當效能可以滿足需求時即可,不要過度優化。在實際資料庫中我們不可能把每個SQL請求的欄位都建在索引裡,所以這種只通過索引訪問資料的方法一般只用於核心應用,也就是那種對核心表訪問量最高且查詢欄位資料量很少的查詢。

1.3、優化SQL執行計劃

SQL執行計劃是關係型資料庫最核心的技術之一,它表示SQL執行時的資料訪問演算法。由於業務需求越來越複雜,表資料量也越來越大,程式設計師越來越懶惰,SQL也需要支援非常複雜的業務邏輯,但SQL的效能還需要提高,因此,優秀的關係型資料庫除了需要支援複雜的SQL語法及更多函式外,還需要有一套優秀的演算法庫來提高SQL效能。

目前ORACLE有SQL執行計劃的演算法約300種,而且一直在增加,所以SQL執行計劃是一個非常複雜的課題,一個普通DBA能掌握50種就很不錯了,就算是資深DBA也不可能把每個執行計劃的演算法描述清楚。雖然有這麼多種演算法,但並不表示我們無法優化執行計劃,因為我們常用的SQL執行計劃演算法也就十幾個,如果一個程式設計師能把這十幾個演算法搞清楚,那就掌握了80%的SQL執行計劃調優知識。

由於篇幅的原因,SQL執行計劃需要專題介紹,在這裡就不多說了。

 

2、返回更少的資料

2.1、資料分頁處理

一般資料分頁方式有:

2.1.1、客戶端(應用程式或瀏覽器)分頁

將資料從應用伺服器全部下載到本地應用程式或瀏覽器,在應用程式或瀏覽器內部通過原生程式碼進行分頁處理

優點:編碼簡單,減少客戶端與應用伺服器網路互動次數

缺點:首次互動時間長,佔用客戶端記憶體

適應場景:客戶端與應用伺服器網路延時較大,但要求後續操作流暢,如手機GPRS,超遠端訪問(跨國)等等。

2.1.2、應用伺服器分頁

將資料從資料庫伺服器全部下載到應用伺服器,在應用伺服器內部再進行資料篩選。以下是一個應用伺服器端Java程式分頁的示例:

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

Int count= list.size();

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

 

優點:編碼簡單,只需要一次SQL互動,總資料與分頁資料差不多時效能較好。

缺點:總資料量較多時效能較差。

適應場景:資料庫系統不支援分頁處理,資料量較小並且可控。

 

2.1.3、資料庫SQL分頁

採用資料庫SQL分頁需要兩次SQL完成

一個SQL計算總數量

一個SQL返回分頁後的資料

優點:效能好

缺點:編碼複雜,各種資料庫語法不同,需要兩次SQL互動。

 

oracle資料庫一般採用rownum來進行分頁,常用分頁語法有如下兩種:

 

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

資料訪問開銷=索引IO+索引全部記錄結果對應的表資料IO

 

採用rowid分頁語法

優化原理是通過純索引找出分頁記錄的ROWID,再通過ROWID回表返回資料,要求內層查詢和排序欄位全在索引裡。

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;

資料訪問開銷=索引IO+索引分頁結果對應的表資料IO

 

例項:

一個公司產品有1000條記錄,要分頁取其中20個產品,假設訪問公司索引需要50個IO,2條記錄需要1個表資料IO。

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

 

2.2、只返回需要的欄位

通過去除不必要的返回欄位可以提高效能,例:

調整前:select * from product where company_id=?;

調整後:select id,name from product where company_id=?;

 

優點:

1、減少資料在網路上傳輸開銷

2、減少伺服器資料處理開銷

3、減少客戶端記憶體佔用

4、欄位變更時提前發現問題,減少程式BUG

5、如果訪問的所有欄位剛好在一個索引裡面,則可以使用純索引訪問提高效能。

缺點:增加編碼工作量

由於會增加一些編碼工作量,所以一般需求通過開發規範來要求程式設計師這麼做,否則等專案上線後再整改工作量更大。

如果你的查詢表中有大欄位或內容較多的欄位,如備註資訊、檔案內容等等,那在查詢表時一定要注意這方面的問題,否則可能會帶來嚴重的效能問題。如果表經常要查詢並且請求大內容欄位的概率很低,我們可以採用分表處理,將一個大表分拆成兩個一對一的關係表,將不常用的大內容欄位放在一張單獨的表中。如一張儲存上傳檔案的表:

T_FILE(ID,FILE_NAME,FILE_SIZE,FILE_TYPE,FILE_CONTENT)

我們可以分拆成兩張一對一的關係表:

T_FILE(ID,FILE_NAME,FILE_SIZE,FILE_TYPE)

T_FILECONTENT(ID, FILE_CONTENT)

         通過這種分拆,可以大大提少T_FILE表的單條記錄及總大小,這樣在查詢T_FILE時效能會更好,當需要查詢FILE_CONTENT欄位內容時再訪問T_FILECONTENT表。

 

3、減少互動次數

3.1、batch DML

資料庫訪問框架一般都提供了批量提交的介面,jdbc支援batch的提交處理方法,當你一次性要往一個表中插入1000萬條資料時,如果採用普通的executeUpdate處理,那麼和伺服器互動次數為1000萬次,按每秒鐘可以向資料庫伺服器提交10000次估算,要完成所有工作需要1000秒。如果採用批量提交模式,1000條提交一次,那麼和伺服器互動次數為1萬次,互動次數大大減少。採用batch操作一般不會減少很多資料庫伺服器的物理IO,但是會大大減少客戶端與服務端的互動次數,從而減少了多次發起的網路延時開銷,同時也會降低資料庫的CPU開銷。

 

假設要向一個普通表插入1000萬資料,每條記錄大小為1K位元組,表上沒有任何索引,客戶端與資料庫伺服器網路是100Mbps,以下是根據現在一般計算機能力估算的各種batch大小效能對比值:

 

 單位:ms

No batch

Batch=10

Batch=100

Batch=1000

Batch=10000

伺服器事務處理時間

0.1

0.1

0.1

0.1

0.1

伺服器IO處理時間

0.02

0.2

2

20

200

網路互動發起時間

0.1

0.1

0.1

0.1

0.1

網路資料傳輸時間

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倍效能,一般根據主鍵的Update或Delete操作也可能提高2-3倍效能,但不如Insert明顯,因為Update及Delete操作可能有比較大的開銷在物理IO訪問。以上僅是理論計算值,實際情況需要根據具體環境測量。

 

3.2、In List

很多時候我們需要按一些ID查詢資料庫記錄,我們可以採用一個ID一個請求發給資料庫,如下所示:

for :var in ids[] do begin

  select * from mytable where id=:var;

end;

 

我們也可以做一個小的優化, 如下所示,用ID INLIST的這種方式寫SQL:

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

 

通過這樣處理可以大大減少SQL請求的數量,從而提高效能。那如果有10000個ID,那是不是全部放在一條SQL裡處理呢?答案肯定是否定的。首先大部份資料庫都會有SQL長度和IN裡個數的限制,如ORACLE的IN裡就不允許超過1000個值

另外當前資料庫一般都是採用基於成本的優化規則,當IN數量達到一定值時有可能改變SQL執行計劃,從索引訪問變成全表訪問,這將使效能急劇變化。隨著SQL中IN的裡面的值個數增加,SQL的執行計劃會更復雜,佔用的記憶體將會變大,這將會增加伺服器CPU及記憶體成本。

評估在IN裡面一次放多少個值還需要考慮應用伺服器本地記憶體的開銷,有併發訪問時要計算本地資料使用週期內的併發上限,否則可能會導致記憶體溢位。

綜合考慮,一般IN裡面的值個數超過20個以後效能基本沒什麼太大變化,也特別說明不要超過100,超過後可能會引起執行計劃的不穩定性及增加資料庫CPU及記憶體成本,這個需要專業DBA評估。

 

3.3、設定Fetch Size

當我們採用select從資料庫查詢資料時,資料預設並不是一條一條返回給客戶端的,也不是一次全部返回客戶端的,而是根據客戶端fetch_size引數處理,每次只返回fetch_size條記錄,當客戶端遊標遍歷到尾部時再從服務端取資料,直到最後全部傳送完成。所以如果我們要從服務端一次取大量資料時,可以加大fetch_size,這樣可以減少結果資料傳輸的互動次數及伺服器資料準備時間,提高效能。

 

以下是jdbc測試的程式碼,採用本地資料庫,表快取在資料庫CACHE中,因此沒有網路連線及磁碟IO開銷,客戶端只遍歷遊標,不做任何處理,這樣更能體現fetch引數的影響:

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位元組

 

以下是測試結果,對每種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

 

 

 

Oracle jdbc fetchsize預設值為10,由上測試可以看出fetchsize對效能影響還是比較大的,但是當fetchsize大於100時就基本上沒有影響了。fetchsize並不會存在一個最優的固定值,因為整體效能與記錄集大小及硬體平臺有關。根據測試結果建議當一次性要取大量資料時這個值設定為100左右,不要小於40。注意,fetchsize不能設定太大,如果一次取出的資料大於JVM的記憶體會導致記憶體溢位,所以建議不要超過1000,太大了也沒什麼效能提高,反而可能會增加記憶體溢位的危險。

注:圖中fetchsize在128以後會有一些小的波動,這並不是測試誤差,而是由於resultset填充到具體對像時間不同的原因,由於resultset已經到本地記憶體裡了,所以估計是由於CPU的L1,L2 Cache命中率變化造成,由於變化不大,所以筆者也未深入分析原因。

 

iBatis的SqlMapping配置檔案可以對每個SQL語句指定fetchsize大小,如下所示:

 

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

select * from employee

</select>

 

3.4、使用儲存過程

大型資料庫一般都支援儲存過程,合理的利用儲存過程也可以提高系統效能。如你有一個業務需要將A表的資料做一些加工然後更新到B表中,但是又不可能一條SQL完成,這時你需要如下3步操作:

a:將A表資料全部取出到客戶端;

b:計算出要更新的資料;

c:將計算結果更新到B表。

 

如果採用儲存過程你可以將整個業務邏輯封裝在儲存過程裡,然後在客戶端直接呼叫儲存過程處理,這樣可以減少網路互動的成本。

當然,儲存過程也並不是十全十美,儲存過程有以下缺點:

a、不可移植性,每種資料庫的內部程式設計語法都不太相同,當你的系統需要相容多種資料庫時最好不要用儲存過程。

b、學習成本高,DBA一般都擅長寫儲存過程,但並不是每個程式設計師都能寫好儲存過程,除非你的團隊有較多的開發人員熟悉寫儲存過程,否則後期系統維護會產生問題。

c、業務邏輯多處存在,採用儲存過程後也就意味著你的系統有一些業務邏輯不是在應用程式裡處理,這種架構會增加一些系統維護和除錯成本。

d、儲存過程和常用應用程式語言不一樣,它支援的函式及語法有可能不能滿足需求,有些邏輯就只能通過應用程式處理。

e、如果儲存過程中有複雜運算的話,會增加一些資料庫服務端的處理成本,對於集中式資料庫可能會導致系統可擴充套件性問題。

f、為了提高效能,資料庫會把儲存過程程式碼編譯成中間執行程式碼(類似於java的class檔案),所以更像靜態語言。當儲存過程引用的對像(表、檢視等等)結構改變後,儲存過程需要重新編譯才能生效,在24*7高併發應用場景,一般都是線上變更結構的,所以在變更的瞬間要同時編譯儲存過程,這可能會導致資料庫瞬間壓力上升引起故障(Oracle資料庫就存在這樣的問題)。

 

個人觀點:普通業務邏輯儘量不要使用儲存過程,定時性的ETL任務或報表統計函式可以根據團隊資源情況採用儲存過程處理。

 

3.5、優化業務邏輯

要通過優化業務邏輯來提高效能是比較困難的,這需要程式設計師對所訪問的資料及業務流程非常清楚。

舉一個案例:

某移動公司推出優惠套參,活動對像為VIP會員並且2010年1,2,3月平均話費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

  執行套參();

end;

 

如果我們修改業務邏輯為:

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

    執行套參();

  end;

end;

通過這樣可以減少一些判斷vip_flag的開銷,平均話費20元以下的使用者就不需要再檢測是否VIP了。

 

如果程式設計師分析業務,VIP會員比例為1%,平均話費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

    執行套參();

  end;

end;

這樣就只有1%的VIP會員才會做檢測平均話費,最終大大減少了SQL的互動次數。

 

以上只是一個簡單的示例,實際的業務總是比這複雜得多,所以一般只是高階程式設計師更容易做出優化的邏輯,但是我們需要有這樣一種成本優化的意識。

 

3.6、使用ResultSet遊標處理記錄

現在大部分Java框架都是通過jdbc從資料庫取出資料,然後裝載到一個list裡再處理,list裡可能是業務Object,也可能是hashmap。

由於JVM記憶體一般都小於4G,所以不可能一次通過sql把大量資料裝載到list裡。為了完成功能,很多程式設計師喜歡採用分頁的方法處理,如一次從資料庫取1000條記錄,通過多次迴圈搞定,保證不會引起JVM Out of memory問題。

 

以下是實現此功能的程式碼示例,t_employee表有10萬條記錄,設定分頁大小為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();

}

 

以上程式碼實際執行時間為6.516秒

 

很多持久層框架為了儘量讓程式設計師使用方便,封裝了jdbc通過statement執行資料返回到resultset的細節,導致程式設計師會想採用分頁的方式處理問題。實際上如果我們採用jdbc原始的resultset遊標處理記錄,在resultset迴圈讀取的過程中處理記錄,這樣就可以一次從資料庫取出所有記錄。顯著提高效能。

這裡需要注意的是,採用resultset遊標處理記錄時,應該將遊標的開啟方式設定為FORWARD_READONLY模式(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY),否則會把結果快取在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);

         }

}

調整後的程式碼實際執行時間為3.156秒

 

從測試結果可以看出效能提高了1倍多,如果採用分頁模式資料庫每次還需發生磁碟IO的話那效能可以提高更多。

iBatis等持久層框架考慮到會有這種需求,所以也有相應的解決方案,在iBatis裡我們不能採用queryForList的方法,而應用該採用queryWithRowHandler加回撥事件的方式處理,如下所示:

 

MyRowHandler myrh=new MyRowHandler();

sqlmap.queryWithRowHandler("getAllEmployee", myrh);

 

class MyRowHandler implements RowHandler {

    public void handleRow(Object o) {

       //todo something

    }

}

 

iBatis的queryWithRowHandler很好的封裝了resultset遍歷的事件處理,效果及效能與resultset遍歷一樣,也不會產生JVM記憶體溢位。

 

4、減少資料庫伺服器CPU運算

4.1、使用繫結變數

繫結變數是指SQL中對變化的值採用變數引數的形式提交,而不是在SQL中直接拼寫對應的值。

非繫結變數寫法:Select * from employee where id=1234567

繫結變數寫法:

Select * from employee where id=?

Preparestatement.setInt(1,1234567)

 

Java中Preparestatement就是為處理繫結變數提供的對像,繫結變數有以下優點:

1、防止SQL隱碼攻擊

2、提高SQL可讀性

3、提高SQL解析效能,不使用繫結變更我們一般稱為硬解析,使用繫結變數我們稱為軟解析。

第1和第2點很好理解,做編碼的人應該都清楚,這裡不詳細說明。關於第3點,到底能提高多少效能呢,下面舉一個例子說明:

 

假設有這個這樣的一個資料庫主機:

2個4核CPU 

100塊磁碟,每個磁碟支援IOPS為160

業務應用的SQL如下:

select * from table where pk=?

這個SQL平均4個IO(3個索引IO+1個資料IO)

IO快取命中率75%(索引全在記憶體中,資料需要訪問磁碟)

SQL硬解析CPU消耗:1ms  (常用經驗值)

SQL軟解析CPU消耗:0.02ms(常用經驗值)

 

假設CPU每核效能是線性增長,訪問記憶體Cache中的IO時間忽略,要求計算系統對如上應用採用硬解析與採用軟解析支援的每秒最大併發數:

 

是否使用繫結變數

CPU支援最大併發數

磁碟IO支援最大併發數

不使用

2*4*1000=8000

100*160=16000

使用

2*4*1000/0.02=400000

100*160=16000

 

從以上計算可以看出,不使用繫結變數的系統當併發達到8000時會在CPU上產生瓶頸,當使用繫結變數的系統當並行達到16000時會在磁碟IO上產生瓶頸。所以如果你的系統CPU有瓶頸時請先檢查是否存在大量的硬解析操作。

 

使用繫結變數為何會提高SQL解析效能,這個需要從資料庫SQL執行原理說明,一條SQL在Oracle資料庫中的執行過程如下圖所示:

 

 

 

當一條SQL傳送給資料庫伺服器後,系統首先會將SQL字串進行hash運算,得到hash值後再從伺服器記憶體裡的SQL快取區中進行檢索,如果有相同的SQL字元,並且確認是同一邏輯的SQL語句,則從共享池快取中取出SQL對應的執行計劃,根據執行計劃讀取資料並返回結果給客戶端。

如果在共享池中未發現相同的SQL則根據SQL邏輯生成一條新的執行計劃並儲存在SQL快取區中,然後根據執行計劃讀取資料並返回結果給客戶端。

為了更快的檢索SQL是否在快取區中,首先進行的是SQL字串hash值對比,如果未找到則認為沒有快取,如果存在再進行下一步的準確對比,所以要命中SQL快取區應保證SQL字元是完全一致,中間有大小寫或空格都會認為是不同的SQL。

如果我們不採用繫結變數,採用字串拼接的模式生成SQL,那麼每條SQL都會產生執行計劃,這樣會導致共享池耗盡,快取命中率也很低。

 

一些不使用繫結變數的場景:

a、資料倉儲應用,這種應用一般併發不高,但是每個SQL執行時間很長,SQL解析的時間相比SQL執行時間比較小,繫結變數對效能提高不明顯。資料倉儲一般都是內部分析應用,所以也不太會發生SQL隱碼攻擊的安全問題。

b、資料分佈不均勻的特殊邏輯,如產品表,記錄有1億,有一產品狀態欄位,上面建有索引,有稽核中,稽核通過,稽核未通過3種狀態,其中稽核通過9500萬,稽核中1萬,稽核不通過499萬。

要做這樣一個查詢:

select count(*) from product where status=?

採用繫結變數的話,那麼只會有一個執行計劃,如果走索引訪問,那麼對於稽核中查詢很快,對稽核通過和稽核不通過會很慢;如果不走索引,那麼對於稽核中與稽核通過和稽核不通過時間基本一樣;

對於這種情況應該不使用繫結變數,而直接採用字元拼接的方式生成SQL,這樣可以為每個SQL生成不同的執行計劃,如下所示。

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

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

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

 

4.2、合理使用排序

Oracle的排序演算法一直在優化,但是總體時間複雜度約等於nLog(n)。普通OLTP系統排序操作一般都是在記憶體裡進行的,對於資料庫來說是一種CPU的消耗,曾在PC機做過測試,單核普通CPU在1秒鐘可以完成100萬條記錄的全記憶體排序操作,所以說由於現在CPU的效能增強,對於普通的幾十條或上百條記錄排序對系統的影響也不會很大。但是當你的記錄集增加到上萬條以上時,你需要注意是否一定要這麼做了,大記錄集排序不僅增加了CPU開銷,而且可能會由於記憶體不足發生硬碟排序的現象,當發生硬碟排序時效能會急劇下降,這種需求需要與DBA溝通再決定,取決於你的需求和資料,所以只有你自己最清楚,而不要被別人說排序很慢就嚇倒。

以下列出了可能會發生排序操作的SQL語法:

Order by

Group by

Distinct

Exists子查詢

Not Exists子查詢

In子查詢

Not In子查詢

Union(並集),Union All也是一種並集操作,但是不會發生排序,如果你確認兩個資料集不需要執行去除重複資料操作,那請使用Union All 代替Union。

Minus(差集)

Intersect(交集)

Create Index

Merge Join,這是一種兩個表連線的內部演算法,執行時會把兩個表先排序好再連線,應用於兩個大表連線的操作。如果你的兩個表連線的條件都是等值運算,那可以採用Hash Join來提高效能,因為Hash Join使用Hash 運算來代替排序的操作。具體原理及設定參考SQL執行計劃優化專題。

 

4.3、減少比較操作

我們SQL的業務邏輯經常會包含一些比較操作,如a=b,a<b之類的操作,對於這些比較運算元據庫都體現得很好,但是如果有以下操作,我們需要保持警惕:

Like模糊查詢,如下所示:

a like ‘%abc%’

 

Like模糊查詢對於資料庫來說不是很擅長,特別是你需要模糊檢查的記錄有上萬條以上時,效能比較糟糕,這種情況一般可以採用專用Search或者採用全文索引方案來提高效能。

不能使用索引定位的大量In List,如下所示:

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

如果這裡的a欄位不能通過索引比較,那資料庫會將欄位與in裡面的每個值都進行比較運算,如果記錄數有上萬以上,會明顯感覺到SQL的CPU開銷加大,這個情況有兩種解決方式:

a、  將in列表裡面的資料放入一張中間小表,採用兩個表Hash Join關聯的方式處理;

b、  採用str2varList方法將欄位串列表轉換一個臨時表處理,關於str2varList方法可以在網上直接查詢,這裡不詳細介紹。

 

以上兩種解決方案都需要與中間表Hash Join的方式才能提高效能,如果採用了Nested Loop的連線方式效能會更差。

如果發現我們的系統IO沒問題但是CPU負載很高,就有可能是上面的原因,這種情況不太常見,如果遇到了最好能和DBA溝通並確認準確的原因。

 

4.4、大量複雜運算在客戶端處理

什麼是複雜運算,一般我認為是一秒鐘CPU只能做10萬次以內的運算。如含小數的對數及指數運算、三角函式、3DES及BASE64資料加密演算法等等。

如果有大量這類函式運算,儘量放在客戶端處理,一般CPU每秒中也只能處理1萬-10萬次這樣的函式運算,放在資料庫內不利於高併發處理。

 

5、利用更多的資源

5.1、客戶端多程式並行訪問

多程式並行訪問是指在客戶端建立多個程式(執行緒),每個程式建立一個與資料庫的連線,然後同時向資料庫提交訪問請求。當資料庫主機資源有空閒時,我們可以採用客戶端多程式並行訪問的方法來提高效能。如果資料庫主機已經很忙時,採用多程式並行訪問效能不會提高,反而可能會更慢。所以使用這種方式最好與DBA或系統管理員進行溝通後再決定是否採用。

 

例如:

我們有10000個產品ID,現在需要根據ID取出產品的詳細資訊,如果單執行緒訪問,按每個IO要5ms計算,忽略主機CPU運算及網路傳輸時間,我們需要50s才能完成任務。如果採用5個並行訪問,每個程式訪問2000個ID,那麼10s就有可能完成任務。

那是不是並行數越多越好呢,開1000個並行是否只要50ms就搞定,答案肯定是否定的,當並行數超過伺服器主機資源的上限時效能就不會再提高,如果再增加反而會增加主機的程式間排程成本和程式衝突機率。

 

以下是一些如何設定並行數的基本建議:

如果瓶頸在伺服器主機,但是主機還有空閒資源,那麼最大並行數取主機CPU核數和主機提供資料服務的磁碟數兩個引數中的最小值,同時要保證主機有資源做其它任務。

如果瓶頸在客戶端處理,但是客戶端還有空閒資源,那建議不要增加SQL的並行,而是用一個程式取回資料後在客戶端起多個程式處理即可,程式數根據客戶端CPU核數計算。

如果瓶頸在客戶端網路,那建議做資料壓縮或者增加多個客戶端,採用map reduce的架構處理。

如果瓶頸在伺服器網路,那需要增加伺服器的網路頻寬或者在服務端將資料壓縮後再處理了。

 

5.2、資料庫並行處理

資料庫並行處理是指客戶端一條SQL的請求,資料庫內部自動分解成多個程式並行處理,如下圖所示:

 

 

並不是所有的SQL都可以使用並行處理,一般只有對錶或索引進行全部訪問時才可以使用並行。資料庫表預設是不開啟並行訪問,所以需要指定SQL並行的提示,如下所示:

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

 

並行的優點:

使用多程式處理,充分利用資料庫主機資源(CPU,IO),提高效能。

並行的缺點:

1、單個會話佔用大量資源,影響其它會話,所以只適合在主機負載低時期使用;

2、只能採用直接IO訪問,不能利用快取資料,所以執行前會觸發將髒快取資料寫入磁碟操作。

 

注:

1、並行處理在OLTP類系統中慎用,使用不當會導致一個會話把主機資源全部佔用,而正常事務得不到及時響應,所以一般只是用於資料倉儲平臺。

2、一般對於百萬級記錄以下的小表採用並行訪問效能並不能提高,反而可能會讓效能更差。


SQL效能優化  --- 面試題

今天面試,我簡歷上寫了熟悉sql的效能優化,但是今天面試,一時想不起別的,就僅僅說出了一條,在這裡再總結一些,完善自己的知識點。

我經常用的資料庫是oracle,所以我的sql優化是程式設計師針對於oracle的。


總結,這個sql優化是針對程式設計師的,而不是針對dba的,主要就是第一,儘量防止模糊,明確指出,即用列名代替*,第二,在where語句上下工夫。第三多表查詢和子查詢,第四儘量使用繫結。



資料庫效能優化之SQL語句優化1

一、問題的提出

在應用系統開發初期,由於開發資料庫資料比較少,對於查詢SQL語句,複雜檢視的的編寫等體會不出SQL語句各種寫法的效能優劣,但是如果將應用系統提交實際應用後,隨著資料庫中資料的增加,系統的響應速度就成為目前系統需要解決的最主要的問題之一。系統優化中一個很重要的方面就是SQL語句的優化。對於海量資料,劣質SQL語句和優質SQL語句之間的速度差別可以達到上百倍,可見對於一個系統不是簡單地能實現其功能就可,而是要寫出高質量的SQL語句,提高系統的可用性。

在多數情況下,Oracle使用索引來更快地遍歷表,優化器主要根據定義的索引來提高效能。但是,如果在SQL語句的where子句中寫的SQL程式碼不合理,就會造成優化器刪去索引而使用全表掃描,一般就這種SQL語句就是所謂的劣質SQL語句。在編寫SQL語句時我們應清楚優化器根據何種原則來刪除索引,這有助於寫出高效能的SQL語句。

二、SQL語句編寫注意問題

下面就某些SQL語句的where子句編寫中需要注意的問題作詳細介紹。在這些where子句中,即使某些列存在索引,但是由於編寫了劣質的SQL,系統在執行該SQL語句時也不能使用該索引,而同樣使用全表掃描,這就造成了響應速度的極大降低。

1. 操作符優化

(a) IN 操作符

用IN寫出來的SQL的優點是比較容易寫及清晰易懂,這比較適合現代軟體開發的風格。但是用IN的SQL效能總是比較低的,從Oracle執行的步驟來分析用IN的SQL與不用IN的SQL有以下區別:

ORACLE試圖將其轉換成多個表的連線,如果轉換不成功則先執行IN裡面的子查詢,再查詢外層的表記錄,如果轉換成功則直接採用多個表的連線方式查詢。由此可見用IN的SQL至少多了一個轉換的過程。一般的SQL都可以轉換成功,但對於含有分組統計等方面的SQL就不能轉換了。

推薦方案:在業務密集的SQL當中儘量不採用IN操作符,用EXISTS 方案代替。

(b) NOT IN操作符

此操作是強列不推薦使用的,因為它不能應用表的索引。

推薦方案:用NOT EXISTS 方案代替

(c) IS NULL 或IS NOT NULL操作(判斷欄位是否為空)

判斷欄位是否為空一般是不會應用索引的,因為索引是不索引空值的。不能用null作索引,任何包含null值的列都將不會被包含在索引中。即使索引有多列這樣的情況下,只要這些列中有一列含有null,該列就會從索引中排除。也就是說如果某列存在空值,即使對該列建索引也不會提高效能。任何在where子句中使用is null或is not null的語句優化器是不允許使用索引的。

推薦方案:用其它相同功能的操作運算代替,如:a is not null 改為 a>0 或a>’’等。不允許欄位為空,而用一個預設值代替空值,如申請中狀態欄位不允許為空,預設為申請。

(d) > 及 < 操作符(大於或小於操作符)

大於或小於操作符一般情況下是不用調整的,因為它有索引就會採用索引查詢,但有的情況下可以對它進行優化,如一個表有100萬記錄,一個數值型欄位A,30萬記錄的A=0,30萬記錄的A=1,39萬記錄的A=2,1萬記錄的A=3。那麼執行A>2與A>=3的效果就有很大的區別了,因為A>2時ORACLE會先找出為2的記錄索引再進行比較,而A>=3時ORACLE則直接找到=3的記錄索引。

(e) LIKE操作符

LIKE操作符可以應用萬用字元查詢,裡面的萬用字元組合可能達到幾乎是任意的查詢,但是如果用得不好則會產生效能上的問題,如LIKE ‘%5400%’ 這種查詢不會引用索引,而LIKE ‘X5400%’則會引用範圍索引。

一個實際例子:用YW_YHJBQK表中營業編號後面的戶標識號可來查詢營業編號 YY_BH LIKE ‘%5400%’ 這個條件會產生全表掃描,如果改成YY_BH LIKE ’X5400%’ OR YY_BH LIKE ’B5400%’ 則會利用YY_BH的索引進行兩個範圍的查詢,效能肯定大大提高。

帶萬用字元(%)的like語句:

同樣以上面的例子來看這種情況。目前的需求是這樣的,要求在職工表中查詢名字中包含cliton的人。可以採用如下的查詢SQL語句:

select * from employee where last_name like '%cliton%';

這裡由於萬用字元(%)在搜尋詞首出現,所以Oracle系統不使用last_name的索引。在很多情況下可能無法避免這種情況,但是一定要心中有底,萬用字元如此使用會降低查詢速度。然而當萬用字元出現在字串其他位置時,優化器就能利用索引。在下面的查詢中索引得到了使用:

select * from employee where last_name like 'c%';

(f) UNION操作符

UNION在進行錶連結後會篩選掉重複的記錄,所以在錶連結後會對所產生的結果集進行排序運算,刪除重複的記錄再返回結果。實際大部分應用中是不會產生重複的記錄,最常見的是過程表與歷史表UNION。如: 
select * from gc_dfys 
union 
select * from ls_jg_dfys 
這個SQL在執行時先取出兩個表的結果,再用排序空間進行排序刪除重複的記錄,最後返回結果集,如果表資料量大的話可能會導致用磁碟進行排序。

推薦方案:採用UNION ALL操作符替代UNION,因為UNION ALL操作只是簡單的將兩個結果合併後就返回。

select * from gc_dfys 
union all 
select * from ls_jg_dfys

(g) 聯接列

對於有聯接的列,即使最後的聯接值為一個靜態值,優化器是不會使用索引的。我們一起來看一個例子,假定有一個職工表(employee),對於一個職工的姓和名分成兩列存放(FIRST_NAME和LAST_NAME),現在要查詢一個叫比爾.克林頓(Bill Cliton)的職工。

下面是一個採用聯接查詢的SQL語句:

select * from employss where first_name||''||last_name ='Beill Cliton';

上面這條語句完全可以查詢出是否有Bill Cliton這個員工,但是這裡需要注意,系統優化器對基於last_name建立的索引沒有使用。當採用下面這種SQL語句的編寫,Oracle系統就可以採用基於last_name建立的索引。

*** where first_name ='Beill' and last_name ='Cliton';

(h) Order by語句

ORDER BY語句決定了Oracle如何將返回的查詢結果排序。Order by語句對要排序的列沒有什麼特別的限制,也可以將函式加入列中(象聯接或者附加等)。任何在Order by語句的非索引項或者有計算表示式都將降低查詢速度。

仔細檢查order by語句以找出非索引項或者表示式,它們會降低效能。解決這個問題的辦法就是重寫order by語句以使用索引,也可以為所使用的列建立另外一個索引,同時應絕對避免在order by子句中使用表示式。

(i) NOT

我們在查詢時經常在where子句使用一些邏輯表示式,如大於、小於、等於以及不等於等等,也可以使用and(與)、or(或)以及not(非)。NOT可用來對任何邏輯運算子號取反。下面是一個NOT子句的例子:

... where not (status ='VALID')

如果要使用NOT,則應在取反的短語前面加上括號,並在短語前面加上NOT運算子。NOT運算子包含在另外一個邏輯運算子中,這就是不等於(<>)運算子。換句話說,即使不在查詢where子句中顯式地加入NOT詞,NOT仍在運算子中,見下例:

... where status <>'INVALID';

對這個查詢,可以改寫為不使用NOT:

select * from employee where salary<3000 or salary>3000;

雖然這兩種查詢的結果一樣,但是第二種查詢方案會比第一種查詢方案更快些。第二種查詢允許Oracle對salary列使用索引,而第一種查詢則不能使用索引。

2. SQL書寫的影響

(a) 同一功能同一效能不同寫法SQL的影響。

如一個SQL在A程式設計師寫的為  Select * from zl_yhjbqk

B程式設計師寫的為 Select * from dlyx.zl_yhjbqk(帶表所有者的字首)

C程式設計師寫的為 Select * from DLYX.ZLYHJBQK(大寫表名)

D程式設計師寫的為 Select *  from DLYX.ZLYHJBQK(中間多了空格)

以上四個SQL在ORACLE分析整理之後產生的結果及執行的時間是一樣的,但是從ORACLE共享記憶體SGA的原理,可以得出ORACLE對每個SQL 都會對其進行一次分析,並且佔用共享記憶體,如果將SQL的字串及格式寫得完全相同,則ORACLE只會分析一次,共享記憶體也只會留下一次的分析結果,這不僅可以減少分析SQL的時間,而且可以減少共享記憶體重複的資訊,ORACLE也可以準確統計SQL的執行頻率。

(b) WHERE後面的條件順序影響

WHERE子句後面的條件順序對大資料量表的查詢會產生直接的影響。如: 
Select * from zl_yhjbqk where dy_dj = '1KV以下' and xh_bz=1 
Select * from zl_yhjbqk where xh_bz=1 and dy_dj = '1KV以下' 
以上兩個SQL中dy_dj(電壓等級)及xh_bz(銷戶標誌)兩個欄位都沒進行索引,所以執行的時候都是全表掃描,第一條SQL的dy_dj = '1KV以下'條件在記錄集內比率為99%,而xh_bz=1的比率只為0.5%,在進行第一條SQL的時候99%條記錄都進行dy_dj及xh_bz的比較,而在進行第二條SQL的時候0.5%條記錄都進行dy_dj及xh_bz的比較,以此可以得出第二條SQL的CPU佔用率明顯比第一條低。

(c) 查詢表順序的影響

在FROM後面的表中的列表順序會對SQL執行效能影響,在沒有索引及ORACLE沒有對錶進行統計分析的情況下,ORACLE會按表出現的順序進行連結,由此可見表的順序不對時會產生十分耗服物器資源的資料交叉。(注:如果對錶進行了統計分析,ORACLE會自動先進小表的連結,再進行大表的連結)

3. SQL語句索引的利用

(a) 對條件欄位的一些優化

採用函式處理的欄位不能利用索引,如:

substr(hbs_bh,1,4)=’5400’,優化處理:hbs_bh like ‘5400%’

trunc(sk_rq)=trunc(sysdate), 優化處理:sk_rq>=trunc(sysdate) and sk_rq<trunc(sysdate+1)

進行了顯式或隱式的運算的欄位不能進行索引,如:ss_df+20>50,優化處理:ss_df>30

‘X’ || hbs_bh>’X5400021452’,優化處理:hbs_bh>’5400021542’

sk_rq+5=sysdate,優化處理:sk_rq=sysdate-5

hbs_bh=5401002554,優化處理:hbs_bh=’ 5401002554’,注:此條件對hbs_bh 進行隱式的to_number轉換,因為hbs_bh欄位是字元型。

條件內包括了多個本表的欄位運算時不能進行索引,如:

ys_df>cx_df,無法進行優化 
qc_bh || kh_bh=’5400250000’,優化處理:qc_bh=’5400’ and kh_bh=’250000’

4. 更多方面SQL優化資料分享

(1) 選擇最有效率的表名順序(只在基於規則的優化器中有效):

ORACLE 的解析器按照從右到左的順序處理FROM子句中的表名,FROM子句中寫在最後的表(基礎表 driving table)將被最先處理,在FROM子句中包含多個表的情況下,你必須選擇記錄條數最少的表作為基礎表。如果有3個以上的表連線查詢, 那就需要選擇交叉表(intersection table)作為基礎表, 交叉表是指那個被其他表所引用的表.

(2) WHERE子句中的連線順序:

ORACLE採用自下而上的順序解析WHERE子句,根據這個原理,表之間的連線必須寫在其他WHERE條件之前, 那些可以過濾掉最大數量記錄的條件必須寫在WHERE子句的末尾.

(3) SELECT子句中避免使用 ‘ * ‘:

ORACLE在解析的過程中, 會將'*' 依次轉換成所有的列名, 這個工作是通過查詢資料字典完成的, 這意味著將耗費更多的時間。

(4) 減少訪問資料庫的次數:

ORACLE在內部執行了許多工作: 解析SQL語句, 估算索引的利用率, 繫結變數 , 讀資料塊等。

(5) 在SQL*Plus , SQL*Forms和Pro*C中重新設定ARRAYSIZE引數, 可以增加每次資料庫訪問的檢索資料量 ,建議值為200。

(6) 使用DECODE函式來減少處理時間:

使用DECODE函式可以避免重複掃描相同記錄或重複連線相同的表.

(7) 整合簡單,無關聯的資料庫訪問:

如果你有幾個簡單的資料庫查詢語句,你可以把它們整合到一個查詢中(即使它們之間沒有關係) 。

(8) 刪除重複記錄:

最高效的刪除重複記錄方法 ( 因為使用了ROWID)例子: 
DELETE  FROM  EMP E  WHERE  E.ROWID > (SELECT MIN(X.ROWID) FROM  EMP X  WHERE  X.EMP_NO = E.EMP_NO)。

(9) 用TRUNCATE替代DELETE:

當刪除表中的記錄時,在通常情況下, 回滾段(rollback segments ) 用來存放可以被恢復的資訊. 如果你沒有COMMIT事務,ORACLE會將資料恢復到刪除之前的狀態(準確地說是恢復到執行刪除命令之前的狀況) 而當運用TRUNCATE時, 回滾段不再存放任何可被恢復的資訊.當命令執行後,資料不能被恢復.因此很少的資源被呼叫,執行時間也會很短. (譯者按: TRUNCATE只在刪除全表適用,TRUNCATE是DDL不是DML) 。

(10) 儘量多使用COMMIT:

只要有可能,在程式中儘量多使用COMMIT, 這樣程式的效能得到提高,需求也會因為COMMIT所釋放的資源而減少,COMMIT所釋放的資源: 
a. 回滾段上用於恢復資料的資訊. 
b. 被程式語句獲得的鎖 
c. redo log buffer 中的空間 
d. ORACLE為管理上述3種資源中的內部花費

(11) 用Where子句替換HAVING子句:

避免使用HAVING子句, HAVING 只會在檢索出所有記錄之後才對結果集進行過濾. 這個處理需要排序,總計等操作. 如果能通過WHERE子句限制記錄的數目,那就能減少這方面的開銷. (非oracle中)on、where、having這三個都可以加條件的子句中,on是最先執行,where次之,having最後,因為on是先把不符合條件的記錄過濾後才進行統計,它就可以減少中間運算要處理的資料,按理說應該速度是最快的,where也應該比having快點的,因為它過濾資料後才進行sum,在兩個表聯接時才用on的,所以在一個表的時候,就剩下where跟having比較了。在這單表查詢統計的情況下,如果要過濾的條件沒有涉及到要計算欄位,那它們的結果是一樣的,只是where可以使用rushmore技術,而having就不能,在速度上後者要慢如果要涉及到計算的字 段,就表示在沒計算之前,這個欄位的值是不確定的,根據上篇寫的工作流程,where的作用時間是在計算之前就完成的,而having就是在計算後才起作 用的,所以在這種情況下,兩者的結果會不同。在多表聯接查詢時,on比where更早起作用。系統首先根據各個表之間的聯接條件,把多個表合成一個臨時表 後,再由where進行過濾,然後再計算,計算完後再由having進行過濾。由此可見,要想過濾條件起到正確的作用,首先要明白這個條件應該在什麼時候起作用,然後再決定放在那裡。

(12) 減少對錶的查詢:

在含有子查詢的SQL語句中,要特別注意減少對錶的查詢.例子: 
SELECT  TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT TAB_NAME,DB_VER FROM  TAB_COLUMNS  WHERE  VERSION = 604)

(13) 通過內部函式提高SQL效率:

複雜的SQL往往犧牲了執行效率. 能夠掌握上面的運用函式解決問題的方法在實際工作中是非常有意義的。

(14) 使用表的別名(Alias):

當在SQL語句中連線多個表時, 請使用表的別名並把別名字首於每個Column上.這樣一來,就可以減少解析的時間並減少那些由Column歧義引起的語法錯誤。

(15) 用EXISTS替代IN、用NOT EXISTS替代NOT IN:

在許多基於基礎表的查詢中,為了滿足一個條件,往往需要對另一個表進行聯接.在這種情況下, 使用EXISTS(或NOT EXISTS)通常將提高查詢的效率. 在子查詢中,NOT IN子句將執行一個內部的排序和合並. 無論在哪種情況下,NOT IN都是最低效的 (因為它對子查詢中的表執行了一個全表遍歷). 為了避免使用NOT IN ,我們可以把它改寫成外連線(Outer Joins)或NOT EXISTS。 
例子: 
(高效)SELECT * FROM  EMP (基礎表)  WHERE  EMPNO > 0  AND  EXISTS (SELECT ‘X'  FROM DEPT  WHERE  DEPT.DEPTNO = EMP.DEPTNO  AND  LOC = ‘MELB') 
(低效)SELECT  * FROM  EMP (基礎表)  WHERE  EMPNO > 0  AND  DEPTNO IN(SELECT DEPTNO  FROM  DEPT  WHERE  LOC = ‘MELB')

(16) 識別'低效執行'的SQL語句:

雖然目前各種關於SQL優化的圖形化工具層出不窮,但是寫出自己的SQL工具來解決問題始終是一個最好的方法: 
SELECT  EXECUTIONS , DISK_READS, BUFFER_GETS, 
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio, 
ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run, 
SQL_TEXT 
FROM  V$SQLAREA 
WHERE  EXECUTIONS>0 
AND  BUFFER_GETS > 0 
AND  (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8 
ORDER BY  4 DESC;

(17) 用索引提高效率:

索引是表的一個概念部分,用來提高檢索資料的效率,ORACLE使用了一個複雜的自平衡B-tree結構. 通常,通過索引查詢資料比全表掃描要快. 當ORACLE找出執行查詢和Update語句的最佳路徑時, ORACLE優化器將使用索引. 同樣在聯結多個表時使用索引也可以提高效率. 另一個使用索引的好處是,它提供了主鍵(primary key)的唯一性驗證.。那些LONG或LONG RAW資料型別, 你可以索引幾乎所有的列. 通常, 在大型表中使用索引特別有效. 當然,你也會發現, 在掃描小表時,使用索引同樣能提高效率. 雖然使用索引能得到查詢效率的提高,但是我們也必須注意到它的代價. 索引需要空間來儲存,也需要定期維護, 每當有記錄在表中增減或索引列被修改時, 索引本身也會被修改. 這意味著每條記錄的INSERT , DELETE , UPDATE將為此多付出4 , 5 次的磁碟I/O . 因為索引需要額外的儲存空間和處理,那些不必要的索引反而會使查詢反應時間變慢.。定期的重構索引是有必要的: 
ALTER  INDEX <INDEXNAME> REBUILD <TABLESPACENAME>

(18) 用EXISTS替換DISTINCT:

當提交一個包含一對多表資訊(比如部門表和僱員表)的查詢時,避免在SELECT子句中使用DISTINCT. 一般可以考慮用EXIST替換, EXISTS 使查詢更為迅速,因為RDBMS核心模組將在子查詢的條件一旦滿足後,立刻返回結果. 例子: 
(低效): 
SELECT  DISTINCT  DEPT_NO,DEPT_NAME  FROM  DEPT D , EMP E WHERE  D.DEPT_NO = E.DEPT_NO 
(高效): 
SELECT  DEPT_NO,DEPT_NAME  FROM  DEPT D  WHERE  EXISTS ( SELECT ‘X'  FROM  EMP E  WHERE E.DEPT_NO = D.DEPT_NO);

(19) sql語句用大寫的;因為oracle總是先解析sql語句,把小寫的字母轉換成大寫的再執行。

(20) 在java程式碼中儘量少用連線符“+”連線字串!

(21) 避免在索引列上使用NOT,通常我們要避免在索引列上使用NOT, NOT會產生在和在索引列上使用函式相同的影響. 當ORACLE”遇到”NOT,他就會停止使用索引轉而執行全表掃描。

(22) 避免在索引列上使用計算 
WHERE子句中,如果索引列是函式的一部分.優化器將不使用索引而使用全表掃描.舉例: 
低效: 
SELECT … FROM  DEPT  WHERE SAL * 12 > 25000; 
高效: 
SELECT … FROM DEPT WHERE SAL > 25000/12;

(23) 用>=替代> 
高效: 
SELECT * FROM  EMP  WHERE  DEPTNO >=4 
低效: 
SELECT * FROM EMP WHERE DEPTNO >3 
兩者的區別在於, 前者DBMS將直接跳到第一個DEPT等於4的記錄而後者將首先定位到DEPTNO=3的記錄並且向前掃描到第一個DEPT大於3的記錄。

(24) 用UNION替換OR (適用於索引列)

通常情況下, 用UNION替換WHERE子句中的OR將會起到較好的效果. 對索引列使用OR將造成全表掃描. 注意, 以上規則只針對多個索引列有效. 如果有column沒有被索引, 查詢效率可能會因為你沒有選擇OR而降低. 在下面的例子中, LOC_ID 和REGION上都建有索引. 
高效: 
SELECT LOC_ID , LOC_DESC , REGION 
FROM LOCATION 
WHERE LOC_ID = 10 
UNION 
SELECT LOC_ID , LOC_DESC , REGION 
FROM LOCATION 
WHERE REGION = “MELBOURNE” 
低效: 
SELECT LOC_ID , LOC_DESC , REGION 
FROM LOCATION 
WHERE LOC_ID = 10 OR REGION = “MELBOURNE” 
如果你堅持要用OR, 那就需要返回記錄最少的索引列寫在最前面.

(25) 用IN來替換OR

這是一條簡單易記的規則,但是實際的執行效果還須檢驗,在ORACLE8i下,兩者的執行路徑似乎是相同的. 
低效: 
SELECT…. FROM LOCATION WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30 
高效 
SELECT… FROM LOCATION WHERE LOC_IN  IN (10,20,30);

(26) 避免在索引列上使用IS NULL和IS NOT NULL

避免在索引中使用任何可以為空的列,ORACLE將無法使用該索引.對於單列索引,如果列包含空值,索引中將不存在此記錄. 對於複合索引,如果每個列都為空,索引中同樣不存在此記錄. 如果至少有一個列不為空,則記錄存在於索引中.舉例: 如果唯一性索引建立在表的A列和B列上, 並且表中存在一條記錄的A,B值為(123,null) , ORACLE將不接受下一條具有相同A,B值(123,null)的記錄(插入). 然而如果所有的索引列都為空,ORACLE將認為整個鍵值為空而空不等於空. 因此你可以插入1000 條具有相同鍵值的記錄,當然它們都是空! 因為空值不存在於索引列中,所以WHERE子句中對索引列進行空值比較將使ORACLE停用該索引. 
低效: (索引失效) 
SELECT … FROM  DEPARTMENT  WHERE  DEPT_CODE IS NOT NULL; 
高效: (索引有效) 
SELECT … FROM  DEPARTMENT  WHERE  DEPT_CODE >=0;

(27) 總是使用索引的第一個列:

如果索引是建立在多個列上, 只有在它的第一個列(leading column)被where子句引用時,優化器才會選擇使用該索引. 這也是一條簡單而重要的規則,當僅引用索引的第二個列時,優化器使用了全表掃描而忽略了索引。

(28) 用UNION-ALL 替換UNION ( 如果有可能的話):

當SQL 語句需要UNION兩個查詢結果集合時,這兩個結果集合會以UNION-ALL的方式被合併, 然後在輸出最終結果前進行排序. 如果用UNION ALL替代UNION, 這樣排序就不是必要了. 效率就會因此得到提高. 需要注意的是,UNION ALL 將重複輸出兩個結果集合中相同記錄. 因此各位還是要從業務需求分析使用UNION ALL的可行性. UNION 將對結果集合排序,這個操作會使用到SORT_AREA_SIZE這塊記憶體. 對於這塊記憶體的優化也是相當重要的. 下面的SQL可以用來查詢排序的消耗量 
低效: 
SELECT  ACCT_NUM, BALANCE_AMT 
FROM  DEBIT_TRANSACTIONS 
WHERE TRAN_DATE = '31-DEC-95' 
UNION 
SELECT ACCT_NUM, BALANCE_AMT 
FROM DEBIT_TRANSACTIONS 
WHERE TRAN_DATE = '31-DEC-95' 
高效: 
SELECT ACCT_NUM, BALANCE_AMT 
FROM DEBIT_TRANSACTIONS 
WHERE TRAN_DATE = '31-DEC-95' 
UNION ALL 
SELECT ACCT_NUM, BALANCE_AMT 
FROM DEBIT_TRANSACTIONS 
WHERE TRAN_DATE = '31-DEC-95'

(29) 用WHERE替代ORDER BY:

ORDER BY 子句只在兩種嚴格的條件下使用索引. 
ORDER BY中所有的列必須包含在相同的索引中並保持在索引中的排列順序. 
ORDER BY中所有的列必須定義為非空. 
WHERE子句使用的索引和ORDER BY子句中所使用的索引不能並列. 
例如: 
表DEPT包含以下列: 
DEPT_CODE PK NOT NULL 
DEPT_DESC NOT NULL 
DEPT_TYPE NULL 
低效: (索引不被使用) 
SELECT DEPT_CODE FROM  DEPT  ORDER BY  DEPT_TYPE 
高效: (使用索引) 
SELECT DEPT_CODE  FROM  DEPT  WHERE  DEPT_TYPE > 0

(30) 避免改變索引列的型別:

當比較不同資料型別的資料時, ORACLE自動對列進行簡單的型別轉換. 
假設 EMPNO是一個數值型別的索引列. 
SELECT …  FROM EMP  WHERE  EMPNO = ‘123' 
實際上,經過ORACLE型別轉換, 語句轉化為: 
SELECT …  FROM EMP  WHERE  EMPNO = TO_NUMBER(‘123') 
幸運的是,型別轉換沒有發生在索引列上,索引的用途沒有被改變. 
現在,假設EMP_TYPE是一個字元型別的索引列. 
SELECT …  FROM EMP  WHERE EMP_TYPE = 123 
這個語句被ORACLE轉換為: 
SELECT …  FROM EMP  WHERE TO_NUMBER(EMP_TYPE)=123 
因為內部發生的型別轉換, 這個索引將不會被用到! 為了避免ORACLE對你的SQL進行隱式的型別轉換, 最好把型別轉換用顯式表現出來. 注意當字元和數值比較時, ORACLE會優先轉換數值型別到字元型別。

分析select   emp_name   form   employee   where   salary   >   3000   在此語句中若salary是Float型別的,則優化器對其進行優化為Convert(float,3000),因為3000是個整數,我們應在程式設計時使用3000.0而不要等執行時讓DBMS進行轉化。同樣字元和整型資料的轉換。


(31) 需要當心的WHERE子句:

某些SELECT 語句中的WHERE子句不使用索引. 這裡有一些例子. 
在下面的例子裡, (1)‘!=' 將不使用索引. 記住, 索引只能告訴你什麼存在於表中, 而不能告訴你什麼不存在於表中. (2) ‘ ¦ ¦'是字元連線函式. 就象其他函式那樣, 停用了索引. (3) ‘+'是數學函式. 就象其他數學函式那樣, 停用了索引. (4)相同的索引列不能互相比較,這將會啟用全表掃描.

(32) a. 如果檢索資料量超過30%的表中記錄數.使用索引將沒有顯著的效率提高. b. 在特定情況下, 使用索引也許會比全表掃描慢, 但這是同一個數量級上的區別. 而通常情況下,使用索引比全表掃描要塊幾倍乃至幾千倍!

(33) 避免使用耗費資源的操作:

帶有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL語句會啟動SQL引擎執行耗費資源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要執行兩次排序. 通常, 帶有UNION, MINUS , INTERSECT的SQL語句都可以用其他方式重寫. 如果你的資料庫的SORT_AREA_SIZE調配得好, 使用UNION , MINUS, INTERSECT也是可以考慮的, 畢竟它們的可讀性很強。

(34) 優化GROUP BY:

提高GROUP BY 語句的效率, 可以通過將不需要的記錄在GROUP BY 之前過濾掉.下面兩個查詢返回相同結果但第二個明顯就快了許多. 
低效: 
SELECT JOB , AVG(SAL) 
FROM EMP 
GROUP by JOB 
HAVING JOB = ‘PRESIDENT' 
OR JOB = ‘MANAGER' 
高效: 
SELECT JOB , AVG(SAL) 
FROM EMP 
WHERE JOB = ‘PRESIDENT' 
OR JOB = ‘MANAGER' 
GROUP by JOB


轉自:http://my.oschina.net/xianggao/blog/87216

另有參考 http://my.oschina.net/xianggao/blog/87448 資料庫效能優化之SQL語句優化2

http://my.oschina.net/xianggao/blog/87450 資料庫效能優化之SQL語句優化3

http://my.oschina.net/xianggao/blog/87453 資料庫效能優化之SQL語句優化4

http://my.oschina.net/xianggao/blog/87223  關於如何形成一個好的資料庫設計

SQL效能優化二

  • 優化目標
    1. 減少 IO 次數
      IO永遠是資料庫最容易瓶頸的地方,這是由資料庫的職責所決定的,大部分資料庫操作中超過90%的時間都是 IO 操作所佔用的,減少 IO 次數是 SQL 優化中需要第一優先考慮,當然,也是收效最明顯的優化手段。
    2. 降低 CPU 計算
      除了 IO 瓶頸之外,SQL優化中需要考慮的就是 CPU 運算量的優化了。order by, group by,distinct … 都是消耗 CPU 的大戶(這些操作基本上都是 CPU 處理記憶體中的資料比較運算)。當我們的 IO 優化做到一定階段之後,降低 CPU 計算也就成為了我們 SQL 優化的重要目標
  • 優化方法
    1. 改變 SQL 執行計劃
      明確了優化目標之後,我們需要確定達到我們目標的方法。對於 SQL 語句來說,達到上述2個目標的方法其實只有一個,那就是改變 SQL 的執行計劃,讓他儘量“少走彎路”,儘量通過各種“捷徑”來找到我們需要的資料,以達到 “減少 IO 次數” 和 “降低 CPU 計算” 的目標
  • 常見誤區
    1. count(1)和count(primary_key) 優於 count(*)
      很多人為了統計記錄條數,就使用 count(1) 和 count(primary_key) 而不是 count(*) ,他們認為這樣效能更好,其實這是一個誤區。對於有些場景,這樣做可能效能會更差,應為資料庫對 count(*) 計數操作做了一些特別的優化。
    2. count(column) 和 count(*) 是一樣的
      這個誤區甚至在很多的資深工程師或者是 DBA 中都普遍存在,很多人都會認為這是理所當然的。實際上,count(column) 和 count(*) 是一個完全不一樣的操作,所代表的意義也完全不一樣。
      count(column) 是表示結果集中有多少個column欄位不為空的記錄
      count(*) 是表示整個結果集有多少條記錄
    3. select a,b from … 比 select a,b,c from … 可以讓資料庫訪問更少的資料量
      這個誤區主要存在於大量的開發人員中,主要原因是對資料庫的儲存原理不是太瞭解。
      實際上,大多數關係型資料庫都是按照行(row)的方式儲存,而資料存取操作都是以一個固定大小的IO單元(被稱作 block 或者 page)為單位,一般為4KB,8KB… 大多數時候,每個IO單元中儲存了多行,每行都是儲存了該行的所有欄位(lob等特殊型別欄位除外)。
      所以,我們是取一個欄位還是多個欄位,實際上資料庫在表中需要訪問的資料量其實是一樣的。
      當然,也有例外情況,那就是我們的這個查詢在索引中就可以完成,也就是說當只取 a,b兩個欄位的時候,不需要回表,而c這個欄位不在使用的索引中,需要回表取得其資料。在這樣的情況下,二者的IO量會有較大差異。
    4. order by 一定需要排序操作
      我們知道索引資料實際上是有序的,如果我們的需要的資料和某個索引的順序一致,而且我們的查詢又通過這個索引來執行,那麼資料庫一般會省略排序操作,而直接將資料返回,因為資料庫知道資料已經滿足我們的排序需求了。
      實際上,利用索引來優化有排序需求的 SQL,是一個非常重要的優化手段
      延伸閱讀:MySQL ORDER BY 的實現分析 ,MySQL 中 GROUP BY 基本實現原理 以及 MySQL DISTINCT 的基本實現原理 這3篇文章中有更為深入的分析,尤其是第一篇
    5. 執行計劃中有 filesort 就會進行磁碟檔案排序
      有這個誤區其實並不能怪我們,而是因為 MySQL 開發者在用詞方面的問題。filesort 是我們在使用 explain 命令檢視一條 SQL 的執行計劃的時候可能會看到在 “Extra” 一列顯示的資訊。
      實際上,只要一條 SQL 語句需要進行排序操作,都會顯示“Using filesort”,這並不表示就會有檔案排序操作。
      延伸閱讀:理解 MySQL Explain 命令輸出中的filesort,我在這裡有更為詳細的介紹
  • 基本原則
    1. 儘量少 join
      MySQL 的優勢在於簡單,但這在某些方面其實也是其劣勢。MySQL 優化器效率高,但是由於其統計資訊的量有限,優化器工作過程出現偏差的可能性也就更多。對於複雜的多表 Join,一方面由於其優化器受限,再者在 Join 這方面所下的功夫還不夠,所以效能表現離 Oracle 等關係型資料庫前輩還是有一定距離。但如果是簡單的單表查詢,這一差距就會極小甚至在有些場景下要優於這些資料庫前輩。
    2. 儘量少排序
      排序操作會消耗較多的 CPU 資源,所以減少排序可以在快取命中率高等 IO 能力足夠的場景下會較大影響 SQL 的響應時間。
      對於MySQL來說,減少排序有多種辦法,比如:

      • 上面誤區中提到的通過利用索引來排序的方式進行優化
      • 減少參與排序的記錄條數
      • 非必要不對資料進行排序
    3. 儘量避免 select *
      很多人看到這一點後覺得比較難理解,上面不是在誤區中剛剛說 select 子句中欄位的多少並不會影響到讀取的資料嗎?
      是的,大多數時候並不會影響到 IO 量,但是當我們還存在 order by 操作的時候,select 子句中的欄位多少會在很大程度上影響到我們的排序效率,這一點可以通過我之前一篇介紹 MySQL ORDER BY 的實現分析 的文章中有較為詳細的介紹。
      此外,上面誤區中不是也說了,只是大多數時候是不會影響到 IO 量,當我們的查詢結果僅僅只需要在索引中就能找到的時候,還是會極大減少 IO 量的。
    4. 儘量用 join 代替子查詢
      雖然 Join 效能並不佳,但是和 MySQL 的子查詢比起來還是有非常大的效能優勢。MySQL 的子查詢執行計劃一直存在較大的問題,雖然這個問題已經存在多年,但是到目前已經發布的所有穩定版本中都普遍存在,一直沒有太大改善。雖然官方也在很早就承認這一問題,並且承諾儘快解決,但是至少到目前為止我們還沒有看到哪一個版本較好的解決了這一問題。
    5. 儘量少 or
      當 where 子句中存在多個條件以“或”並存的時候,MySQL 的優化器並沒有很好的解決其執行計劃優化問題,再加上 MySQL 特有的 SQL 與 Storage 分層架構方式,造成了其效能比較低下,很多時候使用 union all 或者是union(必要的時候)的方式來代替“or”會得到更好的效果。
    6. 儘量用 union all 代替 union
      union 和 union all 的差異主要是前者需要將兩個(或者多個)結果集合並後再進行唯一性過濾操作,這就會涉及到排序,增加大量的 CPU 運算,加大資源消耗及延遲。所以當我們可以確認不可能出現重複結果集或者不在乎重複結果集的時候,儘量使用 union all 而不是 union。
    7. 儘量早過濾
      這一優化策略其實最常見於索引的優化設計中(將過濾性更好的欄位放得更靠前)。
      在 SQL 編寫中同樣可以使用這一原則來優化一些 Join 的 SQL。比如我們在多個表進行分頁資料查詢的時候,我們最好是能夠在一個表上先過濾好資料分好頁,然後再用分好頁的結果集與另外的表 Join,這樣可以儘可能多的減少不必要的 IO 操作,大大節省 IO 操作所消耗的時間。
    8. 避免型別轉換
      這裡所說的“型別轉換”是指 where 子句中出現 column 欄位的型別和傳入的引數型別不一致的時候發生的型別轉換:

      • 人為在column_name 上通過轉換函式進行轉換
        直接導致 MySQL(實際上其他資料庫也會有同樣的問題)無法使用索引,如果非要轉換,應該在傳入的引數上進行轉換
      • 由資料庫自己進行轉換
        如果我們傳入的資料型別和欄位型別不一致,同時我們又沒有做任何型別轉換處理,MySQL 可能會自己對我們的資料進行型別轉換操作,也可能不進行處理而交由儲存引擎去處理,這樣一來,就會出現索引無法使用的情況而造成執行計劃問題。
    9. 優先優化高併發的 SQL,而不是執行頻率低某些“大”SQL
      對於破壞性來說,高併發的 SQL 總是會比低頻率的來得大,因為高併發的 SQL 一旦出現問題,甚至不會給我們任何喘息的機會就會將系統壓跨。而對於一些雖然需要消耗大量 IO 而且響應很慢的 SQL,由於頻率低,即使遇到,最多就是讓整個系統響應慢一點,但至少可能撐一會兒,讓我們有緩衝的機會。
    10. 從全域性出發優化,而不是片面調整
      SQL 優化不能是單獨針對某一個進行,而應充分考慮系統中所有的 SQL,尤其是在通過調整索引優化 SQL 的執行計劃的時候,千萬不能顧此失彼,因小失大。
    11. 儘可能對每一條執行在資料庫中的SQL進行 explain
      優化 SQL,需要做到心中有數,知道 SQL 的執行計劃才能判斷是否有優化餘地,才能判斷是否存在執行計劃問題。在對資料庫中執行的 SQL 進行了一段時間的優化之後,很明顯的問題 SQL 可能已經很少了,大多都需要去發掘,這時候就需要進行大量的 explain 操作收集執行計劃,並判斷是否需要進行優化



    Mysql效能優化之引擎的選擇


    MySQL 的儲存引擎可能是所有關係型資料庫產品中最具有特色的了,不僅可以同時使用多種儲存引擎,而且每種儲存引擎和MySQL之間使用外掛方式這種非常鬆的耦合關係。

    由於各儲存引擎功能特性差異較大,這篇文章主要是介紹如何來選擇合適的儲存引擎來應對不同的業務場景。

    • MyISAM
      • 特性
        1. 不支援事務:MyISAM儲存引擎不支援事務,所以對事務有要求的業務場景不能使用
        2. 表級鎖定:其鎖定機制是表級索引,這雖然可以讓鎖定的實現成本很小但是也同時大大降低了其併發效能
        3. 讀寫互相阻塞:不僅會在寫入的時候阻塞讀取,MyISAM還會在讀取的時候阻塞寫入,但讀本身並不會阻塞另外的讀
        4. 只會快取索引:MyISAM可以通過key_buffer快取以大大提高訪問效能減少磁碟IO,但是這個快取區只會快取索引,而不會快取資料
      • 適用場景
        1. 不需要事務支援(不支援)
        2. 併發相對較低(鎖定機制問題)
        3. 資料修改相對較少(阻塞問題)
        4. 以讀為主
        5. 資料一致性要求不是非常高
      • 最佳實踐
        1. 儘量索引(快取機制)
        2. 調整讀寫優先順序,根據實際需求確保重要操作更優先
        3. 啟用延遲插入改善大批量寫入效能
        4. 儘量順序操作讓insert資料都寫入到尾部,減少阻塞
        5. 分解大的操作,降低單個操作的阻塞時間
        6. 降低併發數,某些高併發場景通過應用來進行排隊機制
        7. 對於相對靜態的資料,充分利用Query Cache可以極大的提高訪問效率
        8. MyISAM的Count只有在全表掃描的時候特別高效,帶有其他條件的count都需要進行實際的資料訪問
    • InnoDB
      • 特性
        1. 具有較好的事務支援:支援4個事務隔離級別,支援多版本讀
        2. 行級鎖定:通過索引實現,全表掃描仍然會是表鎖,注意間隙鎖的影響
        3. 讀寫阻塞與事務隔離級別相關
        4. 具有非常高效的快取特性:能快取索引,也能快取資料
        5. 整個表和主鍵以Cluster方式儲存,組成一顆平衡樹
        6. 所有Secondary Index都會儲存主鍵資訊
      • 適用場景
        1. 需要事務支援(具有較好的事務特性)
        2. 行級鎖定對高併發有很好的適應能力,但需要確保查詢是通過索引完成
        3. 資料更新較為頻繁的場景
        4. 資料一致性要求較高
        5. 硬體裝置記憶體較大,可以利用InnoDB較好的快取能力來提高記憶體利用率,儘可能減少磁碟 IO
      • 最佳實踐
        1. 主鍵儘可能小,避免給Secondary index帶來過大的空間負擔
        2. 避免全表掃描,因為會使用表鎖
        3. 儘可能快取所有的索引和資料,提高響應速度
        4. 在大批量小插入的時候,儘量自己控制事務而不要使用autocommit自動提交
        5. 合理設定innodb_flush_log_at_trx_commit引數值,不要過度追求安全性
        6. 避免主鍵更新,因為這會帶來大量的資料移動
    • NDBCluster
      • 特性
        1. 分散式:分散式儲存引擎,可以由多個NDBCluster儲存引擎組成叢集分別存放整體資料的一部分
        2. 支援事務:和Innodb一樣,支援事務
        3. 可與mysqld不在一臺主機:可以和mysqld分開存在於獨立的主機上,然後通過網路和mysqld通訊互動
        4. 記憶體需求量巨大:新版本索引以及被索引的資料必須存放在記憶體中,老版本所有資料和索引必須存在與記憶體中
      • 適用場景
        1. 具有非常高的併發需求
        2. 對單個請求的響應並不是非常的critical
        3. 查詢簡單,過濾條件較為固定,每次請求資料量較少,又不希望自己進行水平Sharding
      • 最佳實踐
        1. 儘可能讓查詢簡單,避免資料的跨節點傳輸
        2. 儘可能滿足SQL節點的計算效能,大一點的叢集SQL節點會明顯多餘Data節點
        3. 在各節點之間儘可能使用萬兆網路環境互聯,以減少資料在網路層傳輸過程中的延時


    Mysql效能優化 --- 包含SQL、表結構、索引和快取


    • 優化目標
      1. 減少 IO 次數
        IO永遠是資料庫最容易瓶頸的地方,這是由資料庫的職責所決定的,大部分資料庫操作中超過90%的時間都是 IO 操作所佔用的,減少 IO 次數是 SQL 優化中需要第一優先考慮,當然,也是收效最明顯的優化手段。
      2. 降低 CPU 計算
        除了 IO 瓶頸之外,SQL優化中需要考慮的就是 CPU 運算量的優化了。order by, group by,distinct … 都是消耗 CPU 的大戶(這些操作基本上都是 CPU 處理記憶體中的資料比較運算)。當我們的 IO 優化做到一定階段之後,降低 CPU 計算也就成為了我們 SQL 優化的重要目標
    • 優化方法
      1. 改變 SQL 執行計劃
        明確了優化目標之後,我們需要確定達到我們目標的方法。對於 SQL 語句來說,達到上述2個目標的方法其實只有一個,那就是改變 SQL 的執行計劃,讓他儘量“少走彎路”,儘量通過各種“捷徑”來找到我們需要的資料,以達到 “減少 IO 次數” 和 “降低 CPU 計算” 的目標
    • 常見誤區
      1. count(1)和count(primary_key) 優於 count(*)
        很多人為了統計記錄條數,就使用 count(1) 和 count(primary_key) 而不是 count(*) ,他們認為這樣效能更好,其實這是一個誤區。對於有些場景,這樣做可能效能會更差,應為資料庫對 count(*) 計數操作做了一些特別的優化。
      2. count(column) 和 count(*) 是一樣的
        這個誤區甚至在很多的資深工程師或者是 DBA 中都普遍存在,很多人都會認為這是理所當然的。實際上,count(column) 和 count(*) 是一個完全不一樣的操作,所代表的意義也完全不一樣。
        count(column) 是表示結果集中有多少個column欄位不為空的記錄
        count(*) 是表示整個結果集有多少條記錄
      3. select a,b from … 比 select a,b,c from … 可以讓資料庫訪問更少的資料量
        這個誤區主要存在於大量的開發人員中,主要原因是對資料庫的儲存原理不是太瞭解。
        實際上,大多數關係型資料庫都是按照行(row)的方式儲存,而資料存取操作都是以一個固定大小的IO單元(被稱作 block 或者 page)為單位,一般為4KB,8KB… 大多數時候,每個IO單元中儲存了多行,每行都是儲存了該行的所有欄位(lob等特殊型別欄位除外)。
        所以,我們是取一個欄位還是多個欄位,實際上資料庫在表中需要訪問的資料量其實是一樣的。
        當然,也有例外情況,那就是我們的這個查詢在索引中就可以完成,也就是說當只取 a,b兩個欄位的時候,不需要回表,而c這個欄位不在使用的索引中,需要回表取得其資料。在這樣的情況下,二者的IO量會有較大差異。
      4. order by 一定需要排序操作
        我們知道索引資料實際上是有序的,如果我們的需要的資料和某個索引的順序一致,而且我們的查詢又通過這個索引來執行,那麼資料庫一般會省略排序操作,而直接將資料返回,因為資料庫知道資料已經滿足我們的排序需求了。
        實際上,利用索引來優化有排序需求的 SQL,是一個非常重要的優化手段
        延伸閱讀:MySQL ORDER BY 的實現分析 ,MySQL 中 GROUP BY 基本實現原理 以及 MySQL DISTINCT 的基本實現原理 這3篇文章中有更為深入的分析,尤其是第一篇
      5. 執行計劃中有 filesort 就會進行磁碟檔案排序
        有這個誤區其實並不能怪我們,而是因為 MySQL 開發者在用詞方面的問題。filesort 是我們在使用 explain 命令檢視一條 SQL 的執行計劃的時候可能會看到在 “Extra” 一列顯示的資訊。
        實際上,只要一條 SQL 語句需要進行排序操作,都會顯示“Using filesort”,這並不表示就會有檔案排序操作。
        延伸閱讀:理解 MySQL Explain 命令輸出中的filesort,我在這裡有更為詳細的介紹
    • 基本原則
      1. 儘量少 join
        MySQL 的優勢在於簡單,但這在某些方面其實也是其劣勢。MySQL 優化器效率高,但是由於其統計資訊的量有限,優化器工作過程出現偏差的可能性也就更多。對於複雜的多表 Join,一方面由於其優化器受限,再者在 Join 這方面所下的功夫還不夠,所以效能表現離 Oracle 等關係型資料庫前輩還是有一定距離。但如果是簡單的單表查詢,這一差距就會極小甚至在有些場景下要優於這些資料庫前輩。
      2. 儘量少排序
        排序操作會消耗較多的 CPU 資源,所以減少排序可以在快取命中率高等 IO 能力足夠的場景下會較大影響 SQL 的響應時間。
        對於MySQL來說,減少排序有多種辦法,比如:
        • 上面誤區中提到的通過利用索引來排序的方式進行優化
        • 減少參與排序的記錄條數
        • 非必要不對資料進行排序
        • 避免使用耗費資源的操作,帶有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL語句會啟動SQL引擎 執行,耗費資源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要執行兩次排序
      3. 儘量避免 select *
        很多人看到這一點後覺得比較難理解,上面不是在誤區中剛剛說 select 子句中欄位的多少並不會影響到讀取的資料嗎?
        是的,大多數時候並不會影響到 IO 量,但是當我們還存在 order by 操作的時候,select 子句中的欄位多少會在很大程度上影響到我們的排序效率,這一點可以通過我之前一篇介紹 MySQL ORDER BY 的實現分析 的文章中有較為詳細的介紹。
        此外,上面誤區中不是也說了,只是大多數時候是不會影響到 IO 量,當我們的查詢結果僅僅只需要在索引中就能找到的時候,還是會極大減少 IO 量的。
      4. 儘量用 join 代替子查詢
        雖然 Join 效能並不佳,但是和 MySQL 的子查詢比起來還是有非常大的效能優勢。MySQL 的子查詢執行計劃一直存在較大的問題,雖然這個問題已經存在多年,但是到目前已經發布的所有穩定版本中都普遍存在,一直沒有太大改善。雖然官方也在很早就承認這一問題,並且承諾儘快解決,但是至少到目前為止我們還沒有看到哪一個版本較好的解決了這一問題。
      5. 儘量少 or
        當 where 子句中存在多個條件以“或”並存的時候,MySQL 的優化器並沒有很好的解決其執行計劃優化問題,再加上 MySQL 特有的 SQL 與 Storage 分層架構方式,造成了其效能比較低下,很多時候使用 union all 或者是union(必要的時候)的方式來代替“or”會得到更好的效果。
      6. 儘量用 union all 代替 union
        union 和 union all 的差異主要是前者需要將兩個(或者多個)結果集合並後再進行唯一性過濾操作,這就會涉及到排序,增加大量的 CPU 運算,加大資源消耗及延遲。所以當我們可以確認不可能出現重複結果集或者不在乎重複結果集的時候,儘量使用 union all 而不是 union。
      7. 儘量早過濾
        這一優化策略其實最常見於索引的優化設計中(將過濾性更好的欄位放得更靠前)。
        在 SQL 編寫中同樣可以使用這一原則來優化一些 Join 的 SQL。比如我們在多個表進行分頁資料查詢的時候,我們最好是能夠在一個表上先過濾好資料分好頁,然後再用分好頁的結果集與另外的表 Join,這樣可以儘可能多的減少不必要的 IO 操作,大大節省 IO 操作所消耗的時間。
      8. 避免型別轉換
        這裡所說的“型別轉換”是指 where 子句中出現 column 欄位的型別和傳入的引數型別不一致的時候發生的型別轉換:
        • 人為在column_name 上通過轉換函式進行轉換
          直接導致 MySQL(實際上其他資料庫也會有同樣的問題)無法使用索引,如果非要轉換,應該在傳入的引數上進行轉換
        • SELECT emp.ename, emp.job FROM emp WHERE emp.empno = 7369;
          不要使用:SELECT emp.ename, emp.job FROM emp WHERE emp.empno = ‘7369
        • 由資料庫自己進行轉換
          如果我們傳入的資料型別和欄位型別不一致,同時我們又沒有做任何型別轉換處理,MySQL 可能會自己對我們的資料進行型別轉換操作,也可能不進行處理而交由儲存引擎去處理,這樣一來,就會出現索引無法使用的情況而造成執行計劃問題。
      9. 優先優化高併發的 SQL,而不是執行頻率低某些“大”SQL
        對於破壞性來說,高併發的 SQL 總是會比低頻率的來得大,因為高併發的 SQL 一旦出現問題,甚至不會給我們任何喘息的機會就會將系統壓跨。而對於一些雖然需要消耗大量 IO 而且響應很慢的 SQL,由於頻率低,即使遇到,最多就是讓整個系統響應慢一點,但至少可能撐一會兒,讓我們有緩衝的機會。
      10. 從全域性出發優化,而不是片面調整
        SQL 優化不能是單獨針對某一個進行,而應充分考慮系統中所有的 SQL,尤其是在通過調整索引優化 SQL 的執行計劃的時候,千萬不能顧此失彼,因小失大。
      11. 儘可能對每一條執行在資料庫中的SQL進行 explain
        優化 SQL,需要做到心中有數,知道 SQL 的執行計劃才能判斷是否有優化餘地,才能判斷是否存在執行計劃問題。在對資料庫中執行的 SQL 進行了一段時間的優化之後,很明顯的問題 SQL 可能已經很少了,大多都需要去發掘,這時候就需要進行大量的 explain 操作收集執行計劃,並判斷是否需要進行優化。

    二、MySQL 資料庫效能優化之表結構

    很多人都將 資料庫設計正規化 作為資料庫表結構設計“聖經”,認為只要按照這個正規化需求設計,就能讓設計出來的表結構足夠優化,既能保證效能優異同時還能滿足擴充套件性要求。殊不知,在N年前被奉為“聖經”的資料庫設計3正規化早就已經不完全適用了。這裡我整理了一些比較常見的資料庫表結構設計方面的優化技巧,希望對大家有用。由於MySQL資料庫是基於行(Row)儲存的資料庫,而資料庫操作 IO 的時候是以 page(block)的方式,也就是說,如果我們每條記錄所佔用的空間量減小,就會使每個page中可存放的資料行數增大,那麼每次 IO 可訪問的行數也就增多了。反過來說,處理相同行數的資料,需要訪問的 page 就會減少,也就是 IO 操作次數降低,直接提升效能。此外,由於我們的記憶體是有限的,增加每個page中存放的資料行數,就等於增加每個記憶體塊的快取資料量,同時還會提升記憶體換中資料命中的機率,也就是快取命中率。
    • 資料型別選擇
      資料庫操作中最為耗時的操作就是 IO 處理,大部分資料庫操作 90% 以上的時間都花在了 IO 讀寫上面。所以儘可能減少 IO 讀寫量,可以在很大程度上提高資料庫操作的效能。我們無法改變資料庫中需要儲存的資料,但是我們可以在這些資料的儲存方式方面花一些心思。下面的這些關於欄位型別的優化建議主要適用於記錄條數較多,資料量較大的場景,因為精細化的資料型別設定可能帶來維護成本的提高,過度優化也可能會帶來其他的問題:
      1. 數字型別:非萬不得已不要使用DOUBLE,不僅僅只是儲存長度的問題,同時還會存在精確性的問題。同樣,固定精度的小數,也不建議使用DECIMAL,建議乘以固定倍數轉換成整數儲存,可以大大節省儲存空間,且不會帶來任何附加維護成本。對於整數的儲存,在資料量較大的情況下,建議區分開 TINYINT / INT / BIGINT 的選擇,因為三者所佔用的儲存空間也有很大的差別,能確定不會使用負數的欄位,建議新增unsigned定義。當然,如果資料量較小的資料庫,也可以不用嚴格區分三個整數型別。
      2. 字元型別:非萬不得已不要使用 TEXT 資料型別,其處理方式決定了他的效能要低於char或者是varchar型別的處理。定長欄位,建議使用 CHAR 型別,不定長欄位儘量使用 VARCHAR,且僅僅設定適當的最大長度,而不是非常隨意的給一個很大的最大長度限定,因為不同的長度範圍,MySQL也會有不一樣的儲存處理。
      3. 時間型別:儘量使用TIMESTAMP型別,因為其儲存空間只需要 DATETIME 型別的一半。對於只需要精確到某一天的資料型別,建議使用DATE型別,因為他的儲存空間只需要3個位元組,比TIMESTAMP還少。不建議通過INT型別類儲存一個unix timestamp 的值,因為這太不直觀,會給維護帶來不必要的麻煩,同時還不會帶來任何好處。
      4. ENUM & SET:對於狀態欄位,可以嘗試使用 ENUM 來存放,因為可以極大的降低儲存空間,而且即使需要增加新的型別,只要增加於末尾,修改結構也不需要重建表資料。如果是存放可預先定義的屬性資料呢?可以嘗試使用SET型別,即使存在多種屬性,同樣可以遊刃有餘,同時還可以節省不小的儲存空間。
      5. LOB型別:強烈反對在資料庫中存放 LOB 型別資料,雖然資料庫提供了這樣的功能,但這不是他所擅長的,我們更應該讓合適的工具做他擅長的事情,才能將其發揮到極致。在資料庫中儲存 LOB 資料就像讓一個多年前在學校學過一點Java的營銷專業人員來寫 Java 程式碼一樣。
    • 字元編碼
      字符集直接決定了資料在MySQL中的儲存編碼方式,由於同樣的內容使用不同字符集表示所佔用的空間大小會有較大的差異,所以通過使用合適的字符集,可以幫助我們儘可能減少資料量,進而減少IO操作次數。
      1. 純拉丁字元能表示的內容,沒必要選擇 latin1 之外的其他字元編碼,因為這會節省大量的儲存空間
      2. 如果我們可以確定不需要存放多種語言,就沒必要非得使用UTF8或者其他UNICODE字元型別,這回造成大量的儲存空間浪費
      3. MySQL的資料型別可以精確到欄位,所以當我們需要大型資料庫中存放多位元組資料的時候,可以通過對不同表不同欄位使用不同的資料型別來較大程度減小資料儲存量,進而降低 IO 操作次數並提高快取命中率
    • 適當拆分
      有些時候,我們可能會希望將一個完整的物件對應於一張資料庫表,這對於應用程式開發來說是很有好的,但是有些時候可能會在效能上帶來較大的問題。當我們的表中存在類似於 TEXT 或者是很大的 VARCHAR型別的大欄位的時候,如果我們大部分訪問這張表的時候都不需要這個欄位,我們就該義無反顧的將其拆分到另外的獨立表中,以減少常用資料所佔用的儲存空間。這樣做的一個明顯好處就是每個資料塊中可以儲存的資料條數可以大大增加,既減少物理 IO 次數,也能大大提高記憶體中的快取命中率。
    上面幾點的優化都是為了減少每條記錄的儲存空間大小,讓每個資料庫中能夠儲存更多的記錄條數,以達到減少 IO 操作次數,提高快取命中率。下面這個優化建議可能很多開發人員都會覺得不太理解,因為這是典型的反正規化設計,而且也和上面的幾點優化建議的目標相違背。
    • 適度冗餘
      為什麼我們要冗餘?這不是增加了每條資料的大小,減少了每個資料塊可存放記錄條數嗎?確實,這樣做是會增大每條記錄的大小,降低每條記錄中可存放資料的條數,但是在有些場景下我們仍然還是不得不這樣做:
      1. 被頻繁引用且只能通過 Join 2張(或者更多)大表的方式才能得到的獨立小欄位
        這樣的場景由於每次Join僅僅只是為了取得某個小欄位的值,Join到的記錄又大,會造成大量不必要的 IO,完全可以通過空間換取時間的方式來優化。不過,冗餘的同時需要確保資料的一致性不會遭到破壞,確保更新的同時冗餘欄位也被更新
    • 儘量使用 NOT NULL
      NULL 型別比較特殊,SQL 難優化。雖然 MySQL NULL型別和 Oracle 的NULL 有差異,會進入索引中,但如果是一個組合索引,那麼這個NULL 型別的欄位會極大影響整個索引的效率。此外,NULL 在索引中的處理也是特殊的,也會佔用額外的存放空間。
      很多人覺得 NULL 會節省一些空間,所以儘量讓NULL來達到節省IO的目的,但是大部分時候這會適得其反,雖然空間上可能確實有一定節省,倒是帶來了很多其他的優化問題,不但沒有將IO量省下來,反而加大了SQL的IO量。所以儘量確保 DEFAULT 值不是 NULL,也是一個很好的表結構設計優化習慣。

     三、MySQL 資料庫效能優化之索引優化

    大家都知道索引對於資料訪問的效能有非常關鍵的作用,都知道索引可以提高資料訪問效率。為什麼索引能提高資料訪問效能?他會不會有“副作用”?是不是索引建立越多,效能就越好?到底該如何設計索引,才能最大限度的發揮其效能?這篇文章主要是帶著上面這幾個問題來做一個簡要的分析,同時排除了業務場景所帶來的特殊性,請不要糾結業務場景的影響。
    • 索引為什麼能提高資料訪問效能?
      很多人只知道索引能夠提高資料庫的效能,但並不是特別瞭解其原理,其實我們可以用一個生活中的示例來理解。我們讓一位不太懂計算機的朋友去圖書館確認一本叫做《MySQL效能調優與架構設計》的書是否在藏,這樣對他說:“請幫我借一本計算機類的資料庫書籍,是屬於 MySQL 資料庫範疇的,叫做《MySQL效能調優與架構設計》”。朋友會根據所屬類別,前往存放“計算機”書籍區域的書架,然後再尋找“資料庫”類存放位置,再找到一堆講述“MySQL”的書籍,最後可能發現目標在藏(也可能已經借出不在書架上)。在這個過程中: “計算機”->“資料庫”->“MySQL”->“在藏”->《MySQL效能調優與架構設計》其實就是一個“根據索引查詢資料”的典型案例,“計算機”->“資料庫”->“MySQL”->“在藏” 就是朋友查詢書籍的索引。假設沒有這個索引,那查詢這本書的過程會變成怎樣呢?朋友只能從圖書館入口一個書架一個書架的“遍歷”,直到找到《MySQL效能調優與架構設計》這本書為止。如果幸運,可能在第一個書架就找到。但如果不幸呢,那就慘了,可能要將整個圖書館所有的書架都找一遍才能找到我們想要的這本書。注:這個例子中的“索引”是記錄在朋友大腦中的,實際上,每個圖書館都會有一個非常全的實際存在的索引系統(大多位於入口顯眼處),由很多個貼上了明顯標籤的小抽屜構成。這個索引系統中存放這非常齊全詳盡的索引資料,標識出我們需要查詢的“目標”在某個區域的某個書架上。而且每當有新的書籍入庫,舊的書籍銷燬以及書記資訊修改,都需要對索引系統進行及時的修正。
    下面我們通過上面這個生活中的小示例,來分析一下索引,看看能的出哪些結論?
    • 索引有哪些“副作用”?
      1. 圖書的變更(增,刪,改)都需要修訂索引,索引存在額外的維護成本
      2. 查詢翻閱索引系統需要消耗時間,索引存在額外的訪問成本
      3. 這個索引系統需要一個地方來存放,索引存在額外的空間成本
    • 索引是不是越多越好?
      1. 如果我們的這個圖書館只是一個進出中轉站,裡面的新書進來後很快就會轉發去其他圖書館而從這個館藏中“清除”,那我們的索引就只會不斷的修改,而很少會被用來查詢圖書
        所以,對於類似於這樣的存在非常大更新量的資料,索引的維護成本會非常高,如果其檢索需求很少,而且對檢索效率並沒有非常高的要求的時候,我們並不建議建立索引,或者是儘量減少索引。
      2. 如果我們的書籍量少到只有幾本或者就只有一個書架,索引並不會帶來什麼作用,甚至可能還會浪費一些查詢索引所花費的時間。
        所以,對於資料量極小到通過索引檢索還不如直接遍歷來得快的資料,也並不適合使用索引。
      3. 如果我們的圖書館只有一個10平方的面積,現在連放書架都已經非常擁擠,而且館藏還在不斷增加,我們還能考慮建立索引嗎?
        所以,當我們連儲存基礎資料的空間都捉襟見肘的時候,我們也應該儘量減少低效或者是去除索引。
    • 索引該如何設計才高效?
      1. 如果我們僅僅只是這樣告訴對方的:“幫我確認一本資料庫類別的講述 MySQL 的叫做《MySQL效能調優與架構設計》的書是否在藏”,結果又會如何呢?朋友只能一個大類區域一個大類區域的去尋找“資料庫”類別,然後再找到 “MySQL”範疇,再看到我們所需是否在藏。由於我們少說了一個“計算機類”,朋友就必須到每一個大類去尋找。
        所以,我們應該儘量讓查詢條件儘可能多的在索引中,儘可能通過索引完成所有過濾,回表只是取出額外的資料欄位。
      2. 如果我們是這樣說的:“幫我確認一本講述 MySQL 的資料庫範疇的計算機叢書,叫做《MySQL效能調優與架構設計》,看是否在藏”。如果這位朋友並不知道計算機是一個大類,也不知道資料庫屬於計算機大類,那這位朋友就悲劇了。首先他得遍歷每個類別確認“MySQL”存在於哪些類別中,然後從包含 “MySQL” 書籍中再看有哪些是“資料庫”範疇的(有可能部分是講述PHP或者其他開發語言的),然後再排除非計算機類的(雖然可能並沒有必要),然後才能確認。
        所以,欄位的順序對組合索引效率有至關重要的作用,過濾效果越好的欄位需要更靠前。
      3. 如果我們還有這樣一個需求(雖然基本不可能):“幫我將圖書館中所有的計算機圖書借來”。朋友如果通過索引來找,每次都到索引櫃找到計算機書籍所在的區域,然後從書架上搬下一格(假設只能以一格為單位從書架上取下,類比資料庫中以block/page為單位讀取),取出第一本,然後再從索引櫃找到計算機圖書所在區域,再搬下一格,取出一本… 如此往復直至取完所有的書。如果他不通過索引來找又會怎樣呢?他需要從地一個書架一直往後找,當找到計算機的書,搬下一格,取出所有計算機的書,再往後,直至所有書架全部看一遍。在這個過程中,如果計算機類書籍較多,通過索引來取所花費的時間很可能要大於直接遍歷,因為不斷往復的索引翻閱所消耗的時間會非常長。(延伸閱讀:這裡有一篇以前寫的關於Oracle的文章,索引掃描還是全表掃描(Index Scan Or Full Table Scan)
        所以,當我們需要讀取的資料量佔整個資料量的比例較大抑或者說索引的過濾效果並不是太好的時候,使用索引並不一定優於全表掃描。
      4. 如果我們的朋友不知道“資料庫”這個類別可以屬於“計算機”這個大類,抑或者圖書館的索引系統中這兩個類別屬性並沒有關聯關係,又會怎樣呢?也就是說,朋友得到的是2個獨立的索引,一個是告知“計算機”這個大類所在的區域,一個是“資料庫”這個小類所在的區域(很可能是多個區域),那麼他只能二者選其一來搜尋我的需求。即使朋友可以分別通過2個索引檢索然後自己在腦中取交集再找,那這樣的效率實際過程中也會比較低下。
        所以,在實際使用過程中,一次資料訪問一般只能利用到1個索引,這一點在索引建立過程中一定要注意,不是說一條SQL語句中Where子句裡面每個條件都有索引能對應上就可以了。
      5. 最後總結一下法則:不要在建立的索引的資料列上進行下列操作:
        ◆避免對索引欄位進行計算操作◆避免在索引欄位上使用not,,!=◆避免在索引列上使用IS NULL和IS NOT NULL◆避免在索引列上出現資料型別轉換◆避免在索引欄位上使用函式◆避免建立索引的列中使用空值。

     四、MySQL 資料庫效能優化之快取引數優化

    資料庫屬於 IO 密集型的應用程式,其主要職責就是資料的管理及儲存工作。而我們知道,從記憶體中讀取一個資料庫的時間是微秒級別,而從一塊普通硬碟上讀取一個IO是在毫秒級別,二者相差3個數量級。所以,要優化資料庫,首先第一步需要優化的就是 IO,儘可能將磁碟IO轉化為記憶體IO。本文先從 MySQL 資料庫IO相關引數(快取引數)的角度來看看可以通過哪些引數進行IO優化:

    • query_cache_size/query_cache_type (global) Query cache 作用於整個 MySQL Instance,主要用來快取 MySQL 中的 ResultSet,也就是一條SQL語句執行的結果集,所以僅僅只能針對select語句。當我們開啟了 Query Cache 功能,MySQL在接受到一條select語句的請求後,如果該語句滿足Query Cache的要求(未顯式說明不允許使用Query Cache,或者已經顯式申明需要使用Query Cache),MySQL 會直接根據預先設定好的HASH演算法將接受到的select語句以字串方式進行hash,然後到Query Cache 中直接查詢是否已經快取。也就是說,如果已經在快取中,該select請求就會直接將資料返回,從而省略了後面所有的步驟(如 SQL語句的解析,優化器優化以及向儲存引擎請求資料等),極大的提高效能。當然,Query Cache 也有一個致命的缺陷,那就是當某個表的資料有任何任何變化,都會導致所有引用了該表的select語句在Query Cache 中的快取資料失效。所以,當我們的資料變化非常頻繁的情況下,使用Query Cache 可能會得不償失。Query Cache的使用需要多個引數配合,其中最為關鍵的是 query_cache_size 和 query_cache_type ,前者設定用於快取 ResultSet 的記憶體大小,後者設定在何場景下使用 Query Cache。在以往的經驗來看,如果不是用來快取基本不變的資料的MySQL資料庫,query_cache_size 一般 256MB 是一個比較合適的大小。當然,這可以通過計算Query Cache的命中率(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))來進行調整。query_cache_type可以設定為0(OFF),1(ON)或者2(DEMOND),分別表示完全不使用query cache,除顯式要求不使用query cache(使用sql_no_cache)之外的所有的select都使用query cache,只有顯示要求才使用query cache(使用sql_cache)。
    • binlog_cache_size (global) Binlog Cache 用於在開啟了二進位制日誌(binlog)記錄功能的環境,是 MySQL 用來提高binlog的記錄效率而設計的一個用於短時間內臨時快取binlog資料的記憶體區域。一般來說,如果我們的資料庫中沒有什麼大事務,寫入也不是特別頻繁,2MB~4MB是一個合適的選擇。但是如果我們的資料庫大事務較多,寫入量比較大,可與適當調高binlog_cache_size。同時,我們可以通過binlog_cache_use 以及 binlog_cache_disk_use來分析設定的binlog_cache_size是否足夠,是否有大量的binlog_cache由於記憶體大小不夠而使用臨時檔案(binlog_cache_disk_use)來快取了。
    • key_buffer_size (global) Key Buffer 可能是大家最為熟悉的一個 MySQL 快取引數了,尤其是在 MySQL 沒有更換預設儲存引擎的時候,很多朋友可能會發現,預設的 MySQL 配置檔案中設定最大的一個記憶體引數就是這個引數了。key_buffer_size 引數用來設定用於快取 MyISAM儲存引擎中索引檔案的記憶體區域大小。如果我們有足夠的記憶體,這個快取區域最好是能夠存放下我們所有的 MyISAM 引擎表的所有索引,以儘可能提高效能。此外,當我們在使用MyISAM 儲存的時候有一個及其重要的點需要注意,由於 MyISAM 引擎的特性限制了他僅僅只會快取索引塊到記憶體中,而不會快取表資料庫塊。所以,我們的 SQL 一定要儘可能讓過濾條件都在索引中,以便讓快取幫助我們提高查詢效率。
    • bulk_insert_buffer_size (thread)和key_buffer_size一樣,這個引數同樣也僅作用於使用 MyISAM儲存引擎,用來快取批量插入資料的時候臨時快取寫入資料。當我們使用如下幾種資料寫入語句的時候,會使用這個記憶體區域來快取批量結構的資料以幫助批量寫入資料檔案:insert … select …
      insert … values (…) ,(…),(…)…
      load data infile… into… (非空表)
    • innodb_buffer_pool_size(global)當我們使用InnoDB儲存引擎的時候,innodb_buffer_pool_size 引數可能是影響我們效能的最為關鍵的一個引數了,他用來設定用於快取 InnoDB 索引及資料塊的記憶體區域大小,類似於 MyISAM 儲存引擎的 key_buffer_size 引數,當然,可能更像是 Oracle 的 db_cache_size。簡單來說,當我們操作一個 InnoDB 表的時候,返回的所有資料或者去資料過程中用到的任何一個索引塊,都會在這個記憶體區域中走一遭。和key_buffer_size 對於 MyISAM 引擎一樣,innodb_buffer_pool_size 設定了 InnoDB 儲存引擎需求最大的一塊記憶體區域的大小,直接關係到 InnoDB儲存引擎的效能,所以如果我們有足夠的記憶體,儘可將該引數設定到足夠打,將盡可能多的 InnoDB 的索引及資料都放入到該快取區域中,直至全部。我們可以通過 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100% 計算快取命中率,並根據命中率來調整 innodb_buffer_pool_size 引數大小進行優化。
    • innodb_additional_mem_pool_size(global)這個引數我們平時調整的可能不是太多,很多人都使用了預設值,可能很多人都不是太熟悉這個引數的作用。innodb_additional_mem_pool_size 設定了InnoDB儲存引擎用來存放資料字典資訊以及一些內部資料結構的記憶體空間大小,所以當我們一個MySQL Instance中的資料庫物件非常多的時候,是需要適當調整該引數的大小以確保所有資料都能存放在記憶體中提高訪問效率的。這個引數大小是否足夠還是比較容易知道的,因為當過小的時候,MySQL 會記錄 Warning 資訊到資料庫的 error log 中,這時候你就知道該調整這個引數大小了。
    • innodb_log_buffer_size (global)這是 InnoDB 儲存引擎的事務日誌所使用的緩衝區。類似於 Binlog Buffer,InnoDB 在寫事務日誌的時候,為了提高效能,也是先將資訊寫入 Innofb Log Buffer 中,當滿足 innodb_flush_log_trx_commit 引數所設定的相應條件(或者日誌緩衝區寫滿)之後,才會將日誌寫到檔案(或者同步到磁碟)中。可以通過 innodb_log_buffer_size 引數設定其可以使用的最大記憶體空間。
      注:innodb_flush_log_trx_commit 引數對 InnoDB Log 的寫入效能有非常關鍵的影響。該引數可以設定為0,1,2,解釋如下:0:log buffer中的資料將以每秒一次的頻率寫入到log file中,且同時會進行檔案系統到磁碟的同步操作,但是每個事務的commit並不會觸發任何log buffer 到log file的重新整理或者檔案系統到磁碟的重新整理操作;
      1:在每次事務提交的時候將log buffer 中的資料都會寫入到log file,同時也會觸發檔案系統到磁碟的同步;
      2:事務提交會觸發log buffer 到log file的重新整理,但並不會觸發磁碟檔案系統到磁碟的同步。此外,每秒會有一次檔案系統到磁碟同步操作。此外,MySQL文件中還提到,這幾種設定中的每秒同步一次的機制,可能並不會完全確保非常準確的每秒就一定會發生同步,還取決於程式排程的問題。實際上,InnoDB 能否真正滿足此引數所設定值代表的意義正常 Recovery 還是受到了不同 OS 下檔案系統以及磁碟本身的限制,可能有些時候在並沒有真正完成磁碟同步的情況下也會告訴 mysqld 已經完成了磁碟同步。
    • innodb_max_dirty_pages_pct (global)這個引數和上面的各個引數不同,他不是用來設定用於快取某種資料的記憶體大小的一個引數,而是用來控制在 InnoDB Buffer Pool 中可以不用寫入資料檔案中的Dirty Page 的比例(已經被修但還沒有從記憶體中寫入到資料檔案的髒資料)。這個比例值越大,從記憶體到磁碟的寫入操作就會相對減少,所以能夠一定程度下減少寫入操作的磁碟IO。但是,如果這個比例值過大,當資料庫 Crash 之後重啟的時間可能就會很長,因為會有大量的事務資料需要從日誌檔案恢復出來寫入資料檔案中。同時,過大的比例值同時可能也會造成在達到比例設定上限後的 flush 操作“過猛”而導致效能波動很大。
    上面這幾個引數是 MySQL 中為了減少磁碟物理IO而設計的主要引數,對 MySQL 的效能起到了至關重要的作用。
    —EOF—
    按照 mcsrainbow 朋友的要求,這裡列一下根據以往經驗得到的相關引數的建議值:
    • query_cache_type : 如果全部使用innodb儲存引擎,建議為0,如果使用MyISAM 儲存引擎,建議為2,同時在SQL語句中顯式控制是否是喲你gquery cache
    • query_cache_size: 根據 命中率(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))進行調整,一般不建議太大,256MB可能已經差不多了,大型的配置型靜態資料可適當調大
    • binlog_cache_size: 一般環境2MB~4MB是一個合適的選擇,事務較大且寫入頻繁的資料庫環境可以適當調大,但不建議超過32MB
    • key_buffer_size: 如果不使用MyISAM儲存引擎,16MB足以,用來快取一些系統表資訊等。如果使用 MyISAM儲存引擎,在記憶體允許的情況下,儘可能將所有索引放入記憶體,簡單來說就是“越大越好”
    • bulk_insert_buffer_size: 如果經常性的需要使用批量插入的特殊語句(上面有說明)來插入資料,可以適當調大該引數至16MB~32MB,不建議繼續增大,某人8MB
    • innodb_buffer_pool_size: 如果不使用InnoDB儲存引擎,可以不用調整這個引數,如果需要使用,在記憶體允許的情況下,儘可能將所有的InnoDB資料檔案存放如記憶體中,同樣將但來說也是“越大越好”
    • innodb_additional_mem_pool_size: 一般的資料庫建議調整到8MB~16MB,如果表特別多,可以調整到32MB,可以根據error log中的資訊判斷是否需要增大
    • innodb_log_buffer_size: 預設是1MB,系的如頻繁的系統可適當增大至4MB~8MB。當然如上面介紹所說,這個引數實際上還和另外的flush引數相關。一般來說不建議超過32MB
    • innodb_max_dirty_pages_pct: 根據以往的經驗,重啟恢復的資料如果要超過1GB的話,啟動速度會比較慢,幾乎難以接受,所以建議不大於 1GB/innodb_buffer_pool_size(GB)*100 這個值。當然,如果你能夠忍受啟動時間比較長,而且希望儘量減少記憶體至磁碟的flush,可以將這個值調整到90,但不建議超過90
    注:以上取值範圍僅僅只是我的根據以往遇到的資料庫場景所得到的一些優化經驗值,並不一定適用於所有場景,所以在實際優化過程中還需要大家自己不斷的調整分析,也歡迎大家隨時通過 Mail 與我聯絡溝通交流優化或者是架構方面的技術,一起探討相互學習。
     

    Mysql優化總結


    一、索引
    1、建立索引:
    (1).ALTER TABLE   
     ALTER TABLE用來建立普通索引、UNIQUE索引或PRIMARY KEY索引。    
        
     ALTER TABLE table_name ADD INDEX index_name (column_list)   
      
     ALTER TABLE table_name ADD UNIQUE (column_list)   
      
     ALTER TABLE table_name ADD PRIMARY KEY (column_list)   
      
    (2)、CREATE INDEX   
     CREATE INDEX可對錶增加普通索引或UNIQUE索引。   
      
     CREATE INDEX index_name ON table_name (column_list)   
      
     CREATE UNIQUE INDEX index_name ON table_name (column_list)  
    2、檢視索引  
      
     mysql> show index from tblname;   
      
     mysql> show keys from tblname; 
    3、刪除索引
     可利用ALTER TABLE或DROP INDEX語句來刪除索引。類似於CREATE INDEX語句,DROP INDEX可以在ALTER TABLE 內部作為一條語句處理,語法如下。  
     DROP INDEX index_name ON talbe_name   
      
     ALTER TABLE table_name DROP INDEX index_name   
      
     ALTER TABLE table_name DROP PRIMARY KEY   

    索引:http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html
    **explain +select ·····用來獲取select語句的執行的相關資訊及索引的使用等
    **describe table table_name;
    **analyze table table_name;檢視錶的資訊,幫助優化
    **show 檢視執行狀態

    二、my.ini中的配置
    http://www.chinaz.com/program/2009/1210/100740.shtml
    mysql > show status; 可以檢視具體的設定 伺服器的狀態
    具體的配置呀什麼,沒有親自試驗過

    三、資料表引擎
     1、MyISAM:mysql預設的
     2、InnoDB:支援事務、鎖、外來鍵、聚簇索引
    引擎介紹:http://blog.csdn.net/cheungjustin/article/details/5999880
     http://limaolinjia.blog.163.com/blog/static/539162282011012145139/

    四、索引的型別:
     1、B-Tree索引
     2、hash索引
    具體的參考還是一)

    五、事務
    資料表引擎使用InnoDB
    http://www.cnblogs.com/winner/archive/2011/11/09/2242272.html


    六、儲存過程
    經編譯和優化後儲存在資料庫伺服器中,執行效率高,可以降低客戶機和伺服器之間的通訊量,有利於集中控制,易於維護 (P247)
    http://blog.sina.com.cn/s/blog_52d20fbf0100ofd5.html

    七、mysql profiling(mysql效能分析器)優化sql語句
    檢視SQL執行消耗系統資源的資訊
    ++++需要開啟+++
    具體使用:http://www.jiunile.com/mysql-profiling%E7%9A%84%E4%BD%BF%E7%94%A8.html

    八、慢查詢日誌
    ++++需要開啟++++
    通過慢日誌查詢可以知道哪些SQL語句執行效率低下,那些sql語句使用的頻率高等
    對MySQL查詢語句的監控、分析、優化是MySQL優化非常重要的一步。開啟慢查詢日誌後,由於日誌記錄操作,在一定程度上會佔用CPU資源影響mysql的效能,但是可以階段性開啟來定位效能瓶頸。
    具體參考:http://blog.csdn.net/renzhenhuai/article/details/8839874

    關於mysql的一些講解:http://www.ccvita.com/category/mysql

  • 相關文章