技術交流

SQL 優化小經驗

更新於

對於任何系統來說,回應時間是用戶體驗的一個重要指標,也直接決定了使用者系統的滿意度。如何加快系統回應時間,改善使用者的體驗,提高用戶滿意度,其中重要的一方面就是要做好 SQL 的優化。以下是我在工作當中所學習到的一些關於 SQL 優化方面的小經驗:

WHERE 語句中,儘量避免對索引欄位進行計算操作

例如:Where trunc(create_date)=trunc(sysdate) 雖然已對 create_date 欄位建了索引,但由於加了 TRUNC,使得索引無法用上。 此處應該改為如下段:
Where create_date>=trunc(sysdate) and create_date]<trunc(sysdate)+1
或者
Where create_date between trunc(sysdate) and trunc(sysdate)+1-1/(24*60*60)

合理利用 ROWID

ROWID 包含了表中記錄的物理位置資訊,Oracle 採用 INDEX 實現了資料和存放資料的物理位置之間的聯繫,通常索引提供了快速訪問 ROWID 的方法,因此那些基於索引列的查詢就可以得到性能上的提高。如下最高效的刪除重複記錄方法:


DELETE FROM EMP E WHERE E.ROWID > 
(SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);

選擇最有效率的表名順序

Oracle 的解析器按照從右到左的連續處理 FROM 子句中的表名,FROM 子句中寫在最後的表將被最先處理,在 FROM 子句中包含多個表的情況下,必須選擇記錄條數最少的表作為基礎資料表。如果有 3 個以上的表連接查詢,那就需要選擇交叉表作為基礎資料表,交叉表是指那個被其他表所引用的表。

WHERE 子句中的連接順序

Oracle 採用自下而上的順序解析 WHERE 子句,根據這個原理,表之間的連接必須寫在其他 WHERE 條件之前,那些可以過濾掉最大數量記錄的條件必須寫在 WHERE 子句的末尾。

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

例如兩個欄位寫一起。


SELECT TAB_NAME
FROM TABLES
WHERE (TAB_NAME,DB_VER) = (SELECT TAB_NAME,DB_VER FROM TAB_COLUMNS WHERE VERSION = 604)

>= 替代 >

A. SELECT * FROM EMP WHERE DEPTNO >= 4
B. SELECT * FROM EMP WHERE DEPTNO > 3
兩者的區別在於,前者 DBMS 將直接跳到第一個 DEPT 等於 4 的記錄;而後者將首先定位到 DEPTNO=3 的記錄,並且向前掃描到第一個 DEPT 大於 3 的記錄。

應儘量避免在 WHERE 子句中使用 OR 來連接條件,否則將導致引擎放棄使用索引而進行全資料表掃描

例如:select id from t where num=10 or num=20 可以用如下 SQL 替代:


select id from t where num=10
union all
select id from t where num=20

當然資料庫性能的提升範圍很廣泛,以上僅為個人工作中使用並非常有效的 SQL 技巧,希望能夠起到抛磚引玉的作用,也歡迎指正。

閱讀更多