怎么理解mysql innodb_autoinc_lock_mode的与数据库行为

这篇文章主要介绍“怎么理解mysql innodb_autoinc_lock_mode的与数据库行为”，在日常操作中，相信很多人在怎么理解mysql innodb_autoinc_lock_mode的与数据库行为问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”怎么理解mysql innodb_autoinc_lock_mode的与数据库行为”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

mysql 的auto_incremet 都知道了 

对应的还有一个这个行为的一个锁的类型的参数,对数据库的行为会有影响. 

innodb_autoinc_lock_mode  有3个取值 0 , 1 ,2 对应于 
“traditional”,“consecutive”, or “interleaved” lock mode

针对有3中行为: 

1. insert_like  语句 包括: insert ,  insert ... select,  replace ,replace ... select  已经 load data 语句. 

2.simple insert  语句:  insert , replace 这样. 

3.bulk insert  语句:  insert ...select , replace...select , load data.

4.mixed insert 语句: 例如 INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d'); 
           还有   INSERT ... ON DUPLICATE KEY UPDATE 这种语句. 

innodb_autoinc_lock_mode  取值为 0 : 

这个参数是从5.1 引入的, 对应于传统的模式.  对于insert like 语句这个auto_incremet  取得的是一个表级锁. 

持有锁的时间是到当前语句结束. 确保语句的写入是可以预见以及可以预测,可以重现的,也就是可以保证语句入库的数据时顺序的. 

对于使用statement format 的 binlog 模式的复制,slave 跟主库同一行记录取得的值是一致. 

例如 : 
CREATE TABLE t1 (  c1 INT(11) NOT NULL AUTO_INCREMENT,  c2 VARCHAR(10) DEFAULT NULL,  PRIMARY KEY (c1)) ENGINE=InnoDB;

执行语句:
Tx1: INSERT INTO t1 (c2) SELECT 1000 rows from another table ...

Tx2: INSERT INTO t1 (c2) VALUES ('xxx');

 在statement 的复制中, 日志重演这个sql执行,是可以确保主从取得的值是一致的. 

tx1 执行的时候, 是可以保证 这1000笔记录是连续的取得 autoinc 字段的值, tx2 要等到tx1 执行完后才能拿到autoinc的值. 

这样限制了并发. 

innodb_autoinc_lock_mode 取值 1 : 

取值为1 为默认值. 

对于 bulk insert  语句.  每个语句只有这个锁到语句结束,但是每个语句持有这个锁只能执行一次. 

对于simple inset  语句是可预计的插入的值的数量,那么语句是可以不需要持有这个表级锁到语句结束. 而是更高级别的mutex 锁.

对于不可以预见插入数量的语句, 起行为跟bulk insert 是一样的,持有锁到语句结束,每次只能执行一次. 

对于mixed inset  数据库会分派多于插入插入数量的 autoinc 值 , 语句结束后,多余的值将丢弃, 这个时候, autoinc 字段的值可能不是连续的. 

innodb_autoinc_lock_mode  取值 2 : 

insert like 语句 可以持有这个表级锁,并执行相同的时间. 

这样对于statement based  复制来说, 在恢复的时候,结果就变得不确定了. 也就是可能主从库的autoinc 字段的值是不一致的. 

这样会对业务逻辑代理困扰. 

这种模式下,每个语句都可以几乎在同时产生autoinc值,这样每个语句的所取得的autoinc字段的值是不确定的.也不可预测. 

对于simple insert 如果在执行前 可以预测到要插入的值的数量,这个sql的数据获取的autoinc值是连续的.  否则就不一定是连续的. 

对于mixed insert 语句 取得autoinc字段的值是不可预测的. 

对于bulk insert 语句 取得autoinc 的值可能存在空缺 (gap)  即取得的值可能不是连续的. 

innodb_autoinc_lock_mode 的取值与复制的关系.

取值 0 跟 1  对复制来说都是安全的, 即可以保证主从数据库的值是一致的. 

如果却只为2  在 基于 statement based 复制来说,从库的数据时不确定的. 即不安全. 

对应 row based 或者 mixed based 的复制  从库的数据时确定跟主库一致的,是安全. 

可以看出在 取值2 的时候,可以获得更大的性能. 

另外如果指定了 autoinc字段的值 insert 可能会触发 key 冲突的 错误. 

如果insert 指定的值大于autoinc 字段的最大值, 其结果是不确定的. 

innodb_autoinc_lock_mode 取值为2的时候对于bulk insert 会产生空洞(gap)   同一个sql 写入数据库的值不一定是连续的. 

到此，关于“怎么理解mysql innodb_autoinc_lock_mode的与数据库行为”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！