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

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

之前参与的敏捷项目中，开发在编码之前，召集测试一起把设计文档过一遍，目的是通过沟通，尽早发现设计上的一些缺陷，避免放大效应。文档内容包括业务需求的具体实现，以及业务涉及的新表定义、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 将使用唯一性索引而完全忽略非唯一性索引。