MySQL之auto-increment相关处理

说明

本文主要用于阅读后记录，参考翻译MySQL 5.7说明文档章节，后续讨论中所使用的的数据库存储引擎为InnoDB。

目录

1.概述

2.术语

3.锁模式

4.AUTO_INCREMENT的使用说明

5.AUTO_INCREMENT 计数器初始化

6.参数查询与设置

---

1.概述

在数据库表的列中存在AUTO_INCREMENT的列时，配合锁机制可以极大地改进数据库的并发性和SQL的执行性能。

2.术语

“INSERT-like”语句：任何在数据库表中产生了一行新数据的SQL语句都称为 “类插入” 语句。例如：INSERT, INSERT ... SELECT, REPLACE,REPLACE ... SELECT, and LOAD DATA等。它们可以分为以下三大类：

a、Simple inserts：简单插入语句，它描述的是数据进行插入之前就可以判断出会有多少条记录插入到表中的情况。

b、Bulk inserts：批量插入语句，它描述的是数据进行插入之前不可以判断出会有多少条记录插入到表中的情况。这些批量插入的数据应该是都可以插进入表中的（自己添加）。

c、Mixed-mode inserts：混合模式插入语句，它描述的有两种情况：

    情况1：在“Simple inserts”情况下时，如果待插入的行中存在部分行没有指定该auto-increment的值，或者为空，例如：INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

    情况2：在类似语句INSERT ... ON DUPLICATE KEY UPDATE下情况，在这种情况下，该auto-increment的值可能发生变化，也可能不会变化。

3.锁模式

锁模式依赖于innodb_autoinc_lock_mode变量的配置值，它的值可以为0（traditional）、1（consecutive）、2（interleaved）。

• innodb_autoinc_lock_mode = 0：traditional lock mode

本模式下，所有“INSERT-like”语句操作带有AUTO_INCREMENT的列时，它们都将去获取一个特殊的表级锁“AUTO-INC lock”。它们一直持有这把锁，直到语句结束。这种方式确保了auto-increment值的可预见性和连续性。在statement-based的主从同步时，如果slave server配置的起始值与master一样，那么它同步之后写入表中的数据也将于master一致。

缺点：本模式会影响数据的并发性

• innodb_autoinc_lock_mode = 1：consecutive lock mode

本模式属于默认模式。在这种模式下，“Bulk inserts”会使用特殊的“AUTO-INC lock”来保证批量插入数据时，它们的auto-increment值的连续性。对于“Simple Inserts”，本模式通过一个轻量级的锁一次获取这些简单插入行的auto-increment值，然后将这些值连同数据一起插入到数据表中，这样免去了对表进行加锁而导致的并发性的问题。

不过，在本模式下也可能存在一个问题：那就是在“mixed-mode inserts”时，由于用户显式地指定了部分auto-increment的值，然而这里又是一次性获取到了那么多的auto-increment值，这样将导致部分的auto-increment值不被使用，从而在auto-increment值上出现“间隙”。

• innodb_autoinc_lock_mode = 1：interleaved lock mode

本模式下所有的语句都不会使用锁，并且多个statement可以同时进行。这时并发性最好的模式，但是缺点是auto-increment值可能会出现交叉情况，在主从同步或者备份时安全性不高。

4.AUTO_INCREMENT的使用说明

• 在进行replication时，如果bin log为statement-based模式时，建议配置innodb_autoinc_lock_mode为0/1.同时在master和slave上Auto-increment使用同样的初始值。bin log为其他模式，innodb_autoinc_lock_mode可以随意。
• 事物在回滚后，分配的那些auto-increment值都不会存在，后续新插入时，这些不存在的值不可以被重复使用。
• auto-increment值指定为NULL或者0时，MySQL会自动产生一个新值给该行数据。
• 为auto-increment赋值为负值或者超过定义的范围时，其含义是未定义。
• innodb_autoinc_lock_mode等于1/2情况下auto-increment值可能会出现“间隙”。

5.AUTO_INCREMENT 计数器初始化

在InnoDB的表中，如果定义了一个auto-increment列，那么InnoDB的数据字典中将会包含一个特殊的定时器，它用来为该列进行赋值。这个定时器存储在内存中，而不是在磁盘上。

mySQL Server在启动后，InnoDB为了启动该定时器，它首先会在表中查找auto-increment列的最大值，然后获取到该值并递增后赋值给该定时器。默认增量为1，可以通过auto_increment_increment进行修改。

如果表的内容为空，那么InnoDB会使用1作为其初始值。它也可以通过auto_increment_offset进行修改。

在初始化计数器完成之后，如果用户插入数据时没有指定该auto-increment的值，那么计数器将自动为该列赋值；如果用户显式地指定了一个值，并且该值大于当前计数器的值，则计数器将更新到该值。

6.参数查询与设置

select @@innodb_autoinc_lock_mode；

set global innodb_autoinc_lock_mode = xx；