mysql insert into select如何提升性能_MySQL自增列锁参数优化——innodb_autoinc_lock

概述

今天主要讲讲innodb_autoinc_lock_mode这个参数。

innodb_autoinc_lock_mode这个参数控制着在向有auto_increment 列的表插入数据时，相关锁的行为，我们可以通过对它的设置可以达到性能与安全(主从的数据一致性)的平衡

官网：https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html

一、insert 语句分类

insert 语句分三种类型：simple insert, bulk insert, mixed insert

1、“INSERT-like” statements(类INSERT语句)

所有可以向表中增加行的语句,包括INSERT, INSERT ... SELECT, REPLACE, REPLACE ... SELECT, and LOAD DATA.包括“simple-inserts”, “bulk-inserts”, and “mixed-mode” inserts.

2、simple insert

Statements for which the number of rows to be inserted can be determined in advance (when the statement is initially processed). This includes single-row and multiple-row INSERT and REPLACE statements that do not have a nested subquery, but not INSERT ... ON DUPLICATE KEY UPDATE.

simple insert 时可以预先知道插入的行记录数量。例如insert into t values(a), (b), 这个语句插入时，mysql就可以预先知道插入的行记录数量为2。其中insert ... on duplicate...语句不属于simple insert

3、bulk insert

Statements for which the number of rows to be inserted (and the number of required auto-increment values) is not known in advance. This includes INSERT ... SELECT, REPLACE ... SELECT, and LOAD DATA statements, but not plain INSERT. InnoDBassigns new values for the AUTO_INCREMENT column one at a time as each row is processed.

事先不知道要插入的行数(和所需自动递增值的数量)的语句。这包括INSERT ... SELECT，REPLACE ... SELECT和LOAD DATA语句，但不包括纯INSERT。 InnoDB在处理每行时一次为AUTO_INCREMENT列分配一个新值。

4、mixed insert

These are “simple insert” statements that specify the auto-increment value for some (but not all) of the new rows. An example follows, where c1 is an AUTO_INCREMENT column of table t1:

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

Another type of “mixed-mode insert” is INSERT ... ON DUPLICATE KEY UPDATE, which in the worst case is in effect an INSERTfollowed by a UPDATE, where the allocated value for the AUTO_INCREMENT column may or may not be used during the update phase.

这些是“Simple inserts”语句但是指定一些(但不是全部)新行的自动递增值。示例如下，其中c1是表t1的AUTO_INCREMENT列：
INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');
另一种类型的“Mixed-mode inserts”是INSERT ... ON DUPLICATE KEY UPDATE，其在最坏的情况下实际上是INSERT语句随后又跟了一个UPDATE，其中AUTO_INCREMENT列的分配值不一定会在 UPDATE 阶段使用

二、innodb_autoinc_lock_mode

As of MySQL 8.0, interleaved lock mode (innodb_autoinc_lock_mode=2) is the default setting. Prior to MySQL 8.0, consecutive lock mode is the default (innodb_autoinc_lock_mode=1).

innodb_autoinc_lock_mode有三个值：

0, traditional
1, consecutive
2, interleaved

1、innodb_autoinc_lock_mode = 0 (“traditional” lock mode)

这种模式下，所有的insert语句在开始时都会获得一个表锁autoinc_lock.该锁会一直持有到insert语句执行结束才会被释放。对于一条insert插入多个行记录的语句，他保证了同一条语句插入的行记录的自增ID是连续的。
这个锁并不是事务级别的锁，而是语句级的锁，一个事务可能包含有一个或多个语句。
在这种模式下，主从复制时，基于语句复制模式下，主和从的同一张表的同一个行记录的自增ID是一样的。但是同样基于语句复制模式下，interleaved模式，也就是innodb_autoinc_lock_mode=2时则不能保证主从同一张表的同一个行记录的自增ID一样。
由于在这种模式下auto_inc锁一直要保持到语句的结束，所以这个就影响到了并发的插入，这种模式下，表的并发性最低。

2、innodb_autoinc_lock_mode = 1 (“consecutive” lock mode)

这一模式对simple insert 做了优化，在这种模式下，insert语句在开始时会获得一个表锁autoinc_lock, simple insert在获取到需要增加的ID的量后，autoinc_lock就会被释放,不必等到语句执行结束。但对于bulk insert，自增锁会被一直持有直到语句执行结束才会被释放。这种模式仍然保证了同一条语句插入的行记录的自增ID是连续的。
这种模式下是在mysql8版本之前的默认值，其主从复制表现跟traditional模式下一样，但是性能会有所提高。

3、innodb_autoinc_lock_mode = 2 (“interleaved” lock mode)

这种模式下，simple insert语句能保证ID是连续的，但是bulk insert的ID则可能有空洞，主从复制的同一张表下的同一行id有可能不一样。

由于这个模式下已经没有了auto_inc锁，所以这个模式下的性能是最好的。

三、总结

如果二进制文件格式是mixed | row，那么这三个值中的任何一个对于数据库都是复制安全的。由于现在mysql已经推荐把二进制的格式设置成row，所以在binlog_format不是statement的情况下最好是innodb_autoinc_lock_mode=2，这样数据库可以获得更好的性能。

觉得有用的朋友多帮忙转发哦！后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~