MySQL自增长与锁的进一步认识

自增长在数据库中是一种非常常见的一种属性，也是很多DBA或开发人员或者DBA人员首选的主键方式。在InnoDB存储引擎的内存结构中，对每个含有自增长值的表都有一个自增长计数器(auto_increment counter)。当对含有自增长的计数器的表进行插入操作时，这个计数器会被初始化，执行如下的语句来得到计数器的值：

select max(auto_inc_col) from test for update;

插入操作会根据这个自增长的计数器值加1赋予自增长列。这个实现方式称作为AUTO-INC Locking。这种锁采用一种特殊的表锁机制，为了提高插入的性能，锁不是在一个事务完成后才释放，而是在完成对自增长值插入的SQL语句后立即释放。

虽然AUTO-INC Locking从一定程度上提高了并发插入的效率，但还是存在一些性能问题。首先，对于有自增长值的列的并发插入性能较差，事务必须等待前一个插入的完成。其次，对于INSERT—-SELECT的大数据量的插入会影响插入的性能，因为另一个事务中的插入会被阻塞。

从MySQL5.1.22版本开始，InnoDB存储引擎引擎中提供了一种轻量级互斥量的自增长实现机制，这种机制大大提高了自增长值插入的性能。并且从该版本开始，InnoDB存储引擎提供了一个参数innodb_autoinc_lock_mode来控制自增长的模式，该参数的默认值为1.

mysql> select @@version;+------------+| @@version  |+------------+| 5.7.17-log |+------------+1 row in set (0.00 sec)mysql> show variables like "%autoinc_lock_mode%";+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| innodb_autoinc_lock_mode | 1     |+--------------------------+-------+1 row in set (0.00 sec)mysql> 

在继续讨论新的自增长方式实现方式之前，需要对自增长的插入进行分类，如下：

• insert-like：指所有的插入语句，如insert，replace，insert—select,replace—select,load data等
• simple insert：指能在插入之前就确定插入行数的语句。这些语句包含insert、replace等，需要注意的是：simple inserts不包含insert—on duplicater key update这类SQL语句
• bulk inserts：指在插入之前不能确定得到插入行数的语句，如insert—select，replace–select，load data
• mixed-mode inserts：指插入中有一部分的值是自增长的，有一部分是确定的

其中参数innodb_autoinc_lock_mode以及各个设置下对自增的影响，其总共有三个可供设定，即0、1、2，具体说明如下：
- 0：这是MySQL5.1.22版本之前自增长的实现方式，即通过表锁的AUTO-INC Locking方式，因为有了新的自增长实现方式，0这个选项不应该是新版用户的首选项
- 1：这是该参数的默认值。对于simple inserts，该值会用互斥量去对内存中的计数器进行累加的操作，对于bulk inserts，还是使用传统表锁的AUTO-INC Locking方式。在这种配置下，如果不考虑回滚操作，对于自增长列的增长还是连续的，并且在这种方式下，statement-based方式的replication还是能很好地工作。需要注意的是，如果已经使用AUTO-INC Locking方式去产生自增长的值，而这时需要进行simple inserts的操作时，还是需要等待AUTO-INC Locking的释放
- 2：在这个模式下，对于所有的insert-like自增长的产生都是通过互斥量，而不是通过AUTO-INC Locking的方式，显然这时性能最高的方式。然而会带来一定的问题。因为并发插入的存在，在每次插入时，自增长的值可能不是连续的。最重要的是，基于Statment-base replication会出现问题。因此，使用这个模式，任何时候都应该使用row-base replication，这样才能保证最大的并发性能及replication主从数据的一致。

此外，InnoDB存储引擎中自增长的实现和MyISAM不同，MyISAM存储引擎是表锁设计，自增长不用考虑并发插入的问题，因此在master上用InnoDB存储引擎，在slave上的MyISAM存储引擎的replication架构下，用户必须考虑这种情况。

另外，在InnoDB存储引擎引擎中，自增长的列必须是索引，同时必须是索引的第一个列。如果不是第一个列，则MySQL数据库会抛出异常，而MyISAM存储引擎没有这个问题。

本文摘自《MySQL技术内幕-innodb存储引擎》章节。