无锡网站制作怎么样,做网站运营有前途么,wordpress注册填写密码,免费服务器购买(转)SQL 优化原则一、问题的提出 在应用系统开发初期#xff0c;由于开发数据库数据比较少#xff0c;对于查询SQL语句#xff0c;复杂视图的的编写等体会不出SQL语句各种写法的性能优劣#xff0c;但是如果将应用 系统提交实际应用后#xff0c;随着数据库中数据的增加由于开发数据库数据比较少对于查询SQL语句复杂视图的的编写等体会不出SQL语句各种写法的性能优劣但是如果将应用 系统提交实际应用后随着数据库中数据的增加系统的响应速度就成为目前系统需要解决的最主要的问题之一。系统优化中一个很重要的方面就是SQL语句的优 化。对于海量数据劣质SQL语句和优质SQL语句之间的速度差别可以达到上百倍可见对于一个系统不是简单地能实现其功能就可而是要写出高质量的 SQL语句提高系统的可用性。 在多数情况下Oracle使用索引来更快地遍历表优化器主要根据定义的索引来提高性能。但是如果在SQL语句的where子句中写的 SQL代码不合理就会造成优化器删去索引而使用全表扫描一般就这种SQL语句就是所谓的劣质SQL语句。在编写SQL语句时我们应清楚优化器根据何种 原则来删除索引这有助于写出高性能的SQL语句。 二、SQL语句编写注意问题 下面就某些SQL语句的where子句编写中需要注意的问题作详细介绍。在这些where子句中即使某些列存在索引但是由于编写了劣质的SQL系统在运行该SQL语句时也不能使用该索引而同样使用全表扫描这就造成了响应速度的极大降低。 1. IS NULL 与 IS NOT NULL 不能用null作索引任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下只要这些列中有一列含有null该列就会从索引中排除。也就是说如果某列存在空值即使对该列建索引也不会提高性能。 任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。 2. 联接列 对于有联接的列即使最后的联接值为一个静态值优化器是不会使用索引的。我们一起来看一个例子假定有一个职工表(employee)对于 一个职工的姓和名分成两列存放(FIRST_NAME和LAST_NAME)现在要查询一个叫比尔.克林顿(Bill Cliton)的职工。 下面是一个采用联接查询的SQL语句 select*from employss where first_name||||last_name Beill Cliton; 上面这条语句完全可以查询出是否有Bill Cliton这个员工但是这里需要注意系统优化器对基于last_name创建的索引没有使用。 当采用下面这种SQL语句的编写Oracle系统就可以采用基于last_name创建的索引。 *** where first_name Beilland last_name Cliton; . 带通配符(%)的like语句 同样以上面的例子来看这种情况。目前的需求是这样的要求在职工表中查询名字中包含cliton的人。可以采用如下的查询SQL语句: select*from employee where last_name like%cliton%; 这里由于通配符(%)在搜寻词首出现所以Oracle系统不使用last_name的索引。在很多情况下可能无法避免这种情况但是一定要心中有底通 配符如此使用会降低查询速度。然而当通配符出现在字符串其他位置时优化器就能利用索引。在下面的查询中索引得到了使用: select*from employee where last_name likec%; 4. Order by语句 ORDER BY语句决定了Oracle如何将返回的查询结果排序。Order by语句对要排序的列没有什么特别的限制也可以将函数加入列中(象联接或者附加等)。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。 仔细检查order by语句以找出非索引项或者表达式它们会降低性能。解决这个问题的办法就是重写order by语句以使用索引也可以为所使用的列建立另外一个索引同时应绝对避免在order by子句中使用表达式。 5. NOT 我们在查询时经常在where子句使用一些逻辑表达式如大于、小于、等于以及不等于等等也可以使用and(与)、or(或)以及not(非)。NOT可用来对任何逻辑运算符号取反。下面是一个NOT子句的例子: ... wherenot (status VALID) 如果要使用NOT则应在取反的短语前面加上括号并在短语前面加上NOT运算符。NOT运算符包含在另外一个逻辑运算符中这就是不等于()运算符。换句话说即使不在查询where子句中显式地加入NOT词NOT仍在运算符中见下例: ... where status INVALID; 对这个查询可以改写为不使用NOT: select*from employee where salary3000or salary3000; 虽然这两种查询的结果一样但是第二种查询方案会比第一种查询方案更快些。第二种查询允许Oracle对salary列使用索引而第一种查询则不能使用索引。 虽然这两种查询的结果一样但是第二种查询方案会比第一种查询方案更快些。第二种查询允许Oracle对salary列使用索引而第一种查询则不能使用索引。 我们要做到不但会写SQL,还要做到写出性能优良的SQL,以下为笔者学习、摘录、并汇总部分资料与大家分享 1 选择最有效率的表名顺序(只在基于规则的优化器中有效) ORACLE 的解析器按照从右到左的顺序处理FROM子句中的表名FROM子句中写在最后的表(基础表 driving table)将被最先处理在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表. 2 WHERE子句中的连接顺序 ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾. 3 SELECT子句中避免使用 ‘ * ‘ ORACLE在解析的过程中, 会将* 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间 4 减少访问数据库的次数 ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等 5 在SQL*Plus , SQL*Forms和Pro*C中重新设置ARRAYSIZE参数, 可以增加每次数据库访问的检索数据量 ,建议值为200 6 使用DECODE函数来减少处理时间 使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表. 7 整合简单,无关联的数据库访问 如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系) 8 删除重复记录 最高效的删除重复记录方法 ( 因为使用了ROWID)例子 DELETE FROM EMP E WHERE E.ROWID (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO E.EMP_NO); 9 用TRUNCATE替代DELETE 当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短. (译者按: TRUNCATE只在删除全表适用,TRUNCATE是DDL不是DML) 10 尽量多使用COMMIT 只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少: COMMIT所释放的资源: a. 回滚段上用于恢复数据的信息. b. 被程序语句获得的锁 c. redo log buffer 中的空间 d. ORACLE为管理上述3种资源中的内部花费 11 用Where子句替换HAVING子句 避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销. (非oracle中)on、where、having这三个都可以加条件的子句中on是最先执行where次之having最后因为on是先把不 符合条件的记录过滤后才进行统计它就可以减少中间运算要处理的数据按理说应该速度是最快的where也应该比having快点的因为它过滤数据后 才进行sum在两个表联接时才用on的所以在一个表的时候就剩下where跟having比较了。在这单表查询统计的情况下如果要过滤的条件没有涉及到要计算字段那它们的结果是一样的只是where可以使用rushmore技术而having就不能在速度上后者要慢如果要涉及到计算的字 段就表示在没计算之前这个字段的值是不确定的根据上篇写的工作流程where的作用时间是在计算之前就完成的而having就是在计算后才起作 用的所以在这种情况下两者的结果会不同。在多表联接查询时on比where更早起作用。系统首先根据各个表之间的联接条件把多个表合成一个临时表 后再由where进行过滤然后再计算计算完后再由having进行过滤。由此可见要想过滤条件起到正确的作用首先要明白这个条件应该在什么时候起作用然后再决定放在那里 12 减少对表的查询 在含有子查询的SQL语句中,要特别注意减少对表的查询.例子 SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) ( SELECT TAB_NAME,DB_VER FROM TAB_COLUMNS WHERE VERSION 604) 13 通过内部函数提高SQL效率. 复杂的SQL往往牺牲了执行效率. 能够掌握上面的运用函数解决问题的方法在实际工作中是非常有意义的 14 使用表的别名(Alias) 当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误. 15 用EXISTS替代IN、用NOT EXISTS替代NOT IN 在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率. 在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS. 例子 高效SELECT * FROM EMP (基础表) WHERE EMPNO 0 AND EXISTS (SELECT ‘X FROM DEPT WHERE DEPT.DEPTNO EMP.DEPTNO AND LOC ‘MELB) (低效)SELECT * FROM EMP (基础表) WHERE EMPNO 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC ‘MELB) 16 识别低效执行的SQL语句 虽然目前各种关于SQL优化的图形化工具层出不穷,但是写出自己的SQL工具来解决问题始终是一个最好的方法 SELECT EXECUTIONS , DISK_READS, BUFFER_GETS, ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio, ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run, SQL_TEXT FROM V$SQLAREA WHERE EXECUTIONS0 AND BUFFER_GETS 0 AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS 0.8 ORDER BY 4 DESC;17 用索引提高效率 索引是表的一个概念部分,用来提高检索数据的效率ORACLE使用了一个复杂的自平衡B-tree结构. 通常,通过索引查询数据比全表扫描要快. 当ORACLE找出执行查询和Update语句的最佳路径时, ORACLE优化器将使用索引. 同样在联结多个表时使用索引也可以提高效率. 另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证.。那些LONG或LONG RAW数据类型, 你可以索引几乎所有的列. 通常, 在大型表中使用索引特别有效. 当然,你也会发现, 在扫描小表时,使用索引同样能提高效率. 虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价. 索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也会被修改. 这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.。定期的重构索引是有必要的. ALTER INDEX INDEXNAME REBUILD TABLESPACENAME 18 用EXISTS替换DISTINCT 当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT. 一般可以考虑用EXIST替换, EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果. 例子 (低效): 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); 19 sql语句用大写的因为oracle总是先解析sql语句把小写的字母转换成大写的再执行 20 在java代码中尽量少用连接符“”连接字符串 21 避免在索引列上使用NOT 通常 我们要避免在索引列上使用NOT, NOT会产生在和在索引列上使用函数相同的影响. 当ORACLE”遇到”NOT,他就会停止使用索引转而执行全表扫描. 22 避免在索引列上使用计算 WHERE子句中如果索引列是函数的一部分优化器将不使用索引而使用全表扫描 举例: 低效 SELECT … FROM DEPT WHERE SAL * 12 25000; 高效: SELECT … FROM DEPT WHERE SAL 25000/12; 23 用替代 高效: SELECT * FROM EMP WHERE DEPTNO 4 低效: SELECT * FROM EMP WHERE DEPTNO 3 两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO3的记录并且向前扫描到第一个DEPT大于3的记录. 24 用UNION替换OR (适用于索引列) 通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描. 注意, 以上规则只针对多个索引列有效. 如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低. 在下面的例子中, 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, 那就需要返回记录最少的索引列写在最前面. 25 用IN来替换OR 这是一条简单易记的规则但是实际的执行效果还须检验在ORACLE8i下两者的执行路径似乎是相同的 低效: SELECT…. FROM LOCATION WHERE LOC_ID 10 OR LOC_ID 20 OR LOC_ID 30 高效 SELECT… FROM LOCATION WHERE LOC_IN IN (10,20,30); 26 避免在索引列上使用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; 27 总是使用索引的第一个列 如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引. 这也是一条简单而重要的规则当仅引用索引的第二个列时,优化器使用了全表扫描而忽略了索引 28 用UNION-ALL 替换UNION ( 如果有可能的话) 当SQL 语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并, 然后在输出最终结果前进行排序. 如果用UNION ALL替代UNION, 这样排序就不是必要了. 效率就会因此得到提高. 需要注意的是UNION ALL 将重复输出两个结果集合中相同记录. 因此各位还是要从业务需求分析使用UNION ALL的可行性. UNION 将对结果集合排序,这个操作会使用到SORT_AREA_SIZE这块内存. 对于这块内存的优化也是相当重要的. 下面的SQL可以用来查询排序的消耗量 低效 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 29 用WHERE替代ORDER BY ORDER BY 子句只在两种严格的条件下使用索引. ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序. ORDER BY中所有的列必须定义为非空. WHERE子句使用的索引和ORDER BY子句中所使用的索引不能并列. 例如: 表DEPT包含以下列: DEPT_CODE PK NOT NULL DEPT_DESC NOT NULL DEPT_TYPE NULL 低效: (索引不被使用) SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_TYPE 高效: (使用索引) SELECT DEPT_CODE FROM DEPT WHERE DEPT_TYPE 0 30 避免改变索引列的类型.: 当比较不同数据类型的数据时, ORACLE自动对列进行简单的类型转换. 假设 EMPNO是一个数值类型的索引列. SELECT … FROM EMP WHERE EMPNO ‘123 实际上,经过ORACLE类型转换, 语句转化为: SELECT … FROM EMP WHERE EMPNO TO_NUMBER(‘123) 幸运的是,类型转换没有发生在索引列上,索引的用途没有被改变. 现在,假设EMP_TYPE是一个字符类型的索引列. SELECT … FROM EMP WHERE EMP_TYPE 123 这个语句被ORACLE转换为: SELECT … FROM EMP WHERETO_NUMBER(EMP_TYPE)123 因为内部发生的类型转换, 这个索引将不会被用到! 为了避免ORACLE对你的SQL进行隐式的类型转换, 最好把类型转换用显式表现出来. 注意当字符和数值比较时, ORACLE会优先转换数值类型到字符类型 31 需要当心的WHERE子句: 某些SELECT 语句中的WHERE子句不使用索引. 这里有一些例子. 在下面的例子里, (1)‘! 将不使用索引. 记住, 索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中. (2) ‘ ¦ ¦是字符连接函数. 就象其他函数那样, 停用了索引. (3) ‘是数学函数. 就象其他数学函数那样, 停用了索引. (4)相同的索引列不能互相比较,这将会启用全表扫描. 32 a. 如果检索数据量超过30%的表中记录数.使用索引将没有显著的效率提高. b. 在特定情况下, 使用索引也许会比全表扫描慢, 但这是同一个数量级上的区别. 而通常情况下,使用索引比全表扫描要块几倍乃至几千倍! 33 避免使用耗费资源的操作: 带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎 执行耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序. 通常, 带有UNION, MINUS , INTERSECT的SQL语句都可以用其他方式重写. 如果你的数据库的SORT_AREA_SIZE调配得好, 使用UNION , MINUS, INTERSECT也是可以考虑的, 毕竟它们的可读性很强 34 优化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 如果你正在负责一个基于SQL Server的项目或者你刚刚接触SQL Server你都有可能要面临一些数据库性能的问题这篇文章会为你提供一些有用的指导其中大多数也可以用于其它的DBMS。 在这里我不打算介绍使用SQL Server的窍门也不能提供一个包治百病的方案我所做的是总结一些经验----关于如何形成一个好的设计。这些经验来自我过去几年中经受的教训一直来我看到许多同样的设计错误被一次又一次的重复。 一、了解你用的工具 不要轻视这一点这是我在这篇文章中讲述的最关键的一条。也许你也看到有很多的SQL Server程序员没有掌握全部的T-SQL命令和SQL Server提供的那些有用的工具。 “什么我要浪费一个月的时间来学习那些我永远也不会用到的SQL命令”你也许会这样说。对的你不需要这样做。但是你应该用一个周末浏览所有的 T-SQL命令。在这里你的任务是了解将来当你设计一个查询时你会记起来“对了这里有一个命令可以完全实现我需要的功能”于是到MSDN 查看这个命令的确切语法。 二、不要使用游标 让我再重复一遍不要使用游标。如果你想破坏整个系统的性能的话它们倒是你最有效的首选办法。大多数的初学者都使用游标而没有意识到它们对性能造成的影响。它们占用内存还用它们那些不可思议的方式锁定表另外它们简直就像蜗牛。而最糟糕的是它们可以使你的DBA所能做的一切性能优化等于没做。不 知你是否知道每执行一次FETCH就等于执行一次SELECT命令这意味着如果你的游标有10000条记录它将执行10000次SELECT如果你 使用一组SELECT、UPDATE或者DELETE来完成相应的工作那将有效率的多。 初学者一般认为使用游标是一种比较熟悉和舒适的编程方式可很不幸这会导致糟糕的性能。显然SQL的总体目的是你要实现什么而不是怎样实现。 我曾经用T-SQL重写了一个基于游标的存储过程那个表只有100,000条记录原来的存储过程用了40分钟才执行完毕而新的存储过程只用了10秒钟。在这里我想你应该可以看到一个不称职的程序员究竟在干了什么 我们可以写一个小程序来取得和处理数据并且更新数据库这样做有时会更有效。记住对于循环T-SQL无能为力。 我再重新提醒一下使用游标没有好处。除了DBA的工作外我从来没有看到过使用游标可以有效的完成任何工作。 三、规范化你的数据表 为什么不规范化数据库大概有两个借口出于性能的考虑和纯粹因为懒惰。至于第二点你迟早得为此付出代价。而关于性能的问题你不需要优化根本就不慢的东西。我经常看到一些程序员“反规范化”数据库他们的理由是“原来的设计太慢了”可结果却常常是他们让系统更慢了。DBMS被设计用来处理规范数据库 的因此记住按照规范化的要求设计数据库。 四、不要使用SELECT * 这点不太容易做到我太了解了因为我自己就经常这样干。可是如果在SELECT中指定你所需要的列那将会带来以下的好处 1 减少内存耗费和网络的带宽 2 你可以得到更安全的设计 3 给查询优化器机会从索引读取所有需要的列 五、了解你将要对数据进行的操作 为你的数据库创建一个健壮的索引那可是功德一件。可要做到这一点简直就是一门艺术。每当你为一个表添加一个索引SELECT会更快了可INSERT 和DELETE却大大的变慢了因为创建了维护索引需要许多额外的工作。显然这里问题的关键是你要对这张表进行什么样的操作。这个问题不太好把握特别是涉及DELETE和UPDATE时因为这些语句经常在WHERE部分包含SELECT命令。 六、不要给“性别”列创建索引 首先我们必须了解索引是如何加速对表的访问的。你可以将索引理解为基于一定的标准上对表进行划分的一种方式。如果你给类似于“性别”这样的列创建了一个 索引你仅仅是将表划分为两部分男和女。你在处理一个有1,000,000条记录的表这样的划分有什么意义记住维护索引是比较费时的。当你设计索 引时请遵循这样的规则根据列可能包含不同内容的数目从多到少排列比如姓名省份性别。 七、使用事务 请使用事务特别是当查询比较耗时。如果系统出现问题这样做会救你一命的。一般有些经验的程序员都有体会-----你经常会碰到一些不可预料的情况会导致存储过程崩溃。 八、小心死锁 按照一定的次序来访问你的表。如果你先锁住表A再锁住表B那么在所有的存储过程中都要按照这个顺序来锁定它们。如果你不经意的某个存储过程中先锁定表B再锁定表A这可能就会导致一个死锁。如果锁定顺序没有被预先详细的设计好死锁是不太容易被发现的。 九、不要打开大的数据集 一个经常被提出的问题是我怎样才能迅速的将100000条记录添加到ComboBox中这是不对的你不能也不需要这样做。很简单你的用户要浏览 100000条记录才能找到需要的记录他一定会诅咒你的。在这里你需要的是一个更好的UI你需要为你的用户显示不超过100或200条记录。 十、不要使用服务器端游标 与服务器端游标比起来客户端游标可以减少服务器和网络的系统开销并且还减少锁定时间。 十一、使用参数查询 有时我在CSDN技术论坛看到类似这样的问题“SELECT * FROM a WHERE a.idAB因为单引号查询发生异常我该怎么办”而普遍的回答是用两个单引号代替单引号。这是错误的。这样治标不治本因为你还会在其他 一些字符上遇到这样的问题更何况这样会导致严重的bug除此以外这样做还会使SQL Server的缓冲系统无法发挥应有的作用。使用参数查询釜底抽薪这些问题统统不存在了。 十二、在程序编码时使用大数据量的数据库 程序员在开发中使用的测试数据库一般数据量都不大可经常的是最终用户的数据量都很大。我们通常的做法是不对的原因很简单现在硬盘不是很贵可为什么性能问题却要等到已经无可挽回的时候才被注意呢 十三、不要使用INSERT导入大批的数据 请不要这样做除非那是必须的。使用UTS或者BCP这样你可以一举而兼得灵活性和速度。 十四、注意超时问题 查询数据库时一般数据库的缺省都比较小比如15秒或者30秒。而有些查询运行时间要比这长特别是当数据库的数据量不断变大时。 十五、不要忽略同时修改同一记录的问题 有时候两个用户会同时修改同一记录这样后一个修改者修改了前一个修改者的操作某些更新就会丢失。处理这种情况不是很难创建一个timestamp字段在写入前检查它如果允许就合并修改如果存在冲突提示用户。 十六、在细节表中插入纪录时不要在主表执行SELECT MAX(ID) 这是一个普遍的错误当两个用户在同一时间插入数据时这会导致错误。你可以使用SCOPE_IDENTITYIDENT_CURRENT和IDENTITY。如果可能不要使用IDENTITY因为在有触发器的情况下它会引起一些问题详见这里的讨论。 十七、避免将列设为NULLable 如果可能的话你应该避免将列设为NULLable。系统会为NULLable列的每一行分配一个额外的字节查询时会带来更多的系统开销。另外将列设为NULLable使编码变得复杂因为每一次访问这些列时都必须先进行检查。 我并不是说NULLS是麻烦的根源尽管有些人这样认为。我认为如果你的业务规则中允许“空数据”那么将列设为NULLable有时会发挥很好的作用但是如果在类似下面的情况中使用NULLable那简直就是自讨苦吃。 CustomerName1 CustomerAddress1 CustomerEmail1 CustomerName2 CustomerAddress2 CustomerEmail3 CustomerName1 CustomerAddress2 CustomerEmail3 如果出现这种情况你需要规范化你的表了。 十八、尽量不要使用TEXT数据类型 除非你使用TEXT处理一个很大的数据否则不要使用它。因为它不易于查询速度慢用的不好还会浪费大量的空间。一般的VARCHAR可以更好的处理你的数据。 十九、尽量不要使用临时表 尽量不要使用临时表除非你必须这样做。一般使用子查询可以代替临时表。使用临时表会带来系统开销如果你是用COM进行编程它还会给你带来很大的麻 烦因为COM使用数据库连接池而临时表却自始至终都存在。SQL Server提供了一些替代方案比如Table数据类型。 二十、学会分析查询 SQL Server查询分析器是你的好伙伴通过它你可以了解查询和索引是如何影响性能的。 二十一、使用参照完整性 定义主健、唯一性约束和外键这样做可以节约大量的时间。 【IT168 技术文档】任何事情都有它的源头要解决问题也得从源头开始影响ORACLE性能的源头非常多主要包括如下方面数据库的硬件配置:CPU、内存、网络条件。 1. CPU:在任何机器中CPU的数据处理能力往往是衡量计算机性能的一个标志并且ORACLE是一个提供并行能力的数据库系统在CPU方面的要求就更高 了如果运行队列数目超过了CPU处理的数目性能就会下降我们要解决的问题就是要适当增加CPU的数量了当然我们还可以将需要许多资源的进程 KILL掉; 2. 内存:衡量机器性能的另外一个指标就是内存的多少了在ORACLE中内存和我们在建数据库中的交换区进行数据的交换读数据时磁盘I/O必须等待物理 I/O操作完成在出现ORACLE的内存瓶颈时我们第一个要考虑的是增加内存由于I/O的响应时间是影响ORACLE性能的主要参数我将在这方面 进行详细的讲解 3. 网络条件:NET*SQL负责数据在网络上的来往大量的SQL会令网络速度变慢。比如10M的网卡和100的网卡就对NET*SQL有非常明显的影响 还有交换机、集线器等等网络设备的性能对网络的影响很明显建议在任何网络中不要试图用3个集线器来将网段互联。 OS参数的设置 下表给出了OS的参数设置及说明DBA可以根据实际需要对这些参数进行设置 内核参数名 说明 bufpages 对buffer空间不按静态分配采用动态分配使bufpages值随nbuf一起对buffer空间进行动态分配。 create_fastlinks 对HFS文件系统允许快速符号链接 dbc_max_pct 加大最大动态buffer空间所占物理内存的百分比以满足应用系统的读写命中率的需要。 dbc_min_pct 设置最小动态buffer空间所占物理内存的百分比 desfree 提高开始交换操作的最低空闲内存下限保障系统的稳定性防止出现不可预见的系统崩溃(Crash)。 fs_async 允许进行磁盘异步操作提高CPU和磁盘的利用率 lotsfree 提高系统解除换页操作的空闲内存的上限值保证应用程序有足够的可用内存空间。 maxdsiz 针对系统数据量大的特点加大最大数据段的大小保证应用的需要。(32位) maxdsiz_64bit maximum process data segment size for 64_bit Maxssiz 加大最大堆栈段的大小。(32_bit) maxssiz_64bit 加大最大堆栈段的大小。(64_bit) Maxtsiz 提高最大代码段大小满足应用要求 maxtsiz_64bit 原值过大应调小 Minfree 提高停止交换操作的自由内存的上限 Shmem 允许进行内存共享以提高内存的利用率 Shmmax 设置最大共享内存段的大小完全满足目前的需要 Timeslice 由于系统的瓶颈主要反映在磁盘I/O上因此 降低时间片的大小一方面可避免因磁盘I/O不畅造成CPU的等待从而提高了CPU的综合利用率。另一方面减少了进程的阻塞量。 unlockable_mem 提高了不可锁内存的大小使可用于换页和交换的内存空间扩大,用以满足系统对内存管理的要求。 用户SQL质量 以上讲的都是硬件方面的东西在条件有限的条件下我们可以调整应用程序的SQL质量: 1. 不要进行全表扫描(Full Table Scan):全表扫描导致大量的I/O 2. 尽量建好和使用好索引:建索引也是有讲究的在建索引时也不是索引越多越好当一个表的索引达到4个以上时ORACLE的性能可能还是改善不了因为 OLTP系统每表超过5个索引即会降低性能而且在一个sql 中 Oracle 从不能使用超过 5个索引;当我们用到GROUP BY和ORDER BY时,ORACLE就会自动对数据进行排序,而ORACLE在INIT.ORA中决定了sort_area_size区的大小,当排序不能在我们给定的 排序区完成时,ORACLE就会在磁盘中进行排序,也就是我们讲的临时表空间中排序, 过多的磁盘排序将会令 free buffer waits 的值变高,而这个区间并不只是用于排序的,对于开发人员我提出如下忠告: 1)、select,update,delete 语句中的子查询应当有规律地查找少于20%的表行.如果一个语句查找的行数超过总行数的20%,它将不能通过使用索引获得性能上的提高. 2)、索引可能产生碎片,因为记录从表中删除时,相应也从表的索引中删除.表释放的空间可以再用,而索引释放的空间却不能再用.频繁进行删除操 作的被索引的表,应当阶段性地重建索引,以避免在索引中造成空间碎片,影响性能.在许可的条件下,也可以阶段性地truncate表,truncate命 令删除表中所有记录,也删除索引碎片. 3)、在使用索引时一定要按索引对应字段的顺序进行引用。 4)、用()比用NOT IN更有效率。 降低ORACLE的竞争: 先讲几个ORACLE的几个参数这几个参数关系到ORACLE的竞争: 1)、freelists 和 freelist 组:他们负责ORACLE的处理表和索引的空间管理; 2)、pctfree 及 pctused:该参数决定了freelists 和 freelist 组的行为pctfree 和pctused 参数的唯一目的就是为了控制块如何在 freelists 中进出 设置好pctfree 及 pctused对块在freelists的移走和读取很重要。 其他参数的设置 1)、包括SGA区(系统全局区):系统全局区(SGA)是一个分配给Oracle 的包含一个 Oracle 实例的数据库的控制信息内存段。 主要包括数据库高速缓存(the database buffer cache) 重演日志缓存(the redo log buffer) 共享池(the shared pool) 数据字典缓存(the data dictionary cache)以及其它各方面的信息 2)、db_block_buffers(数据高速缓冲区)访问过的数据都放在这一片内存区域该参数越大Oracle在内存中找到相同数据的可能性就越大也即加快了查询速度。 3)、share_pool_size (SQL共享缓冲池):该参数是库高速缓存和数据字典的高速缓存。 4)、Log_buffer (重演日志缓冲区) 5)、sort_area_size(排序区) 6)、processes (同时连接的进程数) 7)、db_block_size (数据库块大小):Oracle默认块为2KB太小了因为如果我们有一个8KB的数据则2KB块的数据库要读4次盘才能读完而8KB块的数据库 只要1次就读完了大大减少了I/O操作。数据库安装完成后就不能再改变db_block_size的值了只能重新建立数据库并且建库时要选择手工 安装数据库。 8)、open_links (同时打开的链接数) 9)、dml_locks 10)、open_cursors (打开光标数) 11)、dbwr_io_slaves (后台写进程数) 6. IN和EXISTS 有时候会将一列和一系列值相比较。最简单的办法就是在where子句中使用子查询。在where子句中可以使用两种格式的子查询。 第一种格式是使用IN操作符: ... wherecolumnin(select*from ... where ...); 第二种格式是使用EXIST操作符: ... whereexists (selectXfrom ...where ...);转载于:https://www.cnblogs.com/LRJ20110831/archive/2012/10/08/2715148.html
