sql語句性能提高_(dá)百度文庫

1、sql 語句性能提高很久不來了 , 最近忙于 Oracle 數(shù)據(jù)庫大數(shù)據(jù)量的業(yè)務(wù)邏輯處理 , 整理一些零散的網(wǎng)上資料 . 項目忙完后再 結(jié)合自己經(jīng)驗 , 給出文章 . 申明 :下面內(nèi)容來自合理使用索引索引是數(shù)據(jù)庫中重要的數(shù)據(jù)結(jié)構(gòu),它的根本目的就是為了提高查詢效率?，F(xiàn)在大多數(shù)的數(shù)據(jù)庫產(chǎn)品 都采用 IBM 最先提出的 ISAM 索引結(jié)構(gòu)。索引的使用要恰到好處,其使用原則如下:在經(jīng)常進(jìn)行連接,但是沒有指定為外鍵的列上建立索引,而不經(jīng)常連接的字段則由優(yōu)化器自動生 成索引。在頻繁進(jìn)行排序或分組(即進(jìn)行 group by或 order by操作的列上建立索引。在條件表達(dá)式中經(jīng)常用到的不同值較多的列上建立檢

2、索,在不同值少的列上不要建立索引。比如 在雇員表的“性別”列上只有“男”與“女”兩個不同值,因此就無必要建立索引。如果建立索引不但不會 提高查詢效率,反而會嚴(yán)重降低更新速度。如果待排序的列有多個,可以在這些列上建立復(fù)合索引(compound index。使用系統(tǒng)工具。如 Informix 數(shù)據(jù)庫有一個 tbcheck 工具,可以在疑的索引上進(jìn)行檢查。在一些數(shù)據(jù)庫服務(wù)器上,索引可能失效或者因為頻繁操作而使得讀取效率降 低,如果一個使用索引的查詢不明不白地慢下來,可以試著用 tbcheck 工具檢查索引的完整性,必要時進(jìn)行 修復(fù)。另外,當(dāng)數(shù)據(jù)庫表更新大量數(shù)據(jù)后,刪除并重建索引可以提高查詢速度。(1

3、在下面兩條 select 語句中 :select * from table1 where field1=0;select * from table1 where field1=0 and field1=0,則第一條 select 語句要比第二條 select 語句效率高的多, 因為第二條 select 語句的第一個條件耗費了大量的系統(tǒng)資源。第一個原則:在 where 子句中應(yīng)把最具限制性的條件放在最前面。 (2 select * from tab where a= and b= and c= ;若有索引 index(a,b,c,則 where 子句中字段的順序應(yīng)和索引中字段順序一致。第二個原則

4、:where 子句中字段的順序應(yīng)和索引中字段順序一致。以下假設(shè)在 field1上有唯一索引 I1,在 field2上有非唯一索引 I2。(3select field3,field4 from tb where field1=sdf 快(4select * from tb where field1=sdf 慢,因為后者在索引掃描后要多一步 ROWID 表訪問。(4select field3,field4 from tb where field1=sdf 快(5select field3,field4 from tb where field1sdf 慢因為前者可以迅速定位索引。 (5 select

5、 field3,field4 from tb where field2 like R% 快 select field3,field4 from tb where field2 like %R 慢,因為后者不使用索引。(6select field3,field4 from tb where upper(field2=RMN不使用索引。如果一個表有兩萬條記錄,建議不使用函數(shù);如果一個表有五萬條以上記錄,嚴(yán)格禁止使用函數(shù)!兩萬 條記錄以下沒有限制。(7 空值不在索引中存儲 ,所以select field3,field4 from tb where field2 isnot null不使用索引。(8

6、不等式 如select field3,field4 from tb where field2!=TOM不使用索引 。相似地,select field3,field4 from tb where field2 not in(M,P不使用索引。(9多列索引,只有當(dāng)查詢中索引首列被用于條件時,索引才能被使用。(10MAX, MIN 等函數(shù),如Select max(field2 from tb使用索引。所以,如 果需要對字段取 max , min , sum 等,應(yīng)該加索引。 一次只使用一個聚集函數(shù),如:select “min”=min(field1, “max”=max(field1 from tb

7、不如:select “min”=(select min(field1 from tb , “max”=(select max(field1 from tb (11重復(fù)值過多的索引不會被查詢優(yōu)化器使用 。而且因為建了索引,修改該字段值時還要修改索引,所以 更 新該字段的操作比沒有索引更慢 。(12索引值過大(如在一個 char(40的字段上建索引,會造成大量的 I/O開銷(甚至?xí)^表掃描的 I/O開銷。因此, 盡量使用整數(shù)索引 。 Sp_estspace可以計算表和索引的開銷。(13對于多列索引, order by的順序必須和索引的字段順序一致。(14在 sybase 中,如果 order b

8、y的字段組成一個簇索引,那么無須做 order by。記錄的排列順序是與簇索 引一致的。(15多表聯(lián)結(jié)(具體查詢方案需要通過測試得到where 子句中限定條件盡量使用相關(guān)聯(lián)的字段,且盡量把相關(guān)聯(lián)的字段放在前面。select a.field1,b.field2 from a,b where a.field3=b.field31. field3上沒有索引 的情況下 :對 a 作全表掃描,結(jié)果排序?qū)?b 作全表掃描,結(jié)果排序結(jié)果合并。對于很小的表或巨大的表比較合適。2. field3上有索引按照表聯(lián)結(jié)的次序, b 為驅(qū)動表, a 為被驅(qū)動表對 b 作全表掃描對 a 作索引范圍掃描如果匹配,通過 a

9、的 rowid 訪問(16避免一對多的 join 。 如:select tb1.field3,tb1.field4,tb2.field2 from tb1,tb2 where tb1.field2=tb2.field2 and tb1.field2=BU1032 and tb2.field2= aaa不如:declare a varchar(80select a=field2 from tb2 where field2=aaaselect tb1.field3,tb1.field4,a from tb1 where field2= aaa(16子查詢用 exists/not exists代替

10、in/not in操作比較:select a.field1 from a where a.field2 in(select b.field1 from b where b.field2=100select a.field1 from a where exists( select 1 from b where a.field2=b.field1 and b.field2=100 select field1 from a where field1 not in( select field2 from bselect field1 from a where not exists( select 1

11、from b where b.field2=a.field1(17主、外鍵主要用于數(shù)據(jù)約束, sybase 中創(chuàng)建主鍵時會自動創(chuàng)建索引,外鍵與索引無關(guān),提高性能必須再 建索引。(18char 類型的字段不建索引比 int 類型的字段不建索引更糟糕。建索引后性能只稍差一點。(19使用 count(*而不要使用 count(column_name,避免使用 count(distinct column_name。(20等號右邊盡量不要使用字段名, 如:select * from tb where field1 = field3(21避免使用 or 條件,因為 or 不使用索引。2. 避免使用 ord

12、er by和 group by字句。因為使用這兩個子句會占用大量的臨時空間 (tempspace,如果一定要使用,可用視圖、人工生成臨時表 的方法來代替。如果必須使用,先檢查 memory 、 tempdb 的大小。測試證明,特別要避免一個查詢里既使用 join 又使用 group by,速度會非常慢!3. 盡量少用子查詢,特別是相關(guān)子查詢 。因為這樣會導(dǎo)致效率下降。一個列的標(biāo)簽同時在主查詢和 where 子句中的查詢中出現(xiàn),那么很可能當(dāng)主查詢中的列值改變之后,子 查詢必須重新查詢一次。 查詢嵌套層次越多,效率越低,因此應(yīng)當(dāng)盡量避免子查詢 。如果子查詢不可避免, 那么要在子查詢中過濾掉盡可能多

13、的行。4. 消除對大型表行數(shù)據(jù)的順序存取在嵌套查詢中,對表的順序存取對查詢效率可能產(chǎn)生致命的影響。比如采用順序存取策略,一個嵌套 3層的查詢,如果每層都查詢 1000行,那么這個查詢就要查詢 10億行數(shù)據(jù)。 避免這種情況的主要方法就是對 連接的列進(jìn)行索引。例如,兩個表:學(xué)生表(學(xué)號、姓名、年齡和選課表(學(xué)號、課程號、成績。如果兩個表要做連接,就要在“學(xué)號”這個連接字段上建立索引。還可以 使用并集來避免順序存取。 盡管在所有的檢查列上都有索引,但某些形式的 where 子句強迫優(yōu)化 器使用順序存取。下面的查詢將強迫對 orders 表執(zhí)行順序操作:SELECT * FROM orders WHE

14、RE (customer_num=104 AND order_num1001 OR order_num=1008雖然在 customer_num和 order_num上建有索引, 但是在上面的語句中優(yōu)化器還是使用順序存取路徑掃描 整個表。因為這個語句要檢索的是分離的行的集合,所以應(yīng)該改為如下語句:SELECT * FROM orders WHERE customer_num=104 AND order_num1001UNIONSELECT * FROM orders WHERE order_num=1008這樣就能利用索引路徑處理查詢。5. 避免困難的正規(guī)表達(dá)式MATCHES 和 LIKE 關(guān)

15、鍵字支持通配符匹配, 技術(shù)上叫正規(guī)表達(dá)式。 但這種匹配特別耗費時間。 例如:SELECT *FROM customer WHERE zipcode LIKE “98_ _ _” 即使在 zipcode 字段上建立了索引,在這種情況下也 還是采用順序掃描的方式。如果把語句改為 SELECT *FROM customer WHERE zipcode “98000”,在執(zhí)行 查詢時就會利用索引來查詢,顯然會大大提高速度。另外,還要避免非開始的子串。例如語句:SELECT * FROM customer WHERE zipcode2,3 “80”, 在 where 子句中采用了非開始子串,因而這個語句

16、也不會使用索引。6. 使用臨時表加速查詢把表的一個子集進(jìn)行排序并創(chuàng)建臨時表,有時能加速查詢。它有助于避免多重排序操作,而且在其他方 面還能簡化優(yōu)化器的工作。例如:SELECT , rcvbles.balance ,other columnsFROM cust, rcvblesWHERE cust.customer_id = rcvlbes.customer_idAND rcvblls.balance0AND cust.postcode“98000”O(jiān)RDER BY 如果這個查詢要被執(zhí)行多次而不止一次,可以把所有未付款的客戶找出來放在一個臨時文件中,并按客 戶

17、的名字進(jìn)行排序:SELECT , rcvbles.balance ,other columnsFROM cust, rcvblesWHERE cust.customer_id = rcvlbes.customer_idAND rcvblls.balance0ORDER BY INTO TEMP cust_with_balance然后以下面的方式在臨時表中查詢:SELECT * FROM cust_with_balanceWHERE postcode“98000”臨時表中的行要比主表中的行少,而且物理順序就是所要求的順序,減少了磁盤 I/O,所以查詢工作量 可

18、以得到大幅減少。注意:臨時表創(chuàng)建后不會反映主表的修改。在主表中數(shù)據(jù)頻繁修改的情況下,注意不要丟失數(shù)據(jù)。 7. 用排序來取代非順序存取非順序磁盤存取是最慢的操作,表現(xiàn)在磁盤存取臂的來回移動。 SQL 語句隱藏了這一情況,使得我們在寫應(yīng) 用程序時很容易寫出要求存取大量非順序頁的查詢。有些時候,用數(shù)據(jù)庫的排序能力來替代非順序的存取能改進(jìn)查詢。ORACLE 的執(zhí)行計劃共享 sql 語句為了不重復(fù)解析相同的 SQL 語句 (因為解析操作比較費資源,會導(dǎo)致性能下降 ,在第一次解析之后, ORACLE 將 SQL 語句及解析后得到的執(zhí)行計劃存放在內(nèi)存中。 這塊位于系統(tǒng)全局區(qū)域 SGA(system glob

19、al area 的共享池 (shared buffer pool中的內(nèi)存可以被所有的數(shù)據(jù)庫用戶共享 。因此,當(dāng)你執(zhí)行一個 SQL 語句 (有 時被稱為一個游標(biāo) 時, 如果該語句和之前的執(zhí)行過的某一語句完全相同, 并且之前執(zhí)行的該語句與其執(zhí)行計 劃仍然在內(nèi)存中存在,則 ORACLE 就不需要再進(jìn)行分析,直接得到該語句的執(zhí)行路徑。 ORACLE 的這個功能大 大地提高了 SQL 的執(zhí)行性能并大大節(jié)省了內(nèi)存的使用。使用這個功能的關(guān)鍵是將執(zhí)行過的語句盡可能放到內(nèi) 存中,所以這要求有大的共享池 (通過設(shè)置 shared buffer pool 參數(shù)值 和盡可能的使用綁定變量的方法執(zhí)行 SQL 語句。當(dāng)你

20、向 ORACLE 提交一個 SQL 語句, ORACLE 會首先在共享內(nèi)存中查找是否有相同的語句。這里需要注明 的是, ORACLE 對兩者采取的是一種嚴(yán)格匹配,要達(dá)成共享, SQL 語句必須完全相同 (包括空格 , 換行等 。 下面是判斷 SQL 語句是否與共享內(nèi)存中某一 SQL 相同的步驟:1 對所發(fā)出語句的文本串進(jìn)行 hashed 。如果 hash 值與已在共享池中 SQL 語句的 hash 值相同,則進(jìn)行 第 2步;2 將所發(fā)出語句的文本串 (包括大小寫、 空白和注釋 與在第1步中識別的所有已存在的 SQL 語句相比 較。例如:SELECT * FROM emp WHERE empno

21、 = 1000;和下列每一個都不同SELECT * from emp WHERE empno = 1000;SELECT * FROM EMP WHERE empno = 1000;SELECT * FROM emp WHERE empno = 2000;在上面的語句中列值都是直接 SQL 語句中的,今后我們將這類 sql 成為硬編碼 SQL 或字面值 SQL 使用綁定變量的 SQL 語句中必須使用相同的名字的綁定變量 (bind variables ,例如:a. 該 2個 sql 語句被認(rèn)為相同select pin , name from people where pin = :blk1.p

22、in;select pin , name from people where pin = :blk1.pin;b. 該 2個 sql 語句被認(rèn)為不相同select pin , name from people where pin = :blk1.ot_ind;select pin , name from people where pin = :blk1.ov_ind;今后我們將上面的這類語句稱為綁定變量 SQL 。3 將所發(fā)出語句中涉及的對象與第2步中識別的已存在語句所涉及對象相比較。例如 :如用戶 user1與用戶 user2下都有 EMP 表,則用戶 user1發(fā)出的語句:SELECT *

23、 FROM EMP;與用戶 user2發(fā)出的語句:SELECT * FROM EMP;被認(rèn)為是不相同的語句,因為兩個語句中引用的 EMP 不是指同一個表。4 在 SQL 語句中使用的捆綁變量的捆綁類型必須一致。如果語句與當(dāng)前在共享池中的另一個語句是等同的話, Oracle 并不對它進(jìn)行語法分析。而直接執(zhí)行該語 句,提高了執(zhí)行效率,因為語法分析比較耗費資源。注意的是,從 oracle 8i 開始,新引入了一個 CURSOR_SHARING參數(shù),該參數(shù)的主要目的就是為了解決在 編程過程中已大量使用的硬編碼 SQL 問題。因為在實際開發(fā)中,很多程序人員為了提高開發(fā)速度,而采用類 似下面的開發(fā)方法:

24、str_sql string;int_empno int;int_empno = 2000;str_sql = SELECT * FROM emp WHERE empno = + int_empno;int_empno = 1000;str_sql = SELECT * FROM emp WHERE empno = + int_empno;上面的代碼實際上使用了硬編碼 SQL ,使我們不能使用共享 SQL 的功能,結(jié)果是數(shù)據(jù)庫效率不高。但是 從上面的 2個語句來看,產(chǎn)生的硬編碼 SQL 只是列值不同,其它部分都是相同的,如果僅僅因為列值不同而 導(dǎo)致這 2個語句不能共享是很可惜的, 為了解決這個

25、問題, 引入了 CURSOR_SHARING參數(shù), 使這類問題也可以 使用共享 SQL ,從而使這樣的開發(fā)也可以利用共享 SQL 功能。聽起來不錯, ORACLE 真為用戶著想,使用戶在 不改變代碼的情況下還可以利用共享 SQL 的功能。真的如此嗎?天上不會無緣無故的掉一個餡餅的, ORACLE 對該參數(shù)的使用做了說明,建議在經(jīng)過實際測試后再改該參數(shù)的值 (缺省情況下,該參數(shù)的值為 EXACT ,語句 完全一致才使用共享 SQL 。因為有可能該變該值后,你的硬編碼 SQL 是可以使用共享 SQL 了,但數(shù)據(jù)庫的性 能反而會下降。 我在實際應(yīng)用中已經(jīng)遇到這種情況。 所以建議編寫需要穩(wěn)定運行程序的

26、開發(fā)人員最好還是一 開始就使用綁定變量的 SQL 。Rowid 的概念:rowid 是一個偽列,既然是偽列,那么這個列就不是用戶定義,而是系統(tǒng)自己給加上的。對每個表都有 一個 rowid 的偽列,但是表中并不物理存儲 ROWID 列的值。不過你可以像使用其它列那樣使用它,但是不能 刪除改列,也不能對該列的值進(jìn)行修改、插入。一旦一行數(shù)據(jù)插入數(shù)據(jù)庫,則 rowid 在該行的生命周期內(nèi)是 唯一的,即即使該行產(chǎn)生行遷移,行的 rowid 也不會改變。為什么使用 ROWIDrowid 對訪問一個表中的給定的行提供了最快的訪問方法, 通過 ROWID 可以直接定位到相應(yīng)的數(shù)據(jù)塊上, 然后將其讀到內(nèi)存。我們

27、創(chuàng)建一個索引時,該索引不但存儲索引列的值,而且也存儲索引值所對應(yīng)的行的 ROWID ,這樣我們通過索引快速找到相應(yīng)行的 ROWID 后,通過該 ROWID ,就可以迅速將數(shù)據(jù)查詢出來。這也就 是我們使用索引查詢時,速度比較快的原因。在 ORACLE8以前的版本中, ROWID 由 FILE 、 BLOCK 、 ROW NUMBER 構(gòu)成。隨著 oracle8中對象概念的擴展, ROWID 發(fā)生了變化, ROWID 由 OBJECT 、 FILE 、 BLOCK 、 ROW NUMBER構(gòu)成。利用 DBMS_ROWID可以將 rowid 分解 成上述的各部分,也可以將上述的各部分組成一個有效的

28、rowid 。Recursive SQL概念有時為了執(zhí)行用戶發(fā)出的一個 sql 語句, Oracle 必須執(zhí)行一些額外的語句,我們將這些額外的語句稱之 為 recursive calls或 recursive SQL statements。如當(dāng)一個 DDL 語句發(fā)出后, ORACLE 總是隱含的發(fā)出 一些 recursive SQL語句,來修改數(shù)據(jù)字典信息,以便用戶可以成功的執(zhí)行該 DDL 語句。當(dāng)需要的數(shù)據(jù)字典 信息沒有在共享內(nèi)存中時,經(jīng)常會發(fā)生 Recursive calls,這些 Recursive calls會將數(shù)據(jù)字典信息從硬盤 讀入內(nèi)存中。用戶不比關(guān)心這些 recursive SQ

29、L 語句的執(zhí)行情況,在需要的時候, ORACLE 會自動的在內(nèi)部執(zhí) 行這些語句。 當(dāng)然 DML 語句與 SELECT 都可能引起 recursive SQL 。 簡單的說, 我們可以將觸發(fā)器視為 recursive SQL 。Row Source(行源 用在查詢中,由上一操作返回的符合條件的行的集合,即可以是表的全部行數(shù)據(jù)的集合;也可以是表的 部分行數(shù)據(jù)的集合;也可以為對上 2個 row source進(jìn)行連接操作 (如 join 連接 后得到的行數(shù)據(jù)集合。 Predicate(謂詞 一個查詢中的 WHERE 限制條件Driving Table(驅(qū)動表 該表又稱為外層表 (OUTER TABLE

30、 。這個概念用于嵌套與 HASH 連接中。如果該 row source 返回較多的行 數(shù)據(jù), 則對所有的后續(xù)操作有負(fù)面影響。 注意此處雖然翻譯為驅(qū)動表, 但實際上翻譯為驅(qū)動行源 (driving row source 更為確切。 一般說來,是應(yīng)用查詢的限制條件后, 返回較少行源的表作為驅(qū)動表,所以如果一個大表 在 WHERE 條件有有限制條件 (如等值限制 ,則該大表作為驅(qū)動表也是合適的,所以并不是只有較小的表可以 作為驅(qū)動表,正確說法應(yīng)該為應(yīng)用查詢的限制條件后,返回較少行源的表作為驅(qū)動表。在執(zhí)行計劃中,應(yīng)該 為靠上的那個 row source,后面會給出具體說明。在我們后面的描述中,一般將該

31、表稱為連接操作的 row source 1。Probed Table(被探查表 該表又稱為內(nèi)層表 (INNER TABLE 。在我們從驅(qū)動表中得到具體一行的數(shù)據(jù)后,在該表中尋找符合連接條 件的行。所以該表應(yīng)當(dāng)為大表 (實際上應(yīng)該為返回較大 row source 的表 且相應(yīng)的列上應(yīng)該有索引。在我們后 面的描述中,一般將該表稱為連接操作的 row source 2。組合索引 (concatenated index由多個列構(gòu)成的索引,如 create index idx_emp on emp(col1, col2, col3, ,則我們稱 idx_emp索引為組合索引。在組合索引中有一個重要的概念

32、:引導(dǎo)列 (leading column ,在上面的例子中, col1列為 引導(dǎo)列。當(dāng)我們進(jìn)行查詢時可以使用”where col1 = ? ”,也可以使用”where col1 = ? and col2 = ?”, 這樣的限制條件都會使用索引,但是”where col2 = ? ”查詢就不會使用該索引。所以限制條件中包含先導(dǎo) 列時,該限制條件才會使用該組合索引?？蛇x擇性 (selectivity:比較一下列中唯一鍵的數(shù)量和表中的行數(shù),就可以判斷該列的可選擇性。如果該列的”唯一鍵的數(shù)量 /表中的行數(shù)”的比值越接近 1,則該列的可選擇性越高,該列就越適合創(chuàng)建索引,同樣索引的可選擇性也越 高。在可選

33、擇性高的列上進(jìn)行查詢時,返回的數(shù)據(jù)就較少,比較適合使用索引查詢。有了這些背景知識后就開始介紹執(zhí)行計劃。為了執(zhí)行語句, Oracle 可能必須實現(xiàn)許多步驟。這些步驟中 的每一步可能是從數(shù)據(jù)庫中物理檢索數(shù)據(jù)行,或者用某種方法準(zhǔn)備數(shù)據(jù)行,供發(fā)出語句的用戶使用。 Oracle 用來執(zhí)行語句的這些步驟的組合被稱之為執(zhí)行計劃。執(zhí)行計劃是 SQL 優(yōu)化中最為復(fù)雜也是最為關(guān)鍵的部分, 只有知道了 ORACLE 在內(nèi)部到底是如何執(zhí)行該 SQL 語句后, 我們才能知道優(yōu)化器選擇的執(zhí)行計劃是否為最優(yōu)的。 執(zhí)行計劃對于 DBA 來說,就象財務(wù)報表對于財務(wù)人員一樣重要。所以我們面臨的問題主要是:如何得到執(zhí)行 計劃;如何

34、分析執(zhí)行計劃,從而找出影響性能的主要問題。下面先從分析樹型執(zhí)行計劃開始介紹,然后介紹 如何得到執(zhí)行計劃,再介紹如何分析執(zhí)行計劃。舉例:這個例子顯示關(guān)于下面 SQL 語句的執(zhí)行計劃。SELECT ename, job, sal, dnameFROM emp, deptWHERE emp.deptno = derpt.deptnoAND NOT EXISTS( SELECT *FROM salgradeWHERE emp.sal BETWEEN losal AND hisal ;此語句查詢薪水不在任何建議薪水范圍內(nèi)的所有雇員的名字,工作,薪水和部門名。訪問路徑 (方法 - access path優(yōu)

35、化器在形成執(zhí)行計劃時需要做的一個重要選擇是如何從數(shù)據(jù)庫查詢出需要的數(shù)據(jù)。對于 SQL 語句存取 的任何表中的任何行,可能存在許多存取路徑 (存取方法 ,通過它們可以定位和查詢出需要的數(shù)據(jù)。優(yōu)化器 選擇其中自認(rèn)為是最優(yōu)化的路徑。在物理層, oracle 讀取數(shù)據(jù),一次讀取的最小單位為數(shù)據(jù)庫塊 (由多個連續(xù)的操作系統(tǒng)塊組成 ,一次讀 取的最大值由操作系統(tǒng)一次 I/O的最大值與 multiblock 參數(shù)共同決定, 所以即使只需要一行數(shù)據(jù), 也是將該 行所在的數(shù)據(jù)庫塊讀入內(nèi)存。邏輯上, oracle 用如下存取方法訪問數(shù)據(jù):1 全表掃描(Full Table Scans, FTS為實現(xiàn)全表掃描, O

36、racle 讀取表中所有的行,并檢查每一行是否滿足語句的 WHERE 限制條件。 Oracle 順序地讀取分配給表的每個數(shù)據(jù)塊,直到讀到表的最高水線處 (high water mark, HWM ,標(biāo)識表的最后一個數(shù) 據(jù)塊 。一個多塊讀操作可以使一次 I/O能讀取多塊數(shù)據(jù)塊 (db_block_multiblock_read_count參數(shù)設(shè)定 , 而不是只讀取一個數(shù)據(jù)塊,這極大的減少了 I/O總次數(shù),提高了系統(tǒng)的吞吐量,所以利用多塊讀的方法可以 十分高效地實現(xiàn)全表掃描,而且只有在全表掃描的情況下才能使用多塊讀操作。在這種訪問模式下,每個數(shù) 據(jù)塊只被讀一次。 由于 HWM 標(biāo)識最后一塊被讀入的

37、數(shù)據(jù), 而 delete 操作不影響 HWM 值, 所以一個表的所有數(shù) 據(jù)被 delete 后,其全表掃描的時間不會有改善,一般我們需要使用 truncate 命令來使 HWM 值歸為 0。幸運 的是 oracle 10G后,可以人工收縮 HWM 的值。由 FTS 模式讀入的數(shù)據(jù)被放到高速緩存的 Least Recently Used (LRU列表的尾部,這樣可以使其快速 交換出內(nèi)存,從而不使內(nèi)存重要的數(shù)據(jù)被交換出內(nèi)存。使用 FTS 的前提條件:在較大的表上不建議使用全表 掃描,除非取出數(shù)據(jù)的比較多,超過總量的 5% - 10%,或你想使用并行查詢功能時。使用全表掃描的例子:SQL expla

38、in plan for select * from dual;Query Plan-SELECT STATEMENT CHOOSE Cost=TABLE ACCESS FULL DUAL2 通過 ROWID 的表存取(Table Access by ROWID或 rowid lookup行的 ROWID 指出了該行所在的數(shù)據(jù)文件、數(shù)據(jù)塊以及行在該塊中的位置,所以通過 ROWID 來存取數(shù)據(jù)可 以快速定位到目標(biāo)數(shù)據(jù)上,是 Oracle 存取單行數(shù)據(jù)的最快方法。為了通過 ROWID 存取表, Oracle 首先要獲 取被選擇行的 ROWID , 或者從語句的 WHERE 子句中得到, 或者通過表的

39、一個或多個索引的索引掃描得到。 Oracle 然后以得到的 ROWID 為依據(jù)定位每個被選擇的行。這種存取方法不會用到多塊讀操作,一次 I/O只能讀取一個數(shù)據(jù)塊。我們會經(jīng)常在執(zhí)行計劃中看到該存 取方法,如通過索引查詢數(shù)據(jù)。使用 ROWID 存取的方法:SQL explain plan for select * from dept where rowid = AAAAyGAADAAAAATAAF;Query Plan-SELECT STATEMENT CHOOSE Cost=1TABLE ACCESS BY ROWID DEPT ANALYZED3索引掃描(Index Scan或 index l

40、ookup我們先通過 index 查找到數(shù)據(jù)對應(yīng)的 rowid 值 (對于非唯一索引可能返回多個 rowid 值 , 然后根據(jù) rowid 直接從表中得到具體的數(shù)據(jù),這種查找方式稱為索引掃描或索引查找 (index lookup。一個 rowid 唯一的表 示一行數(shù)據(jù),該行對應(yīng)的數(shù)據(jù)塊是通過一次 i/o得到的,在此情況下該次 i/o只會讀取一個數(shù)據(jù)庫塊。在索引中,除了存儲每個索引的值外,索引還存儲具有此值的行對應(yīng)的 ROWID 值。索引掃描可以由 2步 組成:(1 掃描索引得到對應(yīng)的 rowid 值。(2 通過找到的 rowid 從表中讀出具體的數(shù)據(jù)。每步都是單獨的一次 I/O,但是對于索引,

41、由于經(jīng)常使用,絕大多數(shù)都已經(jīng) CACHE 到內(nèi)存中,所以第 1步的 I/O經(jīng)常是邏輯 I/O,即數(shù)據(jù)可以從內(nèi)存中得到。但是對于第 2步來說,如果表比較大,則其數(shù)據(jù)不可 能全在內(nèi)存中,所以其 I/O很有可能是物理 I/O,這是一個機械操作,相對邏輯 I/O來說,是極其費時間的。 所以如果多大表進(jìn)行索引掃描,取出的數(shù)據(jù)如果大于總量的 5% - 10%,使用索引掃描會效率下降很多。 如下列所示:SQL explain plan for select empno, ename from emp where empno=10;Query Plan-SELECT STATEMENT CHOOSE Cost

42、=1TABLE ACCESS BY ROWID EMP ANALYZEDINDEX UNIQUE SCAN EMP_I1注意 TABLE ACCESS BY ROWID EMP 部分, 這表明這不是通過 FTS 存取路徑訪問數(shù)據(jù), 而是通過 rowid lookup 存取路徑訪問數(shù)據(jù)的。 在此例中, 所需要的 rowid 是由于在索引查找 empno 列的值得到的, 這種方式是 INDEX UNIQUE SCAN查找,后面給予介紹, EMP_I1為使用的進(jìn)行索引查找的索引名字。但是如果查詢的數(shù)據(jù)能全在索引中找到,就可以避免進(jìn)行第 2步操作,避免了不必要的 I/O,此時即使 通過索引掃描取出的數(shù)

43、據(jù)比較多,效率還是很高的,因為這只會在索引中讀取。所以上面我在介紹基于規(guī)則 的優(yōu)化器時,使用了 select count(id from SWD_BILLDETAIL where cn 6,而沒有使用 select count(cn from SWD_BILLDETAIL where cn explain plan for select empno from emp where empno=10; - 只查詢 empno 列值Query Plan-SELECT STATEMENT CHOOSE Cost=1INDEX UNIQUE SCAN EMP_I1進(jìn)一步講,如果 sql 語句中對索引列進(jìn)

44、行排序,因為索引已經(jīng)預(yù)先排序好了,所以在執(zhí)行計劃中不需要 再對索引列進(jìn)行排序SQL explain plan for select empno, ename from empwhere empno 7876 order by empno;Query Plan- SELECT STATEMENT CHOOSE Cost=1從這個例子中可以看到:因為索引是已經(jīng)排序了的,所以將按照索引的順序查詢出符合條件的行,因此 避免了進(jìn)一步排序操作。根據(jù)索引的類型與 where 限制條件的不同,有 4種類型的索引掃描:索引唯一掃描 (index unique scan索引范圍掃描 (index range sc

45、an索引全掃描 (index full scan索引快速掃描 (index fast full scan(1 索引唯一掃描 (index unique scan通過唯一索引查找一個數(shù)值經(jīng)常返回單個 ROWID 。如果該唯一索引有多個列組成 (即組合索引 ,則 至少 要有組合索引的引導(dǎo)列 參與到該查詢中,如創(chuàng)建一個索引:create index idx_test on emp(ename, deptno, loc 。則 select ename from emp where ename = JACK and deptno = DEV語句可以使用該索引。如 果該語句只返回一行,則存取方法稱為索引唯

46、一掃描。而 select ename from emp where deptno = DEV 語句則不會使用該索引,因為 where 子句種沒有引導(dǎo)列。如果存在 UNIQUE 或 PRIMARY KEY 約束(它保證了 語句只存取單行的話, Oracle 經(jīng)常實現(xiàn)唯一性掃描。使用唯一性約束的例子:SQL explain plan forselect empno,ename from emp where empno=10;Query Plan-SELECT STATEMENT CHOOSE Cost=1TABLE ACCESS BY ROWID EMP ANALYZEDINDEX UNIQUE

47、SCAN EMP_I1(2 索引范圍掃描 (index range scan使用一個索引存取多行數(shù)據(jù),同上面一樣,如果索引是組合索引,如 (1所示,而且 select ename from emp where ename = JACK and deptno = DEV語句返回多行數(shù)據(jù),雖然該語句還是使用該組合索引進(jìn)行 查詢,可此時的存取方法稱為索引范圍掃描。在唯一索引上使用索引范圍掃描的典型情況下是在謂詞 (where限制條件 如 、 、 、 =、 explain plan for select empno,ename from empwhere empno 7876 order by emp

48、no;Query Plan-SELECT STATEMENT CHOOSE Cost=1在非唯一索引上,謂詞 col = 5可能返回多行數(shù)據(jù),所以在非唯一索引上都使用索引范圍掃描。使用 index rang scan的 3種情況:(a 在唯一索引列上使用了 range 操作符 ( = explain plan for select empno, ename from big_emp order by empno,ename;Query Plan-SELECT STATEMENT CHOOSE Cost=26INDEX FULL SCAN BE_IX ANALYZED(4 索引快速掃描 (ind

49、ex fast full scan掃描索引中的所有的數(shù)據(jù)塊,與 index full scan很類似,但是一個顯著的區(qū)別就是它不對查詢出的數(shù) 據(jù)進(jìn)行排序,即數(shù)據(jù)不是以排序順序被返回。在這種存取方法中,可以使用多塊讀功能,也可以使用并行讀 入,以便獲得最大吞吐量與縮短執(zhí)行時間。索引快速掃描的例子:BE_IX索引是一個多列索引:big_emp (empno,enameSQL explain plan for select empno,ename from big_emp;Query Plan-SELECT STATEMENT CHOOSE Cost=1INDEX FAST FULL SCAN BE

50、_IX ANALYZED只選擇多列索引的第 2列:SQL explain plan for select ename from big_emp;Query PlanSELECT STATEMENT CHOOSE Cost=1INDEX FAST FULL SCAN BE_IX ANALYZED表之間的連接Join 是一種試圖將兩個表結(jié)合在一起的謂詞,一次只能連接 2個表,表連接也可以被稱為表關(guān)聯(lián)。在后 面的敘述中,我們將會使用”row source”來代替”表”,因為使用 row source 更嚴(yán)謹(jǐn)一些,并且將參與連 接的 2個 row source分別稱為 row source1和 row

51、 source 2。 Join 過程的各個步驟經(jīng)常是串行操作,即使 相關(guān)的 row source 可以被并行訪問,即可以并行的讀取做 join 連接的兩個 row source 的數(shù)據(jù),但是在將表 中符合限制條件的數(shù)據(jù)讀入到內(nèi)存形成 row source 后, join 的其它步驟一般是串行的。有多種方法可以將 2個表連接起來, 當(dāng)然每種方法都有自己的優(yōu)缺點, 每種連接類型只有在特定的條件下才會發(fā)揮出其最大優(yōu)勢。 row source(表 之間的連接順序?qū)τ诓樵兊男视蟹浅４蟮挠绊?。通過首先存取特定的表,即將該表作 為驅(qū)動表,這樣可以先應(yīng)用某些限制條件,從而得到一個較小的 row source

52、 ,使連接的效率較高,這也就是 我們常說的要先執(zhí)行限制條件的原因。一般是在將表讀入內(nèi)存時,應(yīng)用 where 子句中對該表的限制條件。 根據(jù) 2個 row source的連接條件的中操作符的不同,可以將連接分為等值連接 (如 WHERE A.COL3 = B.COL4 、非等值連接 (WHERE A.COL3 B.COL4、外連接 (WHERE A.COL3 = B.COL4(+。上面的各個連接的 連接原理都基本一樣,所以為了簡單期間,下面以等值連接為例進(jìn)行介紹。在后面的介紹中,都已:SELECT A.COL1, B.COL2FROM A, BWHERE A.COL3 = B.COL4;為例進(jìn)行

53、說明,假設(shè) A 表為 Row Soruce1,則其對應(yīng)的連接操作關(guān)聯(lián)列為 COL 3; B 表為 Row Soruce2, 則其對應(yīng)的連接操作關(guān)聯(lián)列為 COL 4;連接類型:目前為止,無論連接操作符如何,典型的連接類型共有 3種:排序 - - 合并連接 (Sort Merge Join (SMJ 嵌套循環(huán) (Nested Loops (NL 哈希連接 (Hash Join排序 - - 合并連接 (Sort Merge Join, SMJ內(nèi)部連接過程:1 首先生成 row source1需要的數(shù)據(jù),然后對這些數(shù)據(jù)按照連接操作關(guān)聯(lián)列 (如 A.col3 進(jìn)行排序。2 隨后生成 row source

54、2需要的數(shù)據(jù),然后對這些數(shù)據(jù)按照與 sort source1對應(yīng)的連接操作關(guān)聯(lián)列 (如 B.col4 進(jìn)行排序。3 最后兩邊已排序的行被放在一起執(zhí)行合并操作,即將 2個 row source按照連接條件連接起來下面是連接步驟的圖形表示:MERGE/ SORT SORT| |Row Source 1 Row Source 2如果 row source已經(jīng)在連接關(guān)聯(lián)列上被排序,則該連接操作就不需要再進(jìn)行 sort 操作,這樣可以大大 提高這種連接操作的連接速度,因為排序是個極其費資源的操作,特別是對于較大的表。 預(yù)先排序的 row source 包括已經(jīng)被索引的列 (如 a.col3或 b.col4上有索引 或 row source已經(jīng)在前面的步驟中被排序了。 盡管合并兩個 row source的過程是串行的,但是可以并行訪問這兩個 row source(如并行讀入數(shù)據(jù),并行