admin管理员组文章数量:1660166
实验六 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查询的代价在建立索引前后区别更加明显
版权声明：本文标题：Oracle数据学习笔记六——Oracle数据库的sql优化 内容由热心网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：https://www.elefans.com/xitong/1729851293a1215448.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论