SQL优化的一些实用方法

SQL优化的一些使用方法

1. 任何在where字句中使用is null 或 is not null的语句优化器是不允许使用索引的。因为只有该字段中有null值，即使创建了索引其实也是没有用的，所以创建索引应该在不为空的字段上创建；
2. 使用该sql语句将不会使用索引： select * from employee where last_name like ‘%cim%’;
这样的话会使用索引select * from employee where last_name like ‘cim%’;
3. Order by语句
Order by语句决定了oracle如果将返回的查询结果排序。Order by语句对要排序的列没有什么特别的限制，也可以将函数加入列中(象连接或者附加等)。任何在order by语句的非索引项或者有计算表达式都将降低查询速度。仔细检查order by 语句以找出非索引项或者表达式，它们会降低性能，解决这个问题的办法就是重写order by 语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在order by 子句中使用表达式。
4. 选择最有效率的表名顺序（只在基于规则的优化器中有效）：
Oracle的解析器按照从右到左的顺序处理from子句中的表名，from子句中写在最后的表（基础表driving table）将最先处理，在form子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表，如果有三个以上的表连接查询，那就需要选择交叉表(intersection table)作为基础表，交叉表是指哪个被其他表所引用的表。
5. Select子句中避免使用*
Oracle在解析的过程中，会将’*’依次转换成所有的列名，这个工作时通过查询字典完成的， 意味着将耗费更多的时间。
6. 减少访问数据库的次数，能使用一条sql语句查询出来的最好使用一条语句直接查询出来，整合简单，无关联的数据库访问：
如果你有几个简单的数据库查询语句，你可以把它们整合到一个查询中（即使他们之间没有关系）
7. 通过内部函数提高SQL效率：复杂的SQL往往牺牲了执行效率，能够掌握上面的运用函数解决问题的方法在实际工作中是非常有意的
8. 使用表的别名(Alias):
当在SQL语句中连续多个表时，请使用表的别名并把别名前缀于每个Column上。这样一来，就可以减少解析的时间并减少那些由Column歧义引起的语法错误
9. 用Exists替代IN，用NOT EXISTS 替代NOT IN
在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行连接，在这种情况下，使用EXISTS（或NOT EXISTS）通常将提高查询的效率。在子查询中，NOT IN都是最低效的（因为它对子查询中的表执行了一个全表遍历），为了避免使用NOT IN，我们可以把它改写成外链接(Outer Joins)或NOT EXISTS

例如：(低效)select * from z_shangbiao u where u.ann_nnum!=’’ and u.ann_nnum in(select e.user_account from t_evaluation e)
(高效)select * from t_user u where u.account!=’’ and EXISTS(select e.user_account from t_evaluation e where u.account=e.user_account)
10. 用索引提高效率：虽然使用索引能得到查询效率的提高，但是我们也必须注意到它的代价，索引需要空间来存储，也需要定期维护，每当有记录在表中增减或索引列被修改时，索引本身也会被修改，这意味着每条记录的insert，delete，update将为此多付出4,5次的磁盘I/O，因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。定义的重构索引是有必要的。
11. 用Exists替换Distinct：
当提交一个包含一对多表信息（比如部门表和员工表）的查询时，避免在select子句中使用distinct，一般可以考虑用exists替换，exists使查询更为迅速，因为RDBMS核心模块将在子查询的条件一旦满足后，立刻返回结果：
(低效):SELECT DISTINCT DEPT_NO,DEPT_NAME FORM DEPT D,EMP P WHERE D.DEPT_NO=E.DEPT_NO
(高效):SELECT DEPT_NO,DEPT_NAME FORM DEPT D WHERE EXISTS (SELECT X FROM EMP E WHERE E.DEPT_NO=D.DEPT_NO);
12. SQL语句用大写的：因为ORACLE总是先解析SQL语句，把小写的字母转换成大写的再执行
13. 避免在索引列上使用计算：
WHER子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全表扫描
举例：
低效：SELECT … FROM DEPT WHERE SAL * 12 >25000;
高效：SELECT … FORM DEPT WHERE SAL > 25000/12;
14. 用>=替代>

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

如果坚持要用OR，那就需要返回记录最少的索引列卸载最前面
16. 总是使用索引的第一个列：
如果索引是建立在多个列上，只有在它的第一个列(leading colum)被where子句引用时，优化器才会选择使用该索引。这也是一条简单而重要的规则，当仅引用索引的第二个列时，优化器使用了全表扫描而忽略了索引

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

18. 避免改变索引列的类型
比如age字段为int型，然后你查询的时候写where age =’13’，虽然查询出来的结果是一样的，但是效率会降低，因为数据库内部多做了操作，自动对字符13进行了TO_NUMBER(‘13’)处理
19. 需要当心的WHERE子句
某些SELECT语句中的WHERE子句不使用索引。这里有一些例子。
在下面的例子里
a.!=将不使用索引，记住，索引只能告诉你什么存在于表中，而不能告诉你什么不存在于表中
b.||是字符连接函数，就像其他函数那样，停用了索引
c.+是数学函数，就像其他数学函数那样，停用了索引
d.相同的索引列不能互相比较，这将会启用全表扫描
20. 优化GROUP BY，尽量使用WHERE代替HAVING
提高GROUP BY 语句的效率，可以通过将不需要的记录在GROUP BY之前过滤掉，下面两个查询返回相同结果，但是第二个明显就快了许多

21. 不要使用SELECT*
这点不太容易做到，可是，如果在SELECT中指定你所需要的列，那将会带来一下的好处：
a. 减少内存耗费和网络的宽带
b. 可以得到更安全的设计
c. 给查询优化器机会从索引读取所有需要的列