如何书写高效的SQL语句

1、如何书写高效的SQL语句在应用开发中，书写 SQL 语句是最基本的工作，但我们往往在这 方面侧重的是功能的实现， 很容易把效率问题给忽略了， 在随着系统 数据的不断增加，可能有些低效的 SQL 语句会让客户感觉系统反映 缓慢，甚至还有可能会因长时间执行不出结果而报超时或其它系统错 误，所以我们要养成一个良好的编码习惯，不仅要实现其功能，而且 要尽可能的提高效率。下面内容是在网络上收集的一些比较典型实用的优化点，以及在 工作中用到的典型样例，供大家参考学习。1、注意 UNion 和 UNion all 的区别。 它们两都可以把相同结果集并在一起，但 UNION all 的效率稍好一些，在就是如果

2、 A 并 上 B 有重复记录， union all 可以把重复数据 distict 掉UNION 一样会使查询变慢。重复的记录2、注意使用DISTINCT在没有必要时不要用，它同 在查询里是没有问题的3、查询时不要返回不需要的行、列,即最好不要用 select * from tablename ，这样会把所有的字段都解析出来 .4、如果使用了 IN或者OR等时发现查询没有走索引，使用显示申明指定索引：SELECT * FROM PersonMember (INDEX = IX_Title) WHERE processid IN(男'，女' )5、在 SQL2000 以前，一般不

3、要用如下的字句 ："IS NULL", " <> ","!=",”!",”！<", "NOT", "NOT EXISTS", "NOTIN", "NOT LIKE", and "LIKE'%500”'，因为他们不走索引全是表扫描。也不要在 WHere 字句中的列名加函数，如 Convert， substring 等 ,如果必须用函数的时候，创建计算列再创建索引来替代.还可以变通写法：WHERE

4、SUBSTRING(first name,1,1) ='m'改为 WHERE first namelike 'm%'(索引扫描)，一定要将函数和列名分开。并且索引不能建得太多和太大。NOT IN 会多次扫描表，使用EXISTS、 NOTEXISTS ， IN , LEFT OUTER JOIN 来替代，特别是左连接 ,而 Exists比IN更快，最慢的是 NOT操作如果列的值含有空，以前它的索引不起作用，现在2000的优化器能够处理了。相同的是IS NULL，“ NOT", "NOT EXISTS", "NOT IN&qu

5、ot;能优化她，而” <> ”等还是不能优化，用不到索引。6、 将需要查询的结果预先计算好放在表中，查询的时候再SELECT这在SQL7.0以前是最重 要的手段。例如医院的住院费计算。7、MIN（）和MAX（）能使用到合适的索引&数据库有一个原则是代码离数据越近越好，所以优先选择Default,依次为 Rules,Triggers,Con strai nt （约束如外健主健CheckUNIQUE,数据类型的最大长度等等都是约 束）Procedure.这样不仅维护工作小，编写程序质量高，并且执行的速度快。9、如果要插入大的二进制值到 Image 列，使用存储过程， 千万不要用

6、内嵌 INsert 来插入 （不 知JAVA是否）。因为这样应用程序首先将二进制值转换成字符串（尺寸是它的两倍），服务器受到字符后又将他转换成二进制值.存储过程就没有这些动作： 方法：Create procedure p_insert as insert into table（Fimage） values （image）, 在前 台调用这个存储过程传入二进制参数，这样处理速度明显改善。10、Between 在某些时候比 IN 速度更快 ,Between 能够更快地根据索引找到范围。用查询优化器可见到差别。select * from chineseresumewhere title in（

7、9;男','女'）Select * fromchineseresume where between'男' and'女'是一样的。由于 in 会在比较多次，所以有时会慢些。11、 在必要是对全局或者局部临时表创建索引，有时能够提高速度， 但不是一定会这样， 因 为索引也耗费大量的资源。他的创建同是实际表一样。12、不要建没有作用的事物例如产生报表时，浪费资源。只有在必要使用事物时使用它。13、用 OR 的字句可以分解成多个查询，并且通过 UNION 连接多个查询。他们的速度只 同是否使用索引有关 ,如果查询需要用到联合索引， 用 UNION

8、 all 执行的效率更高 .多个 OR 的字句没有用到索引，改写成 UNION 的形式再试图与索引匹配。一个关键的问题是否用到 索引。14、 尽量少用视图，它的效率低。对视图操作比直接对表操作慢,可以用 stored procedure来代替她。特别的是不要用视图嵌套,嵌套视图增加了寻找原始资料的难度。我们看视图的本质：它是存放在服务器上的被优化好了的已经产生了查询规划的SQL对单个表检索数据时，不要使用指向多个表的视图， 直接从表检索或者仅仅包含这个表的视图上读， 否则增加 了不必要的开销，查询受到干扰.为了加快视图的查询，MsSQL增加了视图索引的功能。15、 没有必要时不要用DISTIN

9、CT和ORDER BY,这些动作可以改在客户端执行。它们增加 了额外的开销。这同 UNION 和UNION ALL一样的道理。16、在 IN 后面值的列表中,将出现最频繁的值放在最前面,出现得最少的放在最后面,减 少判断的次数17、当用 SELECT INTO时，它会锁住系统表 （sysobjects，sysindexes等等），阻塞其他的连接的存取。创建临时表时用显示申明语句，而不是 select INTO. drop table t_lxh begin tran select * into t_lxh from chineseresume where name = 'XYZ'

10、; -commit 在另一个连接中 SELECT * from sysobjects 可以看到 SELECT INTO 会锁住系统表， Create table 也会锁系统表 （不管是临时表还是系统表 ）。所以千 万不要在事物内使用它！ ！这样的话如果是经常要用的临时表请使用实表， 或者临时表变量。18、一次更新多条记录比分多次更新每次一条快,就是说批处理好19、 SELECT COUNT（*）的效率教低，尽量变通他的写法，而EXISTS快同时请注意区别： select count（Field of null） from Table 和 select count（Field of NOT nu

11、ll） from Table 的返回值是不同的。20、 当服务器的内存够多时，配制线程数量 = 最大连接数 +5，这样能发挥最大的效率；否则使用配制线程数量 <最大连接数启用 SQLSERVER勺线程池来解决，如果还是数量= 最大连接数 +5，严重的损害服务器的性能。我觉得主要应该从 5 个方面进行调整：1去掉不必要的大型表的全表扫描2缓存小型表的全表扫描3检验优化索引的使用4检验优化的连接技术5尽可能减少执行计划的Cost现在简单的举几个例子Where 子句中有“！ =”将不使用索引select account_name from test where amount != 0（不使用

12、）select account_name from test where amount > 0（使用 ）Where 条件中对字段增加处理函数将不使用该列的索引select * from emp where to_char（hire_date，'yyyymmdd'）='20080411' （ 不使用 ）select * from emp where hire_date = to_char（'20080411'，'yyyymmdd'） （ 使用 ）避免在索引列上使用 IS NULL和IS NOT NULLselect * fro

13、m emp where dept_code is not null（不使用 ）select * from emp where dept_code > 0（使用 ）通配符 % 的使用select * from emp where name like '%A'（不使用索引 ）select * from emp where name like 'A%'（使用索引 ）最高效的删除重复记录方法（因为使用了 ROWID例子:DELETE FROM EMP E WHERE E.ROWID > （SELECT MIN（X.ROWID）FROM EMP X WHERE

14、 X.EMP_NO = E.EMP_NO）;sql 语句用大写的；因为 oracle 总是先解析 sql 语句，把小写的字母转换成大写的再执行在 java 代码中用到 preparedStatement 的時候尽量少用连接符“”连接字符串！1. SELECT子句中避免使用 * '当你想在SELECT?句中列出所有的 COLUMN时,使用动态SQL列引用 * '是一个方 便的方法不幸的是，这是一个非常低效的方法实际上ORACLE在解析的过程中，会将* ' 依次转换成所有的列名 , 这个工作是通过查询数据字典完成的 , 这意味着将耗费更多的时 间.2. 选择最有效率的表名顺

15、序 （只在基于规则的优化器中有效 ）ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表（基础表driving table）将被最先处理.在FROM子句中包含多个表的情况下 ，你必 须选择记录条数最少的表作为基础表当ORACLE处理多个表时，会运用排序及合并的方式连接它们 .首先，扫描第一个表 （FROM 子句中最后的那个表 ）并对记录进行派序 ，然后扫描第二个 表（FROM子句中最后第二个表），最后将所有从第二个表中检索出的记录与第一个表中合适记 录进行合并 .例如: 表 TAB1 16，384 条记录表 TAB2 1 条记录选择TAB2作为基础表（

16、最好的方法）select count（*） from tab1，tab2 执行时间 0.96 秒选择TAB2作为基础表（不佳的方法）select count（*） from tab2，tab1 执行时间 26.09 秒如果有3个以上的表连接查询，那就需要选择交叉表（intersection table）作为基础表，交叉表 是指那个被其他表所引用的表 .例如：EMP表描述了 LOCATION表和CATEGORY的交集SELECT *FROM LOCATION L ，CATEGORY C，EMP EWHERE E.EMP_NO BETWEEN 1000 AND 2000AND E.CAT_NO =

17、 C.CAT_NOAND E. LOCN = L.L OCN 将比下列SQL更有效率SELECT *FROM EMP E ， LOCATION L ，CATEGORY CWHERE E.CAT_NO = C.CAT_NOAND E.LOCN = L.LOCNAND E.EMP_NO BETWEEN 1000 AND 20003. WHERE子句中的连接顺序.ORACLE采用自下而上的顺序解析 WHERE子句,根据这个原理，表之间的连接必须写在其 他WHERE条件之前,那些可以过滤掉最大数量记录的条件必须写在 WHERE子句的末尾. 例如 :(低效 ,执行时间 156.3 秒)SELECT FR

18、OM EMP EWHERE SAL > 50000AND JOB = MANAGE'RAND25 < (SELECT COUNT(*) FROM EMPWHERE MGR=E.EMPNO);(高效,执行时间 10.6 秒)SELECT FROM EMP EWHERE 25 < (SELECT COUNT(*) FROM EMPWHERE MGR=E.EMPNO)ANDSAL > 50000ANDJOB = MANAGE'R ;4. 减少访问数据库的次数当执行每条SQL语句时，ORACLE在内部执行了许多工作：解析SQL语句，估算索引的利 用率 , 绑定变

19、量 , 读数据块等等 . 由此可见 , 减少访问数据库的次数 , 就能实际上减少 ORACLE的工作量.例如 , 以下有三种方法可以检索出雇员号等于 0342或0291的职员 .方法 1 (最低效 )SELECT EMP_NAME , SALARY , GRADEFROM EMPWHERE EMP_NO = 342;SELECT EMP_NAME , SALARY , GRADEFROM EMPWHERE EMP_NO = 291;方法 2 (次低效 )DECLARECURSOR C1 (E_NO NUMBER) ISSELECT EMP_NAME,SALA,RGYRADEFROM EMPWH

20、ERE EMP_NO = E_NO;BEGINOPEN C1(342);FETCH C1 INTO ,.,. ;OPEN C1(291);FETCH C1 INTO ,.,. ;CLOSE C1;END; 方法 3 （高效 ）SELECT A.EMP_NAME , A.SALARY , A.GRADE, B.EMP_NAME , B.SALARY , B.GRADEFROM EMP A,EMP BWHERE A.EMP_NO = 342AND B.EMP_NO = 291;在SQL*Plus , SQL*Forms和Pro*C中重新设置 ARRAYSIZ参数,可以增加每次数据库访问的检 索数据

21、量 ,建议值为 200.5使用DECODE函数来减少处理时间使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表例如 :SELECT COUNT(*，) SUM(SAL)FROM EMPWHERE DEPT_NO = 0020AND ENAME LIKE SMITH%'SELECT COUNT(*，) SUM(SAL)FROM EMPWHERE DEPT_NO = 0030AND ENAME LIKE SMITH%'你可以用DECODE!数高效地得到相同结果SELECT COUNT(DECODE(DEPT_NO,0020,'X',NULL) D0020

22、_COUNT, COUNT(DECODE(DEPT_NO,0030,'X',NULL) D0030_COUNT, SUM(DECODE(DEPT_NO,0020,SAL,NULL) D0020_SAL, SUM(DECODE(DEPT_NO,0030,SAL,NULL) D0030_SALFROM EMP WHERE ENAME LIKE SMITH%'类似的DECODE函数也可以运用于 GROUP BY和ORDER BYF句中6 使用表的别名 (Alias)当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减

23、少那些由Column 歧义引起的语法错误 (译者注：Column歧义指的是由于 SQL中不同的表具有相同的 Column名，当SQL语句中出现 这个Column时,SQL解析器无法判断这个 Column的归属)7. 用 EXISTS替代 IN在许多基于基础表的查询中 ，为了满足一个条件 ，往往需要对另一个表进行联接在这种情况下，使用EXISTS或 NOT EXISTS通常将提高查询的效率 低效 ：SELECT *FROM EMP (基础表)WHERE EMPNO > 0AND DEPTNO IN (SELECT DEPTNOFROM DEPTWHERE LOC = MELB')高

24、效:SELECT *FROM EMP (基础表 )WHERE EMPNO > 0AND EXISTS (SELECT X'FROM DEPTWHERE DEPT.DEPTNO = EM.DPEPTNOAND LOC = MELB')(译者按：相对来说，用NOT EXISTS替换NOT IN将更显著地提高效率，下一节中将指出)8. 用 NOT EXISTS代 NOT IN在子查询中,NOT IN子句将执行一个内部的排序和合并 无论在哪种情况下,NOT IN都 是最低效的 (因为它对子查询中的表执行了一个全表遍历 ). 为了避免使用 NOT IN ，我们可以 把它改写成外连接

25、(Outer Joi ns)或NOT EXISTS.例如 ：SELECT FROM EMPWHERE DEPT_NO NOT IN (SELECT DEPT_NOFROM DEPTWHERE DEPT_CATA'=');为了提高效率 .改写为 ：(方法一 ： 高效)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'

26、FROM DEPT DWHERE D.DEPT_NO = E.DEPT_NOAND DEPT_CAT = A');9. 用表连接替换 EXISTS通常来说，采用表连接的方式比 EXISTS有效率SELECT ENAMEFROM EMP EWHERE EXISTS (SELECT X'FROM DEPTWHERE DEPT_NO = E.DEPT_NOAND DEPT_CAT = A');(更高效 )SELECT ENAMEFROM DEPT D,EMP EWHERE E.DEPT_NO = D.DEPT_NOAND DEPT_CAT = A' ;10. 用 EXISTS替换 DISTINCT当提交一个包含一对多表信息(比如部门表和雇员表)的查询时，避免在SELECT子句中使用DISTINCT. 一般可以考虑用 EXIST替换例如 :低效 :SELECT DISTINCT DEPT_NO，DEPT_NAMEFROM DEPT D，EMP EWHERE D.DEPT_NO = E.DEPT_NO高效 :