ITPub博客

首页 > Linux操作系统 > Linux操作系统 > SQL文的优化

SQL文的优化

原创 Linux操作系统 作者:kong_go 时间:2009-05-16 00:05:08 0 删除 编辑
一般来说,对于一个SQL文,Oracle的解析器先是进行解析验证,比如对于查询SQL文:FROM子句的表名是否正确,各个字段名分别对应哪个表,拼写及语法是否正确等等,然后确立实行计划,选择基础表(Driving Table),按照WHERE子句中的条件进行连表,最后按照实行计划进行查询。基础表是指被最先访问的表(通常以全表扫描的方式被访问)。根据优化器的不同,SQL语句中基础表的选择是不一样的。现在一般使用的都是CBO(COST BASED OPTIMIZER),Oracle优化器会检查SQL语句中的每个表的物理大小,索引的状态,然后选用花费最低的执行路径。
    当然,通常情况下SQL文的优化跟它的可读性是有矛盾的,一句优化的很好的SQL文可能它的可读性会比较差,而一句可读性好的SQL文它的效率可能并不怎么样,所以经常是需要编程人员根据实际情况,对两者作出最佳的调整,比如表的数据不是很多的时候,着重考虑它的可读性,而当表数据量非常多时,着重要考虑的是SQL文的效率。还有一点,对于有些优化规则,根据表的数据量的不同,有时候反而会降低SQL文的实行效率(下文会详细说明),也需要编程人员根据实际情况作出对应,对下文的优化规则进行灵活应用,完了还需要实际的测试验证其实行速度是否可以。在这里,对每个SQL文,先查看它的实行计划,是一种良好的习惯。现在很多Oracle的工具都提供了实行计划的查看,如Object Browser,cse等,在SQL*PLUS中,输入SET AUTOTRACE,将会得到AUTOTRACE的用法如下:
    SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]],
然后输入SET AUTOT TRACE EXP后再实行SQL文,便可以看到其实行计划了。
    为了优化SQL文以提高Oracle的实行速度,优化方法如下。 1、SQL文全部用大写,并且尽量简洁。
    Oracle对于大小写混合的SQL文,会加大SQL文实行前的验证分析时间,虽然影响甚微,但出于最优化考虑,全部用大写,即统一书写规则,又可节省开销。
    有时候对于一个复杂的SQL文,为了可读性,在编程的时候可能会加上大量的回车和空格,也会稍微的影响SQL文的処理時間,请根据实际情况跟可读性作出适当的调整。 2、使用含绑定变量(bind variables)的SQL文,利用Oracle的高速缓冲(cache buffering)功能来提高效率。
    为了不重复解析相同的SQL语句,在第一次解析之后,Oracle将SQL语句存放在内存中,这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享。因此,当执行一个SQL语句时,如果它和之前的执行过的语句完全相同,Oracle就能很快获得已经被解析的语句以及最好的执行路径。Oracle的这个高速缓冲(cache buffering)功能大大地提高了SQL的执行性能并节省了内存的使用。
    可惜的是Oracle只对简单的表提供高速缓冲,这个功能有时候并不适用于多表连接查询。DBA需要在init.ora中为这个区域设置合适的参数,当这个内存区域越大,就可以保留更多的语句,当然被共享的可能性也就越大了。
    当你向Oracle 提交一个SQL语句,Oracle会首先在这块内存中查找相同的语句。这里需要注明的是,Oracle对两者采取的是一种严格匹配。要达成共享,a.SQL语句必须完全相同(包括空格、换行等),字符大小写也需一致;b.两个语句所指的对象必须完全相同,如果同一表名对不同DB用户来说代表的不是同一张表,则不能匹配;c.在使用绑定变量的SQL文时,绑定变量名也必须完全相同。 3、减少访问数据库的次数。
    当执行每条SQL语句时,Oracle在内部执行了许多工作:解析SQL语句,估算索引的利用率,绑定变量,读数据块等等。由此可见,减少访问数据库的次数,就能实际上减少Oracle的工作量。 4、尽量多使用COMMIT。
    只要有可能,在程序中尽量多使用COMMIT,这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少。COMMIT所释放的资源:
    a. 回滚段上用于恢复数据的信息
    b. 被程序语句获得的锁
    c. redo log buffer 中的空间
    d. Oracle为管理上述3种资源中的内部花费
    但是,在使用COMMIT时必须要注意到事务的完整性,一般来说COMMIT的使用更多的是从实际的业务逻辑出发来考虑的。 5、SELECT子句中避免使用"*",只将需要的字段名进行列举。
    对于"*",Oracle在解析的过程中通过查询数据字典(user_tab_columns)将"*"转换为所有的列名,不可取。
    只将所需要的字段名进行选取,如果是统计count(*),则可用Primary Key代替。添加没有必要的字段,会加大Oracle的检索量,从而降低效率。总体来说,使用"*"所带来的影响不是很大,但从最优化角度考虑,此规则还是必须的。 6、对于所用到的表,一定要加上别名。
    使用别名可降低Oracle解析处理的开销,提高效率,同时对于可读性也是有利的。如:
    Select Employee_Id FROM employees, cct_odr         ==> 不妥
    SELECT A.EMPLOYEE_ID FROM EMPLOYEES A, CCT_ODR B         ==> 正确 7、WHERE子句中的连接顺序。
    Oracle采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾(越有效地缩小范围的条件越放后)。但是在效率与可读性之间需做出一定的调整,而且在使用HINT文(提示处理)后能主动指定需要索引的字段来调整收缩顺序,因此建议对于简单的或者数据量小的SQL文可以重点考虑其可读性,而对于复杂的或者数据量大的SQL文则考虑使用HINT文(下文将详细介绍)。
    SELECT ... FROM EMP A
    WHERE A.SAL > 50000 AND A.JOB = 'MANAGER'
    AND 25 < (SELECT COUNT(B.EMPNO) FROM EMP B WHERE A.MGR = B.EMPNO)      ==> 不妥
    SELECT ... FROM EMP A
    WHERE 25 < (SELECT COUNT(B.EMPNO) FROM EMP B WHERE A.MGR = B.EMPNO)
    AND A.SAL > 50000 AND A.JOB = 'MANAGER'    ==> 正确 8、WHERE子句中的条件的指定。
    两表连接的时候,对于收缩条件,记录数少的优先考虑。如下面两表A、B,A.X_VALUE有1万件,而B.X_VALUE有5万件,则:
    SELECT ... FROM TABLE_A A, TABLE_B B WHERE A.X_VALUE = B.X_VALUE AND B.X_VALUE = 10    ==> 不妥
    SELECT ... FROM TABLE_A A, TABLE_B B WHERE A.X_VALUE = B.X_VALUE AND A.X_VALUE = 10    ==> 正确 9、控制使用GROUP BY、ORDER BY和HAVING。
    使用GROUP BY、ORDER BY和HAVING时会发生磁盘I/O操作,不是必要的话,需谨慎使用。 10、对操作符的优化:使用DECODE函数,或者CASE来减少处理时间,避免重复扫描相同记录或重复连接相同的表。
    CASE的语法:CASE WHEN [条件式1] THEN [值1] WHEN [条件式2] THEN [值2] ... ELSE [值] END
    如:
    SELECT A.SAL*10 AS SAL FROM EMP A WHERE A.DEPT_NO = 0020;
    SELECT A.SAL*50 AS SAL FROM EMP A WHERE A.DEPT_NO = 0030;    ==> 不妥:重复扫描字段DEPT_NO,多次进出数据库
    SELECT DECODE(A.DEPT_NO, 0020, A.SAL*10, 0030 A.SAL*50, 0)AS SAL FROM EMP A WHERE SAL > 0;    ==> 正确
    或者:
    SELECT CASE WHEN A.DEPT_NO=0020 THEN A.SAL*10 WHEN A.DEPT_NO=0030 THEN A.SAL*50 ELSE 0 END AS SAL
    FROM EMP A WHERE SAL > 0;    ==> 正确 11、对操作符的优化:避免使用IN、NOT IN操作符,用多表连接或者EXISTS、NOT EXISTS来代替。
    用IN写出来的SQL的优点是比较容易写及清晰易懂,但性能总是比较低的。从Oracle执行的步骤来分析,Oracle试图将其转换成多个表的连接,如果转换不成功则先执行IN里面的子查询,再查询外层的表记录,如果转换成功则直接采用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程。一般的SQL都可以转换成功,但对于含有分组统计等方面的SQL就不能转换了。
    SELECT DEPARTMENT_ID FROM DEPARTMENTS D WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM EMPLOYEES)
         ==>不妥:IN的使用,需避免
    SELECT DEPARTMENT_ID FROM DEPARTMENTS D WHERE EXISTS (SELECT 1 FROM EMPLOYEES E WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID)
         ==>正确:用EXISTS进行了代替 12、对操作符的优化:避免使用DISTINCT操作符,有时候可用EXISTS来代替。
    如果可以的话,一定要避免DISTINCT的使用。有些DISTINCT是可以用EXISTS来进行代替的,如:
    SELECT DISTINCT A.ID1, A.NAME1 FROM TABLE1 A, TABLE2 B WHERE A.ID1 = B.ID2                         ==> 不妥
    SELECT A.ID1, A.NAME1 FROM TABLE1 A WHERE EXISTS (SELECT '1' FROM TABLE2 B WHERE A.ID1 = B.ID2)    ==> 正确 13、对操作符的优化:避免在索引列上使用<>操作符(不等于),用> or <来代替。
    不等于操作符是不能利用索引(INDEX)的,因此对它的处理只会产生全表扫描,需避免。如ID<>0 可改为 ID>0 or ID<0。 14、对操作符的优化:在索引列上使用NOT,需谨慎。
    NOT会产生在和在索引列上使用函数相同的影响,当Oracle遇到NOT时,它就会停止使用索引转而执行全表扫描。然而在某些时候,Oracle优化器会自动将NOT转化成相对应的关系操作符,如:NOT > 转化成: <=,NOT >= 转化成: <之类的,对于下面两句SQL文,其实行计划完全相同,应该是做了自动转换。所以在索引列上使用了NOT后,需验证其实行计划。如:
    SELECT A.NAME FROM EMP A WHERE NOT A.EMPNO > 1;
    SELECT A.NAME FROM EMP A WHERE A.EMPNO <= 1; 15、对操作符的优化:在索引列上使用>、<及>=、<=操作符(大于、小于及大于等于、小于等于)时,需考虑实际情况。
    对于>=,Oracle其实做的是> r =的操作,所以对于一个数值型字段VAL_NUM来说,VAL_NUM>=3的效率不如VAL_NUM>2来的好。当然也有特殊情况,比如30万记录中VAL_NUM=2的有29万,而1万记录是VAL_NUM>=3的,那么执行VAL_NUM>2时Oracle会找出=2的记录索引再进行比较,而VAL_NUM>=3时Oracle则直接找>=3的记录索引,所以这时候VAL_NUM>2的效率不如VAL_NUM>=3来的好。
    一般情况下,着重考虑的是实际情况和可读性,但是当速度差生比较大的影响时,需要考虑一下此规则的应用。 16、对操作符的优化:避免在索引列上使用IS NULL或IS NOT NULL操作(判断字段是否为空),但根据实际情况变动。
    判断字段是否为空一般是不会应用索引的,因为B树索引是不索引空值的。
    推荐方案是不允许字段为空,而用一个缺省值代替空值。然后VAL IS NOT NULL改为 VAL>0 或者 VAL>''等。也可以建立位图索引,但有分区的表不能建,而且位图索引比较难控制,如字段值太多索引会使性能下降,多人更新操作会增加数据块锁的现象。
    总之此优化规则需要考虑实际情况,来决定是否应用,如何应用。 17、对操作符的优化:LIKE操作符。
    LIKE操作符可以应用通配符(%、_)查询,其中%接受任意个数的字符,_接受一个字符。LIKE如果用得不好也会产生性能上的问题,如LIKE '%5400%'这种查询不会引用索引,而LIKE 'X5400%'则会引用范围索引。一个实际例子:VAL LIKE '%5400%'这个条件会产生全表扫描,如果VAL值第一位是'B'或者'X',则改成VAL LIKE 'B5400%' OR VAL LIKE 'X5400%'则会利用VAL的索引进行两个范围的查询,性能要好很多。而后方一致,如LIKE '%5400'也会导致全表检索。 18、对操作符的优化:对于UNION操作符,避免使用UNION,能够的话用UNION ALL。
    UNION在进行表链接后会筛选掉重复的记录,所以在表链接后会对所产生的结果集进行排序运算,删除重复的记录再返回结果。如:
    SELECT A.ID1, A.NAME1 FROM TABLE1 A
    UNION
    SELECT B.ID1, B.NAME1 FROM TABLE2 B
    这个SQL在运行时先取出两个表的结果,再用排序空间进行排序删除重复的记录,最后返回结果集,如果表数据量大的话还可能会用磁盘来进行排序。而采用UNION ALL的话,只是简单的将两个结果合并后就返回。
    SELECT A.ID1, A.NAME1 FROM TABLE1 A
    UNION ALL
    SELECT B.ID1, B.NAME1 FROM TABLE2 B
    一般来说如果对于两个不同的表的话,经常是不用考虑其结果是否重复的,即使需要考虑的话,有时候也可以通过程序中来进行控制。
    此规则请根据实际情况来进行调整。 19、使用索引。
    一般使用了索引的话,会大大的加快SQL文的实行速度,Oracle的实行计划,也会自动使用索引(能够使用的话)来加快速度。
    当然使用索引也会增大开销,一般来说是利大于弊的,特别是对于索引唯一扫描 ( INDEX UNIQUE SCAN),但是对于有些索引范围查询(INDEX RANGE SCAN)而言,则需视情况而定。这个一般是WHERE子句中指定了一部分多列索引的字段条件而导致的,这种场合下有时反而会是全表检索的效率好,需要对实际情况进行考虑,并进行两者的测试。
    比如此索引值的纪录非常多,跟全表纪录差不多,则可能是用全表检索开销会小一点,这种情况下即使Oracle自动应用了此索引,也要用HINT文,或WHERE子句中修改相应条件来强制排除此一索引。
    索引有很多种,传统的索引--B树(B-Tree)索引,位图(bitmap)索引,哈希(hash)索引,基于原始表的索引,基于函数的索引和域(Domain)索引。实际应用中主要是B-Tree索引和位图索引,所以简单介绍这两种索引类型。
    B-Tree索引是最普通的索引,缺省条件下建立的索引就是这种类型的索引,对于包含大量变化数据的列是非常有用的,主键和唯一约束就会隐式的建立这种索引;B-Tree索引可以是唯一或非唯一的,可以是单一的(基于一列)或连接的(多列)。B-Tree索引在检索高基数数据列(该列有很多不同的值)时提供了最好的性能。对于取出较少的数据B-Tree索引比全表检索提供了更有效的方法。但当检查的范围超过表的10%时就不能提高取回数据的性能。正如名字所暗示的那样,B-Tree索引是基于二元树的,由枝干块(branch block)和树叶块(leaf block)组成,枝干块包含了索引列(关键字)和另一索引的地址。树叶块包含了关键字和给表中每个匹配行的ROWID。
    位图(bitmap)索引,对于不同值很少的字段,例如区分等比较合适。主要用于决策支持系统或静态数据,不支持行级锁定。位图索引可以是简单的(单列)也可以是连接的(多列),但在实践中绝大多数是简单的。位图索引最好用于低到中群集(cardinality)列,在这些列上多位图索引可以与AND或OR操作符结合使用。位图索引使用位图作为键值,对于表中的每一数据行位图包含了TRUE(1)、FALSE(0)、或NULL值。位图索引的位图存放在B-Tree结构的页节点中。B-Tree结构使查找位图非常方便和快速。另外,位图以一种压缩格式存放,因此占用的磁盘空间比B-Tree索引要小得多。
    此外使用了函数的字段是没法应用索引的,但是可以建立基于函数的索引,则使用相应函数时,照样可以进行索引。
    还有一点,对于一个关联多列的索引,如表TABLE_A的索引DEPT_IDX由多列字段DE_NO,PT_DIV(按先后顺序)组成,则要利用此索引的话,WHERE子句中必须要包含有此索引第一个列DE_NO的条件。
    在索引带来便利的同时,也要花费一定的开销。索引需要空间来存储,也需要定期维护。每当有记录在表中增减或索引列被修改时,索引本身也会被修改。这意味着每条记录的INSERT、DELETE、UPDATE将为此多付出4、5次的磁盘I/O。因为索引需要额外的存储空间和处理,所以在大型表中正确的使用索引是特别有效的,但索引使用错误,或使用了效率不好的索引,或者对很小的表建立索引,则会适得其反。 20、索引的利用:习惯将需要利用索引(INDEX)的字段名放在WHERE子句中条件的左边,并且避免对其使用函数。
    如果对需要索引的字段使用了函数,则Oracle将无法对此字段进行索引,需要避免,用其他方式来等价替换。如:
    SUBSTR(VAL, 1, 4) = '5400'    ==> 不妥
    VAL LIKE '5400%'                  ==> 正确
    当然可以省略的函数也应该去掉,如:
    TO_NUMBER(A.ORDER_NO) = TO_NUMBER(B.ORDER_NO)         ==> 不妥
    A.ORDER_NO = B.ORDER_NO    ==> 正确 21、索引的利用:WHERE子句中条件的两边类型不一致的情况需要禁止,对类型转换函数TO_NUMBER、TO_CHAR的使用也需要注意。
    当条件两边数据类型不一致时,Oracle会自动进行类型转换,如:CHAR_VAL为VARCHAR2型
    CHAR_VAL = 1      ==> 不妥:Oracle自动进行TO_NUMBER(CHAR_VAL) = 1 和 CHAR_VAL = TO_CHAR(1)的转换,再继续操作
    CHAR_VAL = '1'    ==> 正确
    还有就是对于TO_NUMBER、TO_CHAR的使用,如果想要对CHAR_VAL进行索引,则
    TO_NUMBER(CHAR_VAL) = NUM_VAL    ==> 不妥
    CHAR_VAL = TO_CHAR(NUM_VAL)        ==> 正确 22、索引的利用:进行了显式或隐式的运算的字段不能进行索引,需要避免。
    当条件两边数据类型不一致时,Oracle会隐式的进行类型转换,如上一规则所言。再比如:
    NUM_VAL+20 > 50,          优化处理成:NUM_VAL > 30,
    NUM_VAL+20 > NUMM       优化处理成:NUM_VAL > NUMM-20,
    'X' || CHAR_VAL > 'X5400',优化处理成:CHAR_VAL > '5400' 23、索引的利用:条件内包括了多个本表的字段运算时不能进行索引,需要避免。
    如同一表的两字段:CHAR_VAL1 > CHAR_VAL2,则无法进行优化。
    再如CHAR_VAL1 || CHAR_VAL2 = '5400NAME',可以的话,优化处理成:CHAR_VAL1 = '5400' AND CHAR_VAL2 = 'NAME' 24、索引的利用:复合式的使用需要禁止。
    复合式的应用如:NUM_VAL = NVL(NUM2, NUM_VAL),NVL(NUM_VAL, 0) = ...等,使得Oracle对其实行计划无法进行最优化,结果导致对实行计划以及表的结合方法产生恶劣影响,需要禁止这种用法。如:
    NVL(NUM_VAL, 0) = 0                       ==> 错误
    NUM_VAL = 0 OR NUM_VAL IS NULL    ==> 正确 25、索引的利用:多个索引的利用情况,以及>、<、=等操作符(大于、小于、等于)与AND组合时对索引产生的影响。
    在一个SQL文中可能会分布有多个索引。Oracle选择执行路径时,唯一性索引的等级高于非唯一性索引。如果表中有两个以上(包括两个)索引,其中有一个唯一性索引,而其他是非唯一性。在这种情况下,Oracle将使用唯一性索引而完全忽略非唯一性索引。然而这个规则只有当WHERE子句中索引列和常量比较才有效。如果索引列和其他表的索引类相比较,这种子句在优化器中的等级是非常低的。如果是等式比较和范围比较的组合,两者都有索引,则只对等号的字段使用索引,即范围比较的唯一性索引,等级要比等式比较的非唯一性索引低。如:
    SELECT A.NAME FROM CUSTOMERS A
    WHERE  A.SAL > 1000         ==> INDEX不使用,即使SAL为主键也不索引此字段
    AND    A.JOB = 'MANAGER'    ==> INDEX使用
    如果不同表中两个相同等级的索引被引用,FROM子句中表的顺序将决定哪个会被率先使用。FROM子句中最后的表的索引将有最高的优先级。如果相同表中两个想同等级的索引被引用,WHERE子句中最先被引用的索引将有最高的优先级。如:
    SELECT A.NAME FROM CUSTOMERS A
    WHERE A.TAX > 100    ==> INDEX使用
    AND A.SAL > 1000       ==> INDEX不使用
    如DEPT_NO为EMP的主键,而CUST_NO为CUSTOMERS的主键,则:
    SELECT A.NAME FROM CUSTOMERS A, EMP B
    WHERE  A.CUST_NO = B.CUST_NO
    AND    A.DEPT_NO = '19'    ==> INDEX不使用
    AND    B.CUST_NO = '76'    ==> INDEX使用 26、WITH A AS()的用法。
    复杂的子查询,或者被多次引用到的子查询,可以在SQL文开始处用WITH A AS()建立一个类似临时的视图,然后在SQL文中就可以用A来引用这个查询了,增加可读性,同时在多次引用到的场合下也能提高效率。     综上所述,各种优化规则往往对应其实际情况,并且还伴随着可读性这一很重要的编程因素在里面。这里对于索引是一个很重的方面,很多的考虑都是为了使用索引,若是索引带来便利要小于其开销时,便需要反向来应用这一规则,所以需要编程人员好好的理解,并结合实际情况,灵活的应用各种优化方案,更考虑到程序的可读性,分析各方面的利弊得失,并且反复通过实行计划和实际运行的效果,最终得出一个比较理想的方案。有关HINT文方面的,请见HINT文的简介。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/13956325/viewspace-598286/,如需转载,请注明出处,否则将追究法律责任。

下一篇: HINT文的简介
请登录后发表评论 登录
全部评论

注册时间:2008-04-23

  • 博文量
    28
  • 访问量
    27821