mysql自增主键怎么用

最新推荐文章于 2023-12-21 15:22:54 发布

Freeguy88

最新推荐文章于 2023-12-21 15:22:54 发布

阅读量657

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/Jason_We/article/details/113559588

版权

2 篇文章 0 订阅

订阅专栏

通常，自增主键的设置如下：

`id` int unsigned NOT NULL AUTO_INCREMENT,

如果设置成这样，则id的取值范围是[0, 2^32 -1],即[0,4294967295].若表的数据量庞大，自增主键超过4294967295时，会报主键冲突异常，导致数据无法插入。
解决办法是，将int 改为bigint。

`id` bigint unsigned NOT NULL AUTO_INCREMENT,

由于bigint为8B，他的unsigned 表示范围大大增加，为[0,2^64 -1],即[0,18446744073709551615] ,这下基本上就不用愁主键被用光了。

MyISAM 引擎的自增值保存在数据文件中
InnoDB 引擎的自增值：
- 5.7及之前的版本，保存在内存中，没有持久化，每次重启后，第一次打开表的时候，都会去找自增值的最大值 max(id)，然后将 max(id)+1 作为这个表当前的自增值。这样存在的问题是，重启可能会导致自增主键改变。
- 8.0+版本实现了自增主键的持久化，它将自增值的变更记录在了 redo log 中，重启的时候依靠 redo log 恢复重启之前的值。

如果字段id被定义为AUTO_INCREMENT，在插入一行数据的时候，自增值的行为如下：

1.如果插入数据时id字段指定为0、null或未指定值，那么就把这个表当前的AUTO_INCREMENT值填到自增字段

2.如果插入数据时id字段指定了具体的值，就直接使用语句里指定的值

假设，某次要插入的值是X，当前的自增值是Y

1.如果X<Y，那么这个表的自增值不变

2.如果X>=Y，就需要把当前自增值修改为新的自增值

新的自增值生成算法是：
从auto_increment_offset（初始值）开始，以auto_increment_increment（步长）为步长，持续叠加，直到找到第一个大于X的值，作为新的自增值

发生事务回滚时。因为每次取自增值都会变化，事务回滚时并不会撤回已经被取走的自增值，因此当发生事务回滚时会发生自增列不连续的情况。
发生唯一键冲突时。发生唯一键冲突之前，首先会获取当前自增值填入自增列，然后由于发生唯一键冲突，导致插入失败，但此时自增值已经发生了变化，会导致不连续。
insert…select语句执行时。
insert…select语句执行时如果插入多行，会批量申请自增值，第一次申请1个，第二次申请2个，第三次申请4个，第N次申请2^(N-1)个。所以如果第N次申请了多个值，但是没有用掉的话，也会造成自增列不连续。

MySQL自增主键的设计可以让主键索引尽量地保持递增顺序插入，避免了页分裂，因此索引更紧凑。这样在执行效率上会相对高于用户自定义的主键。

①：由于自增主键的天然属性，别人一旦爬取你的数据库,就可以根据数据库的自增id获取到你的业务增长信息，很容易估计你的经营情况

②：对于高并发的负载，innodb在按主键进行插入的时候会造成明显的锁争用，主键的上界会成为争抢的热点，因为所有的插入都发生在这里，并发插入会导致间隙锁竞争

③：Auto_Increment锁机制会造成自增锁的抢夺,有一定的性能损失
Auto_increment的锁争抢问题，如果要改善需要调优innodb_autoinc_lock_mode的配置

由于这两者ID的随机性，导致MySQL无法实现像自增主键那样的索引定位及内存分配，因此在性能上及内存利用率上都要略逊一筹。从业务角度考虑，如果表的数据量不算大且对性能没有太大的要求的情况下，选择这两者也是可以的。对比文章参考：深入分析mysql为什么不推荐使用uuid或者雪花id作为主键

关注