问题描述

项目上反馈某个功能模块查询效率特别慢，接口响应一次要四分钟，使用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条件中