数据库主键的设计和思考

转自：https://blog.csdn.net/likika2012/article/details/9949949

 

1、 何谓数据库主键

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

1.1 主键的作用

主键的主要作用如下：

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

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

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

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

1.2 主键的特点

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

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

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

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

2、 主键的创建和更改

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

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+……；常见例如201308131616时间等作为标示符

（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

 

 

在做分布式数据采集系统的时候，最初有一张表的外键是自增字段，而分布式的特点就是需要数据在多个数据库之间互相导来导去，这样就会打乱自增字段的值，必须开发一个存储过程或用其他语言开发一个控制器来控制字段不会被打乱，由于硬件和网络的限制，导致该控制器（或存储过程）开发难度极大，同时又有多个系统供应商之间需要开发数据接口，每增加一个供应商，就要重新开发一遍，而每开发一遍，都有巨大的开发难度。不仅如此，自己公司开发的软件之间互相导数据也会存在打乱自增字段值的问题，所以本人强烈建议在设计分布式数据采集系统时，凡是要用到外键的地方，都不要用自增字段了。