【MySQL】——自增主键ID

矫情吗；* 2022-11-27 12:20 221阅读 0赞

### 一、前言 ###

我们常用mysql,在设计表结构的时候必须考虑主键的事情。mysql数据库中有一个主键生成规则，就是自增。本文就给大家从几个维度说明一下mysql自增主键。

### 二、主键设置原则 ###

1.  **主键不可修改**

对于数据库来说，主键其实是可以修改的，只要不和其他主键冲突就可以。但是，对于应用来说，如果一条记录要修改主键，那就会出大问题。因为主键的第二个作用是让其他表的外键引用自己，从而实现关系结构。一旦某个表的主键发生了变化，就会导致所有引用了该表的数据必须全部修改外键。很多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》中的原话

![format_png][]

### 四、MySQL innodb存储引擎建议使用自增主键剖析 ###

问：为什么建表的时候建议创建自增ID，直接用唯一业务的标识ID不香吗？

答：是的，不香。

InnerDB使用的B+树模型，B+树的结构决定了 所有左节点< 节点< 右节点，这样带来的好处我就不详细说了，坏处就是当新增主键破坏了现有树形结构的时候，就需要将大于这个值得主键向后挪用。当需要挪动的数据页已经处于存满的状态，就会更加的麻烦。但是当我们使用主键递增ID的时候，所有新增的主键都为当前主键的最大值，所以只需要在索引树的最右边加上一个记录即可。这也就是为什么要采用自增ID的原因。举一个的例子：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDQ0OTMwMA_size_16_color_FFFFFF_t_70][]

当你想加一条数据（ID=700）的时候，只需要在700节点的右边新增一条新记录即可。但是你新增的记录为（ID=400）的时候，你就需要把ID=500和ID=600的数据往后挪，然后把ID=400的记录加进去。

### 五、MySQL的自增ID用完了怎么办 ###

在 MySQL 中用很多类型的自增 ID，每个自增 ID 都设置了初始值。一般情况下初始值都是从 0 开始，然后按照一定的步长增加。在 MySQL 中只要定义了这个数的字节长度，那么就会有上限。

数据表定义的自增 ID，如果达到上限之后。再申请下一个 ID 的时候，获得到的值将保持不变。我们可以通过下面这个例子来验证一下：

create table `test` (
       `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
       PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=4294967295;
     
    insert into `test` values (null);

**Tips:*如果预计到 ID 值可能会被用完，则应该考虑选择 bigint unsigned 类型。***

**总结：**数据库表的自增 ID 达到上限之后，再申请时它的值就不会在改变了，继续插入数据时会导致报主键冲突错误。因此在设计数据表时，尽量根据业务需求来选择合适的字段类型。

六、参考文章

1、[https://www.cnblogs.com/yxhblogs/p/12041905.html][https_www.cnblogs.com_yxhblogs_p_12041905.html]

2、[https://www.cnblogs.com/520playboy/p/10083393.html][https_www.cnblogs.com_520playboy_p_10083393.html]

3、[https://www.pianshen.com/article/3127956689/][https_www.pianshen.com_article_3127956689]

4、[https://www.kancloud.cn/db-design/mysql-dba/596722][https_www.kancloud.cn_db-design_mysql-dba_596722]

[format_png]: /images/20221124/9d85e33242054b99b7cee1bccb0f3fdd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDQ0OTMwMA_size_16_color_FFFFFF_t_70]: /images/20221124/05375069f1f54d38bbd4e31e341e9aa4.png
[https_www.cnblogs.com_yxhblogs_p_12041905.html]: https://www.cnblogs.com/yxhblogs/p/12041905.html
[https_www.cnblogs.com_520playboy_p_10083393.html]: https://www.cnblogs.com/520playboy/p/10083393.html
[https_www.pianshen.com_article_3127956689]: https://www.pianshen.com/article/3127956689/
[https_www.kancloud.cn_db-design_mysql-dba_596722]: https://www.kancloud.cn/db-design/mysql-dba/596722