oracle执行计划越多,【Oracle性能优化】执行计划与索引类型分析

一条sql的好坏，主要来源两个方面：

1、 从数据库层面：取决于优化器所采用的数据访问方式和数据处理的方式决定

2、从业务方面来讲：这条sql在业务上是不是一条好的sql

我们以oracle 11g为例子进行分析。

一、数据的访问方式

【没有索引】

如果一张表没有建立索引，那么优化器采用的数据访问方式也会截然不同，这就取决于oracle的数据访问方式，下边列举两种：

1、并行访问

2、多数据块访问

【有索引】

建立索引的情况，也会有不同的数据访问方式，主要有下面5种：

1、唯一索引(index unique scan)

2、范围索引扫描(index range scan)

3、全索引扫描(index full scan)

4、全索引快速扫描(index fast full scan)

5、索引跳跃扫描(index skip scan)

二、数据的处理方式

上面列举了几种数据的访问方式，其实像我们日常开发中使用到的排序order by，分组group by、统计count等等操作，都是对数据的一种操作方式，但是，除了这些基本的操作方式之外，我们一般还会对表进行连接join处理，对于连接这种处理方式，又有下面几种情况：

1、nested loop join(内部嵌套循环连接)

2、hash join(哈希连接)

3、sort merge join(合并排序连接)

接下来，我们使用测试用例验证上面3种join数据处理方式，测试SQL用例如下：

-- 删除nestedLoopTest1、nestedLoopTest2表

drop table nestedLoopTest1 ;

drop table nestedLoopTest2 ;

-- 创建nestedLoopTest表

create table nestedLoopTest1

(

id NUMBER(11)

);

commit;

create table nestedLoopTest2

(

id NUMBER(11)

);

commit;

-- 各赋值100条数据

BEGIN

FOR i IN 0..100 LOOP

INSERT INTO nestedLoopTest1(id) VALUES(i);

END LOOP;

END;

commit;

BEGIN

FOR i IN 0..100 LOOP

INSERT INTO nestedLoopTest2(id) VALUES(i);

END LOOP;

END;

commit;

-- 1、hash join 哈希连接，因为此时两张表是并行执行的

xxxxxx

-- 2、nested join 内部嵌套连接，此时t2中id建了索引

xxxxxx

-- 3、两个表的id都建立了索引

复制代码1、hash join(哈希连接)

我们继续执行下面sql：

select t1.* from nestedLoopTest1 t1,nestedLoopTest2 t2 where t1.id=t2.id;

复制代码

此时表nestedLoopTest1和表nestedLoopTest2中的id都没有建立索引，因此，我们会看到下面的执行计划：

执行步骤如上图所示，我们可以看到，此时两张表都是全表扫描，然后再进行一次Hash join,至于hash join的原理，后面单独学习介绍。hash join会将小表load进内存中，然后利用大表和小表进行关联操作

2、nested loop join(内部嵌套循环连接)

我们继续执行下面sql：

create index nestedLoopTest2index on nestedLoopTest2(id);

select t1.* from nestedLoopTest1 t1,nestedLoopTest2 t2 where t1.id=t2.id;

复制代码

从执行计划中可以看出，首先对表t1进行全表扫描，然后对索引nestedLoopTest2index进行range范围扫描,为什么是范围扫描呢？因为表t1中的一条记录，可能在表t2对应多条记录。还有就是，在写sql的时候要尽量减少回表操作，上图从执行计划看没有回表操作，即没有TABLE ACCESS BY INDEX ROWID

对于循环嵌套连接方式，我们可以想象成2个for循环嵌套即可。

另外，当两个表都建立索引时，我们再继续执行下面的sql：

create index nestedLoopTest1index on nestedLoopTest1(id);

select t1.* from nestedLoopTest1 t1,nestedLoopTest2 t2 where t1.id=t2.id;

复制代码

相比上图，表t1不再是全表扫描了，而是全索引扫描。

3、sort merge join(合并排序连接)

该链接方式大概的原理就是，判断原表是否排序，如果未排序，则针对关联字段进行排序；判断关联表是否排序，如果未排序，则进行排序，最后将两个排序的表进行合并。

三、oracle执行计划

我们要知道oracle数据是如何数据访问和数据处理的，我们就要看下执行计划，但执行计划又仅仅告诉我们这些信息。

我们登陆上sqlplus，就拿一条最简单的sql进行说明，简单说下我们应该如何看懂执行计划：

select * from emp;

emp表是oracle自带的员工表，右键点击Explain Plan，或者按下F5查看执行计划，如下图：

执行计划最左边的Description列是比较重要的，它会列出这个sql的一些执行步骤，该列有下面几个查看规则：

1、层次不同情况下，越靠右的步骤越先执行；

2、层次相同情况下，越上方的结果越先执行；

上图告诉我们，这条语句采用的数据访问方式是：TABLE ACCESS FULL，也就是全表扫描，我们可能会问，这个emp表不是有建立索引吗？其实有索引也没有用，因为我们就是要提取整个表的数据，索引没有意义，这也说明一个情况，有索引的表，不一定效率就高，后面会讲到。

对于Cost这个指标，这是oracle优化器用来衡量这条sql执行的代价有多大，比如需要消耗多少CPU计算资源呀之类的。

下面介绍几种通过索引访问方式的SQL例子

一、唯一索引(index unique scan)

empno是emp表的主键，是一个唯一索引，我们执行下面sql语句，查看其执行计划，如下图：

select * from emp where empno=7782

从执行计划中显示的INDEX UNIQUE SCAN可以看出，这句sql，oracle优化器会执行唯一索引扫描，扫描完索引之后，我们得到索引的值为7782，然后oracle肯定要去数据文件中去取这条编号对应的数据块返回嘛，因此我们可以看到执行计划中显示了TABLE ACCESS BY INDEX ROWID，因为索引存的是每一行的id，因此oracle根据rowid这个属性去找对应的数据。

其实去访问数据块取数据这个步骤有时候是没有的，也就是当你只想取其编号empno而不是*的时候，执行计划就不会去数据文件中取数据了，也就是步骤2不会有了,因为oracle直接从索引扫描到之后就直接返回索引这个值就行了，没必要去取数据，我们又不需要，验证如下：

select empno from emp where empno=7782

其实我们一般也不会写这样的sql吧，哈哈~~~

二、范围索引扫描(index range scan)

假设我们执行下面sql语句：

select job from emp where empno>7782

其执行计划如下：

从执行计划中可以看出，这种类型的SQL语句，采用的执行方式为index range scan范围索引扫描。

三、全索引扫描(index full scan)

全索引扫描，顾名思义就是扫描整个索引区域就能确定出执行结果，比如下面sql语句:

select count(*) from emp

我们统计整个表的所有数据个数，直接读索引数据块的个数即可，步骤2为将步骤一的记过进行一个求和，汇总得到一个总数返回。

四、全索引快速扫描(index fast full scan)

我们先用下面语句拷贝一个表并将重命名：

create table emp1 as (select * from emp);

truncate table emp1;

-- 插入1,000,000条数据

BEGIN

FOR I IN 0..1000000 LOOP

INSERT INTO EMP1(EMPNO,ENAME) VALUES(

I,CONCAT('TBL',I));

END LOOP;

END;

复制代码

表建好之后，我们看下下面sql的执行计划，看看当数据量大的时候，这句sql会不会采用index fast full scan

index fast full scan区别于index full scan的地方是前者可以一次性读取多个数据块，类似于并行，而后者串行读取。

使用这种执行方式的SQL语句一般是那种可以直接通过索引就能确定出执行结果，比如我们执行下面SQL：

五、索引跳跃扫描(index skip scan)

index skip scan是oracle 9i之后才提供的索引扫描方式，主要使用来解决组合索引中，where条件使用非前导列查询时，默认采用ACCESS TABLE FULL全表扫描的缺点。但是使用该特性是有一些限制条件的，主要有下面几个点：

1、组合索引前导列唯一值较少(重复值很多)

2、数据库采用CBO优化器，且表和索引都经过分析

3、where查询条件中不存在组合索引前导列

接下来我们主要验证两个问题：

测试相应的SQL语句如下：

--删除表

drop table student;

commit;

-- 创建Student表

create table STUDENT

(

stuno NUMBER(11),

stuname VARCHAR2(20),

schoolno NUMBER(11),

age NUMBER(3)

);

commit;

-- 创建组合索引

create index stucombindex on student(stuname,schoolno);

commit;

--F5查看执行计划，会看到是ACCESS TABLE FULL全表扫描,stuname是前导列，schoolno为非前导列

select * from student t where t.schoolno=100;

-- 赋值100万条数据

BEGIN

FOR i IN 0..1000000 LOOP

INSERT INTO student(stuno,stuname,schoolno) VALUES(

i,'TBL',i);

END LOOP;

END;

commit;

-- 更新3条数据的前导列为不同值

update student t set t.stuname=concat('s',t.stuno) where mod(t.stuno,10000)=0;

commit;

select count(*),count(distinct stuname) from student;

-- 对表、索引进行分析

analyze table student compute statistics for table for all columns for all indexes;

-- 此处查看执行计划，可看到优化器采用的是index skip scan

select * from student where schoolno = 1000;

复制代码

1、组合索引中，使用非前导列进行查询时，优化器采用的是ACCESS TABLE FULL全表扫描

首先将上述sql中，倒数第二句SQL注释掉，将会输出如下内容，默认采用全表扫描：

2、验证index skip scan

经过我们的验证，我们可以知道，当我们建立组合索引时，日常开发中，我们尽可能将 __需求经常用到、选择性高重复值少__的列作为前导列，这样才能最大程度减少非引导列不走索引或者只走跳跃索引的情况。

另外我们什么时候建立组合索引呢？主要考虑下面几种情况：

1、当单条件查询时，返回较多数据

2、当符合条件查询时，返回数据较少

当且仅当条件1和条件2同时成立时，我们这个时候可以建立组合索引了，举个例子，员工表employee中，age=28这个条件的人非常多，role=Java程序员这个条件返回的数据也非常多，但是age=28 and role=Java程序员返回的数据却非常少！

单条件指：where xxx=xxx 复合条件指：where xxx=xxx and xxx1=xxx1

六、应用场景

1、oracle千万级别大表分页查询

传统oracle分页使用如下结构：

select *

from (

select fundacco,rownum rowno from

tbl_20191231

where rownum <= #{end}) b

where

b.rowno > #{start}

复制代码

当时当start越来越大的时候，这个外层子查询所需要遍历的数据量就越多，经过实际生产验证会很慢，500W数据量，每页250条，当start大于200W时，平均耗时在1-2s。

如何优化呢？oracle sql层面上我们不能进行优化了，但我们可以通过新增加一个列rownos,值单调递增且建立唯一索引。然后我们通过下面sql查询就非常快了，平均在20ms左右。

select * from tbl_20191231 t where t.rownos > #{start} and t.rownos <= #{end}

复制代码

其实不难发现，我们是利用了oracle的索引范围扫描(index range scan)特性而已。执行计划如下：