SQL优化的几种方法

1、对查询进行优化，应尽量避免全表扫描，首先考虑在where及order by上建立索引。

2、应尽量避免在where子句中进行以下操作：对字段进行null判断；使用！=或<>操作符；使用or连接条件；使用in或not in；使用like；等号左侧使用算术运算；对字段进行函数运算等。以上操作将导致引擎放弃索引而进行全表扫描。

3、不要写一些没有意义的查询，如生成一个空表。

4、使用exists替代in，用not exists替代not in。not in 是低效的，因为它对子查询中的表执行了一个全表遍历，他执行了一个内部的排序和合并。select num from a where exists（select 1 from b where num=a.num）

5、对只含数值信息的字段尽量使用数值型代替字符型，否则会降低查询和连接性能。

6、尽可能使用varchar代替char，节约存储空间，提高效率。

7、尽量用具体字段代替*进行查询。

8、在使用索引字段作为条件时，如果索引是复合索引，必须使用该索引的第一个字段作为条件才能保证系统使用该索引。

9、当索引中有大量重复数据时，索引是无效的。

10、当进行update或insert操作时，索引的存在会降低该操作的效率。

11、尽量避免频繁创建或删除临时表，减少系统资源消耗。

12、在新建临时表时，如果一次性插入数据量很大，那么可以使用select into代替create table，避免产生大量log，提高效率。

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

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

15、对于小型数据集使用fast_forward游标要优于其他逐行处理方法，尤其是在必须引用几个表才能获取所需要的数据时。

16、表名顺序。选择最有效率的表名顺序，from后面先跟大表，再跟小表，因为from子句中写在最后的表被优先处理，from后跟多个表的情况下，应该选择记录条数最少的表作为优先处理的表；

17、where子句连接顺序。那些可以过滤最大数量记录的条件必须写在where子句的末尾；

18、高效删除重复记录。最高效的删除重复记录方法 ( 因为使用了ROWID)例子：delete from emp a where a.rowid>(select min(b.rowid) from emp b where a.emp_no=b.emp_no);

19、使用truncate替代delete。当用delete删除表中记录时，回滚段rollback用来被存放可以被恢复的信息，如果你不执行commit，oracle会将数据恢复到删除之前的状态；当运行truncate时，回滚段不再存放任何可被恢复的信息，当运行truncate时，数据不再被恢复，此时很少的资源被调用，执行时间也会很短；

20、尽量多使用commit。随着commit的多次使用，系统资源被释放，性能会提高；

21、用where子句替换having子句。having只会在检索出所有记录之后才对结果集进行过滤。on、where、having这三个都是删选条件的子句，on最先执行，where次之，hiving最后；on先把不符合条件的记录过滤才进行统计，它可以减少中间运算要处理的数据，on的使用仅限于多表连接；where也是过滤数据后才进行sum；hiving是在计算之后才启作用；

22、使用表的别名。当sql语句中连接多个表时，请使用表的别名并用别名前缀识别每个column，这样可以减少sql解析时间，避免歧义；

23、用>=替代>

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

前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录。

24、用union all替换union。当SQL语句需要UNION两个查询结果集合时，这两个结果集合会以UNION-ALL的方式被合并，然后在输出最终结果前进行排序。如果用UNION ALL替代UNION，这样排序就不是必要了。效率就会因此得到提高。

25、当使用join（inner join或left join）操作时，就应该是小表在前，大表在后。把重复关联键少的表放在join前面做关联可以提高join的效率。