1. STest软件测试社区首页
  2. 后台测试

专项测试之SQL调优

之前参与的敏捷项目中,开发在编码之前,召集测试一起把设计文档过一遍,目的是通过沟通,尽早发现设计上的一些缺陷,避免放大效应。文档内容包括业务需求的具体实现,以及业务涉及的新表定义、DML、数据流,这种实践对于测试的提早介入有重要意义,我们确实避免了许多问题,如新表设计的不合理、新表缺乏向下兼容性、DML冗余、DML性能问题、DML逻辑问题等等。敏捷项目对测试角色提出了更高的要求,今天主要讲的是Oracle SQL调优,这是性能测试偏底层的知识,效费比不言而喻。

合理选择基础表

ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表)将被最先处理。 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。

避免使用*

ORACLE在解析的过程中, 会将‘*’ 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间

减少访问次数

减少访问占用连接资源,减少网络IO、磁盘IO的次数,以下仅供参考
分散语句

select name from tableA where a_no = 1;
select name from tableB where b_no = 2;

合并语句

select a.name , b.name 
from tableA a , tableB b,dual x
where nvl(x,x.dummy) = nvl(x,a.rowid(+))
and nvl(x,x.dummy) = nvl(x,b.rowid(+))
and a.a_no (+) = 1
and b.b_no (+) = 2;

删除数据

对于删除重复数据,用rowid是最高效的

delete from tableA a where rowid != (select max(rowid) from tableB b where a.Prefix = b.Prefix);

对于删除全表数据,用TRUNCATE替代DELETE。使用TRUNCATE,回滚段不再存放任何可被恢复的信息,数据不能被恢复,因此执行时间也会很短。

delete from table;
--替换成
truncate from table;

养成使用别名的习惯

当在SQL语句中连接多个表时, 请使用表的别名,并把别名前缀于每个Column上,以减少解析的时间并减少那些由Column歧义引起的语法错误。

表连接替换EXISTS

--低效
select ename
from emp e
where exists (select x
from dept
where dept_no = e.dept_no
and dept_cat = a);

--高效
select ename
from dept d,emp e
where e.dept_no = d.dept_no
and dept_cat = a ;

EXISTS替换DISTINCT

--低效
select distinct d.dept_no, d.dept_name
from dept d,emp e
where d.dept_no = e.dept_no

--高效
select d.dept_no, d.dept_name
from dept d
where exists (select x
from emp e
where e.dept_no = d.dept_no);

尽量避免耗费资源的操作

带有DISTINCT、UNION、MINUS、INTERSECT、ORDER BY的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能。 DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序。

避免索引失效

避免改变索引列的类型:当比较不同数据类型的数据时, ORACLE自动对列进行简单的类型转换。当字符和数值比较时, ORACLE会优先转换数值类型到字符类型。如果内部发生类型转换, 这个索引将不会被用到。
避免在索引列上使用IS NULL和IS NOT NULL:对于单列索引,如果列包含空值,索引中将不存在此记录;对于复合索引,如果每个列都为空,索引中同样不存在此记录。
避免在索引列上使用NOT:当ORACLE遇到NOT,他就会停止使用索引转而执行全表扫描。
避免在索引列上使用计算:如果索引列是函数的一部分,将不使用索引而使用全表扫描。

--低效
select field from dept where val * 12 > 120

--高效
select field from dept
where val > 120/12

避免在索引列上使用函数:否则将停止使用索引而使用全表扫描。
避免在索引列上的第一个字符使用通配符:如果索引列所对应的值的第一个字符由通配符开始,索引将不被采用。在这种情况下,ORACLE将使用全表扫描。
避免索引列使用OR:对索引列使用OR将造成全表扫描。
使用索引的第一个列:如果索引是建立在多个列上,只有在它的第一个列被where子句引用时才会选择使用该索引。
了解索引排他性:如果表中有两个以上(包括两个)索引,其中有一个唯一性索引,而其他是非唯一性。在这种情况下,ORACLE将使用唯一性索引而完全忽略非唯一性索引。

善用工具分析问题

PL/SQL、TOAD都集成了图形化的EXPLAIN PLAN,这里不再详述

,

原创文章,作者:测试通,如若转载,请注明出处:https://www.stest.com

发表评论

邮箱地址不会被公开。 必填项已用*标注

QR code