資料庫操作規範及SQL書寫建議
SQL 書寫規範
說明
對於目前 系統的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的寫法規範性的問題，我們需要儘快的解決SQ
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23732248/viewspace-2777120/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 資料庫系統操作規範及SQL書寫建議資料庫SQL
- 資料庫規範之SQL規範寫法資料庫SQL
- SQL書寫規範(通用)SQL
- CSS命名及書寫規範CSS
- MySQL資料庫設計規範和優化建議MySql資料庫優化
- MySQL資料庫規範 (設計規範+開發規範+操作規範)MySql資料庫
- SQL Server資料庫命名規範SQLServer資料庫
- CSS編寫指導規範和建議CSS
- SQL Server 資料庫設計規範SQLServer資料庫
- css書寫規範CSS
- 海量資料遷移之誤操作和防範建議
- css BEM書寫規範CSS
- 程式碼書寫規範
- 資料庫建表及索引規約資料庫索引
- HTML編碼規範建議HTML
- 認識及操作SQL Server 資料庫SQLServer資料庫
- mysql資料庫規範MySql資料庫
- 資料庫命令規範資料庫
- MySQL 規範 (資料庫表設計規範)MySql資料庫
- css命名和書寫規範CSS
- css書寫和命名規範CSS
- html和css書寫規範HTMLCSS
- CSS 樣式書寫規範CSS
- C++程式碼書寫規範——給新手程式設計師的一些建議C++程式設計師
- 書寫高質量sql的一些建議SQL
- MYSQL資料庫欄位命名及設計規範MySql資料庫
- oracle資料庫叢集新增表空間操作規範Oracle資料庫
- 用Python寫協議規範Python協議
- MySQL建庫建表索引規範MySql索引
- 5. SQL 編寫規範SQL
- 必看的資料庫規範資料庫
- 1.2.4 資料庫規範化資料庫
- 資料庫設計規範資料庫
- HTML、CSS程式碼書寫規範HTMLCSS
- 程式碼書寫規範（Java） (轉)Java
- 資料開發流程及規範
- [資料庫]【MySQL】MySQL資料庫規範總結資料庫MySql
- 資料庫日常運維中的幾個操作建議資料庫運維