SQL优化规则

SQL优化规则1SQL语句优化技术分析操作符优化:IN操作符用IN写出来的SQL的优点是比较容易写及清晰易懂，这比较适合现代软件开发的风格。但是用IN的SQL性能总是比较低的，从ORACLE执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别：ORACLE试图将其转换成多个表的连接，如果转换不成功则先执行IN里面的子查询，再查询外层的表记录，如果转换成功则直接采用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程。一般的SQL都可以转换成功，但对于含有分组统计等方面的SQL就不能转换了。推荐方案：在业务密集的SQL当中尽量不采用IN操作符。NOTIN操作符此操作是强列推荐不使用的，因为它不能应用表的索引。推荐方案：用NOTEXISTS或（外连接+判断为空）方案代替操作符（不等于）不等于操作符是永远不会用到索引，因此对它的处理只会产生全表扫描。推荐方案：用其它相同功能的操作运算代替，如a0改为a0ora0a’’改为a’’ISNULL或ISNOTNULL操作（判断字段是否为空）判断字段是否为空一般是不会应用索引的，因为B树索引是不索引空值的。推荐方案：用其它相同功能的操作运算代替，如aisnotnull改为a0或a’’等。不允许字段为空，而用一个缺省值代替空值，如业扩申请中状态字段不允许为空，缺省为申请。建立位图索引（有分区的表不能建，位图索引比较难控制，如字段值太多索引会使性能下降，多人更新操作会增加数据块锁的现象）及操作符（大于或小于操作符）大于或小于操作符一般情况下是不用调整的，因为它有索引就会采用索引查找，但有的情况下可以对它进行优化，如一个表有100万记录，一个数值型字段A，30万记录的A=0，30万记录的A=1，39万记录的A=2，1万记录的A=3。那么执行A2与A=3的效果就有很大的区别了，因为A2时ORACLE会先找出为2的记录索引再进行比较，而A=3时ORACLE则直接找到=3的记录索引。LIKE操作符LIKE操作符可以应用通配符查询，里面的通配符组合可能达到几乎是任意的查询，但是如果用得不好则会产生性能上的问题，如LIKE‘%5400%’这种查询不会引用索引，而LIKE‘X5400%’则会引用范围索引。一个实际例子：用YW_YHJBQK表中营业编号后面的户标识号可来查询营业编号YY_BHLIKE‘%5400%’这个条件会产生全表扫描，如果改成YY_BHLIKE’X5400%’ORYY_BHLIKE’B5400%’则会利用YY_BH的索引进行两个范围的查询，性能肯定大大提高。UNION操作符UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录推荐方案：采用UNIONALL操作符替代UNION，因为UNIONALL操作只是简单的将两个结果合并后就返回。SQL书写的影响同一功能同一性能不同写法SQL的影响,完全相同则ORACLE只会分析一次，共享内存也只会留下一次的分析结果，这不仅可以减少分析SQL的时间，而且可以减少共享内存重复的信息，ORACLE也可以准确统计SQL的执行频率。WHERE后面的条件顺序影响WHERE子句后面的条件顺序对大数据量表的查询会产生直接的影响，如Select*fromzl_yhjbqkwheredy_dj='1KV以下'andxh_bz=1Select*fromzl_yhjbqkwherexh_bz=1anddy_dj='1KV以下'以上两个SQL中dy_dj（电压等级）及xh_bz（销户标志）两个字段都没进行索引，所以执行的时候都是全表扫描，第一条SQL的dy_dj='1KV以下'条件在记录集内比率为99%，而xh_bz=1的比率只为0.5%，在进行第一条SQL的时候99%条记录都进行dy_dj及xh_bz的比较，而在进行第二条SQL的时候0.5%条记录都进行dy_dj及xh_bz的比较，以此可以得出第二条SQL的CPU占用率明显比第一条低。查询表顺序的影响在FROM后面的表中的列表顺序会对SQL执行性能影响，在没有索引及ORACLE没有对表进行统计分析的情况下ORACLE会按表出现的顺序进行链接，由此因为表的顺序不对会产生十分耗服务器资源的数据交叉。（注：如果对表进行了统计分析，ORACLE会自动先进小表的链接，再进行大表的链接）SQL优化规则2采用函数处理的字段不能利用索引，如：substr(hbs_bh,1,4)=’5400’，优化处理：hbs_bhlike‘5400%’trunc(sk_rq)=trunc(sysdate)，优化处理：sk_rq=trunc(sysdate)andsk_rqtrunc(sysdate+1)进行了显式或隐式的运算的字段不能进行索引，如：ss_df+2050，优化处理：ss_df30‘X’||hbs_bh’X5400021452’，优化处理：hbs_bh’5400021542’sk_rq+5=sysdate，优化处理：sk_rq=sysdate-5hbs_bh=5401002554，优化处理：hbs_bh=’5401002554’，注：此条件对hbs_bh进行隐式的to_number转换，因为hbs_bh字段是字符型。条件内包括了多个本表的字段运算时不能进行索引，如：ys_dfcx_df，无法进行优化qc_bh||kh_bh=’5400250000’，优化处理：qc_bh=’5400’andkh_bh=’250000’应用ORACLE的HINT（提示）处理提示处理是在ORACLE产生的SQL分析执行路径不满意的情况下要用到的。它可以对SQL进行以下方面的提示目标方面的提示：COST（按成本优化）RULE（按规则优化）CHOOSE（缺省）（ORACLE自动选择成本或规则进行优化）ALL_ROWS（所有的行尽快返回）FIRST_ROWS（第一行数据尽快返回）执行方法的提示：USE_NL（使用NESTEDLOOPS方式联合）USE_MERGE（使用MERGEJOIN方式联合）USE_HASH（使用HASHJOIN方式联合）索引提示：INDEX（TABLEINDEX）（使用提示的表索引进行查询）其它高级提示（如并行处理等等）笔者在工作实践中发现，不良的SQL往往来自于不恰当的索引设计、不充份的连接条件和不可优化的where子句。在对它们进行适当的优化后，其运行速度有了明显地提高！下面我将从这三个方面分别进行总结：为了更直观地说明问题，所有实例中的SQL运行时间均经过测试，不超过１秒的均表示为（1秒）。----测试环境:主机：HPLHII----主频：330MHZ----内存：128兆----操作系统：Operserver5.0.4----数据库：Sybase11.0.3一、不合理的索引设计----例：表record有620000行，试看在不同的索引下，下面几个SQL的运行情况：----1.在date上建有一非个群集索引selectcount(*)fromrecordwheredate'19991201'anddate'19991214'andamount2000(25秒)selectdate,sum(amount)fromrecordgroupbydate(55秒)selectcount(*)fromrecordwheredate'19990901'andplacein('BJ','SH')(27秒)分析：date上有大量的重复值，在非群集索引下，数据在物理上随机存放在数据页上，在范围查找时，必须执行一次表扫描才能找到这一范围内的全部行。----2.在date上的一个群集索引selectcount(*)fromrecordwheredate'19991201'anddate'19991214'andamount2000（14秒）selectdate,sum(amount)fromrecordgroupbydate（28秒）selectcount(*)fromrecordwheredate'19990901'andplacein('BJ','SH')（14秒）分析：在群集索引下，数据在物理上按顺序在数据页上，重复值也排列在一起，因而在范围查找时，可以先找到这个范围的起末点，且只在这个范围内扫描数据页，避免了大范围扫描，提高了查询速度。----3.在place，date，amount上的组合索引selectcount(*)fromrecordwheredate'19991201'anddate'19991214'andamount2000（26秒）selectdate,sum(amount)fromrecordgroupbydate（27秒）selectcount(*)fromrecordwheredate'19990901'andplacein('BJ,'SH')（1秒）分析：这是一个不很合理的组合索引，因为它的前导列是place，第一和第二条SQL没有引用place，因此也没有利用上索引；第三个SQL使用了place，且引用的所有列都包含在组合索引中，形成了索引覆盖，所以它的速度是非常快的。----4.在date，place，amount上的组合索引selectcount(*)fromrecordwheredate'19991201'anddate'19991214'andamount2000(1秒)selectdate,sum(amount)fromrecordgroupbydate（11秒）selectcount(*)fromrecordwheredate'19990901'andplacein('BJ','SH')（1秒）分析：这是一个合理的组合索引。它将date作为前导列，使每个SQL都可以利用索引，并且在第一和第三个SQL中形成了索引覆盖，因而性能达到了最优。----5.总结：----缺省情况下建立的索引是非群集索引，但有时它并不是最佳的；合理的索引设计要建立在对各种查询的分析和预测上。一般来说：①.有大量重复值、且经常有范围查询（between,,，=,=）和orderby、groupby发生的列，可考虑建立群集索引；SQL优化规则3②.经常同时存取多列，且每列都含有重复值可考虑建立组合索引；③.组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。二、不充份的连接条件：例：表card有7896行，在card_no上有一个非聚集索引，表account有191122行，在account_no上有一个非聚集索引，试看在不同的表连接条件下，两个SQL的执行情况：selectsum(a.amount)fromaccounta,cardbwherea.card_no=b.card_no（20秒）selectsum(a.amount)fromaccounta,cardbwherea.card_no=b.card_noanda.account_no=b.account_no（1秒）分析：在第一个连接条件下，最佳查询方案是将account作外层表，card作内层表，利用card上的索引，其I/O次数可由以下公式估算为：外层表account上的22541页+（外层表account的191122行*内层表card上对应外层表第一行所要查找的3页）=595907次I/O在第二个连接条件下，最佳查询方案是将card作外层表，account作内层表，利用account上的索引，其I/O次数可由以下公式估算为：外层表card上的1944页+（外层表card的7896行*内层表account上对应外层表每一行所要查找的4页）=33528次I/O可见，只有充份的连接条件，真正的最佳方案才会被执行。总结：1.多表操作在被实际执行前，查询优化器会根据连接条件，列出几组可能的连接方案并从中找出系统开销最小的最佳方案。连接条件要充份考虑带有