一．編寫初衷描述

|

在應(yīng)有系統(tǒng)開發(fā)初期，由于數(shù)據(jù)庫數(shù)據(jù)較少，對于sql語句各種寫法的編寫體現(xiàn)不出sql的性能優(yōu)劣，隨著數(shù)據(jù)的不斷增加，出現(xiàn)海量數(shù)據(jù)，劣質(zhì)sql與優(yōu)質(zhì)sql在執(zhí)行效率甚至存在百倍差距，可見sql優(yōu)化的重要性

|

二．Sql語句性能優(yōu)化

|

2.1 認識Oracle的執(zhí)行過程

image

2.2 Oracle優(yōu)化法則---漏斗法則

image

2.3 Oracle 執(zhí)行計劃

2.3.1 什么是Oracle執(zhí)行計劃

執(zhí)行計劃是一條查詢語句在Oracle中執(zhí)行過程或者訪問路徑的描述.

2.3.2 查看Oracle執(zhí)行計劃

1.執(zhí)行計劃常用的列字段解釋

基數(shù)：返回的結(jié)果集行數(shù)

字節(jié)：執(zhí)行該步驟后返回的字節(jié)數(shù)

耗費(cust),CPU耗費：Oracle估計的該步驟的執(zhí)行成本，用于說明SQL執(zhí)行的代價，理論上越小越好.

2.3.3 看懂Oracle執(zhí)行計劃

image

2.3.3.1執(zhí)行順序

根據(jù)縮進來判斷，縮進最多的最先執(zhí)行(縮進相同時，最上面的最先執(zhí)行)

2.4 表的訪問方式

• TABLE ACCESS FULL(全表掃描)
• TABLE ACCESS BY ROWID(通過rowid的表存取)
• TABLE ACCESS BY INDEX SCAN(索引掃描)

image

2.4.1 ABLE ACCESS FULL(全表掃描)

Oracle會讀取表中的所有行，并檢查是否滿足where語句中條件；

使用建議：數(shù)據(jù)量太大的表不建議全表掃描

2.4.2 TABLE ACCESS BY ROWID(通過ROWID的表存取)

ROWID的解釋：oracle會自動加在表的每一行的最后一列偽列，表中并不會物理存儲ROWID的值，一旦一行數(shù)據(jù)插入后，則其對應(yīng)的ROWID在該行的生命周期內(nèi)是唯一的，即使發(fā)生行遷移，該行的ROWID值也不變。

2.4.3 TABLE ACCESS BY INDEX SCAN(索引掃描)

在索引塊中即存儲每個索引的鍵值，也存儲具有該鍵值所對的ROWID.

索引的掃描分兩步：首先是找到索引所對的ROWID，其次通過ROWID讀取改行數(shù)據(jù)

索引掃描又分五種：

• INDEX UNIQUE SCAN（索引唯一掃描）
• INDEX RANGE SCAN（索引范圍掃描）
• INDEX FULL SCAN（索引全掃描）
• INDEX FAST FULL SCAN（索引快速掃描）
• INDEX SKIP SCAN（索引跳躍掃描）

(a).INDEX UNIQUE SCAN****（索引唯一掃描）：

針對唯一性索引（UNIQUE INDEX）的掃描，每次至多只返回一條記錄,主要針對該字段為主鍵或者唯一；

(b). INDEX RANGE SCAN****（索引范圍掃描）

使用一個索引存取多行數(shù)據(jù)；

發(fā)生索引范圍掃描的三種情況：

• 在唯一索引列上使用了范圍操作符（如：> < <> >= <= between）
• 在組合索引上，只使用部分列進行查詢（查詢時必須包含前導(dǎo)列，否則會走全表掃描）
• 對非唯一索引列上進行的任何查詢

(c). INDEX FULL SCAN****（索引全掃描）

• 進行全索引掃描時，查詢出的數(shù)據(jù)都必須從索引中可以直接得到

(d). INDEX FAST FULL SCAN****（索引快速掃描）

• 掃描索引中的所有的數(shù)據(jù)塊，與 INDEX FULL SCAN 類似，但是一個顯著的區(qū)別是它不對查詢出的數(shù)據(jù)進行排序（即數(shù)據(jù)不是以排序順序被返回）

(e). INDEX SKIP SCAN****（索引跳躍掃描）：

Oracle 9i后提供，有時候復(fù)合索引的前導(dǎo)列（索引包含的第一列）沒有在查詢語句中出現(xiàn)，oralce也會使用該復(fù)合索引，這時候就使用的INDEX SKIP SCAN;

當Oracle發(fā)現(xiàn)前導(dǎo)列的唯一值個數(shù)很少時，會將每個唯一值都作為常規(guī)掃描的入口，在此基礎(chǔ)上做一次查找，最后合并這些查詢；

例如：

假設(shè)表emp有ename（雇員名稱）、job（職位名）、sex（性別）三個字段，并且建立了如 create index idx_emp on emp (sex, ename, job) 的復(fù)合索引；

因為性別只有 '男' 和 '女' 兩個值，所以為了提高索引的利用率，Oracle可將這個復(fù)合索引拆成 ('男', ename, job)，('女', ename, job) 這兩個復(fù)合索引；

當查詢 select * from emp where job = 'Programmer' 時，該查詢發(fā)出后：

Oracle先進入sex為'男'的入口，這時候使用到了 ('男', ename, job) 這條復(fù)合索引，查找 job = 'Programmer' 的條目；

再進入sex為'女'的入口，這時候使用到了 ('女', ename, job) 這條復(fù)合索引，查找 job = 'Programmer' 的條目；

最后合并查詢到的來自兩個入口的結(jié)果集。

2.5 Sql語句的處理過程

image

1.在共享池中查找SQL語句

2.檢查語法

3.檢查語義和相關(guān)的權(quán)限

4.合并(MERGE)視圖定義和子查詢

5.確定執(zhí)行計劃

綁定****(BIND)****：

1.在語句中查找綁定變量

2.賦值(或重新賦值

執(zhí)行****(EXECUTE)****：

1.應(yīng)用執(zhí)行計劃

2.執(zhí)行必要的I/O和排序操作

提取****(FETCH)****：

1.從查詢結(jié)果中返回記錄

2.必要時進行排序

3.使用ARRAY FETCH機制

共享游標：好處

1.減少解析

2.動態(tài)內(nèi)存調(diào)整

3.提高內(nèi)存使用率

2.5.1 Sql共享原理

Oracle將執(zhí)行過程中的sql語句放在內(nèi)存的共享池中，可以被所有的數(shù)據(jù)庫用戶共享到，當執(zhí)行一條sql語句時，如果它和之前的sql執(zhí)行語句完全相同時，oracle會快速獲取被解析的語句以及最好的執(zhí)行路勁。

這塊系統(tǒng)屬于全局的區(qū)域，但是oracle只對簡單的表提供高速緩存，如果是多表的連接查詢，數(shù)據(jù)庫管理員必須在啟動參數(shù)文件中為該區(qū)域設(shè)置合適的參數(shù)，增加共享的可能性。

2.5.2 Sql共享的條件(注意事項)

1.執(zhí)行語句必須與共享池語句完全一樣，包括(大小寫，空格，換行等).

2.兩條語句所指的對象必須完全相同。

3.兩個SQL語句綁定變量的名字必須相同。

例子：字符級的比較

SELECT * FROM UR_USER_INFO

Select * from ur_user_info

例子：相同的綁定變量名

select pay_fee,pay_method from bal_payment_info where pay_sn= : pay_sn;

select pay_fee,pay_method from bal_payment_info where pay_sn= : pay_no;

綁定變量不一樣，不能共享。

2.5.3共享sql區(qū)域

image

2.5.4 Sql解析與共享sql語句

當一個Oracle實例接收一條sql后

1、Create a Cursor 創(chuàng)建游標

2、Parse the Statement 分析語句

3、Describe Results of a Query 描述查詢的結(jié)果集

4、Define Output of a Query 定義查詢的輸出數(shù)據(jù)

5、Bind Any Variables 綁定變量

6、Parallelize the Statement 并行執(zhí)行語句

7、Run the Statement 運行語句

8、Fetch Rows of a Query 取查詢出來的行

9、Close the Cursor 關(guān)閉游標

2.6 綁定變量

2.6.1 重編譯問題

例如：

select *from ur_user_info where contract_no = 32013484095139

下面這個語句每執(zhí)行一次就需要在SHARE POOL 硬解析一

次，一百萬用戶就是一百萬次，消耗CPU和內(nèi)存，如果業(yè)務(wù)

量大，很可能導(dǎo)致宕庫……

如果綁定變量，則只需要硬解析一次，重復(fù)調(diào)用即可

2.6.2 綁定變量解決重編譯問題

例如：

select *from ur_user_info where contract_no = 32013484095139

select *from ur_user_info where contract_no = 12013481213149

使用綁定變量

select *from ur_user_info where contract_no =：contract_no

2.6.3 綁定變量注意事項

a、不要使用數(shù)據(jù)庫級的變量綁定參數(shù)cursor_sharing來強

制綁定，無論其值為 force 還是similar

b、有些帶> < 的語句綁定變量后可能導(dǎo)致優(yōu)化器無法正確

使用索引

2.5 SQL優(yōu)化遵循的原則及注意事項

• 目標：

(1).SQL優(yōu)化的一般性原則設(shè)計方面：

• 設(shè)計方面：

(1).盡量依賴oracle的優(yōu)化器，并為其提供條件；

(2).合適的索引，索引的雙重效應(yīng)，列的選擇性；

• 編碼方面：

(1).利用索引，避免大表FULL TABLE SCAN；

(2).合理使用臨時表；

(3).避免寫過于復(fù)雜的sql，不一定非要一個sql解決問題；

(4).在不影響業(yè)務(wù)的前提下減小事務(wù)的粒度；

2.5.1 IS NULL 與IS NOT NULL

任何sql語句只要在where語句后面添加is null或者is not null，那么oracl優(yōu)化器將不再使用索引。

2.5.2 使用帶通配符（%）的語句

列舉兩個例子說明該問題：

查詢ur_user_info表中phone_no帶10的服務(wù)號碼

例子1：Select *from ur_user_info where phone_no like ‘%10%’；

例子2：Select *from ur_user_info where phone_no like ‘10%’；

由于例1中通配符（%）在搜尋詞首出現(xiàn)，所以oracle系統(tǒng)不使用phone_no的索引，通配符會降低查詢的效率，但當通配符不再首出現(xiàn)，又能使用索引，如例2所示。

|

三．ORACLE語句優(yōu)化規(guī)則

|

3.1 選擇最有效的表名順序

例如：TAB1 1000條記錄， TAB2 1條記錄

選擇記錄最少的作為基表

Select count(*) from tab1,tab2;

如果有3個或者3個以上的表則選擇交叉表作為基表

3.2 where字句中的連接順序

oracle的解析按照從上而下解析，因此表之間的連接必須寫在where條件之前：

例如：

低效率：

select .. from

         emp e

         where sal > 50000 and job = 'manager'

         and 25 < (select count(*) from emp where mgr=e.empno);

高效率：

select .. from

emp e

where 25 < (select count(*) from emp where mgr=e.empno)

         and sal > 50000

         and job = 'manager';

3.3 通配符’*’的使用

Sql在執(zhí)行帶通配符的語句時，如果‘%’在首位，那么在字段上建立的主鍵或者索引將會失效！

應(yīng)該避免類似語句的出現(xiàn)

Select name from user_info where name=’%A’;

3.4 使用truncate代替delete

當刪除表時，使用delete執(zhí)行操作，回滾端用來存放可恢復(fù)的信息，當沒有提交事務(wù)的時候，執(zhí)行回滾事務(wù)，數(shù)據(jù)會恢復(fù)到執(zhí)行delete操作之前，而當用truncate是，回滾端則不會存放可恢復(fù)的信息，減少資源的調(diào)用。

3.5 用where字句替換HAVING字句

避免使用 HAVING 子句, HAVING 只會在檢索出所有記錄之后才對結(jié)果集進行過濾. 這個處理需要排序,總計等操作. 如果能通過 WHERE 子句限制記錄的數(shù)目,那就能減少這方面的開銷.

3.6 減少對表的查詢

低效：

Select tab_name from tables where tab_name = ( select

tab_name from tab_columns where version = 604) and db_ver=

( select db_ver from tab_columns where version = 604)

高效：

select tab_name from tables where (tab_name,db_ver) =

( select tab_name,db_ver) from tab_columns where version =604)

3.7 用in代替or

低效****:

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

3.8 刪除重復(fù)數(shù)據(jù)

最高效的刪除重復(fù)記錄的方法

Delete from ur_user_info a

 Where a.rowid>(select min(b.rowid)

          From ur_user_info b

     Where b. uid=a. uid);

3.9 避免使用耗費資源的操作

帶有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL語句會啟動SQL引擎執(zhí)行耗費資源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要執(zhí)行兩次排序.

例如,一個UNION查詢,其中每個查詢都帶有GROUP BY子句, GROUP BY會觸發(fā)嵌入排序(NESTED SORT) ; 這樣, 每個查詢需要執(zhí)行一次排序, 然后在執(zhí)行UNION時, 又一個唯一排序(SORT UNIQUE)操作被執(zhí)行而且它只能在前面的嵌入排序結(jié)束后才能開始執(zhí)行. 嵌入的排序的深度會大大影響查詢的效率.

3.10 自動選擇索引

如果表中有兩個以上（包括兩個）索引，其中有一個唯一性索引，而其他是非唯一性．在這種情況下，ORACLE將使用唯一性索引而完全忽略非唯一性索引．

舉例:

select ename from emp where empno = 2326 and deptno = 20 ;這里，只有empno上的索引是唯一性的，所以empno索引將用來檢索記錄．

table access by rowid on emp index unique scan on emp_no_idx；

3.11 至少要包含組合索引的第一列

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

3.12 避免在索引列上使用函數(shù)

低效：

select ..

from dept

where sal * 12 > 25000;

高效****:

select ..

from dept

where sal > 25000/12;

3.13 避免出現(xiàn)索引列自動轉(zhuǎn)換

當比較不同數(shù)據(jù)類型的數(shù)據(jù)時, ORACLE自動對列進行簡單的類型轉(zhuǎn)換.

假設(shè)EMP_TYPE是一個字符類型的索引列.

select user_no,user_name,address

from user_files

where user_no = 109204421

這個語句被ORACLE轉(zhuǎn)換為:

select user_no,user_name,address

from user_files

where to_number(user_no) = 109204421因為內(nèi)部發(fā)生的類型轉(zhuǎn)換, 這個索引將不會被用到!

3.14 避免出現(xiàn)索引列自動轉(zhuǎn)換

如用 :

where a.order_no = b.order_no

不用 :

where to_number (substr(a.order_no, instr(b.order_no, '.') - 1)

= to_number (substr(a.order_no, instr(b.order_no, '.') - 1)

3.15 使用DECODE來減少處理時間

例如:

select count(*) sum(sal)

from emp

where dept_no = 0020

and ename like 'smith%';

select count(*) sum(sal)

from emp

where dept_no = 0030

and ename like 'smith%';

你可以用DECODE函數(shù)高效地得到相同結(jié)果

select count(decode(dept_no, 0020, 'x', null)) d0020_count,

   count(decode(dept_no, 0030, 'x', null)) d0030_count,

   sum(decode(dept_no, 0020, sal, null)) d0020_sal,

   sum(decode(dept_no, 0030, sal, null)) d0030_sal

from emp

where ename like 'smith%';

3.16 減少對表的查詢

低效

select tab_name

      from tables

      where tab_name = ( select tab_name

                            from tab_columns

                            where version = 604)

      and db_ver= ( select db_ver

                       from tab_columns

                       where version = 604)

高效

select tab_name

      from tables

      where  (tab_name,db_ver)

    = ( select tab_name,db_ver)

               from tab_columns

               where version = 604)   

3.17 Order by語句

(a).ORDER BY語句決定了Oracle如何將返回的查詢結(jié)果排序。Order by語句對要排序的列沒有什么特別的限制，也可以將函數(shù)加入列中（象聯(lián)接或者附加等）。任何在Order by語句的非索引項或者有計算表達式都將降低查詢速度。

(b). order by語句以找出非索引項或者表達式，它們會降低性能。解決這個問題的辦法就是重寫order by語句以使用索引，也可以為所使用的列建立另外一個索引，同時應(yīng)絕對避免在order by子句中使用表達式。

3.18 用索引提高效率

索引是表的一個概念部分,用來提高檢索數(shù)據(jù)的效率，ORACLE使用了一個復(fù)雜的自平衡B-tree結(jié)構(gòu). 通常,通過索引查詢數(shù)據(jù)比全表掃描要快. 當ORACLE找出執(zhí)行查詢和Update語句的最佳路徑時, ORACLE優(yōu)化器將使用索引. 同樣在聯(lián)結(jié)多個表時使用索引也可以提高效率. 另一個使用索引的好處是,它提供了主鍵(primary key)的唯一性驗證。通常, 在大型表中使用索引特別有效. 當然,你也會發(fā)現(xiàn), 在掃描小表時,使用索引同樣能提高效率. 雖然使用索引能得到查詢效率的提高,但是我們也必須注意到它的代價. 索引需要空間來存儲,也需要定期維護, 每當有記錄在表中增減或索引列被修改時, 索引本身也會被修改. 這意味著每條記錄的INSERT , DELETE , UPDATE將為此多付出4 , 5 次的磁盤I/O . 因為索引需要額外的存儲空間和處理,那些不必要的索引反而會使查詢反應(yīng)時間變慢.。定期的重構(gòu)索引是有必要的。

3.19 避免在索引列上使用計算

WHERE子句中，如果索引列是函數(shù)的一部分．優(yōu)化器將不使用索引而使用全表掃描．

低效：

 SELECT … FROM  DEPT  WHERE SAL * 12 > 25000;

高效:

 SELECT … FROM DEPT WHERE SAL > 25000/12;

3.20 用>= 替代 >

如果DEPTNO上有一個索引。

高效:

SELECT *

FROM EMP

WHERE DEPTNO >=4

低效:

SELECT *

FROM EMP

WHERE DEPTNO >3

3.21 通過使用>=、<=等，避免使用NOT命令

例子：

select * from employee where salary <> 3000;

對這個查詢，可以改寫為不使用NOT：

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

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

3.22 字符型字段的引號

比如有的表PHONE_NO字段是CHAR型,而且創(chuàng)建有索引，

但在WHERE條件中忘記了加引號，就不會用到索引。

WHERE PHONE_NO=‘13920202022’

WHERE PHONE_NO=13920202022

|

四．優(yōu)化總結(jié)

|

a.創(chuàng)建表的時候。應(yīng)盡量建立主鍵，盡量根據(jù)實際需要調(diào)整數(shù)據(jù)表的PCTFREE和PCTUSED參數(shù)；大數(shù)據(jù)表刪除，用truncate table代替delete。

b. 合理使用索引，在OLTP應(yīng)用中一張表的索引不要太多。數(shù)據(jù)重復(fù)量大的列不要建立二叉樹索引，可以采用位圖索引；組合索引的列順序盡量與查詢條件列順序保持一致；對于數(shù)據(jù)操作頻繁的表，索引需要定期重建，以減少失效的索引和碎片。

c.查詢盡量用確定的列名，少用*號。

select count(key)from tab where key> 0性能優(yōu)于select count(*)from tab；

d. 盡量少嵌套子查詢，這種查詢會消耗大量的CPU資源；對于有比較多or運算的查詢，建議分成多個查詢，用union all聯(lián)結(jié)起來；多表查詢的查詢語句中，選擇最有效率的表名順序。Oracle解析器對表解析從右到左，所以記錄少的表放在右邊。

e.盡量多用commit語句提交事務(wù)，可以及時釋放資源、解鎖、釋放日志空間、減少管理花費；在頻繁的、性能要求比較高的數(shù)據(jù)操作中，盡量避免遠程訪問，如數(shù)據(jù)庫鏈等，訪問頻繁的表可以常駐內(nèi)存：alter table．．．cache；

f.在Oracle中動態(tài)執(zhí)行SQL，盡量用execute方式，不用dbms_sql包。

參考文獻

《Oracle SQL 語句優(yōu)化》 2010 作者：Black_Snail

《基于Oracle的SQL優(yōu)化典型案例分析》2013作者：dbsnake @dbsnake