sql语法优化

 

1、SQL语句优化

1.1、     多表查询时，记录数少的表放到后面。

Oracle的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表将被最先处理。在FROM子句中包含多个表的情况下，将记录数最少的表放到where的最后。

例：select count(*) from tableA,tableB;(tableA 100万条，tableB 1万条)

例：当三张表关联查询时，应将交叉表放到后面，其次是记录少的表：

Select count(1) from tableA a,tableB b,tableC c where c.id = a.id and c.id = b.id;

1.2、     Where子句中尽量不用is null或is not null。

在where子句中使用is null或is not null的语句优化器不允许使用索引的，尽量不用。

1.3、     通配符(%)在索引字段词首出现时会降低效率。

当通配符在索引字段词首出现时，oracle是不是用此列做索引的，如此会降低查询速度。但是当通配符出现在此字段其他位置时，优化器就能利用索引，下列语句索引起到了作用。

SELECT * FROM employee WHERE last_name LIKE ‘c%’;

1.4、     SELECT子句中避免使用”*”。

1.5、     避免在索引上使用计算。如(WHERE sal > 2500/12)

1.6、     任何在order by语句的非索引项或计算表达式都将降低查询速度。

1.7、     一般情况下性能上：count(一个索引字段) > count(*) > count(一个非索引字段)

1.8、     减少访问数据库的次数。

如查询员工号等于0342或0291的员工信息：

第一种查询方法：

Select * from emp where emp_no = ‘0342’;

Select * from emp where emp_no = ‘0291’;

第二种查询方法：

Select * from emp where emp_no = ‘0342’ or emp_no = ‘0291’;

1.9、     关于char和varchar2的使用。

Char的长度是固定的，而varchar2的长度是可变的；一般char比varchar2效率高。

但varchar2比char节省磁盘空间，在节省很多空间情况下varchar2也会比char效率高。

1.10、  一般情况下，如果字段中含有中文字符用nchar或nvarchar2；如果是纯英文和数字用char或varchar2。

1.11、  少用或不用NOT。

不等于操作永远不会用到索引的，只会全表扫描。

不推荐使用：select * from emp where sal <> 3000;

推荐使用：  select * from emp where sal < 3000 or sal > 3000;

1.12、  IN和EXISTS

IN：子查询先产生结果集，然后主查询再去结果集里去找符合要求的字段列表去，符合要求的输出，反之则不输出。如果子查询得出的结果集记录少，主查询中的表大且有索引时应该用IN。

Select * from tableA where x in(select y from tableB);

以上语句适合tableB记录少，tableA记录多且有索引的情况。

EXISTS：后面的子查询被称做相关子查询。他是不返回列表的值的，只是返回一个ture或false的结果，其运行方式是先运行主查询一次，再去子查询里查询与其对应的结果，如果是ture则输出，反之则不输出，再根据主查询中的每一行去子查询里去查询。如果外层主查询记录少，子查询中的表大又有索引时使用EXISTS。

Select * from tableA a where exists(select * from tableB b where b.id = a.id);

以上语句适合tableA记录少，tableB记录多且有索引的情况。

1.13、  DECODE：(避免重复扫描相同记录或重复连接相同表)

从员工表中查询每个部门的员工数及工资总和。

不推荐：

select count(*),sum(sal) from emp where dept_no = ‘0020’;

select count(*),sum(sal) from emp where dept_no = ‘0030’;

推荐：

select count(decode(dept_no,0020,’x’,null)) count_0020,

count(decode(dept_no,0030,’x’,null)) count_0030,

sum(decode(dept_no,0020,sal,null)) sal_0020,

sum(decode(dept_no,0030,sal,null)) sal_0030

from emp;

1.14、  高效删除重复记录：

Delete from emp e where e.rowid >

(select min(m.rowid) from emp m where m.emp_no = e.emp_no);

ROWID用来唯一标识表中的一条记录，是这条数据在数据库中存放的物理地址.

1.15、  PL/SQL Developer中Expain Plan

在pl/sql developer工具中有个Expain Plan分析的功能，可以帮助我们分析sql语句是否用来索引、使用了那些索引和使用索引的效果。