数据库主键的设计和思考

文：阿蜜果

日期：2011-10-16

版权所有，转载请注明出处

1、 何谓数据库主键

数据库主键是指表中一个列或列的组合，其值能唯一地标识表中的每一行。这样的一列或多列称为表的主键，通过它可强制表的实体完整性。当创建或更改表时可通过定义 PRIMARY KEY 约束来创建主键。一个表只能有一个 PRIMARY KEY 约束，而且 PRIMARY KEY 约束中的列不能接受空值。由于 PRIMARY KEY 约束确保唯一数据，所以经常用来定义标识列。

1.1 主键的作用

主键的主要作用如下：

（1）保证实体的完整性；

（2）加快数据库的操作速度；

（3） 在表中添加新记录时，数据库会自动检查新记录的主键值，不允许该值与其他记录的主键值重复；

（4) 数据库自动按主键值的顺序显示表中的记录。如果没有定义主键，则按输入记录的顺序显示表中的记录。

1.2 主键的特点

（1）唯一性：一个表中只能有一个主键。如果在其他字段上建立主键，则原来的主键就会取消。

（2）非空性：主键的值不可重复，也不可为空；

（3）必要性：在有些数据库中，虽然主键不是必需的，但最好为每个表都设置一个主键，不管是单主键还是复合主键。它存在代表着表结构的完整性，表的记录必须得有唯一区分的字段，主键主要是用于其他表的外键关联，以及本记录的修改与删除；

（4）无意义性：在开发过程中，读者可能会看到将一些表使用有意义的字段表示主键，例如“用户登录信息表”将“登录名”（英文名）作为主键，“订单表”中将“订单编号”作为主键，如此设计主键一般都是没什么问题，因为将这些主键基本不具有“意义更改”的可能性。但是，也有一些例外的情况，例如“订单表”需要支持需求“订单可以作废，并重新生成订单，而且订单号要保持原订单号一致”，那将“订单编号”作为主键就满足不了要求了。因此读者在使用具有实际意义的字段作为主键时，需要考虑是否存在这种可能性。

2、 主键的创建和更改

  在MySQL中，创建时指定某字段为主键的举例如下：

create table article (
id int(4) primary key not null auto_increment,
name varchar(100) not null,
author varchar(32) not null,
publishTime datetime default NULL
);

  标示为主键的字段需要使用“primary key”标识。

若要删除某个表中之前的主键，并设置另外一个字段作为主键，参考语句如下：

ALTER TABLE 表名 DROP PRIMARY KEY, ADD PRIMARY KEY (新的主键字段);

主键可以在执行create table操作时，在字段的后面使用“primary key”指定，也可以不位于字段后面，例如如下语句：

 

create table ss_schedule
(
    schedulekey            varchar(30) not null,
    schedulename           varchar(255),
    updatekey              varchar(30),
    primary key (schedulekey)
);

 3、 主键的选择以及优缺点比较

3.1 自增主键

       这种方式是使用数据库提供的自增数值型字段作为自增主键，它的优点是：

（1）数据库自动编号，速度快，而且是增量增长，按顺序存放，对于检索非常有利；

（2）数字型，占用空间小，易排序，在程序中传递也方便；

（3）如果通过非系统增加记录时，可以不用指定该字段，不用担心主键重复问题。

其实它的缺点也就是来自其优点，缺点如下：

（1）因为自动增长，在手动要插入指定ID的记录时会显得麻烦，尤其是当系统与其它系统集成时，需要数据导入时，很难保证原系统的ID不发生主键冲突（前提是老系统也是数字型的）。特别是在新系统上线时，新旧系统并行存在，并且是异库异构的数据库的情况下，需要双向同步时，自增主键将是你的噩梦；

（2）在系统集成或割接时，如果新旧系统主键不同是数字型就会导致修改主键数据类型，这也会导致其它有外键关联的表的修改，后果同样很严重；

（3）若系统也是数字型的，在导入时，为了区分新老数据，可能想在老数据主键前统一加一个字符标识（例如“o”，old）来表示这是老数据，那么自动增长的数字型又面临一个挑战。

MySQL（auto_increment）、SQL Server（IDENTITY）、Informix、Oracle（首先创建自增序列，接着为自增主键的表创建插入时的触发器，给自增主键ID赋值）等数据库都支持这种自增主键，这种主键在各种系统中应用广泛，但是如果考虑到有新旧系统并存等问题，为了避免不必要的麻烦，使用自增主键要三思。

3.2 max+1主键

由于增主键存在的缺点，所以有些开发人员就采用自己生成同样是数字型的的主键，但不是系统自动生成的。具体的方式是：在INSERT时，读取主键ID的Max值后，加1，这种方法可以避免自动编号的问题，但是也存在各种缺点：

（1）效率问题：如果记录非常大的话，那么Max()也会影响效率的；

（2）并发性问题：如果同时有两人读到相同的Max后，加一后插入的ID值会重复。

基于这些明显的缺点，笔者不提倡使用此种方式。

3.3 自制+1主键

考虑max+1的效率后，有的开发人员采用自制加1，也就是建一个特别的表，字段为：表名、当前序列值。这样在往表中插入值时，先从此表中找到相应表的最大值后加一，进行插入，但也可能会存在并发处理，但是开发人员可以采用lock线程的方式来避免：在生成此值的时，先Lock，取到值以后，再unLock出来，这样不会有两人同时生成了。这比max+1的速度要快多了。

就算解决了并发的问题，但此种方式同样存在致命的缺点：

（1）在与其他系统集成时，脱离了系统中的生成方法后，很麻烦保证自制表中的最大值与导入后的保持一致。

因为此种还需要创建新表，比较麻烦，因此笔者也不推荐此种方式。

3.4 具有实际意义的主键

         有些表可以使用具有实际意义的主键，但这种表为数不多，因为要保证该字段长久的具有行记录唯一的特点，如若有可能变成该表中的非唯一字段，那它就不适合将其变成主键。

         笔者建议有些表可以使用具有实际意义的主键，例如“用户信息表”的“用户登录名”字段基本都是唯一的，而且几乎不可能变成一个登录名对应两条记录，因此可以使用其作为主键。另外，例如“一号通用户信息表”中，“一号通号码”肯定是唯一的，因此也可作为主键。

3.5 GUID主键

目前一个比较好的主键是采用GUID（Globally Unique Identifier，全球唯一标识符），GUID的特点如下：

（1）   在空间上和时间上具有唯一性，保证同一时间不同地方产生的数字不同；

（2）   世界上的任何两台计算机都不会生成重复的GUID值；

（3）   需要GUID的时候，可以完全由算法自动生成，不需要一个权威机构来管理；

（4）   GUID的长度固定，并且相对而言较短小，非常适合于排序、标识和存储。

可以将GUID主键定义为字符型，但值由GUID生成，GUID是可以自动生成，也可以程序生成，而且键值不可能重复，可以解决系统集成问题，几个系统的GUID值导到一起时，也不会发生重复，就算有“o”老数据也可以区分，而且效率很高。在SQL里也可以使用 NewID()生成。主要优点是：

（1）同 IDENTITY 列相比，uniqueidentifier 列可以通过 NewID() 函数提前得知新增加行的ID，为应用程序的后续处理提供很大方便；

（2）便于数据库移植，其它数据库中并不一定具有 IDENTITY 列，而 GUID列可以作为字符型列转换到其它数据库中，同时将应用程序中产生的GUID值存入数据库，它不会对原有数据带来影响。

缺点是：

（1）GUID值较长，不容易记忆和输入，而且这个值是随机、无顺序的。

（2）GUID的值有16个字节，与其它诸如 4 字节的整数相比要相对大一些。这意味着如果在数据库中使用 uniqueidentifier 键，可能会带来两方面的消极影响：存储空间增大、索引时间较慢。

基于上面的分析，使用GUID的利大于弊，笔者推荐可以采用此种方式。

3.6 自制唯一字符型主键

         为了系统集成等的方便，笔者建议将主键定义成字符型，可以使用GUID作为主键，也可以定义一个字符型的主键字段，但是它的值使用变成编程指定。该种方式的优点是：

（1）在异库异构数据库的情况下，若旧系统这些表的主键ID是自增的（数值型），而新系统生成的主键是比较长的字符串型（例如15位），那样旧系统生成或之前的旧数据同步到新系统时，都不会冲突。

这种方式的缺点是：

（1）       需要程序能生成定长的唯一字符串，例如：当前时间+自动机号+进程ID+……；

（2）       由第三方系统生成或手动生成该主键时，处理比较麻烦。

3.7 总结

         在上面几种主键中，较为常用的是“3.1 自增主键”和“3.5 GUID主键”，当在有些特殊的表和某些特定情况中，也可以采用“3.4 具有实际意义的主键”和“3.6 自制唯一的字符型主键”。

4、 参考文档

（1）《主键设计用什么字段类型比较好》：

http://www.th7.cn/Article/bc/de/200902/20090220080511.html

（2）《主关键字_百度百科》：

http://baike.baidu.com/view/68068.htm

（3）《GUID_百度百科》：

http://baike.baidu.com/view/185358.htm