1、SQL的編寫技巧16 / 16文檔可自由編輯打印目 錄1.SQL語句要統一成大寫32.在進行多個表連接時，FROM中的表的順序要按照記錄數由多到少的順序來排列(Oracle)33.可以過濾掉最大數量記錄的條件必須寫在WHERE子句的末尾。（Oracle）34.涉及到多表檢索時，明確地為每個字段指定表名35.對于經常使用的SQL語句（循環處理中使用的SQL等），可以通過預編譯、綁定變量來提高性能46.對于索引列不要執行NULL值的檢索47.對于索引列，不要使用"NOT"、"!="、"<>"比較運算48.對于索引列不要使用函數

2、和計算式49.盡可能將操作移至等號右邊510.對于多鍵值索引，要按照索引的定義順序來使用511.不要通過LIKE運算來執行中間一致或后方一致的檢索512.去掉沒有意義的GROUP BY、ORDER BY子語513.WHERE語句中不要使用NOT IN或者HAVING614.盡量避免較多地使用子查詢615.避免不同類型的查詢條件616.有使用IN或者EXISTS的語句嗎？617.如果DBMS能夠產生執行計劃，驗證一下是否是最優的SQL？618.避免全表掃描的查詢方式719.當有多個索引可供選擇時，使用的是DB設計者所希望的索引嗎720.調整SQL后執行代價變得比原來更低了嗎？721.在循環處理中

3、，是否存在執行大量SQL語句的情形722.SELECT子句中避免使用*（Oracle）71. SQL語句要統一成大寫原因：SQL語句轉換成大寫，可縮短些SQL的解析時間。通過統一成大寫，可提高SQL的再利用率，縮短SQL解析時間。×select * from a_tableSELECT * FROM A_TABLE2. 在進行多個表連接時，FROM中的表的順序要按照記錄數由多到少的順序來排列(Oracle)原因：ORACLE的解析器按照從右到左的順序處理FROM子句中的表名×3. 可以過濾掉最大數量記錄的條件必須寫在WHERE子句的末尾。（Oracle）原因：ORACLE采

4、用自下而上的順序解析WHERE子句,根據這個原理,表之間的連接必須寫在其他WHERE條件之前, 那些可以過濾掉最大數量記錄的條件必須寫在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 J

5、OB = MANAGER;4. 涉及到多表檢索時，明確地為每個字段指定表名原因：通過在A_TABLE、B_TABLE中指定別名A、B,就可不再需要調查A_ITEM、B_ITEM、A_KEY、B_KEY是哪兒個表中的項目,從而縮短SQL解析時間。（為方便編碼可以為表名指定名）×SELECT A_ITEM FROM A_TABLE, B_TABLEWHERE A_KEY = B_KEY;SELECT A.A_ITEM, B.B_ITEM FROM A_TABLE A, B_TABLE BWHERE A.A_KEY = B.B_KEY;5. 對于經常使用的SQL語句（循環處理中使用的SQL

6、等），可以通過預編譯、綁定變量來提高性能原因：由于SQL可以被再利用，所以可縮短SQL解析時間。×SELECT COUNT(*) FROM A_TABLE A WHERE A.KEY = '0'SELECT COUNT(*) FROM A_TABLE A WHERE A.KEY = '1':VAR = '0'SELECT COUNT(*) FROM A_TABLE A WHERE A.KEY = :VAR;:VAR = '1'SELECT COUNT(*) FROM A_TABLE A WHERE A.KEY = :VA

7、R;6. 對于索引列不要執行NULL值的檢索原因：NULL檢索是指查找沒有的東西，所以如果不全部都調查的話無法判斷出是有還是沒有。索引只做成有的數據。調整成不執行NULL檢索的SQL，或者修改表的定義×SELECT * FROM A_TABLE AWHERE A.KEY IS NULL;7. 對于索引列，不要使用"NOT"、"!="、"<>"比較運算原因：同5×SELECT * FROM A_TABLE AWHERE A.KEY != 1;SELECT * FROM A_TABLE AWHERE A.K

8、EY < 1 OR A.KEY > 1;×SELECT * FROM A_TABLE AWHERE NOT EXIST ( SELECT * FROM B_TABLE B WHERE B.KEY = A.KEY);8. 對于索引列不要使用函數和計算式原因：索引將不能發揮索引的作用。×SELECT * FROM A_TABLE A WHERE TO_CHAR(A.KEY, 'YYYYMMDD') = '20030101'SELECT * FROM A_TABLE AWHERE A.KEY = TO_DATE('2003010

9、1','YYYYMMDD');SELECT * FROM A_TABLE AWHERE A.KEY BETWEEN TO_DATE('200', 'YYYYMMDDHH24MISS') AND TO_DATE('259', 'YYYYMMDDHH24MISS');9. 盡可能將操作移至等號右邊原因：任何對列的操作都將導致表掃描，它包括數據庫函數、計算表達式等等，查詢時要盡可能將操作移至等號右邊。×10. 對于多鍵值索引，要按照索引的定義順序來使用原因：如果索引是建立在多個列上,只有在它的第一個列(

10、leading column)被where子句引用時,優化器才會選擇使用該索引。提示：需要修改索引的結合順序或者重新設定復合索引。可能會對其他業務有影響，所以需要經過充分討論后再作決定。 新追加的索引對更新/檢索方面的性能都可能會有極大影響，更需要十分慎重。×在按照A.KEY1 + A.KEY2 + A.KEY3來定義索引的情況下SELECT * FROM A_TABLE AWHERE A.KEY2 = 'KEY2' AND A.KEY3 = 'KEY3'SELECT * FROM A_TABLE AWHERE A.KEY1 = 'KEY1&#

11、39; AND A.KEY2 = 'KEY2'11. 不要通過LIKE運算來執行中間一致或后方一致的檢索原因：中間或后方一致性檢索時，索引是無效的。提示：重新分析一下看是否真的需要中間一致或后方一致檢索，如果真的需要的話，可能需要重新規劃一下業務流程。×SELECT * FROM A_TABLE AWHERE A.KEY LIKE '%XYZ'×SELECT * FROM A_TABLE AWHERE A.KEY LIKE '%EFG%'SELECT * FROM A_TABLE AWHERE A.KEY LIKE '

12、ABC%'12. 去掉沒有意義的GROUP BY、ORDER BY子語原因：對于數據庫來說ORDER BY、GROUP BY執行起來最耗費資源的處理。提示：使用ORDER BY時，需要確認真的需要進行排序處理嗎？×SELECT COUNT(*), A.ITEM FROM A_TABLE AWHERE A.ITEM = 'ABC'GROUP BY A.ITEM;# A.ITEM只抽取1條數據，所以GROUP BY結果也是1行，沒有意義SELECT COUNT(*) FROM A_TABLE AWHERE A.ITEM = 'ABC'13. WHE

13、RE語句中不要使用NOT IN或者HAVING原因：提示：考慮使用NOT EXISTS×14. 盡量避免較多地使用子查詢原因：提示：可用連接實現的場合，就盡量避免使用子查詢。×15. 避免不同類型的查詢條件原因：默認的類型轉會導致索引變成無效。×SELECT * FROM A_TABLE A WHERE A.NUMBER_COL = '123'SELECT * FROM A_TABLE A WHERE A.NUMBER_COL = 123;16. 有使用IN或者EXISTS的語句嗎？原因：提示：使用本身是沒有問題的，但是需要確認。如果這樣的SQL耗

14、時的話，能否用EXISTS代替IN，或者用IN代替EXISTS，通過改寫或許可以使SQL變快，視具體情況來定×17. 如果DBMS能夠產生執行計劃，驗證一下是否是最優的SQL？原因：提示：檢查/調整的方法：在返回相同結果的SQL中，I/O代價小的才是好的SQL；同一SQL的首次執行和以后的執行，因為緩沖的原因會有差異，所以在同一條件下的比較很重要。×18. 避免全表掃描的查詢方式原因：如果樣本很少的話（幾件或幾十件，一個DISK I/O就可以取得的那種程度），應該是沒有問題的。但是一般來說要嚴禁整表掃描的。提示：仔細確認一下：SQL語句的寫法沒有問題嗎？是否忘記了使用索引？

15、索引的使用方法沒有問題嗎？×19. 當有多個索引可供選擇時，使用的是DB設計者所希望的索引嗎原因：使用能最大限度（效率高）地提取到數據的索引。一般來說，能夠抽取原始數據的1/50以內的索引，才是效率高的。如果不具備這樣的條件的話，或許全表掃描也許會很快。×20. 調整SQL后執行代價變得比原來更低了嗎？原因：對于執行代價的絕對值是沒有意義；變更前后的差才是最重要的。×提示：能否考慮循環處理之外的方法，比如是否能通過批處理來實現相同的功能。21. 在循環處理中，是否存在執行大量SQL語句的情形原因：即使單個SQL語句的處理時間短，但這個SQL語句因循環處理而被發行了

16、幾萬次，幾十萬次的話 循環處理中的SQL語句，通常要注意循環次數（即使不準確但能夠大致估算出來也是重要的）。×22. SELECT子句中避免使用*（Oracle）原因：ORACLE在解析的過程中, 會將* 依次轉換成所有的列名, 這個工作是通過查詢數據字典完成的, 這意味著將耗費更多的時間。×SQL使用技巧23. 高效的sql語句Sql優化方法RBO(rule-based optimizer)CBO驅動表1）2張行數不一致的表連接 表TAB1行數：16,384行 表TAB2行數：1行×SELECT COUNT(*) FROM TAB2, TAB1;SELECT C

17、OUNT(*) FROM TAB1, TAB2;2）3張表連接 ×SELECT * FROM EMP E, LOC L, CAT C WHERE E.emp_noBETWEEN 1000 AND 2000 ANt_no= C.cat_no AND E.locn= L.locn;SELECT * FROM LOC L, CAT C, EMP E WHERE E.emp_noBETWEEN 1000 AND 2000 ANt_no= C.cat_no AND E.locn= L.locn;Where語句順序的效率1) 使用索引引起的where語句效率使用AND語句時行數多的放在前面

18、15;SELECT * FROM emp E WHERE emp_sal > 50000 AND emp_type = 'MANAGER' AND 25 < (SELECT COUNT(*) FROM EMP WHERE emp_mgr = E.emp_no);SELECT * FROM emp E WHERE 25 < (SELECT COUNT(*) FROM EMP WHERE emp_mgr = E.emp_no) AND emp_sal > 50000 AND emp_type = 'MANAGER'使用OR語句時，行數多的放在

19、后面×SELECT * FROM emp E WHERE 25 < (SELECT COUNT(*) FROM EMP WHERE emp_mgr = E.emp_no) OR (emp_sal > 50000 AND emp_type = 'MANAGER');SELECT * FROM emp E WHERE (emp_sal > 50000 AND emp_type = 'MANAGER') OR 25 < (SELECT COUNT(*) FROM EMP WHERE emp_mgr = E.emp_no);2）ROWI

20、D的使用 使用ROWID的WHERE語句效率最高。 SELECT ROWID, . INTO :emp_rowid, . FROM emp WHERE emp.emp_no = 56722 FOR UPDATE;UPDATE emp SE = . , WHERE ROWID = :emp_rowid; 3）減少訪問次數×SELECT emp_name, sal, grade FROM emp WHERE emp_no = 0342;SELECT emp_name, sal, grade FROM emp WHERE emp_no = 0291;SELECT A.emp_name, A

21、.sal, A.grade, B.emp_name, B.sal, B.grade FROM emp A, emp B WHERE A.emp_no = 0342 AND B.emp_no = 0291;4）Where語句的索引的使用 （1）SUBSTR×SELECT acc_name, trans_date, amount FROM transaction WHERE SUBSTR(account_name,1,7) = 'CAPITAL' SELECT acc_name, trans_date, amount FROM transaction WHERE acco

22、unt_name LIKE 'CAPITAL%' （2）！= ×SELECT acc_name, trans_date, amount FROM transaction WHERE amount != 0; SELECT acc_name, trans_date, amount FROM transaction WHERE amount > 0; （3）|×SELECT acc_name, trans_date, amount FROM transaction WHERE acc_name | acc_type = 'AMEXA' SE

23、LECT acc_name, trans_date, amount FROM transaction WHERE acc_name = 'AMEX' AND acc_type = 'A'（4）運算×SELECT acc_name, trans_date, amount FROM transaction WHERE amount + 3000 < 5000; SELECT acc_name, trans_date, amount FROM transaction WHERE amount < 5000 + 3000;SQL命令的使用秘訣DEC

24、ODE的使用×SELECT COUNT(*), SUM(salary) FROM emp WHERE dept_no = 0020 AND emp_name LIKE 'SMITH%'SELECT COUNT(*), SUM(salary) FROM emp WHERE dept_no = 0030 AND emp_name LIKE 'SMITH%'SELECT COUNT（DECODE(dept_no, 0020, 'X', NULL)D0020_count, COUNT (DECODE(dept_no, 0030, 'X&

25、#39;, NULL)D0030_count, SUM (DECODE(dept, 0020, salary, NULL) D0020_sal, SUM (DECODE(dept, 0030, salary, NULL) D0030_sal FROM emp WHERE emp_name LIKE 'SMITH%'表的別名的使用× SELECT E.emp_no, name, tax_no, C.comp_code, comp_name FROM company C, emp E WHERp_code = C.comp_code; SELECT E.emp_no, E

26、.name, E.tax_no, C.comp_code, C.comp_name FROM company C, emp E WHERp_code = C.comp_code;去掉重復行DELETE FROM emp E WHERE E.rowid > ( SELECT MIN(X.rowid) FROM emp X WHERE X.emp_no = E.emp_no );表的行計數SELECT COUNT(有索引的列) FROM TRANS;SELECT COUNT(*) FROM TRANS;SELECT COUNT(1) FROM TRANS;用WHERE語句替換HAVING語句

27、的使用×SELECT region, AVG(loc_size) FROM location GROUP BY region HAVING region != 'SYDNEY' AND region != 'PERTH'SELECT region, AVG(loc_size) FROM location WHERE region != 'SYDNEY' AND region != 'PERTH' GROUP BY region使用表連接替代EXISTS使用×SELECT emp_name FROM emp WH

28、ERE (emp_cat, sal_range) = (SELECT MAX(category), MAX(sal_range) FROM emp_categories) AND emp_dept = 0020;SELECT emp_name FROM emp WHERE emp_cat = (SELECT MAX(category) FROM emp_categories) AND sal_range = (SELECT MAX(sal_range) FROM emp_categories) AND emp_dept = 0020;使用EXISTS替代表連接SELECT . FROM emp

29、 E WHERE EXISTS ( SELECT 'X' FROM deptWHERE dept_no = E.dept_no AND dept_cat = 'A') AND E.emp_type = 'MANAGER'使用EXISTS代替DISTINCT語句×SELECT DISTINCT dept_no, dept_name 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);使用NOT EXIS