MySQL自增锁引起的插入性能问题

1 问题背景

学习《深入理解 MySQL ——锁、事务与并发控制》（https://mp.weixin.qq.com/s/JFSDqI5yaOc-Znr6Q1ohVA）的过程中了解到，自增锁（autoinc_lock）会影响事务插入的性能问题：

AUTO-INC 锁是一种特殊的表级锁，产生于这样的场景：事务插入(inserting into )到具有 AUTO_INCREMENT 列的表中。

在最简单的情况下，如果一个事务正在向表中插入值，那么其他任何事务必须等待向该表中插入它们自己的值，以便由第一个事务插入的行接收连续的主键值。

—— 那么已知我们工程大量使用事务插入记录、又有自增列，会锁表，问怎么提高插入效率？

2 自增锁的插入

（参考：http://keithlan.github.io/2017/03/03/auto_increment_lock/）

2.1 和auto_increment相关的insert种类

（1）INSERT-like

解释：任何会产生新记录的语句，都叫上INSERT-like，比如：

INSERT, INSERT ... SELECT, REPLACE, REPLACE ... SELECT, and LOAD DATA

总之包括：“simple-inserts”, “bulk-inserts”, and “mixed-mode” inserts.

 

（2）Simple insert

插入的记录行数是确定的：比如：insert into values，replace

但是不包括：

INSERT ... ON DUPLICATE KEY UPDATE.

 

（3）Bulk inserts（批量插入）

插入的记录行数不能马上确定的，比如： INSERT ... SELECT, REPLACE ... SELECT, and LOAD DATA

 

（4）Mixed-mode inserts

这些都是simple-insert，但是部分auto increment值给定或者不给定

以上都是Mixed-mode inserts

1. INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

2. INSERT ... ON DUPLICATE KEY UPDATE

2.2 锁模式

（1）innodb_autoinc_lock_mode = 0 (“traditional” lock mode)

优点：极其安全

缺点：对于这种模式，写入性能最差，因为任何一种insert-like语句，都会产生一个table-level AUTO-INC lock

 

（2）innodb_autoinc_lock_mode = 1 (“consecutive” lock mode)（连续的）

原理：这是默认锁模式，当发生bulk inserts的时候，会产生一个特殊的AUTO-INC table-level lock直到语句结束，注意：（这里是语句结束就释放锁，并不是事务结束哦，因为一个事务可能包含很多语句）

对于Simple inserts，则使用的是一种轻量级锁，只要获取了相应的auto increment就释放锁，并不会等到语句结束。

——我们工程里一般都“insert into values”，属于simple inserts，所以讲道理性能瓶颈在数据库自增主键的自增速度。

 

PS：当发生AUTO-INC table-level lock的时候，这种轻量级的锁也不会加锁成功，会等待。。。。

优点：非常安全，性能与innodb_autoinc_lock_mode = 0相比要好很多。

缺点：还是会产生表级别的自增锁

深入思考： 为什么这个模式要产生表级别的锁呢？

因为：他要保证bulk insert自增id的连续性，防止在bulk insert的时候，被其他的insert语句抢走auto increment值。

 

（3）innodb_autoinc_lock_mode = 2 (“interleaved” lock mode)（交叉的）

原理：当进行bulk insert的时候，不会产生table级别的自增锁，因为它是允许其他insert插入的。

来一个记录，插入分配一个auto 值，不会预分配。

优点：性能非常好，提高并发，SBR不安全

缺点：

一条bulk insert，得到的自增id可能不连续

SBR模式下：会导致复制出错，不一致

 

3 如何解决自增锁引起的插入性能问题

（1）自已写一个分布式自增id的发号器，然后把主键上的 AUTO_INCREMENT 去掉；

（2）避免 insert … select … ，这样会导致Bulk inserts，产生表锁；

（3）如果binlog-format是row模式，而且不关心一条bulk-insert的auto值连续（一般不用关心），那么设置innodb_autoinc_lock_mode = 2 可以提高更好的写入性能

 

 

4 其他

（1）查看锁的命令：show engine innodb status

（2）设置锁等待超时时间（防止死锁引起系统崩溃）：innodb_lock_wait_timeout