NCSQL规范及效率优化知识分享学习教案

1、会计学1NCSQL规范规范(gufn)及效率优化知识分享及效率优化知识分享第一页，共43页。Yonyou Software Corporation目录(ml)概述SQL规范SQL性能问题优化技巧总结第1页/共42页第二页，共43页。SQL对NC性能非常重要！SQl效率问题：环境并发量大解析，执行，读结果(ji gu)集设计不足是SQL复杂，效率低下的重要原因快速定位解决SQL相关问题第2页/共42页第三页，共43页。u执行计划的产生u语法检测判断一条SQL语句的语法是否符合SQL的规范u语义检查(jinch)表及列是否准确？用户是否有权限访问或更改相应的表或列u生成执行计划 软解析（共享池存在

2、） 硬解析（共享池不存在）-耗时第3页/共42页第四页，共43页。uRowid概念rowid是一个伪列，一旦一行数据插入数据库，则rowid在该行的生命周期内是唯一的uRow Source(行源)用在查询中，由上一操作返回的符合条件的行的集合(jh)u可选择性(selectivity)唯一键的数量/表中的行数uDriving Table(驱动表)uProbed Table(被探查表)第4页/共42页第五页，共43页。u嵌套循环u读取row source1中的每一行u然后在row sourc2中检查是否有匹配的行，所有被匹配的行都被放到结果集中(jzhng)u然后处理row source1中的下

3、一行u哈希连接u较小的row source被用来构建hash table与bitmapu第2个row source被用来被hashu第一个row source生成的hash table进行匹配，以便进行进一步的连接第5页/共42页第六页，共43页。u全表扫描(FTS)顺序地读取分配给表的每个数据块，直到读到表的最高水线处uRowid 物理ID扫描通过ROWID来存取数据可以快速定位到目标数据u索引唯一扫描(index unique scan)通过唯一索引查找一个数值(shz)经常返回单个ROWID第6页/共42页第七页，共43页。u索引范围扫描(index range scan)使用一个索引存

4、取多行数据u索引全扫描(index full scan)查询出的数据都必须从索引中可以直接得到(d do)u索引快速全扫描(index fast full scan)同索引全扫描，使用多块读功能，也可以使用并行读入第7页/共42页第八页，共43页。采用最右最上最先执行的原则SQL语句(yj)select * from ia_detailledger d, ia_calcrange c where d.ccalcrangeid=c.ccalcrangeid执行计划0 SELECT STATEMENT Optimizer=CHOOSE1 0 HASH JOIN2 1 TABLE ACCESS (F

5、ULL) OF ia_detailledger3 1 TABLE ACCESS (FULL) OF ia_calcrange123第8页/共42页第九页，共43页。Yonyou Software CorporationSQL规范第9页/共42页第十页，共43页。uSQL语句全部使用小写（目前NC的整体(zhngt)习惯）；u引用字符时用单引号。如：update testable set idcol=abcd。u连接符或运算符or、in、and、=，+，- 等前后加上一个空格；u在子查询中前后必须加上括号, select col1, col2 from tablea where col3 in

6、( select col4 from tableb where col40)；u当SQL语句含有运算符时，运算符需与其他字符串用空格区分(或者用括号分开)。否则容易导致以下类似问题。在语句select ab from table 中， a，b均为变量。拼写该语句时，如果a=6， b= -3，则语句变为select 6-3 from table。-变为Sql的注释，语句报错！u在拼装SQL的时候，使用StringBuffer,不要用String+String的方式（我们目前更多的是用SqlBuilder拼写SQL）；第10页/共42页第十一页，共43页。u严禁使用select * .形式的语句，

7、要指出select的具体字段；u严禁使用 insert into table value(?)，要指出具体要赋值的字段；uSQL语句包含多表连接(linji)时，建议对每个表命名别名，对每个字段的使用都要带上表别名，即 select a.col1, a.col2, b.col3 from tablea a, tableb b where a.col4=b.col5；u拼写SQL加上表别名不仅仅是解决SQL解析耗时的问题：u例： select * from po_order where pk_order in (select pk_order from md_class)；uselect * f

8、rom po_order a where a.pk_order in (select b.pk_order from md_class b)；u上面俩条SQL执行结果？第11页/共42页第十二页，共43页。u避免隐含的类型转换。例如在where子句(z j)中String型和Number型的列的比较或相加；u例：ucreate table t(id varchar2(10),name varchar2(10),sal number);ucreate index t_idx on t(id);u1、select * from t where id=7369; 2、select * from t

9、where id=7369;第12页/共42页第十三页，共43页。u避免在where使用1=1,1=2这种表达式作为部分条件，如 select col1, col2 from tablea where 1=1 and col1 0；u禁止使用视图；u 优点？u 缺点？uIn最多支持1000（Oracle出于效率考虑(kol)默认1000），超过1000必须使用临时表，一般200以上都应该使用临时表。u目前供应链的统一标准是超过100就用临时表第13页/共42页第十四页，共43页。u整型字段：读取时根据字段设置保存为Integer或者Long；u数字型字段：读取为BigDecimal，并保存为U

10、FDouble，插入或者更新时为BigDecimalu字符型字段：读取为String，并保存为String，插入或者更新为Stringu布尔型字段：读取为String(Y OR N)，并保存为UFBoolean，插入或者更新时为String(Y OR N)u时间字段：读取为String，并保存为UFDateTime，插入或者更新时的时间格式由中间件统一(tngy)处理，有单独需求的要申请后才能决定。u注：读取是指通过JDBC读到的数据格式，保存是指保存在VO中的数据格式，插入或者更新是指insert或者update语句中的数据格式；第14页/共42页第十五页，共43页。Where子句(z j)

11、替换HAVING子句(z j)例：#SQL一SELECT REGION, AVG(LOG_SIZE) FROM LOCATION GROUP BY REGION HAVING REGION REGION != SYDNEY AND REGION != PERTH#SQL二SELECT REGION, AVG(LOG_SIZE) FROM LOCATION WHERE REGION REGION != SYDNEY AND REGION != PERTH GROUP BY REGIONHAVING只会在检索出所有记录之后才对结果集进行过滤，这个处理需要排序、统计等操作；而通过WHERE子句(z

12、j)限制记录的数目，就能减少这方面的开销第15页/共42页第十六页，共43页。使用表的别名使用表的别名 例：例：下面两个下面两个(lin )Sql执行效果哪个更好？（字段执行效果哪个更好？（字段a，b，c是表是表table1的字段，的字段，x，y是是table2的字段）的字段） #SQL一一 Select a,b,x from table1,table2 where a100 and c=y #SQL二二 Select t1.a,t1.b,t2.x from table1 t1,table2 t2 where t1.a100 and t1.c = t2.y 使用表的别名使用表的别名(Alias

13、)，当在，当在SQL语句中连接多个表时，请使用表的语句中连接多个表时，请使用表的别别 名并把别名前缀于每个名并把别名前缀于每个Column上，这样可以上，这样可以 1）减少解析的时间）减少解析的时间 2）减少那些由）减少那些由Column歧义引起的语法错误歧义引起的语法错误第16页/共42页第十七页，共43页。SELECT子句中避免使用子句中避免使用* 示例：下面两个示例：下面两个Sql执行效果哪个更好？执行效果哪个更好？ #SQL一一 Select * from Emp where pk_department = 1 #SQL二二 Select ,t.code from Emp

14、t where t.pk_department = 1 当你想在当你想在SELECT子句中列出所有的子句中列出所有的COLUMN时，使用动态时，使用动态SQL列引用列引用 * 是一个方便的方法，不幸的是，这是一个非常是一个方便的方法，不幸的是，这是一个非常(fichng)低效的方法。低效的方法。 实际上，数据库在解析的过程中，会将实际上，数据库在解析的过程中，会将*依次转换成所有的依次转换成所有的列名，列名， 这个工作是通过查询数据字典完成的，这意味着将耗费更这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间；多的时间； 并且一般情况下，我们并不需要查询表的所有字段；并且一般情况下，我

15、们并不需要查询表的所有字段；第17页/共42页第十八页，共43页。合理使用索引合理使用索引(suyn)提高效率提高效率 索引索引(suyn)可以提高检索数据的效率。通常在大型表中使用索可以提高检索数据的效率。通常在大型表中使用索引引(suyn)特别有效；特别有效； 虽然使用索引虽然使用索引(suyn)能得到查询效率的提高，但是我们也必须能得到查询效率的提高，但是我们也必须注意到它的代价：注意到它的代价： 索引索引(suyn)需要空间来存储，也需要定期维护，每当有记录需要空间来存储，也需要定期维护，每当有记录在表中增减或索引在表中增减或索引(suyn)列被修改时，索引列被修改时，索引(suyn)

16、本身也会被本身也会被修改；修改； 这意味着每条记录的这意味着每条记录的INSERT、DELETE、UPDATE将为此将为此多付出多付出4、5次的磁盘次的磁盘I/O。 因为索引因为索引(suyn)需要额外的存储空间和处理，所以那些不必需要额外的存储空间和处理，所以那些不必要的索引要的索引(suyn)反而会使查询反应时间变慢反而会使查询反应时间变慢; 另外，不正确的索引另外，不正确的索引(suyn)使用方法可能会导致索引使用方法可能会导致索引(suyn)无效无效第18页/共42页第十九页，共43页。用索引提高效率用索引提高效率 通配符通配符 示例：下面两个示例：下面两个Sql哪个不会使用索引？（哪

17、个不会使用索引？（ MANAGER 列创列创建了索引）建了索引） #SQL一一 SELECT LODGING FROM LODGING WHERE MANAGER LIKE HANMAN; #SQL二二 SELECT LODGING FROM LODGING WHERE MANAGER LIKE HANMAN%; WHERE子句中，如果子句中，如果(rgu)索引列所对应的值的第一个字索引列所对应的值的第一个字符由通配符开始，索引将不被采用；在这种情况下，数据库将符由通配符开始，索引将不被采用；在这种情况下，数据库将使用全表扫描使用全表扫描需要明确知道哪些情况下索引会失效！需要明确知道哪些情况下

18、索引会失效！第19页/共42页第二十页，共43页。用索引提高效率用索引提高效率 避免改变索引列的类型避免改变索引列的类型.: 示例：下面两个示例：下面两个Sql哪个不会使用索引？（哪个不会使用索引？（ EMPNO列是一个字列是一个字符类型的索引列）符类型的索引列） #SQL一一 SELECT FROM EMP WHERE EMPNO = 123 -index #SQL二二 SELECT FROM EMP WHERE EMP_TYPE = 123 full 当比较不同数据类型的数据时当比较不同数据类型的数据时, ORACLE自动对列进行简单自动对列进行简单的类型转换的类型转换. oracle中中

19、select * from gl_detail where pk_detail =11 -fullselect * from gl_detail where localdebitamount =11 -index其它数据库需验证其它数据库需验证需要明确知道需要明确知道(zh do)哪些情况下索引会失效！哪些情况下索引会失效！第20页/共42页第二十一页，共43页。用索引用索引(suyn)提高效率提高效率 is not null， is null; 索引索引(suyn)列使用表达式：列使用表达式： , != is null , to_char(date) like %abc% like %abc

20、 where (date - 10) sysdate;/*+ full(tab1) */需要明确知道哪些情况下索引需要明确知道哪些情况下索引(suyn)会失效！会失效！第21页/共42页第二十二页，共43页。复合索引的构建复合索引的构建例：例： in的子查询的子查询(chxn)返回返回5万多万多UPDATE invp_supply SET dr = 1 WHERE pk_org = 00016010000000000J8T AND dr = 0 AND cmaterialoid IN (SELECT cmaterialoid FROM invp_material WHERE pk_planba

21、tch = 1003Z810000000PJ821O)差差:Create index idx_cmaid_batchOn (cmaterialoid,pk_planbatch)优优:Create index idx_batch_cmaidOn (pk_planbatch, cmaterialoid)第22页/共42页第二十三页，共43页。使用使用UNION-ALL和和UNION 当当SQL语句需要语句需要UNION两个查询结果集合时，这两个结果集合会以两个查询结果集合时，这两个结果集合会以UNION-ALL的方式被合并，然后在输出最终结果前进行排序的方式被合并，然后在输出最终结果前进行排序 如

22、果用如果用UNION ALL替代替代UNION，这样排序就不是必要了，效率就会因此得到提高，这样排序就不是必要了，效率就会因此得到提高 需要注意的是，需要注意的是，UNION ALL将重复输出两个结果集合中相同记录将重复输出两个结果集合中相同记录(jl)，因此还是，因此还是要从业务需求分析使用要从业务需求分析使用UNION ALL的可行性的可行性 注意：注意：NC 规范中是禁止使用规范中是禁止使用Union的的, 如果确有业务上的需要，我们需要拆分如果确有业务上的需要，我们需要拆分为两个查询；为两个查询；1） select * from gl_voucher where pk_voucher=

23、abc or pk_voucher in (select pk_voucher from gl_voucher where OFFERVOUCHER=345)2）select * from gl_voucher where pk_voucher=abc union allselect * from gl_voucher where pk_voucher in (select pk_voucher from gl_voucher where OFFERVOUCHER=345) 。第23页/共42页第二十四页，共43页。使用批处理代替循环处理使用批处理代替循环处理 在进行多条记录的增加在进行多条记

24、录的增加(zngji)、修改、删除时，建议使用批处理功能，批处、修改、删除时，建议使用批处理功能，批处理的次数以整个理的次数以整个SQL语句不超过相应数据库的语句不超过相应数据库的SQL语句大小的限制为准。语句大小的限制为准。 批处理有如下优势：批处理有如下优势： 1）减少访问数据库的次数。）减少访问数据库的次数。 2）当执行每条）当执行每条SQL语句时，数据库在内部执行了许多工作：解析语句时，数据库在内部执行了许多工作：解析SQL语句，语句，估算索引的利用率，绑定变量，读数据块等等，所以批处理可以减少数据库的处估算索引的利用率，绑定变量，读数据块等等，所以批处理可以减少数据库的处理次数，从而

25、提高操作效率；理次数，从而提高操作效率；for(int i=0;i100;i+) for(int i=0;i100;i+)statement.execute(sql); statement.addBath(sql); Statement.execute* 批处理需要考虑一次性提交事务还是分批提交事务！不同的应用场景应该采取不批处理需要考虑一次性提交事务还是分批提交事务！不同的应用场景应该采取不同的处理方式。同的处理方式。第24页/共42页第二十五页，共43页。禁止使用禁止使用not in 语句，建议用语句，建议用not exist 示例：下面两个示例：下面两个Sql哪个效率更高？哪个效率更高？

26、 #SQL一一 SELECT * FROM EMP WHERE DEPT_NO NOT IN (SELECT DEPT_NO FROM DEPT WHERE DEPT_CAT = A); #SQL二二 SELECT * FROM EMP E WHERE NOT EXISTS (SELECT X FROM DEPT D WHERE D.DEPT_NO = E.DEPT_NO AND DEPT_CAT = A); 在子查询中，在子查询中，NOT IN子句将执行子句将执行(zhxng)一个内部的排序和合并；无论在哪种一个内部的排序和合并；无论在哪种情况下，情况下，NOT IN都是最低效的，因为它对子

27、查询中的表执行都是最低效的，因为它对子查询中的表执行(zhxng)了一个全表了一个全表遍历。遍历。 oracle11g开始，如果关联字段都是非空基本都一样。开始，如果关联字段都是非空基本都一样。 Not in是等值判断、是等值判断、 not exists 是集合存在判断。是集合存在判断。 所以所以null值处理不一致。值处理不一致。 为了避免使用为了避免使用NOT IN，我们可以把它改写成外连接，我们可以把它改写成外连接(Outer Joins)或或NOT EXISTS第25页/共42页第二十六页，共43页。用多表连接代替用多表连接代替EXISTS子句。子句。示例：下面两个示例：下面两个Sql

28、哪个哪个(n ge)效率更高？效率更高？ #SQL一一 SELECT ENAME FROM EMP E WHERE EXISTS (SELECT X FROM DEPT WHERE DEPT_NO = E.DEPT_NO AND DEPT_CAT = A); #SQL二二 SELECT ENAME FROM DEPT D, EMP E WHERE E.DEPT_NO = D.DEPT_NO AND DEPT_CAT = A; 通常来说，采用表连接的方式比通常来说，采用表连接的方式比EXISTS更有效率；更有效率；第26页/共42页第二十七页，共43页。不要使用外键约束不要使用外键约束项目中发现

29、外键约束对性能影响很大，特别是大并发的场景。项目中发现外键约束对性能影响很大，特别是大并发的场景。NC60去掉外键约束，数据约束通过业务逻辑来保证。去掉外键约束，数据约束通过业务逻辑来保证。1 ，在大型系统中（性能要求不高，安全要求高），使用外键；在大型系统中（性能要，在大型系统中（性能要求不高，安全要求高），使用外键；在大型系统中（性能要求高，安全自己求高，安全自己(zj)控制），不用外键；小系统随便，最好用外键。控制），不用外键；小系统随便，最好用外键。 2 ，用外键要适当，不能过分追求，用外键要适当，不能过分追求 3 ，不用外键而用程序控制数据一致性和完整性时，应该写一层来保证，然后个个

30、应用，不用外键而用程序控制数据一致性和完整性时，应该写一层来保证，然后个个应用通过这个层来访问数据库。通过这个层来访问数据库。第27页/共42页第二十八页，共43页。10、子查询、子查询(chxn)与与or连用的时候改成连用的时候改成uniona.Select * from table1 where col1= or col2 in (select col2 from table2) 改成改成Select * from table1 where col1= Union Select * from table1 where col2 in (select col2 from table2)b.S

31、elect * from table1 where col1 in (select col1 from table2) or col2 in (select col2 from table3) 改成改成Select * from table1 where col1 in (select col1 from table2)Union Select * from table1 where col2 in (select col2 from table3)第28页/共42页第二十九页，共43页。不要不要(byo)在子查询中引用主查询的条件在子查询中引用主查询的条件Select * from tabl

32、e1 where col1 in (select col1 from table2 where table1.col2=value)改成改成Select * from table1 where col1 in (select col1 from table2 where table2.col2=value)第29页/共42页第三十页，共43页。子查询外部子查询外部(wib)(wib)条件内推条件内推select select * * from from (select sum(a3),a1,a2 From A(select sum(a3),a1,a2 From AGroup by a1,a2)

33、Group by a1,a2)Where a1=? and a2=?Where a1=? and a2=?Select sum(a3),a1,a2Select sum(a3),a1,a2From AFrom AWhere a1=? and a2=?Where a1=? and a2=?Group by a1,a2Group by a1,a2第30页/共42页第三十一页，共43页。多表关联update两种情况：更新成常数更新成关联表的相关字段,注意where条件,避免导致更新后出现数据错误(cuw) (oracle)UPDATE a set a.id=(select id from b wher

34、e a.a=b.a) where exists (select 1 from b where a.a=b.a)如果没有这个子查询：UPDATE a set (a.id,a.id2)=(select b.id,b.id2 from b where a.a=b.a)这个sql将会把a表中对应b表(a.a=b.a)中的记录更新成我们需要的值，但是如果在a中存在与b表不关联的记录，则会将这些记录更新成Null，造成逻辑错误(cuw)。第31页/共42页第三十二页，共43页。多表关联查询条件中冗余字段多表关联查询条件中冗余字段 在多单位的集中应用中在多单位的集中应用中PK_CORPPK_CORP通常具有

35、一定的选择性，索引中通通常具有一定的选择性，索引中通常也加了这个字段，但经常在多表关联的查询条件中只指定常也加了这个字段，但经常在多表关联的查询条件中只指定(zhdng)(zhdng)了其中一个表的了其中一个表的PK_CORPPK_CORP，这在逻辑上并没有错，但减小了未指定，这在逻辑上并没有错，但减小了未指定(zhdng)PK_CORP(zhdng)PK_CORP的表的选择性的表的选择性 总帐系统的总帐系统的PK_GLORGBOOKPK_GLORGBOOK 以及以及V6V6的的pk_orgpk_org1)1)使用冗余字段的好处使用冗余字段的好处. .2)2)科目余额表和辅助明细账的问题科目余

36、额表和辅助明细账的问题. .第32页/共42页第三十三页，共43页。创建静态(jngti)表备份数据，插入表数据 create table as select 回滚段、临时表资源抢占！ Truncate table 删除大量数据，不要用delete from 第33页/共42页第三十四页，共43页。Yonyou Software CorporationSQL优化技巧第34页/共42页第三十五页，共43页。NMCORACLE 自身SQL截取工具(gngj)执行计划统计信息索引并行SPR报告二次分页存储过程第35页/共42页第三十六页，共43页。u查询查询(chxn)最近执行过的最近执行过的 SQL语句：语句：uSELECT sql_text, last_load_time FROM v$sql WHERE last_load_time IS NOT NULL and sql_text like update% ORDER BY last_load_time DESC;uSELECT sql_text, last_load_time FROM v$sql WHERE last_load_time I