设计表的时候，对于自增列做物理主键使用的一点思考
本文出处：http://www.cnblogs.com/wy123/p/7581380.html
（保留出处并非什么原创作品权利，本人拙作还远远达不到，仅仅是为了链接到原文，因为后续对可能存在的一些错误进行修正或补充，无他）
关于自增列
自增列作为数据库的一个特性之一，在MSSQL和MySQL以及Oracle中都被支持。
之前在网上发现一个类似的问题，是关于MySQL的：“为什么InnoDB表最好要有自增列做主键？”
自增列作为一项特性，（可能）会应用到表的设计方面，不管是在那种数据库平台下。
抛开具体的数据库平台，这个问题可以从更泛的层次去思考，为什么在设计表的时候，需要增加一个自增列做主键？
或者反过来问，设计表的时候，增加一个自增列做主键有什么好处？
多数时候，自增列作为一个冗余字段，在设计表的时候是非必须的，也就是说，一个表中，要不要自增列（甚至要不要自增列作为主键）都是可选的，不是说没有自增列某些表就无法满足业务需求的。
当设计表的时候增加一个自增列已经成为一个非强制的要求的时候，做过数据库设计的人对此问题会习以为常。
但是突然有人问上一句“为什么”，还是很难一句话解释清楚的，
比如刚工作不久的人问的问题就很尖锐：“为什么表中要加一个自增列？有什么好处？看起来这个自增列是可有可无的？”
这是一个DB Designer必须要考虑清楚且要随时去回答一些人的质疑的问题。
业务主键与逻辑主键的选择
这之前首先解释两个概念：业务主键与逻辑主键，以设计一个User表为例，有UserId，UserName，CreateDate等等一些属性
业务主键：把具有实际含义的字段作为主键
通常情况下可以把UserId作为业务主键，主键就暗含了非空+唯一性，一个表中每个UserId是不能为空且唯一的，UserId作为主键并没有什么不妥当的。
通常来说，业务主键在一个表中的地位很明显，也很直观，比如UserId，订单ID，CustomerId，登录名等等。
逻辑主键：采用与业务无关的唯一性的字段做主键，或称之为“伪主键”
自增列或者GUID都可以实现所谓的逻辑主键，这里暂且不不讨论GUID（自增与GUID也是一个很大的话题），在User表中增加一个自增列作为主键，因为自增列是也符合主键的特性，也即非空且唯一。
这个字段的第一个特点是与任何业务逻辑无关，第二个特点是对业务来说是不可见的，
比如设计一张用户表，在满足存储所有用户信息的条件下，该字段作为一个独立的列存在，而不描述任何业务含义。
以上就是所谓的业务主键和逻辑主键，实话说，仅仅从表面上看，后者并没有什么明显的优势，甚至可以说，如果仅仅就这一张表来说的话，有点冗余的感觉。
但是实际应用中，一个应用是具体一系列复杂的表来支撑的，表与表之间存在着一系列的关联关系，比如1对1或者1对多的关系，此时，自增列的作用就可以提现出来了。
业务主键存在的问题
首先说业务主键存在的弊端，业务主键必然要与业务逻辑挂钩，这就意味着业务主键可能是一个或者多个字段的结合，甚至对于每一个业务主键字段都有要求，
比如订单号要求以DH0000000000X开头，或者通常情况下的UserId，比如博客园的登录Id，是介于一定长度之间的字符与数字的组合
这样存在一下几个弊端：
1，当前表的主键与其他表的存在关联关系的时候，必然要存储当前这个表的业务主键信息，甚至不止一个字段，这样无疑增加了表与表之间关联的复杂性与存储空间。
同理，在表之间join的时候，必然要用基于业务主键的各个条件连接，业务增加SQL的复杂性和降低SQL的执行效率。
2，既然是业务主键，也存在潜在的修改的可能性，有人说主键还会修改，不可能吧，其实这种情况还真不少，比如呢？
博客园的登录名，作为一个类似于UserId的东西，可以认为是一个业务主键，其特点就是非空+唯一的
但是这个用户名就是可以修改的，如果其他跟用户信息相关的表存储了用户名，用户名有修改之后，要达到数据的一致性，要修改一系列存储了用户名相关的表。
再比如，如果用邮箱作为用户名进行注册，比如雅虎邮箱关闭之后，
注册的各种宝宝账号，如果相关表中直接存储的这个业务主键，也即直接存储的这个邮箱，在需要给用户发送邮件的地方存一个邮箱信息字段，
那么修改个人邮箱的时候，要涉及多少张表的修改？
另外，在SQLServer中，在Update主键的时候，实际上执行行的是一个delete和insert操作，也就是说会先删除要更新的那条记录，再插入一条记录。
这也是一个代价相对较大的操作。
3，在SQLServer中，默认情况下主键就是聚集索引，非聚集索引的叶子节点会存储一个指向聚集索引的Key值的，
一旦一个大的设计了一个较大的主键，那么会导致任何非聚集索引都会包含一个较大的聚集索引key值，这也无疑使得非聚集索引变得膨胀和占用较大的存储空间。
4，不得不考虑的索引碎片问题，同3，默认情况下主键就是聚集索引，如果聚集索引是一个无规律的字段，新填充进来的数据必然会根据排序规则随机存放，
一旦随机存放，极有可能导致不断的页拆分与伴随着碎片的产生，这对性能来说也不是什么好现象
以上是没有自增列的时候，业务字段作为主键可能潜在的问题。
自增列作为主键解决的问题
如果使用自增列作为主键，均可以避免上述问题，
对于问题1和2，表与表之间的关联，一个自增列就可以完成了，在表之间的join关系的时候，从表仅需存储主表的一个自增字段，就可以将表之间的关联关系串起来
比如博客园的用户名修改，当你修改了用户名，因为其他跟用户相关的表存储的是一个与任何业务没有关联关系的自增字段，随你怎么修改用户名，其他表是根据自增Id来管理的，
自增列的信息永远使用户是不可见，也就意味着永远不会被修改。
对于问题3和4，自增列做主键的时候是唯一的，这就意味着不会存在复合主键列的情况，同时也会消除因为随机字段做聚集索引造成的碎片问题。
自增列主键与聚集索引的补充
另外一个不可回避的话题就是，聚集索引在查询时候的宝贵性，聚集索引（B树）本身就数据，可以通过聚集索引直接找到数据。
在进行某些查询或者排序的时候，聚集索引的对于查询性能的好处，一旦将聚集索引建立在一个无业务无关的字段上，聚集索引的价值就白白浪费了。
在SQLServer数据库中，主键与聚集索引没有必然的关系（MySQL的InnoDB就例外了），聚集索引与其所在字段的是否为空和唯一性也没有必然的关系
意味着自增列仅仅是为了消除业务主键可能潜在的问题而存在，而不影响聚集索引的自由选择。
如果设计了自增列，自增列做了主键，主键又是聚集索引，我们知道聚集索引的宝贵性，一张表只能有一个聚集索引。
在sqlserver中，这一点设计的比较灵活，就是主键不一定非要是聚集索引，可以指定主键是非聚集索引，指定其他键为聚集索引
同时，对于有唯一性含义的字段，可以指定为NOT NULL+UNIQUE约束，也保证其属性的非空和唯一性。
对于聚集索引的选择，可以根据具体的业务需要作出合理的选择，比如经常需要按照时间范围查询或者排序，那么就可以将聚集索引建立在CreateDate字段上。
但是在MySQL的InnoDB中，就没有sqlserver上这一点灵活，
主键必须是聚集索引（即便是没有主键或者唯一约束字段，会自动生成一个主键做聚集索引），这一点没有商量的余地，因此，在MySQL中，“为什么InnoDB表最好要有自增列做主键？”这一点就显得更加有必要。
自增列存在的问题
以上说明了，自增列作为主键解决了业务字段作为主键存在的问题，但是自增列也并非万能的，
1，仅仅从存储的角度来看，一个最直观的特点就是冗余存储，也就存储了一个与业务无关的字段，尽管这个字段有其本身存在的诸多的合理性。
2，并发插入的时候，如果自增列作为聚集索引，可能导致新进来的数据存储在一个逻辑页面上，也就是所谓热点页。
对于这一点，严格来说，也不仅仅是自增列引起的，而是聚集索引的选择与并发写入时候本身就是一个矛盾体。
我们总是想让数据按照某种方式来顺序存储，比如按照时间顺序存储，但是在并发存储的时候又会发生热点页的争用问题。
3，对于分布式数据库，或者多个数据库的数据进行合并作为OLAP分析的时候，每个库中的自增列是独立的，可能会存在冲突的问题
不过这个问题也不是问题，自增列可以指定自增的开始值和递增规律，来绕开不同数据源中自增列冲突的问题。
比如A，B两个业务数据库的同样性质的一个表，可以指定在A库中，表上的自增列从1开始，B库中的自增列从100000000开始
甚至可以指定自增每次增加的值，A库的表的自增列从1开始，每次加2，结果就是1,3,5,7,9,11……；B库的表的自增列从2开始，每次加2，结果就是2,4,6,8,10……
设置可以根据需求，对自增列的自增特点做的更复杂，这样就会避免在对多个库的数据进行合并分析的时候自增列了的冲突。
4，类似于自增列的Sequence，Sequence与自增列有部分类似的地方，可以看做是自增列在功能上的扩展。关于Sequence就不多阐述了。
自增列使用的范例：
最后看一下参考一下微软给出的AdventureWorks2012示例数据库中的订单表的设计，有兴趣的可以看一下表结构的字段信息。
从业务上看，订单信息，在业务上看，必然有一个能决定订单唯一性的因素：比如CustomerId+OrderDate从理论上说就可以决定唯一的一条数据，
但是他依然使用了一个自增列作为主键，并且在子表，也即订单明细表中使用主表的自增列作为外键，
而对于订单明细表，依然有一个自增列作为主键，因此，可以认为自增列作为逻辑主键，相对还是有一定的优势的。
总结：
自增列作为一个冗余存储的字段，在复杂的表结构之中，起着承上启下的关联作用，相当于使用一种轻量级的冗余，来简化业务主键带来的繁复。
作为一个与任何业务无关的字段，避免业务主键在某些特殊情况下修改带来的一系列问题。
