SQL优化原则和技巧.doc

SQL优化原则和技巧
·索引的引用
1、 索引的删除与重建
当插入的数据为数据表中的记录数量的10%以上，首先删除该表的索引来提高数据的插入效率，当数据插入后，在建立索引
2、避免在所有列上使用函数或计算，
在where字句中，如果索引是函数的一部分，优化器将不再使用索引而是用全表扫描。如：
低效：select * from table_name where col1*10>1000;
高效：select * from table_name where col1>1000/10;
3、尽量避免在索引列上使用not和”!=”和”<>”,
索引只能告诉你什么存在于表中，而不能告诉你什么不存在于表中，当数据库遇到not和”!=”和”<>”时，就会停止使用索引而去执行全表扫描。
请务必注意，检索中不要对索引列进行处理，如:trim,to_date,类型转换等操作，破坏索引，使用全表扫描，影响执行效率。
避免在索引列上使用 is null 和 is not null
避免在索引中使用任何可以为空的列，oracle将无法使用索引
对于单列索引，如果列包含空值，索引将不存在此记录
对于复合索引，如果每列都为空，索引中同样不存在此记录。如果至少有一列不为空，则记录存在于索引中
因为空值不存在于索引中，所以where字句中对索引列进行空值比较将使oracle停用该索引
4、索引列上”>=”代替”>”
低效：select * from table_name where col1>10;
高效: select * from table_name where cil1>=10.000000000001;
·SQL的优化
where字句的连接顺序：
oracle采用自下而上的顺序解析where字句，根据这个原理，表之间的连接必须写在其他where条件之前，那些可以过滤掉大量记录的条件必须写在where字句的末尾，例如：
低效：select * from table_name t where col1>5000 and col2=’00102’ and 25<(select count(1) from table_name where code=t.code);
高效：select * from table_name t where 25<(select count(1) from table_name) and col1>5000 and col2=’00102’;
删除全表时，用truncate替代delete
同时注意truncate只能在删除全表时使用，因为truncate是ddl而不是dml。例如删除一个100万行的数据。Truncate table table_name比delete from teble_name至少快1000倍。
尽量多使用commit。
只要有可能就在程序中对每个delete、insert、update操作尽量多使用commit，这样系统性能会因为commit所释放的资源二大大提高
用exit替代in，可以提高查询的效率
低效：select * from table_name where com_code not in（select code from table_name2 where col1=’10101’）;
高效：select * from table_name where not exits (select code from table_name2 where code = table__code and col1=’10101’)
优化group by
提高group by语句的效率，可以将不需要的记录在group by之前过滤掉。例如
低效：select col1 ,avg(col2) from table_name group by col1 having col1=’10101’ or col1=’10102’;
高效：select col1,avg(col2) from table_name where col1=’10101’ or col2=’10102’ group by col1;
避免使用having字句
Having只会在检索出所有记录之后才会对结果集进行过滤，这个处理需要排序、统计等操作。如果能通过where字句限制记录的数目，那就能减少这方面的开销
有条件的使用union-all替代union：
这样做会使效率提高3到5倍
在含有子查询的SQL语句中，要注意减少对表的查询
例如
低效：select sum(col1) from table_name1 where col2 =(select col2 from table_name2 where code=’10101’) and pp=(select pp from table_name2 where code =’10101’);
高效：select sum(col1) from table_name where (col2,pp)=(select col2,pp from table_name2 where code=’10101’);
Update多个column同样适用
select字句中尽量避免使用‘*’
当你想在select字句中 列出所有的column时，使用动态SQL列引用‘*’是一个比较方便的方法，不幸的是，这是一个非常低效的方法，实际上，oracle在解析的过程中，会将*依次转换为所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。
·一些函数的使用技巧
虽然在SQL中盲目引用函数会使性能下降，但如果正确使用合适的函数不仅会使SQL可读性加强，而且能对SQL性能得到提高，使复杂的查询能很方便地实现
例如：有如下学生成绩表（表名称 grade
姓名
分数
张三
78
李四
92
王五
67
赵六
88
钱七
91
陈八
30

我们的需求：
90分或90以上为优
80—89为良
60—79为中
60以下为差
获得以下数据
姓名
分数
等级
低效的做法：select name，grade，’优’ from grade where grade>=90
Union all
select name，grade，’良’ from grade where grade>=80 and grade<90
union all
select name，grade，’优’ from grade where grade>=60 and grade <80
union all
select name，grade，’优’ from grade where grade<60;

高效的做法：select name ,grade decode(sign(grade-90),-1, decode(sign(grade-80),-1 decode(sign(grade-60),-1,’差’,’ 中’,’ 良’,’优’) from grade;