数据库优化及性能课件.ppt

《数据库优化及性能课件.ppt》由会员分享，可在线阅读，更多相关《数据库优化及性能课件.ppt（36页珍藏版）》请在三一办公上搜索。

1、Oracle SQL性能优化,准备,Oracle存储结构 逻辑存储结构由块,区,段,表空间等组成 块区段表空间数据库 物理存储结构 控制文件 数据文件 重做日志文件等,表空间,逻辑存储结构-表空间(tablespace)表空间是最大的逻辑单位.一个数据库可以有多个表空间,一个表空间可以包含多个数据文件(一个数据文件只能属于一个表空间).任何方案对象都被存储在表空间的数据文件中,虽然不能被存储在多个表空间中,但可以被存储在多个数据文件中.表空间分系统表空间和非系统表空间两类.系统表空间包括SYSTEM表空间和SYSAUX表空间,其余的表空间就是非系统表空间.,段,逻辑存储结构-段(segment

2、)段用于存储表空间中某一种特定的具有独立存储结构的对象的所有数据,它由一个或多个区组成.段的几种类型:表段(数据段)索引段临时段回退段,区,逻辑存储结构-区(extent)区是由物理上连续存放的块构成的.区是Oracle存储分配的最小单位,由一个或多个块组成区,由一个或多个区组成段.当在数据库中创建带有实际存储结构的方案对象(如表,索引,簇)时,Oracle将为该方案对象分配若干个区,以便组成一个对应的段来为该方案对象提供初始的存储空间.当段中已分配的区都写满后,Oracle就为该段分配一个新的区,以便容纳更多的数据.,块,逻辑存储结构-块(block)块是最小的数据管理单位,也是执行输入输出

3、操作时的最小单位.相对应地,操作系统执行输入输出操作的最小单位是操作系统块.块的大小是操作系统块大小的整数倍.以Windows 2000为例,操作系统块的大小是4kb,所以块的大小可以是4kb,8kb,16kb等如果块的大小是4kb,EMP表每行的数据占100个字节.如果某个查询语句只返回1行数据,那么,在将数据读入到数据高速缓存时,读取的数据量是4kb而不是100个字节,数据库性能优化的内容,（1）调整数据结构的设计。（2）调整应用程序结构设计。（3）调整数据库SQL语句。（本次重点关注）（4）调整服务器内存分配。（5）调整硬盘I/O，这一步是在信息系统开发之前完成的。（6）调整操作系统参数

4、。,SQL语句优化,SQL语句优化的具体方法(1)SQL语句的开发技巧(2)索引的使用(3)开发SQL中的一些建议,选择最有效率的表名顺序,例如:表 TAB1 16,384 条记录 表 TAB2 1 条记录 选择TAB2作为基础表(最好的方法)select count(*)from tab1,tab2 执行时间0.96秒 选择TAB2作为基础表(不佳的方法)select count(*)from tab2,tab1 执行时间26.09秒 原因：ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理.在FR

5、OM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时,会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并.,WHERE子句中的连接顺序,例如:(低效,执行时间156.3秒)SELECT FROM EMP E WHERE SAL 50000 AND JOB=MANAGER AND 25 50000 AND JOB=MANAGER;原因ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表

6、之间的连接必须写在其他WHERE条件之前,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.,减少访问数据库的次数,方法1 SELECT A.EMP_NAME,A.SALARY,A.GRADE,B.EMP_NAME,B.SALARY,B.GRADE FROM EMP A,EMP B WHERE A.EMP_NO=342 AND B.EMP_NO=291;方法2SELECT EMP_NAME,SALARY,GRADE FROM EMP WHERE EMP_NO=342;SELECT EMP_NAME,SALARY,GRADE FROM EMP WHERE EMP_NO=291;原因：

7、当执行每条SQL语句时,ORACLE在内部执行了许多工作:解析SQL语句,估算索引的利用率,绑定变量,读数据块等等.由此可见,减少访问数据库的次数,就能实际上减少ORACLE的工作量.,删除,删除重复记录 最高效的删除重复记录方法(因为使用了ROWID)DELETE FROM EMP E WHERE E.ROWID(SELECT MIN(X.ROWID)FROM EMP X WHERE X.EMP_NO=E.EMP_NO);用TRUNCATE替代DELETE 当删除表中的记录时,在通常情况下,回滚段(rollback segments)用来存放可以被恢复的信息.如果你没有COMMIT事务,OR

8、ACLE会将数据恢复到删除之前的状态(准确地说是 恢复到执行删除命令之前的状况)而当运用TRUNCATE时,回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短.(译者按:TRUNCATE只在删除全表适用,TRUNCATE是DDL不是DML),用NOT EXISTS替代NOT IN 在子查询中,NOT IN子句将执行一个内部的排序和合并.无论在哪种情况下,NOT IN都是最低效的(因为它对子查询中的表执行了一个全表遍历).为了避免使用NOT IN,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.例如:SELECT FR

9、OM EMP WHERE DEPT_NO NOT IN(SELECT DEPT_NO FROM DEPT WHERE DEPT_CAT=A);为了提高效率.改写为:(方法一:高效)SELECT.FROM EMP A,DEPT B WHERE A.DEPT_NO=B.DEPT(+)AND B.DEPT_NO IS NULL AND B.DEPT_CAT(+)=A(方法二:最高效)SELECT.FROM EMP E WHERE NOT EXISTS(SELECT X FROM DEPT D WHERE D.DEPT_NO=E.DEPT_NO AND DEPT_CAT=A);,索引,1 使用索引访问

10、Table的方式 ORACLE 采用两种访问表中记录的方式:a.全表扫描 全表扫描就是顺序地访问表中每条记录.ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描.b.通过ROWID访问表 你可以采用基于ROWID的访问方式情况,提高访问表的效率,ROWID包含了表中记录的物理位置信息.ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系.通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到性能上的提高.,索引的创建,Oracle服务器索引是一种模式对象。通过指针可以有效地提高检索数据的速度。索引在逻辑上

11、和物理上是独立于创建索引的表（即索引在任何时候的创建或删除都不会影响它的基表或其他索引）。索引的创建：（1）自动创建：当在表中定义的primary key 或 Unique约束，便自动在包含该约束的列上创建了索引，索引的名称和约束的名称相同。（2）手动创建：,索引,更多的索引意味着更高的性能吗？过多的索引并不意味着更快的查询速度，因为每一次对含有索引的表进行DML操作就意味着索引必须重新更新，过多的索引会导致在DML操作后Oracle服务器要花费更多的时间在索引的更新上。,索引,适合创建索引的情况：1）经常用于where子句或作为连接条件的列。2）所含数据值范围比较的列。3）含有大量空值的列。

12、4）经常同时用于一个where子句或连接条件的两个或多个列。5）绝大多数情况下只查询出其总记录的2%4%的表,建立索引语句,create index IX_UNITTEL_ADDRESS on T_TELEPHONE(ADDRESS)Local tablespace ICD114_MAIN_IDX pctfree 10 initrans 2 maxtrans 255 storage(initial 1M minextents 1 maxextents unlimited);,创建一个本地函数索引,create index IX_UNIT_FUNCLASTDATESTAFFNO on T_ENT

13、ERPRISE(TO_DATE(LASTDATE,yyyy-mm-dd hh24:mi:ss)Localtablespace ICD114_MAIN_IDX pctfree 10 initrans 2 maxtrans 255 storage(initial 1M minextents 1 maxextents unlimited);创建一个本地函数索引,使用函数索引,使用函数索引SELECT T.ID,T.UNITNAME,T.UNITCODE,T.LASTDATE,S.TELEPHONE TEL,S.ADDRESSFROM V_ENTERPRISE_ONE T,T_ENTERPRISE_R

14、EL_TEL H,T_TELEPHONE SWHERE 1=1AND H.TEL_ID=S.TEL_IDAND H.CITYCODE=S.CITYCODEAND s.citycode=T.citycodeAND H.ENTERPRISE_ID=T.IDAND H.DATAFLAG 9 AND S.DATAFLAG 9AND RevNameOne LIKE%AND to_date(T.lastdate,yyyy-mm-dd hh24:mi:ss)=to_Date(2008-11-3,yyyy-mm-dd hh24:mi:ss)AND to_date(T.lastdate,yyyy-mm-dd h

15、h24:mi:ss)to_Date(2009-11-3,yyyy-mm-dd hh24:mi:ss)+1AND T.citycode=hz,关于主键本地索引的创建,注意：对于分区表要创建本地索引。1）主键本地索引的创建语句alter table T_ENTERPRISE add constraint PK_UNIT primary key(ENTERPRISE_ID,CITYCODE)using index local tablespace ICD114_MAIN_IDX pctfree 10 initrans 2 maxtrans 255 storage(initial 1M minexte

16、nts 1 maxextents unlimited);,Unique约束本地索引的创建语句,Unique约束本地索引的创建语句alter table T_TELEPHONE add constraint IX_UNITTEL_TELEPHONE unique(TELEPHONE,CITYCODE,AREA_CODE)using index local tablespace ICD114_MAIN_IDX pctfree 10 initrans 2 maxtrans 255 storage(initial 1M minextents 1 maxextents unlimited);,索引失效的

17、情况,（2）索引不起作用的情况存在数据类型隐形转换列上有数学运算使用不等于（）运算使用substr字符串函数%通配符在第一个字符字符串连接（|）,查询私人电话暂停即将到期的语句,SELECT T.TELID,T.TELFROM t_individual_tel T where T.CITYCODE=HZ And T.flag!=1 AND T.Status=1 AND to_date(T.PAUSEDATE,yyyy-MM-dd HH24:mi:ss)=SYSDATE-(to_number(10)-to_number(T.PAUSEDATENUM)And to_date(T.PAUSEDATE

18、,yyyy-MM-dd HH24:mi:ss)=SYSDATE+(to_number(3)-to_number(T.PAUSEDATENUM),原因,错误1：存在数据类型隐形转换，使用不等于（）运算Flag字段是NUMBER类型。T.flag!=1 错误2：Status字段当时的数据区分度不高。错误3：列上有数学运算错误4：PAUSEDATE建立的函数索引使用不上。,优化后的语句,SELECT T.TELID,T.TEL FROM T_INDIVIDUAL_TEL T WHERE T.CITYCODE=HZ AND T.FLAG!=1 AND T.STATUS|=1 AND TO_DATE(P

19、AUSEDATE,yyyy-mm-dd hh24:mi:ss)-TO_NUMBER(T.PAUSEDATENUM)=SYSDATE-TO_NUMBER(10)AND TO_DATE(PAUSEDATE,yyyy-mm-dd hh24:mi:ss)=SYSDATE+(TO_NUMBER(3)-TO_NUMBER(T.PAUSEDATENUM),用UNION替换OR,用UNION替换OR(适用于索引列)通常情况下,用UNION替换WHERE子句中的OR将会起到较好的效果.对索引列使用OR将造成全表扫描.注意,以上规则只针对多个索引列有效.如果有column没有被索引,查询效率可能会因为你没有选择O

20、R而降低.在下面的例子中,LOC_ID 和REGION上都建有索引.高效:SELECT LOC_ID,LOC_DESC,REGION FROM LOCATION WHERE LOC_ID=10 UNION SELECT LOC_ID,LOC_DESC,REGION FROM LOCATION WHERE REGION=“MELBOURNE”低效:SELECT LOC_ID,LOC_DESC,REGION FROM LOCATION WHERE LOC_ID=10 OR REGION=“MELBOURNE”如果你坚持要用OR,那就需要返回记录最少的索引列写在最前面.,建议,建议：在开发过程中，在

21、SQL语句中，尽量不要指定索引。说明：指定索引后，在以后的优化过程中，无法通过创建和优化索引，使对应的SQL语句用到其他更好的索引，对指定的索引删除、重建改名后，可能导致该SQL语句用不到索引。,建议,建议：在不使用DISTINCT、UNION、ORDER BY、GROUP BY情况下，也能实现业务功能的情况，一定不要使用这些功能。使用这些功能会导致对应的SQL语句排序，增加系统的开销。示例：错误的用法：SELECT COUNT(*)FROM(SELECT SERIALNO FROM T_PUB_COMMONINFO WHERE A.PARTID=0127 AND A.PARTID=1227

22、ORDER BY ACCEPTBEGINTIME DESC-没有用的ORDER BY),建议,建议：创建组合索引时，要注意组合索引的顺序和字段的选择性，把经常出现在WHERE条件中同时选择性比较好的字段放在复合索引的第一个位置。说明：SQL语句在使用复合索引时，与该复合索引字段的组合顺序有关，当索引的第一个字段出现在WHERE条件中，这时候对该表的查询能够用到该索引，当该索引的第一个字段没有出现在SQL语句的查询条件中，但该索引的其他的字段出现在WHERE条件中时，在基于规则的优化模式中，SQL引擎会用全表扫描方式查询，在基于成本的优化模式中的（First Rows）方式查询，SQL引擎会对该

23、索引全索引扫描方式查询。示例：错误的用法：ALTER TABLE T_WF_SERVICEINFOHIS ADD CONSTRAINT PK_WF_SERVICEINFOHIS PRIMARY KEY(MONTHDAY,SERIALNO,SERVICECLASSID);说明：在业务逻辑的查询中，许多时候之间按SERIALNO,SERVICECLASSID查时，在基于规则的优化模式中，用不到索引。正确的用法：ALTER TABLE T_WF_SERVICEINFOHIS ADD CONSTRAINT PK_WF_SERVICEINFOHIS PRIMARY KEY(SERIALNO,SERVIC

24、ECLASSID,MONTHDAY);,建议,建议：创建表时数据和索引建议在不同的表空间。,建议,建议：在WHERE条件表达式中，尽可能避免在要使用到索引的字段上使用函数，如果要使用函数建议创建相应的函数索引。示例：错误用法：SELECT FIELD FROM TABLENAME WHERE SUBSTRB(FIELD,1,4)=5378正确用法：SELECT FIELD FROM TABLENAME WHERE FIELD LIKE 5378%,建议,建议：当查询条件选择性很低时使用索引反而降低效率，这种情况下，应该用特殊的方法屏蔽该索引，如果字段为数值型的就在表达式的字段名后+0，为字符型

25、的就并上空串。示例：SELECT NUM_FIELD FROM TABLENAME WHERE NUM_FIELD+0 30SELECT STRING_FIELD FROM TABLENAME WHERE STRING_FIELD|=EXAMPLE,建议,建议：创建分区表的索引时，必须创建本地（LOCAL）索引。说明：如果创建的是全局索引，在对分区表的某个分区TRUNCATE时，导致该索引失效；需要对该全局索引进行重新编译,建议,建议：避免通过DUAL表赋值。说明：过多的对DUAL表的访问，导致调用该表的等待时间事件比较长。比如取系统时间之类的操作，往一个表插入记录等。示例：错误的用法：SELECT SYSDATE INTO v_Date FROM DUAL正确的用法：v_Date:=SYSDATE 错误的用法：INSERT INTO TABLENAME(FIELD1,FIELD2,FIELD3)SELECT 2,SYSDATE,SUSBTR(v_Name,1,30)FROM DUAL;正确的用法：INSERT INTO TABLENAME(FIELD1,FIELD2,FIELD3)VALUES(2,SYSDATE,SUSBTR(v_Name,1,30);,结束,感谢各位同事百忙中抽出时间来学习ORACLE 性能优化，希望在以后开发的过程中能帮到大家。,