自增主键
AUTO_INCREMENT primary key not null;
是在不指定主键id时, 数据库根据当前表中的id信息, 自动分配自增id
数据中 id不连续 可能的情况?
- 某行数据删掉了
-
插入数据时指定了id
只有当id传入0或者null时, 使用自增值
- 事务执行了插入(使得主键id增加) 但进行了回滚, 自增id记录值不会回滚
- ON DUPLICATE KEY UPDATE 可以直接插入数据，遇到唯一性冲突时自动做更新, 但会出现id跳跃 (插入时id进行自增, 唯一键冲突进行更新时, id也会自增)
- insert into table select 语句, 批量申请id
第一次申请id分配一个, 该sql继续申请则获得2个, 再申请获得4个, 以此类推… 但它可能没有用完…
innodb_autoinc_lock_mode 这个参数是记录申请id获取锁后释放锁的策略
- 设置为0，则申请id的语句结束后才释放锁；
- 设置为1，则对单条insert语句，申请到id后马上释放，对批量插入语句像上面的例子，则是语句执行结束后释放；
- 设置成2，则所有语句都是申请到id后马上释放，效率最高。
为啥8.x版本修改成策略2?
为了保证并发安全 和 效率 两方面
- 策略1 如果出现一个
insert ... select语句, 则在这个语句完成之前, 其他插入语句只能等待 自增锁的释放 (是否只有插入语句受影响? not sure), 性能很差 - 8.x 改成 策略2后, 在批量申请了自增id后即刻释放(即使可能申请的自增id不够), 意味着在
insert ... select的过程中, 可能有其他事务的插入语句偷偷混在里面的 (即批量插入的id不一定是连续的)使用策略2 需要额外将 bin log的记录方式调整为
row(8.x 默认是row) 如果bin log 使用statement会出现主从自增id不一致的情况 主库insert ... select过程中会有其他insert并行执行, 而从库通过bin log 执行时是顺序执行, 即执行完insert ... select才执行其他并发的insert(也可能先执行insert, 后执行insert ... select, 具体看bin log记录的顺序了.)
另外 还看到一点 自增id的值, 在8.x版本是将该值持久化了, mysql server重启后值不会变化 在5.7及之前的版本, 自增id是存在内存中的, server重启后是通过max(id) 获取当前表的最大id
eg: 如果一张表中有10条数据, id最大值是10, 删掉id=10的数据, 然后重启server, 再向表里写入一条数据, 且使用id自增 8.x 版本: 新写入的这条数据 id=11 5.7及之前: 新写入的这条数据 id=10
如何把不连续的数据改成连续?
方案1. 备份数据, 然后truncate表, 再把备份写会原表 方案2. 执行SQL
ALTER TABLE `zh_user` DROP `id`;
ALTER TABLE `zh_user` ADD `id` MEDIUMINT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT FIRST;
如果允许低峰期短时间停服，可以考虑在交易最小的时间段评估执行时间来完成
如果不允许停服，可以建一张备份表，把数据备份过去，让应用同时写2张表，数据一致后停止写原表，这种方法的问题是需要改代码测试上线
MySQL 5.7 之前, 自增id是存在内存中的, 每次重启后会重新执行max(id) 得到当前最大id, 将id+1 作为该表的自增值;
MySQL 5.6 中, 如果表中有 id = 10 和 id=11的两条记录, 删掉id=11, 重启服务, 向表中插入数据, 则插入的数据行id为11
InnoDB引擎： 在 MySQL 5.7 及之前的版本，自增值保存在内存里，并没有持久化。每次重启后，第一次打开表的时候，都会去找自增值的最大值 max(id)，然后将 max(id)+1 作为这个表当前的自增值。
在 MySQL 8.0 版本，将自增值的变更记录在了 redo log 中，重启的时候依靠 redo log 恢复重启之前的值。