2021-06-05 11:30  阅读(1511)
文章分类:MySQL 技术内幕 文章标签:MySQLMySQL 技术内幕MySQL 高级
©  原文作者:一直不懂 原文地址:https://blog.csdn.net/shenchaohao12321/category_8075653.html

自增长在数据库中是非常常见的一种属性,也是很多DBA或开发人员首选的主键方式。在 InnoDB存储引擎的内存结构中,对每个含有自增长值的表都有一个自增长计数器(auto-increment counter)。当对含有自增长的计数器的表进行插入操作时,这个计数器会被初始化,执行如下的语句来得到计数器的值:

        SELECT MAX (auto_inc_col) FROM t FOR UPDATE;

插入操作会依据这个自增长的计数器值加1赋予自增长列。这个实现方式称做AUTO-INC Locking。这种锁其实是采用一种特殊的表锁机制,为了提高插入的性能,锁不是在一个事务完成后才释放,而是在完成对自增长值插入的SQL语句后立即释放。
虽然AUTO-INC Locking从一定程度上提高了并发插入的效率,但还是存在一些性能上的问题。首先,对于有自增长值的列的并发插入性能较差,事务必须等待前一个插入的完成(虽然不用等待事务的完成)。其次,对于 INSERT… SELECT的大数据量的插人会影响插人的性能,因为另一个事务中的插入会被阻塞。
从 MySQL5.1.22版本开始, InnoDB存储引擎中提供了一种轻量级互斥量的自增长实现机制,这种机制大大提高了自增长值插入的性能。并且从该版本开始, InnoDB存储引擎提供了一个参数 innodb_autoinc_lock_mode来控制自增长的模式,该参数的默认值为1。
在继续讨论新的自增长实现方式之前,需要对自增长的插入进行分类,如下表所示:

插入类型 说明
插入类型 说明
insert-like 指所有的插入语句,如INSERT、REPLACE、INSERT…SELECT,REPLACE…SEECT、LOADDATA等
simpleinserts simpleinserts指能在插入前就确定插入行数的语句。这些语句包括INSERT、REPLACE等。需要注意的是;simpleinserts不包含INSERT…ONDUPLICATEKEYUPDATE这类SQL语句
bulkinserts 指在插入前不能确定得到插人行数的语句,如INSERT…SELECT,REPLACE...SELECT,LOADDATA
mixed-modeinserts 指插入中有一部分的值是自增长的,有一部分是确定的。如INSERTINTOt(c1,c2)VALUES(1,'a'),(NULL,'b'),(5,'c'),(NULL,'d');也可以是指INSERT…ONDUPLICATEKEYUPDATE这类SQL语句

接着来分析参数 innodb_autoinc_lock_mode以及各个设置下对自增的影响,其总共有三个有效值可供设定,即0、1、2,具体说明如表所示。

innodb_autoinc_lock_mode 说明
innodb_autoinc_lock_mode 说明
0 这是MySQL5.1.22版本之前自增长的实现方式,即通过表锁的AUTO-INCLocking方式。因为有了新的自增长实现方式,0这个选项不应该是新版用户的首选项。
1 这是该参数的默认值。对于“simpleinserts”,该值会用互斥量(mutex)去对内存中的计数器进行累加的操作。对于“bulkinserts”,还是使用传统表锁的AUTO-INCLocking方式。在这种配置下,如果不考虑回滚操作,对于自增值列的增长还是连续的。并且在这种方式下,statement-based方式的replication还是能很好地工作。需要注意的是,如果已经使用AUTO-INCLocing方式去产生自增长的值,而这时需要再进行“simpleinserts”的操作时,还是需要等待AUTO-INCLocking的释放。
2 在这个模式下,对于所有“INSERT-like”自增长值的产生都是通过互斥量,而不是AUTO-INCLocking的方式。显然,这是性能最高的方式。然而,这会带来定的问题。因为并发插人的存在,在每次插入时,自增长的值可能不是连续的。此外,最重要的是,基于Statement-BaseReplication会出现问题。因此,使用这个模式,任何时候都应该使用row-basereplication。这样才能保证最大的并发性能及replication主从数据的一致。

此外,还需要特别注意的是InnoDB存储引擎中自增长的实现和 MyISAM不同。MyIsAM存储引擎是表锁设计,自增长不用考虑并发插入的问题。因此在master上用InnoDB存储引擎,在slave上用 MyISAM存储引擎的 replication架构下,用户必须考虑这种情况。
另外,在 InnoDB存储引擎中,自增长值的列必须是索引,同时必须是索引的第一个列。如果不是第一个列,则 MySQL数据库会抛出异常,而 MyISAM存储引擎没有这个问题,下面的测试反映了这两个存储引擎的不同。
202106051130446381.png


来源:https://blog.csdn.net/shenchaohao12321/category_8075653.html

点赞(0)
版权归原创作者所有,任何形式转载请联系作者; Java 技术驿站 >> 【MySQL技术内幕】38-自增长与锁
上一篇
【MySQL技术内幕】37-一致性锁定读
下一篇
【MySQL技术内幕】39-外键和锁