GUID Primary Key資料庫避雷守則
27 | 51,510 |
【聲明】該不該用GUID當Primary Key是可以讓開發人員大戰三百回合的好題材,由標題可知我屬於GUID陣營,這篇文章不打算花時間論證該不該用GUID PK,假設讀者已接受使用GUID當PK,只聚焦如何避免GUID PK導致資料庫效能悲劇。
故事源起MVP James最近寫的幾篇GUID鬼故事(包含一起寫入資料3秒變40秒的案例,也實證了GUID作為叢集索引造成索引破碎現象,值得一看),讓我有所警覺,身為一個偏好GUID Primary Key的開發者,有必要正視這個問題,避免掉進資料庫效能陷阱。
簡單歸納我愛用GUID當Primary Key的理由:
- 不仰賴資料庫就能取得唯一鍵值
建立新資料的當下就能決定PK,放心地使用它衍生相關應用。即使程式執行當下資料庫還沒建好、三個月後才會寫入資料庫,或是一年後來自十個資料庫的資料要合併成一個資料表,都不必擔心鍵值重複的問題。(註:傳說GUID仍有重複的可能,但機率極低,在此忽略) - 無法被猜測,用於參數時內含安全防護效果
GetTradeData.aspx?no=617ad98a-c010-4cd2-bc07-9a64d907154f 比 GetTradeData.aspx?no=6386 安全,可避免惡意人士修改參數嘗試存取非授權範圍資料的風險。
但使用GUID當Key也有缺點,例如:
- 不利於人工查詢或偵錯
例如:GetTradeData.aspx?no=617ad98a-c010-4cd2-bc07-9a64d907154f,SELECT * FROM MyTable WHERE Id = '617ad98a-c010-4cd2-bc07-9a64d907154f',大多得靠複製貼上處理參數,難以手工輸入。 - 增加儲存空間
每個GUID有16 Byte,相較於整數4 Byte,多耗用4倍的儲存空間。 - 衝擊資料庫效能
GUID的非連續特性,易導致索引破碎(Index Fragement),降低系統效能。
依我的看法,人工查詢不便頂多費工不會致命,12 Byte vs 4 Byte在資料筆數很龐大時才有顯著影響,而第3點可能嚴重影響效能,輕忽不得。
由James提供的案例可知,在資料庫使用GUID PK,稍有不慎便會發生悲劇。所以該好好思索,如果你打算使用GUID PK,要怎麼樣才能避免掉下效能陷阱?
首先,鬼故事裡有一個共同關鍵-「GUID PK被設成叢集索引(Clustered Index)」。我們都知道,資料庫的索引有兩種,Clustered Index與Nonclustered Index,依據MSDN,二者比較如下:
- Clustered Index
叢集索引決定資料表儲存資料的實際順序,每個資料表最多只能有一個叢集索引,而叢集索引不需額外空間儲存鍵值。有叢集索引的資料表稱為叢集資料表(Clustered Table)。找到索引鍵時一併找得到資料列,查詢效能比非叢集索引好,用在最常用的鍵值(例如:Primary Key)可以產生最大效益。若資料表沒設叢集索引,資料會以沒有固定排序的方式儲存,這種儲存結構稱之為Heap。 - Nonclustered Index
非叢集索引需在實際資料列之外另行建立資料結構,每筆索引除了索引包含欄位外,還需要一個指標(Pointer)指向資料位置,若資料表有叢集索引,指標指向叢集索引的鍵值,若資料表使用Heap結構,指標直接指向資料列本身。
非叢集索引可以附加非鍵值欄位以突破16欄900Bytes的索引鍵值上限,完全涵蓋查詢條件提升效能。(參考:Create Indexes with Included Columns.)
PK使用頻率很高,設成叢集索引對效能最有利,故慣例上會設成叢集索引以提升效能。然而,因為GUID具有不連續的隨機性,即使循序寫入資料,常常後寫的資料GUID排序較前,依叢集索引特性,實體儲存位置應擺在前段,造成每次寫入資料都需挪動調整既有資料造成索引破碎,拖累寫入與查詢效能。
由此看來,將GUID PK設為叢集索引的缺點蓋過優點。轉念一想,沒人規定PK一定要設成叢集索引呀!只要將PK改為非叢集索引就能避開GUID導致索引破碎的危機。當然,非叢集索引的效能不如叢集索引,但減損幅度不致太嚴重,相較於其降低的風險是划算的。
但這衍生另一個議題,PK不設成叢集索引,資料表就只有非叢集索引可行嗎?前面提到,沒有叢集索引的資料表稱為Heap Table。
依據MSDN,Heap Table只適合資料極少(例如數十筆)的場合,即使不設任何Index,Table Scan也很有效率,或者某些資料架構師會巧妙利用非叢集索引配合Row Identifier (RID,由File Number, Data Page Number, Slot on The Page組成,長度不長),達到比叢集索引還好的效率。但絕大部分的情況下,設定叢集索引有好處:
- 循序讀取一段資料時,叢集索引可以節省排序動件
- GROUP BY時,分群前必須先排序,叢集索引可以省去排序作業
- 記得避免資料多又無非叢集索引可用的狀況,如此永遠只能Table Scan,包準慢死你
而我覺得缺乏叢集索引的最致命點是-Heap Table也會產生破碎現象,一旦出現,依MSDN的建議是建個Clustered Index再砍掉,網路上提到的其他做法還有把資料先搬出來再重新塞回去、匯出到新資料表再更名,不管哪一種做法,聽起來都好沒效率,好可怕。[2016-01-30更新]SQL 2008起,增加了ALTER TABLE … REBUILD指令,背後使用建立Clutstered Index再刪除做法,但省去建增刪過程連動更新非叢集索引的浪費,明顯提升效率。感謝James補充。
至此,我得到兩點結論:1.將GUID PK設成非叢集索引利大於弊 2.資料表欠缺叢集索引就會形成Heap Table,弊大於利。所以,最好的折衷方案就是「GUID PK設成非叢集索引,並另外增設叢集索引」,而這個額外的叢集索引,自動跳號整數會是首選。如此我們降低GUID PK導致索引破碎的風險,自動跳號叢集索引避免新增資料造成索引破碎,而叢集索引讓資料表可以透過索引重建重組改善破碎狀況,同時避開索引破碎及Heap Table陷阱。
綜合以上,來段CREATE SCRIPT示意:
排版顯示純文字 CREATE TABLE [dbo].[MiniFlow]( [SeqNo] [int] IDENTITY(1,1) NOT NULL, [FlowId] [uniqueidentifier] NOT NULL, [FormCode] [varchar](4) NOT NULL, [FormNo] [varchar](16) NOT NULL, [Subject] [nvarchar](256) NOT NULL, CONSTRAINT [PK_MiniFlow] PRIMARY KEY NONCLUSTERED ( [FlowId] ASC ) ) GO CREATE CLUSTERED INDEX [IX_MiniFlow] ON [dbo].[MiniFlow] ( [SeqNo] ASC ) GO |
有幾個重點:
- PK外外增設SeqNo INT,以IDENTITY(1,1)設定自動跳號
- FlowId為GUID是MiniFlow資料表的Primary Key,但設定時加上NONCLUSTERED指定為非叢集索引
- 利用CREATE CLUSTERED INDEX將SeqNo建為叢集索引
依我的看法,這是可以克服GUID PK負面效應的最佳設計,在Stackoverflow上也獲得印證,大家如有不同看法,歡迎提出討論。手邊一些使用GUID PK的現有系統效能都還OK,不打算積極調整,但日後開發使用GUID PK新系統,我應該會採用這種設計方式。
最後提一下NEWSEQUENTIALID,有不少人建議用它取代GUID避免索引破碎,但為NEWSEQUENTIALID只能用於資料庫INSERT時自動產生,又可以被預測,並不符合我期待GUID PK應提供的隨時可取及防猜保密要求,我認為只適合用在處理跨資料庫合併用的鍵值。
Comments
# by oaww
請教一下暗黑大,如果發生索引破碎定期做Index Rebuild是否可以有效改善呢?@@
# by sueprshowwei
黑大,那這樣「一年後來自十個資料庫的資料要合併成一個資料表,都不必擔心鍵值重複的問題。」這個使用 GUID 當 PK 的優點是不是就消失了呢?還是說這個優點其實還是存在的,需要做一點手腳?
# by Jeffrey
to oaww, 是的,只要Index Rebuild的頻率趕得上資料更新造成的破碎量,效能仍會在可接受範圍。若非如此,我手上的現有系統(千萬筆等級)應該早就爆炸了。
# by Jeffrey
to sueprshowwei, 不太理解合併資料表時GUID PK優點消失的原委,需要你做些補充。
# by supershowwei
我們多一個欄位 [SeqNo] 建立 CLUSTERED INDEX 解決 GUID PK 的副作用,當多個相同結構的資料表要合併成一個資料表的時候,我們要怎麼處理 [SeqNo] 重覆的問題?還是說我多慮了?
# by Jeffrey
to supershowwei,原來如此。合併的目標資料表SeqNo也設自動跳號,合併資料時排除原有SeqNo欄位,交由合併資料表自己跳號,由於SeqNo並不具邏輯上的意義,純為避開Heap Table的缺點,合併前後SeqNo不同不致出現副作用。
# by 亂馬客
一般來說,Cluster Index也可以設定在 for 整批Search 所用的欄位,例如 有時 Order 會使用 Order Date 去Search 一季的 Order. 當然,這通常是真的有遇到效能問題時,才會去思考的問題吧... 這問題之前在 SQL PASS上也有討論過, 個人也讚同版大這樣的作法,推推
# by tomexou
在大陸技術群裏大家討論DB表格的PK值,該用INT或是GUID之爭?撇開性能及儲存成本不談,最後大家的共識是GUID只在資料合併及新增項目時有利(10%),其他時刻它只是一個ID符號而己(90%)。INT(PK) + GUID (選擇性字串欄位)這樣的設計,就可以打敗GUID當PK的理由,獲得效能及UI查詢比對的方便性。
# by James
SQL Server 2008 之後,針對 Heap 破碎的狀況,可以透過 Alter Table xxx Rebuild 而不需要特別去建立一個 Cluster Index , 或者是把資料匯出再匯入,那個主要都是 SQL Server 2005 之前的做法了。
# by Jeffrey
to James, 感謝補充,已加入本文。查了資料 http://www.sqlmaestros.com/sql-server-alter-table-rebuild/ ,看起來背後也是靠暫時性叢集索引重組資料,但效率較佳。
# by JerryH
請問黑大,此篇避雷守則是for Oracle也適用嗎? 謝謝!!
# by Jeffrey
to JerryH, 隨機性叢集索引導致索引破碎的現象在Oracle上也會發生,我認為避免將GUID(或隨機性質鍵值)設成叢集索引,另取自動跳號整數當成叢集索引的概念在Oracle也適用,但細節需要調整(例如:UniqueIdentifier -> RAW(16)…)
# by isarhoo
請教一下,那是不是查詢及join資料時要改用SeqNo來取資料比較好,還是一樣用guid就好?
# by Jeffrey
to isarhoo, SeqNo與Guid PK二者都是Unique Index,雖然有int vs uniqueidentifier的長度差異,我認為效能相差有限。對程式邏輯來說SeqNo是隱形的,我想用PK比較直覺方便。
# by 余小章
Hi 黑大, 用SeqNo,將來資料庫合併的時候,不就有重複的問題?重複資料對Cluster沒影響嗎?
# by Jeffrey
to 余小章,依我的想法SeqNo只用來當叢集索引解決索引重建重組,不且實質意義,合併資料表重新以自動跳號重新給值即可,與原表格不同也沒關係,故不會有重複問題。
# by 余小章
黑大: 理解你的意思。 再請教一個問題,重新給值,是要自己寫code處理?還是透過工具匯入 or 合併資料時,會自動處理?
# by Jeffrey
to 余小章,最簡單的做法: INSERT INTO MergedTable (Col1,Col2...) SELECT Col1,Col2,... FROM SourceTable Col1,Col2...列出SeqNo以外的所有欄位排除,交由SQL自己跳號即可,除非有特殊需求,不需特別寫Code或找工具。
# by 余小章
黑大: 拍腦,怎麼沒想到略過該欄位,一語驚醒夢中人阿
# by tony520
Hi~ 因為很多老系統都是使用流水號PK,新增非叢集唯一索引的GUID欄位,應該是影響較小的方式了 然後………大大提供的SQL語法內容裡,是不是少了一個右括號收尾? CREATE TABLE [dbo].[MiniFlow]( [SeqNo] [int] IDENTITY(1,1) NOT NULL, [FlowId] [uniqueidentifier] NOT NULL, [FormCode] [varchar](4) NOT NULL, [FormNo] [varchar](16) NOT NULL, [Subject] [nvarchar](256) NOT NULL, CONSTRAINT [PK_MiniFlow] PRIMARY KEY NONCLUSTERED ( [FlowId] ASC ) ) GO CREATE CLUSTERED INDEX [IX_MiniFlow] ON [dbo].[MiniFlow] ( [SeqNo] ASC ) GO
# by Jeffrey
to tony520, 謝謝指正。
# by Lik
黑大,我想問問像你這樣設置到MiniFlow table,結合 EF 後,有沒有辦法 讓Sql server自動編號 FlowId呢? 我試驗過 即使加上[Key][DatabaseGenerated(DatabaseGeneratedOption.Identity)] FlowId。 結合到c# Entity framework, dbcontext insert (entity)後,新的FlowId 還是00000000-0000-0000-0000-000000000000。 無效的原因可能是因為自動編號設置到了 SeqNo,而不是FlowId。 雖然,用c# 寫FlowId = Guid.NewGuid 並不難。 即使在 sql server 設置 FlowID 加上 default = (newid()) 也無用。因為 c# 裡面,
# by Lik
剛剛找到方法了,在edmx裡面設置。 ID 欄位StoreGeneratedPattern: Identity https://dotblogs.com.tw/jojo/2016/06/17/172709
# by eric
黑大: 幾年之後又來挑戰這個問題。 如果ID是採用uniqueidentifier 配 SequentialGuidValueGenerator (https://docs.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.valuegeneration.sequentialguidvaluegenerator?view=efcore-5.0) 這個方案呢? 他的source code在這兒(https://github.com/dotnet/efcore/blob/f7f6d6e23c8e47e44a61983827d9e41f2afe5cc7/src/EFCore/ValueGeneration/SequentialGuidValueGenerator.cs)
# by Jeffrey
to eric, 哦哦哦, 又出現新東西了,有空來研究一下,感謝分享。
# by 馬克
剛剛查了一下,oracle table存在一個虛擬欄位,叫rowid,沒特別異動或設定的話,資料跟rowid就是依新增順序儲存下去。 如果希望資料儲存順序跟著index走,反而需要特別的table。
# by 馬克
看來oracle的rowid,就相當於黑大設計的SeqNo。