討論區快速選單
知識庫快速選單
討論區最近新進100則主題 掌握Salesforce雲端管理秘訣 網路投保旅行平安險
[ 回上頁 ] [ 討論區發言規則 ]
SQL 效能與關聯式資料庫精神如何平衡?
更改我的閱讀文章字型大小
作者 : little boy(Little Boy)
[ 貼文 22 | 人氣 5961 | 評價 0 | 評價/貼文 0 | 送出評價 1 次 ] 
[ 給個讚 ]  [ 給個讚 ]  [ 回應本文 ]  [ 發表新文 ]  [ 回上頁 ] [ 回討論區列表 ] [ 回知識入口 ]
2004/3/31 下午 03:32:34
各位先進大家好:

我有三個table分別為a 文件主檔,b人員主檔,c部門主檔;schema分別如下:
a
--
flow_num varchar2(13)
empno varchar2(8)

b
--
empno varchar2(8)
dept_no varchar2(8)

c
--
dept_no varchar2(8)
dept_name varchar2(20)

一般我們若要得知此流程是由那個部門申請的,會使用union的方式去串出來,sql 如下:
select a.flow_num,c.dept_name from a,b,c
where a.empno=b.empno
    and b.dep_no=c.dept_no;

但是若要反向查詢,即查詢所有指定部門的流程,則會使用sub query,但sub query一但資料量有數十萬筆時,便有效能上的疑慮,我的老闆叫我直接在 a 中加入dep_no這個欄位,但這樣一來卻又違反了關聯式資料庫的精神,且也有往後若人員調動所產生的問題。

想請教各位先進對於類似的問題是否有可指教小弟的地方,因為我總覺得這個問題是自己對SQL的掌握度還不夠所產生的,不應該是以這種破壞關聯式資料庫的作法來實作!!

望各位先進能不吝指教 !!謝謝!!
作者 : mydick(ㄉ一ˊㄎㄜˋ) SQL Language優秀好手貼文超過200則
[ 貼文 223 | 人氣 6296 | 評價 2890 | 評價/貼文 12.96 | 送出評價 34 次 ] 
[ 給個讚 ]  [ 給個讚 ]  [ 回應本文 ]  [ 發表新文 ]  [ 回上頁 ] [ 回討論區列表 ] [ 回知識入口 ]
2004/4/1 下午 01:24:25
小弟對這個議題心有戚戚, 以下淺見希望能有所幫助!一. 老闆的提議可能是對的:
....人員隸屬的部門也可能改變, 若UserX 由Dept0 調至Dept1, 在TableA 中記錄人員隸屬的部門,則可快速知道UserX 在提出申請流程時所隸屬的部門!
....若未在TableA 中增加Dept_No 欄位,則在人事異動後,以Join 取得的部門資訊只能代表提出申請者最後的部門Dept1,但並非UserX 提出申請當時的部門(可能是Dept0),這會影響後續對該流程資料異動的權限!
....因此.老闆的這個提議可能是對的,請先確認您們需要的邏輯是否需要留下申請人員歷史的隸屬部門!

二. 資料庫反正規化的風險:
....資料庫反正規化後會有兩種現象:1.一份原始資訊會被複製並散佈在多處(至少兩處); 2.某些欄位內容為多份資訊合併的結果!
....現象1代表多處資訊同時都可代表原始資訊, 其可能的風險有:
....(1)在某些因素下導致這幾份資訊並不相符時,怎麼辦?最值得信賴的還是那份原始資料, 而複製的其他資訊則還需要撰寫其他程式才能重建!
     (2) 系統的輸出(以下通稱"報表") 之資料來源若未統一, 某些報表資料來自原始資料, 某些來自被複製的資料, 又這些資料不相符時, 如何確認哪些報表是正確的? 哪些是錯的, 錯在哪裡?這增加了系統除錯的困難度!
....現象2代表某欄位FieldX 代表了多份資訊的結合,其可能的風險為:多份資訊來源結合至FieldX 的過程可能發生意外的問題或程式錯誤,導致Field_X 的值已經無法代表多份資訊結合的結果了,但是FieldX 值的錯誤並不容易發現,而且也不容易追蹤或比對,這也會隨著FieldX的使用對系統造成不同程度的影響!

三. 只要有適當的索引, Join 不是大問題:
....關聯式資料庫藉由資料表之間的關聯來滿足各種應用,而且各家資料庫都有完整的測試,應付上百萬筆的資料應該是很輕易的!
....在大量資料應用下,"效能"有個很重要的伙伴"索引",有適當索引幫助下,資料表之間的關聯並不會消耗太多資源(包括時間);反之只能經由Table Scan的方式找資料,"效能"應該是不切實際的!
    
四, 儘可能以Join 替代Sub Query:
....Sub Query 的確效能不彰,特別是和"Not" 結合時,其表現更是令人不滿!
....有個具備相同作用,但是效能遠勝Sub Query 的方式就是Join,以下例子實際說明替代的方式(以T-SQL 語法為例):
....1.找出存在Sub Query 中的資料:
.......原始語法:
.......(1)Select Tbl0.* From Tbl0 Where Exists (Select ... From Tbl1 Where Tbl0.Fld0=Tbl1.Fld1....) /
     (2)Select Tbl0.* From Tbl0 Where Fld0 In (Select Fld1 From Tbl1)
.......替代語法: Select Tbl0.* From Tbl0 Inner Join Tbl1 On Tbl0.Fld0=Tbl1.Fld1
.......原理: 存在Table0 又同時存在Table1 中的資料正是Inner Join 的結果!
....2.找出不存在Sub Query 中的資料:
.......原始語法:
Select Tbl0.* From Tbl0 Where Not Exists (Select ... From Tbl1 Where Tbl0.Fld0=Tbl1.Fld1....) /
.......Select Tbl0.* From Tbl0 Where Fld0 Not In (Select Fld1 From Tbl1):
.......替代語法: Select Tbl0.* From Tbl0 Left Join Tbl1 On Tbl0.Fld0=Tbl1.Fld1 Where Tbl1.Fld1 Is Null
.......原理: 經由Outer Join 關聯後, 只存在Table0 但是不存在Table1 中的資料, 其Table1 中所有欄位內容均會為Null, 藉此可過濾需要的資料!

以上的想法皆源自資料庫的反正規化, 應用反正規化的多數理由是"提升效能", 小弟要提醒的是要同時衡量反正規化帶來的效能與風險!
另外, "效能" 的影響因素眾多, 在反正規化之外, 是否還有其他有效的做法(例如: 如何有效應用SQL 語法?)!
作者 : little boy(Little Boy)
[ 貼文 22 | 人氣 5961 | 評價 0 | 評價/貼文 0 | 送出評價 1 次 ] 
[ 給個讚 ]  [ 給個讚 ]  [ 回應本文 ]  [ 發表新文 ]  [ 回上頁 ] [ 回討論區列表 ] [ 回知識入口 ]
2004/4/2 下午 04:40:58
您好:

真是很好的一篇文章,本來以為這個問題會石沈大海了!!:p

真的很謝謝您提供這麼好的觀點與相關sql的用法,小弟常以exists去做join的動作,看來現在sql效能可以再調校一翻了~~^^
作者 : flair(flair) 人氣指數超過10000點
[ 貼文 104 | 人氣 13327 | 評價 0 | 評價/貼文 0 | 送出評價 5 次 ] 
[ 給個讚 ]  [ 給個讚 ]  [ 回應本文 ]  [ 發表新文 ]  [ 回上頁 ] [ 回討論區列表 ] [ 回知識入口 ]
2005/2/11 上午 12:49:06
>以上的想法皆源自資料庫的反正規化, 應用反正規化的多數理由是'提升效能', 小弟要提醒的是要同時衡量反正規化帶來的效能與風險!
>另外, '效能' 的影響因素眾多, 在反正規化之外, 是否還有其他有效的做法(例如: 如何有效應用SQL 語法?)!
這個問題小弟也正有感觸,在效能和正規化之間如何取得一個平衡點..
舉例來說:
1.小弟遇過一個資料庫,透過程式將文字檔的資料轉入資料庫內.
所花費時間即需10分鐘了..如果你是User你會做接受嗎?
余是小弟試著看了一下程式 ,發現程式採用一筆筆的新增入db中,當資料筆數很大時,相對花費
時間就變長.
於是小弟試著用sql本身的滙入功能,效能馬上提升,不用到1min即將資料全部滙入進去。
接著小弟又查了一下bulk這個指令可以用於大量資料滙入資料庫時提升效能。

2.,此資料庫查詢一筆資料所花費的時間,儘然足夠去抽一根煙了..(你相信嗎?)
於是小弟又看了一下所下的sql語法.尤於資料庫採用正規化,加上資料庫的筆數又很大很大.而且
指今中採用了大量的join和sub query 故造成效能不張。

經過這些事情後,我常在想資料正規化後對於效能是正面或負面呢?
如果資料庫不做正規化,是否就不會遇到n^3的搜索時間?
尤其當資料庫很大時, join只為把部門代碼.員工代碼等轉成名稱.就要join的半死,加上一些判別條件,實在會花費許多時間.
.這樣做的價值是否值的?
作者 : mydick(ㄉ一ˊㄎㄜˋ) SQL Language優秀好手貼文超過200則
[ 貼文 223 | 人氣 6296 | 評價 2890 | 評價/貼文 12.96 | 送出評價 34 次 ] 
[ 給個讚 ]  [ 給個讚 ]  [ 回應本文 ]  [ 發表新文 ]  [ 回上頁 ] [ 回討論區列表 ] [ 回知識入口 ]
2005/2/13 上午 12:41:11
原小弟(以下以此代表ㄉ一ˊㄎㄜˋ, 以利區分ㄉ一ˊㄎㄜˋ與flair 兩個小弟) 認為應用反正規化的多數理由是"提升效能", 資料庫正規化的首要目的在於"資料正確"! 這兩種價值應該很容易辨識輕重!

AP 效能有數種面向, 因此, 要提升系統效能就有很多著力點, 當然資料庫效能通常是很關鍵的一部分!

如果單純地由資料庫查詢工具(例如: MS-SQL 的Query analyzer 或 Oracle 的 SQL plus) 抓取一筆資料需要耗費一根煙或一柱香的時間, 那麼資料庫部分真的很有問題, 可能是SQL command 的不合適(例如: 加上not 的Subquery), 或是資料表設計不當(例如: 需要經過不相干資料表的關聯才能找到目的資料, 或是缺乏合適的索引), 或是伺服器記憶體過少, 或者.....等眾多原因, 不會單純地只是資料庫正規化所釀成!

資料表筆數過多也會影響查詢效能, 系統設計時應該要治本地考慮處理經年累月的龐大交易資料, 否則, 即使應用了反正規化, 還是會有系統反應偏慢的問題!

正規化使得資訊不集中而需要關聯數個資料表才能提供完整資訊(例如: 一些資料的名稱需要與基本資料關聯才能取得), 在已建立適當主索引且SQL command 不需要對大量資料 table scan 的情況下, 效能影響並不明顯, 剩下的主要則為SQL command 的撰寫稍嫌麻煩而已了!

新小弟也分享了一些資料庫方面改善效能的方法, 由此可見: 要改善AP 效能有不少因素需要關注, 並非單純地將資料庫反正規化即可明顯解決, 反而還要承擔反正規化附帶資料不一的嚴重風險!
作者 : hkln(HKLN.net) Perl卓越專家Oracle卓越專家資訊類作業求救優秀好手一般優秀好手程式設計甘苦談優秀好手C#卓越專家貼文超過2000則人氣指數超過100000點
[ 貼文 2135 | 人氣 122272 | 評價 14600 | 評價/貼文 6.84 | 送出評價 7 次 ] 
[ 給個讚 ]  [ 給個讚 ]  [ 回應本文 ]  [ 發表新文 ]  [ 回上頁 ] [ 回討論區列表 ] [ 回知識入口 ]
2005/2/13 下午 03:12:03
>我有三個table分別為a 文件主檔,b人員主檔,c部門主檔;schema分別如下:
>a
>--
>flow_num varchar2(13)
>empno varchar2(8)

Primary Key 應該使用 NUMBER。

a
--
flow_id number
emp_id number



a
--
flow_id number
flow_num varchar2(13)
emp_id number


>但是若要反向查詢,即查詢所有指定部門的流程,則會使用sub query,但sub query一但資料量有數十萬筆時,便有效能上的疑慮,我的老闆叫我直接在 a 中加入dep_no這個欄位,但這樣一來卻又違反了關聯式資料庫的精神,且也有往後若人員調動所產生的問題。


先用 EXPLAIN PLAN ,找出真正的瓶頸所在。
 板主 : 徵求中
 > 資料庫設計與應用 - 討論區
 - 最近熱門問答精華集
 - 全部歷史問答精華集
 - 資料庫設計與應用 - 知識庫
  ■ 全站最新Post列表
  ■ 我的文章收藏
  ■ 我最愛的作者
  ■ 全站文章收藏排行榜
  ■ 全站最愛作者排行榜
  ■  月熱門主題
  ■  季熱門主題
  ■  熱門主題Top 20
  ■  本區Post排行榜
  ■  本區評價排行榜
  ■  全站專家名人榜
  ■  全站Post排行榜
  ■  全站評價排行榜
  ■  全站人氣排行榜
 請輸入關鍵字 
  開始搜尋
 
Top 10
評價排行
資料庫設計與應用
1 ㄉ一ˊㄎㄜˋ 350 
2 好說 120 
3 BlueTulip 110 
4 老芋仔 90 
5 坤哥 80 
6 cnliou 80 
7 小朱 60 
8 HKLN.net 60 
9 小元元 60 
10 Aries 50 
資料庫設計與應用
  專家等級 評價  
  一代宗師 10000  
  曠世奇才 5000  
  頂尖高手 3000  
  卓越專家 1500  
  優秀好手 750  
Microsoft Internet Explorer 6.0. Screen 1024x768 pixel. High Color (16 bit).
2000-2019 程式設計俱樂部 http://www.programmer-club.com.tw/
0.046875