下面小编就为大家带来一篇浅谈innodb_autoinc_lock_mode的表现形式和选值参考方法。小编觉得挺不错的,现在就分享给大家,也给大家做个参考。一起跟随小编过来看看吧
前提条件,percona 5.6版本,事务隔离级别为RR
mysql> show create table test_autoinc_lock\G *************************** 1. row *************************** Table: test_autoinc_lock Create Table: CREATE TABLE `test_autoinc_lock` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_a` (`a`) ) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> select * from test_autoinc_lock; +----+------+ | id | a | +----+------+ | 1 | 1 | | 12 | 2 | | 2 | 3 | | 3 | 5 | | 4 | 7 | | 5 | 7 | | 6 | 9 | | 7 | 10 | +----+------+ 8 rows in set (0.00 sec)
条件1 innodb_autoinc_lock_mode设置为0
session1 begin;delete from test_autoinc_lock where a>7;//这时未提交 session2 mysql> insert into test_autoinc_lock(a) values(100);//gap锁的存在,这时处于锁等待 session3 mysql> insert into test_autoinc_lock(a) values(2);//这时同样处于等待状态,理论上这个不是gap锁的锁定范围,那么它是在等什么呢 session4 mysql> select * from information_schema.innodb_trx\G *************************** 1. row *************************** trx_id: 2317 trx_state: LOCK WAIT trx_started: 2016-10-31 19:28:05 trx_requested_lock_id: 2317:20 trx_wait_started: 2016-10-31 19:28:05 trx_weight: 1 trx_mysql_thread_id: 9 trx_query: insert into test_autoinc_lock(a) values(2) trx_operation_state: setting auto-inc lock trx_tables_in_use: 1 trx_tables_locked: 1 trx_lock_structs: 1 trx_lock_memory_bytes: 360 trx_rows_locked: 0 trx_rows_modified: 0 trx_concurrency_tickets: 0 trx_isolation_level: REPEATABLE READ trx_unique_checks: 1 trx_foreign_key_checks: 1 trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 10000 trx_is_read_only: 0 trx_autocommit_non_locking: 0
这时查看session3是等待自增锁,一直处于setting auto-inc lock状态
session2
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
这时session3锁等待超时退出
session3
这时再看session3可以发现insert完成。
mysql> select * from test_autoinc_lock; +----+------+ | id | a | +----+------+ | 1 | 1 | | 12 | 2 | | 13 | 2 | | 2 | 3 | | 3 | 5 | | 4 | 7 | | 5 | 7 | | 6 | 9 | | 7 | 10 | +----+------+ 9 rows in set (0.00 sec)//注意看这时的最大自增值是13,也就是之前自增最大值上+1,也就是说session2后来释放了预计生成的自增id,将13留给了session3,自增id值的申请完全是串行顺序的。
结论:innodb_autoinc_lock_mode为0时的,也就是官方说的traditional
级别,该自增锁是表锁级别,且必须等待当前SQL执行完成后或者回滚掉才会释放,这样在高并发的情况下可想而知自增锁竞争是比较大的。
条件2 innodb_autoinc_lock_mode设置为1
session1 mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> delete from test_autoinc_lock where a>7; Query OK, 2 rows affected (0.00 sec) mysql> select * from test_autoinc_lock; +----+------+ | id | a | +----+------+ | 1 | 1 | | 12 | 2 | | 13 | 2 | | 2 | 3 | | 3 | 5 | | 4 | 7 | | 5 | 7 | | 6 | 9 | | 7 | 10 | +----+------+ 9 rows in set (0.00 sec)//注意看这时的最大自增值是13 session2 mysql> insert into test_autoinc_lock(a) values(100);//同样gap锁的存在,这时处于锁等待 session3 mysql> insert into test_autoinc_lock(a) values(5); Query OK, 1 row affected (0.00 sec) mysql> select * from test_autoinc_lock; +----+------+ | id | a | +----+------+ | 1 | 1 | | 12 | 2 | | 13 | 2 | | 2 | 3 | | 3 | 5 | | 15 | 5 | | 4 | 7 | | 5 | 7 | | 6 | 9 | | 7 | 10 | +----+------+ 10 rows in set (0.00 sec)//session3直接完成了,并且注意观察插入的自增id值是15,也就是跳过了预计分配给session2的14,可以看到自增id值立马就分配给了session3,而不必等session2执行完成
结论:innodb_autoinc_lock_mode为1时的,也就是官方说的consecutive
级别,这时如果是单一的insert SQL,可以立即获得该锁,并立即释放,而不必等待当前SQL执行完成(除非在其他事务中已经有session获取了自增锁)。另外当SQL是一些批量insert sql时,比如insert into ...select ...,load data,replace ..select..时,这时还是表级锁,可以理解成退化为必须等待当前SQL执行完才释放。
可以认为,该值为1时是相对比较轻量的锁,也不会对复制产生影响,唯一的缺陷是产生的自增值不一定是完全连续的(不过个人认为这个往往不是很重要,也没必要根据自增id值来统计行数之类)
条件3 innodb_autoinc_lock_mode设置为2
先说结论:当innodb_autoinc_lock_mode设置为2时,所有insert种类的SQL都可以立马获得锁并释放,这时的效率最高。但是会引入一个新的问题:当binlog_format为statement时,这时的复制没法保证安全,因为批量的insert,比如insert ..select..语句在这个情况下,也可以立马获取到一大批的自增id值,不必锁整个表,slave在回放这个sql时必然会产生错乱。我们做个测试验证复制不是安全的。
master session1 mysql> show variables like '%binlog_for%'; +---------------+-----------+ | Variable_name | Value | +---------------+-----------+ | binlog_format | STATEMENT | +---------------+-----------+ 1 row in set (0.00 sec) mysql> insert into test_autoinc_lock(a) select * from test_auto; Query OK, 8388608 rows affected, 1 warning (29.85 sec) Records: 8388608 Duplicates: 0 Warnings: 1 master session2(注意session2在session1执行完成之前执行) mysql> insert into test_autoinc_lock(a) values(2); Query OK, 1 row affected (0.01 sec) mysql> select * from test_autoinc_lock where a=2; +---------+------+ | id | a | +---------+------+ | 1376236 | 2 | +---------+------+ 1 row in set (0.00 sec) slave session1(这时可看到1376236主键冲突) mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.9.73.139 Master_User: ucloudbackup Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000006 Read_Master_Log_Pos: 75823243 Relay_Log_File: mysql-relay.000002 Relay_Log_Pos: 541 Relay_Master_Log_File: mysql-bin.000006 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1062 Last_Error: Error 'Duplicate entry '1376236' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'insert into test_autoinc_lock(a) select * from test_auto' Skip_Counter: 0 Exec_Master_Log_Pos: 75822971
我们这时解析下主库的binlog不难发现问题原因,第一条批量insert还没执行完时,第二条简单insert执行时获得了自增id值为1376236的锁,这时在主库写入是没有问题的,但是反应到从库时,因为是基于statement的复制,必然出现主键冲突。
SET INSERT_ID=1376236/*!*/; #161031 21:44:31 server id 168380811 end_log_pos 75822940 CRC32 0x65797f1c Query thread_id=20 exec_time=0 error_code=0 use `test`/*!*/; SET TIMESTAMP=1477921471/*!*/; insert into test_autoinc_lock(a) values(2) /*!*/; # at 75822940 #161031 21:44:31 server id 168380811 end_log_pos 75822971 CRC32 0xbb91449d Xid = 274 COMMIT/*!*/; # at 75822971 #161031 21:44:26 server id 168380811 end_log_pos 75823050 CRC32 0xa297b57b Query thread_id=57 exec_time=30 error_code=0 SET TIMESTAMP=1477921466/*!*/; BEGIN /*!*/; # at 75823050 # at 75823082 #161031 21:44:26 server id 168380811 end_log_pos 75823082 CRC32 0xa5aa31a1 Intvar SET INSERT_ID=1/*!*/; #161031 21:44:26 server id 168380811 end_log_pos 75823212 CRC32 0x470282ba Query thread_id=57 exec_time=30 error_code=0 SET TIMESTAMP=1477921466/*!*/; insert into test_autoinc_lock(a) select * from test_auto
总结:
1 innodb row复制时,可将innodb_autoinc_lock_mode设置为2,这时可在所有insert情况下表获得最大并发度
2 innodb statement复制时,可将innodb_autoinc_lock_mode设置为1,保证复制安全的同时,获得简单insert语句的最大并发度
3 myisam引擎情况下,无论什么样自增id锁都是表级锁,设置innodb_autoinc_lock_mode参数无效(测试略)
4 实际上提问者说到的在innodb引擎下自增id值作为主键的情况下,相比uuid或者自定义的主键,是可以提到插入速度的,因为innodb是主键聚集索引,实际的主键值必须按照主键顺序存取,那么自增id本身就是升序的,那么在插入数据时,底层就不必再做额外的排序操作,也减少了索引页分裂的次数,从而大大增加insert速度(除非其他方案也能保证主键完全自增)
【相关推荐】
1. Mysql免费视频教程
以上是实例详解mysql中innodb_autoinc_lock_mode的详细内容。更多信息请关注PHP中文网其他相关文章!

InnoDB是一个将表中的数据存储到磁盘上的存储引擎,所以即使关机后重启我们的数据还是存在的。而真正处理数据的过程是发生在内存中的,所以需要把磁盘中的数据加载到内存中,如果是处理写入或修改请求的话,还需要把内存中的内容刷新到磁盘上。而我们知道读写磁盘的速度非常慢,和内存读写差了几个数量级,所以当我们想从表中获取某些记录时,InnoDB存储引擎需要一条一条的把记录从磁盘上读出来么?InnoDB采取的方式是:将数据划分为若干个页,以页作为磁盘和内存之间交互的基本单位,InnoDB中页的大小一般为16

InnoDB是MySQL的数据库引擎之一,现为MySQL的默认存储引擎,为MySQL AB发布binary的标准之一;InnoDB采用双轨制授权,一个是GPL授权,另一个是专有软件授权。InnoDB是事务型数据库的首选引擎,支持事务安全表(ACID);InnoDB支持行级锁,行级锁可以最大程度的支持并发,行级锁是由存储引擎层实现的。

1.Mysql的事务隔离级别这四种隔离级别,当存在多个事务并发冲突的时候,可能会出现脏读,不可重复读,幻读的一些问题,而innoDB在可重复读隔离级别模式下解决了幻读的一个问题,2.什么是幻读幻读是指在同一个事务中,前后两次查询相同范围的时候得到的结果不一致如图,第一个事务里面,我们执行一个范围查询,这个时候满足条件的数据只有一条,而在第二个事务里面,它插入一行数据并且进行了提交,接着第一个事务再去查询的时候,得到的结果比第一次查询的结果多出来一条数据,注意第一个事务的第一次和第二次查询,都在同

一、回退重新装mysql为避免再从其他地方导入这个数据的麻烦,先对当前库的数据库文件做了个备份(/var/lib/mysql/位置)。接下来将Perconaserver5.7包进行了卸载,重新安装原先老的5.1.71的包,启动mysql服务,提示Unknown/unsupportedtabletype:innodb,无法正常启动。11050912:04:27InnoDB:Initializingbufferpool,size=384.0M11050912:04:27InnoDB:Complete

MySQL储存引擎选型对比:InnoDB、MyISAM与Memory性能指标评估引言:在MySQL数据库中,储存引擎的选择对于系统性能和数据完整性起着至关重要的作用。MySQL提供了多种储存引擎,其中最常用的引擎包括InnoDB、MyISAM和Memory。本文将就这三种储存引擎进行性能指标评估,并通过代码示例进行比较。一、InnoDB引擎InnoDB是My

MySQL是一款广泛使用的数据库管理系统,不同的存储引擎对数据库性能有不同的影响。MyISAM和InnoDB是MySQL中最常用的两种存储引擎,它们的特点各有不同,使用不当可能会影响数据库的性能。本文将介绍如何使用这两种存储引擎来优化MySQL性能。一、MyISAM存储引擎MyISAM是MySQL最常用的存储引擎,它的优点是速度快,存储占用空间小。MyISA

Jdk1.5以后,在java.util.concurrent.locks包下,有一组实现线程同步的接口和类,说到线程的同步,可能大家都会想到synchronized关键字,这是java内置的关键字,用来处理线程同步的,但这个关键字有很多的缺陷,使用起来也不是很方便和直观,所以就出现了Lock,下面,我们就来对比着讲解Lock。通常我们在使用synchronized关键字的时候会遇到下面这些问题:(1)不可控性,无法做到随心的加锁和释放锁。(2)效率比较低下,比如我们现在并发的读两个文件,读与读之

提高MySQL存储引擎读取性能的技巧和策略:MyISAM与InnoDB对比分析引言:MySQL是最常用的开源关系型数据库管理系统之一,主要用于存储和管理大量结构化数据。在应用中,对于数据库的读取性能往往是非常重要的,因为读取操作是大部分应用的主要操作类型。本文将重点讨论如何提高MySQL存储引擎的读取性能,重点对比分析MyISAM和InnoDB这两个常用的存


热AI工具

Undresser.AI Undress
人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover
用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool
免费脱衣服图片

Clothoff.io
AI脱衣机

AI Hentai Generator
免费生成ai无尽的。

热门文章

热工具

SublimeText3 Mac版
神级代码编辑软件(SublimeText3)

SublimeText3 Linux新版
SublimeText3 Linux最新版

SecLists
SecLists是最终安全测试人员的伙伴。它是一个包含各种类型列表的集合,这些列表在安全评估过程中经常使用,都在一个地方。SecLists通过方便地提供安全测试人员可能需要的所有列表,帮助提高安全测试的效率和生产力。列表类型包括用户名、密码、URL、模糊测试有效载荷、敏感数据模式、Web shell等等。测试人员只需将此存储库拉到新的测试机上,他就可以访问到所需的每种类型的列表。

WebStorm Mac版
好用的JavaScript开发工具

SublimeText3 英文版
推荐:为Win版本,支持代码提示!