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

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

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

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

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

5、用EXISTS替換DISTINCT。 
當(dāng)提交一個(gè)包含一對(duì)多表信息(比如部門表和雇員表)的查詢時(shí)，避免在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、避免在索引列上使用計(jì)算。 
WHERE 子句中，如果索引列是函數(shù)的一部分。優(yōu)化器將不使用索引而使用全表掃描。這是一個(gè)非常實(shí)用的規(guī)則，請(qǐng)務(wù)必牢記。 
例如： 
SELECT … 
FROM DEPT 
WHERE SAL * 12 > 25000; （低效） 
SELECT … 
FROM DEPT 
WHERE SAL > 25000/12; （高效） 

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

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

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

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

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

13、優(yōu)化GROUP BY。 
提高 GROUP BY 語句的效率， 可以通過將不需要的記錄在 GROUP BY 之前過濾掉。下面兩個(gè)查詢返回相同結(jié)果但第二個(gè)明顯就快了許多。 
例如： 
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是用來過濾組的，因?yàn)樾斜环纸M后，having 才能過濾組，所以盡量用 WHERE 過濾。 

14、避免改變索引列的類型。 
當(dāng)比較不同數(shù)據(jù)類型的數(shù)據(jù)時(shí), ORACLE自動(dòng)對(duì)列進(jìn)行簡(jiǎn)單的類型轉(zhuǎn)換。 

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

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