涉及子查詢sql的一次優化

前幾天對生產庫上的一個sql進行優化，語句結構如下
select min(s.create_time) from justin s
where exists(select 1 from justin_item si
where s.id = si.stat_id and s.status in(4,38)
and  si.num_id = :1)
其目前執行計劃為
------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes |TempSpc| Cost (%CPU)| Time    |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT             |                   |    1 | 27 |    | 52499 (1)| 00:10:30 |
| 1 |  SORT AGGREGATE             |                   |    1 | 27 |    |          |       |
|*  2 | HASH JOIN                |                   | 221K|  5852K|  4768K| 52499 (1)| 00:10:30 |
| 3 | TABLE ACCESS BY INDEX ROWID| justin_ITEM       | 221K|  2165K|    | 18610 (1)| 00:03:44 |
|*  4 |    INDEX RANGE SCAN       | IDX_SI_num_id    | 226K|    |    | 983 (1)| 00:00:12 |
|*  5 | TABLE ACCESS FULL       | justin          | 261K|  4342K|    | 33288 (1)| 00:06:40 |
------------------------------------------------------------------------------------------------------------
對錶justin選擇了全表掃描，而該表有幾百萬條記錄，因此效率十分低下；
剛開始新增hint
select /*+ index(s,pk_justin) */ min(s.create_time) from justin s
where exists(select 1 from justin_item si
where s.id = si.stat_id and s.status in(4,38)
and  si.num_id = :a)
而此時的執行計劃如下
------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes |TempSpc| Cost (%CPU)| Time    |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT             |                   |    1 | 27 |    | 310K  (1)| 01:02:05 |
| 1 |  SORT AGGREGATE             |                   |    1 | 27 |    |          |       |
|*  2 | HASH JOIN                |                   | 221K|  5852K|  4768K| 310K  (1)| 01:02:05 |
| 3 | TABLE ACCESS BY INDEX ROWID| justin_ITEM       | 221K|  2165K|    | 18610 (1)| 00:03:44 |
|*  4 |    INDEX RANGE SCAN       | IDX_SI_num_id    | 226K|    |    | 983 (1)| 00:00:12 |
|*  5 | TABLE ACCESS BY INDEX ROWID| justin          | 261K|  4342K|    | 291K  (1)| 00:58:14 |
| 6 |    INDEX FULL SCAN          | pk_justin       |  1700K|    |    | 20941 (1)| 00:04:12 |
------------------------------------------------------------------------------------------------------------
但是consistent gets卻比新增前高了2倍
仔細觀察一下，對錶justin雖然選擇了主鍵掃描，但是卻是index full scan，且還要回表操作，代價比full table scan還要高；
兩表使用了hash join作為關聯，這意味著需要單獨取出兩表的候選資料，然後進行hash關聯；
而新增Hint的初衷是想要sql先訪問justin_item上的資料，然後將過濾出的資料按照s.id = si.stat_id的關聯條件去訪問justin表上的id主鍵，這就需要使用nest loop join；
再新增一個hint use_nl(si,s)，再次檢視執行計劃
SQL> select /*+ use_nl(si,s) index(s,pk_justin) */ min(s.create_time) from justin s
  2  where exists(select 1 from justin_item si
  3  where s.id = si.stat_id and s.status in(4,38)
  4  and  si.num_id = :a)
  5  ;

------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes |TempSpc| Cost (%CPU)| Time    |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT             |                   |    1 | 27 |    | 310K  (1)| 01:02:05 |
| 1 |  SORT AGGREGATE             |                   |    1 | 27 |    |          |       |
|*  2 | HASH JOIN                |                   | 221K|  5852K|  4768K| 310K  (1)| 01:02:05 |
| 3 | TABLE ACCESS BY INDEX ROWID| justin_ITEM       | 221K|  2165K|    | 18610 (1)| 00:03:44 |
|*  4 |    INDEX RANGE SCAN       | IDX_SI_num_id    | 226K|    |    | 983 (1)| 00:00:12 |
|*  5 | TABLE ACCESS BY INDEX ROWID| justin          | 261K|  4342K|    | 291K  (1)| 00:58:14 |
| 6 |    INDEX FULL SCAN          | pk_justin       |  1700K|    |    | 20941 (1)| 00:04:12 |
------------------------------------------------------------------------------------------------------------
發現執行計劃較上次並沒有改變，兩表關聯依舊為hash join

請注意，子查詢中有一行為s.status in (4, 38)，此過濾條件根本不訪問justin_item，不需要放在子查詢中，將其移到外部，再次檢視執行計劃；
此時訪問pk_justin時用到了index unique scan，而邏輯讀由原來的404518下降為現在的2625，提升了200多倍。
SQL> select /*+ use_nl(si s) index(s pk_justin) */
  2 min(s.create_time)
  3 from justin s
  4 where exists (select 1
  5          from justin_item si
  6          where s.id = si.stat_id
  7          and si.num_id = :a)
  8          and s.status in (4, 38);

-----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time    |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT             |                   |    1 | 27 | 241K  (1)| 00:48:19 |
| 1 |  SORT AGGREGATE             |                   |    1 | 27 |          |       |
| 2 | NESTED LOOPS                |                   | 221K|  5852K| 241K  (1)| 00:48:19 |
| 3 | SORT UNIQUE                |                   | 221K|  2165K| 18610 (1)| 00:03:44 |
| 4 |    TABLE ACCESS BY INDEX ROWID| justin_ITEM       | 221K|  2165K| 18610 (1)| 00:03:44 |
|*  5 |    INDEX RANGE SCAN       | IDX_SI_num_id    | 226K|    | 983 (1)| 00:00:12 |
|*  6 | TABLE ACCESS BY INDEX ROWID | justin          |    1 | 17 |    2 (0)| 00:00:01 |
|*  7 |    INDEX UNIQUE SCAN       | pk_justin       |    1 |    |    1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

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

涉及子查詢sql的一次優化

相關文章