NCSQL规范及效率优化知识分享学习课程

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

2、：十点 十九分。概 述 执 行 计 划uRowid概念rowid是一个伪列，一旦一行数据插入数据库，则rowid在该行的生命周期内是唯一的uRow Source(行源)用在查询中，由上一操作返回的符合条件的行的集合u可选择性(selectivity)唯一键的数量/表中的行数uDriving Table(驱动表)uProbed Table(被探查表)第4页/共42页第四页，编辑于星期六：十点 十九分。概 述 执 行 计 划u嵌套循环读取row source1中的每一行然后在row sourc2中检查是否有匹配的行，所有被匹配的行都被放到结果集中然后处理row source1中的下一行u哈希连接较

3、小的row source被用来构建hash table与bitmap第2个row source被用来被hash第一个row source生成的hash table进行匹配，以便进行进一步的连接第5页/共42页第五页，编辑于星期六：十点 十九分。概 述 执 行 计 划u全表扫描(FTS)顺序地读取分配给表的每个数据块，直到读到表的最高水线处uRowid 物理ID扫描通过ROWID来存取数据可以快速定位到目标数据u索引唯一扫描(index unique scan)通过唯一索引查找一个数值经常返回单个ROWID第6页/共42页第六页，编辑于星期六：十点 十九分。概 述 执 行 计 划u索引范围扫描(

4、index range scan)使用一个索引存取多行数据u索引全扫描(index full scan)查询出的数据都必须从索引中可以直接得到u索引快速全扫描(index fast full scan)同索引全扫描，使用多块读功能，也可以使用并行读入第7页/共42页第七页，编辑于星期六：十点 十九分。概 述 执 行 计 划采用最右最上最先执行的原则SQL语句select * from ia_detailledger d, ia_calcrange c where d.ccalcrangeid=c.ccalcrangeid执行计划0 SELECT STATEMENT Optimizer=CHOO

5、SE1 0 HASH JOIN2 1 TABLE ACCESS (FULL) OF ia_detailledger3 1 TABLE ACCESS (FULL) OF ia_calcrange123第8页/共42页第八页，编辑于星期六：十点 十九分。Yonyou Software CorporationSQL规范第9页/共42页第九页，编辑于星期六：十点 十九分。S Q L 规 范 书 写 风 格uSQL语句全部使用小写（目前NC的整体习惯）；u引用字符时用单引号。如：update testable set idcol=abcd。u连接符或运算符or、in、and、=，+，- 等前后加上一个空

6、格；u在子查询中前后必须加上括号, select col1, col2 from tablea where col3 in ( 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的方式（我们目前

7、更多的是用SqlBuilder拼写SQL）；第10页/共42页第十页，编辑于星期六：十点 十九分。S Q L 规 范 书 写 风 格u严禁使用select * .形式的语句，要指出select的具体字段；u严禁使用 insert into table value(?)，要指出具体要赋值的字段；uSQL语句包含多表连接时，建议对每个表命名别名，对每个字段的使用都要带上表别名，即 select a.col1, a.col2, b.col3 from tablea a, tableb b where a.col4=b.col5；u拼写SQL加上表别名不仅仅是解决SQL解析耗时的问题：例： selec

8、t * from po_order where pk_order in (select pk_order from md_class)；select * from po_order a where a.pk_order in (select b.pk_order from md_class b)；上面俩条SQL执行结果？第11页/共42页第十一页，编辑于星期六：十点 十九分。S Q L 规 范 书 写 风 格u避免隐含的类型转换。例如在where子句中String型和Number型的列的比较或相加；例：create table t(id varchar2(10),name varchar2(1

9、0),sal number);create index t_idx on t(id);1、select * from t where id=7369; 2、select * from t where id=7369;第12页/共42页第十二页，编辑于星期六：十点 十九分。S Q L 规 范 书 写 风 格u避免在where使用1=1,1=2这种表达式作为部分条件，如 select col1, col2 from tablea where 1=1 and col1 0；u禁止使用视图； 优点？ 缺点？uIn最多支持1000（Oracle出于效率考虑默认1000），超过1000必须使用临时表，一般

10、200以上都应该使用临时表。目前供应链的统一标准是超过100就用临时表第13页/共42页第十三页，编辑于星期六：十点 十九分。S Q L 规 范 数 据 类 型u整型字段：读取时根据字段设置保存为Integer或者Long；u数字型字段：读取为BigDecimal，并保存为UFDouble，插入或者更新时为BigDecimalu字符型字段：读取为String，并保存为String，插入或者更新为Stringu布尔型字段：读取为String(Y OR N)，并保存为UFBoolean，插入或者更新时为String(Y OR N)u时间字段：读取为String，并保存为UFDateTime，插入或

11、者更新时的时间格式由中间件统一处理，有单独需求的要申请后才能决定。u注：读取是指通过JDBC读到的数据格式，保存是指保存在VO中的数据格式，插入或者更新是指insert或者update语句中的数据格式；第14页/共42页第十四页，编辑于星期六：十点 十九分。S Q L 规 范 高 效S Q LWhere子句替换HAVING子句例：#SQL一SELECT REGION, AVG(LOG_SIZE) FROM LOCATION GROUP BY REGION HAVING REGION REGION != SYDNEY AND REGION != PERTH#SQL二SELECT REGION,

12、AVG(LOG_SIZE) FROM LOCATION WHERE REGION REGION != SYDNEY AND REGION != PERTH GROUP BY REGIONHAVING只会在检索出所有记录之后才对结果集进行过滤，这个处理需要排序、统计等操作；而通过WHERE子句限制记录的数目，就能减少这方面的开销第15页/共42页第十五页，编辑于星期六：十点 十九分。S Q L 规 范 高 效S Q L使用表的别名 例：下面两个Sql执行效果哪个更好？（字段a，b，c是表table1的字段，x，y是table2的字段） #SQL一 Select a,b,x from table1

13、,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.yu 使用表的别名(Alias)，当在SQL语句中连接多个表时，请使用表的别 名并把别名前缀于每个Column上，这样可以 1）减少解析的时间 2）减少那些由Column歧义引起的语法错误第16页/共42页第十六页，编辑于星期六：十点 十九分。S Q L 规 范 高 效S Q LSELECT子句中避免使用* 示例：下面两个Sql执行效果哪个更好？ #SQL一 Select * fr

14、om Emp where pk_department = 1 #SQL二 Select ,t.code from Emp t where t.pk_department = 1u 当你想在SELECT子句中列出所有的COLUMN时，使用动态SQL列引用 * 是一个方便的方法，不幸的是，这是一个非常低效的方法。u 实际上，数据库在解析的过程中，会将*依次转换成所有的列名， 这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间； 并且一般情况下，我们并不需要查询表的所有字段；第17页/共42页第十七页，编辑于星期六：十点 十九分。S Q L 规 范 高 效S Q L合理使用索引提

15、高效率u 索引可以提高检索数据的效率。通常在大型表中使用索引特别有效；u 虽然使用索引能得到查询效率的提高，但是我们也必须注意到它的代价：u 索引需要空间来存储，也需要定期维护，每当有记录在表中增减或索引列被修改时，索引本身也会被修改； 这意味着每条记录的INSERT、DELETE、UPDATE将为此多付出4、5次的磁盘I/O。u 因为索引需要额外的存储空间和处理，所以那些不必要的索引反而会使查询反应时间变慢;u 另外，不正确的索引使用方法可能会导致索引无效第18页/共42页第十八页，编辑于星期六：十点 十九分。S Q L 规 范 高 效S Q L用索引提高效率 通配符 示例：下面两个Sql哪

16、个不会使用索引？（ MANAGER 列创建了索引） #SQL一 SELECT LODGING FROM LODGING WHERE MANAGER LIKE HANMAN; #SQL二 SELECT LODGING FROM LODGING WHERE MANAGER LIKE HANMAN%; WHERE子句中，如果索引列所对应的值的第一个字符由通配符开始，索引将不被采用；在这种情况下，数据库将使用全表扫描需要明确知道哪些情况下索引会失效！第19页/共42页第十九页，编辑于星期六：十点 十九分。S Q L 规 范 高 效S Q L用索引提高效率 避免改变索引列的类型.: 示例：下面两个Sql

17、哪个不会使用索引？（ EMPNO列是一个字符类型的索引列） #SQL一 SELECT FROM EMP WHERE EMPNO = 123 -index #SQL二 SELECT FROM EMP WHERE EMP_TYPE = 123 full 当比较不同数据类型的数据时, ORACLE自动对列进行简单的类型转换. oracle中select * from gl_detail where pk_detail =11 -fullselect * from gl_detail where localdebitamount =11 -index其它数据库需验证需要明确知道哪些情况下索引会失效！第

18、20页/共42页第二十页，编辑于星期六：十点 十九分。S Q L 规 范 高 效S Q L用索引提高效率 u is not null， is null;u 索引列使用表达式：1. , != 2.is null , 3.to_char(date)4. like %abc% like %abc 5. where (date - 10) sysdate;6. /*+ full(tab1) */需要明确知道哪些情况下索引会失效！第21页/共42页第二十一页，编辑于星期六：十点 十九分。S Q L 规 范 高 效S Q L复合索引的构建例： in的子查询返回5万多UPDATE invp_supply S

19、ET dr = 1 WHERE pk_org = 00016010000000000J8T AND dr = 0 AND cmaterialoid IN (SELECT cmaterialoid FROM invp_material WHERE pk_planbatch = 1003Z810000000PJ821O)差:Create index idx_cmaid_batchOn (cmaterialoid,pk_planbatch)优:Create index idx_batch_cmaidOn (pk_planbatch, cmaterialoid)第22页/共42页第二十二页，编辑于星期

20、六：十点 十九分。S Q L 规 范 高 效S Q L使用UNION-ALL和UNION 当SQL语句需要UNION两个查询结果集合时，这两个结果集合会以UNION-ALL的方式被合并，然后在输出最终结果前进行排序 如果用UNION ALL替代UNION，这样排序就不是必要了，效率就会因此得到提高 需要注意的是，UNION ALL将重复输出两个结果集合中相同记录，因此还是要从业务需求分析使用UNION ALL的可行性 注意：NC 规范中是禁止使用Union的, 如果确有业务上的需要，我们需要拆分为两个查询；1） select * from gl_voucher where pk_voucher

21、=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页第二十三页，编辑于星期六：十点 十九分。S Q L 规 范 高 效S Q L使用批处理代替循环处理

22、 在进行多条记录的增加、修改、删除时，建议使用批处理功能，批处理的次数以整个SQL语句不超过相应数据库的SQL语句大小的限制为准。 批处理有如下优势： 1）减少访问数据库的次数。 2）当执行每条SQL语句时，数据库在内部执行了许多工作：解析SQL语句，估算索引的利用率，绑定变量，读数据块等等，所以批处理可以减少数据库的处理次数，从而提高操作效率；for(int i=0;i100;i+) for(int i=0;i100;i+)statement.execute(sql); statement.addBath(sql); Statement.execute* 批处理需要考虑一次性提交事务还是分批

23、提交事务！不同的应用场景应该采取不同的处理方式。第24页/共42页第二十四页，编辑于星期六：十点 十九分。S Q L 规 范 高 效S Q L禁止使用not in 语句，建议用not exist 示例：下面两个Sql哪个效率更高？ #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

24、 AND DEPT_CAT = A); 在子查询中，NOT IN子句将执行一个内部的排序和合并；无论在哪种情况下，NOT IN都是最低效的，因为它对子查询中的表执行了一个全表遍历。 oracle11g开始，如果关联字段都是非空基本都一样。 Not in是等值判断、 not exists 是集合存在判断。 所以null值处理不一致。 为了避免使用NOT IN，我们可以把它改写成外连接(Outer Joins)或NOT EXISTS第25页/共42页第二十五页，编辑于星期六：十点 十九分。S Q L 规 范 高 效 S Q L用多表连接代替EXISTS子句。示例：下面两个Sql哪个效率更高？ #S

25、QL一 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页第二十六页，编辑于星期六：十点 十九分。S Q L 规 范 高 效S Q L不要使用外键约束项目中发现外键约束对性能影响很大，特别是大并发的场景。NC6

26、0去掉外键约束，数据约束通过业务逻辑来保证。1 ，在大型系统中（性能要求不高，安全要求高），使用外键；在大型系统中（性能要求高，安全自己控制），不用外键；小系统随便，最好用外键。 2 ，用外键要适当，不能过分追求 3 ，不用外键而用程序控制数据一致性和完整性时，应该写一层来保证，然后个个应用通过这个层来访问数据库。第27页/共42页第二十七页，编辑于星期六：十点 十九分。S Q L 规 范 高 效S Q L10、子查询与or连用的时候改成uniona.Select * from table1 where col1= or col2 in (select col2 from table2) 改成

27、Select * from table1 where col1= Union Select * from table1 where col2 in (select col2 from table2)b.Select * 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 i

28、n (select col2 from table3)第28页/共42页第二十八页，编辑于星期六：十点 十九分。S Q L 规 范 高 效 S Q L不要在子查询中引用主查询的条件Select * from table1 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页第二十九页，编辑于星期六：十点 十九分。S Q L 规 范 高

29、 效S Q L子查询外部条件内推select * from (select sum(a3),a1,a2 From AGroup by a1,a2)Where a1=? and a2=?Select sum(a3),a1,a2From AWhere a1=? and a2=?Group by a1,a2第30页/共42页第三十页，编辑于星期六：十点 十九分。S Q L 规 范 高 效S Q L多表关联update两种情况：更新成常数更新成关联表的相关字段,注意where条件,避免导致更新后出现数据错误 (oracle)UPDATE a set a.id=(select id from b whe

30、re 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，造成逻辑错误。第31页/共42页第三十一页，编辑于星期六：十点 十九分。S Q L 规 范 高 效S Q L多表关联查询条件中冗余字段 在多单位的集中应用中PK_CORP通常具有一定的选择性，

31、索引中通常也加了这个字段，但经常在多表关联的查询条件中只指定了其中一个表的PK_CORP，这在逻辑上并没有错，但减小了未指定PK_CORP的表的选择性 总帐系统的PK_GLORGBOOK 以及V6的pk_org1)使用冗余字段的好处.2)科目余额表和辅助明细账的问题.第32页/共42页第三十二页，编辑于星期六：十点 十九分。S Q L 规 范 高 效S Q L创建静态表备份数据，插入表数据 create table as select 回滚段、临时表资源抢占！ Truncate table 删除大量数据，不要用delete from 第33页/共42页第三十三页，编辑于星期六：十点 十九分。Y

32、onyou Software CorporationSQL优化技巧第34页/共42页第三十四页，编辑于星期六：十点 十九分。S Q L 性 能 问 题 优 化 技 巧NMCORACLE 自身SQL截取工具执行计划统计信息索引并行SPR报告二次分页存储过程第35页/共42页第三十五页，编辑于星期六：十点 十九分。S Q L性 能 问 题 优 化 技 巧u查询最近执行过的 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 IS NOT NULL and last_load_time like 14-0