Oracle優化的方法

想做到資料庫優化的高手，不是花幾周，幾個月就能達到的，這並不是因為資料庫優化有多高深，而是因為要做好優化一方面需要有非常好的技術功底，對作業系統、儲存硬體網路、資料庫原理等方面有比較紮實的基礎知識，另一方面是需要花大量時間對特定的資料庫不斷的進行實踐測試與總結。

針對資料庫的優化，可以已Oracle為基點，從Oracle外部因素和Oracle本身的效能兩部分考慮。

一、Oracle的外部因素：

Oracle的外部因素包括CPU，cache L1，L2，L3, 記憶體，網路卡，普通硬碟/SSD硬碟。 這些硬體基於生產條件的限制，也就存在了相應的資料處理的瓶頸。

下面是外部因素，對資料I/O處理的效能指標：

通過圖片可以看出來，有兩個指標：

延時（響應時間）：表示硬體處理突發事件的反應能力。

頻寬（吞吐量）：代表硬體的持續處理能力。

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

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

網路：結果資料傳輸、SQL請求、遠端資料庫訪問（dblink）；

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

從上圖可以看出，計算機系統硬體效能從高到低依次為：

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

可見SSD硬碟效能的平均值遠遠高於普通硬碟。值得一提的是，阿里辦公環境全部為MAC系統，而MAC系統就是SSD硬碟。

下面給出優化建議：

從實際意義上來講，根據需求，資料訪問的需求只會越來越大，通過SQL語句減少訪問量的做法，對大資料而言，效果提升也是有限的

對於Oracle以外的因素，可以從兩點進行考慮，第一就是更換伺服器的硬體設施，將普通硬碟換成SSD固態硬碟，第二點也就是基於分散式的思想，增加多臺計算機充當伺服器對資料進行處理。

二、從Oracle內部的自身效能進行優化

從Oracle內部自身考慮的話，可以從如下5個方向考慮：

1. 減少資料訪問 ---- 索引的使用 　　　　　　　　　　（減少磁碟訪問）
2. 返回更少的資料　　----分頁的應用　　　　　　　　　（減少網路傳輸或磁碟訪問）
3. 減少互動次數　　---- 減少I/O的訪問，邏輯上的優化 （減少網路傳輸）
4. 減少資料庫伺服器CPU運算　　----增加客戶端的運算　（減少CPU及記憶體開銷）
5. 利用更多的資源　---- 分散式的嘗試　　　　　　　　（增加資源）

1、減少資料訪問

1.1、正確認識索引，建立索引

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

如果查詢需求特別巨大，同時滿足一次插入，偶爾修改，不刪除，這樣的情況下可以充分考慮增加資料庫索引，來提高查詢效能

1.2、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（兩個表通過索引欄位關聯）

1.3、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	給索引查詢的值應是已知資料，不能是未知欄位值。

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

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

Select * from company where name=?

因為ID一般都會被設為主鍵，而主鍵已經起到了唯一約束的作用，不需要再設為索引。

1.4、我們一般在什麼欄位上建索引？

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

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

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

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

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

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

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

索引對於Insert效能降低56%
索引對於Update效能降低47%
索引對於Delete效能降低29%

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

如果不涉及增刪改，可以考慮使用索引

1.7、如何建立索引

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

2、返回更少的資料

一般指的是分頁技術

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

-----缺點大於優點，不建議使用

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

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

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

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

2.2、應用伺服器分頁

--------- 很少有資料庫系統不支援分頁，但是在伺服器中進行分頁的效率與在Oracle中進行分頁的效率對比，目前還未知

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

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

Int count= list.size();

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

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

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

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

2.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);

3、減少互動次數

資料的增刪改查四個操作中，插入和查詢普遍存在大批量的操作，較少互動次數也是主要針對這兩個操作。

但是，一般專案中都是存在一次插入多次查詢，甚至千萬次查詢，所以，這裡還是主要考慮減少查詢的互動次數。

3.1 使用 in List

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

for :var in ids[] do begin

  select * from mytable where id=:var;

end;

我們也可以做一個小的優化， 如下所示，用ID IN LIST的這種方式寫SQL：

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

通過這樣處理可以大大減少SQL請求的數量，從而提高效能。在優化中 n個ID放到一個List中，這樣查詢的互動數量只有1。

那如果有10000個ID，那是不是全部放在一條SQL裡處理呢？答案肯定是否定的。首先大部份資料庫都會有SQL長度和IN裡個數的限制，如ORACLE的IN裡就不允許超過1000個值。

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

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

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

3.2、設定Fetch Size

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

3.3、使用儲存過程

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

a：將A表資料全部取出到客戶端；

b：計算出要更新的資料；

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

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

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

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

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

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

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

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

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

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

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支援最大併發數

磁碟IO支援最大併發數

不使用

241000=8000

100*160=16000

使用

241000/0.02=400000

100*160=16000

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

4.2、減少比較操作

我們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方法可以在網上直接查詢，這裡不詳細介紹。

4.3、大量複雜運算在客戶端進行

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

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

5、利用更多資源

5.1、客戶端多程式訪問

-----通常意義上的分散式

多程式並行訪問是指在客戶端建立多個程式(執行緒)，每個程式建立一個與資料庫的連線，然後同時向資料庫提交訪問請求。當資料庫主機資源有空閒時，我們可以採用客戶端多程式並行訪問的方法來提高效能。如果資料庫主機已經很忙時，採用多程式並行訪問效能不會提高，反而可能會更慢。

例如：

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

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

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

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

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

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

總結：
1、減少資料訪問 ---- 索引的使用
2、返回更少的資料　　----分頁的應用、也是減少I/O的訪問
3、減少互動次數　　---- 減少I/O的訪問，邏輯上的優化
4、減少資料庫伺服器CPU運算　　----增加客戶端的運算
5、利用更多的資源　---- 分散式的嘗試
6、主體思想保證伺服器到不了併發瓶頸，在這個前提下，嘗試優化
7、常用的優化策略首選索引，其次是互動次數和分頁技術的使用
8、在資料量極其龐大的前提下，首選分散式處理方式