資料庫系統操作規範及SQL書寫建議

yingyifeng306發表於2021-05-06

 

SQL 書寫規範

說明

對於目前HIS 系統的SQL 書寫,存在不少不合理的地方,主要表現如下

1. 謂詞條件中過多的使用函式

2.SQL 中存在部分巢狀子查詢

3.SQL 查詢中存在過多的使用複雜的巢狀檢視

4. 關聯表格主外來鍵型別不一致

 

巢狀檢視查詢

資料庫中存在一條資源消耗嚴重的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不能很好的最佳化執行計劃,只能按部就班的執行。

 

巢狀子查詢

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

 

 

 

謂詞條件過多使用函式

所謂的謂詞條件過多的使用函式,並不是說謂詞條件中不允許使用函式,而是說在謂詞條件中,我們儘量的將條件放到謂詞條件的後面,當然,能避免就儘量避免去用函式。比如

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資料庫中還有不少。

 

關聯表格主外來鍵型別不一致

關聯表格主外來鍵不一致也會導致索引無法 正常使用,很多時候,開發人員往往喜歡將明明是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 的問題,我們同樣建議改造成表關聯查詢。

 

條件篩選性較差

儘可能的調整業務邏輯避免過多的使用<>,NOT NULL 之類的條件,導致查詢只能進行大規模的全表掃,相應cpu 使用率,磁碟的繁忙程度都加劇了。建議新增篩選性更強的條件。

 

避免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的寫法規範性的問題,我們需要儘快的解決SQL寫法問題。


來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/23732248/viewspace-2770904/,如需轉載,請註明出處,否則將追究法律責任。

相關文章