oracle sql执行计划分析

问题描述

项目上反馈某个功能模块查询效率特别慢，接口响应一次要四分钟，使用jprofiler分析后发现有一个慢sql，如下：

SELECT T.*
  FROM (select t0.ID             as c_1,
               t0.TITLE          as c_2,
               t0.CODE           as c_3,
               t0.MASTERID       as c_4,
               t0.VALIDTIME      as c_5,
               t0.INVALIDTIME    as c_6,
               t1.DEFAULT_PERIOD as c_7,
               t1.D5             as c_8,
               t1.F5             as c_9,
               t1.G5             as c_10,
               t1.D6             as c_11,
               t1.ZT_MLZ         as c_65,
               t0.ID             as c_66
          from ZT_ORG_YSYWPF t0
          left join KTLAI0VB t1
            on t1.DEFAULT_PERIOD = '2020N0001'
           and t0.ID = t1.ZT_MLZ
         where exists (Select 1 From TMP_L2Y5YBEQ_1192 where ID = t0.ID)
          and t0.VALIDTIME <= TO_DATE('2020-12-31', 'yyyy-mm-dd')
          and t0.INVALIDTIME > TO_DATE('2020-12-31', 'yyyy-mm-dd')
           and (t1.ZT_MLZ IS NOT NULL)
         order by c_1
         ) T
 WHERE ROWNUM < 31

各表的数据量信息如下：

 select count(*) as c from KTLAI0VB;  --6851
 select count(*) as c from ZT_ORG_YSYWPF; --7493
 select count(*) as c from TMP_L2Y5YBEQ_1192;  --7479

各表的索引信息如下：
KTLAI0VB表

ZT_ORG_YSYWPF表

TMP_L2Y5YBEQ_1192表

注：使用的oracle数据库,版本11g

问题分析

F5执行计划，结果如下：

发现TMP_L2Y5YBEQ_1192 和KTLAI0VB 表都没有走索引。
将exists改成in，再次执行计划，这次三张表都走了索引，sql执行时间变成了1s左右。

去掉INVALIDTIME 的过滤条件后，执行结果：

解决方案

将ZT_ORG_YSYWPF 表的联合索引中再加一个字段INVALIDTIME ，执行结果如下：

扫描类型

index range scan(索引范围扫描)：

1.对于unique index来说，如果where 条件后⾯出现了<,> ,between …and…的时候，那么就可能执⾏index range scan,如果where条件
后⾯是=，那么就会执⾏index unique scan。
2.对于none unique index来说 如果where 条件后⾯出现了=,>,<,betweed…and…的时候，就有可能执⾏index range scan
3.对于组合索引来说，如果where条件后⾯出现了组合索引的引导列，那么可能执⾏index range scan。
index fast full scan(索引快速全扫描)：
如果select 语句后⾯中的列都被包含在组合索引中，⽽且where后⾯没有出现组合索引的引导列，并且需要检索出⼤部分数据，那么这个时
候可能执⾏index fast full scan。index fast full scan 发⽣的条件:
1.必须是组合索引
2.引导列不在where条件中

index skip scan(索引跳跃式扫描)

当查询可以通过组合索引得到结果，⽽且返回结果很少，并且where条件中没有包含索引引导列的时候，可能执⾏index skip scan
索引跳跃式扫描发⽣的条件:
1.必须是组合索引
2.引导列没有出现在where条件中

table access full（全表扫描）

Oracle 会读取表中所有的行，并检查每一行是否满足 where 限制条件
全表扫描时可以使用多块读（一次 I/O 读取多块数据块）操作，提升吞吐量
使用建议：数据量太大的表不建议使用全表扫描，除非本身需要取出的数据较多，占到表数据总量的 5% ~ 10% 或以上