前几天对生产库上的一个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博客

涉及子查询sql的一次优化

最新文章