一条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)特性而已。执行计划如下: