设为首页 加入收藏

TOP

MySQL字段自增长AUTO_INCREMENT用法实例详解
2018-12-27 14:14:09 】 浏览:57
Tags:MySQL 段自 增长 AUTO_INCREMENT 用法 实例 详解

之前有碰到过开发同事指出一张InnoDB表的自增列 AUTO_INCREMENT 值莫明的变大,由于这张表是通过mysqldump导出导入的。


问题排查:


为了验证这个怀疑的准确性,同时学习下InnoDB处理 AUTO_INCREMENT 的机制,因此在测试环境做了测试总结。


本文使用的MySQL版本为官方社区版 5.7.24


测试环境测试表参考官方文档:https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html


InnoDB自增锁的模式由参数 innodb_autoinc_lock_mode 在启动时指定,这是一个只读参数,并不能在实例运行中动态修改参数值。参数值选项如下:


InnoDB表insert语句主要可以分为三种类型:


其中c1为自增列。


还有一种混合模式插入语句 INSERT ... ON DUPLICATE KEY UPDATE ,这类语句InnoDB为自增列预分配的值有可能被使用,也有可能不被使用。


在这个模式下,所有的 INSERT 语句在插入有自增属性的列时都要获取一个特殊的 AUTO-INC 表级锁。该锁的持有时间到语句结束(而不是到事务结束,一个事务中可能包含多条语句),它能够确保为有自增属性列在 INSERT 一行或者多行数据时分配连续递增的值。


这是默认的锁模式。在这个模式下,大量插入每条语句执行时都将获得特殊的表级 AUTO-INC 锁,语句执行完成后释放。每次只有一条语句可以执行并持有 AUTO-INC 锁。


在这种锁模式下,没有插入语句使用 AUTO-INC 表级锁,并且多条语句可以并发执行。这是最快并且扩展性最好的锁模式,但是如果binlog使用基于语句级复制的在从库重放SQL语句时是不安全的。


无论 AUTO_INCREMENT 处于哪种锁模式下,即 innodb_autoinc_lock_mode 的所有取值情况下,在一个事务当中自增列分配的值是不能被回滚的。这会导致事务回滚了,但是自增列分配的值却消失了,自增列分配的值是无法随着事务回滚而重复利用,这样就自增列上的值就产生了间隙。


测试:


可以看出自增列分配的值是不会再出现 c1=1 的。


无论 AUTO_INCREMENT 处于

哪种锁模式下,如果在 INSERT 语句为自增列指定 NULL 或者 0 时,InnoDB认为并没有为自增列指定值,同时也会为其分配值。


测试:


无论 AUTO_INCREMENT 处于哪种锁模式下,自增列分配值机制不会生效,即为自增列指定负值是不影响 AUTO_INCREMENT 值的。


测试:


无论 AUTO_INCREMENT 处于哪种锁模式下,自增列分配的值如果大于自增列所属字段类型的最大值,则自增列分配值机制就不会生效。


测试:
在MySQL当中,INT类型的最大值为 -2147483648~2147483647


可以看出自增列 AUTO_INCREMENT 并不会分配。


测试表:


可以看出下一个自增列值为 103,因为自增列的值是在每条插入语句执行时分配的,而不是一开始就分配完的。


在此模式下:


可以看出下一个自增列值为 1048665 ,因为自增列值个数在语句执行开始就已经分配了4个(1048661~1048664),但实际语句只使用了2个。


在此模式下:


可以看出下一个自增列值为 1048665 ,TRX1执行时因为大量插入时无法预估插入值数量,TRX2执行时分配了4个自增值,但只使用了2个(262241~262242),造成了2个间隙,TRX1和TRX2的自增列值是交叉分配的。


无论 AUTO_INCREMENT 处于哪种锁模式下,更新自增列的值都有可能会产生 Duplicate entry 重复值错误。


可以看出更新了自增列的值之后产生了 Duplicate entry 重复值错误。建议不要更新自增列的值,或是把自增列值更新为大于 AUTO_INCREMENT 的值。


当为一张InnoDB表指定自增列时,此时表在数据字典中维护着一个特殊的计数器为自增列分配值,名称为 auto_increment 计数器,这个计数器是存储中内存中,而不是在磁盘上。


当服务器重启之后后,为了初始化 auto_increment 计数器,InnoDB执行如下等效语句来确定自增列下个需要分配的值:


默认的,自增列的下一个分配值由以上语句得出的值增加 1 ,这个增加步长由参数 auto_increment_increment 决定。如果是空表,则自增列的下一个分配值为 1 ,这个空表时默认值由参数 auto_increment_offset 决定。


https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html



编程开发网
】【打印繁体】【投稿】【收藏】 【推荐】【举报】【评论】 【关闭】 【返回顶部
上一篇Linux系统下 MySQL 5.7和8.0 版本.. 下一篇使用Navicat将SQL Server数据迁移..

评论

帐  号: 密码: (新用户注册)
验 证 码:
表  情:
内  容:

array(4) { ["type"]=> int(8) ["message"]=> string(24) "Undefined variable: jobs" ["file"]=> string(32) "/mnt/wp/cppentry/do/bencandy.php" ["line"]=> int(217) }