[zt] 影響SQL效能的原因

影響SQL效能的因素很多，如初始化引數設定不合理、匯入了不準確的系統及模式統計資料從而影響最佳化程式

(CBO)的正確判斷等，這些往往和DBA密切相關。純粹從SQL語句出發，筆者認為影響SQL效能不外乎以下四個重要

原因：
  
    (1)在大記錄集上進行高成本操作，如使用了引起排序的謂詞等。

    (2)過多的I/O操作(含物理I/O與邏輯I/O)，最典型的就是未建立恰當的索引，導致對查詢表進行全表掃描。

    (3)處理了太多的無用記錄，如在多表連線時過濾條件位置不當導致中間結果集包含了太多的無用記錄。

    (4)未充分利用資料庫提供的功能，如查詢的並行化處理等。
第(4)個原因處理起來相對簡單。論文將針對前三個原因論述如何提高SQL查詢語句的效能。

 

 

最佳化排序操作

 排序的成本十分高昂，當在查詢語句中使用了引起結果集排序的謂詞時，SQL效能必然受到影響。
 排序過程分析
    當待排序資料集不是太大時，伺服器在記憶體(排序區)完成排序操作，如果排序需要更多的記憶體空間，伺服器

將進行如下處理：

    (1) 將資料分成多個小的集合，對每一集合進行排序。

    (2) 伺服器向磁碟申請臨時空間，將排好序的中間結果寫入臨時段，再對另外的集合進行排序。

    (3) 在所有的集合均排好序後，伺服器再將它們進行合併得到最終的結果，如果排序區尺寸太小，合併無法

一次完成時，將分多次進行。

    從上述分析可知，排序是一種十分昂貴的操作，它消耗大量的CPU時間和記憶體，觸發磁碟分頁和交換操作，因

此只要有可能，我們就應該在SQL語句中儘量避免排序操作。
 SQL中引起排序的操作
    SQL查詢語句中引起排序的操作大致有：ORDER BY和GROUP BY從句；DISTINCT修飾符；UNION、INTERSECT、

MINUS集合運算子；多表連線時的排序合併連線(SORT MERGE JOIN)等。
 如何避免排序
   1)建立恰當的索引
    對經常進行排序和連線操作的欄位建立索引。在建立索引後，當伺服器向這些欄位發出排序請求時，將直接

引用索引而不進行排序操作；當進行等值連線查詢操作時，若建立連線的欄位未建立索引，伺服器進行的是排序

合併連線(SORT MERGE JOIN)，連線操作的過程如下：
    對進行連線的兩個或多個表分別進行全掃描；
    對每一個表中的行集分別進行全排序；
    合併排序結果。
    如果建立連線的欄位已建立索引，伺服器進行巢狀迴圈連線(NESTED LOOP JOINS)，該連線方式不需要任何排

序，其過程如下：
    對驅動表進行全表掃描；
    對返回的每一行利用連線欄位值實施索引惟一掃描；
    利用從索引掃描中返回的ROWID值在從表中定位記錄；
    合併主、從表中的匹配記錄。
    因此，建立索引可避免多數排序操作。
   2)用UNIION ALL替換UNION
    UNION在進行錶連結後會篩選掉重複的記錄，所以在錶連結後會對所產生的結果集進行排序運算，刪除重複的

記錄再返回結果。大部分應用中是不會產生重複記錄的，最常見的是過程表與歷史表UNION 。因此，採用UNION

ALL運算子替代UNION，因為UNION ALL操作只是簡單的將兩個結果合併後就返回。

 

 最佳化I/O
    過多的I/O操作會佔用CPU時間、消耗大量記憶體和佔用過多的栓鎖，因此有必要對SQL的I/O進行最佳化。最佳化I/O

的最有效方式就是用索引掃描代替全表掃描。
 應用基於函式的索引
    基於函式的索引(FUNCTION BASED INDEX，簡記為FBI)提供了索引計算列並在查詢中使用這些索引的能力。

FBI的實質是對查詢所需中間結果進行預處理。如果一個FBI與查詢語句中的內嵌函式完全匹配，CBO在生成查詢計

劃時，將自動啟用索引範圍掃描(INDEX RANGE SCAN)替換全表掃描(FULL TABLE SCAN)。考察下面的程式碼段並用

AUTOTRACE觀察建立FBI前後執行計劃的變化。
    select * from emp where upper(ename)=’SCOTT’
    建立FBI前，很明顯是全表掃描。
    Execution Plan
    ……
       1    0   TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=2 Card=1 Bytes=22)
    idle>CREATE INDEX EMP_UPPER_FIRST_NAME ON EMPLOYEES(UPPER(FIRST_NAME))；
    索引已建立。
    再次執行相同查詢，
    Execution Plan
    ……
       1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEES' (Cost=1 Card=1 Bytes=22)
       2    1   INDEX (RANGE SCAN) OF 'EMP_UPPER_FIRST_NAME' (NON-UNIQUE) (Cost=1 Card=1)
    這一簡單的例子充分說明了FBI在SQL查詢最佳化中的作用。FBI所用的函式可以是使用者自己建立的函式，該函式

越複雜，基於該函式建立FBI對SQL查詢效能的最佳化作用越明顯。
應用物化檢視和查詢重寫
    物化檢視是一個預計算結果集，其中通常包含聚集與多表連線等複雜操作。資料庫自動維護物化檢視，且隨

使用者的要求進行重新整理。查詢重寫機制就是用資料庫中的替代物件(如物化檢視)將使用者提交的查詢重寫為完全不同

但功能等價的查詢。查詢重寫對使用者透明，使用者完全按常規編寫訪問資料庫的查詢語句，最佳化程式(CBO)自動決定

是否對使用者提交的查詢進行重寫。查詢重寫是提高查詢效能的一種非常有效的方法，尤其是在資料倉儲環境中針

對彙總、多表連線以及其它高成本的操作方面。
    下面以一個非常簡單的例子來演示物化檢視和查詢重寫在最佳化SQL查詢效能方面的作用。
    select dept.deptno，dept.dname，count(*)
    from emp，dept
    where emp.deptno=dept.deptno
    group by dept.deptno，dept.dname
    /

 

查詢計劃及主要統計資料如下：
    執行計劃：
    -----------------------------------------
    ……
       2    1   HASH JOIN (Cost=5 Card=14 Bytes=224)
       3    2   TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=4 Bytes=52)
       4    2   TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=42)
    主要統計資料：
    -----------------------------------------
        305  recursive calls
          46  consistent gets
    建立物化檢視EMP_DEPT：
    create materialized view emp_dept build immediate
    refresh on demand
    enable query rewrite
    as
    select dept.deptno，dept.dname，count(*)
    from emp，dept
    where emp.deptno=dept.deptno
    group by dept.deptno，dept.dname
    /
    再次執行查詢，執行計劃及主要統計資料如下：
    執行計劃：
    -------------------------------------
    ……
       1    0   TABLE ACCESS (FULL) OF 'EMP_DEPT' (Cost=2 Card=327 Bytes=11445)
    主要統計資料：
    ------------------------------------
         79  recursive calls
         28  consistent gets
    可見，在建立物化檢視之前，首先執行兩個表的全表掃描，然後進行HASH連線，再進行分組排序和選擇操作

；而建立物化檢視後，CBO自動將上述複雜操作轉換為對物化檢視EMP_DEPT的全掃描，相關的統計資料也有了很大

的改善，遞迴呼叫(RECURSIVE CALLS)由305降到79，邏輯I/O(CONSISTENT GETS)由46降為28。

 將頻繁訪問的小表讀入CACHE
    邏輯I/O總是快於物理I/O。如果資料庫中存在被應用程式頻繁訪問的小表，可將這些表強行讀入KEEP池，從

而避免物理I/O的發生

 

多表連線最佳化
    最能體現查詢複雜性的就是多表連線，多表連線操作往往要耗費大量的CPU時間和記憶體，因此多表連線查詢性

能最佳化往往是SQL最佳化的重點與難點。
 消除外部連線
    透過消除外部連線，不僅使得到的查詢更易於讀取，而且效能也經常可以得到改善。一般的思路是，有以下

形式的查詢：
    SELECT …，OUTER_JOINED_TABLE.COLUMN
    FROM SOME_TABLE，OUTER_JOINED_TO_TABLE
    WHERE …=OUTER_JOINED_TO_TABLE(+)
    可轉換為如下形式的查詢：
    SELECT …，(SELECT COLUMN FROM OUTER_ JOINED_TO_TABLE WHERE …)FROM SOME_TABLE；
 謂詞前推，最佳化中間結果
    多表連線的效能低下多數是因為連線操作與過濾操作的次序不合理，大多數使用者在編寫多表連線查詢時，總

是先進行連線操作再應用過濾條件，這導致伺服器做了太多的無用功。針對這類問題，其最佳化思路就是儘可能將

過濾謂詞前推，使不符合條件的記錄提前被篩選掉，只對符合條件的少數記錄進行連線處理，這樣可成倍的提高

SQL查詢效能。

 

標準連線查詢如下：
Select a.prod_name,  sum(b.sale_quant),

    sum(c.sale_quant),sum(d.sale_quant)
From product a,tele_sale b,online_sale c, store_sale d
Where a.prod_id=b.prod_id
and a.prod_id=c.prod_id
and a.prod_id=d.prod_id
And a.order_date>sysdate-90
Group by a.prod_id

    啟用內嵌檢視，且將條件a.order_date>sysdate-90前移，最佳化後程式碼如下：
Selecta.prod_name，b.tele_sale_sum，c.online_sale_sum，d.store_sale_sum

Fromproduct a，
    (select sum(sal_quant) tele_sale_sum from product，tele_sale
    Where product.order_date>sysdate-90 and product.prod_id =tele_sale.prod_id) b，
    (select sum(sal_quant) online_sale_sum
    from product，tele_sale
    Where product.order_date>sysdate-90 and product.prod_id =online_sale.prod_id) c，
    (select sum(sal_quant) store_sale_sum
    from product，store_sale
    Where product.order_date>sysdate-90 and product.prod_id =store_sale.prod_id) d，
Wherea.prod_id=b.prod_id and
    a.prod_id=c.prod_id and a.prod_id=d.prod_id；