一、前言
我们常用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 的时候,获得到的值将保持不变。我们可以通过下面这个例子来验证一下:
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
2、https://www.cnblogs.com/520playboy/p/10083393.html