ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 一份考验你DBA水平的sql优化文档

一份考验你DBA水平的sql优化文档

原创 Linux操作系统 作者:cnaning 时间:2013-01-31 15:20:38 0 删除 编辑
  
大家好!
 
今天和大家分享下sql优化的资料,这份资料是2年前公司的培训资料,今天拿出来和大家分享,考评!
 
今天重温这份培训资料,发现漏洞百出,这说明在这2年中我的水平是进步的(:)小夸下自己),在此考验下大家,如果能发现5处错误的地方,说明你是一名"合格"的DBA,如果你发现10处以上错误的,说明你是"优秀"的DBA。
 
具体内容如下:
Oracle优化器的选择
一、
Oracle的优化器共有3种模式:
RULE (基于规则RBO)、COST(基于成本CBO)、CHOOSE(基于选择)
 
RBO方式:优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。比如我们常见的,当一个where子句中的一列有索引时去走索引。
 
CBO方式:它是看语句的代价(Cost),这里的代价主要指Cpu和内存。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。统计信息给出表的大小、有少行、每行的长度等信息。这些统计信息起初在库内是没有的,是做analyze命令后才出现的,很多的时侯过期统计信息会令优化器做出一个错误的执行计划,因此应及时更新这些信息。
 
Choose:这个是Oracle的默认值。当一个表或索引有统计信息,则走CBO的方式,如果表或索引没有统计信息,表又不是特别的小,而且相应的列有索引时,那么就走索引,走RBO的方式.
二、
程序中尽量使用绑定变量:避免或减少硬解析
例如:
select * from t1 where t1.a=1;
select * from t1 where t1.a=2;
select * from t1 where t1.a=3;
select * from t1 where t1.a=1000000;
上面这个语句每执行一次就需要在SHARE POOL 硬解析一次,如果一百万户万并发查询,就要硬解析一百万次,消耗CPU和内存,如果业务量大,很可能导致共享池满,数据库宕机。
如果绑定变量,则只需要硬解析一次,重复调用即可。
三、
Select 中尽量避免使用 "*"
这样的全字段访问,ORACLE在解析的过程中, 会将*依次转换成所有的列名,这个工作是通过查询数据字典完成的,这意味着将耗费更多的时间。只提取你所要使用的列;使用别名能够加快解析速度.
四、
避免使用耗费资源的操作
带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的
SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能.
DISTINCT需要一次排序操作, 而其他的至少需要执行两次
排序.
例如:一个UNION查询,其中每个查询都带有GROUP BY子句
, GROUP BY会触发嵌入排序(NESTED SORT) ; 这样, 每个
查询需要执行一次排序, 然后在执行UNION时, 又一个唯一
排序(SORT UNIQUE)操作被执行,而且它只能在前面的嵌入
排序结束后才能开始执行. 嵌入的排序的深度会大大影响查
询的效率.
通常, 带有UNION, MINUS , INTERSECT的SQL语句都可以
用其他方式重写.
五、
用EXISTS替换DISTINCT
例如:
低效:
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);
六、
用UNION-ALL 替换UNION (允许的话)
UNION-ALL 不去重,效率更高;UNION先以UNION-ALL的方式被合并, 然后进行排序。
例如:
低效:
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = ’31-DEC-95’
UNION
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = ’31-DEC-95;
高效:
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = ’31-DEC-95’
UNION ALL
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = ’31-DEC-95;
七、
组合索引必须使用先导列
如果索引是建立在多个列上, 只有在它的第一个列(先导列) 被where子句引用时,优化器才会选择使用该索引.
 
例如:
create table multiindexusage ( inda number , indb number , descr varchar2(10));
create index multindex on multiindexusage(inda,indb);
select * from multiindexusage where inda = 1; --走索引
select * from multiindexusage where indb = 1; --不走索引
八、
WHERE条件中正确使用函数或者表达式
sql语句中,where条件中要正确使用函数或者表达式,不正确使用,会进行
字段类型转换,字段上索引失效
例如:
下面语句会使索引失效,并且把数据库库中所有记录的log_time时间值转换
为字符后,和条件中的字符串进行比较,走全表扫描,sql 执行效率大大降
低。
select log_time from account_login where to_char(log_time,'yyyy-mm
-dd hh24:mi:ss')='2009-04-01 00:00:00';
正确写法,使用to_date函数
select log_time from account_login where log_time=to_date('2009-0
4-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
九、
合理使用函数索引,提升查询速度
 
在项目的开发中,经常需要对外部传送如数据库中的数据,进行处理后,再将它们存放在数据库中
 
例如:
select account from account_base where account=lower('AbcDe');
在account字段上创建索引,使用lower函数后,索引失效
在account字段上创建函数索引lower(account),语句改写如下:
select account from account_base where lower(account)=lower('AbcDe');
这样查收走函数索引lower(account),会大大提升查询效率
十、
带通配符(%)的like语句
last_name字段创建索引,下面语句Oracle不使用last_name的索引。
select * from employee where last_name like '%cliton%';
在很多情况下可能无法避免这种情况,但是一定要心中有底
,通配符如此使用会降低查询速度。然而当通配符出现在字
符串其他位置时,优化器就能利用索引。在下面的查询中索
引得到了使用:
select * from employee where last_name like 'c%';
十一、
避免在索引列上使用如下操作符
 
避免在索引列上使用NOT、OR、IS NULL、IS NOT NULL、!=、||、+等这样的关系操作符或连接符;这样会产生和在索引列上不正确使用函数相同的影响,导致索引失效。
 
如果一定要对使用函数的列启用索引, 请使用ORACLE的基于函数的索引。
十二、
避免出现索引列自动转换
当比较不同数据类型的数据时, ORACLE自动对列进行简单的类型转换.
例如:EMP_TYPE是一个字符类型的索引列.
SELECT USER_NO,USER_NAME,ADDRESS
FROM USER_FILES
WHERE USER_NO = 109204421
这个语句被ORACLE转换为:
SELECT USER_NO,USER_NAME,ADDRESS
FROM USER_FILES
WHERE TO_NUMBER(USER_NO) = 109204421
因为内部发生的类型转换, 这个索引将失效
十三、
减少访问数据库的次数
 
当执行每条SQL语句时, ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等等. 由此可见, 减少访问数据库的次数 , 就能实际上减少ORACLE的工作量.
 
SQL优化的一个重要目标,减少数据库的逻辑读次数。
十四、
使用DECODE函数来减少处理时间
 
DECODE函数的语法:DECODE(value,if1,then1,if2,then2,if3,then3,...,else),表示如果value等于if1时,DECODE函数的结果返回then1,.value等于if2时,返回then2..,如果value不等于任何一个if值,则返回else值。
 
使用DECOD函数可以避免重复扫描相同记录或重复连接相同表。
 
例如:
低效:
SELECT COUNT(*),SUM(SAL)
FROM EMP
WHERE DEPT_NO = 0020
AND ENAME LIKE ‘SMITH%’;
SELECT COUNT()SUM(SAL)
十五、
ORACLE解析器的两个默认解析顺序
 
ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表)将被最先处理。 在FROM子句中包含多个表的情况下,选择记录条数最少的表作为基础表。(只在基于规则的优化器中有效)。
 ORACLE采用自下而上的顺序解析WHERE子句,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。
 
 十六、
Order by使用注意事项
 
Order by语句的非索引项或者有计算表达式都将降低查询速度。
 
Order by语句包含的字段最好创建索引,避免在order by子句中使用表达式(会使索引失效)
十七、
优化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
十八、
用Where子句替换HAVING子句
避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序
总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.
例如:
低效:
SELECT REGION,AVG(LOG_SIZE)
FROM LOCATION
GROUP BY REGION
HAVING REGION REGION != ‘SYDNEY’
AND REGION != ‘PERTH’
高效:
SELECT REGION,AVG(LOG_SIZE)
FROM LOCATION
WHERE REGION REGION != ‘SYDNEY’
AND REGION != ‘PERTH’

十九、
几个使用替代的优化写法
 
用EXISTS替代IN
 
使用NOT EXISTS替代NOT IN
 
用IN来替换OR
 
用UNION替换OR (适用于索引列)
 
用>=替代>
 
用< or >替代<>
 
NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历)
二十、
能使用外连接,就不要使用子查询
inner join 内连接和where相同
left join 左向外连接,返回左边表所有符合条件的
例如:select * from a, b where a.id=b.id(+);
right join 右向外连接,返回右边表所有符合条件的
例如: select * from a, b where a.id(+)=b.id;
full join完整外部连接,左向外连接和右向外连接的合集
交叉连接,也称笛卡儿积,返回左表中的每一行与右表中所有行的组合cross join
二十一、
尽量多使用COMMIT
 
COMMIT所释放的资源:
回滚段上用于恢复数据的信息
被程序语句获得的锁
redo log buffer 中的空间
ORACLE为管理上述3种资源中的内部花费
 
实际应用中需要注意:
循环批量插入大量数据,合理的做法是:设置一个COMMIT频度,比如插入
1000条记录后COMMIT一次,这样不会因为频繁COMMIT浪费数据库资源;也不会最后一次COMMIT,如果插入失败,全部回滚。
二十二、
用TRUNCATE替代DELETE
 
如果是全表删除,用TRUNCATE替代DELETE,执行时间大大提高,并且降低高水位线,释放不用的数据块空间,以便数据库重复利用数据块空间。
二十三、
使用rowid来写去重sql是效率最高的
表数据去重sql写法有很多,使用rowid来写sql是效率最高的,rowid是一个伪
列,通过ROWID可以直接定位到存放相应记录的数据块,然后将其读到内存
,以最快的速度找到要去重的记录。
例如:
SQL>DELETE FROM CONFIG_ACTIVE_CODE A
WHERE ROWID > (
SELECT min(rowid) FROM CONFIG_ACTIVE_CODE B
WHERE A.ACTIVE_CODE= B.ACTIVE_CODE);
二十四、
合理使用表的别名(Alias)
 
当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上。这样一来,就能够减少解析的时间并减少那些由Column歧义引起的语法错误。
二十五、
合理使用索引
 
检索数据记录条数在表数据总量30%以上,使用索引将不会有显著的效率提高。检索数据记录条数在表数据总量10%以内,通过索引会大大提高数据查询速度。
 索引通常会使SELECT查询变快,但会使INSERT,UPDATE这样的DML变慢(因为要写索引数据,有I/O);要合理使用索引。
 
 二十六、
养成查看执行计划的好习惯
为了执行语句,Oracle必须实现许多步骤,这些步骤中的每一步可能是从数
据库中物理检索数据行,或者用某种方法准备数据行,供发出语句的用户使
用。Oracle用来执行语句的这些步骤的组合被称之为执行计划。只有知道了
ORACLE在内部到底是如何执行该SQL语句后,才能知道优化器选择的执行
计划是否为最优的。
查看执行计划命令: set autot trace exp;
开发人员可以使用TOAD, PL/SQL developer工具查看SQL自己计划,调
优自己写的SQL语句。

二十七、
合理使用Hint提示,稳定执行计划
 
SQL语句中可以通过提示的方式,指定想要数据库走哪种执行计划,称为hint
例如:
表明对表选择全局扫描的方法.
SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='CCBZZP';
表明对表选择索引升序的扫描方法.
SELECT /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='CCBZZP';

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

请登录后发表评论 登录
全部评论

注册时间:2010-12-12

  • 博文量
    36
  • 访问量
    217258