以 GUID 當做主鍵(PK)對於效率的影響
-
2008年5月8日 上午 07:41
剛剛在 MSDN 上查別的東西的時候，無意間看到這一篇，當中有這麼一段文字：
GUID 值又長又難以理解，對使用者而言不具任何意義。如果將隨機產生的 GUID 用於索引鍵值，且插入大量資料列，則會使隨機 I/O 進入索引，進而對效能產生負面影響。而且相較之下，GUID 比其他資料型別還要大。因此，一般建議僅在其他資料型別都不適用的極少案例中，才使用 GUID。
官方說明文件都這樣說了......
目前已近開發完成的案子，已經用了一堆 GUID 欄位去做 PK (and FK) ......
要改..工程浩大，不改..又怕有後遺症.....
想問問看，它是真的會嚴重影響效率嗎，還是說只是稍微？
假設說將 PK 都改為 int 去做為例，衍生出來的其它問題，如：編號的管理、重複的問題、前端程式為了取得唯一值而多出的 DB 查詢以及鎖定問題......等等
這些問題要解決，勢必要仰賴前端應用程式，這樣一來所增加的額外的 DB 存取與前端程式的負擔，與直接用 GUID 做掉所影響的效能孰輕孰重呢？
謝謝~
解答
-
2008年5月8日 上午 08:18版主
我自己的使用經驗是還好。
而且識別碼本來就不是給使用者看的，如果使用 IDENTITY，重覆率會維護會更麻煩。
但 IDENTITY 的優點，就是清楚，以及大小比較小 (4 bytes)。
GUID 則是不清楚（128位元的 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx）和大小比較大 (16 bytes)。
以多重參照的資料表來看，如果一個表有四個 FK 和一個 PK，那就是有五個 GUID，一列至少有 16*5 = 80bytes，那十萬列就是 80bytes * 100,000 = 8,000,000bytes，也就是至少 8MB，不過這種算法太簡單了，因為一列可能不只這些。
另外，如果要做手動查詢的話，使用 GUID 來做確實比用數字做來的不方便。
GUID 是整數型別 (int) 的四倍大，以硬碟和記憶體資源還很貴的時候，當然不要用，因為隨便用的話資料庫會膨脹很快的，但現在儲存資源已經這麼便宜了，用 GUID 來做也不是壞事。
不過 GUID 最大的好處就是不會重覆（至少數十億筆資料後才有一次重覆），光這點我自己就認為值得用了。
因此，只要 I/O 夠強，然後不要把 GUID 送上 Internet 去傳輸的話，我想效能其實也不會差到哪去。
-
2008年5月8日 上午 08:18
最主要還是要看最後你的資料量有多大 造成的效能影響有多大
修正成int有沒有成本效益...
都已經快開發完成了 也不是說改就改
搞不好一改 其他問題又冒出來 要想清楚....
所有回覆
-
2008年5月8日 上午 08:18版主
我自己的使用經驗是還好。
而且識別碼本來就不是給使用者看的，如果使用 IDENTITY，重覆率會維護會更麻煩。
但 IDENTITY 的優點，就是清楚，以及大小比較小 (4 bytes)。
GUID 則是不清楚（128位元的 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx）和大小比較大 (16 bytes)。
以多重參照的資料表來看，如果一個表有四個 FK 和一個 PK，那就是有五個 GUID，一列至少有 16*5 = 80bytes，那十萬列就是 80bytes * 100,000 = 8,000,000bytes，也就是至少 8MB，不過這種算法太簡單了，因為一列可能不只這些。
另外，如果要做手動查詢的話，使用 GUID 來做確實比用數字做來的不方便。
GUID 是整數型別 (int) 的四倍大，以硬碟和記憶體資源還很貴的時候，當然不要用，因為隨便用的話資料庫會膨脹很快的，但現在儲存資源已經這麼便宜了，用 GUID 來做也不是壞事。
不過 GUID 最大的好處就是不會重覆（至少數十億筆資料後才有一次重覆），光這點我自己就認為值得用了。
因此，只要 I/O 夠強，然後不要把 GUID 送上 Internet 去傳輸的話，我想效能其實也不會差到哪去。
-
2008年5月8日 上午 08:18
最主要還是要看最後你的資料量有多大 造成的效能影響有多大
修正成int有沒有成本效益...
都已經快開發完成了 也不是說改就改
搞不好一改 其他問題又冒出來 要想清楚.... -
2008年5月9日 上午 01:28
喔～所以最主要只是差在佔用的空間，對於效能的影響不是很大囉～
那這樣...我知道怎麼做了，我們公司的硬體設備基本上不會太差，呵～所以不用改了 ^_^
感謝二位大大的經驗談唷～
-
2008年5月16日 下午 07:34我也提供一下自己的經驗:
我前一個project的senior developer想說GUID是唯一的且不會重複,所以當初他在設計整個專案的DB架構時把所有的PK都用GUID.
結果後來他離職了,但是問題也在他離職後漸漸浮現. 例如這個專案需要傳遞非常大量的資料表到前端UI,之前都用一些簡單的sample data在try在寫程式,結果等到程式幾乎都開發完畢掛上客戶的database之後噩夢就來了. 客戶端的資料量非常龐大導致網站運作效率非常的慢,追根究底之後才發現使用GUID造成了不少影響,一個非常簡單的query要執行30-40秒才會有結果,前端常常顯示connection timeout,即使stored procedures用了分頁也才改善一些效能(我之前有到版上詢問過這個問題),要改回int型態也為時已晚,導致我們深受performance不佳之困擾. 用execution plan去分析的結果發現花了95%的時間在table index scan & match上.
一個好的專案因為一個錯誤的決定導致最後客戶認為我們寫出來的成果效率太差而有很不好的印象...
題外話
我還發現那個離職的資深程式設計師利用SQL來產生GUID,但是我記得有很多文章都有說到利用SQL SERVER產生的GUID不是真正的GUID,最好還是用程式來產生. 經過我自己測試的結果,如果在短時間內大量產生的話,SQL產生的GUID會出現流水號的情況...
Reference: http://blog.miniasp.com/post/2008/01/The-Gospel-of-the-GUID.aspx- 已編輯 kennyshu 2009年4月27日 下午 06:38 補上reference
-
2008年5月17日 上午 05:26
謝謝您提供寶貴的經驗談～
想請問一下，您說的「一個非常簡單的query要執行30-40秒...」
該query的結果是大量的資料嗎？還是說是「從大量的資料中query出少數資料」？
因為我的專案其實算起來是屬於小專案，所以基本上不會有非常龐大的資料量，不過一些歷史資料(例如顧客名單、訂單記錄....等)還是會隨著時間越來越久，而越長越大。
因此我的專案在上線初期是不會有大量資料，但是時間久了，就會有可能會有「從大量的資料中query出少數資料」的情形。
再，您說的狀況，在 INSERT 等異動的時候呢，是否效率也很差？
至於 GUID 的產生，原本我不知道還有這種問題耶！不過我是用前端的應用成是產生的啦～ 呵，歪打正著^^
-
2008年5月19日 下午 01:32版主
kennyshu 寫信: 我也提供一下自己的經驗:
我前一個project的senior developer想說GUID是唯一的且不會重複,所以當初他在設計整個專案的DB架構時把所有的PK都用GUID.
結果後來他離職了,但是問題也在他離職後漸漸浮現. 例如這個專案需要傳遞非常大量的資料表到前端UI,之前都用一些簡單的sample data在try在寫程式,結果等到程式幾乎都開發完畢掛上客戶的database之後噩夢就來了. 客戶端的資料量非常龐大導致網站運作效率非常的慢,追根究底之後才發現使用GUID造成了不少影響,一個非常簡單的query要執行30-40秒才會有結果,前端常常顯示connection timeout,即使stored procedures用了分頁也才改善一些效能(我之前有到版上詢問過這個問題),要改回int型態也為時已晚,導致我們深受performance不佳之困擾. 用execution plan去分析的結果發現花了95%的時間在table index scan & match上.
一個好的專案因為一個錯誤的決定導致最後客戶認為我們寫出來的成果效率太差而有很不好的印象...
題外話
我還發現那個離職的資深程式設計師利用SQL來產生GUID,但是我記得有很多文章都有說到利用SQL SERVER產生的GUID不是真正的GUID,最好還是用程式來產生. 經過我自己測試的結果,如果在短時間內大量產生的話,SQL產生的GUID會出現流水號的情況...我是不知道你們是怎麼設計資料表的，但你絕對沒有做索引，查詢也都沒有落在索引上，才會有這樣的下場。
我目前有好幾個專案，PK 都是使用 GUID，但我有做索引，而且我自己會去看查詢的執行效能，因此就算是查詢數十萬列的資料表，時間也只有數秒時間，不可能是 30-40 秒，改回 int 也只是把型態變小 (16 bytes -> 4 bytes) 而已，而且改回去，重覆號碼的問題就會浮現，到時修理起來會更累。
還有，不要以為在開發時期測試時很快，就以為放上正式環境後會很快，那是非常天真又不可取的想法。
正式環境上本來就有很多的因素造成速度變慢，資料庫存取只是原因的一種而已。
另外，我不知道所謂 "SQL Server 產生的 GUID 不是真正的 GUID" 這件事，如果你有文章，可以提供連結來給大家參考，但我的經驗中，SQL Server 的 NEWID() 產生的 GUID 格式是正確的，和外部程式生成的是一樣，兩者都是用相同的 CoCreateGuid() 函式，怎麼可能會不一樣呢？再者，GUID 只是求不重覆。
-
2008年5月20日 上午 02:21
關於 NEWID() ，我剛剛實際測試了一下，我跑了500個，確實沒有看到有流水號的情形耶
-
2008年7月5日 下午 07:00
在查詢GUID學術資料時看到這串討論，很訝異的是結論似乎是以目前機器效能，用GUID效能影響不大（或不必太在意）這種結論?!
就GUID的用途來看，幾乎都會被拿來當PK，按SQL Server的預設值（也是一般人的習慣）它也會是叢集索引，而且它也有可能被很多索引所依附，也就是說一個GUID欄位可能不只有存放一份。
上述設計都合理，但這時問題就來了：
第一是GUID隨機產生難以排序的，而它若是叢集索引時新增資料就會是第一個瓶頸，除非您的Fill factor設的非常小且訂了妥善的索引維護計畫，否則一定會很容易引起索引分葉層級的資料頁爭用，簡單的說就是：慢；
第二是若是再加上一些索引，這個問題就會更雪上加霜，因為其他索引可能不只一個，當資料筆數變多時，百分之百會漸漸變成效能瓶頸。
這也是SQL 2005 Sequential GUID的目的，相關的效能比對數據很多熱心的網友都有實作，請自行參考。
這是在解決一個硬體花費數百萬元、存放資料結構非常簡單,但筆數很巨大的SQL Server 2000資料庫效能問題時的經驗，最後查到的禍首就是GUID.
所以結論應該是：GUID在大多數情境是能被取代的，能不用就不用，除非有不得不用的考量、除非您確定您的Table永遠成長緩慢，除非您的設備能充份配合資料的成長而密集更新。
如果真的要用，請考慮使用Sequential GUID.
- 已提議為解答 kennyshu 2009年4月27日 下午 06:39
-
2008年7月6日 下午 03:22版主
我仍然認為 GUID 不是拖慢效能的根本原因，也許我手上的資料庫不夠大 (數 GB 以現在的標準來說真的不夠大)，但我看過更多，就算不用 GUID 效能一樣爛的資料庫，原因還是索引設計不當，查詢設計不當所造成，GUID 只是儲存的資料量大，在查詢回傳時的資料量大一點而已，這可以由修改 SELECT 去解決，但索引設計不當，不管你查詢調的再好，一樣會有一大堆 TABLE SCAN，效能還是一樣爛。
同樣的，因為 GUID 的資料量比較大，這表示磁碟 I/O 不可以太慢，同時也要定時（約一個星期）就要重整索引。
-
2008年7月11日 上午 09:17
我想任何一個資料庫效能問題的追查，通常都不會是只由一個問題造成的，往往都是由很多小問題隨著資料量不斷的成長而變成一個大問題；索引設計不當是問題、查詢設計不當是問題，而欄位設計不當造成資料量大增或Insert時造成大量分頁而導致額外的I/O也一樣是問題。
以現在的科技而言動輒數TB磁碟空間絕對不是問題，但相較起來磁碟I/O速度比起其他的多年來卻是龜速成長，永遠是不夠快的，就像我之前提到的資料庫是存放在三座每座數百萬，合計約二千萬元的SAN Storage，還有一組人專責維護以及Microsoft原廠支援又如何？還是一樣會出現效能瓶頸啊，所以說若把效能問題的解決寄望在磁碟I/O速度就目前而言遠不如透過設計的手段來得直接有效，否則我想Microsoft官方說明文件也不需要這樣說，也不需要在新版SQL Server出現一個叫Sequential GUID來解決叢集索引分頁的問題了，我想這不正是這個主題討論的重點嗎？- 已提議為解答 kennyshu 2009年4月27日 下午 06:37
.png){kind=spacer}
