關(guān)系數(shù)據(jù)庫的查詢優(yōu)化策略

1、關(guān)系數(shù)據(jù)庫的查詢優(yōu)化策略1 引言隨著計(jì)算機(jī)應(yīng)用技術(shù)的不斷普及和發(fā)展，數(shù)據(jù)庫系統(tǒng)正越來越多的走進(jìn)人們的日常生活。在要求查詢結(jié)果正確無誤的同時，人們越來越關(guān)心查詢的效率問題。影響查詢效率的因素很多，諸如處理器的速度、I/O速度、存儲器的容量、操作系統(tǒng)、采取何種的數(shù)據(jù)庫服務(wù)系統(tǒng)等。但是對于特定服務(wù)器來說查詢的效率主要取決于DBA（數(shù)據(jù)庫管理員）所給定的查詢語句。 2 合理使用索引 數(shù)據(jù)庫服務(wù)器對數(shù)據(jù)進(jìn)行訪問一般采用下面的兩種方式：索引掃描，通過索引訪問數(shù)據(jù)；表掃描，讀表中的所有頁。當(dāng)對一個表進(jìn)行查詢時，如果返回的行數(shù)占全表總行數(shù)的10%到15%時，使用索引可以極大的優(yōu)化查詢的性能。但是如果查詢涉及到

2、全表40%以上的行時，表掃描的效率比使用索引掃描的效率高。在具體使用的過程中，要結(jié)合實(shí)際的數(shù)據(jù)庫和用戶的需求來確定要不要索引以及在什么字段上建立什么樣的索引。下面給出一些通用的規(guī)則： 1. 在經(jīng)常用作過濾器或者查詢頻率較高字段上建立索引； 2. 在SQL語句中經(jīng)常進(jìn)行GROUP BY、ORDER BY的字段上建立索引；3. 在不同值較少的字段上不必要建立索引，如性別字段； 4. 對于經(jīng)常存取的列避免建立索引； 5. 用于聯(lián)接的列（主健/外?。┙⑺饕?； 6. 在經(jīng)常存取的多個列上建立復(fù)合索引，但要注意復(fù)合索引的建立順序要按照使用的頻度來確定。 2.1 聚集索引聚集索引是指行的物理順序與行的索引

3、順序相同的索引。一個表只能有一個聚集索引。非聚集索引是指定表的邏輯順序的索引，行的物理順序與索引順序不盡相同，每個表可以有多個非聚集索引。缺省情況下建立的是非聚集索引，但是在一些特定的情況下建立非聚集索引會極大的縮短查詢的時間。有大量重復(fù)值、且經(jīng)常有范圍查詢（between,=,=）和orderby、groupby發(fā)生的列，可考慮建立聚集索引，而對于頻繁修改的列、或者返回小數(shù)目的不同值的這些情況應(yīng)該避免建立聚集索引。 使用聚集索引的最大好處就是能夠根據(jù)查詢要求，迅速縮小查詢范圍，避免全表掃描。比如要返回2004年4月1日到2004年10月1日之間的數(shù)據(jù)，如果在日期的字段建立了聚集索引，那么數(shù)據(jù)

4、本來就是按照日期的順序排列的，只要找到開始和結(jié)尾日期的數(shù)據(jù)就可以了，可以極大的節(jié)省時間。而如果使用非聚集索引，必須查到這個時間段中每個日期對應(yīng)的位置，然后在根據(jù)位置存取數(shù)據(jù)，明顯效率很低。顯而易見，使用聚集索引的優(yōu)勢很明顯。一個表只能按照一個固定的順序來存儲數(shù)據(jù)，因此，在建立聚集索引的時候一定要和實(shí)際查詢相結(jié)合，看哪個字段對于查詢貢獻(xiàn)大，而且操作不是很頻繁。 索引有助于提高檢索性能，但過多或不當(dāng)?shù)乃饕矔?dǎo)致系統(tǒng)低效。因?yàn)橛脩粼诒碇忻刻砑右粋€索引，數(shù)據(jù)庫就要做更多的工作。過多的索引甚至?xí)?dǎo)致索引碎片。所以說，我們要合理使用索引體系，特別是對索引的創(chuàng)建，更應(yīng)精益求精，使數(shù)據(jù)庫的性能得到更好的發(fā)揮

5、。 3 書寫高效的SQL語句 雖然特定的數(shù)據(jù)庫服務(wù)器都會對輸入的查詢語句進(jìn)行一定的優(yōu)化操作，但是查詢效率主要取決于DBA所書寫的SQL語句的好壞。為確保編寫的SQL語句有較好的性能，應(yīng)考慮以下的優(yōu)化方法： 盡量減少使用負(fù)邏輯的操作符和函數(shù)，因?yàn)樗鼈儠?dǎo)致全表掃描，而且容易出錯。可以把含有NOT、 、！= 等負(fù)邏輯的條件表達(dá)式轉(zhuǎn)化為意思相當(dāng)?shù)恼壿嫛?字段提取要多少，取多少，避免使用“select *”格式，因?yàn)樵跀?shù)據(jù)量較大的時候，影響查詢性能的最大因素不在與數(shù)據(jù)的查找，而在于物理I/O的操作。 避免使用LIKE、EXISTS、IN等標(biāo)準(zhǔn)表達(dá)式，他們會使字段上的索引無效，引起全表掃描。盡量減少表

6、的聯(lián)接操作，不可避免的時候要適當(dāng)增加一些冗余條件，使參與聯(lián)接的字段集盡量少。 OR會使字段上的索引失效，引起全表掃描。下面的例子中，可以把or子句分開，在把結(jié)果做加法和算，也可以編寫一個存儲過程來避免索引的失效。 Select work-name, work-dept from work where work-id=2 or work-id=3; 盡量減少使用聯(lián)接字段而把所有的條件分列出來用and來進(jìn)行連接，可以充分的利用在某些字段上已經(jīng)存在的索引。 select work-id from salary where work-salary|”| work-dept=$2000 teacher

7、; 如果把條件分開來寫成下面的格式，系統(tǒng)的查詢性能可以得到一定的提高。 select work-no from salary where work-salary=$2000 and work-dept= teacher ; 盡量避免使用相關(guān)的嵌套查詢， 3.1 Where字句的影響 Where子句說明查詢的條件，直接決定查詢的性能。因此在where子句的書寫及應(yīng)用中要多加注意。書寫where 子句時盡量避免使用不兼容的數(shù)據(jù)類型，避免對where 子句中的條件參數(shù)使用其他的數(shù)學(xué)操作符，盡可能的把操作轉(zhuǎn)化到式子的左邊，這樣可以有效的利用已有的索引技術(shù)。對于where字句中的多個選擇條件，要選取結(jié)果

8、集小的先執(zhí)行。下面給出一些不規(guī)范書寫。 select work-id from salary where work-salary4000; select work-id from salary where work-salary*2$4000； 對于第一個查詢來說，4000是整數(shù)，而工人的工資時money格式的，系統(tǒng)在查詢的時候需要耗費(fèi)時間來進(jìn)行格式轉(zhuǎn)化。對于第二個例子，任何在運(yùn)算符左邊的操作都會使SQL采用全表掃描，對表中的每個數(shù)據(jù)項(xiàng)做相應(yīng)的操作來比較是否滿足條件，如果這個字段有索引，則索引失效。因此上面兩個例子最好可以寫成下面的格式： select work-no from salary

9、where work-salary$4000; select work-no from salary where work-salary$2000; 4 存儲過程的使用 存儲過程由SQL語句和SPL語言的語句組成,創(chuàng)建后轉(zhuǎn)換為可執(zhí)行代碼,作為數(shù)據(jù)庫的一個對象存儲在數(shù)據(jù)庫中,存儲過程的代碼駐留在服務(wù)器端,因而執(zhí)行時不需要將應(yīng)用程序代碼向服務(wù)器端傳送,可以大大減輕網(wǎng)絡(luò)負(fù)載，加快系統(tǒng)響應(yīng)時間。同時,由于存儲過程已編譯為可執(zhí)行代碼,不需要每次執(zhí)行時進(jìn)行分析和優(yōu)化工作,從而減少了預(yù)處理所花費(fèi)的時間,提高了系統(tǒng)的效率。 在工程中，我們可以把經(jīng)常用到的查詢動作編寫成一個存儲過程，并利用參數(shù)實(shí)現(xiàn)動態(tài)查詢過程來

10、響應(yīng)客戶的要求；可以實(shí)現(xiàn)在服務(wù)器端進(jìn)行批量數(shù)據(jù)處理等操作；可以使用存儲過程作為強(qiáng)制安全性工具;還可以利用系統(tǒng)為用戶定義的管理級別存儲過程實(shí)現(xiàn)數(shù)據(jù)的管理、配置和監(jiān)控等。合理使用存儲過程可以有效的提高系統(tǒng)效率。 5 視圖的應(yīng)用 利用視圖不僅可以提高數(shù)據(jù)的保密性，方便的設(shè)置用戶的權(quán)限，而且也可以提高數(shù)據(jù)的精煉性。在DBMS中有著許多不同的角色，他們對數(shù)據(jù)的要求是不同的，針對不同類別的用戶分別建立合適的視圖，可以在有效的條件下提高數(shù)據(jù)的有用性，提高系統(tǒng)對不同用戶的查詢響應(yīng)時間。此外用戶訪問數(shù)據(jù)庫一般要求得到的是最近的數(shù)據(jù)，比如查詢話費(fèi)，最常用的數(shù)據(jù)是最近三個月的。因此在許多情況下，可以按照時間對數(shù)據(jù)庫

11、中的數(shù)據(jù)進(jìn)行水平分片，把最近一段時間的數(shù)據(jù)呈現(xiàn)給用戶。當(dāng)用戶需要查找“過期”數(shù)據(jù)時再把相應(yīng)的塊調(diào)進(jìn)來。由于這種情況極少發(fā)生，在一定的情況下，可以有效的減少數(shù)據(jù)量，縮小數(shù)據(jù)查找范圍。使用這種方法要注意分區(qū)數(shù)據(jù)的維護(hù)，因此一定要在權(quán)衡維護(hù)和查詢代價的基礎(chǔ)上確定是否要使用分片。如果經(jīng)常要訪問全庫數(shù)據(jù)進(jìn)行綜合對比的話，這種方法就不適用。 6 小結(jié) 關(guān)系數(shù)據(jù)庫的優(yōu)化是一個和實(shí)際數(shù)據(jù)庫結(jié)構(gòu)密切相關(guān)的問題，在實(shí)際應(yīng)用中應(yīng)該結(jié)合具體的數(shù)據(jù)庫服務(wù)器，深入的理解服務(wù)器的運(yùn)作模式、資源配置，優(yōu)化服務(wù)器的運(yùn)行環(huán)境，選擇合適的操作系統(tǒng)，最大限度的發(fā)揮服務(wù)器的性能。實(shí)際使用SQL語句中要注意的地方數(shù)據(jù)庫設(shè)計(jì)是應(yīng)用程序設(shè)計(jì)

12、的基礎(chǔ)，其性能直接影響應(yīng)用程序的性能。數(shù)據(jù)庫性能包括存儲空間需求量的大小和查詢響應(yīng)時間的長短兩個方面。為了優(yōu)化數(shù)據(jù)庫性能，需要對數(shù)據(jù)庫中的表進(jìn)行規(guī)范化。規(guī)范化的范式可分為第一范式、第二范式、第三范式、BCNF范式、第四范式和第五范式。一般來說，邏輯數(shù)據(jù)庫設(shè)計(jì)會滿足規(guī)范化的前3級標(biāo)準(zhǔn),但由于滿足第三范式的表結(jié)構(gòu)容易維護(hù)且基本滿足實(shí)際應(yīng)用的要求。因此，實(shí)際應(yīng)用中一般都按照第三范式的標(biāo)準(zhǔn)進(jìn)行規(guī)范化。但是，規(guī)范化也有缺點(diǎn)：由于將一個表拆分成為多個表，在查詢時需要多表連接，降低了查詢速度。 由于規(guī)范化有可能導(dǎo)致查詢速度慢的缺點(diǎn)，考慮到一些應(yīng)用需要較快的響應(yīng)速度，在設(shè)計(jì)表時應(yīng)同時考慮對某些表進(jìn)行反規(guī)范化。

13、反規(guī)范化可以采用以下幾種方法： 1. 分割表 分割表包括水平分割和垂直分割。 水平分割是按照行將一個表分割為多個表，這可以提高每個表的查詢速度，但查詢、更新時要選擇不同的表，統(tǒng)計(jì)時要匯總多個表，因此應(yīng)用程序會更復(fù)雜。 垂直分割是對于一個列很多的表，若某些列的訪問頻率遠(yuǎn)遠(yuǎn)高于其它列，就可以將主鍵和這些列作為一個表，將主鍵和其它列作為另外一個表。通過減少列的寬度，增加了每個數(shù)據(jù)頁的行數(shù)，一次I/O就可以掃描更多的行，從而提高了訪問每一個表的速度。但是由于造成了多表連接，所以應(yīng)該在同時查詢或更新不同分割表中的列的情況比較少的情況下使用。 2. 保留冗余列 當(dāng)兩個或多個表在查詢中經(jīng)常需要連接時，可以在

14、其中一個表上增加若干冗余的列，以避免表之間的連接過于頻繁。由于對冗余列的更新操作必須對多個表同步進(jìn)行，所以一般在冗余列的數(shù)據(jù)不經(jīng)常變動的情況下使用。 3. 增加派生列 派生列是由表中的其它多個列計(jì)算所得，增加派生列可以減少統(tǒng)計(jì)運(yùn)算，在數(shù)據(jù)匯總時可以大大縮短運(yùn)算時間。 應(yīng)用程序性能的優(yōu)化 應(yīng)用程序的優(yōu)化通常可分為兩個方面：源代碼和SQL語句。由于涉及到對程序邏輯的改變，源代碼的優(yōu)化在時間成本和風(fēng)險上代價很高，而對數(shù)據(jù)庫系統(tǒng)性能的提升收效有限，因此應(yīng)用程序的優(yōu)化應(yīng)著重在SQL語句的優(yōu)化。對于海量數(shù)據(jù)，劣質(zhì)SQL語句和優(yōu)質(zhì)SQL語句之間的速度差別可以達(dá)到上百倍，可見對于一個系統(tǒng)不是簡單地能實(shí)現(xiàn)其功能

15、就行，而是要寫出高質(zhì)量的SQL語句，提高系統(tǒng)的可用性。 下面就某些SQL語句的where子句編寫中需要注意的問題作詳細(xì)介紹。在這些where子句中，即使某些列存在索引，但是由于編寫了劣質(zhì)的SQL，系統(tǒng)在運(yùn)行該SQL語句時也不能使用該索引，而同樣使用全表掃描，這就造成了響應(yīng)速度的極大降低。 1. IS NULL 與 IS NOT NULL 不能用null作索引，任何包含null值的列都將不會被包含在索引中。即使索引有多列的情況下，只要這些列中有一列含有null，該列就會從索引中排除。也就是說如果某列存在空值，即使對該列建索引也不會提高性能。 任何在where子句中使用is null或is not

16、 null的語句優(yōu)化器是不允許使用索引的。 2. 聯(lián)接列 對于有聯(lián)接的列，即使最后的聯(lián)接值為一個靜態(tài)值，優(yōu)化器不會使用索引的。例如，假定有一個職工表（employee），對于一個職工的姓和名分成兩列存放（FIRST_NAME和LAST_NAME），現(xiàn)在要查詢一個叫喬治布什（George Bush）的職工。 下面是一個采用聯(lián)接查詢的SQL語句： select * from employee where first_name|last_name =George Bush;上面這條語句完全可以查詢出是否有George Bush這個員工，但是這里需要注意，系統(tǒng)優(yōu)化器對基于last_name創(chuàng)建的索引沒

17、有使用。 當(dāng)采用下面這種SQL語句的編寫，Oracle系統(tǒng)就可以采用基于last_name創(chuàng)建的索引： Select * From employee where first_name =George and last_name =Bush;遇到下面這種情況又如何處理呢？如果一個變量（name）中存放著George Bush這個員工的姓名，對于這種情況我們又如何避免全程遍歷使用索引呢？可以使用一個函數(shù)，將變量name中的姓和名分開就可以了，但是有一點(diǎn)需要注意，這個函數(shù)是不能作用在索引列上。下面是SQL查詢腳本： select * from employee where first_name =

18、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%;這里由于通配符（%）在搜尋詞首出現(xiàn)，所以O(shè)racle系統(tǒng)不使用last_name的索引。在很多情況下可能無法避免這種情況，但是一定要心中有底，通配符如此使用會降低查詢速度。然而

19、當(dāng)通配符出現(xiàn)在字符串其他位置時，優(yōu)化器就能利用索引。例如，在下面的查詢中索引得到了使用： select * from employee where last_name like c%;4. Order by語句 Order by語句決定了Oracle如何將返回的查詢結(jié)果排序。Order by語句對要排序的列沒有什么特別的限制，也可以將函數(shù)加入列中（象聯(lián)接或者附加等）。任何在Order by語句的非索引項(xiàng)或者有計(jì)算表達(dá)式都將降低查詢速度。 仔細(xì)檢查order by語句以找出非索引項(xiàng)或者表達(dá)式，它們會降低性能。解決這個問題的辦法就是重寫order by語句以使用索引，也可以為所使用的列建立另外一個

20、索引，同時應(yīng)絕對避免在order by子句中使用表達(dá)式。 5. NOT 我們在查詢時經(jīng)常在where子句使用一些邏輯表達(dá)式，如大于、小于、等于以及不等于等等，也可以使用and（與）、or（或）以及not（非）。NOT可用來對任何邏輯運(yùn)算符號取反。下面是一個NOT子句的例子： . where not (status =VALID)如果要使用NOT，則應(yīng)在取反的短語前面加上括號，并在短語前面加上NOT運(yùn)算符。NOT運(yùn)算符包含在另外一個邏輯運(yùn)算符中，這就是不等于（）運(yùn)算符。換句話說，即使不在查詢where子句中顯式地加入NOT詞，NOT仍在運(yùn)算符中，見下例： . where status INVAL

21、ID;再看下面這個例子： select * from employee wheresalary3000;對這個查詢，可以改寫為不使用NOT的語句： select * from employee wheresalary3000;雖然這兩種查詢的結(jié)果一樣，但是第二種查詢方案會比第一種查詢方案更快些。第二種查詢允許Oracle對salary列使用索引，而第一種查詢則不能使用索引。 6. IN和EXISTS 有時候會將一列和一系列值相比較。最簡單的辦法就是在where子句中使用子查詢。在where子句中可以使用兩種格式的子查詢。 第一種格式是使用IN操作符： . where column in(sel

22、ect * from . where .);第二種格式是使用EXIST操作符： . where exists (select X from .where .);絕大多數(shù)人會使用第一種格式，因?yàn)樗容^容易編寫，而實(shí)際上第二種格式要遠(yuǎn)比第一種格式的效率高。在Oracle中可以將幾乎所有的IN操作符子查詢改寫為使用EXISTS的子查詢。 第二種格式中，子查詢以select X開始。運(yùn)用EXISTS子句不管子查詢從表中抽取什么數(shù)據(jù)它只查看where子句。這樣優(yōu)化器就不必遍歷整個表而僅根據(jù)索引就可完成工作（這里假定在where語句中使用的列存在索引）。相對于IN子句來說，EXISTS使用相連子查詢，構(gòu)造

23、起來要比IN子查詢困難一些。 通過使用EXISTS，Oracle系統(tǒng)會首先檢查主查詢，然后運(yùn)行子查詢直到找到第一個匹配項(xiàng)，這就節(jié)省了時間。Oracle系統(tǒng)在執(zhí)行IN子查詢時，首先執(zhí)行子查詢，并將獲得的結(jié)果列表存放在一個加了索引的臨時表中。在執(zhí)行子查詢之前，系統(tǒng)先將主查詢掛起，待子查詢執(zhí)行完畢，存放在臨時表中以后再執(zhí)行主查詢。這也就是使用EXISTS比使用IN通常查詢速度快的原因。 同時應(yīng)盡可能使用NOT EXISTS來代替NOT IN，盡管二者都使用了NOT（不能使用索引而降低速度），但NOT EXISTS要比NOT IN查詢效率更高。 其他相關(guān)資料：ORACLE SQL性能優(yōu)化系列 (一)

24、作者：Black_Snail 1. 選用適合的ORACLE優(yōu)化器 ORACLE的優(yōu)化器共有3種: a. RULE (基于規(guī)則) b. COST (基于成本) c. CHOOSE (選擇性) 設(shè)置缺省的優(yōu)化器,可以通過對init.ora文件中OPTIMIZER_MODE參數(shù)的各種聲明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS . 你當(dāng)然也在SQL句級或是會話(session)級對其進(jìn)行覆蓋. 為了使用基于成本的優(yōu)化器(CBO, Cost-Based Optimizer) , 你必須經(jīng)常運(yùn)行analyze 命令,以增加數(shù)據(jù)庫中的對象統(tǒng)計(jì)信息(object stat

25、istics)的準(zhǔn)確性. 如果數(shù)據(jù)庫的優(yōu)化器模式設(shè)置為選擇性(CHOOSE),那么實(shí)際的優(yōu)化器模式將和是否運(yùn)行過analyze命令有關(guān). 如果table已經(jīng)被analyze過, 優(yōu)化器模式將自動成為CBO , 反之,數(shù)據(jù)庫將采用RULE形式的優(yōu)化器. 在缺省情況下,ORACLE采用CHOOSE優(yōu)化器, 為了避免那些不必要的全表掃描(full table scan) , 你必須盡量避免使用CHOOSE優(yōu)化器,而直接采用基于規(guī)則或者基于成本的優(yōu)化器. 2. 訪問Table的方式 ORACLE 采用兩種訪問表中記錄的方式: a. 全表掃描 全表掃描就是順序地訪問表中每條記錄. ORACLE采用一次讀

26、入多個數(shù)據(jù)塊(database block)的方式優(yōu)化全表掃描. b. 通過ROWID訪問表 你可以采用基于ROWID的訪問方式情況,提高訪問表的效率, , ROWID包含了表中記錄的物理位置信息.ORACLE采用索引(INDEX)實(shí)現(xiàn)了數(shù)據(jù)和存放數(shù)據(jù)的物理位置(ROWID)之間的聯(lián)系. 通常索引提供了快速訪問ROWID的方法,因此那些基于索引列的查詢就可以得到性能上的提高. 3. 共享SQL語句 為了不重復(fù)解析相同的SQL語句,在第一次解析之后, ORACLE將SQL語句存放在內(nèi)存中.這塊位于系統(tǒng)全局區(qū)域SGA(system global area)的共享池(shared buffer po

27、ol)中的內(nèi)存可以被所有的數(shù)據(jù)庫用戶共享. 因此,當(dāng)你執(zhí)行一個SQL語句(有時被稱為一個游標(biāo))時,如果它 和之前的執(zhí)行過的語句完全相同, ORACLE就能很快獲得已經(jīng)被解析的語句以及最好的 執(zhí)行路徑. ORACLE的這個功能大大地提高了SQL的執(zhí)行性能并節(jié)省了內(nèi)存的使用. 可惜的是ORACLE只對簡單的表提供高速緩沖(cache buffering) ,這個功能并不適用于多表連接查詢. 數(shù)據(jù)庫管理員必須在init.ora中為這個區(qū)域設(shè)置合適的參數(shù),當(dāng)這個內(nèi)存區(qū)域越大,就可以保留更多的語句,當(dāng)然被共享的可能性也就越大了. 當(dāng)你向ORACLE 提交一個SQL語句,ORACLE會首先在這塊內(nèi)存中查找

28、相同的語句. 這里需要注明的是,ORACLE對兩者采取的是一種嚴(yán)格匹配,要達(dá)成共享,SQL語句必須 完全相同(包括空格,換行等). 共享的語句必須滿足三個條件: A. 字符級的比較: 當(dāng)前被執(zhí)行的語句和共享池中的語句必須完全相同. 例如: SELECT * FROM EMP; 和下列每一個都不同 SELECT * from EMP; Select * From Emp; SELECT * FROM EMP;bruce_lee2005-02-23, 14:334. 選擇最有效率的表名順序(只在基于規(guī)則的優(yōu)化器中有效) ORACLE的解析器按照從右到左的順序處理FROM子句中的表名,因此FROM子

29、句中寫在最后的表(基礎(chǔ)表 driving table)將被最先處理. 在FROM子句中包含多個表的情況下,你必須選擇記錄條數(shù)最少的表作為基礎(chǔ)表.當(dāng)ORACLE處理多個表時, 會運(yùn)用排序及合并的方式連接它們.首先,掃描第一個表(FROM子句中最后的那個表)并對記錄進(jìn)行派序,然后掃描第二個表(FROM子句中最后第二個表),最后將所有從第二個表中檢索出的記錄與第一個表中合適記錄進(jìn)行合并. 例如: 表 TAB1 16,384 條記錄 表 TAB2 1 條記錄 選擇TAB2作為基礎(chǔ)表 (最好的方法) select count(*) from tab1,tab2 執(zhí)行時間0.96秒 選擇TAB2作為基礎(chǔ)表

30、 (不佳的方法) select count(*) from tab2,tab1 執(zhí)行時間26.09秒 如果有3個以上的表連接查詢, 那就需要選擇交叉表(intersection table)作為基礎(chǔ)表, 交叉表是指那個被其他表所引用的表. 例如: EMP表描述了LOCATION表和CATEGORY表的交集. SELECT * FROM LOCATION L , CATEGORY C, EMP E WHERE E.EMP_NO BETWEEN 1000 AND 2000 AND E.CAT_NO = C.CAT_NO AND E.LOCN = L.LOCN 將比下列SQL更有效率 SELECT

31、* FROM EMP E ,LOCATION L , CATEGORY C WHERE E.CAT_NO = C.CAT_NO AND E.LOCN = L.LOCN AND E.EMP_NO BETWEEN 1000 AND 2000bruce_lee2005-02-23, 15:575，WHERE子句中的連接順序ORACLE采用自下而上的順序解析WHERE子句,根據(jù)這個原理,表之間的連接必須寫在其他WHERE條件之前, 那些可以過濾掉最大數(shù)量記錄的條件必須寫在WHERE子句的末尾.。bruce_lee2005-02-23, 21:587. 減少訪問數(shù)據(jù)庫的次數(shù) 當(dāng)執(zhí)行每條SQL語句時, O

32、RACLE在內(nèi)部執(zhí)行了許多工作: 解析SQL語句, 估算索引的利用率, 綁定變量 , 讀數(shù)據(jù)塊等等. 由此可見, 減少訪問數(shù)據(jù)庫的次數(shù) , 就能實(shí)際上減少ORACLE的工作量.bruce_lee2005-02-23, 21:590bruce_lee2005-02-23, 22:036，避免在select 語句中使用*這是一個非常低效的方法. 實(shí)際上，ORACLE在解析的過程中，會將 * 依次轉(zhuǎn)換成所有的列名，這個工作是通過查詢數(shù)據(jù)字典完成的，這意味著將耗費(fèi)更多的時間。bruce_lee2005-02-23, 22:118. 使用DECODE函數(shù)來減少處理時間 使用DECODE函數(shù)可以避免重復(fù)掃

33、描相同記錄或重復(fù)連接相同的表.bruce_lee2005-02-23, 22:1410， 刪除重復(fù)記錄 最高效的刪除重復(fù)記錄方法 ( 因?yàn)槭褂昧薘OWID) DELETE FROM EMP E WHERE E.ROWID (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO)；bruce_lee2005-02-23, 22:1511. 用TRUNCATE替代DELETE 當(dāng)刪除表中的記錄時,在通常情況下, 回滾段(rollback segments ) 用來存放可以被恢復(fù)的信息. 如果你沒有COMMIT事務(wù),ORACLE會將數(shù)據(jù)恢復(fù)

34、到刪除之前的狀態(tài)(準(zhǔn)確地說是 恢復(fù)到執(zhí)行刪除命令之前的狀況) 而當(dāng)運(yùn)用TRUNCATE時, 回滾段不再存放任何可被恢復(fù)的信息.當(dāng)命令運(yùn)行后,數(shù)據(jù)不能被恢復(fù).因此很少的資源被調(diào)用,執(zhí)行時間也會很短bruce_lee2005-02-23, 22:16只要有可能,在程序中盡量多使用COMMIT, 這樣程序的性能得到提高,需求也會因?yàn)镃OMMIT所釋放的資源而減少: COMMIT所釋放的資源: a. 回滾段上用于恢復(fù)數(shù)據(jù)的信息. b. 被程序語句獲得的鎖 c. redo log buffer 中的空間 d. ORACLE為管理上述3種資源中的內(nèi)部花費(fèi) (譯者按: 在使用COMMIT時必須要注意到事務(wù)的

35、完整性,現(xiàn)實(shí)中效率和事務(wù)完整性往往是魚和熊掌不可得兼)bruce_lee2005-02-23, 22:1713. 計(jì)算記錄條數(shù) 和一般的觀點(diǎn)相反, count(*) 比count(1)稍快 , 當(dāng)然如果可以通過索引檢索,對索引列的計(jì)數(shù)仍舊是最快的. 例如 COUNT(EMPNO) (譯者按: 在CSDN論壇中,曾經(jīng)對此有過相當(dāng)熱烈的討論, 作者的觀點(diǎn)并不十分準(zhǔn)確,通過實(shí)際的測試,上述三種方法并沒有顯著的性能差別)bruce_lee2005-02-23, 22:1814. 用Where子句替換HAVING子句 避免使用HAVING子句, HAVING 只會在檢索出所有記錄之后才對結(jié)果集進(jìn)行過濾.

36、 這個處理需要排序,總計(jì)等操作. 如果能通過WHERE子句限制記錄的數(shù)目,那就能減少這方面的開銷.HAVING 中的條件一般用于對一些集合函數(shù)的比較,如COUNT() 等等. 除此而外,一般的條件應(yīng)該寫在WHERE子句中bruce_lee2005-02-23, 22:2015. 減少對表的查詢 在含有子查詢的SQL語句中,要特別注意減少對表的查詢。低效: UPDATE EMP SET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES), SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES) WHERE EMP_DEPT = 0020; 高效: UPDATE EMP SET (EMP_CAT, SAL_RANGE) = (SELECT MAX(CATEGORY) , MAX(SAL_RANGE) FROM EMP_CATEGORIES) WHERE EMP_DEPT = 0020bruce_lee2