【資料庫設計】“Max加一”產生主鍵的注意點

文章目錄

• 1、效率問題
• 2、並發插入問題

Sql Server資料庫的主鍵設計有多種，比如經典的自增長，歡樂的guid，按照時間產生id（有通過程式產生的方式，還有一種也是通過資料庫時間和隨機數產生），按照業務模型組合產生有意義的id等等。最近項目中接觸到一種類比自增長自動編號主鍵的方式，即“Max加一”。

Max加一的原理看上去和自增長是相似的，表的唯一主鍵也設計成數字型(如bigint)的，只是把自動成長去掉了（資料表設計工具標識規範一欄，“是標識”的選項選擇否即可）。在Insert記錄的時候，通常情況下的流程大致是這樣的：讀取當前表的Max主索引值後加一，然後按照傳遞的相關參數，顯式插入主鍵及其他列的值。這種產生主鍵方式的一個最顯著的優點是可以按照自己的規則產生主鍵。比如有如下產生主鍵的使用者自訂的預存程序usp_GetNewID:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].usp_GetNewID') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].usp_GetNewIDGOCREATE PROCEDURE [dbo].usp_GetNewID@tableName nvarchar(30),  --表名@columnName nvarchar(30), --欄位名@NewId int output          --Max(ID)產生的新ID    ASBEGINDECLARE @MaxId bigintDECLARE @MaxIdTemp bigintDECLARE @SQL nvarchar(500)DECLARE @SQLDBId nvarchar(8)set @SQLDBId='10'--擷取原最大IDset @SQL ='SELECT @Mymaxid= isnull(MAX('+@columnName+'),101) From '+ @tableName;-- select @MaxId if @@error=0begin    exec sp_executesql @SQL,N'@Mymaxid bigint output',@MaxId outputend--產生新IDif @@error=0begin     select @MaxIdTemp=SUBSTRING(cast(@MaxId as nvarchar),3,100)+1; endif @@error=0begin    set @NewId=@SQLDBId+cast(@MaxIdTemp as nvarchar)endelsebegin    set @NewId=-1endEND

通過將@SQLDBId='10'和set @NewId=@SQLDBId+cast(@MaxIdTemp as nvarchar)這種方式的組合，我們可以控制不同的資料庫伺服器（或者不同的庫）產生的主鍵都有規律可循，比如第一台伺服器產生的id都以10開頭，第二台都以20開頭，依此類推，這樣多少有利於資料庫的分布式管理。

下面簡單說說這種方式的兩個重大缺陷：

1、效率問題

雖然主鍵有叢集索引，但是當我們的資料表資料達到一定數量級的時候（比如千萬），那麼通過彙總函式Max取值肯定會有不小的代價，這樣顯然會影響一點效率。但是到底效率幾何，和自增長的效能比較又如何？這個我真的還沒有這方面的測試資料，如果有童鞋有這方面的經驗請不吝賜教，懇求告知。

【UPDATE】：根據今天的效能測試，在表已有1百萬資料基礎上，繼續插入資料，每次插入10000條記錄，自增和Max加一這種方式的時間相差不足1秒，總體上自增長的方式會稍快一點，但是並不明顯，在可接受範圍內。測試結果見：

2、並發插入問題

當我們在程式中有順序的先後插入資料的時候，這個問題當然不會發生。但是在大部分應用中，經常會並發處理一些資料，這個時候通過Max加一的方式就會造成插入上的並發問題。因為如果同時有兩個或者多個插入請求讀到相同的MAX值加一以後，在插入的時候就會發生插入重複主鍵的錯誤。

我們可以做一個簡單的測試：

（1）、添加使用者的預存程序usp_AddUser

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].usp_AddUser') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].usp_AddUserGOCREATE PROCEDURE [dbo].usp_AddUserASBEGINDECLARE @Id bigintEXEC  usp_GetNewID 'Users','Id',@Id OUT SELECT @Id if @@ERROR=0BEGININSERT INTO Users(    Id,    Name)VALUES(   @Id,   'jeff wong')    ENDEND

使用者表簡單設計成有Id和Name兩個欄位，插入的時候，Name的值不受任何幹擾，固定為”jeff wong”。

（2）、然後在應用程式中調用如下：

       static void Main(string[] args)        {            int counter = 2000;            Action action = null;            for (int i = 0; i < counter; i++)            {                action = AddUser;                action.BeginInvoke((a) =>                 {                     Action method = a.AsyncState as Action;                     method.EndInvoke(a);                 }, action);            }            Console.Read();        }        private static void AddUser()        {            try            {                using (var conn = new SqlConnection(sqlConnString))                {                    SqlCommand cmd = new SqlCommand("usp_AddUser", conn);                    cmd.CommandType = CommandType.StoredProcedure;                    conn.Open();                    int result = cmd.ExecuteNonQuery();                }            }            catch (Exception ex)            {                Console.WriteLine(ex.ToString());            }        }

在今晚本地的幾組測試中，無一例外地都拋出了插入重複主鍵的異常。這個問題在這幾天的一個資料同步程式中竟然沒有發現，原因就是當時資料庫沒有或者很少合格需要同步的資料。當然現在所有同步都已經改成通過在預存程序中利用遊標順序處理，這樣就合理地解決掉並發插入問題了。

最後，我感覺主鍵的產生選擇還有很多東西可以挖掘，有一些知識可以拿過來深入討論一下，比如自增長是如何控制並發插入的，諸如此類，歡迎您的意見和建議。