【数据库设计】'Max加一”生成主键的注意点

Sql Server数据库的主键设计有多种，比如经典的自增长，欢乐的guid，按照时间生成id（有通过程序生成的方式，还有一种也是通过数据库时间和随机数生成），按照业务模型组合生成有意义的id等等。最近项目中接触到一种模拟自增长自动编号主键的方式，即Max加一

Sql Server数据库的主键设计有多种，比如经典的自增长，欢乐的guid，按照时间生成id（有通过程序生成的方式，还有一种也是通过数据库时间和随机数生成），按照业务模型组合生成有意义的id等等。最近项目中接触到一种模拟自增长自动编号主键的方式，即“Max加一”。

Max加一的原理看上去和自增长是相似的，表的唯一主键也设计成数字型(如bigint)的，只是把自动增长去掉了（表设计器标识规范一栏，“是标识”的选项选择否即可）。在Insert记录的时候，通常情况下的流程大致是这样的：读取当前表的Max主键值后加一，然后按照传递的相关参数，显式插入主键及其他列的值。这种生成主键方式的一个最显著的优点是可以按照自己的规则生成主键。比如有如下生成主键的用户自定义的存储过程usp_GetNewID:

<span>if</span> <span>exists</span> (<span>select</span> * <span>from</span> dbo.sysobjects <span>where</span> id = object_id(N<span>'[dbo].usp_GetNewID'</span>) <span>and</span> OBJECTPROPERTY(id, N<span>'IsProcedure'</span>) = 1)
<span>drop</span> <span>procedure</span> [dbo].usp_GetNewID
<span>GO</span>

<span>CREATE</span> <span>PROCEDURE</span> [dbo].usp_GetNewID
@tableName nvarchar(30),  --表名
@columnName nvarchar(30), --字段名
@NewId <span>int</span> <span>output</span>          --<span>Max</span>(ID)生成的新ID    
<span>AS</span>
<span>BEGIN</span>

<span>DECLARE</span> @MaxId bigint
<span>DECLARE</span> @MaxIdTemp bigint
<span>DECLARE</span> @<span>SQL</span> nvarchar(500)
<span>DECLARE</span> @SQLDBId nvarchar(8)

<span>set</span> @SQLDBId=<span>'10'</span>

--获取原最大ID
<span>set</span> @<span>SQL</span> =<span>'SELECT @Mymaxid= isnull(MAX('</span>+@columnName+<span>'),101) From '</span>+ @tableName;
<span>-- select @MaxId </span>

<span>if</span> @@error=0
<span>begin</span>
    <span>exec</span> sp_executesql @<span>SQL</span>,N<span>'@Mymaxid bigint output'</span>,@MaxId <span>output</span>
<span>end</span>

--生成新ID
<span>if</span> @@error=0
<span>begin</span> 
    <span>select</span> @MaxIdTemp=<span>SUBSTRING</span>(<span>cast</span>(@MaxId <span>as</span> nvarchar),3,100)+1; 

<span>end</span>

<span>if</span> @@error=0
<span>begin</span>
    <span>set</span> @NewId=@SQLDBId+<span>cast</span>(@MaxIdTemp <span>as</span> nvarchar)
<span>end</span>
<span>else</span>
<span>begin</span>
    <span>set</span> @NewId=-1
<span>end</span>

END

通过将@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

<span>if</span> <span>exists</span> (<span>select</span> * <span>from</span> dbo.sysobjects <span>where</span> id = object_id(N<span>'[dbo].usp_AddUser'</span>) <span>and</span> OBJECTPROPERTY(id, N<span>'IsProcedure'</span>) = 1)
<span>drop</span> <span>procedure</span> [dbo].usp_AddUser
<span>GO</span>

<span>CREATE</span> <span>PROCEDURE</span> [dbo].usp_AddUser
<span>AS</span>
<span>BEGIN</span>

<span>DECLARE</span> @Id bigint
<span>EXEC</span>  usp_GetNewID <span>'Users'</span>,<span>'Id'</span>,@Id <span>OUT</span> 
<span>SELECT</span> @Id 

<span>if</span> @@ERROR=0
<span>BEGIN</span>
INSERT <span>INTO</span> Users
(
    Id,
    Name
)
<span>VALUES</span>
(
   @Id,
   <span>'jeff wong'</span>
)    
<span>END</span>

END

用户表简单设计成有Id和Name两个字段，插入的时候，Name的值不受任何干扰，固定为”jeff wong”。

（2）、然后在应用程序中调用如下：

       <span>static</span> <span>void</span> Main(<span>string</span>[] args)
        {
            <span>int</span> counter = 2000;
            Action action = <span>null</span>;
            <span>for</span> (<span>int</span> i = 0; i 
                 {
                     Action method = a.AsyncState <span>as</span> Action;
                     method.EndInvoke(a);
                 }, action);

            }

            Console.Read();

        }

        <span>private</span> <span>static</span> <span>void</span> AddUser()
        {
            <span>try</span>
            {
                <span>using</span> (var conn = <span>new</span> SqlConnection(sqlConnString))
                {
                    SqlCommand cmd = <span>new</span> SqlCommand(<span>"usp_AddUser"</span>, conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    conn.Open();
                    <span>int</span> result = cmd.ExecuteNonQuery();
                }
            }
            <span>catch</span> (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
        }

在今晚本地的几组测试中，无一例外地都抛出了插入重复主键的异常。这个问题在这几天的一个数据同步程序中竟然没有发现，原因就是当时数据库没有或者很少符合条件的需要同步的数据。当然现在所有同步都已经改成通过在存储过程中利用游标顺序处理，这样就合理地解决掉并发插入问题了。

最后，我感觉主键的生成选择还有很多东西可以挖掘，有一些知识可以拿过来深入讨论一下，比如自增长是如何控制并发插入的，诸如此类，欢迎您的意见和建议。