数据库优化及性能.

OracleSQL性能优化准备•Oracle存储结构•●逻辑存储结构•由块,区,段,表空间等组成•块区段表空间数据库•●物理存储结构•▪控制文件•▪数据文件•▪重做日志文件等表空间•逻辑存储结构----表空间(tablespace)•表空间是最大的逻辑单位.一个数据库可以有多个表空间,一个表空间可以包含多个数据文件(一个数据文件只能属于一个表空间).任何方案对象都被存储在表空间的数据文件中,虽然不能被存储在多个表空间中,但可以被存储在多个数据文件中.表空间分系统表空间和非系统表空间两类.系统表空间包括SYSTEM表空间和SYSAUX表空间,其余的表空间就是非系统表空间.段•逻辑存储结构----段(segment)•段用于存储表空间中某一种特定的具有独立存储结构的对象的所有数据,它由一个或多个区组成.•段的几种类型:•●表段(数据段)•●索引段•●临时段•●回退段区•逻辑存储结构----区(extent)•区是由物理上连续存放的块构成的.区是Oracle存储分配的最小单位,由一个或多个块组成区,由一个或多个区组成段.当在数据库中创建带有实际存储结构的方案对象(如表,索引,簇)时,Oracle将为该方案对象分配若干个区,以便组成一个对应的段来为该方案对象提供初始的存储空间.当段中已分配的区都写满后,Oracle就为该段分配一个新的区,以便容纳更多的数据.块•逻辑存储结构----块(block)•块是最小的数据管理单位,也是执行输入输出操作时的最小单位.相对应地,操作系统执行输入输出操作的最小单位是操作系统块.•块的大小是操作系统块大小的整数倍.以Windows2000为例,操作系统块的大小是4kb,所以块的大小可以是4kb,8kb,16kb等•如果块的大小是4kb,EMP表每行的数据占100个字节.如果某个查询语句只返回1行数据,那么,在将数据读入到数据高速缓存时,读取的数据量是4kb而不是100个字节数据库性能优化的内容（1）调整数据结构的设计。（2）调整应用程序结构设计。（3）调整数据库SQL语句。（本次重点关注）（4）调整服务器内存分配。（5）调整硬盘I/O，这一步是在信息系统开发之前完成的。（6）调整操作系统参数。SQL语句优化•SQL语句优化的具体方法•(1)SQL语句的开发技巧•(2)索引的使用•(3)开发SQL中的一些建议选择最有效率的表名顺序•例如:表TAB116,384条记录表TAB21条记录选择TAB2作为基础表(最好的方法)selectcount(*)fromtab1,tab2执行时间0.96秒选择TAB2作为基础表(不佳的方法)selectcount(*)fromtab2,tab1执行时间26.09秒•原因：•ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表drivingtable)将被最先处理.在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时,会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并.WHERE子句中的连接顺序•例如:(低效,执行时间156.3秒)SELECT…FROMEMPEWHERESAL50000ANDJOB=‘MANAGER'AND25(SELECTCOUNT(*)FROMEMPWHEREMGR=E.EMPNO);(高效,执行时间10.6秒)SELECT…FROMEMPEWHERE25(SELECTCOUNT(*)FROMEMPWHEREMGR=E.EMPNO)ANDSAL50000ANDJOB=‘MANAGER';•原因•ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.减少访问数据库的次数•方法1•SELECTA.EMP_NAME,A.SALARY,A.GRADE,B.EMP_NAME,B.SALARY,B.GRADEFROMEMPA,EMPBWHEREA.EMP_NO=342ANDB.EMP_NO=291;•方法2SELECTEMP_NAME,SALARY,GRADEFROMEMPWHEREEMP_NO=342;SELECTEMP_NAME,SALARY,GRADEFROMEMPWHEREEMP_NO=291;•原因：•当执行每条SQL语句时,ORACLE在内部执行了许多工作:解析SQL语句,估算索引的利用率,绑定变量,读数据块等等.由此可见,减少访问数据库的次数,就能实际上减少ORACLE的工作量.删除•删除重复记录最高效的删除重复记录方法(因为使用了ROWID)DELETEFROMEMPEWHEREE.ROWID(SELECTMIN(X.ROWID)FROMEMPXWHEREX.EMP_NO=E.EMP_NO);用TRUNCATE替代DELETE当删除表中的记录时,在通常情况下,回滚段(rollbacksegments)用来存放可以被恢复的信息.如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况)而当运用TRUNCATE时,回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短.(译者按:TRUNCATE只在删除全表适用,TRUNCATE是DDL不是DML)•用NOTEXISTS替代NOTIN在子查询中,NOTIN子句将执行一个内部的排序和合并.无论在哪种情况下,NOTIN都是最低效的(因为它对子查询中的表执行了一个全表遍历).为了避免使用NOTIN,我们可以把它改写成外连接(OuterJoins)或NOTEXISTS.例如:SELECT…FROMEMPWHEREDEPT_NONOTIN(SELECTDEPT_NOFROMDEPTWHEREDEPT_CAT='A');为了提高效率.改写为:(方法一:高效)SELECT….FROMEMPA,DEPTBWHEREA.DEPT_NO=B.DEPT(+)ANDB.DEPT_NOISNULLANDB.DEPT_CAT(+)=‘A'(方法二:最高效)SELECT….FROMEMPEWHERENOTEXISTS(SELECT‘X'FROMDEPTDWHERED.DEPT_NO=E.DEPT_NOANDDEPT_CAT=‘A');索引1使用索引•访问Table的方式•ORACLE采用两种访问表中记录的方式:•a.全表扫描•全表扫描就是顺序地访问表中每条记录.ORACLE采用一次读入多个数据块(databaseblock)的方式优化全表扫描.••b.通过ROWID访问表•你可以采用基于ROWID的访问方式情况,提高访问表的效率,ROWID包含了表中记录的物理位置信息.ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系.通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到性能上的提高.索引的创建•Oracle服务器索引是一种模式对象。通过指针可以有效地提高检索数据的速度。索引在逻辑上和物理上是独立于创建索引的表（即索引在任何时候的创建或删除都不会影响它的基表或其他索引）。•索引的创建：•（1）自动创建：当在表中定义的primarykey或Unique约束，便自动在包含该约束的列上创建了索引，索引的名称和约束的名称相同。•（2）手动创建：索引•更多的索引意味着更高的性能吗？•过多的索引并不意味着更快的查询速度，因为每一次对含有索引的表进行DML操作就意味着索引必须重新更新，过多的索引会导致在DML操作后Oracle服务器要花费更多的时间在索引的更新上。索引•适合创建索引的情况：•1）经常用于where子句或作为连接条件的列。•2）所含数据值范围比较的列。•3）含有大量空值的列。•4）经常同时用于一个where子句或连接条件的两个或多个列。•5）绝大多数情况下只查询出其总记录的2%~4%的表建立索引语句•createindexIX_UNITTEL_ADDRESSonT_TELEPHONE(ADDRESS)Local•tablespaceICD114_MAIN_IDX•pctfree10•initrans2•maxtrans255•storage•(•initial1M•minextents1•maxextentsunlimited•);创建一个本地函数索引•createindexIX_UNIT_FUNCLASTDATESTAFFNOonT_ENTERPRISE(TO_DATE(LASTDATE,'yyyy-mm-ddhh24:mi:ss'))Local•tablespaceICD114_MAIN_IDX•pctfree10•initrans2•maxtrans255•storage•(•initial1M•minextents1•maxextentsunlimited•);•创建一个本地函数索引使用函数索引•使用函数索引•SELECTT.ID,T.UNITNAME,T.UNITCODE,T.LASTDATE,S.TELEPHONETEL,S.ADDRESSFROMV_ENTERPRISE_ONET,T_ENTERPRISE_REL_TELH,T_TELEPHONESWHERE1=1ANDH.TEL_ID=S.TEL_IDANDH.CITYCODE=S.CITYCODEANDs.citycode=T.citycodeANDH.ENTERPRISE_ID=T.IDANDH.DATAFLAG'9'ANDS.DATAFLAG'9'ANDRevNameOneLIKE'%%'ANDto_date(T.lastdate,'yyyy-mm-ddhh24:mi:ss')=to_Date('2008-11-3','yyyy-mm-ddhh24:mi:ss')ANDto_date(T.lastdate,'yyyy-mm-ddhh24:mi:ss')to_Date('2009-11-3','yyyy-mm-ddhh24:mi:ss')+1ANDT.citycode='hz'关于主键本地索引的创建•注意：对于分区表要创建本地索引。•1）主键本地索引的创建语句•altertableT_ENTERPRISE•addconstraintPK_UNITprimarykey(ENTERPRISE_ID,CITYCODE)•usingindexlocal•tablespaceICD114_MAIN_IDX•pctfree10•initrans2•maxtrans255•storage•(•initial1M•minextents1•maxextentsunlimited•);Unique约束本地索引的创建语句•Unique约束本地索引的创建语句•altertableT_TELEPHONE•addconstraintIX_UNITTEL_TELEPHONEunique(TELEPHONE,CITYCODE,AREA_CODE)•usingindexlocal•tablespaceICD114_MAIN_IDX•pctfree10•initrans2•maxtrans255•storage•(•initial1M•minextents1•maxextentsunlimited•);索引失效的情况•（2）索引不起作用的情况•①存在数据类型隐形转换•②列上有数学运算•③使用不等于（）运算•④使用substr字符串函数•⑤‘%’通配符在第一个字符•⑥字符串连接（||）查询私人电话暂停即将到期的语句•SELECTT.TELID,T.TEL•FROMt_individual_telT•whereT.CITYCOD