Oracle數據庫系統性能優化策略
2024-08-29 13:35:09
供稿：網友
一個數據庫系統的生命周期可以分成設計、開發和成品三個階段。在設計階段進行數據庫性能優化的成本最低，收益最大。在成品階段進行數據庫性能優化的成本最高，收益最小。數據庫的優化可以通過對網絡、硬件、操作系統、數據庫參數和應用程序的優化來進行。 最常見的優化手段就是對硬件的升級。據統計，對網絡、硬件、操作系統、數據庫參數進行優化所獲得的性能提升，全部加起來只占數據庫系統性能提升的40%左右，其余的60%系統性能提升來自對應用程序的優化。許多優化專家認為，對應用程序的優化可以得到80%的系統性能的提升。 一、數據庫性能的優化 數據庫設計是應用程序設計的基礎，其性能直接影響應用程序的性能。數據庫性能包括存儲空間需求量的大小和查詢響應時間的長短兩個方面。為了優化數據庫性能，需要對數據庫中的表進行規范化。規范化的范式可分為第一范式、第二范式、第三范式、BCNF范式、第四范式和第五范式。一般來說，邏輯數據庫設計會滿足規范化的前3級標準,但由于滿足第三范式的表結構輕易維護且基本滿足實際應用的要求。因此，實際應用中一般都按照第三范式的標準進行規范化。但是，規范化也有缺點：由于將一個表拆分成為多個表，在查詢時需要多表連接，降低了查詢速度。 由于規范化有可能導致查詢速度慢的缺點，考慮到一些應用需要較快的響應速度，在設計表時應同時考慮對某些表進行反規范化。反規范化可以采用以下幾種方法： 1. 分割表 分割表包括水平分割和垂直分割。 水平分割是按照行將一個表分割為多個表，這可以提高每個表的查詢速度，但查詢、更新時要選擇不同的表，統計時要匯總多個表，因此應用程序會更復雜。 垂直分割是對于一個列很多的表，若某些列的訪問頻率遠遠高于其它列，就可以將主鍵和這些列作為一個表，將主鍵和其它列作為另外一個表。通過減少列的寬度，增加了每個數據頁的行數，一次I/O就可以掃描更多的行，從而提高了訪問每一個表的速度。但是由于造成了多表連接，所以應該在同時查詢或更新不同分割表中的列的情況比較少的情況下使用。 2. 保留冗余列 當兩個或多個表在查詢中經常需要連接時，可以在其中一個表上增加若干冗余的列，以避免表之間的連接過于頻繁。由于對冗余列的更新操作必須對多個表同步進行，所以一般在冗余列的數據不經常變動的情況下使用。 3. 增加派生列 派生列是由表中的其它多個列計算所得，增加派生列可以減少統計運算，在數據匯總時可以大大縮短運算時間。 二、應用程序性能的優化 應用程序的優化通?？煞譃閮蓚€方面：源代碼和SQL語句。由于涉及到對程序邏輯的改變，源代碼的優化在時間成本和風險上代價很高，而對數據庫系統性能的提升收效有限，因此應用程序的優化應著重在SQL語句的優化。對于海量數據，劣質SQL語句和優質SQL語句之間的速度差別可以達到上百倍，可見對于一個系統不是簡單地能實現其功能就行，而是要寫出高質量的SQL語句，提高系統的可用性。 下面就某些SQL語句的where子句編寫中需要注重的問題作具體介紹。在這些where子句中，即使某些列存在索引，但是由于編寫了劣質的SQL，系統在運行該SQL語句時也不能使用該索引，而同樣使用全表掃描，這就造成了響應速度的極大降低。 1. IS NULL 與 IS NOT NULL 不能用null作索引，任何包含null值的列都將不會被包含在索引中。即使索引有多列的情況下，只要這些列中有一列含有null，該列就會從索引中排除。也就是說假如某列存在空值，即使對該列建索引也不會提高性能。 任何在where子句中使用is null或is not null的語句優化器是不答應使用索引的。 2. 聯接列 對于有聯接的列，即使最后的聯接值為一個靜態值，優化器不會使用索引的。例如，假定有一個職工表（employee），對于一個職工的姓和名分成兩列存放（FIRST_NAME和LAST_NAME），現在要查詢一個叫喬治•布什（George Bush）的職工。 下面是一個采用聯接查詢的SQL語句： select * from employee where first_name''last_name ='George Bush'; 上面這條語句完全可以查詢出是否有George Bush這個員工，但是這里需要注重，系統優化器對基于last_name創建的索引沒有使用。 當采用下面這種SQL語句的編寫，Oracle系統就可以采用基于last_name創建的索引： Select * From employee where first_name ='George' and last_name ='Bush'; 碰到下面這種情況又如何處理呢？假如一個變量（name）中存放著George Bush這個員工的姓名，對于這種情況我們又如何避免全程遍歷使用索引呢？可以使用一個函數，將變量name中的姓和名分開就可以了，但是有一點需要注重，這個函數是不能作用在索引列上。下面是SQL查詢腳本： select * from employee where first_name = SUBSTR('&&name',1,INSTR('&&name',' ')-1) and last_name = SUBSTR('&&name',INSTR('&&name’,' ')+1) ; 3. 帶通配符（%）的like語句 同樣以上面的例子來看這種情況。目前的需求是這樣的，要求在職工表中查詢名字中包含Bush的人?？梢圆捎萌缦碌牟樵僑QL語句：
select * from employee where last_name like '%Bush%'; 這里由于通配符（%）在搜尋詞首出現，所以Oracle系統不使用last_name的索引。在很多情況下可能無法避免這種情況，但是一定要心中有底，通配符如此使用會降低查詢速度。然而當通配符出現在字符串其他位置時，優化器就能利用索引。例如，在下面的查詢中索引得到了使用： select * from employee where last_name like 'c%'; 4. Order by語句 Order by語句決定了Oracle如何將返回的查詢結果排序。Order by語句對要排序的列沒有什么非凡的限制，也可以將函數加入列中（象聯接或者附加等）。任何在Order by語句的非索引項或者有計算表達式都將降低查詢速度。 仔細檢查order by語句以找出非索引項或者表達式，它們會降低性能。解決這個問題的辦法就是重寫order by語句以使用索引，也可以為所使用的列建立另外一個索引，同時應絕對避免在order by子句中使用表達式。 5. NOT 我們在查詢時經常在where子句使用一些邏輯表達式，如大于、小于、等于以及不等于等等，也可以使用and（與）、or（或）以及not（非）。NOT可用來對任何邏輯運算符號取反。下面是一個NOT子句的例子： ... where not (status ='VALID') 假如要使用NOT，則應在取反的短語前面加上括號，并在短語前面加上NOT運算符。NOT運算符包含在另外一個邏輯運算符中，這就是不等于（<>）運算符。換句話說，即使不在查詢where子句中顯式地加入NOT詞，NOT仍在運算符中，見下例： ... where status <>'INVALID'; 再看下面這個例子： select * from employee where salary<>3000; 對這個查詢，可以改寫為不使用NOT的語句： select * from employee where salary<3000 or salary>3000; 雖然這兩種查詢的結果一樣，但是第二種查詢方案會比第一種查詢方案更快些。第二種查詢答應Oracle對salary列使用索引，而第一種查詢則不能使用索引。 6. IN和EXISTS 有時候會將一列和一系列值相比較。最簡單的辦法就是在where子句中使用子查詢。在where子句中可以使用兩種格式的子查詢。 第一種格式是使用IN操作符： ... where column in(select * from ... where ...); 第二種格式是使用EXIST操作符： ... where exists (select 'X' from ...where ...); 絕大多數人會使用第一種格式，因為它比較輕易編寫，而實際上第二種格式要遠比第一種格式的效率高。在Oracle中可以將幾乎所有的IN操作符子查詢改寫為使用EXISTS的子查詢。 第二種格式中，子查詢以‘select 'X'’開始。運用EXISTS子句不管子查詢從表中抽取什么數據它只查看where子句。這樣優化器就不必遍歷整個表而僅根據索引就可完成工作（這里假定在where語句中使用的列存在索引）。相對于IN子句來說，EXISTS使用相連子查詢，構造起來要比IN子查詢困難一些。 通過使用EXISTS，Oracle系統會首先檢查主查詢，然后運行子查詢直到找到第一個匹配項，這就節省了時間。Oracle系統在執行IN子查詢時，首先執行子查詢，并將獲得的結果列表存放在一個加了索引的臨時表中。在執行子查詢之前，系統先將主查詢掛起，待子查詢執行完畢，存放在臨時表中以后再執行主查詢。這也就是使用EXISTS比使用IN通常查詢速度快的原因。 同時應盡可能使用NOT EXISTS來代替NOT IN，盡管二者都使用了NOT（不能使用索引而降低速度），但NOT EXISTS要比NOT IN查詢效率更高。（作者單位：中國銀行福建省分行信息科技部）
