sql优化总结(一)

1.对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。char(100) 型 null 值占空间；varchar类型 null不占空间。可以在num上设置默认值0，确保表中num列没有null值。
3.应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。
4.应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描。
举个栗子：

select id from t where num=10 or Name = 'admin'

可以这样查询：

select id from t where num = 10
union all
select id from t where Name = 'admin'

5.in 和 not in 也要慎用，否则会导致全表扫描
举个栗子：

select id from t where num in(1,2,3)

between and 代替 in

select id from t where num between 1 and 3

6.exists 代替 in

select num from a where num in(select num from b)

7.首先尽量避免模糊查询，如果因为业务需要一定要使用模糊查询，则至少保证不要使用全模糊查询，对于右模糊查询，即like‘…%’，是会使用索引的；左模糊like ‘%…’无法直接使用索引，但可以利用reverse + function index的形式，变化成like‘…%’；全模糊是无法优化的，一定要的话考虑用搜索引擎。出于降低数据库服务器的负载考虑，尽可能地减少数据库模糊查询。

8.如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。
应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。

select id from t where num/2 = 100

应改为

select id from t where num = 100*2

9.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。

select id from t where substring(name,1,3) = ’abc’       -–name以abc开头的id
select id from t where datediff(day,createdate,’2005-11-30′) = 0    -–‘2005-11-30’    --生成的id

应改为:

select id from t where name like 'abc%'
select id from t where createdate >= '2005-11-30' and createdate < '2005-12-1'

10.Update 语句，如果只更改1、2个字段，不要Update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志。
索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要。

11.应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。
13.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连 接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
14.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
15.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。
如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。
16.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
17.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。
18.用Where子句替换HAVING子句,避免使用HAVING子句，HAVING 只会在检索出所有记录之后才对结果集进行过滤。
19.减少对表的查询,在含有子查询的SQL语句中,要特别注意减少对表的查询。

SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECTTAB_NAME,DB_VER FROM TAB_COLUMNS WHERE VERSION = 604)

20.用EXISTS替代IN、用NOT EXISTS替代NOT IN：

在许多基于基础表的查询中，为了满足一个条件,往往需要对另一个表进行联接。在这种情况下，使用EXISTS(或NOT EXISTS)通常将提高查询的效率。在子查询中，NOT IN子句将执行一个内部的排序和合并。无论在哪种情况下，NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历)。为了避免使用NOT IN ，我们可以把它改写成外连接(Outer Joins)或NOT EXISTS。
高效

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')

21.WHERE子句中的连接顺序。Oracle采用自下而上的顺序解析WHERE子句,根据这个原理,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。
举个栗子：
(低效,执行时间156.3秒)

SELECT … 
FROM EMP E
WHERE  SAL > 50000
AND    JOB = ‘MANAGER’
AND    25 < (SELECT COUNT(*) FROM EMP
             WHERE MGR=E.EMPNO);

(高效,执行时间10.6秒)

SELECT … 
FROM EMP E
WHERE 25 < (SELECT COUNT(*) FROM EMP
             WHERE MGR=E.EMPNO)
AND    SAL > 50000
AND    JOB = ‘MANAGER’;

22.选择最有效率的表名顺序。查询时，如果基表（from最后面的表）,数据库语句处理from后面的语句时，是从右侧往
左侧处理的，那么选择数据量最小的表作为基表，可加快查询速度，同时，如果三张表，中间的那个交叉表，作为基表。
例如：A，1万条， B，10万条， C，100万条数据。
关联条件： A.id=B.id and C.sid=B.sid此时，B就是交叉表。
查询时： select * from C,A,B where A.id=B.id and C.sid=B.sid (此时，B作为基表出现。)
23.SQL语句尽量都大写字母出现。
24.sql语句，尽量较少对数据库表的访问次数。
这里介绍两种：
（1）子查询时，没用到组函数的情况下，可以使用外连接做，这样可以较少对表访问次数。

(2) 一般用于统计时， 用到了组函数，每次统计数据时都分了很多情况，此时可考虑用
case when then end来做。
下面查询了一个不及格率：效率低下。

select a1/a2 from (select count(*)  a1 from score where scoreNum<60 and clno=111) B1, (select count(distinct stno)  a2 from score where clno=111) B2

下面效率高： （如果是每个课程的，那么直接加group by clno即可）

select round(sum(case when scorenum <60 then 1 else 0 end)/count(distinct stno),2), round(sum(case when scorenum >=60 then 1 else 0 end)/count(distinct stno),2 )  from score where  clno=111

25.针对多个索引列出现在where子句的or条件下时：使用union比or效率高：（使用or时，这些列忽略了索引）

26.在对索引列使用to_char 或者to_number函数时， 此时索引失效。
例如：select * from emp where empno=’10001’ 这种隐士转化，对索引无影响。
select * from emp where empno=to_number(‘10001’) 对非索引列使用转换函数，对索引无影响。
select * from emp where to_char(empno)=’10001’ 此时，如果该empno是索引列，该索引失效。

27.distinct 去重复效率低下： 可以通过exists实现：
select distinct d.dname from dept d,emp e where d.deptno=e.deptno （效率低下）

select d.dname from dept d where exists(select ‘x’ from emp e where e.deptno=d.deptno) （效率高）
28.使用>= 替代>
例如： select * from emp where deptno>=10(效率高，因为depto直接定位到10，效率高)
select * from emp where deptno>9(先定位到9 ，然后还要排除9 ，效率低)

29.如果使用union或者union all了，此时如果不需要考虑去掉重复的数据，尽量不要使用union ，因为union默认是去重复的
在去重复过程中，也会影响效率。
30.使用视图： （数据量非常大的情况下）
经常被查询的列数据，并且这些数据不被经常的修改，删除。
31.使用DECODE函数来减少处理时间：
使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.
decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)

该函数的含义如下：
IF 条件=值1 THEN
　　　　RETURN(翻译值1)
ELSIF 条件=值2 THEN
　　　　RETURN(翻译值2)
　　　　……
ELSIF 条件=值n THEN
　　　　RETURN(翻译值n)
ELSE
　　　　RETURN(缺省值)
END IF

http://blog.csdn.net/e3002/article/details/1817941
http://blog.csdn.net/gprime/article/details/1687930