SQL优化原则和技巧.doc

SQL优化原则和技巧索引的引用1、 索引的删除与重建当插入的数据为数据表中的记录数量的10%以上，首先删除该表的索引来提高数据的插入效率，当数据插入后，在建立索引2、避免在所有列上使用函数或计算，在where字句中，如果索引是函数的一部分，优化器将不再使用索引而是用全表扫描。如：低效：select * from table_name where col1*101000;高效：select * from table_name where col11000/10;3、尽量避免在索引列上使用not和”!=”和”,索引只能告诉你什么存在于表中，而不能告诉你什么不存在于表中，当数据库遇到not和”!=”和”时，就会停止使用索引而去执行全表扫描。请务必注意，检索中不要对索引列进行处理，如:trim,to_date,类型转换等操作，破坏索引，使用全表扫描，影响执行效率。避免在索引列上使用 is null 和 is not null 避免在索引中使用任何可以为空的列，oracle将无法使用索引对于单列索引，如果列包含空值，索引将不存在此记录对于复合索引，如果每列都为空，索引中同样不存在此记录。如果至少有一列不为空，则记录存在于索引中因为空值不存在于索引中，所以where字句中对索引列进行空值比较将使oracle停用该索引4、索引列上”=”代替” 低效：select * from table_name where col110;高效: select * from table_name where cil1=10.000000000001;SQL的优化where字句的连接顺序：oracle采用自下而上的顺序解析where字句，根据这个原理，表之间的连接必须写在其他where条件之前，那些可以过滤掉大量记录的条件必须写在where字句的末尾，例如：低效：select * from table_name t where col15000 and col2=00102 and 25(select count(1) from table_name where code=t.code);高效：select * from table_name t where 255000 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以上为优8089为良6079为中60以下为差获得以下数据 姓名 分数 等级低效的做法：select name，grade，优 from grade where grade=90Union allselect name，grade，良 from grade where grade=80 and grade=60 and grade 80 union allselect name，grade，优 from grade whe