编写高效优化的sql语句

1        引言

SQL语句是我们在软件开发过程中操作数据库经常要编写的，SQL语句的写法很灵活，同一个数据库操作sql可能有好多种写法，但是如何让sql语句高效地执行，不给数据库系统带来更多的压力，这个是我们应该学习并且掌握的。尤其是当一个很大的软件系统操作数据库很频繁的时候，优化的sql语句显得极其重要。本文档主要真对日常软件开发中常用到的一些数据库的操作以及会涉及到的一些数据库地知识、如何高效编写sql语句进行了总结，供大家学习分享。

2        编写高效地sql语句

2.1    尽量避免全表扫描

l  对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。（有明显时间节省）

 

l  应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num=0（）（有明显时间节省）

 

l  应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。（5000条记录的情况下节省0.016秒）

 

l  应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num=10 or num=20，可以这样查询：

select id from twhere num=10

union all

select id from twhere num=20（表中5000条记录的情况下节省大约0.02秒的时间）

 

l  in 和 not in 也要慎用，否则会导致全表扫描，如：

select id from twhere num in(1,2,3)对于连续的数值，能用between 就不要用 in 了：select id from t where num between 1 and 3（170条记录的情况下速度加快0.016秒,5000条记录的情况下节省一半的时间）

   

l  如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

select id from t where num=@num可以改为强制查询使用索引：

select id from t with(index(索引名)) where num=@num

l  .应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：select id from t where num/2=100应 改为: 

select id from t where num=100*2（表达式计算会耗去一定时间）

 

l  应尽量避免在where子句中对字段进行函数 操作，这将导致引擎放弃使用索引而进行全表扫描。

 

l  不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

 

2.2    关于索引的使用 

l  在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。（时间差别很明显，尽量使用索引中的第一个字段查询）

 

l  并不是所有索引对查询都有效，SQL是根据表中 数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段***，male、female几乎各一半，那么即使在***上建了索引也对查询效率起不了作用。

 

l  索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要。（索引会影响insert和update的效率）

 

l  应尽可能的避免更新 clustered 索引数据列，因为clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。

 

l  要注意索引的维护，周期性重建索引。

 

2.3    关于临时表的使用

l  尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。

 

l  避免频繁创建和删除临时表，以减少系统表资源的消耗。

 

l  临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。

 

l  在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

 

l  如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

 

2.4    关于游标的使用

l  尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

 

l  与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。

 

l  使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。

 

2.5    一些关键字以及函数的使用

l  WHERE子句中的连接顺序：

Oracle采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。

 

l  SELECT子句中避免使用‘*’：

Oracle在解析的过程中, 会将‘*’依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间。（时间相差很多，甚至达到一半）

 

l  使用DECODE函数来减少处理时间：

使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表。

 

l  尽量多使用COMMIT：

只要有可能，在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少，COMMIT所释放的资源:

a. 回滚段上用于恢复数据的信息。

b. 被程序语句获得的锁。

c. redo logbuffer 中的空间。

d. Oracle为管理上述3种资源中的内部花费。

 

l  用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进行过滤。由此可见，要想过滤条件起到正确的作用，首先要明白这个条件应该在什么时候起作用，然后再决定放在那里。（having一般和一些聚合函数使用，尽量避免，能用where代替的尽量用where）

 

l  通过内部函数提高SQL效率：

复杂的SQL往往牺牲了执行效率。能够掌握上面的运用函数解决问题的方法在实际工作中是非常有意义的。

 

l  使用表的别名(Alias)：

当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上。这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误。（这点大家都应该能理解）

 

l  用EXISTS替代IN、用NOT EXISTS替代NOT IN：

在许多基于基础表的查询中，为了满足一个条件,往往需要对另一个表进行联接。在这种情况下，使用EXISTS(或NOT EXISTS)通常将提高查询的效率。在子查询中，NOT IN子句将执行一个内部的排序和合并。无论在哪种情况下，NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历)。为了避免使用NOT IN ，我们可以把它改写成外连接(Outer Joins)或NOT EXISTS。

 

l  用EXISTS替换DISTINCT：

当提交一个包含一对多表信息(比如部门表和雇员表)的查询时，避免在SELECT子句中使用DISTINCT。一般可以考虑用EXIST替换， EXISTS 使查询更为迅速，因为RDBMS核心模块将在子查询的条件一旦满足后，立刻返回结果。

 

l  SQL语句用大写的；因为Oracle总是先解析SQL语句，把小写的字母转换成大写的再执行。

 

l  在Java代码中尽量少用连接符“＋”连接字符串。（编程时要注意）

 

l  避免在索引列上使用NOT通常，我们要避免在索引列上使用NOT, NOT会产生在和在索引列上使用函数相同的影响。当Oracle“遇到”NOT,他就会停止使用索引转而执行全表扫描。（耗去一部分时间）

 

l  避免在索引列上使用计算。WHERE子句中，如果索引列是函数的一部分。优化器将不使用索引而使用全表扫描。（耗去一部分时间）

 

l  用UNION替换OR (适用于索引列)：

通常情况下，用UNION替换WHERE子句中的OR将会起到较好的效果。对索引列使用OR将造成全表扫描。注意，以上规则只针对多个索引列有效。如果有column没有被索引，查询效率可能会因为你没有选择OR而降低。在下面的例子中，LOC_ID 和REGION上都建有索引。

 

l  用WHERE替代ORDER BY：

ORDER BY 子句只在两种严格的条件下使用索引。ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序。 ORDER BY中所有的列必须定义为非空。WHERE子句使用的索引和ORDER BY子句中所使用的索引不能并列。

 

l  优化GROUP BY：

提高GROUP BY 语句的效率，可以通过将不需要的记录在GROUP BY 之前过滤掉。

 

l  用UNION-ALL 替换UNION ( 如果有可能的话)：

当SQL语句需要UNION两个查询结果集合时，这两个结果集合会以UNION-ALL的方式被合并，然后在输出最终结果前进行排序。如果用UNION ALL替代UNION，这样排序就不是必要了。效率就会因此得到提高。需要注意的是，UNION ALL 将重复输出两个结果集合中相同记录。因此各位还是要从业务需求分析使用UNION ALL的可行性。 UNION 将对结果集合排序,这个操作会使用到SORT_AREA_SIZE这块内存。对于这块内存的优化也是相当重要的。

 

3        高效sql语句举例

3.1    表操作

l  最高效的删除重复记录方法：

 

DELETE FROM EMP E WHERE E.ROWID> (SELECT MIN(X.ROWID) FROM  EMP XWHERE X.EMP_NO = E.EMP_NO);

 

l  减少对表的查询，在含有子查询的SQL语句中,要特别注意减少对表的查询。例子：

 

SELECT TAB_NAME FROM TABLES WHERE(TAB_NAME, DB_VER) =(SELECTTAB_NAME, DB_VER FROM TAB_COLUMNS WHERE VERSION =604)

 

3.2    关键字的使用

l  用EXISTS替代IN、用NOT EXISTS替代NOT IN：

 

（高效）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 WHERELOC = 'MELB')

 

l  用EXISTS替换DISTINCT：（有明显的时间节省）

 

(低效):

 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);

 

l  避免在索引列上使用计算。WHERE子句中，如果索引列是函数的一部分。优化器将不使用索引而使用全表扫描。

 

(低效):

SELECT … FROM DEPT WHERE SAL *12 > 25000;

(高效):

SELECT … FROM DEPT WHERE SAL> 25000 / 12;

 

l  用>=替代>：

 

高效：SELECT* FROM  EMP  WHERE DEPTNO >=4

低效: SELECT* FROM EMP WHERE DEPTNO >3

 

l  用UNION替换OR (适用于索引列)：

 

高效：SELECTLOC_ID, LOC_DESC, REGION

 FROM LOCATION

 WHERE LOC_ID = 10

UNION

SELECT LOC_ID, LOC_DESC, REGIONFROM LOCATION WHERE REGION = 'MELBOURNE'

低效: SELECTLOC_ID, LOC_DESC, REGION

 FROM LOCATION

 WHERE LOC_ID = 10

    OR REGION = 'MELBOURNE'

 

l  用IN来替换OR：

 

低效:

SELECT…. FROM LOCATION WHERELOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30

高效：

SELECT… FROM LOCATION WHERELOC_IN IN (10,20,30);

 

l  优化GROUP BY：

 

低效: SELECTJOB , AVG(SAL) FROM EMP GROUP JOB HAVING JOB = 'PRESIDENT' OR JOB = 'MANAGER'

高效: SELECTJOB , AVG(SAL) FROM EMP WHERE JOB = 'PRESIDENT' OR JOB = 'MANAGER' GROUP JOB

 

 

l  用WHERE替代ORDER BY：

 

低效: (索引不被使用)

SELECT DEPT_CODE FROM DEPT ORDERBY DEPT_TYPE

高效: (使用索引)

SELECT DEPT_CODE FROM DEPT WHEREDEPT_TYPE > 0

 

4        总结

以上是根据查阅资料以及平时使用sql语句的经验总结的一些技巧和方法，可能不够全面也不是完全准确，希望大家阅读之后都会有收获，在平时开发的过程中能高效地开发。另外，sql语句是比较灵活的，在开发的过程中也希望大家能根据实际情况来进行灵活编写，不一定完全按照文档中所描述的方法，谢谢！