一、sql書寫規範:
1、sql語句的所有表名、欄位名全部小寫,系統保留字、內置函數名、sql保留字大寫。
2、連接子or、in、and、以及=、<=、>=等前後加上一個空格。
3、對較為複雜的sql語句加上注釋,說明演算法、功能。
注釋風格:注釋單獨成行、放在語句前面。
(1) 應對不易理解的分支條件運算式加注釋;
(2) 對重要的計算應說明其功能;
(3) 過長的函數實現,應將其語句按實現的功能分段加以概括性說明;
(4) 每條SQL語句均應有注釋說明(表名、欄位名)。
(5) 常量及變數注釋時,應注釋被保存值的含義(必須),合法取值的範圍(可選)
(6) 可採用單行/多行注釋。(-- 或 /* */ 方式)
4、SQL語句的縮進風格
(1) 一行有多列,超過80個字元時,基於列對齊原則,採用下行縮進
(2) where子句書寫時,每個條件占一行,語句令起一行時,以保留字或者連接子開始,連接子右對齊。
5、多表連接時,使用表的別名來引用列。
6、供別的檔或函式呼叫的函數,絕不應使用全域變數交換資料;
如例(1)
二、書寫優化性能建議
1、避免嵌套連接。例如:A = B and B = C and C = D
2、where條件中儘量減少使用常量比較,改用主機變數
3、系統可能選擇基於規則的優化器,所以將結果集返回資料量小的表作為驅動表(from後邊最後一個表)。
4、大量的排序操作影響系統性能,所以儘量減少order by和group by排序操作。
如必須使用排序操作,請遵循如下規則:
(1) 排序儘量建立在有索引的列上。
(2) 如結果集不需唯一,使用union all代替union。
5、索引的使用。
(1) 儘量避免對索引列進行計算。如對索引列計算較多,請提請系統管理員建立函數索引。
(2) 儘量注意比較值與索引列資料類型的一致性。
(3) 對於複合索引,SQL語句必須使用主索引列
(4) 索引中,儘量避免使用NULL。
(5) 對於索引的比較,儘量避免使用NOT=(!=)
(6) 查詢列和排序列與索引列次序保持一致
6、儘量避免相同語句由於書寫格式的不同,而導致多次語法分析。
7、儘量使用共用的SQL語句。
8、查詢的WHERE過濾原則,應使過濾記錄數最多的條件放在最前面。
9、任何對列的操作都將導致資料表掃描,它包括資料庫函數、計算運算式等等,查詢時要盡可能將操作移至等號右邊。
10、in、or子句常會使用工作表,使索引失效;如果不產生大量重複值,可以考慮把子句拆開;拆開的子句中應該包含索引。
三、其他經驗性規則
1、儘量少用嵌套查詢。如必須,請用not exist代替not in子句。如例(2)
2、用多表連接代替EXISTS子句。如例(3)
3、少用DISTINCT,用EXISTS代替如例(4)
4、使用UNION ALL、MINUS、INTERSECT提高性能
5、使用ROWID提高檢索速度。對SELECT得到的單行記錄,需進行DELETE、UPDATE操作時,使用ROWID將會使效率大大提高。
6、使用優化線索機制進行訪問路徑控制。
7、使用cursor時,顯示游標優於隱式游標
本規範示例:
例一:
SELECT aka042 -- 單位繳費劃入個人帳戶比例
INTO prm_aaa043
FROM ka01 --醫療保險單位繳費劃入個人帳戶比例分段資訊
WHERE akc021 = rec_kc01.akc021 -- 醫療人員類別
AND aka041 >= rec_kc01.akc023 -- 年齡上限
AND aka040 <= rec_kc01.akc023 -- 年齡下限
AND aae030 <= prm_date -- 開始時間
AND ( aae031 >= prm_date OR aae031 IS NULL ); -- 終止時間
例二:
X SELECT ......
FROM emp
WHERE dept_no NOT IN ( SELECT dept_no
FROM dept
WHERE dept_cat='A');
O SELECT ......
FROM emp e
WHERE NOT EXISTS ( SELECT 'X'
FROM dept
WHERE dept_no=e.dept_no
AND dept_cat='A');
例三:
X SELECT ......
FROM emp
WHERE EXISTS ( SELECT 'X'
FROM dept
WHERE dept_no=e.dept_no
AND dept_cat='A');
O SELECT ......
FROM emp e,dept d
WHERE e.dept_no=d.dept_no
AND dept_cat='A';
例四:
X SELECT DISTINCT d.dept_code,d.dept_name
FROM dept d ,emp e
WHERE e.dept_code=d.dept_code;
O SELECT dept_code,dept_name
FROM dept d
WHERE EXISTS ( SELECT 'X'
FROM emp e
WHERE e.dept_code=d.dept_code);