MySQL自增长属性中的锁

01

MySQL自增长属性中的锁

我们在设计表结构的时候，经常会对某一列设置自增长的值，它的作用是可以帮助我们自动递增某一列的值，自增长的属性经常被设置在主键列上，原因是主键必须具有唯一性，而自动增长可以避免重复，二者结合恰到好处。除此之外，自增长的属性还可以避免在数据插入的时候，出现大量的数据页分裂操作，关于这一点，后面说到索引的时候，会着重介绍，现在我们只需要知道，主键一般设置成自增长的即可。

关于自增长的属性，这里我多唠叨一句，试想一个这个场景，如果一个表的主键现在已经增长到8了，也就是id=8，此时我们删除这条记录，那么再次插入值的时候，这个值会是几？？？有没有其他情况，这个问题就留作试验吧，下次文章中我们会进行试验。

在innodb存储引擎中，针对每个自增长的字段都有一个自增长的计数器，在对还有自增长列的表进行插入操作的时候，这个计数器会被初始化，在mysql中，我们可以执行下面的语句来得到这个计数器的当前值：

select max(自增长列) from table;

当我们进行插入操作的时候，该操作会根据这个自增长的计数器的值+1赋予自增长的列，这个操作我们称之为auto-inc Locking，也就是自增长锁，这种锁其实采用的是特殊的表锁机制，如果insert操作出现在一个事务中，这个锁是在insert操作完成之后立即释放，而不是等待事务提交。

高并发执行和安全性从来都是一对敌人，这种锁机制在带来了方便的同时带来了一些问题，在高并发的场景下，这种锁机制的插入操作虽然不用等待事务提交，但是必须等待前一个插入操作完成，因此，类似insert...select的大量数据插入的SQL会对数据库性能有严重的影响。

MySQL5.1.22版本对这种锁进行了升级，提出了一个参数innodb_autoinc_lock_mode的参数来控制自增长的模式，这个参数默认值是1，总共可以设置三个值0,1,2

mysql--dba_admin@127.0.0.1:yeyztest 22:53:34>>show variables like '%autoinc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 1     |
+--------------------------+-------+
1 row in set (0.00 sec)

设置为0：

则采用普通的auto-inc Locking方式，这种模式已经被淘汰。但是它生成的id值一定是连续的。

设置为1：

对于insert、replace等能够确定插入数据量的操作会使用一个更轻量级的互斥自增长机制去实现，官方称之为mutex，这个可以暂时不用理解，后续会细致的讲解，暂时可以理解为一种更优化的方法即可;而对于load data，insert...select...这种未知数量的插入操作，使用普通的auto-inc Locking方法。这种模式下，给予statement的复制关系能够正常工作。这种方式下，不考虑回滚操作，生成的id是连续的。如果有回滚操作，则不一定连续。

设置为2：

在这种模式下，针对所有的insert类型的语句，包括insert，insert...select...,load data、replace...select...等等操作，都是通过mutex的优化操作去实现的，这种方法的性能最高，但是安全性不能得到保证，生成的id可能是不连续的，而且几乎statement模式的复制可能会出现问题。这种模式下主从复制必须使用基于row的复制模式。

可以看出，随着数字的上升，安全性下降，但是效率提高，所以默认的参数取得是中间值1。实际操作中可以根据自己的真实情况来调整这个值，从而保证服务的性能。

需要注意的是，在myisam中和innodb不同，myisam中使用的是表锁，所以不用考虑并发插入的问题，如果主库使用了innodb，从库使用了mysiam，则需要考虑并发插入的问题。

除此之外，在innodb中，自增长的列必须是索引，而且必须是索引的第一个列，如果不是，则会报错，myisam中没有这个问题。看下面的例子：

自增列必须是主键
mysql:yeyztest>>create table test5 (
-> id int not null auto_increment,
-> age int);
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

联合索引中必须放在第一列
mysql:yeyztest >>create table test5 (
-> id int not null auto_increment,age int,
-> key key_age_id(age,id));
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

mysql:yeyztest >>create table test5 (
-> id int not null auto_increment,age int,
-> key key_id_age(id,age));
Query OK, 0 rows affected (0.02 sec)

关于自增列，先说这么多，总结一下：

1、自增列使用了auto-inc Locking锁，插入的时候仅仅需要等待上一条记录插入完成，不必等待上一个事务提交。

2、innodb中，自增列必须作为索引，如果是联合索引，必须放在第一列。

3、innodb_autoinc_lock_mode参数可以控制自增列的锁模式，数字越大，效率越高，安全性越差。