▶ 技術交流

SQL 優化小經驗

文 - 袁昌


關鍵字:SQL、優化、ROWID、Oracle、資料庫

對於任何系統來說,回應時間是用戶體驗的一個重要指標,也直接決定了使用者系統的滿意度。如何加快系統回應時間,改善使用者的體驗,提高用戶滿意度,其中重要的一方面就是要做好 SQL 的優化。

以下是我在工作當中所學習到的一些關於 SQL 優化方面的小經驗:

  1. 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)

  2. 合理利用 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);

  3. 選擇最有效率的表名順序

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

  4. WHERE 子句中的連接順序

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

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

    例如:兩個欄位寫一起。
    SELECT TAB_NAME
    FROM TABLES
    WHERE (TAB_NAME,DB_VER) = (SELECT TAB_NAME,DB_VER FROM TAB_COLUMNS WHERE VERSION = 604)

  6. 用 >= 替代 >

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

  7. 應儘量避免在 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 技巧,希望能夠起到抛磚引玉的作用,也歡迎指正。

推Plurk
列印