怎么理解mysql innodb

共计 2323 个字符，预计需要花费 6 分钟才能阅读完成。

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

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 的与数据库行为”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注丸趣 TV 网站，丸趣 TV 小编会继续努力为大家带来更多实用的文章！