[料理佳餚] SQL Server 的叢集索引(Clustered Index)欄位不一定都要是主鍵(Primary Key)

我個人認為 SQL Server 預設拿主鍵(Primary Key)來當叢集索引(Clustered Index)欄位這件事情,應該要被重新考慮,以目的來講,主鍵與叢集索引的關係其實並不大,主鍵的目的是確保資料是唯一且正確的,而叢集索引的目的是提升查詢效率,所以在這點上,我覺得在資料表一個開始設計的時候,預設是分開考量的會比較恰當。

叢集索引有一個關鍵的特性,就是資料表裡面的資料列是根據叢集索引來排序的,因此增刪改資料有包含到索引鍵欄位時,就會引發資料表的排序,如果經常引發資料表的排序,一下子我們就會發現叢集索引的碎片化程度很高,頁面的飽和度很低,增刪改查的操作愈來愈慢。

由於叢集索引這樣子的特性,我們建叢集索引就意謂著我們希望資料表內的資料列按照某種方式排序,所以被拿來建叢集索引的欄位最好不會被更新,或者經常是查詢結果的預設排序規則,而且最好欄位值也是按照著叢集索引的排序規則被新增,例如像是訂單編號或發票號碼,這種大都使用時間順序或是數值順序來編製的欄位值,而另外像是身份證字號、隨機產生的 GUID 值,則不適合拿來當叢集索引欄位。

如何避免主鍵成為叢集索引?

拿不合適的欄位來建叢集索引可說是百害而無一利,那麼我們經過評估後發現主鍵並不適合成為叢集索引欄位,SQL Server 又會在建主鍵的時候自動讓它成為叢集索引欄位,我們該怎麼樣來避免這種事情發生?

很簡單,只是將一個步驟拆成兩個步驟來做而已,原先我們建主鍵時 SQL Server 會自動讓主鍵成為叢集索引欄位,現在我們拆開來,第一個步驟先建叢集索引,第二步驟則才是建主鍵,去避掉這個自動化的動作,假定我有一個 User 資料表,裡面有兩個欄位 IdName,那我想讓 Id 成為主鍵,但不想讓它成為叢集索引欄位,所以我需要額外多一個欄位出來,來代替 Id 成為叢集索引欄位,如果沒有其他特殊的理由,通常我都是多建一個 IDENTITY 欄位來當作叢集索引欄位,那麼 User 資料表原先的兩個欄位再加上一個 IDENTITY 欄位就會長這樣。

第一個步驟先建立叢集索引

第二個步驟則才是建立主鍵

最終 User 資料表建好之後就會是這個樣子

參考資料