实验六 Sql优化与实践

一、前提概要

为什么要Sql优化？事实上，在实际运用场景中，若开发的时候写了一些慢sql，数据量小的时候可能还好，一旦数据量上来了，查询效能极低，并且请求次数过多的话很可能会因为这一个慢sql把你整个系统拖垮，不能正常对外提供服务，因此要进行sql优化。

事实上，SQL优化有许多方法，本文只介绍其中的两种:

批处理方式和索引方式

二、环境准备

2.1 事先操作

#启动监听
lsnrctl  start

#导入实例
export ORACLE_SID=orcl
#以数据库管理员身份登录
sqlplus / as sysdba

2.2创建PlusTrace角色

角色plustrace是Oracle数据自带一个角色，他由$ORACLE_HOME/sqlplus/admin/路径下的plustrce.sql脚本执行得来，如图:

随后在SQL>命令行中执行plustrce.sql脚本，用@关键字

@/u01/app/oracle/product/19.2.0/db_home1/sqlplus/admin/plustrce.sql 

如此，产生了一个角色plustrace

2.3 创建用户并赋予权利

#创建用户txp
grant connect,resource to txp identified by txp;

grant connect,resource to ly identified by ly;
#将角色plustrace的权利赋予txp
grant plustrace to txp;
grant plustrace to ly;
#将表空间无限使用权赋予
grant unlimited tablespace to txp;
grant unlimited tablespace to ly;

2.4 建表

2.4.1登录txp用户

注：这里的用户是上文所创建并赋予完权限的用户

sqlplus txp/txp

2.4.2建表

CREATE TABLE EMP
 (EMPNO NUMBER(4),
	ENAME VARCHAR2(10),
	JOB VARCHAR2(9),
	MGR NUMBER(4),
	HIREDATE DATE,
	SAL NUMBER(7,2),
	COMM NUMBER(7,2),
	DEPTNO NUMBER(2));

2.4.3插入数据

#插入多条数据
INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,to_date('13-6-87','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,to_date('13-JUL-87')-51,1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);

#一定要commit提交
commit;

2.5查看执行计划

#查看语句花费的代价
set autotrace traceonly

select * from emp where empno=7788; 

2.6创建一张新表

#子查询创建新表
create table emp2 as select * from emp where 1=2;

#为接下来防止插入数据做准备
alter table emp2 modify empno number(9);
alter table emp2 modify ENAME varchar2(20);

2.7往表插入大量记录

#开启一个语句块
begin
  for i in 1..50000 loop
  	insert into emp2 
  	values (i,'orcl'||i,'CLERK',7698,sysdate,3000,1000,10);
  end loop;
  commit;
end;
/ 

注：大量的数据插入可能会导致内存空间不足，谨慎插入数据！

至此，准备工作完成

三、SQL优化

3.1 批量处理方式优化

3.1.1概要

redolog：重做日志，在操作⼀条数据之前需要记录redo log，然后再修改数据，因而在数据操作后会产生大量的redolog

undolog：撤销日志，为了保证读⼀致性，在更新数据到提交之前，Oracle会先把旧数据写⼊到undolog中，因而也会产生大量数据在磁盘中

因而大量数据产生磁盘中，全盘扫描(full)故而花费更大的代价去执行查询语句

针对这个问题，故出现以下优化思路:

1.更少的产生undo数据->阶段性的提交数据

2.产生更少的redolog -> nologging

3.取消归档模式

因此，接下来一一执行

3.1.2 调整优化

1.改变数据库运行方式

#关闭数据库
shutdown abort
	
#启动到mount状态	
startup mount

#改变模式
alter database noarchivelog;
alter database open;

2.调整表，少产生redolog

注：这里txp用户需要重新登录，因为第一步数据库shutdown abort异常关闭

#重新登录
export ORACLE_SID=orcl
sqlplus txp/txp
#修改模式
alter table emp2 nologging;

3.阶段性提交，少产生undo数据

begin
  for i in 1..50000 loop
  	insert into emp2 
  	values (i,'orcl'||i,'CLERK',7698,sysdate,3000,1000,10);
  	if mod(i,1000) =0 then
  	commit;  #阶段性提交
  	end if;
  end loop;
end;
/	

注：可以看到这里的插入速度还是不怎么慢，因为受实验环境受限数据量插入不大，可自行调整

4.删除emp2数据

truncate table emp2;

5.打开计时器

set timin on
#目的是查看执行时间

6.执行修改后的sql语句

select * from emp where empno=7788; 

注：若环境准备步骤中插入的数据量足够多，几十万甚至几千万，插入的时候会等待很长时间，可以用下面的代码来观察数据文件增长情况，undo表空间的数据文件

#查看增长情况
set linesize 120
col name for a50
select name,bytes/1024/1024 from v$datafile;

#查看emp2表数据增长情况
select count(*) from emp2; 

7.还原原来环境，与优化环境做对比

alter table emp2 logging;

8.改成归档模式

#关闭数据库
shutdown immediate
#启动到mount状态
startup mount
#修改模式
alter database archivelog;

alter database open;

9.操作前清理emp2:

truncate table emp2;

10.重新登录操作，与优化作对比

注:这里是因为改变了模式，数据库关闭，需要重新登录txp用户

#打开计时器
set timin on
#插入数据
begin
  for i in 1..50000 loop
  	insert into emp2 
  	values (i,'orcl'||i,'CLERK',7698,sysdate,3000,1000,10);
  end loop;
  commit;    #注意：这里没有采用阶段性的提交数据
end;
/ 

#注意前后对比两种插入花费的时间开销

补充说明：由于实验本人实验环境的数据库的容量有限，为防止导入大量数据占据恢复区空间而导致数据库崩溃，这里作者导入的数据量是偏少的，因此能察觉到的变化很小。事实上，若插入的数据在几十万条或者几千万条的时候，优化和不优化前后执行的插入数据语句执行速度，有着明显的快慢区别

至此，批处理优化方法介绍完毕

3.2 建立索引的优化方法

3.2.1概要

在日常开发中，select查询sql很慢，大部分都可以通过添加索引来解决。但索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。

值得一提的是，索引是Oracle优化中效果最明显的方式

3.2.2 调整优化

1.未创索引前

#查看执行计划
set autotrace traceonly
#执行查询语句
SQL> select * from emp2 where empno=7788;

注:索引优化主要优化查询select语句，因此用select语句可更清楚看清前后的对比

2.创建索引后

#创建索引
create index emp2_empno_ind on emp2 (empno);
#让计算机为表产生索引(采用估算的方法)
analyze table emp2 estimate statistics;
#格式化大小 正确显示
set linesize 120
#再次查看执行计划
select * from emp2 where empno=7788;

查看查询代价

可以看出，索引创建前后，select查询的花销是有大小之分的
补:若数据量足够多的时候，select查询的代价在建立索引前后区别更加明显