Oracle--SQL性能優化與提升策略

??前言：本博客僅作記錄學習使用，部分圖片出自網絡，如有侵犯您的權益，請聯系刪除

一、導致性能問題的內在原因

系統性能問題的底層原因主要有三個方面：

CPU占用率過高導致資源爭用和等待
內存使用率過高導致內存不足并需要使用磁盤虛擬內存
I/O占用率過高導致磁盤訪問需要等待。

性能影響的優先級從高到低依次是CPU-->內存-->I/O。在PL/SQL性能優化中，重點是減少I/O瓶頸，即盡量減少對磁盤I/O的訪問

根據上述分析，PL/SQL優化的核心思想可以總結為以下幾點：

避免使用過多復雜的SQL腳本，以減少系統的解析過程
避免進行無用的計算，例如避免出現死循環等低效代碼
避免浪費內存空間，例如避免執行不必要的SQL腳本，以免導致內存不足
充分利用內存中的計算和訪問速度快的優勢
盡可能減少磁盤的訪問數據量
盡可能減少磁盤的訪問次數，這是PL/SQL優化中的重要原則

二、如何進行SQL優化

1、選擇最有效率的表名順序

Oracle的解析器從右到左處理FROM子句中的表名，因此最后寫的表（基礎表 driving table）最先處理。在多表查詢時，建議將記錄最少的表作為基礎表，以減少連接操作的數據量。Oracle會通過排序和合并方式連接表：先掃描并排序基礎表，再掃描其他表并與基礎表匹配。這種處理順序對查詢性能至關重要，建議按照此規則編寫SQL語句。目前主要使用基于成本的優化器（CBO），它會自動評估最佳執行計劃，但遵循上述規則有助于提升SQL效率。

?--例如:員工表emp有16384條記錄,而部門表dept有1條記錄,選擇dept作為基礎表  select count(*) from emp,dept;      --選擇dept作為基礎表耗時0.96sselect count(*) from dept,emp;      --選擇emp作為基礎表耗時26.09s

2、WHERE子句中的連接順序

ORACLE 采用自下而上的順序解析 WHERE 子句,根據這個原理，表之間的連接必須寫在其他 WHERE 條件之前

?--低效:select dept.deptno,emp.jobfrom emp.deptwhere emp.job='MANAGER' AND emp.deptno=dept.deptno;?--優化后:select dept.deptno,emp.jobfrom emp.deptwhere emp.deptno=dept.deptno AND emp.job='MANAGER';

3、SELECT子句中避免使用'*'

在SELECT子句中使用動態SQL列引用“*”雖然方便，但效率低下。Oracle解析時會通過查詢數據字典將“”轉換為所有列名，這增加了額外的開銷和時間成本。因此，建議在SELECT子句中盡量避免使用“”，而是明確列出所需的列名，以提高查詢效率。

4、用EXITS 替代 IN

使用EXISTS替換IN效果有時不明顯，但在基礎表查詢需聯接另一表時，EXISTS通常提高效率，因EXISTS找到匹配即停止搜索

?--低效select *from table_name1where column1 in(select column1 from table_name2where column2=str_column2and column3='xxxx');?--優化后:select *from table_name1where exists(select 1 from table_name2where column1=table_name2.column1and column2=str_column2and column3='xxxx');

5、用 NOT EXISTS 替代 NOT IN

Oracle 10g前 NOT IN 效率低，10g 雖改進但仍存在問題。建議用 NOT EXISTS 替代 NOT IN ，因 NOT IN 對子查詢表全表遍歷且需內部排序合并，效率低。改寫為 NOT EXISTS 可提升效率

?--低效:select * from table_name1where column1 NOT IN(select column1 from table_name2where column3='xxxx');?--優化后:select *from table_name1where not exists(select 1 from table_name2where column1=table_name2.column1and column3='xxxx');

6、用表連接替換 EXISTS

在子查詢的表和主表查詢是多對一的情況，一般采用表連接的方式比 EXISTS 更有效率

?--低效:select table name1.*from table name1where exists (select 1 from table_name2where column1 =table name1.column1and column2='xxxx'and column3='xxxxxx');--優化后:SELECT table_name1.*FROM table namel,table name2 Wheretable_name1.column1=table_name2.column1and table_name2.column2='xxxx'and column3='xxxx';

7、減少對表的查詢

該問題是我們編程中出現過的問題，請大家一定注意，并且該類問題優化可以帶來較大性能的提升

?--低效:cursor cur_table_lj1 isselect column1from table1where column1 = str_column1 and column2='1111';cursor cur_table1_lj2 isselect column1from table1where column1 =str_column1 and column2='2222';?for rec_lj1 in cur_table1 loop業務邏輯1處理end loop;for rec_lj2 in cur_table2 loop業務邏輯2處理end loop;?--優化后:cursor cur_tablel_lj1 isselect column1,column2from table1where column1 =str_columnl and column2 in ('11111','22222');?for rec_ljl in cur_tablel lj1 loopif rec_lj1.column2='11111' then業務邏輯1處理.…..end if;if rec lj1.column2='22222' then業務邏輯2處理....end if,end loop;

高效的做法使用同樣的條件(或者說是索引)只訪問一次磁盤，低效的做法訪問了2次磁盤，這樣速度差別將近2倍。

8、避免循環（游標）里面嵌查詢

游標中不能有游標或update、delete等語句，只能有select語句，但在實際編程中難以完全避免，需盡量減少。優化方法是將游標循環中的查詢語句提前到游標查詢中一次性查詢出來，減少磁盤訪問次數，提升效率。如果無法避免游標中使用查詢語句，要確保查詢語句使用索引，提高查詢速度。

9、盡量用 union all 替換 all

Union 會去掉重復的記錄，會有排序的動作，會浪費時間。因此在沒有重復記錄的情況下或可以允許有重,復記錄的話，要盡量采用 uoion all 來關聯

10、group by 優化

Group by需要查詢后分組，速度慢影響性能，如果查詢數據量大，并且分組復雜，這樣的査詢語句在性能上是有問題的。采用 group by的也一定要進行優化

?--低效:select table1.column1,table2.column2table2.column3,sum(column5),table1.column4from table1,table2where table1.column1=table2.column1and table1.column4='xxxxxx'group py table1.column1,table2.column2table2.column3,table2.column4?--優化后:select table1.column1,table2.column2,table2.column3,gzze,table1.column4from(select column1,sum(column5) gzzefrom table1 group by column1) table1,table2where table1.column1=table2.column1and column4='xxxx';

11、盡量避免用 order by

使用 ORDER BY 會因查詢后排序而拖慢速度，尤其數據量大時。盡管有時無法避免使用 ORDER BY ，但需注意排序列表應符合索引，這樣能顯著提升速度。

12、用where 子句替換Having 子句

避免使用HAVING子句，因為它會在檢索完所有記錄后才對結果集進行過濾，這個過程需要排序、總計等操作。如果能通過WHERE子句限制記錄數量，就能減少這方面的開銷。

?--低效:select column1,count(1) from table1group by column1having column1 in ('1','2');--優化后:select column1,count(1) from table1where column1 in ('1','2')group by column1;

HAVING 中的條件一般用于對一些集合函數的比較,如 COUNT() 等等。除此而外，一般的條件應該寫在 WHERE 子句中

13、使用表的別名（alias）

在SQL語句中連接多個表時，使用表的別名并將其前綴于每個列名，可減少解析時間及因列名歧義引發的語法錯誤。

14、COMMIT 使用

提交頻率過高會浪費時間，盡管單次提交時間短。
提交可釋放資源，在大量數據更新時需及時提交。

?--cur_table1 有5000萬數據n_count :=0For arec in cur_table1 loopInsert into table ...n_count := n_count + 1;If n_count = = 100000 then      --10萬一提交commit;n_count := 0End if;End loop;Commit;

15、減少多表關聯

表關聯越多，查詢速度越慢，建議表關聯不超過3個（子查詢也算表關聯）。
大數據量表關聯會影響索引效率，可采用建立臨時表的方法來提高速度。

三、索引使用優化

1、避免在索引列上使用函數或運算

在實際編程中要注意：在索引列上使用函數或運算，查詢條件不會使用索引。

?--不使用索引select * from table1where column1='xxx'and to_char(column2,'yyyymm')='200801';或者select * from table1where column1='xxx'and column2+1=sysdate;?--使用索引select * from table1where column1='xxx'and column2=to_date('200801','yyyymm');或者select * from table1where column1='xxx'and column2=sysdate -1;

2、避免改變索引列的類型

索引列的條件如果類型不匹配，則不能使用索引。

3、避免在索引列上使用NOT

避免在索引列上使用 NOT ， NOT 不會使查詢條件使用索引。對于 != 這樣的判斷也不能使用索引，因為索引只能告訴你什么存在于表中，而不能告訴你什么不存在于表中。

?--低效:select * from table1 where not column1='10';?--優化后:select * from table1 where column1 in ('20','30');

4、用>=替代>

雖然效果不是特別明顯，但建議采用這種方式

?--低效:select * from table1 where column1 > '10';?--優化后:select * from table1 where column >= '20';

特別說明:兩者的區別在于,前者 DBMS 首先定位到 column1=10的記錄并且向前掃描到第一個column1 大于 10 的記錄,而后者 DBMS 將直接跳到第一個 column1 等于 20 的記錄

5、避免在索引列上使用 IS NULL和IS NOT NULL

在Oracle中，索引列使用 IS NULL 或 IS NOT NULL 時不會利用索引，因為空值不存儲于索引列。這會導致Oracle停用索引

?--低效:select * from table1 where column1 is not null;?--優化后:select * from table1 where column1 in ('10','20','30');

6、帶通配符（%）的like語句

%在常量前面索引就不會使用。

?--不使用索引:select * from table1 where column1 like '%210104';select * from table1 where column1 like '%210104%';?--使用索引:select * from table1 where column1 like '210104%';

7、總是使用索引的第一個列

如果索引是建立在多個列上,只有在它的第一個列被 where 子句引用時,優化器才會選擇使用該索引。

?--如:table1的復合索引(column1,column2,column3)--低效(不會使用索引):select * from table1 where column2='110' and column3='200801';?--優化后(會使用索引):select * from table1 where column1 = '10001000';

如果不使用索引第一列基本上不會使用索引，使用索引要按照索引的順序使用，另外使用復合索引的列越多，查詢的速度就越快