一、前言
我们常用mysql,在设计表结构的时候必须考虑主键的事情。mysql数据库中有一个主键生成规则，就是自增。本文就给大家从几个维度说明一下mysql自增主键。
二、主键设置原则
- 主键不可修改
对于数据库来说，主键其实是可以修改的，只要不和其他主键冲突就可以。但是，对于应用来说，如果一条记录要修改主键，那就会出大问题。因为主键的第二个作用是让其他表的外键引用自己，从而实现关系结构。一旦某个表的主键发生了变化，就会导致所有引用了该表的数据必须全部修改外键。很多Web应用的数据库并不是强约束（仅仅引用主键但并没有设置外键约束），修改主键会导致数据完整性直接被破坏。
2. 业务字段不可用于主键
所有涉及到业务的字段，无论它看上去是否唯一，都决不能用作主键。例如，用户表的Email字段是唯一的，但是，如果用它作主键，就会导致其他表到处引用Email字段，从而泄露用户信息。类似的，看上去唯一的用户名、身份证号等，也不能用作主键。对这些唯一字段，应该加上unique索引约束。此外，修改Email实际上是一个业务操作，这个操作就直接违反了上一条原则。那么，主键应该使用哪个字段呢？主键必须使用单独的，完全没有业务含义的字段，也就是主键本身除了唯一标识和不可修改这两个责任外，主键没有任何业务含义。
三、MySQL使用自增主键优缺点
1、优点
mysql使用什么字段做主键，是重点考虑两方面：业务场景和效率问题。在满足业务场景的前提下，使用自增序列做主键是最优选择。原因如下： 1.自增主键节省存储空间。 2.innodb的索引特性导致了自增id做主键是效率最好的 与UUID主键对比： 在500W记录表的测试下： （1） 普通单条或者20条左右的记录检索，uuid为主键的相差不大几乎效率相同； （2） 但是范围查询特别是上百成千条的记录查询，自增id的效率要大于uuid； （3） 在范围查询做统计汇总的时候，自增id的效率要大于uuid； （4） 在存储上面，自增id所占的存储空间是uuid的1/2； （5） 在备份恢复上，自增ID主键稍微优于UUID。 在1000W记录表的测试下： （1）普通单条或者20条左右的记录检索，自增主键效率是uuid主键的2到3倍； （2）但是范围查询特别是上百成千条的记录查询，自增id的效率要大于uuid； （3）在范围查询做统计汇总的时候，自增id主键的效率是uuid主键1.5到2倍； （4）在存储上面，自增id所占的存储空间是uuid的1/2； （5）在写入上面，自增ID主键的效率是UUID主键的3到10倍，相差比较明显，特别是update小范围之内的数据上面。 （6）在备份恢复上，自增ID主键稍微优于UUID。 对于InnoDB这种聚集主键类型的引擎来说，数据会按照主键进行排序，由于UUID的无序性，InnoDB会产生巨大的IO压力，而且由于索引和数据存储在一起，字符串做主键会造成存储空间增大一倍。 在存储和检索的时候，innodb会对主键进行物理排序，这对auto_increment_int是个好消息，因为后一次插入的主键位置总是在最后。但是对uuid来说，这却是个坏消息，因为uuid是杂乱无章的，每次插入的主键位置是不确定的，可能在开头，也可能在中间，在进行主键物理排序的时候，势必会造成大量的 IO操作影响效率，在数据量不停增长的时候，特别是数据量上了千万记录的时候，读写性能下降的非常厉害。 总结： （1）单实例或者单节点组： 经过500W、1000W的单机表测试，自增ID相对UUID来说，自增ID主键性能高于UUID，磁盘存储费用比UUID节省一半的钱。所以在单实例上或者单节点组上，使用自增ID作为首选主键。 （2）分布式架构场景： 20个节点组下的小型规模的分布式场景，为了快速实现部署，可以采用多花存储费用、牺牲部分性能而使用UUID主键快速部署； 20到200个节点组的中等规模的分布式场景，可以采用自增ID+步长的较快速方案。 200以上节点组的大数据下的分布式场景，可以借鉴类似twitter雪花算法构造的全局自增ID作为主键
2、缺点
引用《高性能MySQL》中的原话
四、MySQL innodb存储引擎建议使用自增主键剖析
问：为什么建表的时候建议创建自增ID，直接用唯一业务的标识ID不香吗？
答：是的，不香。
InnerDB使用的B+树模型，B+树的结构决定了 所有左节点< 节点< 右节点，这样带来的好处我就不详细说了，坏处就是当新增主键破坏了现有树形结构的时候，就需要将大于这个值得主键向后挪用。当需要挪动的数据页已经处于存满的状态，就会更加的麻烦。但是当我们使用主键递增ID的时候，所有新增的主键都为当前主键的最大值，所以只需要在索引树的最右边加上一个记录即可。这也就是为什么要采用自增ID的原因。举一个的例子：
当你想加一条数据（ID=700）的时候，只需要在700节点的右边新增一条新记录即可。但是你新增的记录为（ID=400）的时候，你就需要把ID=500和ID=600的数据往后挪，然后把ID=400的记录加进去。
五、MySQL的自增ID用完了怎么办
在 MySQL 中用很多类型的自增 ID，每个自增 ID 都设置了初始值。一般情况下初始值都是从 0 开始，然后按照一定的步长增加。在 MySQL 中只要定义了这个数的字节长度，那么就会有上限。
数据表定义的自增 ID，如果达到上限之后。再申请下一个 ID 的时候，获得到的值将保持不变。我们可以通过下面这个例子来验证一下：
Tips:如果预计到 ID 值可能会被用完，则应该考虑选择 bigint unsigned 类型。
总结：数据库表的自增 ID 达到上限之后，再申请时它的值就不会在改变了，继续插入数据时会导致报主键冲突错误。因此在设计数据表时，尽量根据业务需求来选择合适的字段类型。
六、参考文章
1、https://www.cnblogs.com/yxhblogs/p/12041905.html
2、https://www.cnblogs.com/520playboy/p/10083393.html
以上就是本篇文章【【MySQL】——自增主键ID】的全部内容了，欢迎阅览 ！ 文章地址：http://www.tpjde.com/quote/1141.html 行业 资讯 企业新闻 行情 企业黄页 同类资讯 网站地图 返回首页 推平第移动站 http://mip.tpjde.com/ , 查看更多