SQL优化.

SQL优化3.基于数据库的SQL优化1.SQL语句书写注意事项2.基于程序的SQL优化SQL语句书写注意事项1、ISNULL或ISNOTNULL操作(判断字段是否为空)判断字段是否为空一般是不会应用索引的，因为B树索引是不索引空值的。如：selectidfromtwherenumisnull可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：selectidfromtwherenum=0SQL语句书写注意事项2、操作符(不等于)不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。可以用其它相同功能的操作运算代替，如a0改为a0ora0a''改为a''SQL语句书写注意事项3、LIKE操作符LIKE操作符可以应用通配符查询，里面的通配符组合可能达到几乎是任意的查询，但是如果用得不好则会产生性能上的问题。如我们使用名称对公文实力表进行查询时如果使用namelike'%2012%'或者namelike'%2012'来查询则会产生全表扫描。而如果使用namelike'2012%'来进行查询时则会用到那么列上的索引。SQL语句书写注意事项4、慎用in和notin因为IN会使系统无法使用索引,而只能直接搜索表中的数据。如select*frommv_workitemawherea.receiver_idin(1214,1213,1215)如果在oracle中对mv_workitem做了表分析，则oracle优化器会将该表改写为：select*frommv_formset_instawherea.receiver=1213ora.receiver=1214ora.receiver=1215改写前的语句无法用到索引，而改写后的语句则可以用到receiver列的索引。基于性能考虑我们可以在程序中对此类语句进行改写，而不是依赖数据库特性来完成。SQL语句书写注意事项5、不要在where子句中的“=”左边进行函数、算术运算或其他表达式运算应尽量避免在where子句中对字段进行函数、算术运算或其他表达式运算的操作，这些操作将导致引擎放弃使用索引而进行全表扫描如SELECT*FROMT1WHEREF1/2=100SELECT*FROMRECORDWHERESUBSTRING(CARD_NO,1,4)=’5378’应该将此类操作放到“=”右边进行改写后如下：SELECT*FROMT1WHEREF1=100*2SELECT*FROMRECORDWHERECARD_NOLIKE‘5378%’SQL语句书写注意事项6、复合索引的使用限制createindexInx_mwi_receiveronmv_workitem(receiver_id,receiver);索引Inx_mwi_receiver为复合索引，对mv_workitem表的receiver_id与receiver列进行索引，其中receiver_id列为此复合索引的引导列如果查询时条件中没有附带引导列进行查询则该索引失效，如selectmv_workitemwherereceiver_id=1213;selectmv_workitemwherereceiver='张三';selectmv_workitemwherereceiver_id=1213andreceiver='张三';上述三条语句中第一与第三条语句能用到索引，而第二条语句无法使用索引。SQL语句书写注意事项7、使用exists替代in操作符electnumfromawherenumin(selectnumfromb)与：selectnumfromawhereexists(select1frombwherenum=a.num)两条语句产生相同的结果，但是后者的效率显然要高于前者。因为后者不会产生大量锁定的表扫描或是索引扫描。SQL语句书写注意事项8、避免使用不一致的数据类型进行查询如：select*fromt_cm_info_instawherea.infoid=1213;select*fromt_cm_info_instawherea.infoid=‘1213’;t_cm_info_inst表中的infoid为字符类型，此两条语句运行的结果相同，但是第一条语句会是数据库引擎做全表扫描，而第二条语句则会用到infoid列的索引。SQL语句书写注意事项9、使用列名而不是*进行表查询如：select*fromt_cm_info_inst;selectinfoid,infotitle,....fromt_cm_info_inst;此两条语句查询时第二条语句的性能高于第一条语句的性能，因为第一条语句数据引擎在解析时会从字典表中将t_cm_info_inst表的列名全部找出来后改写为第二条语句再运算结果集，这个过程是有性能消耗的。在程序中用到多少列就查询多少列，而不是没有选择性的使用*进行语句查询。SQL语句书写注意事项10、能用UNIONALL就不要用UNION如：select*frommv_workitemawherea.receiver_id=1213unionallselect*frommv_workitemawherea.receiver_id=1214与select*frommv_workitemawherea.receiver_id=1213unionselect*frommv_workitemawherea.receiver_id=1214上述两条语句得到的结果集一致，但是第二条语句的效率高于第一条语句，因为UNION操作符会有一个重复值判断的过程，而UNIONALL操作符连接两个语句查询出来的所有结果集SQL语句书写注意事项11、灵活运用视图把表的一个子集进行排序并创建视图，有时能加速查询。它有助于避免多重排序操作，而且在其他方面还能简化优化器的工作。视图是预编译的，每一条语句在运行时数据库引擎都会对语句进行解析编译后存放在内存中，如果将一些复杂的语句做成视图则减少了编译语句的过程，能加快sql语句运行速度。3.基于数据库的SQL优化1.SQL语句书写注意事项2.基于程序的SQL优化基于程序的SQL优化1、不要在循环中作sql语句查询操作，最好能一次将需要的数据查询出来后再用循环进行比对。2、能一次查询出结果集就不要分开成多个语句进行查询，因为java每执行一次sql语句包括打开连接，执行语句，获取结果集，关闭连接等等一系列操作，这些操作都是需要消耗性能的。3、对于大表可以在程序中作分表操作。如log_table表我们可以根据日志产生的年度将日志存放到不同的表中，2010年的记录可以放到log_table_2010表中，2011年产生的记录可以存放到log_table_2011表中，查询时对不同年度日志则查询不同年度的表，这样操作可以避免一张表中存放太多记录导致sql语句执行缓慢。基于程序的SQL优化4、多表关联查询时时尽量减少关联表的结果集如：selectcount(*)ascol_0_0_fromT_CM_INFO_INSTinfo0_,T_CM_INFO_CLASS_DEFinfoclass1_whereinfo0_.CLASSID=infoclass1_.CLASSIDandinfo0_.INFOSTATE=2and(info0_.INFOIDin(selectinforights2_.INFOIDfromT_CM_INFO_RIGHTSinforights2_where(actID=?oractID=?oractID=?oractID=?oractID=?oractID=?oractID=?oractID=?oractID=?oractID=?oractID=?oractID=?oractID=?oractID=?)and(inforights2_.CLASSID=?orinforights2_.CLASSID=?orinforights2_.CLASSID=?))orinfo0_.AUTHORID=?and(info0_.CLASSID=?orinfo0_.CLASSID=?orinfo0_.CLASSID=?))and(isDeleteisnull)修改后的语句如下：selectcount(*)ascol_0_0_fromT_CM_INFO_INSTinfo0_,T_CM_INFO_CLASS_DEFinfoclass1_whereinfo0_.CLASSID=infoclass1_.CLASSIDandinfo0_.INFOSTATE=2and(info0_.INFOIDin(selectinforights2_.INFOIDfromT_CM_INFO_RIGHTSinforights2_where(actID=?oractID=?oractID=?oractID=?oractID=?oractID=?oractID=?oractID=?oractID=?oractID=?oractID=?oractID=?oractID=?oractID=?)and(inforights2_.CLASSID=?orinforights2_.CLASSID=?orinforights2_.CLASSID=?))orinfo0_.AUTHORID=?and(info0_.CLASSID=?orinfo0_.CLASSID=?orinfo0_.CLASSID=?))and(isDeleteisnull)and(infoclass1_.CLASSID=?orinfoclass1_.CLASSID=?orinfoclass1_.CLASSID=?)3.基于数据库的SQL优化1.SQL语句书写注意事项2.基于程序的SQL优化基于数据库的SQL优化1、创建索引1、对于经常查询的表需要做索引，索引包括唯一索引、普通索引、组合索引、位图索引。2、如果表中的某个列的不同数据过少（如性别列，只有男女时两种情况，每种占50%的数据量），且表中的数据超过100W以上，则对该列创建的索引会失效，这时就可以考虑位图索引。3、在分析表的查询计划时，如果遇到全表扫描的情况，且查询使用频繁就可以对查询条件列创建索引。基于数据库的SQL优化2、对频繁发生删除操作的表定期进行收缩表操作oracle表中的数据时存在一个个数据块中的，随着表数据的增长，该表占用的数据块也在增加，而表中数据删除时，oracle会删除数据，但是不会回收已删除数据所占用的数据块，所以要定期做收缩表操作。检查表的数据库空闲大小，一般每个数据块默认空闲大小为2K（特指在创建表空间时指定的数据块大小为8K左右的），如果超过2k则说明该表数据量有删除，而该表当前的数据量不够分布所占用的数据块，导致每个块的数据量过小，此时就可以做收缩表的操作，一下依据查询数据块空闲比较大的表，收缩时要注意该表是否存在longraw（blobclob）类型的字段基于数据库的SQL优化2、对频繁发生删除操作的表定期进行收缩表操作检查语句为：selecta.table_name,a.avg_space,a.blocksfromdba_tablesawherea.avg_space2000orderbya.avg_spacedesc;收缩表语句为：---将该表的归档日志关闭altertableJ_C_JOURNALnologging;---更改表为可删除状态altertableJ_C_JOURNALenablerowmovement;---收缩表的占用空间altertableJ_C_JOURNALshrinkspace;---如果表有lob字段则需要对lob字段单独进行回收操作altertableJ_C_JOURNALmodifylob(REVISION_DATA)(shrinkspace);---重建表索引alterindexJ_C_JOURNAL_IDXrebuild;---将表的归档日志打开altertableJ_C_JOURNALlogging;基于数