SQL优化法则

待我称王封你为后i 2022-03-10 15:01 231阅读 0赞

1.尽量少用IN操作符，基本上所有的IN操作符都可以用EXISTS代替。  
原因：这篇文章讲解的生动形象。http://www.cnblogs.com/iceword/archive/2011/02/15/1955337.html  
2.不用NOT IN操作符，可以用NOT EXISTS或者外连接+替代。  
原因：因为NULL值并没有被定义。在SQL语句中使用NULL会有很多的麻烦。  
因此建议开发人员在建表时，把需要索引的列设成NOT NULL。 如果被索引的列在某些行中存在NULL值，就不会使用这个索引（除非索引是一个位图索引，详情请查阅oracle中的位图索引资料）。  
3.不用“<>”或者“!=”操作符。对不等于操作符的处理会造成全表扫描，可以用“<” or “>”代替。  
原因：下面的查询即使在cust\_rating列有一个索引，查询语句仍然执行一次全表扫描。  
select cust\_Id,cust\_name  
from customers  
where cust\_rating <> 'aa';  
把上面的语句改成如下的查询语句，这样，在采用基于规则的优化器而不是基于代价的优化器（更智能）时，将会使用索引。  
select cust\_Id,cust\_name  
from customers  
where cust\_rating < 'aa' or cust\_rating > 'aa';  
特别注意：通过把不等于操作符改成OR条件，就可以使用索引，以避免全表扫描  
4.应该使用索引的第一个列  
如果索引是建立在多个列上 , 只有在它的第一个列 (leading column) 被 where 子句引用时 , 优化器才会选择使用该索引 .  
这也是一条简单而重要的规则，当仅引用索引的第二个列时 , 优化器使用了全表扫描而忽略了索引  
5.Where子句中出现IS NULL或者IS NOT NULL时，Oracle会停止使用索引而执行全表扫描。  
办法：可以考虑在设计表时，对索引列设置为NOT NULL。这样就可以用其他操作来取代判断NULL的操作。  
\------------------------------------------------------------------------------------  
查询表中存在的索引  
select \* from user\_indexes where table\_name = 'table\_name';  
6.当通配符“%”或者“\_”作为查询字符串的第一个字符时，索引不会被使用。  
7.对于有连接的列“||”，最后一个连接列索引会无效。尽量避免连接，  
可以分开连接或者使用不作用在列上的函数替代。  
8.如果索引不是基于函数的，那么当在Where子句中对索引列使用函数时，索引不再起作用。  
select empno,ename,deptno  
from emp  
where trunc(hiredate)='01-MAY-81';  
把上面的语句改成下面的语句，这样就可以通过索引进行查找。  
select empno,ename,deptno  
from emp  
where hiredate<(to\_date('01-MAY-81')+0.9999);  
9.Where子句中避免在索引列上使用计算，否则将导致索引失效而进行全表扫描。  
10.对数据类型不同的列进行比较时，会使索引失效。  
比较不匹配的数据类型也是比较难于发现的性能问题之一。  
注意下面查询的例子，account\_number是一个VARCHAR2类型，在account\_number字段上有索引。下面的语句将执行全表扫描。  
select bank\_name,address,city,state,zip  
from banks  
where account\_number = 990354;  
Oracle可以自动把where子句变成to\_number(account\_number)=990354，这样就限制了索引的使用,改成下面的查询就可以使用索引：  
select bank\_name,address,city,state,zip  
from banks  
where account\_number ='990354';  
特别注意：不匹配的数据类型之间比较会让Oracle自动限制索引的使用，即便对这个查询执行Explain Plan也不能让您明白为什么做了一次“全表扫描”。  
11.用“>=”替代“>”。  
12.UNION操作符会对结果进行筛选，消除重复，数据量大的情况下可能会引起磁盘排序。  
如果不需要删除重复记录，应该使用UNION ALL。  
13.Oracle从下到上处理Where子句中多个查询条件，所以表连接语句应写在其他Where条件前，  
可以过滤掉最大数量记录的条件必须写在Where子句的末尾。  
14.Oracle从右到左处理From子句中的表名，所以在From子句中包含多个表的情况下，  
将记录最少的表放在最后。  
15.Order By语句中的非索引列会降低性能，可以通过添加索引的方式处理。  
严格控制在Order By语句中使用表达式。  
16.不同区域出现的相同的Sql语句，要保证查询字符完全相同，以利用SGA共享池，  
防止相同的Sql语句被多次分析。  
17.当在Sql语句中连接多个表时，使用表的别名，并将之作为每列的前缀。这样可以减少解析时间。  
18.SELECT 子句中避免使用 ‘ \* ‘ ：  
ORACLE 在解析的过程中 , 会将 '\*' 依次转换成所有的列名 , 这个工作是通过查询数据字典完成的 , 这意味着将耗费更多的时间  
19.sql 语句用大写的 ；因为 oracle 总是先解析 sql 语句，把小写的字母转换成大写的再执行  
20.在 java 代码中尽量少用连接符“＋”连接字符串！  
21.优化 GROUP BY:  
提高 GROUP BY 语句的效率 , 可以通过将不需要的记录在 GROUP BY 之前过滤掉 . 下面两个查询返回相同结果但第二个明显就快了许多 .  
低效 :  
SELECT JOB , AVG(SAL)  
FROM EMP  
GROUP JOB  
HAVING JOB = ‘PRESIDENT'  
OR JOB = ‘MANAGER'  
高效 :  
SELECT JOB , AVG(SAL)  
FROM EMP  
WHERE JOB = ‘PRESIDENT'  
OR JOB = ‘MANAGER'  
GROUP JOB  
22. 用 TRUNCATE 替代 DELETE ：  
当删除表中的记录时 , 在通常情况下 , 回滚段 (rollback segments ) 用来存放可以被恢复的信息 . 如果你没有 COMMIT 事务 ,  
ORACLE 会将数据恢复到删除之前的状态 ( 准确地说是 恢复到执行删除命令之前的状况 )而当运用 TRUNCATE 时 ,  
回滚段不再存放任何可被恢复的信息 .  
当命令运行后 , 数据不能被恢复 . 因此很少的资源被调用 , 执行时间也会很短 .( 译者按 : TRUNCATE 只在删除全表适用 ,TRUNCATE 是 DDL 不是 DML)  
23.尽量多使用 COMMIT ：  
只要有可能 , 在程序中尽量多使用 COMMIT, 这样程序的性能得到提高 , 需求也会因为 COMMIT 所释放的资源而减少 :  
COMMIT 所释放的资源 :  
a. 回滚段上用于恢复数据的信息 .  
b. 被程序语句获得的锁  
c. redo log buffer 中的空间  
d. ORACLE 为管理上述 3 种资源中的内部花费