SQL效能最佳化之索引最佳化法

SQL最佳化是最佳化工作中經常會涉及的問題，由於早期的開發人員往往只關注於SQL功能的實現，而忽略了效能。特別是複雜的SQL，上線之後很少修改，一旦出現問題，即使是當初的開發人員自己也很難理清其中的業務邏輯，需要花費大量的時間去理解程式碼之間的關係，最終可能還是感覺無從下手。因此開發人員前期應做好程式碼註釋，避免編寫過於複雜的SQL語句。本文為大家介紹一些生產環境中真實的常用索引最佳化方法。

遇到問題SQL時，大家可以根據各自的習慣使用不同的工具(PL/SQL、TOAD等)對SQL進行格式化，我們需要重點關注的是FROM後面的表，以及包含WHERE語句的條件，然後透過awrsqrpt或dbms_xplan獲取SQL的詳細執行計劃和資源消耗資訊，業務案例中的SQL語句如下：

SQL> select sum(cggzl) cggzl, sum(qbgzl) qbgzl 
  from (select case 
                 when zlxm_mc like '%2ê3?3￡1??ì2é%' then 
                  gzl 
                 else 
                  0 
               end cggzl, 
               case 
                 when zlxm_mc like '%?3±í?÷1ù%' then 
                  gzl 
                 else 
                  0 
               end qbgzl 
          from dictmanage.dict_zl_pro   b, 
               his.pat_inpat_order_info c, 
               pat_inpat_order_cost     d 
         where d.sfxm_id = b.zlxm_id 
           and c.yzjl_id = d.dyzy_yzjl_id 
           and zlxm_mc like '%2???%' 
           and c.yz_zxrq >= to_date(sysdate) 
           and c.yz_zxrq < to_date(sysdate + 1) 
           and d.fy_status in ('1', '2') 
           and sfxm_je > 0 
           and c.yz_zfrq is null 
           and c.zylsh = :in_zylsh)

SQL的詳細執行計劃如圖1所示。

AWR報告中的資源消耗資訊如圖2所示。

上述程式碼所示的業務SQL語句透過三張表進行關聯，最終返回的行數為個位數，從執行計劃中我們可以看出，Id=0，CBO計算總的COST為123K，其中絕大部分的COST是由Id=10的表pat_inpat_order_cost全表掃描所產生的。此時，我們需要重點關注 pat_inpat_order_cost與其他兩張表格的關聯情況，where條件中，pat_inpat_order_cost的sfxm_id和dyzy_yzjl_id除了與其他兩張表的欄位相關聯之外，只有fy_status一個過濾條件，下面我們就來看下該列的選擇性，程式碼如下：

SQL> select /*+ NO_MERGE LEADING(a b) */ 
 b.owner, 
 b.table_name, 
 a.column_name, 
 b.num_rows, 
 a.num_distinct Cardinality, 
 ROUND(A.num_distinct * 100 / B.num_rows, 1) selectivity 
  from dba_tab_col_statistics a, dba_tables b 
 where a.owner = b.owner 
   and a.table_name = b.table_name 
   and a.owner = upper('his') 
   and a.table_name = upper('pat_inpat_order_cost') 
   and a.column_name = upper('fy_status');

pat_inpat_order_cost表的欄位資訊如圖3所示。

SQL> select count(*), FY_STATUS 
  from his.pat_inpat_order_cost c 
 group by FY_STATUS;

fy_status欄位列的選擇性如圖4所示。

由圖4可知，fy_status的選擇性並不好，而且存在嚴重傾斜，語句中的固定寫法d.fy_status in ('1', '2')幾乎包含了所有記錄，因此其並不是一個很好的過濾條件。where條件中的大部分過濾條件均來自於C表pat_inpat_order_info，而且C表與D表pat_inpat_order_cost的sfxm_id欄位相關聯。

整個SQL語句最終返回的行數為個位數，C表透過YZ_ZXRQ_IDX索引範圍掃描再回表進行過濾，獲取繫結變數值，之後再進一步確認C表返回的行數，程式碼如下：

SQL> select sql_Id, name, datatype_string, last_captured, value_string 
  from v$sql_bind_capture 
 where sql_id = '18rwad2bgcxfa';

SQL繫結變數值獲取情況如圖5所示。

SQL> select count(*) 
  from his.pat_inpat_order_info c 
 where c.yz_zxrq >= to_date(sysdate) 
   and c.yz_zxrq < to_date(sysdate + 1) 
   and c.yz_zfrq is null 
   and c.zylsh = 72706;

帶入繫結變數我們可以發現，這個查詢返回的行數都保持在個位數，如果C表和D表採用巢狀連線的方式，C表能作為驅動表與D表pat_inpat_order_cost相關聯，被驅動表只需要在關聯列上建立索引，即可大幅提升整個查詢的效率，做法其實很簡單，只需要在sfxm_id欄位上建立索引即可， 如下：

SQL> create index IDX_SFXM_ID on PAT_INPAT_ORDER_COST (SFXM_ID); 
Plan hash value: 408580053 
------------------------------------------------------------------------------------------------ 
| Id  | Operation               | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | 
------------------------------------------------------------------------------------------------ 
|   0 | SELECT STATEMENT        |                      |       |       |    12 (100)|          | 
|   1 |  SORT AGGREGATE         |                      |     1 |    68 |            |          | 
|*  2 |   FILTER                |                      |       |       |            |          | 
|   3 |    NESTED LOOPS         |                      |     1 |    68 |    12   (0)| 00:00:01 | 
|   4 |     NESTED LOOPS        |                      |     1 |    68 |    12   (0)| 00:00:01 | 
|   5 |      NESTED LOOPS       |                      |     1 |    39 |    11   (0)| 00:00:01 | 
|*  6 |       TABLE ACCESS BY GLOBAL INDEX ROWID 
                                | PAT_INPAT_ORDER_INFO |     1 |    21 |     5   (0)| 00:00:01 | 
|*  7 |        INDEX RANGE SCAN | YZ_ZXRQ_IDX          |     4 |       |     3   (0)| 00:00:01 | 
|*  8 |       TABLE ACCESS BY GLOBAL INDEX ROWID 
                                | PAT_INPAT_ORDER_COST |     6 |   108 |     6   (0)| 00:00:01 | 
|*  9 |        INDEX RANGE SCAN | IDX_DYZY_YZJL_ID     |     6 |       |     2   (0)| 00:00:01 | 
|* 10 |      INDEX UNIQUE SCAN  | DICT_ZL_PRO_PK       |     1 |       |     0   (0)|          | 
|* 11 |     TABLE ACCESS BY INDEX ROWID | DICT_ZL_PRO  |     1 |    29 |     1   (0)| 00:00:01 | 
------------------------------------------------------------------------------------------------ 
Predicate Information (identified by operation id): 
--------------------------------------------------- 
   2 - filter(TO_DATE(TO_CHAR(SYSDATE@!+1))>TO_DATE(TO_CHAR(SYSDATE@!))) 
   6 - filter(("C"."ZYLSH"=TO_NUMBER(:IN_ZYLSH) AND "C"."YZ_ZFRQ" IS NULL)) 
   7 - access("C"."YZ_ZXRQ">=TO_DATE(TO_CHAR(SYSDATE@!)) AND "C"."YZ_ZXRQ"0 AND INTERNAL_FUNCTION("D"."FY_STATUS"))) 
   9 - access("C"."YZJL_ID"="D"."DYZY_YZJL_ID") 
  10 - access("D"."SFXM_ID"="B"."ZLXM_ID") 
  11 - filter("ZLXM_MC" LIKE '%部位%')

建立索引之後，整個執行計劃按照我們設想的方式進行，SQL執行時間也從原來的24分鐘縮短到1秒，速度提升了上千倍。

上述案例介紹了一種最簡單的SQL最佳化方式，在大多數情況下，我們很難讓開發商修改應用，因此索引的最佳化在SQL最佳化工作中顯得尤為重要。