2018_04_16 数据库SQL优化方案(一)

(1)选择最有效率的表名顺序

Oracle的解析器是按照从右到左的顺序处理From子句中的表名,From子句中写在最后的表(基础表)将被最先处理,当From子句中包含多个表的情况时,应该选择记录条数最少的表作为基础表;如果是三个以上的表连接查询,需要选择交叉表作为基础表,交叉表就是被其他表所引用的表。

(2)Where子句中的连接顺序

Oracle采用自下而上的顺序解析Where子句,根据这个原理,表之间的连接应该写在其他where条件之前,那些可以过滤掉最大数量记录的条件必须写在where子句的末尾。

(3)Select子句中避免使用'*'

Oracle在解析过程中,会将'*'一次转换成所有的列名。

(4)减少访问数据库的次数:

Oracle在内部执行了很多工作:解析SQL语句,估算索引的利用率,绑定变量,读数据块等。

(5)使用Decode函数来减少处理时间:

使用Decode函数可以避免重复扫描相同记录或重复连接相同的表。

DECODE的语法:DECODE(value,if1,then1,if2,then2,if3,then3,...,else),表示如果value 等于if1时,DECODE函数的结果返回then1,...,如果不等于任何一个if值,则返回else。初看一下,DECODE 只能做等于测试,实际上我们通过一些函数或计算替代value,是可以使DECODE函数具备大于、小于或等于功能。

比较大小的函数:

  select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; --取较小值

  sign()函数根据某个值是0、正数还是负数,分别返回0、1、-1

  例如:

  变量1=10,变量2=20

  则sign(变量1-变量2)返回-1,decode解码结果为“变量1”,达到了取较小值的目的。


(6)用Where子句替换Having子句


避免使用Having子句,HAVING 只会在检索出所有记录之后才对结果集进行过滤。这个处理需要排序,总计等操作。如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销。(非 oracle中)on、where、having这三个都可以加条件的子句中,on是最先执行,where次之,having最后,因为on是先把不符合 条件的记录过滤后才进行统计,它就可以减少中间运算要处理的数据,按理说应该速度是最快的,where也应该比having快点的,因为它过滤数据后才进 行sum,在两个表联接时才用on的,所以在一个表的时候,就剩下where跟having比较了。在这单表查询统计的情况下,如果要过滤的条件没有涉及 到要计算字段,那它们的结果是一样的,只是where可以使用rushmore技术,而having就不能,在速度上后者要慢如果要涉及到计算的字段,就 表示在没计算之前,这个字段的值是不确定的,根据上篇写的工作流程,where的作用时间是在计算之前就完成的,而having就是在计算后才起作用的, 所以在这种情况下,两者的结果会不同。在多表联接查询时,on比where更早起作用。系统首先根据各个表之间的联接条件,把多个表合成一个临时表后,再 由where进行过滤,然后再计算,计算完后再由having进行过滤。由此可见,要想过滤条件起到正确的作用,首先要明白这个条件应该在什么时候起作用,然后再决定放在那里。


(7)减少对表的查询:

在含有子查询的SQL语句中,要特别注意减少对表的查询

(8)使用表的别名(Alias): 

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

(9)用EXISTS替代IN、用NOT EXISTS替代NOT IN:
在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接。在这种情况下,使用EXISTS(或NOT EXISTS)通常将提高查询的效率。在子查询中,NOT IN子句将执行一个内部的排序和合并。无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历)。为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS。

例子: 

(高效)SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND EXISTS (SELECT ‘X' FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB')

(低 效)SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB')

(10)用EXISTS替换DISTINCT:
当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT。一般可以考虑用EXIST替换, EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果。例子:

(低效): SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D , EMP E WHERE D.DEPT_NO = E.DEPT_NO 

(高效): SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT ‘X' FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);

(11)SQL语句用大写的;因为Oracle总是先解析SQL语句,把小写的字母转换成大写的再执行。 

(12)避免在索引列上使用计算。WHERE子句中,如果索引列是函数的一部分。优化器将不使用索引而使用全表扫描。

(低 效): SELECT … FROM DEPT WHERE SAL * 12 > 25000; 
(高 效): SELECT … FROM DEPT WHERE SAL > 25000/12;

(13)用>=替代>:

高效:SELECT * FROM EMP WHERE DEPTNO >=4 

低效: SELECT * FROM EMP WHERE DEPTNO >3

两者的区别在于,前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记 录。

(14)用IN来替换OR: 

(低 效):SELECT…. FROM LOCATION WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30

(高 效):SELECT… FROM LOCATION WHERE LOC_IN IN (10,20,30);

(15)优化GROUP BY:
提高GROUP BY 语句的效率,可以通过将不需要的记录在GROUP BY 之前过滤掉。下面两个查询返回相同结果但第二个明显就快了许多。
低效: SELECT JOB , AVG(SAL) FROM EMP GROUP BY JOB HAVING JOB = ‘PRESIDENT' OR JOB = ‘MANAGER' 
高效: SELECT JOB , AVG(SAL) FROM EMP WHERE JOB = ‘PRESIDENT' OR JOB = ‘MANAGER' GROUP BY JOB

阅读更多
文章标签: SQL优化
个人分类: 日常积累
上一篇2018_04_16 本地项目发布到linux服务器上404问题
下一篇2018_04_16 数据库SQL优化方案(二)
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭