MySQL 自增主键

介绍MySQL自增主键。

auto increment

mysql的自增步长可以通过下面的命令查询,

1
2
3
4
5
6
7
mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+

其中,auto_increment_increment是自增的步长,value为1代表每次+1,auto_increment_offset是自增的偏移量,也就是自增开始,value为1代表从1开始增加。

InnoDB自增主键是通过本身的自增计数器获取,该方式会通过表锁机制完成。
表锁只有在插入结束后才释放,也就是事务完成后。

为了解决自增主键锁表的问题,引入了innodb_autoinc_lock_mode,通过轻量级互斥量的增长机制来完成。

1
2
3
4
5
6
mysql> show variables like 'innodb_autoinc_lock_mode';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 1 |
+--------------------------+-------+

innodb_autoinc_lock_mode的取值有三种:

  • 0,表锁
  • 1,默认值,互斥量,会“预申请”多余的值,可能会出现不连续的情况
  • 2,自增值不连续,性能好

参数innodb_autoinc_lock_mode = 1时,每次会预申请多余的id(handler.cc:compute_next_insert_id),而insert执行完成后,会特别将这些预留的id空出,就是特意将预申请后的当前最大id回写到表中

最近就发现一次一个数据表由于频繁的insert on duplicate key update导致了表的不连续,具体原因是该数据会预分配id但如果插入失败执行更新操作,那么该id就被废弃了,而下一条插入操作会跳过该值。

应用

在mysql主主同步时(两台机器互相同步数据),需要设置

1
2
auto_increment_increment = 2
auto_increment_offset = 1 和 2

这样才能避免两台服务器同时做更新时自增字段的值之间的冲突。

其他

http://www.cnblogs.com/zhoujinyi/p/3433823.html
http://blog.csdn.net/yanzongshuai/article/details/46476151