第十七章 SQL优化
删除一张表的重复记录(前面的那个题)
数据量很大,性能要求很高
表名T
Id name age
1 louis 20
2 louis 20
3 jimmy 30
4 louis 20
做法一:
Delete from t where id not in (select min(id) from t group by name,age);
做法二:
Delete from t wehre id in (select distinct a2.id from t a1, t a2 where a1.id > a2.id and a1.name=a2.name and a1.age=a2.age);
做法三:
Delete from t a1 where not exists (select * from t a2 where a1.id >a2.id and a1.nam=a2.name and a1.age=a2.age);
(后期补充说明)
尽量少用IN操作符
基本上所有的IN操作符都可以用EXISTS代替,在选择IN或EXISTS操作时,要根据主子表数据量来具体考虑(详见第四章)。
尽量用NOT EXISTS或者外连接替代NOT IN操作符
因为NOT IN不能应用表的索引
尽量不用“<>”或者“!=”操作符
不等于操作符是永远不会用到索引的,因此对它的处理只会产生全表扫描。比如a<>0改为a>0 or a<0
在设计表时,把索引列设置为NOT NULL
判断字段是否为空右边是不会应用索引的,因为B树索引是不索引空值的。
尽量不用通配符“%”或者“_”作为查询字符串的第一个字符
当通配符作为查询字符串的第一个字符时,索引不会被使用。比如用T表中的Column1 LIKE ‘%5400%’这个条件会产生全表扫描,如果改为Column1 ‘X5400%’ OR Column1 LIKE ‘B5400%’则会利用Column1的索引进行两个范围的查询,性能查询大大提高。
Where子句中避免在索引列上使用计算
如果索引不是基于函数的,那么当在where子句中对索引列使用函数时,索引不再起作用。因此Where子句中避免在索引列上使用计算。
比如:
Substr(no, 1,4) =’5400’,优化处理:no like ‘5400%’
Trunc(hiredate)=trunk(sysdate),优化处理:hiredate>=trunk(sysdate) and hiredate<trunk(sysdate+1)
使用“>=”替代“>”
大于或小于操作符一般情况下是不用调整的,因为它有索引就会采用索引查找,但有的情况下可以对它进行优化,如一个表有100W条记录,一个数值型字段A,30W记录为A=0和A=1,39万记录为A=2,1万记录为A=3,那么执行A>2与执行A>=3的效果就有很大的区别了,因为A>2时ORACLE会先找出为2 的记录索引再进行比较,而A>=3时ORACLE则直接找到3的记录索引。
利用SGA共享池,避开parse阶段
同一功能同一性能不同写法SQL的影响。
Select * from abc;
Select * from t.abc; --带表所有者的前缀
Select * from T.ABC; --大写表名
Select * from T.ABC; --中间多几个空格
以上四个SQL在ORACLE分析整理之后产生的结果及执行时间是一样的,但是从ORACLE共享内存SGA的原理,可以得出ORACLE对每个SQL都会对其进行一次分析,并且占用共享内存,如果将SQL的字符串及格式写得完全相同则ORACLE只会分析一次,共享内存也只会留下一次的分析结果,这不仅可以减少分析SQL的时间,而且可以减少共享内存重复的信息,ORACLE也可以准确统计SQL的执行频率。
不同区域出现的相同的SQL语句要保证查询字符完全相同,建议经常使用变量来代替常量,以尽量使用重复的SQL代码,以利用SGA共享池,避开parse阶段,防止相同的SQL语句被多次分析,提高执行速度。
因此使用存储过程,是一种很有效的提高share pool共享率,跳过parse阶段,提高效率的办法。
Where后面的条件顺序要求
Where后面的条件,表连接语句写在最前,可以过滤掉最大数量记录的条件局后。比如:
Select * from t where c1=’1kv以下’ and c2=1
Select * from t where c2=1 and c1=’1kv以下’
以上两个SQL中c1及c2两个字段都没进行索引,所以执行的时候都是全表扫描,第一条SQL的c1的条件在记录集内比率为99%,而c2的比率只为0.5%,在进行第一条SQL的时候99%条记录都进行c1和c2的比较,而在第二条SQL的时候0.5%条记录都进行c1和c2的比较,以此可以得出第二条SQL的CPU占用率明显比第一条低。
使用表的别名,并将之作为每列的前缀
当在SQL语句中连接多个表时,使用表的别名,并将之作为每列的前缀。这样可以减少解析时间。
进行了显式或隐式的运算的字段不能进行索引
比如ss_df+20>50,优化后:ss_df>30
Hbs_bh=5401002554,优化处理:hbs_bh=’5401002554’,注:此条件对hbs_bh进行隐式的to_number转换,因为hbs_bh字段是字符型的。
用UNION ALL代替UNION
UNION是最常用的集操作,使多个记录集联结称为单个集,对返回的数据行有唯一性要求,所以oracle就需要进行SORT UNIQUE操作(与使用distinct时操作类似),如果结果集又比较大,则操作会比较慢。
UNION ALL操作不排除重复记录行,所以会快很多,如果数据本身重复行存在可能性较小时,用union all会比用union效率高很多!
其他操作
尽量使用packages:packages在第一次调用时能将整个包load进内存,对提高性能有帮助。
尽量使用cached sequences来生成primary key:提高主键生成速度和使用性能;
很好地利用空间:如用varchar2数据类型代替char等;
使用SQL优化工具:sqlexpert,toad;explain-table;PL/SQL;OEM
通过改变oracle的SGA的大小
SGA:数据库的系统全局区
SGA主要由三部分构成:共享池、数据缓冲区、日志缓冲区
1, 共享池又由两部分构成:共享SQL区和数据字典缓冲区。共享SQL区专门存放用户SQL命令,oracle使用最近最少使用等优先级算法来更新覆盖;数据字典缓冲区(library cache)存放数据库运行的动态信息。数据库运行一段时间后,DBA需要查看这些内存区域的命中率以从数据库角度对数据库性能调优。
通过执行:
select (sum(pins-reloads))/sum(pins) “Lib Cache” from v$librarycache;--查看共享SQL区的重用率,最好在90%以上,否则需要增加共享池的大小。
select (sum(gets-getmisses-usage-fixED))/sum(gets) “ROW Cache” from v$rowcache; --查看数据字典缓冲区的命中率,最好在90%以上,否则需要增加共享池的大小。
2, 数据缓冲区:存放sql运行结果抓取到的data block。
Select name,value from v$sysstat where name in (‘db block gets’,’consistent gets’,’physical reads’);--查看数据库数据缓冲区的使用情况。查询出来的结果可以计算出来数据缓冲区的使用命中率=1-(physical reads /(db block gets + consistent gets))。命中率应该在90%以上,否则需要增加数据缓冲区的大小。
3, 日志缓冲区:存放数据库运行生成的日志。
Select name,value from v$sysstat where name in (‘redo entries’,’redo log space requests’);--查看日志缓冲区的使用情况。查询出的结果可以计算出日志缓冲区的申请失败率:申请失败率=requests/entries,申请失败率应该接近于0,否则说明日志缓冲区开设太小,需要增加oracle数据库的日志缓冲区。