sql查詢優(yōu)化的幾種方法

1、ORACLE 的解析器按照從右到左的順序處理 FROM 子句中的表名，因此 FROM 子句中寫在最后的表(基礎表 driving table)將被最先處理。在FROM 子句中包含多個表的情況下，你必須選擇記錄條數最少的表作為基礎表。 
例如： 
表ceshi_xiao有969條記錄，emp_xiao有14條記錄。 
select count(*) from emp_xiao, ceshi_xiao;（低效方法） 
select count(*) from ceshi_xiao, emp_xiao;（高效方法） 
 
注意：這里由于ceshi_xiao表記錄太少差別不明顯，但已經能看出差別。當表記錄上百萬條時，該差距會無限放大。 

2、ORACLE 采用自下而上的順序解析 WHERE 子句。 
根據這個原理，表之間的連接必須寫在其他 WHERE 條件之前， 那些可以過濾掉最大數量記錄的條件必須寫在 WHERE 子句的末尾。 
例如： 
SELECT … 
FROM EMP E 
WHERE SAL > 50000 
AND JOB = ‘MANAGER' 
AND 25 < (SELECT COUNT(*) FROM EMP 
WHERE MGR=E.EMPNO); (低效，執(zhí)行時間 156.3秒) 
SELECT … 
FROM EMP E 
WHERE 25 < (SELECT COUNT(*) FROM EMP 
WHERE MGR=E.EMPNO) 
AND SAL > 50000 
AND JOB = ‘MANAGER';(高效,執(zhí)行時間 10.6秒) 
注意：在進行多表關聯時，多用 Where 語句把單個表的結果集最小化，多用聚合函數匯總結果集后再與其它表做關聯，以使結果集數據量最小化。 

3、減少對表的查詢。 
在含有子查詢的 SQL語句中，要特別注意減少對表的查詢。 

4、用EXISTS替代IN。 
在許多基于基礎表的查詢中，為了滿足一個條件，往往需要對另一個表進行聯接。在這 種情況下， 使用 EXISTS(或 NOT EXISTS)通常將提高查詢的效率。使用 exists 而不用 IN 因為 Exists 只檢查行的存在，而 in 檢查實際值。 
例如： 
SELECT * 
FROM EMP (基礎表) 
WHERE EMPNO > 0 
AND DEPTNO IN (SELECT DEPTNO 
FROM DEPT 
WHERE LOC = ‘MELB')（低效） 
SELECT * 
FROM EMP (基礎表) 
WHERE EMPNO > 0 
AND EXISTS (SELECT ‘X' 
FROM DEPT 
WHERE DEPT.DEPTNO = EMP.DEPTNO 
AND LOC = ‘MELB') （高效） 
用 IN 的 SQL 性能總是比較低，原因是：對于用 IN 的 SQL 語句 ORACLE 總是試圖將其轉換成多個表的連接，如果轉換不成功則先執(zhí)行 IN里面的子查詢，再查詢外層的表記錄如果轉換成功就轉換成多個表的連接。因此 不管理怎么，用 IN 的 SQL 語句總是多了 一個轉換的過程。因此在業(yè)務密集的SQL當中盡量不采用IN操作符。 

5、用EXISTS替換DISTINCT。 
當提交一個包含一對多表信息(比如部門表和雇員表)的查詢時，避免在SELECT 子句 中使用 DISTINCT. 一般可以考慮用 EXIST 替換。 
例如：SELECT DISTINCT DEPT_NO,DEPT_N 
FROM DEPT D,EMP E 
WHERE D.DEPT_NO = E.DEPT_NO（低效） 
SELECT DEPT_NO,DEPT_NAME 
FROM DEPT D 
WHERE EXISTS ( SELECT ‘X' 
FROM EMP E 
WHERE E.DEPT_NO = D.DEPT_NO);（高效） 

6、用表連接替換EXISTS。 
通常來說 ，采用表連接的方式比 EXISTS 更有效率。 
例如： 
SELECT ENAME 
FROM EMP E 
WHERE EXISTS (SELECT ‘X' 
FROM DEPT 
WHERE DEPT_NO = E.DEPT_NO 
AND DEPT_CAT = ‘A'); 
為了提高效率。改寫為： 
SELECT ENAME 
FROM DEPT D,EMP E 
WHERE E.DEPT_NO = D.DEPT_NO 
AND DEPT_CAT = ‘A' ; 

7、避免在索引列上使用計算。 
WHERE 子句中，如果索引列是函數的一部分。優(yōu)化器將不使用索引而使用全表掃描。這是一個非常實用的規(guī)則，請務必牢記。 
例如： 
SELECT … 
FROM DEPT 
WHERE SAL * 12 > 25000; （低效） 
SELECT … 
FROM DEPT 
WHERE SAL > 25000/12; （高效） 

8、避免在索引列上使用NOT。 
通常，我們要避免在索引列上使用 NOT，NOT 會產生在和在索引列上使用函數相同 的影響。當ORACLE“遇到”NOT，他就會停止使用索引轉而執(zhí)行全表掃描。 

9、不使用<>、!=、~=、^=操作符。 
不等于操作符是永遠不會用到索引的，因此對它的處理只會產生全表掃描。 
a <> 0 ==> a > 0 or a < 0 

10、用>=替代>。 
SELECT * 
FROM EMP 
WHERE DEPTNO >3（低效） 
SELECT * 
FROM EMP 
WHERE DEPTNO >=4（高效） 
兩者的區(qū)別在于， 前者 DBMS將直接跳到第一個 DEPT 等于 4的記錄而后者將首先定位到 DEPTNO=3的記錄并且向前掃描到第一個 DEPT 大于 3的記錄。 

11、不使用like 操作符。 
遇到 需要用到 LIKE 過濾的SQL語句，完全可以用 instr 代替，處理速度將顯著提高。 

12、用(UNION)UNION ALL替換OR (適用于索引列)。 
通常情況下， 用 UNION替換 WHERE 子句中的 OR將會起到較好的效果。對索引列使用 OR將造成全表掃描。注意， 以上規(guī)則只針對多個索引列有效。 如果有 column沒有被索引， 查詢效率可能會因為你沒有選擇 OR而降低。 
如果你堅持要用 OR， 那就需要返回記錄最少的索引列寫在最前面。注意, 以上規(guī)則只針對多個索引列有效. 如果有column沒有被索引, 查詢效率可能會因為你沒有選擇OR而降低。 

13、優(yōu)化GROUP BY。 
提高 GROUP BY 語句的效率， 可以通過將不需要的記錄在 GROUP BY 之前過濾掉。下面兩個查詢返回相同結果但第二個明顯就快了許多。 
例如： 
SELECT JOB ， AVG(SAL) 
FROM EMP 
GROUP by JOB 
HAVING JOB = ‘PRESIDENT' 
OR JOB = ‘MANAGER'（低效） 
SELECT JOB ， AVG(SAL) 
FROM EMP 
WHERE JOB = ‘PRESIDENT' 
OR JOB = ‘MANAGER'GROUP by JOB（高效） 
使用 where 而不是 having ，where是用于過濾行的，而having是用來過濾組的，因為行被分組后，having 才能過濾組，所以盡量用 WHERE 過濾。 

14、避免改變索引列的類型。 
當比較不同數據類型的數據時, ORACLE自動對列進行簡單的類型轉換。 

15、SQL書寫的影響。 
同一功能同一性能不同寫法SQL的影響。 
例如： 
如一個SQL在A程序員寫的為select * from zl_yhjbqk 
B程序員寫的為select * from dlyx.zl_yhjbqk（帶表所有者的前綴） 
C程序員寫的為select * from DLYX.ZLYHJBQK（大寫表名） 
D程序員寫的為select *  from DLYX.ZLYHJBQK（中間多了空格） 
四個SQL在ORACLE分析整理之后產生的結果及執(zhí)行的時間是一樣的，但是從ORACLE共享內存SGA的原理，可以得出ORACLE對每個SQL都會對其進行一次分析，并且占用共享內存，如果將SQL的字符串及格式寫得完全相同則ORACLE只會分析一次，共享內存也只會留下一次的分析結果，這不僅可以減少分析SQL的時間，而且可以減少共享內存重復的信息，ORACLE也可以準確統計SQL的執(zhí)行頻率。 

總結： 
1).應盡量避免在 where 子句中對字段進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描。 
2).應盡量避免在 where 子句中使用!=或<>操作符，否則將引擎放棄使用索引而進行全表掃描。 
3).應盡量避免在 where 子句中使用 or 來連接條件，否則將導致引擎放棄使用索引而進行全表掃描。 
4).in 和 not in 也要慎用，否則會導致全表掃描。 
5).在使用索引字段作為條件時，如果該索引是復合索引，那么必須使用到該索引中的第一個字段作為條件時才能保證系統使用該索引，否則該索引將不會被使用，并且應盡可能的讓字段順序與索引順序相一致。 
6).任何地方都不要使用 select * from t ，用具體的字段列表代替“*”，不要返回用不到的任何字段。