数据库的唯一标示符（ID）的选择

分手后的思念是犯贱 2022-08-20 14:26 135阅读 0赞

背景：数年的工作中，已经设计了很多系统或产品的[数据库][Link 1]，有单机的、有局域网环境下的、也有互联网环境下的，对于不同的环境，设计考虑都有所不同。即使对于相同的环境，也会因为业务或者数据量的不同而有不同的设计。近期，又要设计一款互联网产品的数据库（MySQL服务）。经过之前的积累，在表的ID设计这个环节就进行了大量的分析、比较、学习，对ID的设计也有了更系统和深刻的认知，把自己学习实践到的知识总结下来，分享给大家。

--------------------

### 主键id的选择    ###

对于[关系数据库][Link 2]来说，[设计][Link 3]每个表的第一步都会确定其主键，主键就是ID。在“常识”之中，int型的自增id，字符串类型的uuid，其他与业务相关的唯一键…都是我们作为主键的选择。那么是不是说在一张表中只要能保证值唯一的属性列都可以做为主键或者更合适做主键呢？

那我们首先清晰几个概念：

*  逻辑主键（代理主键）：在数据库表中采用一个与当前表中业务逻辑信息无关的字段作为其主键，或称为“伪主键”;
 *  业务主键（自然主键）：在数据库表中把具有业务逻辑含义的字段作为主键;

举一个很常见的例子：一张用户信息表，列属性有id、用户名、[手机号][Link 4]…，其中用户名和手机号（作为登录账号二者都是唯一的）。其中id便可作为逻辑主键，用户名和手机号都可以作为业务主键。那我是不是可以随便选一个，甚至我选择了业务主键都可以不要逻辑主键？

那么我们首先来看看逻辑主键和业务主键之间纷纷烈烈的观点分歧：

*  支持逻辑主键
    
    > 表通过主键来保证每条记录的唯一性，表的主键应当不具有任何业务含义，因为任何有业务含义的列都有改变的可能性。[关系数据库][Link 2]学的最重要的一个理论就是：不要给[关键字][Link 5]赋予任何业务意义。假如关键字具有了业务意义，当用户决定改变业务含义，也许他们想要为关键字增加几位数字或把数字改为字母，那么就必须修改相关的关键字。一个表中的主关键字有可能被其他表作为外键。就算是一个简单的改变，譬如在客户号码上增加一位数字，也可能会造成极大的维护上的开销。
    > 
    > 为了使表的主键不具有任何业务含义，一种解决方法是使用[代理][Link 6]主键，例如为表定义一个不具有任何业务含义的ID字段（也可以叫其他的名字），专门作为表的主键。  
    > ——孙卫琴[《精通Hibernate：Java对象持久化技术详解》][Hibernate_Java]P8
    
      
    
 *  >   
    > 使用逻辑主键的主要原因是，业务主键一旦改变则系统中关联该主键的部分的修改将会是不可避免的，并且引用越多改动越大。而使用逻辑主键则只需要修改相应的业务主键相关的业务逻辑即可，减少了因为业务主键相关改变对系统的影响范围。业务逻辑的改变是不可避免的，因为“永远不变的是变化”，没有任何一个公司是一成不变的，没有任何一个业务是永远不变的。最典型的例子就是\*\*\*升位和[驾驶执照][Link 7]号换用\*\*\*号的业务变更。而且现实中也确实出现了\*\*\*号码重复的情况，这样如果用\*\*\*号码作为主键也带来了难以处理的情况。当然应对改变，可以有很多解决方案，方案之一是做一新系统与时俱进，这对软件公司来说确实是件好事。
    > 
    > 使用逻辑主键的另外一个原因是，业务主键过大，不利于传输、处理和存储。我认为一般如果业务主键超过8字节就应该考虑使用逻辑主键了，因为int是4字节的，bigint是8字节的，而业务主键一般是字符串，同样是 8 字节的 bigint 和 8 字节的字符串在传输和处理上自然是 bigint 效率更高一些。想象一下 id 为”12345678” 和 id为12345678 的汇编码的不同就知道了。当然逻辑主键不一定是 int 或者 bigint ，而业务主键也不一定是字符串也可以是 int 或 datetime 等类型，同时传输的也不一定就是主键，这个就要具体分析了，但是原理类似，这里只是讨论通常情况。同时如果其他表需要引用该主键的话，也需要存储该主键，那么这个存储空间的开销也是不一样的。而且这些表的这个引用字段通常就是外键，或者通常也会建索引方便查找，这样也会造成存储空间的开销的不同，这也是需要具体分析的。
    > 
    > 使用逻辑主键的再一个原因是，使用 int 或者 bigint 作为外键进行联接查询，性能会比以字符串作为外键进行联接查询快。原理和上面的类似，这里不再重复。
    > 
    > 使用逻辑主键的再一个原因是，存在用户或维护人员误录入数据到业务主键中的问题。例如错把 RMB 录入为 RXB ，相关的引用都是引用了错误的数据，一旦需要修改则非常麻烦。如果使用逻辑主键则问题很好解决，如果使用业务主键则会影响到其他表的外键数据，当然也可以通过级联更新方式解决，但是不是所有都能级联得了的。  
    > ——[SwitchBlade的总结][SwitchBlade]
 *  支持业务主键
    
    > 如果你的表中包含一列能确保唯一、非空以及能够用来定位一条记录，就别仅仅因为传统而觉得有必要再加上一个伪主键。  
    > ——Bill Karwin [《SQL反模式》][SQL] p41
    
      
    
 *  >   
    > 使用业务主键的主要原因是，增加逻辑主键就是增加了一个业务无关的字段，而用户通常都是对于业务相关的字段进行查找（比如员工的工号，书本的 ISBN No. ），这样我们除了为逻辑主键加索引，还必须为这些业务字段加索引，这样[数据库][Link 1]的性能就会下降，而且也增加了存储空间的开销。所以对于业务上确实不常改变的基础数据而言，使用业务主键不失是一个比较好的选择。另一方面，对于基础数据而言，一般的增、删、改都比较少，所以这部分的开销也不会太多，而如果这时候对于业务逻辑的改变有担忧的话，也是可以考虑使用逻辑主键的，这就需要具体问题具体分析了。
    > 
    > 使用业务主键的另外一个原因是，对于用户操作而言，都是通过业务字段进行的，所以在这些情况下，如果使用逻辑主键的话，必须要多做一次映射转换的动作。我认为这种担心是多余的，直接使用业务主键查询就能得到结果，根本不用管逻辑主键，除非业务主键本身就不唯一。另外，如果在[设计][Link 3]的时候就考虑使用逻辑主键的话，编码的时候也是会以主键为主进行处理的，在系统内部传输、处理和存储都是相同的主键，不存在转换问题。除非现有系统是使用业务主键，要把现有系统改成使用逻辑主键，这种情况才会存在转换问题。暂时没有想到还有什么场景是存在这样的转换的。
    > 
    > 使用业务主键的再一个原因是，对于银行系统而言[安全性][Link 8]比性能更加重要，这时候就会考虑使用业务主键，既可以作为主键也可以作为冗余数据，避免因为使用逻辑主键带来的关联丢失问题。如果由于某种原因导致主表和子表关联关系丢失的话，银行可是会面临无法挽回的损失的。为了杜绝这种情况的发生，业务主键需要在重要的表中有冗余存在，这种情况最好的处理方式就是直接使用业务主键了。例如\*\*\*号、存折号、卡号等。所以通常银行系统都要求使用业务主键，这个需求并不是出于性能的考虑而是出于安全性的考虑。  
    > ——[SwitchBlade的总结][SwitchBlade]

所以说明逻辑主键和业务主键的选择并不是拍脑瓜的结果，而是根据不同的应用场景、不同的需求决策的结果。

如果我们使用整数类型的自增id作为主键又会面临什么问题呢？  
对于数据量非常大的表后期往往会涉及到水平分表的需求，这时这个自增主键会成为阻碍。（其实关于这种情况也会有解决方案，请参见文章[《又拍网架构中的分库设计》][Link 9]

### ID数据类型的选择 ###

我们再换一个角度考虑主键的选择：数据类型。

*  整数类型：  
    整数类型往往是id列最好的选择，因为效率最高并且可以使用[数据库][Link 1]的自增主键。
 *  字符串类型  
    字符串类型相比整数类型肯定更消耗空间，也会比整数类型操作慢。我主要使用的是Mysql，关于这个话题的解释建议看[《高性能MySQL》第三版][MySQL] P125。

我采用的方案（MySQL）：使用自增id作为主键，以此来应对插入效率问题；采用uuid做逻辑id，拥有了逻辑主键的诸多好处，而且可以用来应对之后的水平分表。

[Link 1]: http://cpro.baidu.com/cpro/ui/uijs.php?adclass=0&app_id=0&c=news&cf=1001&ch=0&di=128&fv=18&is_app=0&jk=5559f96707d4fd05&k=%CA%FD%BE%DD%BF%E2&k0=%CA%FD%BE%DD%BF%E2&kdi0=0&luki=10&mcpm=0&n=10&p=baidu&q=65035100_cpr&rb=0&rs=1&seller_id=1&sid=5fdd40767f95955&ssp2=1&stid=9&t=tpclicked3_hc&td=1836545&tu=u1836545&u=http%3A%2F%2Fwww%2Ebubuko%2Ecom%2Finfodetail%2D428974%2Ehtml&urlid=0
[Link 2]: http://cpro.baidu.com/cpro/ui/uijs.php?adclass=0&app_id=0&c=news&cf=1001&ch=0&di=128&fv=18&is_app=0&jk=5559f96707d4fd05&k=%B9%D8%CF%B5%CA%FD%BE%DD%BF%E2&k0=%B9%D8%CF%B5%CA%FD%BE%DD%BF%E2&kdi0=0&luki=7&mcpm=0&n=10&p=baidu&q=65035100_cpr&rb=0&rs=1&seller_id=1&sid=5fdd40767f95955&ssp2=1&stid=9&t=tpclicked3_hc&td=1836545&tu=u1836545&u=http%3A%2F%2Fwww%2Ebubuko%2Ecom%2Finfodetail%2D428974%2Ehtml&urlid=0
[Link 3]: http://cpro.baidu.com/cpro/ui/uijs.php?adclass=0&app_id=0&c=news&cf=1001&ch=0&di=128&fv=18&is_app=0&jk=5559f96707d4fd05&k=%C9%E8%BC%C6&k0=%C9%E8%BC%C6&kdi0=0&luki=9&mcpm=0&n=10&p=baidu&q=65035100_cpr&rb=0&rs=1&seller_id=1&sid=5fdd40767f95955&ssp2=1&stid=9&t=tpclicked3_hc&td=1836545&tu=u1836545&u=http%3A%2F%2Fwww%2Ebubuko%2Ecom%2Finfodetail%2D428974%2Ehtml&urlid=0
[Link 4]: http://cpro.baidu.com/cpro/ui/uijs.php?adclass=0&app_id=0&c=news&cf=1001&ch=0&di=128&fv=18&is_app=0&jk=5559f96707d4fd05&k=%CA%D6%BB%FA%BA%C5&k0=%CA%D6%BB%FA%BA%C5&kdi0=0&luki=6&mcpm=0&n=10&p=baidu&q=65035100_cpr&rb=0&rs=1&seller_id=1&sid=5fdd40767f95955&ssp2=1&stid=9&t=tpclicked3_hc&td=1836545&tu=u1836545&u=http%3A%2F%2Fwww%2Ebubuko%2Ecom%2Finfodetail%2D428974%2Ehtml&urlid=0
[Link 5]: http://cpro.baidu.com/cpro/ui/uijs.php?adclass=0&app_id=0&c=news&cf=1001&ch=0&di=128&fv=18&is_app=0&jk=5559f96707d4fd05&k=%B9%D8%BC%FC%D7%D6&k0=%B9%D8%BC%FC%D7%D6&kdi0=0&luki=5&mcpm=0&n=10&p=baidu&q=65035100_cpr&rb=0&rs=1&seller_id=1&sid=5fdd40767f95955&ssp2=1&stid=9&t=tpclicked3_hc&td=1836545&tu=u1836545&u=http%3A%2F%2Fwww%2Ebubuko%2Ecom%2Finfodetail%2D428974%2Ehtml&urlid=0
[Link 6]: http://cpro.baidu.com/cpro/ui/uijs.php?adclass=0&app_id=0&c=news&cf=1001&ch=0&di=128&fv=18&is_app=0&jk=5559f96707d4fd05&k=%B4%FA%C0%ED&k0=%B4%FA%C0%ED&kdi0=0&luki=4&mcpm=0&n=10&p=baidu&q=65035100_cpr&rb=0&rs=1&seller_id=1&sid=5fdd40767f95955&ssp2=1&stid=9&t=tpclicked3_hc&td=1836545&tu=u1836545&u=http%3A%2F%2Fwww%2Ebubuko%2Ecom%2Finfodetail%2D428974%2Ehtml&urlid=0
[Hibernate_Java]: http://book.douban.com/subject/4201514/
[Link 7]: http://cpro.baidu.com/cpro/ui/uijs.php?adclass=0&app_id=0&c=news&cf=1001&ch=0&di=128&fv=18&is_app=0&jk=5559f96707d4fd05&k=%BC%DD%CA%BB%D6%B4%D5%D5&k0=%BC%DD%CA%BB%D6%B4%D5%D5&kdi0=0&luki=3&mcpm=0&n=10&p=baidu&q=65035100_cpr&rb=0&rs=1&seller_id=1&sid=5fdd40767f95955&ssp2=1&stid=9&t=tpclicked3_hc&td=1836545&tu=u1836545&u=http%3A%2F%2Fwww%2Ebubuko%2Ecom%2Finfodetail%2D428974%2Ehtml&urlid=0
[SwitchBlade]: http://www.cnblogs.com/LIS2011/archive/2012/10/31/2748001.html
[SQL]: http://book.douban.com/subject/6800774/
[Link 8]: http://cpro.baidu.com/cpro/ui/uijs.php?adclass=0&app_id=0&c=news&cf=1001&ch=0&di=128&fv=18&is_app=0&jk=5559f96707d4fd05&k=%B0%B2%C8%AB%D0%D4&k0=%B0%B2%C8%AB%D0%D4&kdi0=0&luki=2&mcpm=0&n=10&p=baidu&q=65035100_cpr&rb=0&rs=1&seller_id=1&sid=5fdd40767f95955&ssp2=1&stid=9&t=tpclicked3_hc&td=1836545&tu=u1836545&u=http%3A%2F%2Fwww%2Ebubuko%2Ecom%2Finfodetail%2D428974%2Ehtml&urlid=0
[Link 9]: http://www.zolazhou.com/posts/sharding-at-yupoo/
[MySQL]: http://book.douban.com/subject/23008813/