开发高性能的数据库SQL.doc

1 SQL执行顺序1.1 SELECT语句一般select 语句处理按以下步骤顺序执行：1） 建立游标。2） 分析语句。3） 定义输出：指定位置，类型，结果集的数据类型。4） 绑定变量：如果查询使用变量的话，Oracle就要知道变量的值。5） 是否能并行运行（如果有多个服务器时）。6） 执行查询。7） 检索出数据。8） 关闭游标。1.2 DML语句一般INSERT,UPADTE,DELETE 语句处理执行顺序是：1) 建立游标（Oracle建立一个隐含的游标。）2) 分析语句。3) 绑定变量：如果语句用了变量，Oracle要知道变量的值。4) 看语句是否能以并行方式运行（如果有多个服务器时）。5) 执行语句。6) 通知用户，语句已执行完毕。7) 关闭游标。1.3 COMMIT语句当事务提交时，Oracle分配一个唯一的顺序号SCN(System Change Number)给事务。数据库恢复总是基于该SCN号来进行处理。SCN号是记录在控制文件、数据文件、块头及重做日志文件中。Oracle 在下面情况提交事务：发出一个COMMIT语句、执行DDL语句时、离开Oracle时。Oracle处理COMMIT的顺序：1） 服务器为每个COMMIT产生一个SCN。使改变永久化。2） LGWR进程将日志缓冲区数据并带有SCN一起写到重做日志文件。3） 服务器释放表级和行级锁。4） 用户被提示COMMIT完成。5） 服务器使事务已完成。1.4 ROLLBACK语句当下面情况发生时，数据库执行回滚：发出ROLLBACK命令、服务器进程放弃地终止、会话被DBA终止。ORACLE处理ROLLBACK的顺序：1） 服务器进程不做任何的改变。2） 服务器释放表级和行级锁。3） 服务器使事务已完成。2 访问表中记录2.1 访问表中记录的方式ORACLE只有两种访问表中记录的方式：a.全表扫描全表扫描就是顺序地访问表中每条记录。 ORACLE采用一次读入多个数据块（database block）的方式优化全表扫描。b.通过ROWID访问表你可以采用基于ROWID的访问方式情况，提高访问表的效率， ROWID包含了表中记录的物理位置信息，ORACLE采用索引（INDEX）实现了数据和存放数据的物理位置（ROWID）之间的联系。 通常索引提供了快速访问ROWID的方法，因此那些基于索引列的查询就可以得到性能上的提高。2.2 SQL对表与索引的可能操作情况ORACLE的执行一个SQL对表与索引的可能操作情况有三种：A 只读表该情况只出现在全表扫描的情况。B 只读索引该情况只出现在全索引扫描的情况。Oracle对全索引扫描限制为SQL请求的全部列（column）必须驻留在索引树中；也就是说，SELECT和WHERE字句中的所有数据列必须存在于索引中C 读取索引和表最普遍的查询方式，数据库先根据索引查询到满足要求的rowid,再根据rowid查询表。2.3 全表扫描与高水位线做全表扫描的时候，影响性能最大的不是表内记录数的多少，也不是该表所分配的空间，而是这个表的高水位线下的空间的大小。比如说表A,在创建的时候分配了100M空间，然后在表A内插入数据的时候，表A的高水位线下的空间会逐渐加大；而删除表内的数据的时候，表的高水位线并不会变化，也就是说高水位线下的空间不会减少。随着高水位线的不断上移，以上查询的效率就会越来越低。2.3.1 解除高水位线过高的处理移动表所在的表空间，然后再移回原来的表空间Alter table table_name move tablespace tablespace_name;缺点：需要足够的空间，并且需要另外一个足够空间的表空间，移动表后需重建该表的索引，需要放在系统空闲时做，会影响业务对该表的使用。Exp/imp将该表export后再import缺点：import前需要将该表drop，需要中断业务，有该表参与的过程，函数等需要进行重编译。将原表改名后重新建立新表再用insert into select 方式转移数据缺点：需要中断业务，需要足够的空间，比较移动表空间的方式可以在同一表空间上进行处理；改名时还需要对索引，约束，触发器等进行处理（影响建立新表）通过create table a as select * from b建立表a保存数据，将表b truncate后将保存的数据用insert into .select 恢复。缺点：需要中断业务，需要足够的空间，存在外键时需要先drop外键2.4 表与索引相关的系统对象dba_tables;dba_tab_cols;dba_tab_columns;dba_tab_comments;dba_indexes;dba_ind_columns;dba_segments;user_tables;user_tab_cols;user_tab_columns;user_tab_comments;user_indexes;user_ind_columns;user_segments;3 索引的组织结构3.1 基本的多叉树索引NULL-B1MOVE-B2Null-L1Bolan-L2Floyd-L3Move-L4Queen-L5Ziggy-L6ABBA rowidAcdc rowidBolan rowidCed rowidDfg rowidFloyd rowidGed rowidHfg rowidMove rowidOrg rowidPxc rowidQueen rowidRac rowidRdg rowidZiggy rowidZxc rowid第一层为根节点，第二层为分叉节点，第三层为叶节点3.2 索引的结构的变动3.2.1 INSERT引起的索引结构变动在INSERT数据时，索引的结构增加，索引占用的空间规模、扫描需要花费的时间规模、插入操作需要花费的时间规模增加。问题：随着数据的增加，插入数据时对索引的变动花费的时间增加。3.2.2 DELETE引起的索引结构变动在delete数据时，在索引中仅置一标志，表明该数据被删除而不影响索引的基本结构。3.2.3 重建索引ALTER INDEX REBUILD ;4 ORACLE优化器与执行计划4.1 Oracle优化器ORACLE的优化器共有3种： RULE （基于规则） COST （基于成本） CHOOSE （选择性） 设置缺省的优化器，可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明，如RULE，COST，CHOOSE，ALL_ROWS，FIRST_ROWS . 你当然也在SQL句级或是会话（session）级对其进行覆盖。 为了使用基于成本的优化器（CBO， Cost-Based Optimizer） ， 你必须经常运行analyze 命令，以增加数据库中的对象统计信息（object statistics）的准确性。 如果数据库的优化器模式设置为选择性（CHOOSE），那么实际的优化器模式将和是否运行过analyze命令有关。 如果table已经被analyze过， 优化器模式将自动成为CBO ， 反之，数据库将采用RULE形式的优化器。在缺省情况下，ORACLE采用CHOOSE优化器，为了避免那些不必要的全表扫描（full table scan） ， 你必须尽量避免使用CHOOSE优化器，而直接采用基于规则或者基于成本的优化器。4.2 获取执行计划4.2.1 Plsql中获取执行计划在sql窗口中选中sql，按F5键即可获取该sql的执行计划。4.2.2 Sqlplus中获取执行计划D:oracleora92sqlplus /nologSQL*Plus: Release 9.2.0.1.0 - Production on 星期二 6月 3 15:16:03 2003Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.SQL connect sys as sysdba请输入口令:已连接。SQL ?rdbmsadminutlxplan表已创建。SQL create public synonym plan_table for plan_table;同义词已创建。SQL grant all on plan_table to public ;授权成功。SQL ?sqlplusadminplustrceSQLSQL drop role plustrace;drop role plustrace*ERROR 位于第 1 行:ORA-01919: 角色PLUSTRACE不存在SQL create role plustrace;角色已创建SQLSQL grant select on v_$sesstat to plustrace;授权成功。SQL grant select on v_$statname to plustrace;授权成功。SQL grant select on v_$session to plustrace;授权成功。SQL grant plustrace to dba with admin option;授权成功。SQLSQL set echo off DBA用户首先被授予了plustrace角色,然后我们可以把plustrace授予public这样所有用户都将拥有plustrace角色的权限.SQL grant plustrace to public ;授权成功。SQL set autotrace on;SQL select count(*) from t_cell; COUNT(*)- 5384Execution Plan- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (FULL) OF T_CELLStatistics- 0 recursive calls 0 db block gets 27 consistent gets 0 physical reads 0 redo size 380 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processedSQL4.3 分析表和索引以选择正确的执行计划analyze table tablename compute statistics;analyze table abc compute statistics sample 20 percent;analyze index|cluster indexname estimate statistics;analyze table tablename compute statisticsfor tablefor all local indexesfor all indexed columns;analyze table tablename delete statistics;analyze table tablename validate ref update;analyze table tablename validate structure;4.4 系统忙紧急处理方式1 通过top类命令获取cpu使用率高的进程ID（pid）2 根据PID查询该进程在执行的sql语句：select sql_text from v$sql where address = (select sql_address from v$session where paddr =(select addr from v$process where spid= ?);3 分析该sql语句的执行计划。4 优化该sql语句的执行计划。4.5 通过statspack获取性能差的sql4.5.1 系统参数 为了能够顺利安装和运行Statspack你可能需要设置以下系统参数： 1. job_queue_processes 为了能够建立自动任务，执行数据收集，该参数需要大于0。你可以在初试化参数文件中修改该参数。 2. timed_statistics收集操作系统的计时信息，这些信息可被用来显示时间等统计信息、优化数据库和 SQL 语句。要防止因从操作系统请求时间而引起的开销，请将该值设置为False。 使用statspack收集统计信息时建议将该值设置为 TRUE，否则收集的统计信息大约只能起到10%的作用，将timed_statistics设置为True所带来的性能影响与好处相比是微不足道的。 该参数使收集的时间信息存储在在V$SESSTATS 和V$SYSSTATS 动态性能视图中。Timed_statistics参数可以在实例级进行更改 SQL alter system set timed_statistics = true; System altered SQL4.5.2 .安装Statspack 安装Statspack需要用internal身份登陆，或者拥有SYSDBA(connect / as sysdba)权限的用户登陆。需要在本地安装或者通过telnet登陆到服务器。 在Oracle8.1.6版本中运行statscre.sql;在Oracle8.1.7及以上版本中运行spcreate.sql。 首先登陆到数据库，最好转到$ORACLE_HOME/RDBMS/ADMIN目录，这样我们执行脚本就可以方便些。Sqlspcreate.sql4.5.3 测试安装好的Statspack 运行statspack.snap可以产生系统快照，运行两次，然后执行spreport.sql就可以生成一个基于两个时间点的报告。 如果一切正常，说明安装成功。SQLexecute statspack.snap PL/SQL procedure successfully completed. SQLexecute statspack.snap PL/SQL procedure successfully completed. SQLspreport.sql4.5.4 生成分析报告 调用spreport.sql可以生成分析报告：SQL spreport4.5.5 删除历史数据 删除stats$snapshot数据表中的相应数据，其他表中的数据会相应的级连删除： SQL select max(snap_id) from stats$snapshot; MAX(SNAP_ID) - 166 SQL delete from stats$snapshot where snap_id execute statspack.snap(i_snap_level=0,i_modify_parameter=true); 通过这样的设置，以后的收集级别都将是0级。 如果你只是想本次改变收集级别，可以忽略i_modify_parameter参数。 SQLexecute statspack.snap(i_snap_level=10); 4.5.6.2 快照门限 快照门限只应用于stats$sql_summary表中获取的SQL语句。 因为每一个快照都会收集很多数据，每一行都代表获取快照时数据库中的一个SQL语句，所以stats$sql_summary很快就会成为Statspack中最大的表。 门限存储在stats$statspack_parameter表中。让我们了结一下各种门限： a. executions_th 这是SQL语句执行的数量(默认值是100) b. disk_reads_tn 这是SQL语句执行的磁盘读入数量（默认值是1000） c. parse_calls_th 这是SQL语句执行的解析调用的数量（默认值是1000） d. buffer_gets_th 这是SQL语句执行的缓冲区获取的数量（默认值是10000） 任何一个门限值超过以上参数就会产生一条记录。 通过调用statspack.modify_statspack_parameter函数我们可以改变门限的默认值。 例如： SQLexecute statspack.modify_statspack_parameter(i_buffer_gets_th=100000,i_disk_reads_th=100000;4.6 简明低性能sql获取select * from (select sql_text,disk_reads,executions from v$sql order by disk_reads desc) where rownum (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);5.8 用TRUNCATE替代DELETE当删除表中的全部记录时，在通常情况下， 回滚段（rollback segments ） 用来存放可以被恢复的信息。 如果你没有COMMIT事务，ORACLE会将数据恢复到删除之前的状态（准确地说是恢复到执行删除命令之前的状况） 而当运用TRUNCATE时， 回滚段不再存放任何可被恢复的信息。当命令运行后，数据不能被恢复。因此很少的资源被调用，执行时间也会很短。 注意：TRUNCATE只在删除全表适用，TRUNCATE是DDL不是DML5.9 用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 GROUP BY REGION注意：HAVING 中的条件一般用于对一些集合函数的比较，如COUNT（） 等等。 除此而外，一般的条件应该写在WHERE子句中。5.10 减少对表的查询在含有子查询的SQL语句中，要特别注意减少对表的查询。 例如：低效 SELECT TAB_NAME FROM TABLES WHERE TAB_NAME = ( SELECT TAB_NAME FROM TAB_COLUMNS WHERE VERSION = 604) ANDDB_VER= ( SELECT DB_VER FROM TAB_COLUMNS WHERE VERSION = 604) 高效 SELECT TAB_NAME FROM TABLES WHERE(TAB_NAME,DB_VER)= ( SELECT TAB_NAME,DB_VER) FROM TAB_COLUMNS WHERE VERSION = 604) Update 多个Column 例子：低效： UPDATE EMP SET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES), SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES) WHERE EMP_DEPT = 0020;高效： UPDATE EMP SET (EMP_CAT, SAL_RANGE) = (SELECT MAX(CATEGORY) , MAX(SAL_RANGE)FROM EMP_CATEGORIES) WHERE EMP_DEPT = 0020;5.11 利用with减少表的查询insert into mct_balance_bill_cp(company_id,company_name,provincename,balance_MONTH,busi_code,busi_count,busi_fee,busi_percent) with a as (select aaa.mobileno,bbb.movieprovider,aaa.filmid,count(*) filmcount from mct_film_oper aaa,mct_movie bbb where aaa.filmid = bbb.filmid and aaa.servicecode = -YDDM and aaa.opertime = add_months(trunc(sysdate,mm),-1) and aaa.opertime 0AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC = MELB) 高效：SELECT * FROM EMP (基础表)WHERE EMPNO 0AND EXISTS (SELECT X FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNOAND LOC = MELB)5.14 用NOT EXISTS替代NOT IN在子查询中，NOT IN子句将执行一个内部的排序和合并。 无论在哪种情况下，NOT IN都是最低效的 （因为它对子查询中的表执行了一个全表遍历）。 为了避免使用NOT IN ，我们可以把它改写成外连接（Outer Joins）或NOT EXISTS.例如：SELECT FROM EMPWHERE DEPT_NO NOT IN (SELECT DEPT_NO FROM DEPT WHERE DEPT_CAT=A);为了提高效率。改写为：（方法一： 高效）SELECT .FROM EMP A,DEPT BWHERE A.DEPT_NO = B.DEPT(+)AND B.DEPT_NO IS NULLAND B.DEPT_CAT(+) = A（方法二： 最高效）SELECT .FROM EMP EWHERE NOT EXISTS (SELECT X FROM DEPT D WHERE D.DEPT_NO = E.DEPT_NO AND DEPT_CAT = A);5.15 用表连接替换EXISTS通常来说 ， 采用表连接的方式比EXISTS更有效率 SELECT ENAME FROM EMP E WHERE EXISTS (SELECT X FROM DEPT WHERE DEPT_NO = E.DEPT_NO AND DEPT_CAT = A);（更高效） SELECT ENAME FROM DEPT D,EMP E WHERE E.DEPT_NO = D.DEPT_NO AND DEPT_CAT = A ;5.16 强制索引失效 在特定的时候，我们可以通过强制索引失效让数据库选择正确的索引或不使用索引来优化sql。 举例：SELECT ENAMEFROM EMP WHERE EMPNO = 7935 AND DEPTNO + 0 = 10 /*DEPTNO上的索引将失效*/ AND EMP_TYPE | = A /*EMP_TYPE上的索引将失效*/ 这是一种相当直接的提高查询效率的办法。 但是你必须谨慎考虑这种策略，一般来说，只有在你希望单独优化几个SQL时才能采用它。 这里有一个例子关于何时采用这种策略， 假设在EMP表的EMP_TYPE列上有一个非唯一性的索引而EMP_CLASS上没有索引。SELECT ENAME FROM EMP WHERE EMP_TYPE = AAND EMP_CLASS = X； 优化器会注意到EMP_TYPE上的索引并使用它。 这是目前唯一的选择。 如果，一段时间以后， 另一个非唯一性建立在EMP_CLASS上，优化器必须对两个索引进行选择，在通常情况下，优化器将使用两个索引并在他们的结果集合上执行排序及合并。 然而，如果其中一个索引（EMP_TYPE）接近于唯一性而另一个索引（EMP_CLASS）上有几千个重复的值。 排序及合并就会成为一种不必要的负担。 在这种情况下，你希望使优化器屏蔽掉EMP_CLASS索引。 用下面的方案就可以解决问题。SELECT ENAME FROM EMP WHERE EMP_TYPE = AAND EMP_CLASS| = X；5.17 避免在索引列上使用计算 WHERE子句中，如果索引列是函数的一部分。优化器将不使用索引而使用全表扫描。 举例： 低效：SELECT FROM DEPT WHERE SAL * 12 25000； 高效：SELECT FROM DEPT WHERE SAL 25000/12；5.18 用=替代 如果DEPTNO上有一个索引， 高效：SELECT FROM EMP WHERE DEPTNO = 4 低效：SELECT FROM EMP WHERE DEPTNO 3 两者的区别在于， 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录。5.19 避免在索引列上使用IS NULL和IS NOT NULL避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引。对于单列索引，如果列包含空值，索引中将不存在此记录。 对于复合索引，如果每个列都为空，索引中同样不存在此记录。如果至少有一个列不为空，则记录存在于索引中。 举例：如果唯一性索引建立在表的A列和B列上， 并且表中存在一条记录的A，B值为（123，null） ， ORACLE将不接受下一条具有相同A，B值（123，null）的记录（插入）。 然而如果所有的索引列都为空，ORACLE将认为整个键值为空而空不等于空。 因此你可以插入1000条具有相同键值的记录，当然它们都是空！ 因为空值不存在于索引列中，所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引。 举例： 低效： （索引失效）SELECT FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL； 高效： （索引有效）SELECT FROM DEPARTMENT WHERE DEPT_CODE =0；5.20 总是使用索引的第一个列如果索引是建立在多个列上， 只有在它的第一个列（leading column）被where子句引用时，优化器才会选择使用该索引。5.21 用UNION-ALL 替换UNION （ 如果有可能的话） 当SQL语句需要UNION两个查询结果集合时，这两个结果集合会以UNION-ALL的方式被合并， 然后在输出最终结果前进行排序。 如果用UNION ALL替代UNION， 这样排序就不是必要了。 效率就会因此得到提高。 举例：低效：SELECT ACCT_NUM， BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = 31-DEC-95UNION 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-95UNION ALL SELECT ACCT_NUM， BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = 31-DEC-95注意: UNION ALL 将重复输出两个结果集合中相同记录。 因此还是要从业务需求分析使用UNION ALL的可行性。5.22 需要当心的WHERE子句某些SELECT 语句中的WHERE子句不使用索引。注意以下的例子。5.22.1 ！= 将不使用索引 不使用索引：SELECT ACCOUNT_NAME FROM TRANSACTION WHERE AMOUNT ！=0；使用索引：SELECT ACCOUNT_NAME FROM TRANSACTION WHERE AMOUNT 0；5.22.2 |将不使用索引。 不使用索引：SELECT ACCOUNT_NAME，AMOUNT FROM TRANSACTION WHERE ACCOUNT_NAME|ACCOUNT_TYPE=AMEXA；使用索引：SELECT ACCOUNT_NAME，AMOUNT FROM TRANSACTION WHERE ACCOUNT_NAME = AMEXAND ACCOUNT_TYPE= A；5.22.3 +将不使用索引 不使用索引：SELECT ACCOUNT_NAME， AMOUNT FROM TRANSACTION WHERE AMOUNT + 3000 5000；使用索引：SELECT ACCOUNT_NAME， AMOUNT FROM TRANSACTION WHERE AMOUNT 2000 ；5.22.