关于SQL优化的一些思想和原则

数据库 Oracle
SQL语句是对数据库(数据)进行操作的惟一途径,消耗了70%~90%的数据库资源。独立于程序设计逻辑,相对于对程序源代码的优化,对SQL语句的优化在时间成本和风险上的代价都很低。本节主要介绍一些SQL优化方面的知识,希望会对读者有所帮助。

一、优化的目的

对应用程序的优化通常可分为两个方面:源代码优化和SQL语句优化。由于涉及到对程序逻辑的改变,源代码的优化在时间成本和风险上代价很高(尤其是对正在使用中的系统进行优化) 。另一方面,源代码的优化对数据库系统性能的提升收效有限,因为应用程序对数据库的操作最终要表现为SQL语句对数据库的操作。

对SQL语句优化有以下一些直接原因:

1.SQL语句是对数据库(数据) 进行操作的惟一途径,应用程序的执行最终要归结为SQL语句的执行,SQL语句的效率对数据库系统的性能起到了决定性的作用。

2.SQL语句消耗了70%~90%的数据库资源。

3.SQL语句独立于程序设计逻辑,对SQL语句进行优化不会影响程序逻辑,相对于对程序源代码的优化,对SQL语句优化在时间成本和风险上的代价都很低。

4.SQL语句可以有不同的写法,不同的写法在性能上的差异可能很大。

5.SQL语句易学,难精通。SQL语句的性能往往同实际运行系统的数据库结构、记录数量等有关,不存在普遍适用的规律来提升性能。

二、优化数据库的思想

1、关键字段建立索引。

2、使用存储过程,它使SQL变得更加灵活和高效。

3、备份数据库和清除垃圾数据。

4、SQL语句语法的优化。

5、清理删除日志。

三、SQL语句优化的原则

不要以为只有SELECT语句是查询。实际上,带有任何WHERE条件的DML(INSERT、UPDATE、DELETE)语句中都包含查询要求,在后面的文章中,当说到查询时,不一定只是指SELECT语句,也有可能指DML语句中的查询部分。

我们知道,SQL语句同其它语言(如C语言)的语句不一样,它是非过程化(non-procedural)的语句,即当你要取数据时,不需要告诉数据库通 过何种途径去取数据,如到底是通过索引取数据,还是应该将表中的每行数据都取出来,然后再通过一一比较的方式取数据(即全表扫描)。

为了实现一个查询,内核必须为每个查询定制一个查询策略,或为取出符合条件的数据生成一个执行计划(execution plan)。典型的,对于同一个查询,可能有几个执行计划都符合要求,都能得到符合条件的数据。例如,参与连接的表可以有多种不同的连接方法,这取决于连接条件和优化器采用的连接方法。为了在多个执行计划中选择最优的执行计划,优化器必须使用一些实际的指标来衡量每个执行计划使用的资源(I/0次数、 CPU等),这些资源也就是我们所说的代价(cost)。如果一个执行计划使用的资源多,我们就说使用执行计划的代价大。以执行计划的代价大小作为衡量标准,优化器选择代价最小的执行计划作为真正执行该查询的执行计划,并抛弃其它的执行计划。

#p#

四、关于ORACLE优化器

在任何可能的时候都会对表达式进行评估,并且把特定的语法结构转换成等价的结构,这么做的原因是 :要么结果表达式能够比源表达式具有更快的速度;要么源表达式只是结果表达式的一个等价语义结构。不同的SQL结构有时具有同样的操作(例如:= ANY (subquery) and IN (subquery)),ORACLE会把他们映射到一个单一的语义结构。下面我们介绍常量优化和一些常用的操作符优化的知识。

1、常量优化:

常量的计算是在语句被优化时一次性完成,而不是在每次执行时。下面是检索月薪大于2000的的表达式:

 

  1. sal > 24000/12   
  2.  
  3. sal > 2000   
  4.  
  5. sal*12 > 24000  

 

如果SQL语句包括第一种情况,优化器会简单地把它转变成第二种。

优化器不会简化跨越比较符的表达式,例如第三条语句,鉴于此,应尽量写用常量跟字段比较检索的表达式,而不要将字段置于表达式当中。否则没有办法优化,比如如果sal上有索引,第一和第二就可以使用,第三就难以使用。

2、操作符优化:

优化器把使用LIKE操作符和一个没有通配符的表达式组成的检索表达式转换为一个“=”操作符表达式。例如:优化器会把表达式ename LIKE 'SMITH'转换为ename = 'SMITH'。优化器只能转换涉及到可变长数据类型的表达式,前一个例子中,如果ENAME字段的类型是CHAR(10), 那么优化器将不做任何转换。一般来讲LIKE比较难以优化。

IN 操作符优化:

优化器把使用IN比较符的检索表达式替换为等价的使用“=”和“OR”操作符的检索表达式。

例如,优化器会把表达式ename IN ('SMITH','KING','JONES')替换为:ename = 'SMITH' OR ename = 'KING' OR ename = 'JONES‘。

ANY和SOME 操作符优化:

优化器将跟随值列表的ANY和SOME检索条件用等价的同等操作符和“OR”组成的表达式替换。例如,优化器将如下所示的第一条语句用第二条语句替换:

 

  1. sal > ANY (:first_sal, :second_sal)   
  2.  
  3. sal > :first_sal OR  
  4.  
  5. sal > :second_sal  

 

优化器将跟随子查询的ANY和SOME检索条件转换成由“EXISTS”和一个相应的子查询组成的检索表达式。例如,优化器将如下所示的第一条语句用第二条语句替换:

 

  1. > ANY (SELECT sal FROM emp WHERE job = 'ANALYST')   
  2.  
  3. EXISTS (SELECT sal FROM emp WHERE job = 'ANALYST' AND x > sal) 

 

ALL操作符优化:

优化器将跟随值列表的ALL操作符用等价的“=”和“AND”组成的表达式替换。例如:sal > ALL (:first_sal, :second_sal)表达式会被替换为:sal > :first_sal AND sal > :second_sal 。

对于跟随子查询的ALL表达式,优化器用ANY和另外一个合适的比较符组成的表达式替换。例如x > ALL (SELECT sal FROM emp WHERE deptno = 10) 替换为:NOT (x <= ANY (SELECT sal FROM emp WHERE deptno = 10)) 。接下来优化器会把第二个表达式适用ANY表达式的转换规则转换为下面的表达式:

 

  1. NOT EXISTS (SELECT sal FROM emp WHERE deptno = 10 AND x <= sal) 

 

BETWEEN 操作符优化:

优化器总是用“>=”和“<=”比较符来等价的代替BETWEEN操作符。

例如:优化器会把表达式sal BETWEEN 2000 AND 3000用sal >= 2000 AND sal <= 3000来代替。

NOT 操作符优化:

优化器总是试图简化检索条件以消除“NOT”逻辑操作符的影响,这将涉及到“NOT”操作符的消除以及代以相应的比较运算符。例如,优化器将下面的第一条语句用第二条语句代替:

 

  1. NOT deptno = (SELECT deptno FROM emp WHERE ename = 'TAYLOR')   
  2.  
  3. deptno <> (SELECT deptno FROM emp WHERE ename = 'TAYLOR')  

 

通常情况下一个含有NOT操作符的语句有很多不同的写法,优化器的转换原则是使“NOT”操作符后边的子句尽可能的简单,即使可能会使结果表达式包含了更多的“NOT”操作符。例如,优化器将如下所示的第一条语句用第二条语句代替:

 

  1. NOT (sal < 1000 OR comm IS NULL)   
  2.  
  3. NOT sal < 1000 AND comm IS NOT NULL sal >= 1000 AND comm IS NOT NULL 

 

关于SQL优化的知识就介绍到这里,谢谢各位的支持!

【编辑推荐】

  1. 提高SQL执行效率的几点建议
  2. SQL Server视图管理中的四个限制条件
  3. SQL Server数据库多种方式查找重复记录
  4. 使用SQL Trace来实现SQL Server的跟踪操作
  5. CTE和WITH AS短语结合使用提高SQL查询性能
责任编辑:赵鹏 来源: CSDN博客
相关推荐

2021-06-10 10:02:19

优化缓存性能

2017-09-20 15:07:32

数据库SQL注入技巧分享

2018-07-30 08:41:48

VueReact区别

2012-06-07 10:17:55

软件设计设计原则Java

2011-07-13 09:13:56

Android设计

2013-04-07 10:40:55

前端框架前端

2009-06-18 09:51:25

Java继承

2010-09-28 14:14:19

SQL语句

2013-08-21 13:47:29

PhoneUDIDUUID

2013-07-18 16:16:51

2009-07-01 16:20:34

Flex垃圾回收性能优化

2011-05-23 18:06:24

站内优化SEO

2012-09-25 10:03:56

JavaJava封面Java开发

2011-03-11 09:27:11

Java性能监控

2012-04-19 10:06:55

微软Windows 8 E

2009-06-04 16:28:43

EJB常见问题

2015-12-04 10:04:53

2020-09-28 06:45:42

故障复盘修复

2022-04-14 10:22:44

故事卡业务

2017-12-21 07:54:07

点赞
收藏

51CTO技术栈公众号