SQL优化

一、创建索引可以大大提高系统的性能。

优点：

通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

可以大大加快 数据的检索速度，这也是创建索引的最主要的原因

可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

在使用分组和排序 子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。 

通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能

什么情况适合创建索引：

在经常需要搜索的列上，可以加快搜索的速度；

在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；

在经常用在连接的列上，这 些列主要是一些外键，可以加快连接的速度；

在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；

在经常需要排序的列上创 建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；

在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度

二、sql提高性能

要使用索引需要注意的情况
1.IS NULL与 IS NOT NULL
不能用null作索引，任何包含null值的列都将不会被包含在索引中。
任何在where子句中使用IS NULL与 IS NOT NULL的语句优化器是不允许使用索引的
2.联接列
对于有连接的列，即使最后的联接值为一个静态值，优化器是不会使用索引的。
select * from employss where first_name||''||last_name ='Beill Cliton'; 这种情况last_name创建的索引没有使用
*** where first_name ='Beill' and last_name ='Cliton';last_name创建的索引就被使用了。
3.带有通配符（%）的like语句
select * from employee where last_name like '%cliton%';
这里由于通配符(%)在搜寻词首出现，所以Oracle系统不使用last_name的索引。
select * from employee where last_name like 'c%';
然而当通配符出现在字符串其他位置时，优化器就能利用索引
4.ORDER BY语句
任何在ORDER BY语句的非索引项或者有计算表达式都将降低查询速度。解决这个问题的办法就是重写ORDER BY语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在order by子句中使用表达式
5.NOT
我们在查询时经常在where子句使用一些逻辑表达式，如大于、小于、等于以及不等于等等，也可以使用and(与)、or(或)以及not(非)。NOT可用来对任何逻辑运算符号取反。
.. where not (status ='VALID')
如果要使用NOT，则应在取反的短语前面加上括号，并在短语前面加上NOT运算符
NOT运算符包含在另外一个逻辑运算符中，这就是不等于(<>)运算符。换句话说，即使不在查询where子句中显式地加入NOT词，NOT仍在运算符中
... where status <>'INVALID';
select * from employee where salary<3000 or salary>3000;
虽然这两种查询的结果一样，但是第二种查询方案会比第一种查询方案更快些。第二种查询允许Oracle对salary列使用索引，而第一种查询则不能使用索引。


写出优性能的SQL
1.选择最有效率的表名顺序
ORACLE 的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表 driving table)将被最先处理，在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表
2.WHERE子句中的连接顺序
ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。
3.SELECT子句中避免使用 ‘ * ‘
ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间。
4.尽量多使用COMMIT
只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少:
5.用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  WHERE  LOC = ‘MELB') 
6.使用索引
索引是表的一个概念部分,用来提高检索数据的效率，ORACLE使用了一个复杂的自平衡B-tree结构. 通常,通过索引查询数据比全表扫描要快. 当ORACLE找出执行查询和Update语句的最佳路径时, ORACLE优化器将使用索引. 同样在联结多个表时使用索引也可以提高效率. 另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证.
7.用EXISTS替换DISTINCT： 
当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT. 一般可以考虑用EXIST替换, EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果
(低效): 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);
8.sql语句用大写的；因为oracle总是先解析sql语句，把小写的字母转换成大写的再执行
9.在java代码中尽量少用连接符“＋”连接字符串
10.避免在索引列上使用NOT通常我们要避免在索引列上使用NOT,NOT会产生在和在索引列上使用函数相同的影响. 当ORACLE”遇到”NOT,他就会停止使用索引转而执行全表扫描
11.避免在索引列上使用计算
WHERE子句中，如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描． 
低效：SELECT … FROM  DEPT  WHERE SAL * 12 > 25000; 
高效: SELECT … FROM DEPT WHERE SAL > 25000/12;
12.用>=替代> 
高效: SELECT * FROM  EMP  WHERE  DEPTNO >=4 
低效: SELECT * FROM EMP WHERE DEPTNO >3 
13.用UNION替换OR (适用于索引列) 
通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描. 注意, 以上规则只针对多个索引列有效. 如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低. 在下面的例子中, LOC_ID 和REGION上都建有索引.
高效: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 
      UNION 
      SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE REGION = “MELBOURNE” 
低效: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 OR REGION = “MELBOURNE” 
如果你坚持要用OR, 那就需要返回记录最少的索引列写在最前面.
14.用IN来替换OR
低效: SELECT…. FROM LOCATION WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30 
高效: SELECT… FROM LOCATION WHERE LOC_IN  IN (10,20,30);
15.总是使用索引的第一个列
如果索引是建立在多个列上,只有在它的第一个列(leadingcolumn)被where子句引用时,优化器才会选择使用该索引. 这也是一条简单而重要的规则，当仅引用索引的第二个列时,优化器使用了全表扫描而忽略了索引 
16.需要当心的WHERE子句
‘!=' 将不使用索引. 记住, 索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中
‘ ¦ ¦'是字符连接函数. 就象其他函数那样, 停用了索引
‘+'是数学函数. 就象其他数学函数那样, 停用了索引
相同的索引列不能互相比较,这将会启用全表扫描.
17.a如果检索数据量超过30%的表中记录数.使用索引将没有显著的效率提高.                   b在特定情况下,使用索引也许会比全表扫描慢,但这是同一个数量级上的区别.而通常情况下,使用索引比全表扫描要块几倍乃至几千倍!
18.优化GROUP BY，提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉
低效: SELECT JOB , AVG(SAL) FROM EMP GROUP by JOB HAVING JOB = ‘PRESIDENT' OR JOB = ‘MANAGER' 
高效: SELECT JOB , AVG(SAL) FROM EMP WHERE JOB = ‘PRESIDENT' OR JOB = ‘MANAGER' GROUP by JOB 
19.使用函数
如果不使用基于函数的索引，那么在SQL语句的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);
20.比较不匹配的数据类型
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';