MySQL -- Innodb是如何处理自增列的
2017-09-26 15:45 abce 阅读(1453) 评论(0) 编辑 收藏 举报insert into t1(c1,c2) values(1,'a'),(null,'b'),(5,'c'),(null,'d')
另一种"mixed-mode inserts"是insert...on duplicate key update,最差的场景就是一条insert后跟着一条update,产生的自增列的值可能都没有用到。
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 ... #事务1插入1000行 tx2: insert into t1 (c2) values ('xxx');
innodb无法提前知道tx1会插入多少条数据,只好每插入一条数据就产生一个自增值。对于表级锁,每次执行执行一条sql语句,所以对于不同的sql语句,自增值的产生不是交错的。
·1表示"consecutive"锁模式
这是默认的锁定模式。在这种模式下,“bulk inserts”使用特殊的AUTO-INC表级锁并将其保持到语句结束。这适用于所有INSERT ... SELECT,REPLACE ... SELECT和LOAD DATA语句。一次只能执行一条持有AUTO-INC锁的语句。如果批量插入操作的源表与目标表不同,则在对源表中选择的第一行加共享锁之后,将对目标表执行AUTO-INC锁。如果批量插入操作的源和目标在同一表中,则在对所有选定行施加共享锁之后,获取AUTO-INC锁。
"Simple inserts"可以提前知道需要哪些自增值,在mutex的帮助下,不需要对表加上标级auto-inc锁。mutex只是在分配自增值得时候存在,不需要等整个sql语句结束才释放。如果有另外的事务持有auto-inc锁得时候,就需要获取表级auto-inc锁了。
此锁模式可确保在存在INSERT语句的情况下(事先不知道行数)(并且随着语句的进行自动分配自增值),所有由“insert-like”语句分配的自动递增值语句是连续的,并且操作对于基于语句的复制是安全的。
简而言之,此锁模式可提高可伸缩性,同时可安全地用于基于语句的复制。此外,与“传统”锁定模式一样,任何给定语句分配的自动增量编号都是连续的。对于任何使用自增的语句,与“传统”模式相比,语义没有任何变化,但有一个重要的例外。
"mixed-mode inserts"例外,其中用户为多行"simple insert"中的某些(但不是全部)行的AUTO_INCREMENT列提供了显式值。对于此类插入,InnoDB分配的自增值比要插入的行数更多。但是,所有自动分配的值都是连续生成的(因此高于由最近执行的先前语句生成的自增值)。"多余"的自增值被丢失。
·2表示"interleaved"锁模式
在这种锁模式下,没有"insert-like"语句使用表级的AUTO-INC锁定,并且可以同时执行多个语句。这是最快,最具扩展性的锁定模式,但是当使用基于语句的复制或恢复方案从二进制日志中重放SQL语句时,这是不安全的。
在这种锁模式下,保证自增值是唯一的,并且在所有同时执行的"insert-like"语句中单调递增。但是,由于多个语句可以同时生成数字(也就是说,在语句之间交错分配数字),因此为任何给定语句插入的行生成的值可能不是连续的。
如果仅执行的语句是"insert-like",为单个语句生成的数字中没有空格。其中要提前知道要插入的行数,则为“除混合模式插入”外,为单个语句生成的数字中没有空格。但是,执行"bulk-inserts"时,任何给定语句分配的自增值可能存在间隙。
Innodb自增锁的使用
1.使用自增列进行复制
如果使用基于语句的复制,innodb_autoinc_lock_mode设置为0或1(主、备都使用相同的配置)。如果是设置为2或者主备设置不一样,不能保证准备端一致。
如果是使用基于行的、混合模式的复制,所有自增锁模式都是安全的,因为基于行的复制对sql的执行顺序是不敏感的(混合模式会将基于语句的不安全的复制转换成行复制)。
2.自增值的丢失和序列空隙(sequence gaps)
在所有自增锁模式中(0,1,2),如果使用自增值得事务发生了回滚,这些自增值就丢失了。这些值是不可以重用的,这样就产生了gap。
3.自增列指定null或0值
在所有自增锁模式中(0,1,2),如果将自增列指定为null或0,innodb会自行给自增列赋值
4.自增列指定为负值
可以插入赋值,但是不能在负值得基础上进行自增
5.超过自增列指定的最大值
超过自增列指定的最大值就不能自增了。
6."mixed-mode inserts"中自增值得使用
"mixed-mode inserts"中有"simple insert"指定的自增值,也有没有指定的。在不同的自增锁模式下结果是不同的。
7.在插入的过程中修改自增值
会导致重复的值。
Innodb自增计数器的初始化
创建了自增列后,对应的数据字典中包含一个自增计数器,用来为列赋值。这个计数器是存储在内存中,而不是磁盘上。
在实例启动后,innodb会执行类似下面的语句来为计数器初始化:
select max(ai_col) from table_name for update;
缺省情况下,会将获取的值增加1。也可以修改参数auto_increment_increment进行配置。
如果表是空的,计数器的值就是1。