数据库优化

大家好！

 

今天和大家分享下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';

 

 

 

路人回答1：

1 第一点 优化器应该就是两种，CBO 和 RBO, 如果把CHOOSE也算上，那应该还有FIRST_ROWS,ALL_ROWS等 2 第五点， 第一种用distinct 的方式不一定就是低效，如果dept，emp表数据较大，可能会用merge 或hash join，比起 exists使用的类似nested loop的方法会更高效 3 第九点，“select account from account_base where account=lower('AbcDe'), 在account字段上创建索引，使用lower函数后，索引失效.” 这个能用上索引的 4 第二点， 用绑定变量需要关注绑定变量窥探问题 5 第七点， 使用索引不一定需要先导列，可以skip scan 6 第二十三点， 通过rowid删除重复行效率高没错，但给出的sql不对吧 7 第二十二， truncate可能会影响恢复，DDL语句不需要commit，所以选择时慎重 8 第二十五， 总体上意思没错，但是30%， 10%的数据比较绝对。 9 第十五点 ，基于CBO, from 后表的顺序和where 条件的顺序不用考虑 10 第十一点， OR 和 is not null 能用到索引。 11 第十九点，not in , <> 尽量避免或替换为

 

路人回答2：

1.使用绑定变量会存在绑定变量窥探的问题； 2.用rowid删除重复数据，如果表被move或者shrink了rowid就变量了。 3.不要尽量commit，这样会增加数据库的交互次数，甚至产生commit的等待事件， 4.在第15点中，cbo时代不存在这个现象 5.第25点子中，说得具体值也不是绝对的； 6.truncate和delete，要看具体的情况，truncate后表是很难恢复的，即使采用odu工具也不一定。甚至可能出现ORA-08103错误； 7.函数索引，创建后，表是无法做shrink操作的， 8.在索引字段上使用is not null是可能用上索引的，使用is null可能不能用上索引。 9.in exist等的使用是没有绝对的，需要分情况。 10.组合索引必须使用先导列是不对的，10g后是可以使用index skip scan的。 11.第二十点也不是绝对的。