通用技术 专项测试之 SQL 调优

扫地僧 · 2016年04月13日 · 最后由 云翼风希 回复于 2016年07月13日 · 2426 次阅读

之前参与的敏捷项目中,开发在编码之前,召集测试一起把设计文档过一遍,目的是通过沟通,尽早发现设计上的一些缺陷,避免放大效应。文档内容包括业务需求的具体实现,以及业务涉及的新表定义、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,这里不再详述

如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
共收到 8 条回复 时间 点赞

单测不仅限于程序代码,数据这块,包括存储过程、package 等都可进行单测,比调用它的程序代码更底层,更原子化了

赞,很实用。

mark!

mark,好文

简明的讲 SQL 优化的不多,mark 一下,感谢分享。

需要 登录 后方可回复, 如果你还没有账号请点击这里 注册