sql优化原则与技巧

xctdlxg 2016-03-14

展开全文

加快sql查询是非常重要的技巧，简单来说加快sql查询的方式有以下几种：一、索引的引用

1、索引一般可以加速数据的检索速度，加速表与表之间的链接，提高性能，所以在对海量数据进行处理时，考虑到信息量比较大，应该对表建立索引，包括在主键上建立聚簇索引，将聚合索引建立在日期刊上等。索引的优点有很多，但是对于索引的建立，还需要考虑实际情况，而不是对每一个列建立一个索引，比如针对大表的分组、排序等字段，都要建立相应索引，同时还应该考虑建立符合索引。增加索引的同时也有很多不好的方面，首先，创建索引和维护索引都耗费时间，且随着数据量的增加而增加；其次，索引占据物理空间，聚簇索引的空间更大。最后，当对表进行增加、删除和修改的时候，索引也要动态的维护。所以索引的引用有以下几个原则：

（1）当插入数据为表中数据的百分之10以上时，首先需要删除该表的索引来提高数据的插入效率，然后再重新建立索引；

（2）避免在索引列上使用函数或计算，在where字句中，如果索引是函数的一部分，优化器将不再使用索引，将使用全表扫描；例如：

select * from table where hh*10>1000;//低效
select * from table where hh>1000/10;//更高效

（3）尽量避免在索引列上使用not，!=和<>，索引只能告诉什么在表中，而不能告诉什么不在表中，当数据库遇上以上几种符号时，将不再使用索引，使用全表扫描

（4）检索中不要对索引进行处理，如TRIM，TO_DATE，类型转换等，这会破坏索引

（5）避免在索引列上使用IS NULL 或 IS NOT NULL。避免在索引列上使用任何可以为空的列，这样将无法使用此索引。因为空值不存在于索引列中，所以当where字句中对索引进行空值比较，将无法使用该索引。

（6）在索引列上，使用>=代替>，比如：

select * from table where hh>10;//低效
select * from table where hh>=10.0000001//相对高效

二、SQL语句的优化

介绍几种SQL语句的优化技巧：

（1）where字句中的链接顺序

oracle采用自下而上的顺序解析where字句，所以表之间的链接必须写在其他where条件之前，那些可以滤过大量纪录的条件必须写在where字句的末尾，例如：

select * from table e
      where h>500     
 and d='001'
 and 25<(select count(*) 
 from table 
 where count=e.count);  //低效

select * from table e
      where  25<(select count(*) 
 from table 
 where count=e.count);    
 and  h>500
 and d='001';//更高效

（2）删除全表时，用truncate而不用delete。因为truncate是ddl不是dml（truncate只能在删除全表时使用），例如：

Truncate table account；
比
delete from account；
快1000倍

（3）尽量多使用commit，只要有可能就对程序中每个delete，insert，update使用commit，这样系统会因为commit所释放的资源而大大提高效率。

（4）用exists代替in，可以提高查询的效率。例如：

SELECT * FROM ACCOUNT
    WHERE AC_CODE 
    NOT IN (
 SELECT CODE
 FROM GOODS
 WHERE NUM='001')       //低效

SELECT * FROM ACCOUNT
    WHERE NOT EXISTS
       （SELECT CODE
 FROM GOODS
 WHERE CODE=ACCOUNT.AC_CODE
 AND NUM='001') //更高效

（5）使用group by

可以将不需要的语句在group by之前过滤掉

（6）避免使用HAVING字句

HAVING字句只有在检索出所有记录之后，才会对结果集进行过滤，这样涉及到排序，统计等操作，如果能通过WHERE字句限制记录的数目，就可以减少开销。用Where子句替换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进行过滤。由此可见，要想过滤条件起到正确的作用，首先要明白这个条件应该在什么时候起作用，然后再决定放在那里。

（7）有条件的使用union-all代替union提高效率

（8）在所有查询的SQL语句中要特别注意减少对表的查询，如：

（9）select字句中避免使用*

当想在select字句中列出所有的列时，使用*是一个很方便，但是很低效的方法，因为在oracle中，会通过查询数据字典，将*依次转换为所有的列名，很耗时

（10）Order by语句

ORDER BY语句决定了Oracle如何将返回的查询结果排序。Order by语句对要排序的列没有什么特别的限制，也可以将函数加入列中(象联接或者附加等)。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。仔细检查order by语句以找出非索引项或者表达式，它们会降低性能。解决这个问题的办法就是重写order by语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在order by子句中使用表达式。

（11）带通配符(%)的like语句

目前的需求是这样的，要求在职工表中查询名字中包含cliton的人。可以采用如下的查询SQL语句:

select*fromemployeewherelast_namelike'%cliton%';

这里由于通配符(%)在搜寻词首出现，所以Oracle系统不使用last_name的索引。在很多情况下可能无法避免这种情况，但是一定要心中有底，通配符如此使用会降低查询速度。然而当通配符出现在字符串其他位置时，优化器就能利用索引。在下面的查询中索引得到了使用:

select*fromemployeewherelast_namelike'c%';

（12）选择最有效率的表名顺序(只在基于规则的优化器中有效)：

ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表driving table)将被最先处理，在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询,那就需要选择交叉表(intersection table)作为基础表,交叉表是指那个被其他表所引用的表。

（13）整合简单,无关联的数据库访问：如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系)

（14）删除重复记录：

最高效的删除重复记录方法(因为使用了ROWID)例子：

DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID)

FROM EMP X WHERE X.EMP_NO = E.EMP_NO);

（15）使用表的别名(Alias)：

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

（16）sql语句用大写的；因为oracle总是先解析sql语句，把小写的字母转换成大写的再执行（17）在java代码中尽量少用连接符“＋”连接字符串！

（18）总是使用索引的第一个列：如果索引是建立在多个列上,只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引.这也是一条简单而重要的规则，当仅引用索引的第二个列时,优化器使用了全表扫描而忽略了索引

三、函数的使用技巧

虽然有时肆意的使用函数，会将性能降低，但是用好函数有时也能提高性能和可读性。如：