SQL優化案例-正確的使用索引（二）

ylj007發表於2018-06-15
SQL優化索引

下面sql 30秒執行出結果，檢視sql謂詞中有like，我們知道謂詞中有這樣的語句是不走索引的（為了保護客戶的隱私，表名和部分列已經重新命名）。

點選(此處)摺疊或開啟

  1. SELECT /*+1*/
  2. CHECKNUM AS PINGZBSM,
  3. CHECKDATE,
  4. XXXMODE,
  5. XXXRESULT,
  6. (SELECT RESULT
  7. FROM (select ID,to_char(WMSYS.WM_CONCAT(xxxnum||xxxtype||xxxmode||xxxresult)) RESULT
  8.        from OOOO_XXXCHECKLOG
  9.       WHERE CHECKDATE BETWEEN DATE'2018-05-04' AND DATE'2018-05-04' and xxxtype like '%PAR'
  10.       GROUP BY ID
  11.     ) b where b.id=a.id
  12. ) RESULT,
  13. CLERKNUM AS CHECKNUM
  14. FROM OOO_XXXECHECKLOG A;




邏輯讀600多萬。檢視索引情況如下


表過濾返回資料量如下：

點選(此處)摺疊或開啟

  1. SQL> select count(*) from OOOO_XXXCHECKLOG;
  2. 2799616
  3. select count(*) from OOOO_XXXCHECKLOG WHERE CHECKDATE BETWEEN DATE'2018-05-04' AND DATE'2018-05-04' and xxxtype like '%PAR';
  4.  12856
  5. select count(*) from OOOO_XXXCHECKLOG WHERE CHECKDATE BETWEEN DATE'2018-05-04' AND DATE'2018-05-04';
  6. 197984

通過查詢上面返回資料可知，因為xxxtype不走索引，所以通過索引要回表197984次，如果走了索引只回表12856次。

下面我們建立REVERSE索引IDX_ID_TYPE_RE

點選(此處)摺疊或開啟

  1. SELECT /*+OOOO_XXXCHECKLOG index(IDX_ID_TYPE_RE) 2*/
  2. CHECKNUM AS PINGZBSM,
  3. CHECKDATE,
  4. XXXMODE,
  5. XXXRESULT,
  6. (SELECT RESULT
  7. FROM (select ID,to_char(WMSYS.WM_CONCAT(xxxnum||xxxtype||xxxmode||xxxresult)) RESULT
  8.        from OOOO_XXXCHECKLOG
  9.       WHERE CHECKDATE BETWEEN DATE'2018-05-04' AND DATE'2018-05-04' and REVERSE(xxxtype) like 'RAP%'
  10.       GROUP BY ID
  11.     ) b where b.id=a.id
  12. ) RESULT,
  13. CLERKNUM AS CHECKNUM
  14. FROM OOO_XXXECHECKLOG A;

檢視執行計劃如下，邏輯讀將為300萬，但是時間還是維持在18秒，根本原因在於這個索引因為標量子查詢的問題被訪問700萬次導致。



   下面我們改寫sql如下

點選(此處)摺疊或開啟

  1. SELECT /*+ index(OOOO_XXXCHECKLOG IDX_ID_TYPE_RE) 3*/
  2. CHECKNUM AS PINGZBSM,
  3. CHECKDATE,
  4. XXXMODE,
  5. XXXRESULT,
  6. B.RESULT,
  7. CLERKNUM AS CHECKNUM
  8. FROM OOO_XXXECHECKLOG A
  9. left join (select ID,to_char(WMSYS.WM_CONCAT(xxxnum||xxxtype||xxxmode||xxxresult)) RESULT
  10.        from OOOO_XXXCHECKLOG
  11.       WHERE CHECKDATE BETWEEN DATE'2018-05-04' AND DATE'2018-05-04' and REVERSE(xxxtype) like 'RAP%'
  12.       GROUP BY ID
  13.     ) b on b.id=a.id;


執行計劃中出現index_skip_scan。 

下面我們建立如下索引:

點選(此處)摺疊或開啟

  1. create index idx_date_seal_re on OOOO_XXXCHECKLOG(CHECKDATE,REVERSE(xxxtype));




   可以看到，邏輯讀降到64424，50個物理讀是因為剛剛建立索引的原因，sql也秒出。

| 作者簡介

姚崇·沃趣科技高階資料庫技術專家

熟悉Oracle資料庫內部機制，豐富的資料庫及RAC叢集層故障診斷、效能調優、OWI、資料庫備份恢復及遷移經驗。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31542492/viewspace-2156227/，如需轉載，請註明出處，否則將追究法律責任。

相關文章