二 SQL 书写规范
2.1 说明
对于目前 系统的SQL 书写，存在不少不合理的地方，主要表现如下
1. 谓词条件中过多的使用函数
2.SQL 中存在部分嵌套子查询
3.SQL 查询中存在过多的使用复杂的嵌套视图
4. 关联表格主外键类型不一致
2.2 嵌套视图查询
数据库中存在一条资源消耗严重的SQL ：
|
select xz_lx , yp_mc , gg , max_sl , max_jl , max_ts , max_sl_mzk , max_jl_mzk , max_ts_mzk , ys_bm_id , a.kf_id , a.yp_bh from drug_limit_info a, dictmanage.v_dict_drug b where a.yp_bh = b.yp_bh order by xz_lx |
在该查询中 dictmanage.v_dict_drug b 对应为一张视图，视图查询导致该SQL较为封闭且查询复杂，导致该SQL执行计划较为复杂，因为在查询中嵌套了复杂视图，导致CBO并不能合理的生成执行计划。容易出现执行计划紊乱现象。类似上述的SQL还有很对。我们建议对以上的查询进行重写，去除视图，通过union等联合查询进行SQL重写。类似这样的SQL数据库中存有很多，导致CBO不能很好的优化执行计划，只能按部就班的执行。
2.3 嵌套子查询
|
select distinct ( a.jz_kh ), d.pat_name , case when d.xb_id = '1' then '?' when d.xb_id = '2' then '?' else '??' end as xb , case when floor ( months_between ( sysdate , d.CS_RQ ) / 12 ) > 3 then to_char ( floor ( months_between ( sysdate , d.CS_RQ ) / 12 )) || '??' else his.fun_getage ( d.CS_RQ ) end as nl , c.zflx_mc , max ( a.yz_qrsj ) yzqrsj , max ( a.yzjsrq ) yzjsrq from his.outpat_order a , dictmanage.dict_pay_type c , his.pat_info d where a.pat_id = d.pat_id and a.pay_type = c.zflx_id (+) and a.yfstatus = '1' and a.tfbz in ( '0' , '6' ) and a.cflx_id = '3' and a.YZ_STATUS = '4' and a.winid in (select f.win_id from dictmanage.dict_machine_cfg f where machine = :in_machine) group by a.jz_kh , d.pat_name , d.xb_id , d.CS_RQ , c.zflx_mc order by yzjsrq asc |
上述SQL ，由于在谓词条件中嵌套了子查询，导致CBO 不能很好的选择执行计划，只能顺序执行，且in 这种操作效率更低，建议对该SQL 进行改写，通过关联表，让CBO 能够更好的选择执行计划。如上的SQL 在数据库中还是存在很多，一定程度上导致了多个执行计划的产生， 建议对数据库中存在子查询的 SQL 尽量进行改写 。
改写后如下：
|
select distinct ( a.jz_kh ), d.pat_name , case when d.xb_id = '1' then '?' when d.xb_id = '2' then '?' else '??' end as xb , case when floor ( months_between ( sysdate , d.CS_RQ ) / 12 ) > 3 then to_char ( floor ( months_between ( sysdate , d.CS_RQ ) / 12 )) || '??' else his.fun_getage ( d.CS_RQ ) end as nl , c.zflx_mc , max ( a.yz_qrsj ) yzqrsj , max ( a.yzjsrq ) yzjsrq from his.outpat_order a , dictmanage.dict_pay_type c , his.pat_info d dictmanage.dict_machine_cfg f where a.pat_id = d.pat_id and a.pay_type = c.zflx_id (+) and a.yfstatus = '1' and a.tfbz in ( '0' , '6' ) and a.cflx_id = '3' and a.YZ_STATUS = '4' and a.winid = f.win_id and f.machine = :in_machine group by a.jz_kh , d.pat_name , d.xb_id , d.CS_RQ , c.zflx_mc order by yzjsrq asc
|
2.4 谓词条件过多使用函数
所谓的谓词条件过多的使用函数，并不是说谓词条件中不允许使用函数，而是说在谓词条件中，我们尽量的将条件放到谓词条件的后面，当然，能避免就尽量避免去用函数。比如
|
select distinct a.jzkh , b.pat_name , c.yp_mc , a.cf_lsh_id , a.yzzx_lsh_id , c.psjgsj , ceil (( a.ps_jssj - a.ps_kssj ) * 24 * 60 ) psygsj , d.yzjl_id , a.fin_mxid , case nvl ( f.brlx , '0' ) when '1' then '??' || f.room || '(' || f.cwh || ')' when '3' then '??' || f.room || '(' || f.cwh || ')' else '???' end brlx_mc from his.outpat_order_act_detail a , his.pat_info b , dictmanage.drug_info c , his.outpat_order d , his.pat_observe_info f where a.pat_id = b.pat_id and a.sfxm_id = c.yp_id and a.mzyzjl_id = d.yzjl_id and a.jzlsh_id = f.jz_lsh_id (+) and a.psjg_id <> '0' and a.ps_jssj is not null and a. fs_rq is null and ceil((sysdate - a.ps_kssj) * 24 * 60) > psjgsj and a.zxks_id = : ksid |
从上面的SQL 看 a.ps_kssj 字段是日期函数， ceil((sysdate - a.ps_kssj) * 24 * 60)这部分计算的是一个分钟的整数，而 psjgsj是一个整数字段，在这里ceil（）函数完全是没有必要的，就算有需要用到函数，我们也不能把函数放在谓词部分，而是要放在>号后面来进行计算，这里，我们完全可以在 a.ps_kssj字段上添加一个索引，并把
and ceil((sysdate - a.ps_kssj) * 24 * 60) > psjgsj
改写成
a.ps_kssj < sysdate - c.psjgsj/60/24
经过验证，以上改写是成立的，如下验证：
|
SQL> select count(*) from his.outpat_order_act_detail a,dictmanage.drug_info c where a.ps_kssj < sysdate - c.psjgsj/60/24;
COUNT(*) ---------- 28081264
SQL> select count(*) from his.outpat_order_act_detail a,dictmanage.drug_info c where ceil((sysdate - a.ps_kssj) * 24 * 60) > c.psjgsj;
COUNT(*) ---------- 28081264 |
在SQL的 写法中，我们需要尽量的避免在谓词中出现函数等，真的需要，我们也应该尽量放在谓词的条件中，类似的SQL数据库中还有不少。
2.5 关联表格主外键类型不一致
关联表格主外键不一致也会导致索引无法 正常使用，很多时候，开发人员往往喜欢将明明是number 类型的字段做成varchar2 类型的，从而导致表之间关联出现问题。如：
|
update his.outpat_order set psjg_id = (select case when psjg_id = ps_fsjg then psjg_id else '4' end psjg from his.outpat_order_act_detail where yzzx_lsh_id = 476253 and ly = '1'), zhgxsj = sysdate, zhgxr = '2899' where YZJL_ID = (select mzyzjl_id from his.outpat_order_act_detail where yzzx_lsh_id = 476253) |
outpat_order.YZJL_ID varchar 类型outpat_order_act_detail.mzyzjl_id 为number 类型
我们可以考虑改造该字段，使得类型匹配，当然，我们也可以使用to_char 或者to_number 函数来隐式转换
|
update his.outpat_order set psjg_id = (select case when psjg_id = ps_fsjg then psjg_id else '4' end psjg from his.outpat_order_act_detail where yzzx_lsh_id = 476253 and ly = '1'), zhgxsj = sysdate, zhgxr = '2899' where YZJL_ID = (select to_char(mzyzjl_id) from his.outpat_order_act_detail where yzzx_lsh_id = 476253) |
当然，上面这条SQL 同样的存在子查询SQL 的问题，我们同样建议改造成表关联查询。
2.6 where 条件筛选性较差
尽可能的调整业务逻辑避免过多的使用<>,NOT NULL 之类的条件，导致查询只能进行大规模的全表扫，相应cpu 使用率，磁盘的繁忙程度都加剧了。建议添加筛选性更强的条件。
2.7 避免select for update 操作
在生产环境规范开发人员的语句规范性，尽量少或者避免for update 的使用，它会导致大量TX 锁的产生，影响整体性能。
三 业务高峰期的DDL 操作
在数据库运行期间，特别是业务高峰期的时候，建议不要进行一些添加索引，修改表结构的DDL操作进行。同样的，在业务高峰期的时候，我们也尽量不要去进行一些系统的操作，比如添加表空间，数据文件等等。
四 数据库规范化管理
目前来看，系统的索引和表都在同一个表空间中，并没有很好的做到索引和表分离，索引和表分离。在业务量小的情况下，我们将索引和表放在同一个表空间中并没有太大的关系，但是，随着业务数据的增长，我们还是建议将表和索引放在不同的表空间中：
1. 提高性能：分离后，索引和表对应的表空间尽量放在不同的LUN 或者磁盘上，把不同类型的IO 分离，一定程度上提高IO 性能
2. 便于管理：索引和表存放在不同的表空间下，当对应的索引表空间损坏后，一定程度上我们只需要重建索引即可，不会造成数据丢失。
|
31 HIS INDEX PARTITION TP_HIS42 26 HIS TABLE DICTSPACES 31 HIS INDEX PARTITION TP_HIS45 31 HIS INDEX PARTITION TP_HIS52 16 HIS TABLE PARTITION TP_HIS06 16 HIS TABLE PARTITION TP_HIS07 16 HIS TABLE PARTITION TP_HIS12 16 HIS TABLE PARTITION TP_HIS16 16 HIS TABLE PARTITION TP_HIS23 16 HIS TABLE PARTITION TP_HIS26 16 HIS TABLE PARTITION TP_HIS28 16 HIS TABLE PARTITION TP_HIS41 16 HIS TABLE PARTITION TP_HIS61 31 HIS INDEX PARTITION TP_HIS02 31 HIS INDEX PARTITION TP_HIS16 31 HIS INDEX PARTITION TP_HIS32 31 HIS INDEX PARTITION TP_HIS35 4 HIS LOBINDEX XMLDATA 31 HIS INDEX PARTITION TP_HIS29 31 HIS INDEX PARTITION TP_HIS44 16 HIS TABLE PARTITION TP_HIS02 16 HIS TABLE PARTITION TP_HIS05 16 HIS TABLE PARTITION TP_HIS14 16 HIS TABLE PARTITION TP_HIS36 16 HIS TABLE PARTITION TP_HIS37 16 HIS TABLE PARTITION TP_HIS39 16 HIS TABLE PARTITION TP_HIS40 16 HIS TABLE PARTITION TP_HIS42 16 HIS TABLE PARTITION TP_HIS49 16 HIS TABLE PARTITION TP_HIS53 16 HIS TABLE PARTITION TP_HIS59 31 HIS INDEX PARTITION TP_HIS10 31 HIS INDEX PARTITION TP_HIS12 31 HIS INDEX PARTITION TP_HIS14 31 HIS INDEX PARTITION TP_HIS58 225 HIS TABLE XMLDATA 26 HIS INDEX DICTSPACES 31 HIS INDEX PARTITION TP_HIS19 31 HIS INDEX PARTITION TP_HIS20 31 HIS INDEX PARTITION TP_HIS28 16 HIS TABLE PARTITION TP_HIS11 16 HIS TABLE PARTITION TP_HIS17 16 HIS TABLE PARTITION TP_HIS30 16 HIS TABLE PARTITION TP_HIS34 16 HIS TABLE PARTITION TP_HIS35 16 HIS TABLE PARTITION TP_HIS44 16 HIS TABLE PARTITION TP_HIS45 16 HIS TABLE PARTITION TP_HIS58 16 HIS TABLE PARTITION TP_HIS62 16 HIS TABLE PARTITION TP_HIS63 16 HIS TABLE PARTITION TP_HIS64 31 HIS INDEX PARTITION TP_HIS09 31 HIS INDEX PARTITION TP_HIS11 31 HIS INDEX PARTITION TP_HIS15 31 HIS INDEX PARTITION TP_HIS36 31 HIS INDEX PARTITION TP_HIS59 31 HIS INDEX PARTITION TP_HIS60 31 HIS INDEX PARTITION TP_HIS62 31 HIS INDEX PARTITION TP_HIS43 31 HIS INDEX PARTITION TP_HIS46 31 HIS INDEX PARTITION TP_HIS48 31 HIS INDEX PARTITION TP_HIS50 16 HIS TABLE PARTITION TP_HIS24 16 HIS TABLE PARTITION TP_HIS27 16 HIS TABLE PARTITION TP_HIS43 16 HIS TABLE PARTITION TP_HIS54 16 HIS TABLE PARTITION TP_HIS56 31 HIS INDEX PARTITION TP_HIS01 31 HIS INDEX PARTITION TP_HIS04 31 HIS INDEX PARTITION TP_HIS17 31 HIS INDEX PARTITION TP_HIS37 31 HIS INDEX PARTITION TP_HIS54 31 HIS INDEX PARTITION TP_HIS56 31 HIS INDEX PARTITION TP_HIS57 3 HIS TABLE PARTITION XMLDATA 31 HIS INDEX PARTITION TP_HIS22 31 HIS INDEX PARTITION TP_HIS23 31 HIS INDEX PARTITION TP_HIS26 16 HIS TABLE PARTITION TP_HIS03 16 HIS TABLE PARTITION TP_HIS09 16 HIS TABLE PARTITION TP_HIS15 16 HIS TABLE PARTITION TP_HIS19 16 HIS TABLE PARTITION TP_HIS29 16 HIS TABLE PARTITION TP_HIS38 16 HIS TABLE PARTITION TP_HIS48 31 HIS INDEX PARTITION TP_HIS07 31 HIS INDEX PARTITION TP_HIS31 31 HIS INDEX PARTITION TP_HIS38 31 HIS INDEX PARTITION TP_HIS55 31 HIS INDEX PARTITION TP_HIS40 31 HIS INDEX PARTITION TP_HIS21 31 HIS INDEX PARTITION TP_HIS47 31 HIS INDEX PARTITION TP_HIS53 16 HIS TABLE PARTITION TP_HIS01 16 HIS TABLE PARTITION TP_HIS08 16 HIS TABLE PARTITION TP_HIS13 16 HIS TABLE PARTITION TP_HIS18 16 HIS TABLE PARTITION TP_HIS33 16 HIS TABLE PARTITION TP_HIS50 16 HIS TABLE PARTITION TP_HIS52 16 HIS TABLE PARTITION TP_HIS57 16 HIS TABLE PARTITION TP_HIS60 31 HIS INDEX PARTITION TP_HIS05 31 HIS INDEX PARTITION TP_HIS08 31 HIS INDEX PARTITION TP_HIS13 31 HIS INDEX PARTITION TP_HIS18 31 HIS INDEX PARTITION TP_HIS34 31 HIS INDEX PARTITION TP_HIS63 31 HIS INDEX PARTITION TP_HIS64 4 HIS LOBSEGMENT XMLDATA 31 HIS INDEX PARTITION TP_HIS25 31 HIS INDEX PARTITION TP_HIS30 31 HIS INDEX PARTITION TP_HIS51 16 HIS TABLE PARTITION TP_HIS20 16 HIS TABLE PARTITION TP_HIS21 16 HIS TABLE PARTITION TP_HIS22 16 HIS TABLE PARTITION TP_HIS25 16 HIS TABLE PARTITION TP_HIS31 16 HIS TABLE PARTITION TP_HIS47 16 HIS TABLE PARTITION TP_HIS51 31 HIS INDEX PARTITION TP_HIS06 452 HIS INDEX XMLDATA 31 HIS INDEX PARTITION TP_HIS41 31 HIS INDEX PARTITION TP_HIS24 31 HIS INDEX PARTITION TP_HIS27 31 HIS INDEX PARTITION TP_HIS49 16 HIS TABLE PARTITION TP_HIS04 16 HIS TABLE PARTITION TP_HIS10 16 HIS TABLE PARTITION TP_HIS32 16 HIS TABLE PARTITION TP_HIS46 16 HIS TABLE PARTITION TP_HIS55 31 HIS INDEX PARTITION TP_HIS03 31 HIS INDEX PARTITION TP_HIS33 31 HIS INDEX PARTITION TP_HIS39 31 HIS INDEX PARTITION TP_HIS61
135 rows selected. |
五 总结说明
总的来说，当前数据库中亟需改进的是大量的SQL的写法规范性的问题，我们需要尽快的解决SQ