GUID Primary Key資料庫避雷守則

【聲明】該不該用GUID當Primary Key是可以讓開發人員大戰三百回合的好題材,由標題可知我屬於GUID陣營,這篇文章不打算花時間論證該不該用GUID PK,假設讀者已接受使用GUID當PK,只聚焦如何避免GUID PK導致資料庫效能悲劇。

故事源起MVP James最近寫的幾篇GUID鬼故事(包含一起寫入資料3秒變40秒的案例,也實證了GUID作為叢集索引造成索引破碎現象,值得一看),讓我有所警覺,身為一個偏好GUID Primary Key的開發者,有必要正視這個問題,避免掉進資料庫效能陷阱。

簡單歸納我愛用GUID當Primary Key的理由:

  1. 不仰賴資料庫就能取得唯一鍵值
    建立新資料的當下就能決定PK,放心地使用它衍生相關應用。即使程式執行當下資料庫還沒建好、三個月後才會寫入資料庫,或是一年後來自十個資料庫的資料要合併成一個資料表,都不必擔心鍵值重複的問題。(註:傳說GUID仍有重複的可能,但機率極低,在此忽略)
  2. 無法被猜測,用於參數時內含安全防護效果
    GetTradeData.aspx?no=617ad98a-c010-4cd2-bc07-9a64d907154f 比 GetTradeData.aspx?no=6386 安全,可避免惡意人士修改參數嘗試存取非授權範圍資料的風險。

但使用GUID當Key也有缺點,例如:

  1. 不利於人工查詢或偵錯
    例如:GetTradeData.aspx?no=617ad98a-c010-4cd2-bc07-9a64d907154f,SELECT * FROM MyTable WHERE Id = '617ad98a-c010-4cd2-bc07-9a64d907154f',大多得靠複製貼上處理參數,難以手工輸入。
  2. 增加儲存空間
    每個GUID有16 Byte,相較於整數4 Byte,多耗用4倍的儲存空間。
  3. 衝擊資料庫效能
    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組成,長度不長),達到比叢集索引還好的效率。但絕大部分的情況下,設定叢集索引有好處:

  1. 循序讀取一段資料時,叢集索引可以節省排序動件
  2. GROUP BY時,分群前必須先排序,叢集索引可以省去排序作業
  3. 記得避免資料多又無非叢集索引可用的狀況,如此永遠只能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

有幾個重點:

  1. PK外外增設SeqNo INT,以IDENTITY(1,1)設定自動跳號
  2. FlowId為GUID是MiniFlow資料表的Primary Key,但設定時加上NONCLUSTERED指定為非叢集索引
  3. 利用CREATE CLUSTERED INDEX將SeqNo建為叢集索引

依我的看法,這是可以克服GUID PK負面效應的最佳設計,在Stackoverflow上也獲得印證,大家如有不同看法,歡迎提出討論。手邊一些使用GUID PK的現有系統效能都還OK,不打算積極調整,但日後開發使用GUID PK新系統,我應該會採用這種設計方式。

最後提一下NEWSEQUENTIALID,有不少人建議用它取代GUID避免索引破碎,但為NEWSEQUENTIALID只能用於資料庫INSERT時自動產生,又可以被預測,並不符合我期待GUID PK應提供的隨時可取及防猜保密要求,我認為只適合用在處理跨資料庫合併用的鍵值。

歡迎推文分享:
Published 29 January 2016 07:16 AM 由 Jeffrey
Filed under:
Views: 22,557



意見

# oaww said on 28 January, 2016 08:30 PM

請教一下暗黑大,如果發生索引破碎定期做Index Rebuild是否可以有效改善呢?@@

# sueprshowwei said on 28 January, 2016 08:31 PM

黑大,那這樣「一年後來自十個資料庫的資料要合併成一個資料表,都不必擔心鍵值重複的問題。」這個使用 GUID 當 PK 的優點是不是就消失了呢?還是說這個優點其實還是存在的,需要做一點手腳?

# Jeffrey said on 28 January, 2016 08:51 PM

to oaww, 是的,只要Index Rebuild的頻率趕得上資料更新造成的破碎量,效能仍會在可接受範圍。若非如此,我手上的現有系統(千萬筆等級)應該早就爆炸了。

# Jeffrey said on 28 January, 2016 08:53 PM

to sueprshowwei, 不太理解合併資料表時GUID PK優點消失的原委,需要你做些補充。

# supershowwei said on 28 January, 2016 10:13 PM

我們多一個欄位 [SeqNo] 建立 CLUSTERED INDEX 解決 GUID PK 的副作用,當多個相同結構的資料表要合併成一個資料表的時候,我們要怎麼處理 [SeqNo] 重覆的問題?還是說我多慮了?

# Jeffrey said on 28 January, 2016 11:03 PM

to supershowwei,原來如此。合併的目標資料表SeqNo也設自動跳號,合併資料時排除原有SeqNo欄位,交由合併資料表自己跳號,由於SeqNo並不具邏輯上的意義,純為避開Heap Table的缺點,合併前後SeqNo不同不致出現副作用。

# 亂馬客 said on 29 January, 2016 12:12 AM

一般來說,Cluster Index也可以設定在 for 整批Search 所用的欄位,例如 有時 Order 會使用 Order Date 去Search 一季的 Order. 當然,這通常是真的有遇到效能問題時,才會去思考的問題吧...

這問題之前在 SQL PASS上也有討論過, 個人也讚同版大這樣的作法,推推

# tomexou said on 29 January, 2016 12:28 AM

在大陸技術群裏大家討論DB表格的PK值,該用INT或是GUID之爭?撇開性能及儲存成本不談,最後大家的共識是GUID只在資料合併及新增項目時有利(10%),其他時刻它只是一個ID符號而己(90%)。INT(PK) + GUID (選擇性字串欄位)這樣的設計,就可以打敗GUID當PK的理由,獲得效能及UI查詢比對的方便性。

# James said on 29 January, 2016 09:38 AM

SQL Server 2008 之後,針對 Heap 破碎的狀況,可以透過

Alter Table xxx Rebuild

而不需要特別去建立一個 Cluster Index , 或者是把資料匯出再匯入,那個主要都是 SQL Server 2005 之前的做法了。

# Jeffrey said on 29 January, 2016 04:16 PM

to James, 感謝補充,已加入本文。查了資料 www.sqlmaestros.com/sql-server-alter-table-rebuild ,看起來背後也是靠暫時性叢集索引重組資料,但效率較佳。

# JerryH said on 29 January, 2016 08:30 PM

請問黑大,此篇避雷守則是for Oracle也適用嗎?

謝謝!!

# Jeffrey said on 29 January, 2016 09:19 PM

to JerryH, 隨機性叢集索引導致索引破碎的現象在Oracle上也會發生,我認為避免將GUID(或隨機性質鍵值)設成叢集索引,另取自動跳號整數當成叢集索引的概念在Oracle也適用,但細節需要調整(例如:UniqueIdentifier -> RAW(16)…)

# isarhoo said on 13 April, 2016 09:06 PM

請教一下,那是不是查詢及join資料時要改用SeqNo來取資料比較好,還是一樣用guid就好?

# Jeffrey said on 14 April, 2016 12:14 AM

to isarhoo, SeqNo與Guid PK二者都是Unique Index,雖然有int vs uniqueidentifier的長度差異,我認為效能相差有限。對程式邏輯來說SeqNo是隱形的,我想用PK比較直覺方便。

# 余小章 said on 20 June, 2016 09:47 PM

Hi 黑大,

用SeqNo,將來資料庫合併的時候,不就有重複的問題?重複資料對Cluster沒影響嗎?

# Jeffrey said on 20 June, 2016 11:13 PM

to 余小章,依我的想法SeqNo只用來當叢集索引解決索引重建重組,不且實質意義,合併資料表重新以自動跳號重新給值即可,與原表格不同也沒關係,故不會有重複問題。

# 余小章 said on 22 June, 2016 12:38 PM

黑大:

理解你的意思。

再請教一個問題,重新給值,是要自己寫code處理?還是透過工具匯入 or 合併資料時,會自動處理?

# Jeffrey said on 23 June, 2016 02:35 AM

to 余小章,最簡單的做法:

INSERT INTO MergedTable (Col1,Col2...)

SELECT Col1,Col2,... FROM SourceTable

Col1,Col2...列出SeqNo以外的所有欄位排除,交由SQL自己跳號即可,除非有特殊需求,不需特別寫Code或找工具。

# 余小章 said on 28 June, 2016 10:23 PM

黑大:

拍腦,怎麼沒想到略過該欄位,一語驚醒夢中人阿

你的看法呢?

(必要的) 
(必要的) 
(選擇性的)
(必要的) 
(提醒: 因快取機制,您的留言幾分鐘後才會顯示在網站,請耐心稍候)

5 + 3 =

搜尋

Go

<January 2016>
SunMonTueWedThuFriSat
272829303112
3456789
10111213141516
17181920212223
24252627282930
31123456
 
RSS
創用 CC 授權條款
【廣告】
twMVC
最新回應

Tags 分類檢視
關於作者

一個醉心技術又酷愛分享的Coding魔人,十年的IT職場生涯,寫過系統、管過專案, 也帶過團隊,最後還是無怨無悔地選擇了技術鑽研這條路,近年來則以做一個"有為的中年人"自許。

文章典藏
其他功能

這個部落格


Syndication